Skip to content

# MySQL 5.6 RC: further thoughts and questions

MySQL New Features November 19, 2012November 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
12
Leave a Reply

12 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
6 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Sunny Bains

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.

Guest
James Day

Answers to some of the questions: 1. Use FOR EXPORT, the final syntax for doing it, described at http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html and http://dev.mysql.com/doc/refman/5.6/en/flush.html . 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… Read more »

Guest
James Day

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

Author
shlomi

@James,

Thanks,
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”.

Author
shlomi

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

Cheers

Guest
Dmitry Lenev

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… Read more »

Author
shlomi

@Dmitry, thanks

Guest
Lars Thalmann

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,… Read more »

Author
shlomi

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

Guest
Lars Thalmann

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

Author
shlomi

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

Guest
Calvin Sun

Hi Shlomi,

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

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.