Bug #19478 SQL 'UPDATE' stops working
Submitted: 2 May 2006 13:04 Modified: 29 Jan 2008 10:02
Reporter: Van Stokes Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.20 OS:Windows (Windows 2003)
Assigned to: MySQL Verification Team CPU Architecture:Any

[2 May 2006 13:04] Van Stokes
Description:
Server: 5.0.20, Windows 2003, MySQLD-MAX
Problem with C API
Dev Env: Microsoft 2005 Pro (V 8.0)

Reference forum post: http://forums.mysql.com/read.php?45,86446,86691#msg-86691

Problem appeared after upgrading from MySQL serer 5.0.18 to 5.0.20.

Problem:

Issuing a SQL 'UPDATE' query via the C API does not always yield a proper response to 'affect rows' and it failes to 'update' the table. It is irratic. The same command will work for awhile (when the app starts) and then just stop working only after several calls are made. The application function that is calling the sql 'UPDATE' shares the same MySQL database connection with other functions inside the application. What is interesting is that sql 'SELECTS' continue to work on the same connection even after the sql 'UPDATE' stops working.

Here is a log entry from my application (not from MySQL)

--- LOG BEGIN --- 

04/30/06 @ 21:03:36: clsScene::saveInventory (Q:0, 0, 0, S:299200, -20, 20): ERROR: Inventory 58. SQL UPDATE did not affect any rows! 
04/30/06 @ 21:03:36: clsScene::saveInventory (Q:0, 0, 0, S:299200, -20, 20): UPDATE Inventory SET ContInvKey=6, ContAs=0, QuadrantX=0, QuadrantY=0, QuadrantZ=0, SectorX=299200, SectorY=-20, SectorZ=20, PositionX=13500.000, PositionY=0.000, PositionZ=2000.000, OrientationW=1.000, OrientationX=0.000, OrientationY=0.000, OrientationZ=0.000, RecLastModified=NULL WHERE InventoryKey=58 
04/30/06 @ 21:03:36: clsScene::CheckDatabaseError (Q:0, 0, 0, S:299200, -20, 20): MySQL Error: 0: 
04/30/06 @ 21:03:36: clsScene::CheckDatabaseError (Q:0, 0, 0, S:299200, -20, 20): MySQL Info: Rows matched: 1 Changed: 0 Warnings: 0 
04/30/06 @ 21:03:36: clsScene::processSceneLists (Q:0, 0, 0, S:299200, -20, 20): ERROR: Inventory 58. saveInventory() failed. 

--- LOG END ---

Note the SQL 'UPDATE' command of:

UPDATE Inventory SET ContInvKey=6, ContAs=0, QuadrantX=0, QuadrantY=0, QuadrantZ=0, SectorX=299200, SectorY=-20, SectorZ=20, PositionX=13500.000, PositionY=0.000, PositionZ=2000.000, OrientationW=1.000, OrientationX=0.000, OrientationY=0.000, OrientationZ=0.000, RecLastModified=NULL WHERE InventoryKey=58

My appliction is failing NOT because there was a MySQL error, note the line:

MySQL Error: 0:

and the MySQL INFO line:

MySQL Info: Rows matched: 1 Changed: 0 Warnings: 0 

It's failing because the C API is reporting back that there are ZERO "affected rows". And indeed if I check the database, the table.row was NOT updated. If I copy and paste that exact SQL statement into a client it executes fine and updates the row.

I don't understand how MySQL INFO reports back a matching row but never performs the update and does not issue an error code and to further compound the problem, the same statement/function works correctly after the application starts and continues to work for many uses but just stops working. I have looked at the MySQL server logs and there is nothing posted that indicates and error. I have also dropped and recreated the entire schema thinking it may have been caused during an upgrade.

Here is the create table syntax:

