MySQL Issue 1: Commands out of sync; you can't run this command now and related issues

The recording program has the function of writing recorded file information to the MySQL database for query by the BS system.

Therefore, a MySQL class is encapsulated for database operations.

The main interface is Update (): Execute SQL statements.

 

Now the question is:
(1) In a specific scenario, after inserting a record into table1, we need to get its ID and then update the matching record in another table table2. Because insert itself does not return a result set, we cannot directly obtain the ID of the inserted record.

 

What should we do?
The method obtained from the BS group is: Execute insert in table1 and then execute another statement: "select @ IDENTITY ;"; this statement returns the ID of the last inserted record. This solves the problem.

However, in a codeview, the head raised a question: if the insert statement is executed to table1 in multiple threads, the other thread also executes the SQL statement to operate the database, then, Run "select @ IDENTITY;" to obtain the correct result?
Obviously, this problem exists: we cannot guarantee the atomicity of the entire process, but this can be solved by locking.
Head asked again: if other clients connect to the database and execute the query, will the result of "select @ IDENTITY;" be changed?
At that time did not take this into account, and later by querying the mysql Manual (http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html), found that the scope of the system variable IDENTITY is "SESSION ", that is, the query executed by other clients and other connections will not change the value of the IDENTITY variable for this connection, so it will not be affected.

Later I found that mysql provided the internal function LAST_INSERT_ID (). I think it is more reasonable than "select @ IDENTITY.

 

(2) For some queries, we need to obtain and process the result set.
Originally, a member variable mysql_res * m_res was encapsulated in the MySQL class to save the returned query result set. In this way, after we execute Update (), if we need to obtain the result set, call getqueryresult () to obtain the result set, and release it after use.
But the problem is: in a multi-threaded environment, we may have multiple updates () to obtain the result set, but if they share a member variable m_res to store the result set, we must wait for a query to use its result set and release it before executing the next update (). Otherwise, MySQL will report the error "" Because m_res has not been released yet, you cannot perform the next query.

 

To solve the above problems, we can adjust the query interface.
Two Query Interfaces are provided:
(1) Update (): Execute the query; for problem (1), enable multi-query support, allow the execution of Multiple SQL statements at a time, specific operations see (http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html ); in this way, we use a similar

Insert into t_alarm_record_file (recordpath, recordname, hostip, starttime, endtime, DeviceID, programnumber, devicetype, interfaceno, alarmtype, alarmtime) values ('/figure/data/alarmrecord/streamts/1-bitstream _ charm music main path/2011-11-07/20111107150116. TS ', '', '10. 0.60.2 ', '2017-11-07 15:01:16', '2017-01-01 08:00:00 ', 37486602,3905, '0', 2011, '2017-11-07 15:01:20 '); update t_alarm set fileid = last_insert_id () Where DeviceID = 37486602 and programnumber = 3905 and alarmtype = 12 and alarmdate = '2017-11-07 15:01:20 'and fileid is null

Can solve this problem perfectly by executing two SQL statements at a time;
(2) update2 (): executes the query and returns the result set to the caller. The caller processes the query and determines when to release the result set. Because the result set is returned as a parameter, therefore, multithreading is not affected.

However, in the actual test, there is still a problem: "commands out of sync; you can't run this command now". Our log shows:

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-bitstream _ charm music main path/2011-11-07/20111107150116. ts ', '', '10. 0.60.2 ', '2017-11-07 15:01:16', '2017-01-01 08:00:00 ', 37486602,3905, '0', 2011, '2017-11-07 15:01:20 '); update t_alarm set fileId = LAST_INSERT_ID () where deviceI D = 37486602 and programNumber = 3905 and alarmType = 12 and alarmDate = '2017-11-07 15:01:20 'and fileId is null2011-11-07 15:01:35, 2011: 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-bitstream _ charm music main path/2011-11-07/20111107150116. ts ', '', '10. 0.60.2 ', '2017-11-07 15:01:16', '2017-01-01 08:00:00 ', 37486602,3905, '0', 2011, '2017-11-07 15:01:27 '); update t_alarm set fileId = LAST_INSERT_ID () where deviceId = 37486602 and programNumber = 3905 and alarmType = 13 and alarmDate = '2017-11-07 15:01:27 'and fileId is null, ERROR: commands out of sync; you can't run this command now

When Update () is called for the first time to execute multiple SQL statements, all subsequent calls fail.

After searching, the problem is that when multiple SQL statements are executed in update,

If you just insert an SQL statement that does not need to return a value, you have to read the entire resault set and release it. The minimal syntax is as follows:

Do
{
Result = mysql_store_result (mysql );
Mysql_free_result (result );
} While (! Mysql_next_result (mysql ));

For more details, see: http://www.rosoo.net/a/201103/11043.html

 

After such a solution, the problem was finally solved.