pt-table-checksum reports misleading error if comment has apostroph

[Sveta Smirnova]

If column comment has apostrophe pt-table-checksum fails with error "Table YYY on replica ZZZ is missing these columns: XXX"

How to repeat:

Start replication, for example, from MTR test suite directory

./mtr --suite=rpl --start rpl_alter &
$ mysql -P13002
...
mysql> CHANGE MASTER TO master_host='127.0.0.1', master_port=13001, master_user='root';
Query OK, 0 rows affected, 1 warning (0.53 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

$mysql -P13001
mysql> create database db1;
Query OK, 1 row affected (0.02 sec)

mysql> use db1
Database changed
mysql> create table t3( `id` int(11) NOT NULL AUTO_INCREMENT, `f22aBcDe` int(10) unsigned DEFAULT NULL COMMENT 'xxx`xxx', `f23aBc` int(10) unsigned NOT NULL DEFAULT '255' COMMENT 'yyy', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.46 sec)

mysql> insert into t3 values(1,2,3);
Query OK, 1 row affected (0.06 sec)

$mysql -P13002 db1
mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f22aBcDe` int(10) unsigned DEFAULT NULL COMMENT 'xxx`xxx',
  `f23aBc` int(10) unsigned NOT NULL DEFAULT '255' COMMENT 'yyy',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

$ ~/mysql_packages/percona-toolkit-3.0.4/bin/pt-table-checksum h=127.0.0.1,P=13001,u=root --set-vars innodb_lock_wait_timeout=50 --no-check-binlog-format --ignore-databases mysql --nocheck-replication-filters
08-05T00:17:40 Skipping table db1.t3 because it has problems on these replicas:
Table db1.t3 on replica Thinkie is missing these columns: f23abc
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
08-05T00:17:41 0 0 126 1 0 0.764 mtr.global_suppressions
08-05T00:17:41 0 0 0 1 0 0.603 mtr.test_suppressions
08-05T00:17:42 0 0 6 1 0 0.486 sys.sys_config

Suggested fix: do not fail

[Sveta Smirnova (svetasmirnova)]

Reason for this bug is wrong regular expression in sub parse:

$ddl =~ s/(`[^`]+`)/\L$1/g;

[Sveta Smirnova (svetasmirnova)]

Original DDL before parsing:

CREATE TABLE `t3` (
# `id` int(11) NOT NULL AUTO_INCREMENT,
# `f22aBcDe` int(10) unsigned DEFAULT NULL COMMENT 'xxx`xxx',
# `f23aBc` int(10) unsigned NOT NULL DEFAULT '255' COMMENT 'yyy',
# PRIMARY KEY (`id`)
# ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

DDL after buggy parsing:

CREATE TABLE `t3` (
# `id` int(11) NOT NULL AUTO_INCREMENT,
# `f22abcde` int(10) unsigned DEFAULT NULL COMMENT 'xxx`xxx',
# `f23aBc` int(10) unsigned not null default '255' comment 'yyy',
# primary key (`id`)
# ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Expected behavior:

CREATE TABLE `t3` (
# `id` int(11) NOT NULL AUTO_INCREMENT,
# `f22abcde` int(10) unsigned DEFAULT NULL COMMENT 'xxx`xxx',
# `f23abc` int(10) unsigned NOT NULL DEFAULT '255' COMMENT 'yyy',
# primary key (`id`)
# ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

[Shahriyar Rzayev (rzayev-sehriyar)]

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