Trivial MySQL Variable of the Day

Posted in: Technical Track

Today’s trivial MySQL system variable:


The interesting bit is that this is a system variable, and shows up in SHOW GLOBAL VARIABLES, but is not documented on the Server System Variables manual page.

Instead, it is documented on the manual page for Server options.

Unfortunately, that documentation is very sparse. It tells us:

old-alter-table is an option that can be set in an option file (such as /etc/my.cnf)

old_alter_table is the name of the variable.

And….that’s it. It is neither a system nor status variable, there is no scope, and no entry for whether or not it is dynamic. These last 2 are baffling, though they show up in other variables in the matrix on the “server options” manual page. The scope is either session or global; there is no NULL — the scope may not be *relevant*, but it still exists.

As well, either the variable can be settable on commandline, or not. There is no meaning to a NULL value in that column.

The most egregious issue is that there is no documentation whatsoever about what the variable does. What happens if I set old_alter_table to ON?

(My guess is that it’s a placeholder for the behavior of ALTER TABLE in 5.0 and earlier, perhaps it disables the use of ALTER TABLE ONLINE? My second guess is that whatever the functionality, it is not implemented yet, similar to date_format).

Interested in working with Sheeri? Schedule a tech call.

5 Comments. Leave new

Hi Sheeri,

It’s used to disable the optimizations that were added in 5.1 for “Faster Alter Table” – in ./sql/ down in the depths of mysql_alter_table():

if (thd->variables.old_alter_table
|| (table->s->db_type() != create_info->db_type)
|| partition_changed
need_copy_table= ALTER_TABLE_DATA_CHANGED;
enum_alter_table_change_level need_copy_table_res;
/* Check how much the tables differ. */
if (compare_tables(table, alter_info,
create_info, order_num,
&index_drop_buffer, &index_drop_count,
&index_add_buffer, &index_add_count,
goto err;

if (need_copy_table == ALTER_TABLE_METADATA_ONLY)
need_copy_table= need_copy_table_res;

So – if you have change an ENUM value, or rename a column, or change a column default etc. it will still follow the old 5.0 way of making a temporary table, copy over, and switch etc.

I can’t imagine why anybody would want to use this, other than testing / debugging. :)

Sheeri Cabral
June 17, 2009 5:53 pm

Mark — thanx, so I was half right — my guess on the functionality was right, it makes it so the new online changes are not online, but my guess that it wasn’t implemented yet was wrong.

Still, documentation would be nice.


Well, I also really wouldn’t call it “online alter table” – it’s not “online”, there are still metadata locks taken, etc. – it just happens a lot quicker because of the lack of the table copying.

I’ll point the docs team over here and get it documented better.


Documentation have been alerted to the presence of this item and it will be fixed soon


Leave a Reply

Your email address will not be published. Required fields are marked *