Bug #28268 commands out of sync, when starting a transaction
Submitted: 6 May 2007 15:58 Modified: 28 May 2013 8:22
Reporter: Louis Breda van Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:Versie 5.0 (at least up to 5/5/2007) OS:Windows (VISTA 64)
Assigned to: CPU Architecture:Any
Tags: ODBC5

[6 May 2007 15:58] Louis Breda van
Description:
Sometimes when I have to to a large number of updates, I build them to a couple of transactions. That in order to force commits every xxxx records.

The code looks about like this:

beginloop
  cn.BeginTrans

  group of updates

  cn.CommitTrans
 
if not ready goto begin loop

with ODBC5 there is an error when reaching the first cn.begintrans the first time "commands out of sync; you can't run this command now.

I assume the problem is related to ODBC

How to repeat:
The code looks about like this:

beginloop
  cn.BeginTrans

  group of updates

  cn.CommitTrans
 
if not ready goto begin loop

with ODBC5 there is an error when reaching the first cn.begintrans the first time "commands out of sync; you can't run this command now

The connection points to MySQL 5117. The VB is VBA access 2003. Used recordsets are ADODB.
[14 May 2007 17:37] Jess Balint
Louis, Can you please attach an ODBC trace log so we can look further into this? Thanks.
[16 May 2007 18:55] Tonci Grgin
So Louis, what a bout logs Jess requested?
[16 May 2007 19:12] Louis Breda van
Tony,

Since the error orrcur at the start of the transaction, this problem is probably simple to log. 

I do not have any expirence using an ODBC-trace, but I will try in the comming days. Do you have a link to a page describing the trace?

Sincerely,

Louis
[17 May 2007 6:13] Tonci Grgin
Louis, no problem. 
On *32 bit* OS you would have to start Control Panel / Administrative tools / ODBC datasources, go to / Tracing / and check Machine wide  tracing ..., click on "Start tracing now". Don't do it on 64bit systems! As our ODBC drivers are 32bit, on x64 machines you have to locate 32bit ODBC datasources administrator in %SystemRoot%\sysWoW64\odbcad32.exe, start it and do the same. You may wish to change where the control panel shortcut points to or add shortcut to odbcad32.exe to your desktop.

As for additional logs, you may start MySQL server with --log or --log-bin (or add "log" to server section in my.ini).
[20 May 2007 17:48] Louis Breda van
Tonci,

During the past few days:
- I did a "forced" install of 5118, working properly now I think
- Installed 5.11 ODBC + latest ODBC-build on this system
- Installed 3.15 ODBC
- made a significant number of changes to my DB, sometimes trying to overcome problems.

With this setup I tryed the transaction problem. What I saw:
- tryed 3.15 did not complain about the transaction, but did fail a few statements later
- tryed 5.11+ did stop on the transaction, with a different error message beeing: "Can not start transaction, because there is more than one transaction in use". 
- I did start an ODBC-trace, but I doubt if it works, it stays empty
- In mysql server settings, warnings are enabled. In the log
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt: missing DBUG_RETURN or DBUG_VOID_RETURN macro in function "?func"
070520 18:48:57 [Note] Event Scheduler: Purging the queue. 0 events
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt: missing DBUG_RETURN or DBUG_VOID_RETURN macro in function "?func"
070520 18:48:57  InnoDB: Starting shutdown...
070520 18:49:01  InnoDB: Shutdown completed; log sequence number 0 383170408
070520 18:49:01 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt: Shutdown complete

070520 18:52:09 [Warning] Server variable data_file_path of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070520 18:52:09 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070520 18:52:09 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070520 18:52:09 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070520 18:52:09 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070520 18:52:10  InnoDB: Started; log sequence number 0 383170408
070520 18:52:10 [Note] Event Scheduler: Loaded 0 events
070520 18:52:10 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt: ready for connections.
Version: '5.1.18-beta-community-nt-debug'  socket: ''  port: 3306  MySQL Community Server (GPL)

For info, the Used Database has the following global setup:
- the maindb (Ms-Access 2003)
- a couple of MsAccess backends on the same machine with linked tables
- one MySQL based backend on the same machine
- the MySQL backend is connected to the maindb via odbc-table links and
via connections used by VBA

IMHO thats all completely valid. I do not see any reasons why an transcation should not be possible if there is more than one connection open, that appart from the question if that is really true. Probably it is true given the table links.

To make thinks worse, when removing the transaction statements, the ODBC-connefction give strange update errors later on. Not sure but it could be related to assigning NULL to a recordset string field.

Sincerely,

Louis
[2 Jun 2007 13:57] Tonci Grgin
Hi Louis. How did you know that Tonci is Slavic for Tony?

Can you make my life easier and attach a standalone (preferably C/C++) test case exhibiting this behavior?

As for logs:
 - Empty SQL trace log means your code did nothing...
 - MySQL log: You have pasted lines from server_name.err file, not the server_name.log file. Please post lines from server_name.log file (usually in datadir).
[2 Jul 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[28 May 2013 8:22] Bogdan Degtyariov
Cannot continue without feedback from the reporter.
The bug is closed.