MySQL問題一則:Commands out of sync; you can’t run this command now以及相關問題

錄製程式有一功能：將錄製的檔案資訊寫入MySQL資料庫，供BS系統查詢。

因此封裝了一個MySQL類，進行資料庫操作。

主要介面為Update()：執行SQL語句。

 

現在問題來了：
（一）在某個情境下，我們在向table1中insert一條記錄後，需要得到得到它的ID,然後update與之匹配的另一張表table2中的記錄。由於insert本身並不返回結果集，因此我們無法直接得到插入記錄的ID。

 

那該怎麼辦呢？
之前從BS組得到的方法是：在table1中執行insert後，立即執行另一條語句：“select @@IDENTITY;”；該語句會返回最後插入的那條記錄的ID，這樣問題就解決了。

但是，在一次codeview上，頭兒提出了一個問題：如果在多線程中，在向table1執行insert後，另一個線程也執行了sql語句對資料庫進行操作，這時再執行“select @@IDENTITY;”，能得到正確的結果嗎？
很顯然，確實存在這種問題：即我們無法保證整個過程的原子性，但這可以通過加鎖來解決。
頭兒又問：那這時候如果其他的用戶端串連到資料庫，執行了查詢，會更改“select @@IDENTITY;”的結果嗎？
當時沒考慮到這一點，後來通過查詢mysql手冊（http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html），發現系統變數IDENTITY的作用範圍是“SESSION”，也就是其他用戶端、其他串連執行的query不會更改本次串連的IDENTITY變數的值，因此不會有影響。

後來發現mysql提供了內建函式LAST_INSERT_ID()，我認為比“select @@IDENTITY;”更合理。

 

（二）另一個問題，針對有些查詢，我們需要擷取並處理結果集。
原來是在Mysql類中封裝了一個成員變數MYSQL_RES* m_res，用來儲存有返回的查詢結果集。這樣一來，我們執行Update()的後，如果需要擷取結果集就調用GetQueryResult()來擷取結果集，用完之後再釋放掉。
但是問題在於：在多線程環境中，我們可能有多個Update()需要擷取結果集，但是如果它們共用一個成員變數m_res來儲存結果集的話，我們必須等待一次查詢使用完它的結果集並釋放後才能執行下一次的Update()，否則MySQL就會報錯：“”，因為此時m_res還未被釋放，不能執行下一次查詢。

 

針對以上問題，我們的解決辦法是：對查詢介面做調整。
一共提供兩個查詢介面：
（1）Update():執行查詢；針對問題（一），開啟multi-query支援，允許一次執行多條sql語句，具體操作參看（http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html）；這樣我們在Upadte時使用類似

insert into  t_alarm_record_file  (recordPath,recordName,hostIp,startTime,endTime,deviceId,programNumber,deviceType,interfaceNo,alarmType,alarmTime) values ('/figure/data/AlarmRecord/StreamTS/1-碼流_魅力音 樂主路/2011-11-07/20111107150116.ts','','10.0.60.2','2011-11-07 15:01:16','1970-01-01 08:00:00',37486602,3905,'0',1,12,'2011-11-07 15:01:20');update  t_alarm  set fileId = LAST_INSERT_ID()  where deviceId = 37486602 and programNumber = 3905 and alarmType = 12 and alarmDate = '2011-11-07 15:01:20' and fileId is null

 的語句一次執行兩條sql語句就可完美的解決這種問題了；
（2）Update2():執行查詢，並返回結果集給調用者，由調用者自己處理並決定何時釋放該結果集，由於該結果集是以參數的形式返回的，因此多線程不會受到影響。

但在實際測試中，還是出現問題:"Commands out of sync; you can't run this command now",我們的日誌顯示：

2011-11-07 15:01:28,660: INFO  : Update OK!sql: insert into  t_alarm_record_file  (recordPath,recordName,hostIp,startTime,endTime,deviceId,programNumber,deviceType,interfaceNo,alarmType,alarmTime) values ('/figure/data/AlarmRecord/StreamTS/1-碼流_魅力音 樂主路/2011-11-07/20111107150116.ts','','10.0.60.2','2011-11-07 15:01:16','1970-01-01 08:00:00',37486602,3905,'0',1,12,'2011-11-07 15:01:20');update  t_alarm  set fileId = LAST_INSERT_ID()  where deviceId = 37486602 and programNumber = 3905 and alarmType = 12 and alarmDate = '2011-11-07 15:01:20' and fileId is null2011-11-07 15:01:35,331: ERROR  : Update failed!sql: insert into  t_alarm_record_file  (recordPath,recordName,hostIp,startTime,endTime,deviceId,programNumber,deviceType,interfaceNo,alarmType,alarmTime) values ('/figure/data/AlarmRecord/StreamTS/1-碼流_魅力音 樂主路/2011-11-07/20111107150116.ts','','10.0.60.2','2011-11-07 15:01:16','1970-01-01 08:00:00',37486602,3905,'0',1,13,'2011-11-07 15:01:27');update  t_alarm  set fileId = LAST_INSERT_ID()  where deviceId = 37486602 and programNumber = 3905 and alarmType = 13 and alarmDate = '2011-11-07 15:01:27' and fileId is null, ERROR:Commands out of sync; you can't run this command now

 在第一次調用Update()執行多條sql語句時成功，但以後的所有調用都失敗了。

經過尋找，發現問題在於：在Update()中執行多條sql語句時，

如果僅僅是插入等不需要傳回值的SQL語句，也一樣得讀完整個resault集並釋放，最小化的寫法：

do
{
    result = mysql_store_result( mysql );
    mysql_free_result(result);
}while( !mysql_next_result( mysql ) );

更詳細的說明參見：http://www.rosoo.net/a/201103/11043.html

 

經過這麼一處理，問題終於解決了。