MySQL insert into table where data already exists

[bmacbmac]

Hello. I have a 25gb SQLDataDump.SQL data dump I am trying to restore into MySQL. I am using the command line to insert the file:

mysql -u root -ppassword -h localhost database_name < SQLDataDump.sql

I am getting an error around line 18,000,000. I can find the problem line in a file viewer, but the 25gb file is too big for any editor I have tried. I am unable to delete that one line.

I found a text file splitter that will split up my File.SQL file into several smaller files. It split out into about 200 individual .sql files. The first file runs just fine:

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE `associnst` (
  `id` int(11) NOT NULL auto_increment,
  `SourceInstId` int(11) default NULL,
  `DestInstId` int(11) default NULL,
  `DestInstNum` varchar(32) default NULL,
  `DestVol` varchar(16) default NULL,
  `DestBook` varchar(8) default NULL,
  `DestPage` varchar(8) default NULL,
  `CaseFileId` int(11) default NULL,
  `CaseFileNumber` varchar(32) default NULL,
  `Version` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `SourceFK` (`SourceInstId`),
  KEY `DestIdFK` (`DestInstId`),
  KEY `DestNumIndx` (`DestInstNum`),
  KEY `DestBkPgIndx` (`DestBook`,`DestPage`),
  CONSTRAINT `associnst_ibfk_3` FOREIGN KEY (`DestInstId`) REFERENCES `instrument` (`Id`) ON UPDATE CASCADE,
  CONSTRAINT `associnst_ibfk_4` FOREIGN KEY (`SourceInstId`) REFERENCES `instrument` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=217684 DEFAULT CHARSET=utf8;



INSERT INTO `associnst` VALUES ('116000', '339849', '233945', null, null, '0184', '0376', '0', ', '0');
INSERT INTO `associnst` VALUES ('116001', '340055', '334564', null, null, '0402', '1644', '0', ', '0');
INSERT INTO `associnst` VALUES ('116002', '339848', '328682', null, null, '0394', '0921', '0', ', '0');
INSERT INTO `associnst` VALUES ('116003', '340056', '328612', null, null, '0394', '0758', '0', ', '0');
INSERT INTO `associnst` VALUES ('116004', '340057', '336476', null, null, '0405', '1510', '0', ', '0');

The next text file (disk2.sql) picks up right where the first one left off:

INSERT INTO `associnst` VALUES ('116000', '339849', '233945', null, null, '0184', '0376', '0', ', '0');
INSERT INTO `associnst` VALUES ('116001', '340055', '334564', null, null, '0402', '1644', '0', ', '0');
INSERT INTO `associnst` VALUES ('116002', '339848', '328682', null, null, '0394', '0921', '0', ', '0');
INSERT INTO `associnst` VALUES ('116003', '340056', '328612', null, null, '0394', '0758', '0', ', '0');
INSERT INTO `associnst` VALUES ('116004', '340057', '336476', null, null, '0405', '1510', '0', ', '0');

However, when I run it in command line I get an error:

ERROR 1452 (23000) at line 1: cannot add or update a child row: a foreign key constraint fails ('database_name', 'associsnt', constraint 'asocinst_ibfk_4' Foreign key ('sourceinstid') REFERENCES 'instrument' ('id) on update cascade)

These rows insert just fine when I run them as one command line job. Now that I am splitting it up I get this error. I am pretty familiar with MSSQL, but not MySQL. Anyone have an idea how I can make this work?

Thanks!

Brian

 

[r937]

the second file actually repeats the INSERTs from the first file

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bmacbmac]

That was a copy/paste mistake on my part. Here are the correct first/second parts:

first file:

INSERT INTO `associnst` VALUES ('116000', '339849', '233945', null, null, '0184', '0376', '0', ', '0');
INSERT INTO `associnst` VALUES ('116001', '340055', '334564', null, null, '0402', '1644', '0', ', '0');
INSERT INTO `associnst` VALUES ('116002', '339848', '328682', null, null, '0394', '0921', '0', ', '0');
INSERT INTO `associnst` VALUES ('116003', '340056', '328612', null, null, '0394', '0758', '0', ', '0');
INSERT INTO `associnst` VALUES ('116004', '340057', '336476', null, null, '0405', '1510', '0', ', '0');

and the 2nd file:

INSERT INTO `associnst` VALUES ('116010', '340062', '339010', null, null, '0409', '1296', '0', ', '0');
INSERT INTO `associnst` VALUES ('116011', '339859', '278744', null, null, '0285', '0488', '0', ', '0');
INSERT INTO `associnst` VALUES ('116012', '339853', '339428', null, null, '0410', '1083', '0', ', '0');
INSERT INTO `associnst` VALUES ('116013', '339853', '231649', null, null, '0178', '0477', '0', ', '0');
INSERT INTO `associnst` VALUES ('116014', '339863', '276873', null, null, '0281', '0155', '0', ', '0');

I have confirmed that the 'id' field data doesn't truly duplicate in the files.