MySQL Bugs: #58998: Generated SQL scripts should clean up connection settings after themselves

Bug #58998	Generated SQL scripts should clean up connection settings after themselves
Submitted:	17 Dec 2010 12:50	Modified:	8 Mar 2011 11:50
Reporter:	Craig Fowler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S4 (Feature request)
Version:	5.2.31	OS:	Linux (Debian Testing, 64-bit using Ubuntu 10.04 .deb package)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	create.forward-engineer, SQL

Description:
Any generated SQL scripts for import into a database instance (such as the Forward-engineer SQL CREATE script) should clean up any connection-specific settings/variables after themselves.

The specific instance I just tripped over is when creating INSERTs for a table.  The generated script includes SQL along the lines of:

SET AUTOCOMMIT=0;
/* Perform the actual inserts here */
COMMIT;

The problem here being that AUTOCOMMIT isn't being switched back to its previous state after the script finishes executing.  This could (and in my case, did) interfere with other scripts that I was executing immediately afterwards on the same connection - namely, because I didn't include COMMIT commands on the other scripts, they were being rolled back when the connection terminated.

How to repeat:
Create a new Workbench model that includes INSERTs on one or more tables.

Export a SQL CREATE SCRIPT with "Create INSERTs" enabled in the export options.

The AUTOCOMMIT settings tend to be at the bottom of the generated SQL.

Suggested fix:
Always tidy up any global or connection-specific config variables that were set from within a generated SQL script.

It would probably be best to create a variable like @PREVIOUS_AUTOCOMMIT_SETTING before changing it (to hold the current setting of AUTOCOMMIT) and then - at the end of the script set to AUTOCOMMIT back using that variable.

AUTOCOMMIT is one connection-specific configuration that I have tripped over, there might be others that I haven't noticed.

A workaround for the time being would be to either add the restoration of the AUTOCOMMIT setting to the end of the generated SQL script by hand or to ensure that script imports from WB are handled within their own database connection - which means that connection-specific config will not be an issue for subsequently-running SQL queries.

Indeed, the following code is generated:

SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`table1` (`id`, `c1`) VALUES (1, NULL);
INSERT INTO `mydb`.`table1` (`id`, `c1`) VALUES (2, NULL);
INSERT INTO `mydb`.`table1` (`id`, `c1`) VALUES (3, NULL);

COMMIT;

Thus if you'll continue to use the same connection autocommit will remain switched off.

fix confirmed in repository

An entry has been added to the 5.2.32 changelog: 

Generated scripts did not manage connection variables cleanly. For example, 
the state of AUTOCOMMIT was not preserved by the script produced by forward 
engineering a SQL CREATE script. The script switched off AUTOCOMMIT, but did 
not then restore it correctly to its previous state.