0

I have production dbs using Mysql 5.5.31-0ubuntu0.12.04-1 (I know its old, will be upgraded soon).

I used a too advanced version of mysql workbench which allowed me to add a comment to key. (Now reverted to WB 6.3 which should be compatible with mysql 5.5)

Now when I 'Alter table' on one of the tables I get Error parsing DDL. When I click it shows the error is in the comment of the key (see below)

 KEY `for cron (product ready)` (`a`,`b`),
  KEY `subproducts` (`a`,`b`,`c`) COMMENT 'find products by the subproducts joiner'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The question is how can I remove the comment of the key without mysql rebuilding the table and without access to the mysql workbench alter table UI. This is an important production server and I would like to avoid alter table which will scan all the table. Thanks

  • The only altering operation which can be applied to an index in ALTER TABLE statement is changing its visibility. So the only way to remove the comment seems to be dropping and re-creating the index. – Akina Jan 22 at 12:05
  • "Visibility" did not exist in 5.5. – Rick James Jan 22 at 16:52
1

There were no "fast" index ALTERs in 5.5. Check out Percona's pt-online-schema-change to drop and re-add the index (without the comment). It should work with only a small downtime.

A newer product is gh-ost, but that depends on the binlogs, and may not work for such an old version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.