Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Changing the column combination of the index does not work #53

Closed
uechoco opened this issue Jan 29, 2020 · 2 comments
Closed

Changing the column combination of the index does not work #53

uechoco opened this issue Jan 29, 2020 · 2 comments

Comments

@uechoco
Copy link

uechoco commented Jan 29, 2020

Step to reproduce

My environment:

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

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 NOT NULL,
  `type` int(10) unsigned NOT 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`, `type`)
) 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 NOT NULL,
  `type` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_tools_01` (`created_at`, `updated_at`),
  KEY `index_tools_02` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  • changed index_tools_01 columns
  • changed index_tools_02 columns

Step4. Do mysqldef, but it does not recoginze modified two index

$ ./mysqldef -uroot --file=schema.sql --dry-run test01
-- Nothing is modified --

(Step5. If I change the index name in advance, it worked normally)

  • login mysql
  • use test01 database
  • rename index_tools_01 with index_tools_03
  • rename index_tools_02 with index_tools_04
  • do mysqldef (step4)
$ ./mysqldef -uroot --file=schema.sql --dry-run test01
-- dry run --
ALTER TABLE tools ADD key index_tools_01(created_at, updated_at);
ALTER TABLE tools ADD key index_tools_02(name);
ALTER TABLE tools DROP INDEX index_tools_03;
ALTER TABLE tools DROP INDEX index_tools_04;

Are these also sqldef limitations ?

@k0kubun
Copy link
Collaborator

k0kubun commented Jan 30, 2020

It would be considered as a limitation that sqldef does not generate RENAME INDEX here.

However, generating -- Nothing is modified -- is a bug. I fixed sqldef to detect the change properly in 7e4a2dd and it now generates DROP INDEX + ADD key, while I'm not sure if that's what you really wanted. Please try v0.5.12 which is being released at https://travis-ci.org/k0kubun/sqldef/builds/643693973.

@uechoco
Copy link
Author

uechoco commented Jan 30, 2020

This is what I'd wanted! Thank you as always!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants