MySQL 5.6 RC: further thoughts and questions

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.

12 thoughts on “MySQL 5.6 RC: further thoughts and questions

  1. Hello Shlomi!

    Regarding:

    “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”

    Clauses in ALTER TABLE are normally separated by comma (‘,’). For
    example, as in “ALTER TABLE t1 ADD COLUMN i INT, ALGORITHM=INPLACE”.

    What can be confusing here is that clause that allows to set new options for
    table can lookslike “ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4”.
    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.

    Regards,
    Dmitry

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

  3. @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!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.