| Bug #29239 | ODBC prepared statement with bound columns and no parameters returns bad results | ||
|---|---|---|---|
| Submitted: | 20 Jun 2007 13:57 | Modified: | 3 Dec 2007 7:28 |
| Reporter: | Makoto Hamanaka | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.16, 3.51 & 5.1 latest | OS: | Windows (XP) |
| Assigned to: | Jess Balint | CPU Architecture: | Any |
[20 Jun 2007 21:05]
Tonci Grgin
Hi Makoto and thanks for your report. I would be most interesting in part of server general query log from the moment you connect till the test ends. Please attach my.ini/cnf file used for server too.
[20 Jun 2007 21:53]
Makoto Hamanaka
Hi Tonci, thanks for your reply. This is from Query log. 070621 6:45:03 10 Connect root@localhost on test 10 Query SET SQL_AUTO_IS_NULL=0 10 Query SELECT col1 FROM table_bug_test 10 Query INSERT INTO table_bug_test(col1) VALUE(12345) 10 Query SELECT col1 FROM table_bug_test So the order is SELECT -> INSERT -> SELECT. It should be INSERT -> SELECT -> SELECT, as i think.
[22 Jun 2007 7:00]
Tonci Grgin
Makoto, thanks for additional info provided. > This is from Query log. > 070621 6:45:03 10 Connect root@localhost on test > 10 Query SET SQL_AUTO_IS_NULL=0 > 10 Query SELECT col1 FROM table_bug_test > 10 Query INSERT INTO table_bug_test(col1) VALUE(12345) > 10 Query SELECT col1 FROM table_bug_test >So the order is SELECT -> INSERT -> SELECT. It should be INSERT -> SELECT -> SELECT, as i think. I think the order is just fine as connector need some information before it begins working on table. What I want to know is this (as I presume you are not committing changes before first read): mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) As I don't see COMMIT in your log (after insert) and presuming tx_isolation is set to READ-COMMITTED, for example, I would call this expected behavior. Please see http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html: "In consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 14.2.10.4, “Consistent Non-Locking Read”." Just to be on the safe side, can you repeat this behavior with MyISAM tables too?
[22 Jun 2007 9:36]
Makoto Hamanaka
Hi Tonci, thanks for your response. I did't explicitly COMMIT because the default behavior will be auto-commit mode, so every SQL statement will be treated as an transaction. Changing the storage engine to MyISAM, as you suggested, did not affect the (strange) behavior, as below. here's from the MySQL Server Command Line: ------------------------------------------------- Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 38 Server version: 5.0.41-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) ------------------------------------------------- I changed the storage engine of table 'table_bug_test' to MyISAM. And got the same result: ------------------------------------------------- first execute: no data (this is bug). first try end. second execute: data exists. data=12345 second try end. test_odbc_bug() end. hit any key ------------------------------------------------- from my MySQL Server query log: ------------------------------------------------- 070622 17:46:55 47 Connect root@localhost on test 47 Query SET SQL_AUTO_IS_NULL=0 47 Query SELECT col1 FROM table_bug_test 47 Query INSERT INTO table_bug_test(col1) VALUE(12345) 47 Query SELECT col1 FROM table_bug_test ------------------------------------------------- and from the Connector/ODBC traced query log: ------------------------------------------------- -- Query logging -- -- Driver name: MySQL ODBC 3.51 Driver Version: 03.51.16 -- Timestamp: 070622 17:46:55 SELECT col1 FROM table_bug_test; INSERT INTO table_bug_test(col1) VALUE(12345); SELECT col1 FROM table_bug_test; -------------------------------------------------
[22 Jun 2007 9:54]
Tonci Grgin
Thanks Makoto, trying to reproduce with info provided.
[22 Jun 2007 11:30]
Tonci Grgin
Verified as described with test case provided.
[11 Jul 2007 1:53]
Jess Balint
The call to SQLBindCol() is triggering the statement to be executed. Around line 991 of driver/results.c in SQLBindCol(), check_result() is called which executes the statement:
if ( (error= check_result(stmt)) != SQL_SUCCESS )
MYODBCDbgReturnReturn(error);
[20 Nov 2007 23:30]
Jess Balint
Fix committed in rev884 and will be released in 5.1.1. Will not be fixed in 3.51 branch.
[3 Dec 2007 7:28]
MC Brown
A note has been added to the 5.1.1 changelog: Using an ODBC prepared statement with bound columns would produce an empty result set when called immediately after inserting a row into a table.
Description: just after inserting a record to an empty table, prepared statement 'SELECT * FROM table' with bound columns, without any parameters, will return zero rows, on the first execution. It shouldn't be zero rows, because I just inserted a row! also, if the prepared statement is 'SELECT count(*) FROM table', the fetched value is zero. on the first execution. It should be 1. after the first buggy execution, the second, third execution is always ok. if the PS has no bound columns, it is ok. (no bug) if the PS has at least one parameters, it is ok. Environment: MySQL Connector/ODBC 3.51.16 MySQL 5.0.41 community-nt-log WindowsXP Pro Thanks in advance. How to repeat: #define check_ret(ret, text) if ((ret) != SQL_SUCCESS) {\ printf("[%s] ret:%d\n",text, ret);\ goto error;\ } void test_odbc_bug() { SQLHENV env; HSTMT stmt; HSTMT stmt_ins; SQLHDBC dbc; std::string conn_str = "DSN=;UID=;PWD=;DATABASE=;"; SQLCHAR out[1024]; check_ret(SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,(SQLHANDLE*)&env), "SQLAllocHandle env"); check_ret(SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0), "SQLSetEnvAttr"); check_ret(SQLAllocHandle(SQL_HANDLE_DBC,env,(SQLHANDLE*)&dbc), "SQLAllocHandle dbc"); check_ret(SQLDriverConnect(dbc, 0,(SQLCHAR*)conn_str.c_str(), (int)conn_str.length(), out, 1024, 0, SQL_DRIVER_NOPROMPT), "SQLDriverConnect"); check_ret(SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt), "SQLAllocHandle stmt"); check_ret(SQLPrepare(stmt, (SQLCHAR*)"SELECT col1 FROM table_bug_test", SQL_NTS), "SQLPrepare"); // no parameter unsigned int buf = 0; SQLINTEGER len_ret = 0; check_ret(SQLBindCol(stmt, 1, SQL_C_ULONG, &buf, sizeof(unsigned int), &len_ret), "SQLBindCol"); // insert a record check_ret(SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt_ins), "SQLAllocHandle stmt_ins"); check_ret(SQLExecDirect(stmt_ins, (SQLCHAR*)"INSERT INTO table_bug_test(col1) VALUE(12345)", SQL_NTS), "SQLExecDirect INSERT"); SQLFreeHandle(SQL_HANDLE_STMT, stmt_ins); // Let's fetch rows printf("first execute: "); RETCODE ret; check_ret(SQLExecute(stmt), "SQLExecute"); ret = SQLFetch(stmt); if (ret == SQL_NO_DATA) { printf("no data (this is bug).\n"); // <---- this will happen } else { printf("data exists. data=%d\n",buf); } printf("first try end.\n"); SQLFreeStmt(stmt, SQL_CLOSE); printf("second execute: "); check_ret(SQLExecute(stmt), "SQLExecute"); ret = SQLFetch(stmt); if (ret == SQL_NO_DATA) { printf("no data (this is bug).\n"); } else { printf("data exists. data=%d\n",buf); // <---- it will come here second time (it is ok) } printf("second try end.\n"); SQLFreeStmt(stmt, SQL_CLOSE); printf("test_odbc_bug() end. hit any key\n"); getchar(); return; error: printf("odbc error\n"); } 1. create table CREATE TABLE `test`.`table_bug_test` ( `col1` INTEGER UNSIGNED NOT NULL, ) ENGINE = InnoDB; 2. make table empty DELETE FROM table_bug_test; 3. run the program. 4. you'll get this result. ------- buggy result --------------- first execute: no data (this is bug). first try end. second execute: data exists. data=12345 second try end. test_odbc_bug() end. hit any key -------------------------------------------- the result should be like this. ------- desirable result --------------- first execute: data exists. data=12345 first try end. second execute: data exists. data=12345 second try end. test_odbc_bug() end. hit any key -------------------------------------------- Suggested fix: one workaround is just do one SQLExecute() after SQLBindCol(). I don't know why this happens but maybe problem is on the ODBC driver, because I guess prepared statements are emulated on client-side when using MySQL Connector/ODBC.