pt-online-schema-change fails if sql_mode explicitly or implicitly uses ANSI_QUOTES
Bug #1058285 reported by
Brian Fraser
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 |
Bug Description
(This is our internal issue 26211 -- reporting it here so it can be tagged for the next release)
Namely, this: NO_AUTO_
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 modifiedbin/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)
tags: | added: sql-mode |
Changed in percona-toolkit: | |
importance: | Undecided → Medium |
tags: | added: percona-26211 |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
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 |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
status: | Fix Released → New |
status: | New → Fix Released |
To post a comment you must log in.
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.