Most tools won't work when sql_mode=ANSI

[Daniel Nichter]

Imported from http://code.google.com/p/maatkit/issues/detail?id=1128

If sql_mode=ANSI then ANSI quotes are enabled:

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier. (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes)

There's a lot of code that uses double quotes. The workaround: --set-vars "SQL_MODE=''"

[Baron Schwartz (baron-xaprb)]

I just committed a bunch of stuff to lp:~percona-toolkit-dev/percona-toolkit/stabilize-test-suite/ that partially addresses this problem. The crown jewel is TableParser.pm, which is ANSI-quote-compatible in revision 275 of that branch.

[Baron Schwartz (baron-xaprb)]

Revision 278 of that branch fixes almost everything.

The remaining bit is that when SQL_MODE=ANSI, we don't get specific options in SHOW CREATE TABLE. These are things like ENGINE=FOO, AUTO_INCREMENT, and so on. A lot of the parsing code doesn't enter TableParser through a single point of entry, and thus these things break. This exposed some other bugs that I fixed where tools didn't handle missing engines, etc.

Ultimately TableParser is going to go away, so I didn't chase down every last thing. But many of the tools will now work where they didn't at all previously.

[Brian Fraser (fraserbn)]

Apparently ANSI, DB2, MAXDB, MSSQL, ORACLE, and POSTGRESQL all implicitly turn on ANSI_QUOTES, so this isn't quite fixed. This just came up on a customer issue as well.

[Michael Coburn (michaelcoburn)]

Sorry meant to say my.cnf is defining:

sql_mode="ORACLE,NO_ENGINE_SUBSTITUTION"

[Brian Fraser (fraserbn)]

Augh apologies, I mistakenly deleted one of Michael's posts.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-839