1

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

1 Answer 1

1

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.

1
  • ok will take your advice accordingly and perform the changes.
    – newbie14
    Jul 17, 2012 at 1:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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