We help IT Professionals succeed at work.

MySQL Mass insert

blknyella
blknyella asked
on
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
Comment
Watch Question

View Solution Only

Kim WalkerWeb Programmer/Technician

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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

Commented:
"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
CERTIFIED EXPERT

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

mysql -uuser -ppassword < file.sql

Kim WalkerWeb Programmer/Technician

Commented:
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

Author

Commented:
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

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

Author

Commented:
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

Author

Commented:
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 WalkerWeb Programmer/Technician

Commented:
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

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

Author

Commented:
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.
CERTIFIED EXPERT
Commented:
the slash is an escape character, that's why it makes an error. Suppose you had a string with a a single quote in it, you precede the quote with a slash

e.g.

INSERT INTO t1 (lastname, firstname) VALUES ('O\'Malley', 'Mary');

Open in new window


Without the slash, you'd have a syntax error

Explore More ContentExplore courses, solutions, and other research materials related to this topic.