MySQL 5.6 RC: further thoughts and questions

November 19, 2012

Here are a few questions I came up with while experimenting with MySQL 5.6.7 & 5.6.8. They are the impressions of a first-time encounter with 5.6, which is a single opportunity for a person to point out the things that strike as odd.

Bugs-wise, just submitted another crashing bug for 5.6.8. I'm just one man, so I extrapolate to realize there is still much work to be done.

The below list does not necessarily make for a bug list; mostly things that puzzle me. I hope it can stir some additional thinking.

  1. Transportable tablespace: what's the difference between FLUSH TABLES my_table WITH READ LOCK and FLUSH TABLES my_table FOR EXPORT? Both create the .cfg file, and both seem to operate just as well. One document says READ LOCK, another says FOR EXPORT.
  2. What's the ALGORITHM=? flag in online ALTER TABLE? Apparently one can write to altered table even on ALGORITHM=COPY. There's not enough documentation to explain.
  3. How come there's not a single example of online InnoDB DDL in official docs?
  4. Why the inconsistency of putting ALGORITHM=..., LOCK=... in between commas, as opposed to other flags/commands not between commas? For example: ALTER TABLE my_table ADD COLUMN i INT, ALGORITHM=COPY, LOCK=SHARED, ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
  5. Why would anyone care about FULLTEXT search word proximity by bytes? Typically, one would want proximity by words. I can find the excuse for proximity by characters. By bytes? A user is not interested in the low level representation of the text!
  6. Could we get a distinct tablespace for the mysql internal InnoDB tables? (I understand there's a separate tablespace for UNDO logs)
  7. Why the need to configure gtid_mode=ON as well as disable-gtid-unsafe-statements so as to enable GTID replication? If only the first is set, an error is produced upon CHANGE MASTER TO MASTER_AUTO_POSITION=1
  8. And when said error is produced, why does it not mention disable-gtid-unsafe-statements, and instead read out a cryptic message? Also note this post by Giuseppe Maxia.
  • Regarding #1, there is functionally no difference, both forms of the syntax call the same underlying code. For the purpose of tablespace backup we will be focusing more on the new syntax as we increase its functionality.

  • Answers to some of the questions:

    1. Use FOR EXPORT, the final syntax for doing it, described at and . The intended differences are described at the second of those. The blog post was work in progress and the note "The syntax for โ€œexportโ€ is not finalised yet and may change; for now the above examples using FLUSH TABLES WITH READ LOCK should work".

    Don't assume FTWRL will work even if it works now. Its main intended role is just to make mysqldump and similar backup methods work, not low level ones like file copying. though it happens that it does allow it for MyISAM tables. For mysqldump and other logical copy methods there's no need to do a full change buffer merge and flush of modified/dirty pages before acting. There is for the InnoDB file copy method an there's a potentially big difference in time taken between the two degrees of flushing.

    2. See and a search for "ALGORITHM=INPLACE" at . Documenting changed things is an ongoing project so if you read them a few weeks ago I wouldn't be surprised if it's improved now.

    3. Examples at .

    6. Would be good to file a bug report for this if you want it done, saying what you are trying to achieve with it. Also good to say why you wouldn't want to move everything else out instead (speed of dropping tables being slower in ibd files, say).

    I'll leave it to others to cover the rest.

    Thanks for the feedback, please keep it up, you and others! ๐Ÿ™‚

    Views are my own, for an official view seek a PR person.

    James Day, MySQL Senior Principal Support Engineer, Oracle

  • Sunny is the developer who did the work, so believe Sunny about what FTWRL does now.

    But it makes FTWRL slower, potentially a lot slower. Flushing say 40,000 changed pages unnecessarily will hurt some users.

    That difference means is that there's likely to be a bug report requesting that we do pay attention to the difference between the two needs. Might even be me who files it, either before or after someone says it's a problem for them.

    All of this might be more than you really wanted to know about the differences and why they can matter. ๐Ÿ™‚

    James Day

  • @James,

    3. No, there are no examples of online DDL in said page; I have already browsed that page.
    There is not a single ALGORITHM= or LOCK= statement. All there is is a setup for "old_alter_table".

  • @James,
    Thanks for all clarifications.

    For question #1: yes, I now see the comment in the blog post. Good!

    For question #2, the documentation has indeed improved. I am still unclear about all combinations (such as: ALGORITHM=COPY --> does that mean LOCK=NONE can't be used? It only says "if supported". Suppurted by what?)

    For #6, I will, but this looks like a long shot.


  • Dmitry Lenev

    Hello Shlomi!


    "Why the inconsistency of putting ALGORITHM=..., LOCK=... in between
    commas, as opposed to other flags/commands not between commas? For example:

    Clauses in ALTER TABLE are normally separated by comma (','). For

    What can be confusing here is that clause that allows to set new options for
    I.e. that we allow use of spaces for separator between options.

    From the view point of ALTER TABLE syntax this is a single clause, which
    consists of several subclauses and which is separated from other clauses,
    like ADD COLUMN..., ALGORITHM=... and LOCK=..., using commas.

    In short, ALGORITHM and LOCK are not similar to table options like ENGINE, but more like ADD COLUMN, ADD KEY clauses.


  • @Dmitry, thanks

  • Thanks for the feedback, Shlomi!

    7. --enforce-gtid-consistency (earlier called --disable-gtid-unsafe-statements) prevents execution of statements that are
    incompatible with GTIDs. --gtid-mode=ON turns on GTIDs. We made two different options, so that you can first check that your application is compatible with GTIDs, and then in a second step you can turn on GTIDs. It is impossible to set --gtid-mode=ON without --enforce-gtid-consistency.

    8. We double-checked this and we do generate the correct error messages. Did you by any chance use --gtid-mode=1 instead of ON? That would explain the message. Please note that gtid-mode is an enumeration with four values, not a boolean, so 1 and ON are not the same. It was wrongly documented as boolean in an early version of the documentation and this has now been fixed.

  • @Lars,

    7 - thank you for the clarifications. So "--enforce-gtid-consistency" stands on its own, so as to validate that my application confirms to possible GTID use; then, when I'm convinced, I turn GTID on -- did I get it right? That makes sense.

    8 - Now in the error log I do get:
    121121 13:00:41 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 requires --disable-gtid-unsafe-statements

    Which is indeed a clear message. I must have missed the update!

  • @shlomi,
    7. Yes, that is the idea.
    8. Great, thanks for re-testing!

  • @Lars,

    Moreover, now I see it is impossible to start MySQL with only gtid_mode=ON, whereus in my previous test it was possible to do so, and error was only reported on CHANGE MASTER TO.
    This is also an improvement and avoids confusion.

  • Calvin Sun

    Hi Shlomi,

    Thanks for the feedback! re #5, agree that it is a design flaw and we intend to fix it.

Powered by Wordpress and MySQL. Theme by