Posts Tagged ‘MySQL’

MySQL 5.7.4 Overview and Highlights

Tuesday, April 15th, 2014

MySQL 5.7.4 was recently released (it is the latest MySQL 5.7, and is the “m14″ or “Milestone 14″ release), and is available for download here and here.

The 5.7.4 changelog begins with the following, so I felt it appropriate to include it here as well.

In Memoriam:

“This release is dedicated to the memory of two young engineers of the MySQL Engineering family, Astha and Akhila, whom we lost while they were in their early twenties. This is a small remembrance and a way to recognize your contribution to the 5.7 release. You will be missed.”

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html

As for the fixes, there are quite a few, which is to be expected in such an early milestone release.

The main highlights for me were:

  1. The Performance Schema now instruments prepared statements (for both the binary and text protocols). Info is available in the prepared_statements_instances table, along with performance_schema_max_prepared_statements_instances system variable, and Performance_schema_prepared_statements_lost status variable.
  2. Incompatible Change: MySQL deployments installed using RPM packages now are secure by default (single root account, ‘root’@'localhost’, no anonymous-user accounts, no test database).
  3. Incompatible Change: MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.
  4. Performance; InnoDB: InnoDB now supports multiple page_cleaner threads for flushing dirty pages from buffer pool instances. A new system variable, innodb_page_cleaners, is used to specify the number of page_cleaner threads.
  5. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument
  6. InnoDB: InnoDB now supports the Transportable Tablespace feature for partitioned InnoDB tables and individual InnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances.

Of course, there were many, many more fixes/updates (InnoDB being #1, Replication #2, and Partitioning #3 with most fixed bugs), so be sure to read through the full changelog. And if you are running a previous version of *5.7*, then definitely plan on upgrading to this latest 5.7.4.

Hope this helps.

 

MySQL 5.6.17 Overview and Highlights

Thursday, April 10th, 2014

MySQL 5.6.17 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:

For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:

  1. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
  2. Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES).
  3. InnoDB: MySQL now supports rebuilding regular and partitioned InnoDB tables using online DDL (ALGORITHM=INPLACE) for the following operations: OPTIMIZE TABLE, ALTER TABLE … FORCE, and ALTER TABLE … ENGINE=INNODB
  4. On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.
  5. mysql_install_db provides a more informative diagnostic message when required Perl modules are missing.
  6. The IGNORE clause for ALTER TABLE is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and causes problems with foreign keys (rows removed in the parent table).
  7. Incompatible Change: Old clients (older than MySQL 5.5.7) failed to parse authentication data correctly if the server was started with the –default-authentication-plugin=sha256_password option.

In addition to those, there were 60 other bug fixes:

  • 21 InnoDB
  • 13 Replication
  • 01 Partitioning
  • 25 Miscellaneous

There were 2 regression bugs fixed, so check if they might have affected you, and if so, then you’ll want to consider upgrading (*especially* note the 1st since it’s InnoDB and performance degradation)

  1. InnoDB: A regression introduced by Bug #14329288 (Oracle-internal) would result in a performance degradation when a compressed table does not fit into memory. (Bug #71436)
  2. Building MySQL from source on Windows using Visual Studio 2008 would fail with an identifier not found error due to a regression introduced by the patch for Bug #16249481 and Bug #18057449 (both Oracle-internal bugs).

Conclusions:

  1. If you use AES_ENCRYPT() and AES_DECRYPT(), I’d definitely investigate the changes, and plan for an upgrade (but test/check first and make and necessary changes first).
  2. Since there were 21 InnoDB bugs fixed, including 1 regression bug, I would recommend upgrading if you’re running a previous 5.6 version.
  3. Similarly, since there were 13 replication bugs, if you have a replication setup, I’d also plan to upgrade to take advantage of the fixes (and not be affected by the bugs).

The full changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html

Hope this helps. :)

 

MySQL 5.5.37 Overview and Highlights

