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.