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

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

tags: added: sql-mode
Changed in percona-toolkit:
importance: Undecided → Medium
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.

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
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.

Other bug subscribers