creating new DB from a database dump always fails because the constraints are in the wrong place!

eos's profile image eos posted 7 years ago in Import/Export Permalink

If you right click on a DB and do "export" , select create database and create tables, it produces a file which has the FK constraints on each table creation, which will always fails because frequently table B has FK on A, and A has FK on B etc.

The constraints should all be created at the end of the file as ALTER statements.

Now I have a huge export which I have to hand edit to fix which will take hours.

Please could you fix this bug ASAP.

kalvaro's profile image kalvaro posted 7 years ago Permalink

I cannot reproduce it :-?

My dumps include this as script header:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

... and this as script footer:

/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;

That seems to address both potential problems (foreign key creation and data insertion), at least in my computer:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE TABLE IF NOT EXISTS `a` (
  `a_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `b_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a_id`),
  KEY `FK_a_b` (`b_id`),
  CONSTRAINT `FK_a_b` FOREIGN KEY (`b_id`) REFERENCES `b` (`b_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` (`a_id`, `a_name`, `b_id`) VALUES
    (1, 'Parent row', 1);
/*!40000 ALTER TABLE `a` ENABLE KEYS */;

CREATE TABLE IF NOT EXISTS `b` (
  `b_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`b_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*!40000 ALTER TABLE `b` DISABLE KEYS */;
INSERT INTO `b` (`b_id`, `b_name`) VALUES
    (1, 'Child row');
/*!40000 ALTER TABLE `b` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Please login to leave a reply, or register at first.