CREATE TABLE `inventory` (
  `InventoryKey` bigint(20) NOT NULL default '0',
  `ItemKey` bigint(20) NOT NULL default '0',
  `ObjectTypeKey` bigint(20) NOT NULL default '0',
  `ContInvKey` bigint(20) NOT NULL default '0',
  `ContAs` tinyint(4) NOT NULL default '0',
  `OwnerType` tinyint(4) NOT NULL default '0',
  `OwnerKey` bigint(20) NOT NULL default '0',
  `Units` float NOT NULL default '1',
  `QuadrantX` double NOT NULL default '0',
  `QuadrantY` double NOT NULL default '0',
  `QuadrantZ` double NOT NULL default '0',
  `SectorX` double NOT NULL default '0',
  `SectorY` double NOT NULL default '0',
  `SectorZ` double NOT NULL default '0',
  `PositionX` double NOT NULL default '0',
  `PositionY` double NOT NULL default '0',
  `PositionZ` double NOT NULL default '0',
  `OrientationW` double NOT NULL default '1',
  `OrientationX` double NOT NULL default '0',
  `OrientationY` double NOT NULL default '0',
  `OrientationZ` double NOT NULL default '0',
  `DateTimeCreated` datetime default NULL,
  `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`InventoryKey`),
  KEY `Inventory_IDX1` (`ItemKey`),
  KEY `Inventory_IDX2` (`ObjectTypeKey`),
  KEY `Inventory_IDX3` (`ContInvKey`),
  KEY `Inventory_IDX4` (`OwnerType`,`OwnerKey`),
  KEY `Inventory_IDX5` (`QuadrantX`,`QuadrantY`,`QuadrantZ`,`SectorX`,`SectorY`,`SectorZ`),
  CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`ItemKey`) REFERENCES `item` (`ItemKey`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `inventory_ibfk_2` FOREIGN KEY (`ObjectTypeKey`) REFERENCES `objecttype` (`ObjectTypeKey`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

How to repeat:
Here is my (hacked) code:

mDatabaseConn = mysql_init( (MYSQL*) 0);	// MySQL Init

mysql_real_connect( 
			mDatabaseConn,
			clsServerConfig::getSingleton().DBServerAddress,
			clsServerConfig::getSingleton().DBServerLogin,
			clsServerConfig::getSingleton().DBServerAuthCode,
			NULL,
			clsServerConfig::getSingleton().DBServerPortNum,
			NULL,
			0 ) );

mysql_select_db( mDatabaseConn, DB_SCHEMA );

// Set MySQL options;
mDatabaseConn->reconnect = 1;	// Enable auto reconnect.
mysql_autocommit( mDatabaseConn, true );

...........

mLastMySQLError = mysql_query( mConnection, mSQLQueryString );
if (mLastMySQLError) return false;
	
mResultSet = mysql_store_result( mConnection );
mLastMySQLError = mysql_errno( mConnection );
if ( mLastMySQLError ) return false;
	
if ( !mResultSet )
{
	// mysql_store_result() returned nothing; should it have?
        if( mysql_field_count( mConnection ) == 0 )
        {
            // query does not return data
            // (it was not a SELECT)
            mAffectedRows = (unsigned int) mysql_affected_rows( mConnection );
			return true;
	}
}

Suggested fix:
I don't know.
[3 May 2006 17:25] MySQL Verification Team
Thank you for the bug report. Could you please provide the complete
C test code and some insert data if needed for test.

Thanks in advance.
[4 May 2006 12:15] Van Stokes
Actual Code and data you requested.

Attachment: Bug19478-01.zip (application/zip, text), 8.36 KiB.

[4 May 2006 12:23] Van Stokes
I have attached a zip file that contains the information you requested.

The class 'clsRecordSet' is what is used to work with the MySQL database. Details are in the clsRecordSet.h file on how to use. In short, it's a very very simple class based on the examples supplied in the MySQL documentation.

A code file which contains the actual code in production. There code file has the following functions:

ConnectToDatabase() - to connect to the MySQL database.

CheckDatabaseConnectivity() - that checks the connectivity

CheckDatabaseError() - called by other functions when a database error is suspected.

saveInventory() - the actual code that is creating the sql UPDATE command and executing it. It is here that I am getting the annomally - at the ' mInvRS.getNumberOfAffectedRows() '.

A file with the actual sql 'UPDATE' commands as generated by the 'saveInventory()' function.

A 'create table' sql text file was included.
[8 May 2006 1:13] Van Stokes
OK, we think we found the issue.
If successive updates are issued within the same time frame (i.e. second) then the update is not performed. That is why it would work for awhile after starting our service and then stop. Sufficient time had elapsed. However, if we attempted to update the database too quickly, the database (or API) is ignoring the request.
[29 Jan 2008 10:02] MySQL Verification Team
I am not able to repeat with latest source.