| Bug #5740 | AIX Prepared query involving a datetime field can corrupt returned results | ||
|---|---|---|---|
| Submitted: | 24 Sep 2004 20:30 | Modified: | 20 Oct 2004 14:05 |
| Reporter: | Nathaniel Blanchard | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.5 | OS: | IBM AIX (AIX) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[25 Sep 2004 11:28]
Konstantin Osipov
Nathaniel, thank you for all the hard work you're doing to make sure that the new API is working well. I will look at the problem shortly. But could you provide me with definition and data for the table you're using for your query?
[25 Sep 2004 11:35]
Nathaniel Blanchard
Sorry about that.
THis is what I have, if you need it in an actual executable query format, let me know. I probably won't get back to this until Monday.
mysql> describe Bug;
+-------------------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+----------+------+-----+---------------------+-------+
| VisitID | int(11) | | | 0 | |
| FirstViewDateTime | datetime | | | 0000-00-00 00:00:00 | |
| FirstViewHour | int(11) | | | 0 | |
| VisitLength | int(11) | | | 0 | |
| Profiled | int(11) | | | 0 | |
| VisitID2 | int(11) | | | 0 | |
| Spider | int(11) | | | 0 | |
+-------------------+----------+------+-----+---------------------+-------+
7 rows in set (0.00 sec)
mysql> select * from Bug
-> ;
+---------+---------------------+---------------+-------------+----------+----------+--------+
| VisitID | FirstViewDateTime | FirstViewHour | VisitLength | Profiled | VisitID2 | Spider |
+---------+---------------------+---------------+-------------+----------+----------+--------+
| 269 | 1999-08-30 16:32:12 | 16 | 89 | 1 | 269 | 0 |
| 270 | 1999-08-30 16:33:01 | 16 | 291 | 1 | 270 | 0 |
| 271 | 1999-08-30 16:41:06 | 16 | 0 | 1 | 271 | 0 |
| 272 | 1999-08-30 16:42:35 | 16 | 274 | 1 | 272 | 0 |
| 273 | 1999-08-30 16:54:44 | 16 | 155 | 1 | 273 | 0 |
| 274 | 1999-08-30 17:00:49 | 17 | 21 | 1 | 274 | 0 |
+---------+---------------------+---------------+-------------+----------+----------+--------+
6 rows in set (0.00 sec)
[25 Sep 2004 12:02]
Konstantin Osipov
Yes, having SHOW CREATE TABLE output would be nice. Does it matter if tables are populated with data or not? If yes, could you attach mysqldump output for this data as well?
[25 Sep 2004 12:17]
Nathaniel Blanchard
Yes the table needs to have data in it. The point of the bug is that selecting a long, long, datetime out of the table works fine. Selecting a long, datetime, long out of the table (both using prepared queries mind you), the 2nd long column result will contain garbage because it came after a datetime bind column. I'm working on getting you the SHOW CREATE table output, I wasn't the one to come up with this test case so its taking longer then usual.
[25 Sep 2004 12:28]
Frank Faubert
Here is the Show Create Table output: CREATE TABLE `Bug` ( `VisitID` int(11) NOT NULL default '0', `FirstViewDateTime` datetime NOT NULL default '0000-00-00 00:00:00', `FirstViewHour` int(11) NOT NULL default '0', `VisitLength` int(11) NOT NULL default '0', `Profiled` int(11) NOT NULL default '0', `VisitID2` int(11) NOT NULL default '0', `Spider` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin It won't allow me to attach the mysqldump output, so here it is inline: -- MySQL dump 10.7 -- -- Host: localhost Database: rikishi -- ------------------------------------------------------ -- Server version 4.1.4-gamma-standard-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; -- -- Table structure for table `Bug` -- DROP TABLE IF EXISTS `Bug`; CREATE TABLE `Bug` ( `VisitID` int(11) NOT NULL default '0', `FirstViewDateTime` datetime NOT NULL default '0000-00-00 00:00:00', `FirstViewHour` int(11) NOT NULL default '0', `VisitLength` int(11) NOT NULL default '0', `Profiled` int(11) NOT NULL default '0', `VisitID2` int(11) NOT NULL default '0', `Spider` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin; -- -- Dumping data for table `Bug` -- /*!40000 ALTER TABLE `Bug` DISABLE KEYS */; LOCK TABLES `Bug` WRITE; INSERT INTO `Bug` VALUES (269,'1999-08-30 16:32:12',16,89,1,269,0),(270,'1999-08-30 16:33:01',16,291,1,270,0),(271,'1999-08-30 16:41:06',16,0,1,271,0),(272,'1999-08-30 16:42:35',16,274,1,272,0),(273,'1999-08-30 16:54:44',16,155,1,273,0),(274,'1999-08-30 17:00:49',17,21,1,274,0); UNLOCK TABLES; /*!40000 ALTER TABLE `Bug` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
[25 Sep 2004 14:34]
Nathaniel Blanchard
Just in case it matters, while the show create table output shows the engine to be MyISAM, this also happens with InnoDB.
[15 Oct 2004 0:13]
Konstantin Osipov
Nathaniel, I haven't had time yet to verify this bug, but we recently fixed a very similar one, (Bug#6025), would it be possible to verify that the above problem is not present anymore? (For me it's a couple of hours to build the latest MySQL on AIX and run the test). Thanks!
[15 Oct 2004 1:00]
Nathaniel Blanchard
I'm out of the office until the 19th, but I can try and verify it then
[19 Oct 2004 17:23]
Nathaniel Blanchard
I'm sorry to say this bug is still there in 4.1.6. Just to reiterate, if you take my test case, insert the first record into the Bug table and run the program on an AIX machine, lVisitID will contain 269, and lVisitID2 will contain a wrong number because there was a datetime column bound between them. If you then uncomment/comment portions of the example such that the datetime column is bound last, lVisitID will be 269 and lVisitID2 will also be 269. Somehow the order that the datetime column is bound is corrupting the returned values.
[19 Oct 2004 17:54]
Konstantin Osipov
What you describe is exactly Bug#6025, which didn't get into 4.1.6, but is present in the latest tree.
[19 Oct 2004 17:56]
Nathaniel Blanchard
I'm already in the process of getting the latest bitkeeper tree, I'll let you know what I find out.
[19 Oct 2004 18:47]
Nathaniel Blanchard
Is there a script/way to build a source distribution tarball from a given bk tree? Reason I ask is I have all the tools to download and build on my linux box, but I don't have them all on the AIX box. I know the generic unix source installs that you release work.
[19 Oct 2004 19:21]
Konstantin Osipov
On your linux box: ./BUILD/compile-pentium-debug-max (yes, you need to compile the tree first to get a distribution tarball: some of the files are generated) make dist you'll get mysql-4.1.7-gamma.tar.gz cp it to your aix box, untar, and build from sources.
[20 Oct 2004 13:32]
Nathaniel Blanchard
I've verified that this has been fixed using the latest bitkeeper source
[20 Oct 2004 14:05]
Konstantin Osipov
Thank you! Fixed in 4.1.7
Description: Only on AIX (we've tested this on Windows, Linux, Mac OSX, Solaris sparc, Solaris x86, HP-UX, FreeBSD), execute a prepared query that involves a datetime field. Depending on the position of the datetime field within the resultset, the values you get back will be corrupt. This has been an ongoing issue for us since early 4.1.X developement, but we only just now tracked down the cause of our AIX issues. How to repeat: int main( void ) { MYSQL mysql; long lCount; unsigned long length[10]; my_bool is_null[10]; MYSQL_STMT *stmt; MYSQL_BIND bind[10]; MYSQL_BIND results[10]; MYSQL_TIME udtDateTime; int int_data; #define SELECT_QUERY "SELECT VisitID, FirstViewDateTime, VisitID2 FROM Bug" /* #define SELECT_QUERY "SELECT VisitID, VisitID2, FirstViewDateTime FROM Bug" */ if ( mysql_init(&mysql) == NULL ) { printf( "mysql_init failed\n" ); return 0; } if ( mysql_real_connect(&mysql,"fjf","rikishi","rikishi","rikishi",0,NULL,0) == NULL ) { printf( "mysql_real_connect failed\n" ); return 0; } stmt = mysql_stmt_init(&mysql); if ( stmt == NULL ) { printf( "mysql_stmt_init failed\n" ); return 0; } if ( mysql_stmt_prepare(stmt, SELECT_QUERY, strlen(SELECT_QUERY) ) != 0 ) { printf( "mysql_stmt_prepare failed !%s!\n", mysql_stmt_error(stmt) ); return 0; } long lVisitID; MYSQL_TIME udtFirstViewDateTime; long lVisitID2; results[0].buffer_type = MYSQL_TYPE_LONG; results[0].buffer = (char*)&lVisitID; results[0].is_null = &is_null[0]; results[0].length= &length[0]; results[1].buffer_type = MYSQL_TYPE_DATETIME; results[1].buffer = (char*)&udtFirstViewDateTime; results[1].is_null = &is_null[1]; results[1].length= &length[1]; results[2].buffer_type = MYSQL_TYPE_LONG; results[2].buffer = (char*)&lVisitID2; results[2].is_null = &is_null[2]; results[2].length= &length[2]; /* results[1].buffer_type = MYSQL_TYPE_LONG; results[1].buffer = (char*)&lVisitID2; results[1].is_null = &is_null[1]; results[1].length= &length[1]; results[2].buffer_type = MYSQL_TYPE_DATETIME; results[2].buffer = (char*)&udtFirstViewDateTime; results[2].is_null = &is_null[2]; results[2].length= &length[2]; */ if ( mysql_stmt_bind_result( stmt, results ) != 0 ) { printf( "mysql_stmt_bind_result failed !%s!\n", mysql_stmt_error(stmt) ); return 0; } if ( mysql_stmt_execute(stmt) != 0 ) { printf( "mysql_stmt_execute failed !%s!\n", mysql_stmt_error(stmt) ); return 0; } if ( mysql_stmt_store_result( stmt ) != 0 ) { printf( "mysql_stmt_store_result failed !%s!\n", mysql_stmt_error(stmt) ); return 0; } if ( mysql_stmt_fetch(stmt) != 0 ) { printf( "mysql_stmt_fetch failed !%s!\n", mysql_stmt_error(stmt) ); return 0; } printf( "Got %ld, %ld (%ld, %ld, %ld)\n", lVisitID, lVisitID2, length[0], length[1], length[2] ); }