In keeping with a MySQL tradition, we will be holding a storage engine vendors meeting to coincide with MySQL Central @ Open World this year.
The day and venue is TBD, but will be in the San Francisco/Bay Area week of 29th September – October 3rd.
For an invitation, please send me an email!
Jeremy Cole recently blogged about the feature SET GLOBAL sql_log_bin. In his blog post, Jeremy suggested that there is no strong use-case for this feature, and that it is too easy to run accidentally (i.e. SET GLOBAL instead of SET [SESSION]). The result of this incorrect usage is that slaves will drift out of sync from masters.
We discussed Jeremy’s request in the MySQL team, and we agree that the syntax should produce an error in MySQL 5.7. However, we have not yet determined which steps should be taken for MySQL 5.5 and MySQL 5.6. The two proposals we would like to float are:
Option #1 does not specifically solve Jeremy’s reported issue, because even though the command provides a warning, it will still execute. Thus, the data-drift from accidental usage will still occur; the operator will simply be aware of the problem sooner.
Option #2 does not follow our standard policy regarding functionality changes in GA releases. That is to say that we are discussing making a special exception since this behaviour is regarded as dangerous and almost certainly unintended.
We are seeking input from the community as to which is the better of these two options:
Please leave a comment, or get in touch!
Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.20.
In particular:
In addition, we would like to thank the MySQL Community for their feedback on our earlier proposal to deprecate mysqlhotcopy. Based on your feedback we have decided to officially deprecated mysqlhotcopy in 5.6.20, and it will be removed in MySQL 5.7.
Thank you again to all the community contributors listed above. If I missed a name here, please let me know!
– Morgan
Restarting production database servers with gigabytes of memory is difficult. It can lead to cold caches and other operational complexities.
The InnoDB team is looking to make improvements so that restarting MySQL is required as little as possible. Please help them out by answering which configuration settings you are most eager to see made dynamic.
Thanks!
The frm file has provided long service since the earliest days of MySQL. Now, it is time to replace it with a native InnoDB-based Data Dictionary.
This is a change that has been on our wish list for a long time, as well as others in the MySQL development community:
For historical context:
Every table in MySQL has at least a corresponding .frm file. For example in the case of MyISAM these three files constitute a complete backup:
mytable.MYI mytable.MYD mytable.frm
The .frm file stores information such as column names and data-types. It is a binary format, which as Stewart explains predates MySQL and spans back to 1979 with UNIREG. In addition to the .frm file, there are .trn, .trg and .par files which have been added over time to support triggers and partitioning.
Our motivation to develop a native dictionary spans from a number of issues with the current filesystem-based formats:
The current .frm file predates MySQL’s support for transactions, and has a lot of complexity to handle various failure states in replication and crash recovery. For example: Bug#69444. Using a native data dictionary simplifies code and makes handling failure states very simple.
Our information_schema implementation currently suffers, and has been subject to years of complaints. By using a native dictionary, we will be able implement information_schema as views over real tables, significantly improving the speed of queries.
On a closely related point, we currently build information_schema on top of a series of differing filesystem properties, while attempting to provide the same cross-platform experience. The code to account for filesystem case insensitivity increases code complexity, and ties up developer resources that could be better spent elsewhere.
Aside from the MySQL server’s data dictionary, storage engines may also store their own data dictionary. In the case of the InnoDB storage engine, this redundant storage has led to complexity in troubleshooting an out-of-sync data dictionary.
The current non-comformity of the data dictionary (using .frm, .par, .trn and .trg files) spans from a lack of extensibility from the original .frm format. Not having a centralized extensible repository makes it difficult to incorporate feature requests that require additional meta data stored, or to offer new relational objects in the future.
This change is of course in addition to my recent post about storing system tables in InnoDB.
While this change will be transparent for many of our users, we are inviting feedback from the community. Please let us know:
You can either leave a comment, or email me.
Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.19.
In particular:
In Addition:
Thank you again to all the community contributors listed above. If I missed a name here, please let me know!
Please also note:
There was no MySQL Community Server 5.6.18. That version number was used for an out-of-schedule release of the Enterprise Edition to address the OpenSSL “Heartbleed” issue. This issue did not affect the Community Edition because it uses yaSSL, not OpenSSL, so a new release of the Community Server was not needed, and 5.6.17 is followed by 5.6.19.
– Morgan
In the MySQL team, we are changing the system tables currently located in the mysql schema from MyISAM to InnoDB.
Looking at this historically:
Unlike MyISAM, InnoDB is an ACID compliant storage engine, with the behaviour that once a transaction commits, modifications are able to survive power-loss or other failures. This is a solid foundation to build applications on, since developers will need to handle fewer failures. To use an example:
Without durability, (2) could occur with no record of (1) occurring! Durability is a great feature. However, we do not currently offer this for the system tables which use MyISAM. To use an example:
By switching to InnoDB we are improving the experience of system-related tasks by ensuring durability that ACID provides.
This change will have the effect that InnoDB will be required for all MySQL installations, and the configuration setting --skip-innodb will no longer make sense. Users will still be able to use the MyISAM storage engine, and MyISAM-heavy installations can continue to configure the InnoDB buffer pool to as low as 5MB – taking up very little memory.
This is a great step forward for MySQL, and I am personally very excited to see this change. Many in the MySQL community have been requesting this change for years, and we’re happy to now be working on it. If you have any thoughts, please leave a comment, or email me!
In the MySQL team we are proposing to deprecate the COM_REFRESH packet in favor of specific queries to execute FLUSH commands. To provide a bit of context:
# ./sql/sql_parse.cc:1009 (simplified view)
switch (command) {
case COM_REGISTER_SLAVE:
{
/* do stuff */
break;
}
case COM_QUERY:
{
/* parse query, do stuff */
break;
}
case COM_REFRESH:
{
/* equivalent to running a FLUSH command */
break;
}
case COM_SHUTDOWN:
{
kill_mysql();
break;
}
}
The disadvantage of the binary protocol is that it is less extensible. In the case of COM_REFRESH it was designed to accept only a 1 byte payload to indicate which flush command to run. A quick look at the MySQL manual shows that there are now more than eight (1 byte payload = 8 possible flags) flush commands available. This leads to API inconsistency as some commands are available via the binary interface and others are not.
Our proposed solution to this problem is to deprecate the binary interface for refresh commands and promote usage of the query interface (FLUSH LOGS, FLUSH TABLES, FLUSH HOSTS etc) instead. While we have no immediate plans to remove the COM_REFRESH binary interface (and thus break older clients) we are seeking feedback on the direction of this change. Please leave a comment, or get in touch!
Improving security has been a high priority for MySQL 5.7. To give you an idea of some of the changes to date, I recommend reading Todd Farmer’s blog: Redefining –ssl option, Password expiration policy, AES256 encryption.
In MySQL 5.7, we are also proposing to remove the ‘old’ format for saving passwords. To provide some historical context:
This brings us to today’s discussion. We would like to propose that:
In a future version, support for setting secure-auth=0 + old-passwords=1 will be removed. Since our policy is to support each GA release for 8 years this means that using old-password hashing will already be supported via MySQL 5.6 until 2021. Extending support beyond this leaves us in a less secure position.
We are proposing for removal in MySQL 5.7, but are open to suggestions as to what is an acceptable timeframe.
We value your feedback regarding this proposal:
Please leave a comment, or get in touch!
mysql_install_db is the program that is responsible for bootstrapping a fresh copy of MySQL and making sure all of the system tables are setup correctly. This program is usually invoked by other programs as part of installation, but in the case of .tar.gz downloads will need to be executed manually.
As part of our ongoing efforts to refactor and improve MySQL, we are looking at converting mysql_install_db from its current Perl script to a program written in C++. The aim of this change is to reduce external dependencies (Perl + a set of modules) and improve our cross-platform experience.
We plan for this transition to be mostly function neutral, but are open to feature requests for additional improvements as well.
Have you worked in packaging MySQL?
Do you have any suggestions on how mysql_install_db can be improved?
Please leave a comment, or get in touch!