Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a script which is heavily doing select and insert nearly every seconds without fail and the total queries varies from a second to another. Below is the results of running the queries and finally is the error below Commands out of sync; you can't run this command now or sometimes it gives me connection lost.

Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.116','00:14:5E:95:B9:18','53',995,7)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.172','90:F6:52:20:15:E8','2049',5599,16)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.222','00:0C:29:EE:DE:BC','0',60,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.235','00:1E:90:3B:92:AA','17500',408,2)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.24','00:30:1E:F2:5F:B8','0',60,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.250','00:1E:68:5C:CF:93','32767',175,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.43','20:CF:30:5E:0B:41','137',359,3)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.52','00:1E:0B:85:B0:B1','22',10963,40)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.57','00:24:E8:79:BD:3B','5060',561,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.65','00:1A:64:21:D6:B4','0',60,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.160.9','00:09:0F:EF:DA:31','0',60,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.161.115','00:00:1C:DC:22:45','17500',408,2)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.161.137','D8:30:62:63:65:40','0',60,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.161.16','00:1E:90:3B:8F:6C','137',92,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.161.212','48:5D:60:00:DE:C0','137',359,3)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.161.233','00:25:64:CB:92:5C','17500',604,4)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.161.234','00:0C:29:34:8E:FF','137',92,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','10.207.161.3','00:D0:4C:D0:13:B8','32767',1524,19)

Time is 2012-07-16 23:51:46Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','169.254.122.188','00:19:21:3B:F4:17','32767',175,1)
Query 2 after executing INSERT INTO  export1 (iBTID ,timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES (NULL,'2012-07-16 23:51:45','203.91.121.76','00:60:E0:44:E4:B2','32767',1062,9)
Error in second query of insert Commands out of sync; you can't run this command now
share|improve this question

1 Answer 1

up vote 1 down vote accepted

If iBTID is an auto_increment column in the export1 table, you may want to change the INSERT INTO command to exclude that column as follows:

INSERT INTO export1 (timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES ('2012-07-16 23:51:45','10.207.160.116','00:14:5E:95:B9:18','53',995,7)

Every time you INSERT and hand mysqld a row where you set an auto_increment column to NULL, mysqld tries to reconcile the INSERT's Primary Key by finding the next auto_increment value and handling it internally. That's could be a little taxing on mysqld because handing a PRIMARY KEY (which is all defined as NOT NULL) a NULL value forces this reconciliation exercise. That is really unnatural in terms of ANSI SQL.

share|improve this answer
    
ok will take your advice accordingly and perform the changes. –  newbie14 Jul 17 '12 at 1:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.