pt-table-sync crashes if sql_mode includes ANSI_QUOTES

[Mike Isman]

If I replace the double quotes on line 8736 with single quotes it gets past this.

$ pt-table-sync --dry-run h=s5.leadkarma.com,D=id_prod,t=traffic_sources h=agile-production-db,D=keywords

$ pt-table-sync --version
pt-table-sync 2.1.2

first host:
Server version: 5.5.17-log Distributed by The IUS Community Project

second host:
Server version: 5.1.58-1ubuntu1-log (Ubuntu)

$ pt-table-sync --dry-run h=s5.leadkarma.com,D=id_prod,t=traffic_sources h=agile-production-db,D=keywords
# NOTE: --dry-run does not show if data needs to be synced because it
# does not access, compare or sync data. --dry-run only shows
# the work that would be done.
# Syncing D=keywords,h=agile-production-db,t=traffic_sources in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"log_bin"' at line 1 [for Statement "SHOW VARIABLES LIKE "log_bin""] at line 8736 while doing keywords.traffic_sources on agile-production-db
# 0 0 0 0 0 17:29:19 17:29:20 1 id_prod.traffic_sources

[Daniel Nichter (daniel-nichter)]

Mike, can you confirm that your server is using ANSI_QUOTES in its SQL mode? That would cause this error.

In any case, we have been tracking down and fixing all uses of double quotes like this because they conflict with ANSI quotes. So thanks for reporting this.

[Mike Isman (mfp7)]

The destination host is indeed using ANSI_QUOTES:

source db:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.00 sec)
destination db:

mysql> SELECT @@GLOBAL.sql_mode;
+---------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
+---------------------------------------------------------------------------------------------------+
| ANSI_QUOTES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

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