try to auto-detect "ansi quotes" mode on mysql connections

[jek]

Ideally, the 'use_ansiquotes=True' flag for the mysql dialect would be sniffed out at connection time. Generally speaking, users probably do not know if the setting is in effect on their database.

[Former user]

According to

4 docs http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html

and 5 docs http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#id1016449

we can tell if ansi_quotes is needed if the sql_mode var contains 'ANSI_QUOTES'.

Someone who had this issue (that was resolved by ansi_quotes=True) gave me the following output for their sql_mode:

(we can do show variable or get it via a select):

mysql> show variables like 'sql_mode';
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                                                                                                                                           |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> select @@global.sql_mode;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                                                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

It can be set per session, so the

'SELECT @@session.sql_mode;'

should always give us the right value we want (I think @@session is fine for mysql 4.0.x):

the accompaying python would of course just be:

'ANSI_QUOTES' in sql_mode_value.split(',')

• [e-mail]

[jek]

Some auto-detection is now in 96549e6b8fd353a82a77938a754bd168a924385b. This is an 80% solution- the detection triggers during reflection operations. A manual setting is still needed if no reflection is done.

The dialect needs a hook run on first pool connect to detect this most of the time, and a refactor with Dialect-per-Connection to get it right all of the time. (It's a connection-session scoped setting with dialect-modifying behavior)

[Mike Bayer]

things like server modes and such only need be detected on connect, we don't support changing of SQL modes and other global flags on an already in-use dialect. the use_ansiquotes option itself was effectively removed in 0.6, it just never got documented correctly. Some very old documentation has been fixed via #1552.