Cannot ADD INDEX of a column which named 'character' .

[uechoco]

Step to reproduce

My environment:

• MySQL Server version: 5.7.28 Homebrew
• mysqldef v0.5.14

Step1. Create database

CREATE DATABASE IF NOT EXISTS `test01` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Step2. Initialize table

CREATE TABLE `tools` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `character` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_tools_01` (`created_at`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Step3. Write schema.sql file.

CREATE TABLE `tools` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `character` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_tools_01` (`created_at`),
  KEY `index_tools_02` (`name`),
  KEY `index_tools_03` (`character`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

• add index_tools_02 index
• add index_tools_03 index

Step4. Do mysqldef as dry-run. No problems.

$ ./mysqldef -uroot --file=schema.sql --dry-run test01
-- dry run --
ALTER TABLE tools ADD key `index_tools_02`(name);
ALTER TABLE tools ADD key `index_tools_03`(character);

Step5. Do mysqldef as applying, but error occurred.

$ ./mysqldef -uroot --file=schema.sql test01
-- Apply --
ALTER TABLE tools ADD key `index_tools_02`(name);
ALTER TABLE tools ADD key `index_tools_03`(character);
2020/06/15 18:20:56 Error 1064: 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 'character)' at line 1

---

I guess that 'character' is the mysql reserved word, so the column name must surround backquotes like:

ALTER TABLE tools ADD key `index_tools_03`(`character`);

[k0kubun]

Thank you for reporting as always. I fixed it and initiated the v0.5.15 release at this job.

[uechoco]

It worked! Thanks a lot.