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','criss@chat.downetworks.com/spark',1260900807810,'yes i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.domain.com/spark','criss@chat.downetworks.com/spark',1260900807810,'yes i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.domain.com/spark','criss@chat.downetworks.com/spark',1260900807810,'yes i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.domain.com/spark','criss@chat.downetworks.com/spark',1260900807810,'yes i remember now');
INSERT INTO OFMESSAGEARCHIVE VALUES(20116,'aheeva@chat.domain.com/spark','criss@chat.downetworks.com/spark',1260900807810,'yes 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

Kim Walker

All of your inserts are identical here. Are they different in your file?

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?

mankowitz

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.

blknyella

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.com/spark','shaun@chat.domain.com/spark',1254720573215,'more ok.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat.domain.com/spark','shaun@chat.domain.com/spark',1254720583579,'reg so.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'shaun@chat.domain.com/spark','marinda@chat.domain.com/spark',1254720599990,'dankie');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat.domain.com/spark','shaun@chat.domain.com/spark',1254720623914,'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(CONVERSATIONID 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.

mkiredjian

"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

mankowitz

how are you executing the sql file? are you doing something like this?

mysql -uuser -ppassword < file.sql

Kim Walker

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?

blknyella

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.

mkiredjian

ok can you give some samples of the skipped ones . let me check it only 1-2 that are being skipped

blknyella

ASKER

INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat.domain.com/spark','shaun@chat.domain.com/spark',1254720573215,'more ok.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat.domain.com/spark','shaun@chat.domain.com/spark',1254720583579,'reg so.');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'shaun@chat.domain.com/spark','marinda@chat.domain.com/spark',1254720599990,'dankie');
INSERT INTO OFMESSAGEARCHIVE VALUES(15157,'marinda@chat.domain.com/spark','shaun@chat.domain.com/spark',1254720623914,'Terwyl jy daar is, kan jy asb vir my die korrekte persoon kry om mee te praat oor betaling?');

blknyella

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.

Kim Walker

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.

mkiredjian

I agree with xmediaman I've run them on my server and all were inserted nothing skipped

blknyella

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,1236235636935, 'THIS IS A CONVERSATION \ ');

Thank you guys for helping out.

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial