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.
- 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.
- 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.
- How come there’s not a single example of online InnoDB DDL in official docs?
- 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
- 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!
- Could we get a distinct tablespace for the mysql internal InnoDB tables? (I understand there’s a separate tablespace for UNDO logs)
- 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
- 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 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 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 http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html and a search for “ALGORITHM=INPLACE” at http://dev.mysql.com/doc/refman/5.6/en/alter-table.html . 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 http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html .
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,
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”.
@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