MySQL Bugs: #46185: Reverse engineer from DBMS fails if SQL_MODE set to ANSI

Bug #46185	Reverse engineer from DBMS fails if SQL_MODE set to ANSI_QUOTES
Submitted:	14 Jul 2009 20:42	Modified:	10 Aug 2009 10:45
Reporter:	Dimitriy A	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.1.16	OS:	Windows (XP Pro)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	engineer, reverse, reverse engineer

Description:
When trying to reverse engineer from a database, the log shows errors and nothing is reverse engineered.

How to repeat:
Go to Database -> Reverse Engineer. Follow the steps in the wizard. On reverse engineer step, the log shows errors.

Really, we need to address the case when ANSI_QUOTES is present in SQL_MODE variable. Currently SQL parser is hard-coded to interpret SQL with ANSI_QUOTES disabled. I tend to add ANSI_QUOTES property for whole WB document to determine user preferences in that regard. Such a property would affect all SQL code within the document & would cause WB to set SQL_MODE appropriately for reverse/forward engineering & synchronization. Though not sure whether it will be a final solution.

Currently as a workaround I can only suggest to remove ANSI_QUOTES from SQL_MODE temporarily, just to complete reverse engineering, e.g.:
1) execute from separate connection: SET @DEFAULT_SQL_MODE=@@GLOBAL.SQL_MODE, @@GLOBAL.SQL_MODE='';
2) run reverse engineering from WB
3) revert SQL_MODE to initial value by executing from the same connection (see step 1): execute from separate connection: SET @@GLOBAL.SQL_MODE=@DEFAULT_SQL_MODE;
Note: in this case you will get DDL for parsed objects with single quotes for identifiers & if later you want to forward engineer back into DBMS you'll have to change SQL_MODE same way as you did for reverse engineering.

Added new document property `SqlMode` which defines SQL_MODE for all operations affecting SQL parsing aiming to preserve consistency of SQL statements within WB document.
the new property causes WB to:
1) set `SQL_MODE` DBMS session variable to the value stored in `SqlMode` property of the WB document when doing reverse/forward engineering & synchronization.
2) honor `SQL_MODE` values defined in `SqlModeaffecting when parsing SQL using WB facilities same way as server does.

Note 1: Only a subset of all possible `SQL_MODE` values has affect on WB SQL parser. These values are: ANSI_QUOTES, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_BACKSLASH_ESCAPES, PIPES_AS_CONCAT. Other values don't impact WB SQL parser and are ignored.

Note 2: If value of `SqlMode` is left empty then default value of `SQL_MODE` session variable defined by DBMS will stay unchanged during operations with DBMS. But WB SQL parser will behave as if `SQL_MODE` is really empty. This may potentially lead to inconsistencies in SQL syntax stored in the document.
Hence if you intend to use empty value for `SqlMode` property, ensure that default `SQL_MODE` variable defined by server doesn't contain any values from those mentioned in Note 1.

Note 3: `SqlMode` property is defined in 2 places: globally and at document scope. See `\wb\options\options` and `\wb\doc\customData` GRT Tree branches correspondingly. Every document upon its creation copies value of global property into property defined for the document. Thus the property value defined at document scope always has higher priority over one defined globally.

new property has been added and can be set in global preferences or model options dialog.

Main documentation has been updated as requested, describing the new SqlMode property.

Added an entry to the 5.1.17 changelog:

Reverse Engineering from a live database failed if the server had SQL_MODE set to ANSI_QUOTES.