SequelizeMeta is attemping to sync on each migration run

[cusspvz]

It seems that SequelizeMeta syncs between each migration run, is this expected?
IMHO, if there isn't a propose, this is a bottleneck.

logging:

...
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ('20140929124851-hash-security-answers.js');
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta`;
Executing (default): ALTER TABLE `User` ADD `middle_name` VARCHAR(20);
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ('20141014150529-user-middle-name.js');
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta`;
Executing (default): SHOW INDEX FROM `User`
Executing (default): DROP INDEX search ON `User`
Executing (default): CREATE FULLTEXT INDEX `search` ON `User` (`first_name`, `middle_name`, `last_name`, `nick_name`, `full_name`)
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ('20141016122521-index-user-search.js');
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta`;
...

[sdepold]

That is because of https://github.com/sequelize/umzug/blob/master/lib/storages/sequelize.js#L84 Why would you consider that a bottleneck?

[sdepold]

I can see that this is far from ideal. But tbh it has for me ultra high priority.

[cusspvz]

Why would you consider that a bottleneck?

Because you don't need to initialize it and check on each migration run, but i think you already know that. :)

By the way, thanks for changing the table schema, it solved some problems we were getting when merging PRs with different timings. 👍

[rsshilli]

That is because of https://github.com/sequelize/umzug/blob/master/lib/storages/sequelize.js#L84

Unfortunately this link is dead :-(

Why would you consider that a bottleneck?

This seems terribly inefficient. For each migration file (which we have over a hundred), the following 5 extra SQL statements are made (that I believe are unnecessary):

• 2x CREATE TABLE IF NOT EXISTS
• 2x SHOW INDEX FROM `SequelizeMeta`
• 1x SELECT `name` FROM `SequelizeMeta`

Surely nobody is running two migrators against the same DB at the same time. And if they are, I don't see how this would help.

[cusspvz]

Updated link:
https://github.com/sequelize/umzug/blob/67f99bcbee71da0f4a99d56edadc8b59e68bc6fe/src/storages/SequelizeStorage.js#L74-L93

Seems that now it is defined and re-used.
@rsshilli does it still happens?

[rsshilli]

I'm on Umzug 1.11.0 (is that the latest?). I still see all of the SQL I mentioned above.

I worked around this mayhem by creating a mysql dump that I use to initialize new user's DBs. When a new user signs up, I'm reading in the MySQL dump and executing the queries one by one rather than executing the umzug migrations.

A few comparisons:

• 156 migration files runs in ~10.7 seconds on an AWS t2.small RDS MySQL DB.
• My mysql dump file runs in ~3.1 seconds (same SQL but without the extra needless create table, etc above)

I should also note that I (have to) turn off foreign key checks in the mysql dump (although there's no data there). But I suspect that makes the table creation a little faster. I'd be surprised if that made it twice as fast though.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.