Percona Toolkit moved to https://jira.percona.com/projects/PT

pt-online-schema-change fails if sql_mode explicitly or implicitly uses ANSI_QUOTES

Bug #1058285 reported by Brian Fraser
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Medium
Brian Fraser
Percona Toolkit moved to https://jira.percona.com/projects/PT 2.1.6

Bug Description

(This is our internal issue 26211 -- reporting it here so it can be tagged for the next release)

Namely, this: NO_AUTO_VALUE_ON_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

Produces several errors if a table is altered and has foreign keys / doesn't have any data / maybe something else. It can be fixed by calling ->ansi_to_legacy on the output of ->get_create_table, or by making the regexes understand ansi quoting.

Related branches

lp:~percona-toolkit-dev/percona-toolkit/fix-i26211-1058285-821722-implicit-ansi_quotes
Brian Fraser (community): Approve
Daniel Nichter: Approve
Diff: 692 lines (+402/-91)
18 files modified
bin/pt-archiver (+7/-6)
bin/pt-duplicate-key-checker (+7/-6)
bin/pt-find (+7/-6)
bin/pt-heartbeat (+7/-6)
bin/pt-index-usage (+7/-6)
bin/pt-kill (+7/-6)
bin/pt-online-schema-change (+10/-10)
bin/pt-query-advisor (+7/-6)
bin/pt-query-digest (+7/-6)
bin/pt-table-checksum (+7/-6)
bin/pt-table-sync (+7/-6)
bin/pt-table-usage (+7/-6)
bin/pt-upgrade (+7/-6)
lib/TableParser.pm (+13/-8)
t/pt-online-schema-change/ansi_quotes.t (+144/-0)
t/pt-online-schema-change/bugs.t (+0/-1)
t/pt-online-schema-change/samples/issue-26211.sql (+118/-0)
t/pt-online-schema-change/samples/sql-mode-bug-1058285.sql (+33/-0)
Daniel Nichter (daniel-nichter)
tags: added: sql-mode
Daniel Nichter (daniel-nichter)
Changed in percona-toolkit:
importance: Undecided → Medium
Daniel Nichter (daniel-nichter)
tags: added: percona-26211
Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

ANSI_QUOTES, no matter how they're enabled in SQL_MODE, caused pt-online-schema-change to fail on tables with foreign keys because to rebuild the fk constraints we parsed them like m/CONSTRAINT `.../ -- that is, expecting idents to be backtick (`) quoted. But TableParser::get_create_table() was only temporarily removing ANSI_QUOTES, which failed if SQL_MODE had a mode like ORACLE which implicitly enables ANSI_QUOTES. Brian's fix made the sub also remove such modes, but then I altered the code to simply set SQL_MODE='' before doing SHOW CREATE TABLE, then restore the original mode. SQL_MODE='' is MySQL's default, and it's what the sub needs, and the sub only does SHOW CREATE TABLE, so temporarily ignoring all user-set SQL modes in this case should be ok.

Daniel Nichter (daniel-nichter)
Changed in percona-toolkit:
status: In Progress → Fix Committed
Daniel Nichter (daniel-nichter)
summary: - pt-online-schema-change doesn't work with ANSI_QUOTES + some other
- sql_modes
+ pt-online-schema-change fails if sql_mode explicitly or implicitly uses
+ ANSI_QUOTES
Brian Fraser (fraserbn)
Changed in percona-toolkit:
status: Fix Committed → Fix Released
status: Fix Released → New
status: New → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.