Trivial MySQL Variable of the Day

Jun 17, 2009 / By Sheeri Cabral

Tags: ,

Today’s trivial MySQL system variable:

old_alter_table

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).

5 Responses to “Trivial MySQL Variable of the Day”

  • Mark Leith says:

    Hi Sheeri,

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

    if (thd->variables.old_alter_table
    || (table->s->db_type() != create_info->db_type)
    #ifdef WITH_PARTITION_STORAGE_ENGINE
    || partition_changed
    #endif
    )
    need_copy_table= ALTER_TABLE_DATA_CHANGED;
    else
    {
    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,
    &need_copy_table_res,
    &key_info_buffer,
    &index_drop_buffer, &index_drop_count,
    &index_add_buffer, &index_add_count,
    &candidate_key_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 says:

    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.

  • Mark Leith says:

    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.

  • MC Brown says:

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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>