At work we have a big problem that makes at some degree standard alter table changesets unusable.
The problem is that all alter table MySQL statements made a temporal duplication of the table and when ran in a big table can take an hour for example. This is an inherent problem of MySQL but the big problem we have for liquibase if that if we need to add ten new columns then the time is ten hours.
More people suffer from that lack of ability to group alter table statements? Has never evaluated or studied for newer versions? Or has some workarround that preserves database independency?
A lot of thanks in advance,
If it’s just a few times you need to worry about it, you can always use the tag to hand-craft the SQL you want. That is always a good fallback mechanism.
With liquibase 2.0, we are introducing an extension system, partly to handle cases like this where a database has a particular feature or limitation that needs special handling and there isn’t a cross-database way to deal with it. It would be worth looking at creating a mysql extension that will allow you to do operations like this in a tag and the generated SQL would be as efficient as possible.
I’ve found that it’s often fastest to create a new temporary table with the old table’s structure plus the new and modified columns, do a “select into” from the old table to the new (after disabling foreign key constraints) then drop the original table and rename the temp table to the original table’s name. Still not fast, but often times works the best. You would need to either use the tag to perform some of these steps or create a custom extension to wrap the logic up.
Nathan
Thanks Nathan,
Going down to is an alternative but as we target more databases is not the desirable one.
And about the new temp table we try to avoid it unless it’s a very, very big one.
I’ve practically no experience with other databases. Do another databases have notably penalization for alter table operations that can be avoided is some joining?
I’ve seen that the addColumn allows to use several columns inside the tag. Did you leave an open door to an omptimitzation of that type?
A lot of thanks in advance,
Cross-database support is one of the main advantages of the logical tags rather than sql. Having multiple columns on alterTable does open it up for improving the implementation without affecting the changelogs, but it’s not something I’ve looked into yet. Adding multiple columns at once It may be mysql only feature, although I think mysql is particularly bad performing when it comes to adding columns. Other database types are much faster for those operations so it’s not as much of an issue for them.
Not knowing how many tables you have that are expensive to add columns to, you could just do the route for a few and use the is mysql precondition, then make a regular alert table change with a “not mysql” precondition for all other databases you support.
Nathan
I’d also like to point out that, on many databases, adding “nullable” columns without any default value can be much, much faster than adding “not null” columns. That’s because adding a “not null” column requires re-writing of every row in the table, but adding a “nullable” column just requires an adjustment to the table’s metadata. So, even when adding a “not null” column (or a “nullable” column with a non-null default value) takes hours, adding a similar “nullable” column can be almost instantaneous.
So, if you can tolerate having those new columns nullable, you might give it a try.
Good luck!
