MySQL Bugs: #65340: INSERT execution not inherently commited

Bug #65340	INSERT execution not inherently commited
Submitted:	16 May 2012 19:07	Modified:	29 May 2013 11:43
Reporter:	Rudy Labagnara	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (XP SP3)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
Using Visual Basic 6, when executing an INSERT query like "INSERT INTO TABLEX (Field1, Field2) VALUES ('xyz', 'abc') with the ODBC.EXECUTE command the record is not commited. A second ODBC execution of "Commit" is required for the entry to be made.

When using other databases such as Oracle going back as far as 9i (from my personal experience) I have never had to COMMIT a query when running them from a program built in VB, C++, etc..

How to repeat:
Create an ODBC connection and perform an INSERT query. Then read the table form the database usign WorkBench and the record will not be displayed. 

Then execute a second query to "Commit" the last query. Re-read the table in Workbench and the record will now be displayed.

Suggested fix:
The ODBC query execution should auto commit the INSERT query like other ADBC connectors.

Hi Rudy,

Most probably this behavior with COMMIT is caused by the server settings.
By default Connector/ODBC does not change the AUTOCOMMIT mode.
Please check two things:

 1. autocommit server variable from mysql command line:

    mysql> show variables like "autocommit";

    I guess it will be OFF for you, so the transactions for any connection
    including ODBC are not committed by default.

    You can either change it on the server side by editing my.ini/my.cnf
    MySQL server configuration file or (in case the autocommit is disabled 
    on purpose) put "SET AUTOCOMMIT=1" in the initial statement for ODBC 
    connection in the driver GUI (click Details >> button to show the
    additional options).

 2. The autocommit can be disabled for a specific ODBC DSN by setting
    "SET AUTOCOMMIT=0" in the initial statement (see 1).

Hi Bogdan,

Thank you for the reply. It was my understanding that all ODBC connections by default should be "autocommit". When I use Oracle or SQL Server they are autocommit.. Why is MySQL different that the others?

Rudy

Rudy,

As I have already indicated, neither MySQL Server nor Connector/ODBC disable 
the autocommit. It should be enabled by default just as in any other RDBMS as
MS SQL or Oracle.

If autocommit is not working by default then it was deliberately disabled 
on your side. In my previous message I described where you should look in 
the server and connector settings to find that out.

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".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.