Wednesday, April 2nd, 2014

MySQL 5.5.37 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.37

This release, similar to the last 5.5 release, is mostly uneventful.

There was one new feature added (Solaris-specific + obscure), and only 21 bugs fixed.

The new feature is this:

  • On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.

Out of the 21 bugs, most were benign, but there was one definitely worth mentioning (because it is a regression bug with performance degradation):

  • A regression introduced by Bug #14329288 would result in a performance degradation when a compressed table does not fit into memory. (Bug #18124788, Bug #71436)

The bug that causes this regression, bug #14329288 (Oracle-internal), was introduced in 5.5.30, thus versions 5.5.30 through 5.5.36 are affected by this. There are some more details, including some benchmarks on the performance degradation, here.

For reference, the full 5.5.37 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-37.html

Hope this helps.

 

MySQL 5.6.16 Overview and Highlights

Tuesday, February 11th, 2014

MySQL 5.6.16 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:

http://dev.mysql.com/downloads/mysql/5.6.html

As opposed to the latest 5.5 release, this latest 5.6 release has quite a few more bug fixes, but that’s expected since 5.5 has been GA for much longer.

There were 2 minor functionality changes:

  • Previously, ALTER TABLE in MySQL 5.6 could alter a table such that the result had temporal columns in both 5.5 and 5.6 format. Now ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm, so specifying ALGORITHM=INPLACE in these cases results in an error. (Bug #17246318)
  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

I counted 80 bug fixes, 32 of which were InnoDB-related (and some of thoese also memcached-related), 2 partitioning-related, 8 replication-related, and the remaining covered a variety of areas.

I won’t go into all 80 bugs, but if you’re running memcached and/or InnoDB with MySQL 5.6, you should definitely consider upgrading to the latest 5.6.16. And if not, but you’re still running 5.6, I’d recommend reviewing the full changelogs, to see whether you should upgrade asap or not.

Lastly, there was one “known limitation” added, which is:

  • Building MySQL from source on Windows using Visual Studio 2008 fails with an identifier not found error. Later versions of Visual Studio are unaffected. The workaround is to set the CMake build option, INNODB_PAGE_ATOMIC_REF_COUNT, to OFF. This option is ON by default.

So this is very minor, and only related to those building MySQL on Windows using VS 2008. VS 2008 is quite old anyway, but there is the work-around if you’re still using t.

The full changelogs can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-16.html

And in case you missed it, there is a great post from Morgan Tocker giving thanks to each community member involved in each bug reported/fixed in 5.6.16! What a nice combination of both old and new names and folks from all different companies! That sure shows how active and thriving the MySQL ecosystem really is! :)

 

MySQL 5.5.36 Overview and Highlights

Sunday, February 9th, 2014

MySQL 5.5.36 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://dev.mysql.com/downloads/mysql/5.5.html

I was reading through the changelogs to review the changes and fixes, and to summarize, I must say this release is mostly uneventful.

There was one new feature added (for building, so not really applicable to everyone), and only 17 bugs fixed.

The new feature is this:

  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

