Bug #34592 Don't Cache Result does not work with forward cursors and Modify/AddNew
Submitted: 15 Feb 2008 12:13 Modified: 10 Oct 2008 1:24
Reporter: Bogdan Pytlik Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.23,5.1 OS:Microsoft Windows (w2k-SP4)
Assigned to: Jess Balint CPU Architecture:Any
Tags: odbc_postga51

[15 Feb 2008 12:13] Bogdan Pytlik
Description:
 Setup:
    Very large data set (around one million records)
    Moving forward thru all records produces big (order of GB) cache file on %TEMP% dir
    Many active apps/threads moving forward thru big result will fillup free disk space

  Goal: disable cache creation (All scenarios run with
	"Don't Cache Result (forward only cursors)" set in odbc driver options)
        and being able to Add/Update records during forward navigation.
 
 case1: db.open, tab.open(forward), Edit(), Update() -
    Error during Update: Access Violation in MYODBC3.dll (from the stack)

 case2: db.open, tab.open(forward), AddNew(), Update() -
    Error: exception from Update() - Commands out of sync; you can't run this command now

How to repeat:
see attached *.cpp file
create database - see *.sql file
configure odbc DSN system connection (see *.reg.txt)
modify *.cpp for case1/case2 
build project with Visual Studio 6
[15 Feb 2008 12:14] Bogdan Pytlik
sources to reproduce

Attachment: pr3.zip (application/x-zip-compressed, text), 3.59 KiB.

[18 Feb 2008 17:53] Tonci Grgin
Hi Bogdan and thanks for your very interesting report. This has our full attention now.
[18 Feb 2008 18:51] Tonci Grgin
Verified as described by Bogdan with test case attached.
[18 Feb 2008 18:53] Tonci Grgin
DM traces

Attachment: both_case_traces.zip (application/x-zip-compressed, text), 3.43 KiB.

[4 Sep 2008 9:08] Tonci Grgin
Bogdan, according to my research, this should be fixed in http://downloads.mysql.com/snapshots/mysql-connector-odbc-5.1/mysql-connector-odbc-noinsta.... Can you please test it and inform me of results?
[16 Sep 2008 14:50] Bogdan Pytlik
I gave it a quick test with myodbc5.dll but it does NOT work
I see same problems described in initial report

myodbc5.dll  file version 5.1.6.0 from Windows Explorer Properties

md5sum sum  41f9e564703558f2cc55c270ea9b8267 *C:\\WINNT\\system32\\myodbc5.dll

Bogdan
[29 Sep 2008 14:30] Tonci Grgin
Bogdan, rechecking.
[9 Oct 2008 8:36] Bogdan Degtyariov
The error occurs because "Dont cache result" option forces MyODBC driver to use mysql_use_result() function instead of mysql_store_result(). If not all rows are read from the current resultset the stmt->dbc->mysql.status == MYSQL_STATUS_USE_RESULT and MySQL server cannot continue with the next query unless all rows are read or the result set is closed using mysql_free_result(). The command out of sync error is returned, otherwise all unfetched rows will appear in the result set for the next query.

Fix for this problems follows soon.
[9 Oct 2008 8:56] Bogdan Degtyariov
=== modified file 'driver/cursor.c'
--- driver/cursor.c	2008-08-22 22:12:25 +0000
+++ driver/cursor.c	2008-10-09 09:52:32 +0000
@@ -454,6 +454,11 @@
 
     MYLOG_QUERY(stmt, query);
     pthread_mutex_lock(&dbc->lock);
+    /* Fetch all rows if used mysql_use_result */
+    if(stmt->dbc->mysql.status == MYSQL_STATUS_USE_RESULT)
+    {
+         while(mysql_fetch_row(stmt->result));
+    }
     if ( check_if_server_is_alive(dbc) ||
          mysql_real_query(&dbc->mysql, query, len) )
     {
[10 Oct 2008 1:24] Jess Balint
This is not a viable fix as the cursor will be lost. Due to limitations in the MySQL server and network protocol (as explained by Bogdan D.), this will not be fixed.