Option to Perform ALTER ONLINE (or ALGORITHM=INPLACE)

[nunoperalta]

When editing the structure of a table, it would be nice to have an option next to the "Preview SQL" button to try an Online Transaction.

This would perform the ALTER TABLE with either:

ALTER ONLINE or ALGORITHM=INPLACE

Many times, I want to try to see if I can make a change in the database without locking the table. I could simply select this option and try. If it gives an error saying it's not possible to perform online, then I can try again when the traffic is low.

At the moment, the way I do it is by doing "Preview SQL", copy the generated SQL, and add the word "ONLINE" to it, and try the SQL manually.
Having this checkbox would be a nice feature to avoid these extra steps :)

Thank you.

[ibennetch]

Isn’t INPLACE the default, according to https://dev.mysql.com/doc/refman/8.0/en/alter-table.html ? I’m not particularly against implementing this, I just am not sure that it’s needed.

[williamdes]

@ibennetch the PR #15420 adds an option for 'ONLINE' only

[williamdes]

Thank you @kartik1000 for implementing this feature
I added a documentation button

[nunoperalta]

OMG - this!!!
Thank you so much @kartik1000 - you rock.

Just one thing - I believe that ALTER ONLINE is MariaDB specific.
For MySQL, I think we should use "ALGORITHM=INPLACE".

Unless I'm wrong?

[williamdes]

Unless I'm wrong?

I found it in the MySQL docs https://dev.mysql.com/doc/refman/8.0/en/alter-table.html so I do not think it is specific at all :)

[nunoperalta]

I did a search in that page for "ONLINE", but I don't see it as part of the syntax.
Can you please quote the specific part that says "ALTER ONLINE TABLE" is allowed?

Also, I tried in phpMyAdmin Demo with MySQL, I got:

ALTER ONLINE TABLE BanNumber ADD test INT NOT NULL AFTER NbdeBans
MySQL said: Documentation

#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 'ONLINE TABLE BanNumber ADD test INT NOT NULL AFTER NbdeBans' at line 1

[williamdes]

Indeed, I did read the docs too quicky

So for MySQL and MariaDB we need to use ALGORITHM=INPLACE ?

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://mariadb.com/kb/en/innodb-online-ddl-overview/

https://mariadb.com/kb/en/alter-table/#algorithminplace seems to be a more compatible method

[kartik1000]

Does Algorithm = Inplace works for both MariaDB and MySQL? Or we need to use different syntax on the basis of server?

[nunoperalta]

@kartik1000 @williamdes Should work for both.

Thank you!