Out of the 17 bugs, there was only 1 I thought worth mentioning (because it is a wrong results bug):

  • COUNT(DISTINCT) sometimes produced an incorrect result when the last read row contained a NULL value. (Bug #68749, Bug #16539979, Bug #71028, Bug #17867117)

Therefore, if you run COUNT(DISTINCT) and you allow NULL values, then you should consider upgrading so you’re not affected by this.

For reference, the full 5.5.36 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-36.html

Hope this helps.

 

Comprehensive How-To for Enabling the Standard InnoDB Plugin in MariaDB and MySQL

Monday, June 24th, 2013

I’m always switching back-and-forth between the 2 different InnoDB flavors in MariaDB – XtraDB+ and the standard InnoDB plugin, so I thought I’d simply post all of the various combinations in a single place. (And then I cover enabling the InnoDB Plugin in MySQL, since it’s an option in 5.1.) [Addition: Thanks to Andrew and Sergei for the tips on shortening plugin-load=. The changes are reflected below.]

Note: Below is for Windows. For Linux, simply change “.dll” to “.so” where appropriate.

MariaDB 10.0:

Do not add anything, as the standard InnoDB plugin is the current default (as of 10.0.3, although I do anticipate this changing in the near future, and I’ll update the post accordingly when that happens).

MariaDB 5.5:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb.dll

MariaDB 5.3:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

MariaDB 5.2:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

MariaDB 5.1:

# Enable the 2 below to disable XtraDB+ and Enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

Note that enabling it in 5.1, 5.2, and 5.2 are the same. As for 5.5, the only difference is that the name of the .dll has changed from “ha_innodb_plugin.dll” to “ha_innodb.dll” (so that needs changed in each place it occurs).

MySQL 5.1:

For MySQL 5.1, you would enable the InnoDB Plugin in the same way you would for MariaDB 5.1:

# Enable the 2 below to enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

MySQL 5.5+:

In MySQL 5.5+, the InnoDB Plugin is the default (and only InnoDB flavor of InnoDB in MySQL).

And finally, since I’m discussing the InnoDB Plugin, I do have some older posts on the subject for anyone who may be interested:

InnoDB Plugin Versions
http://www.chriscalender.com/?page_id=628

Ease of Switching to the InnoDB Plugin and the Numerous Benefits
http://www.chriscalender.com/?p=99

InnoDB Plugin Version Numbering in MySQL and MariaDB
http://www.chriscalender.com/?p=1205

Hope this helps. :)

 

Awesome to see the MySQL Ecosystem Flourishing

Saturday, March 30th, 2013

I just wanted to take a moment and thank, notice, what ever you want to call it, but share some love with all those and all things MySQL.

I read the post Let’s Celebrate MySQL 5.6 GA! – MySQL Community Reception by Oracle by Oracle MySQL Group and it got me to thinking of how proud I am of (and proud to be a part of) the whole MySQL Ecosystem.

We *should* all celebrate MySQL 5.6 GA! I well remember the 3.22 and 3.23 days, and there were many folks before me already using MySQL!!!

I love to see how it has continued to grow, the ecosystem and all things MySQL, that is. MySQL is better than ever. MariaDB is better than ever. Percona Server is better than ever. You have great Support options with MySQL/Oracle, SkySQL/MariaDB, and Percona as well – not to mention numerous others. I just love the fact that everyone is flourishing, and improving, and the technology continues to advance and most importantly, everyone is working together to provide a great software and service to all MySQL/MariaDB/InnoDB/XtraDB/etc-related users out there.

Not to mention the monitoring softwares, the various plugins and connectors, the numerous storage engines, the number of 3rd party apps & helpful scripts, User Conferences, local meet-ups, books, tutorials, the number of Community bloggers who freely and spiritedly share their knowledge, time, advice, ideas, concerns, and so on. It’s just a great Community to be a part of, and I’m thrilled to see how it’s grown, and continues to grow over the years. And that’s just a testament of the quality of all you great people out there involved and helping. :)

I won’t get too mushy, but it is great to see the MySQL Ecosystem and all of those in it flourish, and may it continue for many, many years to come! :)

Big thanks to everyone out there contributing! “It takes a village, and we are that village”, as my wife always says. :)

 

Tracking down strange “Can’t find file” (ER_FILE_NOT_FOUND) Errors

Saturday, March 9th, 2013

Ever see an error like this in your console or error log, respectively, and wonder where it originated from?

Console (5.5.30):

ERROR 1017 (HY000): Can't find file:
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

Error Log (5.5.30):

130307 23:22:04 [ERROR] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: 
Can't find file: '.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

