MoveTables fails due to reserved keyword in table column name

tokikanno commented 3 years ago

Overview of the Issue

MoveTables operation will fail if any moved table has a reserved keyword in its column name.

Reproduction Steps

Having a table with the following schema

CREATE TABLE profile (
    `uid` VARCHAR(32) NOT NULL PRIMARY KEY,
    `rank` VARCHAR(64) NOT NULL DEFAULT 'general',  -- rank is a reversed keyword in MySQL8
    `created` TIMESTAMP DEFAULT 0
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin;

insert some random rows and execute MoveTables workflow on it

vtctlclient MoveTables -workflow test-move-profile -tablet_types=master pinkoi default '{"profile": {}}'

It will fail while copy insert into the new KeySpace because the auto-generated insert SQL statement didn't quote the reserved keyword in the column name ( rank ).

Can also see following slack link for more detail: https://vitess.slack.com/archives/C0PQY0PTK/p1615454636258200

Ref: MySQL keyword list https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Binary version

Docker image: vitess/lite:mysql80 00485232f586 (built from Vitess source with git commit d508d333632e59838310af6d356c0f62a0809002)

Operating system and Environment details

OS, Architecture, and any other information you can provide about the environment.

Operating system (output of cat /etc/os-release):

NAME="Ubuntu"
VERSION="18.04.4 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.4 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic

Kernel version (output of uname -sr):

Linux 4.15.0-136-generic

Architecture (output of uname -m):

x86_64

Log Fragments

I0311 17:30:08.623379       1 controller.go:195] trying to find a tablet eligible for vreplication. stream id: 6                                              I0311 17:30:08.625062       1 tablet_picker.go:141] tablet picker found tablet alias:<cell:"pinkoi" uid:90001 > hostname:"vttablet_legacy" port_map:<key:"gr  pc" value:16401 > port_map:<key:"vt" value:15401 > keyspace:"pinkoi" shard:"0" type:MASTER db_name_override:"pinkoi" mysql_hostname:"10.101.1.8" mysql_port:  3306 master_term_start_time:<seconds:1612508784 nanoseconds:760556249 >                                                                                       I0311 17:30:08.635503       1 controller.go:207] found a tablet eligible for vreplication. stream id: 6  tablet: cell:"pinkoi" uid:90001                      I0311 17:30:08.642772       1 vplayer.go:134] Starting VReplication player id: 6, startPos: d3e0c129-5d93-11e8-bfb9-ac1f6b66e37e:1-182, stop: <nil>, filter:   keyspace:"pinkoi" shard:"0" filter:<rules:<match:"profile_test" filter:"select * from profile_test" > >                                                      I0311 17:30:09.642949       1 vcopier.go:199] Copying table profile_test, lastpk: <nil>                                                                       I0311 17:30:09.651254       1 vplayer.go:97] Stop position d3e0c129-5d93-11e8-bfb9-ac1f6b66e37e:1-182 already reached: d3e0c129-5d93-11e8-bfb9-ac1f6b66e37e:  1-182               
E0311 17:30:09.673457       1 dbclient.go:108] ExecuteFetch failed w/ error 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 'rank,email,nick,gmail_without_dot,is_email_permanent_bounced,locale,lang,currenc' at line 1 (errno 10  64) (sqlstate 42000) during query: insert into profile_test

tokikanno commented 3 years ago

Make column names quoted here may solve this problem.

https://github.com/vitessio/vitess/blob/a745fd423846144a26f5f37e73986b4bdc9a6f54/go/vt/vttablet/tabletmanager/vreplication/table_plan_builder.go#L528

tokikanno commented 3 years ago

After some tests, I realized that actually vitess/go/vt/vttablet/tabletmanager/vreplication/table_plan_builder.go will quote reserved keywords properly in generated insert statements.

The root cause of this issue is that rank wasn't in the reserved keyword list, so it won't be quoted properly and cause the move table process to fail.

I've made a simple fix (https://github.com/vitessio/vitess/pull/7944) and tested it with the failed move table case in this issue. It can now run the move table case with no problem.

vitessio / vitess