blknyella
asked on
MySQL Mass insert
I have a large embedded database file that i need to move to MySQL. I was able to look through all the jargon and find the data i needed (about 300,000 records). When I attempt to run INSERT for about 10 records some are skipped.
(Examples)
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat. domain.com /spark','c riss@chat. downetwork s.com/spar k',1260900 807810,'ye s i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat. domain.com /spark','c riss@chat. downetwork s.com/spar k',1260900 807810,'ye s i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat. domain.com /spark','c riss@chat. downetwork s.com/spar k',1260900 807810,'ye s i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat. domain.com /spark','c riss@chat. downetwork s.com/spar k',1260900 807810,'ye s i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat. domain.com /spark','c riss@chat. downetwork s.com/spar k',1260900 807810,'ye s i remember now');
Unfortunately, this is how the embedded database kept its records, so I figured it would be easiest to run them simultaneous. And of course some records are being skipped without error.
Please advise!
TIA
(Examples)
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.
Unfortunately, this is how the embedded database kept its records, so I figured it would be easiest to run them simultaneous. And of course some records are being skipped without error.
Please advise!
TIA
your queries should work as written and should never skip values as long as they don't terminate in error.
Some thoughts
1. Are you sure that you are capturing any errors made by your insert queries (specifically duplicate key errors)
2. Do you have any unique columns in your table? you have a lot of similar data up there. Do you have a primary key in place?
3. Are there any triggers on your table that might abort an insert on some condition?
Show us all the code you have, including table definitions and errors generated.
Some thoughts
1. Are you sure that you are capturing any errors made by your insert queries (specifically duplicate key errors)
2. Do you have any unique columns in your table? you have a lot of similar data up there. Do you have a primary key in place?
3. Are there any triggers on your table that might abort an insert on some condition?
Show us all the code you have, including table definitions and errors generated.
ASKER
The lines of code above are just a duplication of one record, mostly for visual aid. Here are some actual records that have skipped been skipped with no errors.
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat .domain.co m/spark',' shaun@chat .domain.co m/spark',1 2547205732 15,'more ok.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat .domain.co m/spark',' shaun@chat .domain.co m/spark',1 2547205835 79,'reg so.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'shaun@chat.d omain.com/ spark','ma rinda@chat .domain.co m/spark',1 2547205999 90,'dankie ');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat .domain.co m/spark',' shaun@chat .domain.co m/spark',1 2547206239 14,'Terwyl jy daar is, kan jy asb vir my die korrekte persoon kry om mee te praat oor betaling?');
Here is the CREATE TABLE code I used:
CREATE TABLE OFMESSAGEARCHIVE(CONVERSAT IONID BIGINT NOT NULL,FROMJID VARCHAR(1024) NOT NULL,TOJID VARCHAR(1024) NOT NULL,SENTDATE BIGINT NOT NULL,BODY VARCHAR(2048);
From the looks of the code, there is no primary key. There are also no triggers to my knowledge.
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'shaun@chat.d
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat
Here is the CREATE TABLE code I used:
CREATE TABLE OFMESSAGEARCHIVE(CONVERSAT
From the looks of the code, there is no primary key. There are also no triggers to my knowledge.
"When I attempt to run INSERT for about 10 records some are skipped." Can you write this 10 records you have tried to insert and some are skipped I will check them and tell you the reason. what you have is an sql file rights and your mysql is which version and on which operating system you are working? also attach your file if you can
how are you executing the sql file? are you doing something like this?
mysql -uuser -ppassword < file.sql
mysql -uuser -ppassword < file.sql
Your create table has an unterminated open parenthesis. I assume it has been shortened for this example.
I would be more interested in the lines before and after the ones that are skipped. I agree with mkiredjian. Can you provide the 10 records you have tried?
I would be more interested in the lines before and after the ones that are skipped. I agree with mkiredjian. Can you provide the 10 records you have tried?
ASKER
its not just 10 records, i'd say out of 300,000 about 80% of them are being skipped. This is not a sql file, but an embedded database file. I've copied the database to my desktop and sifted through the code to find records LIKE the ones you see above. I'm running MySQL verion 5.0.077 on Red Hat 4.1.2-50.
I'm not able to attach the file since this database is from our chat server and has important data. Sorry.
Thanks guys for helping me out.
I'm not able to attach the file since this database is from our chat server and has important data. Sorry.
Thanks guys for helping me out.
ok can you give some samples of the skipped ones . let me check it only 1-2 that are being skipped
ASKER
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat .domain.co m/spark',' shaun@chat .domain.co m/spark',1 2547205732 15,'more ok.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat .domain.co m/spark',' shaun@chat .domain.co m/spark',1 2547205835 79,'reg so.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'shaun@chat.d omain.com/ spark','ma rinda@chat .domain.co m/spark',1 2547205999 90,'dankie ');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat .domain.co m/spark',' shaun@chat .domain.co m/spark',1 2547206239 14,'Terwyl jy daar is, kan jy asb vir my die korrekte persoon kry om mee te praat oor betaling?');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'shaun@chat.d
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat
ASKER
the interesting thing is that if i run them individually, they run fine, but if run them of 5 or more, they'll skip. not sure why that is.
There's nothing wrong with these that you have provided. I've run them on my server and they run fine. I'm still interested in the lines above and below these.
I agree with xmediaman I've run them on my server and all were inserted nothing skipped
ASKER
i came to find out for some reason if the INSERT contained a "\" at the end of the statement, it would error out.
Ex:
INSERT INTO OFMESSAGEARCHIVE VALUES (15675,BLAH@BLAH.COM,BLAH@ BLAH.COM,1 2362356369 35, 'THIS IS A CONVERSATION \ ');
Thank you guys for helping out.
Ex:
INSERT INTO OFMESSAGEARCHIVE VALUES (15675,BLAH@BLAH.COM,BLAH@
Thank you guys for helping out.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Which column is your primary key? Is it possible that some of your source records have the same primary key?
Do all of your inserts have the same number of columns of values which matches the number of columns in the database?
Can you provide samples of the ones that were skipped?