(Fwiw, they are harmless, unless it is indicative of some sql injection – but you may want to sanitize your data better, unless it’s just a one-time user error. Also, if you’re seeing it from the command line, then you’ll likely realize what you did immediately, and thus no need to ‘figure out’ what happened. However, when you *only* see this entry in the error log, and you want to know how that happened after the fact, then this is more relevant.)

At any rate, after searching the code, one can see that “can’t fild file” can originate from several locations, two such being in ./sql/sql_show.cc and ./sql/sql_rename.cc (the code for SHOW CREATE TABLE and RENAME TABLE, respectively).

So, working backwards, I tried both a SHOW CREATE TABLE and RENAME TABLE (with bogus table names), and was able to reproduce the error (because I was wondering how this ended up in an error log I was analyzing):

mysql> SHOW CREATE TABLE `select * from t1`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

mysql> RENAME TABLE `select * from t1` to `t2`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 2)

Note the first *does* get logged to the error log, whereas the second one does not. I’ve filed a bug report about this behavior:

http://bugs.mysql.com/bug.php?id=68618

The only difference between the two errors are the errno values are different for each (22 vs. 2). For reference, here are the meanings for codes 22 and 2 respectively:

C:\Windows\system32>perror 2
OS error code   2:  No such file or directory
Win32 error code 2: The system cannot find the file specified.

C:\Windows\system32>perror 22
OS error code  22:  Invalid argument
Win32 error code 22: The device does not recognize the command.

I will say that in 5.6, the error message is a bit more descriptive by mentioning the text of the “errno”:

Console (5.6.10):

mysql> show create table `select * from t1`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22 - Invalid argument)
mysql> rename table `select * from t1` to `t2`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 2 - No such file or directory)

Error Log (5.6.10):

2013-03-09 10:33:38 6340 [ERROR]
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld:
Can't find file: '.\test\select@0020@002a@0020from@0020t1.frm'
(errno: 22 - Invalid argument)

Hope this helps. :)

 

SSL-related MySQL Bug #62743 Fixed Recently

Friday, August 17th, 2012

I was very pleased to see that the SSL-related MySQL Bug #62743 was just fixed.

Here is the bug for reference:

http://bugs.mysql.com/bug.php?id=62743

In essence, the –ssl-key value was not validated, thus anything could have been specified there.

Your connection would have still been SSL encrypted, which was good at least, but your SSL Key would not have made any difference.

This fix will be included in versions 5.1.66, 5.5.28, 5.6.7, 5.7.0.

For reference, here is the entry that will go into the changelogs:

“The argument to the –ssl-key option was not verified to exist and be
a valid key. The resulting connection used SSL, but the key was not
used.”

 

With InnoDB’s Transportable Tablespaces, Recovering Data from Stranded .ibd Files is a Thing of the Past

Thursday, April 26th, 2012

Being a data recovery specialist and having recovered countless GBs of corrupted, and/or stranded, InnoDB data in my days, I am very happy to hear about the new InnoDB Transportable Tablespaces coming in MySQL 5.6!

Back in the day, if you had a stranded .ibd file (the individual InnoDB data file with –innodb-file-per-table option), you basically had nothing (even though that file contained all of the data). This was because unless you had the original instance that that particular .ibd file (table) originated from, there was no way to load it, import, or dump from it. So it was not of much use, though all the data was *right* there.

Thus I created the method of Recovering an InnoDB table from only an .ibd file (I should note that this was before the InnoDB Recovery Tool had been released, which can also be used to recover data from a stranded .ibd file too).

However, if you’ve used either my method or the InnoDB Recovery Tool for such a job, it can be a bit of work to get the data dumped. For those experienced, it goes much faster. But still, you cannot get any faster than just being able to (roughly) import the individual tablespace right into any running MySQL 5.6 instance. :)

Nice work! :)

Note: Again, I must mention this is only in MySQL 5.6, so if you have a stranded .ibd file you need to recover data from pre-5.6, you’ll either need to use my method or the InnoDB Recovery Tool.

 


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty