Archive for December, 2011

Happy Holidays to the MySQL Ecosystem! :)

Tuesday, December 13th, 2011

Those that know me know I love to send out Christmas/Holiday cards to my colleagues every year. However, with so many colleagues now past and present, and the many friends I’ve made over the years in the MySQL ecosystem, it’s hard to get everyone a card via snail mail (nor do I have everyone’s address), so I wanted to send out a virtual “Happy Holidays!” to all. :)


No, we don’t have children, those gifts are for the family we adopted this year. :D

Wishing you and yours all the best this holiday season! :)

 
 

Dealing with Assertion failure in file fut0lst.ic : addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA

Friday, December 9th, 2011

I recently wrote an article on dealing with an assertion failure in log/log0recv.c, specifically !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table).

I mention it because this occurred after a system outage, and I just encountered another system outage (either HDD power outage or some other serious HDD event), with a completely different assertion failure and error message. Similar to the one above, it’s also kind of obscure, so I wanted to post this so people searching for it will find this.

For reference, the first outage assertion failure was this:

InnoDB: Assertion failure in thread 139838283589376 in file
log/log0recv.c line 1094
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) ==
dict_table_is_comp(index->table)

Here is the new assertion failure:

111201 16:45:00 InnoDB: Assertion failure in thread 4500 in
file fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >=
FIL_PAGE_DATA

As you can see, they are completely different, yet both due to similar, catastrophic events:

In fact, there are a number of relevant/useful snippets from this error log (in case you see one or more of these):

InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Warning: a page in the doublewrite buffer is not within space
InnoDB: bounds; space id 0 page number 2248, page 22 in doublewrite buf.
InnoDB: Warning: a page in the doublewrite buffer is not within space
InnoDB: bounds; space id 0 page number 1775, page 31 in doublewrite buf.
...
111201 16:45:00 InnoDB: ERROR: We were only able to scan the log up to
InnoDB: 670800896, but a checkpoint was at 670801309.
InnoDB: It is possible that the database is now corrupt!
111201 16:45:00 InnoDB: Error: page 7 log sequence number 924466223
InnoDB: is in the future! Current system log sequence number 670801309.
...
111201 16:45:00 InnoDB: Assertion failure in thread 4500 in file
fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset
>= FIL_PAGE_DATA
...
111201 16:45:00 - mysqld got exception 0xc0000005 ;
...
000000013F6A4C6F mysqld.exe!my_osmaperr()
000000013F69B9A0 mysqld.exe!my_osmaperr()
000000013F4727D6 mysqld.exe!?ha_initialize_handlerton@@
YAHPEAUst_plugin_int@@@Z()
000000013F46C172 mysqld.exe!?plugin_lock_by_name@@
YAPEAUst_plugin_int@@PEAVTHD@@PEBUst_mysql_lex_string@@H@Z()
000000013F4713E9 mysqld.exe!?plugin_init@@YAHPEAHPEAPEADH@Z()
000000013F45BDA7 mysqld.exe!handle_shutdown()
000000013F45C91A mysqld.exe!?win_main@@YAHHPEAPEAD@Z()
000000013F45CD90 mysqld.exe!?mysql_service@@YAHPEAX@Z()
000000013F45D0A3 mysqld.exe!?mysqld_main@@YAHHPEAPEAD@Z()
000000013F7FBB27 mysqld.exe!my_mb_ctype_mb()
00000000777F652D kernel32.dll!BaseThreadInitThunk()
0000000077A2C521 ntdll.dll!RtlUserThreadStart()

As you can see, the corruption was severe … so severe that InnoDB wouldn’t even start with innodb_force_recovery set to 6!

As discussed in the previous post, setting innodb_doublewrite=1, innodb_flush_log_at_trx_commit=1, sync_binlog=1, and having a battery backed cache can be the best bets against such issues.

However, I will say that in this case, innodb_flush_log_at_trx_commit=1 and innodb_doublewrite=1 were both set, and binary logging had been disabled, leaving only the battery backed cache to be desired.

Luckily, this was a test environment (and recovery from a backup was possible). Otherwise, using the InnoDB Recovery Tool would be necessary, which can be an undertaking.

Morals of the story: take regular backups (as always) and invest in a battery backed cache. :)

 
 

Glad to See Microsecond Support in both MariaDB and MySQL Now

Wednesday, December 7th, 2011

As you may or may not know, MariaDB has supported microseconds in version 5.3 for several months already.

And now MySQL has added microsecond support in version 5.6 (5.6.4, specifically).

For more details on MariaDB microseconds, please refer to this page:

http://kb.askmonty.org/en/microseconds-in-mariadb

And for more details on MySQL microseconds, see “Fractional Seconds Handling” on the following page:

http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html

From what I can tell, the implementation looks largely to be the same (at least from a user perspective), in that if the fractional part is given, it must be an integer number 0 to 6. And if no precision is given, the default is 0, for backward compatibility.

MySQL 5.6 is still in the “Development” phase, and MariaDB 5.3 is in the “Beta” phase, so I’d expect MariaDB 5.3 to be GA first, but that’s just a guesstimate on my part.

Don’t Miss Tomorrow’s Open Discussion on Using MariaDB

Wednesday, December 7th, 2011

Just a friendly reminder to let everyone know you can still sign up for tomorrow’s Open Discussion on Using MariaDB in commercial database projects provided by Monty Program and SkySQL.

You can register here:

http://www.skysql.com/news-and-events/webinars/mariadb-future-mysql-community-enterprise-open-webinar

When:

Thursday, December 8, 2011

11:00am ET, 8:00am PT, 16:00 UK, 17:00 CET, or 18:00 EET

Agenda & speakers:

Colin Charles, Chief Evangelist at Monty Program
When and why to use MariaDB in a commercial setting

Rasmus Johansson, COO at Monty Program
The offerings of Monty Program Ab and how to interact with us commercially

Kaj Arnö, EVP Products at SkySQL
How SkySQL supports MariaDB users technically and commercially

All – speakers & participants
Open discussion on what is needed to make MariaDB effortless to adopt

Virtual Black Vodka guaranteed! :)

 
 

InnoDB Plugin Version History

Thursday, December 1st, 2011

I’m often wondering what version of the InnoDB Plugin is included with which version of MySQL (or MariaDB). The MySQL changelogs used to denote which version of the InnoDB plugin was included with that particular release of MySQL, but sadly this is no longer the case.

Therefore I’ve compiled a comprehensive list which contains all of this info, and then some (and note all InnoDB Plugin changelog links are provided at the bottom).

Hope you find it helpful :)

MySQL 5.6:

MySQL     Plugin     Status Date
5.6.5 1.2.5 Milestone 8 04/10/2011
5.6.4 1.2.4 Milestone 7 12/20/2011
5.6.3 1.2.3 Milestone 6 10/03/2011
5.6.2 1.2.2 Milestone 5       04/11/2011

MySQL 5.5:

MySQL     Plugin     Status Date
5.5.24 1.1.8 GA 05/07/2012
5.5.23 1.1.8 GA 04/12/2012
5.5.22 1.1.8 GA 03/21/2012
5.5.21 1.1.8 GA 02/17/2012
5.5.20 1.1.8 GA 01/10/2012
5.5.19 1.1.8 GA 12/08/2011
5.5.18 1.1.8 GA 11/16/2011
5.5.17 1.1.8 GA 10/19/2011
5.5.16 1.1.8 GA 09/15/2011
5.5.15 1.1.8 GA 07/28/2011
5.5.14 1.1.8 GA 07/05/2011
5.5.13 1.1.7 GA 05/31/2011
5.5.12 1.1.6 GA 05/05/2011
5.5.11 1.1.6 GA 04/07/2011
5.5.10 1.1.5 GA 03/15/2011
5.5.9 1.1.5 GA 02/07/2011
5.5.8 1.1.4 GA 12/03/2010
5.5.7 1.1.3 GA 10/14/2010
5.5.6 1.1.2 GA 09/13/2010
5.5.5 1.1.1 Early Adopter    06/06/2010 *3
5.5.4 1.1.0 Beta 04/09/2010 *2
5.5.3 1.0.6 RC 03/24/2010
5.5.2 1.0.6 RC 02/12/2010
5.5.1 1.0.6 RC 01/04/2010
5.5.0 1.0.5 RC 12/07/2009 *1

*1: The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB.
*2: InnoDB has been upgraded to version 1.1. This version is considered of Beta quality.
*3: InnoDB has been upgraded to version 1.1.1. This version is considered of “early adopter” quality.

MySQL 5.1:

MySQL     Plugin     Status     Date
5.1.62 1.0.17 GA 03/21/2012
5.1.61 1.0.17 GA 01/10/2012
5.1.60 1.0.17 GA 11/16/2011
5.1.59 1.0.17 GA 09/15/2011
5.1.58 1.0.17 GA 07/05/2011
5.1.57 1.0.16 GA 05/05/2011
5.1.56 1.0.15 GA 03/01/2011
5.1.55 1.0.15 GA 02/07/2011
5.1.54 1.0.14 GA 11/26/2010
5.1.53 1.0.13 GA 11/03/2010
5.1.52 1.0.13 GA 10/11/2010
5.1.51 1.0.12 GA 09/10/2010
5.1.50 1.0.11 GA 08/03/2010
5.1.49 1.0.10 GA 07/09/2010
5.1.48 1.0.9 GA 06/02/2010
5.1.47 1.0.8 GA 05/06/2010
5.1.46 1.0.7 GA 04/06/2010
5.1.45 1.0.6 RC 03/01/2010
5.1.44 1.0.6 RC 02/04/2010
5.1.43 1.0.6 RC 01/15/2010
5.1.42 1.0.6 RC 12/15/2009
5.1.41 1.0.5 RC 11/05/2009 *4
5.1.40 1.0.4 Beta 10/06/2009
5.1.39 1.0.4 Beta 09/04/2009
5.1.38 1.0.4 Beta 09/01/2009 *3
x.x.xx 1.0.3 Alpha 03/11/2009
x.x.xx 1.0.2 Alpha 12/01/2008
x.x.xx 1.0.1 Alpha 05/08/2008 *2
x.x.xx 1.0.0 Alpha 04/15/2008 *1

*1: The initial release of the InnoDB Plugin is based on the built-in InnoDB in MySQL version 5.1.
*2: These 4 versions (1.0.0 – 1.0.3) were only available as separate downloads.
*3: 1st Release of Plugin (see *1* below for more details)
*4: InnoDB Plugin has been upgraded to version 1.0.5. This version is considered of Release Candidate (RC) quality.

MariaDB 5.5:

MariaDB     Plugin     Status    Date            Comments
5.5.24 1.1.8-24.1 GA 05/24/12 XtraDB+
5.5.24 1.1.8 GA 05/24/12 InnoDB
5.5.23 1.1.8-24.1 GA 04/11/12 XtraDB+
5.5.23 1.1.8 GA 04/11/12 InnoDB
5.5.22 1.1.8-24.1 RC 03/29/12 XtraDB+
5.5.22 1.1.8 RC 03/29/12 InnoDB
5.5.21 1.1.8-24.1 Beta 03/16/12 XtraDB+
5.5.21 1.1.8 Beta 03/16/12 InnoDB
5.5.20 1.1.8-24.1     Alpha 02/26/12 XtraDB+
5.5.20 1.1.8 Alpha 02/26/12 InnoDB

MariaDB 5.3:

MariaDB     Plugin     Status    Date            Comments
5.3.7 1.0.17-13.0 GA 05/04/12 XtraDB+
5.3.7 1.0.17 GA 05/04/12 InnoDB
5.3.6 1.0.17-13.0 GA 04/09/12 XtraDB+
5.3.6 1.0.17 GA 04/09/12 InnoDB
5.3.5 1.0.17-13.0 GA 02/29/12 XtraDB+
5.3.5 1.0.17 GA 02/29/12 InnoDB
5.3.4 1.0.17-13.0 GA 02/15/12 XtraDB+
5.3.4 1.0.17 GA 02/15/12 InnoDB
5.3.3 1.0.17-13.0 GA 12/21/11 XtraDB+
5.3.3 1.0.17 GA 12/21/11 InnoDB
5.3.2 1.0.15-12.7     GA 10/14/11 XtraDB+
5.3.2 1.0.16 GA 10/14/11 InnoDB

MariaDB 5.2:

MariaDB     Plugin     Status    Date            Comments
5.2.12 1.0.17-13.0 GA 04/06/12 XtraDB+
5.2.12 1.0.17 GA 04/06/12 InnoDB
5.2.11 1.0.17-13.0 GA 04/02/12 XtraDB+
5.2.11 1.0.17 GA 04/02/12 InnoDB
5.2.10 1.0.17-13.0 GA 12/05/11 XtraDB+
5.2.10 1.0.17 GA 12/05/11 InnoDB
5.2.9 1.0.15-12.7     GA 09/22/11 XtraDB+
5.2.9 1.0.16 GA 09/22/11 InnoDB

MariaDB 5.1:

MariaDB     Plugin     Status    Date            Comments
5.1.62 1.0.17-13.0 GA 04/06/12 XtraDB+
5.1.62 1.0.17 GA 04/06/12 InnoDB
5.1.61 1.0.17-13.0 GA 04/02/12 XtraDB+
5.1.61 1.0.17 GA 04/02/12 InnoDB
5.1.60 1.0.17-13.0 GA 12/05/11 XtraDB+
5.1.60 1.0.17 GA 12/05/11 InnoDB
5.1.55 1.0.15-12.5     GA 03/01/11 XtraDB+ *1
5.1.55 1.0.15 GA 03/01/11 InnoDB

*1: XtraDB+ differs some from XtraDB as it contains even further enhancements on top of XtraDB. If you use the default (i.e., do not enable the InnoDB plugin in the my.cnf file), then you will be using XtraDB+. If you enable the InnoDB Plugin in the my.cnf file, then you are enabling the standard InnoDB Plugin. Please see the following 2 links for more details on these differences:

http://kb.askmonty.org/en/mariadb-53-asynchronous-io-on-windows-with-innodb (work by Wlad)
http://www.facebook.com/note.php?note_id=10150211546215933 (work by KNielsen)

There are even some more optimizer additions, but no graphs yet to report.

 
Changelogs:

Note: The plugin versions had their own changelogs initially, but eventually the changes were just mixed into the general changelogs for the MySQL Server.

Note: Also, some plugin versions span multiple MySQL versions, so those have multiple links below.

Plugin 1.1:

1.1.0	http://dev.mysql.com/doc/innodb/1.1/en/innodb-changes-11.html
1.1.1	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-5.html
1.1.2	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-6.html
1.1.3	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html
1.1.4	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-8.html
1.1.5	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-9.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-10.html
1.1.6	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-11.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-12.html
1.1.7	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-13.html
1.1.8	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-14.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-15.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-16.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-17.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-18.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-19.html

Plugin 1.0:

1.0.0	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-100.html
1.0.1	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-101.html
1.0.2	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-102.html
1.0.3	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-103.html
1.0.4	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-104.html
1.0.5	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-105.html
1.0.6	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-106.html
1.0.7	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-107.html
1.0.8	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-108.html
1.0.9	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-48.html
1.0.10	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-49.html
1.0.11	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-50.html
1.0.12	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-51.html
1.0.13	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-52.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-53.html
1.0.14	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-54.html
1.0.15	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-55.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-56.html
1.0.16	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-57.html
1.0.17	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-58.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-59.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-60.html

Misc. Notes:

5.1.38 is first release of the plugin

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html

And for reference, here is the text from that announcement regarding the InnoDB Plugin:

C.1.1. Changes in MySQL 5.1.38

  As of MySQL 5.1.38, the InnoDB Plugin is included in MySQL
  releases, in addition to the built-in version of InnoDB that
  has been included in previous releases. This version of the
  InnoDB Plugin is 1.0.4 and is considered of Beta quality.

  The InnoDB Plugin offers new features, improved performance
  and scalability, enhanced reliability and new capabilities
  for flexibility and ease of use. Among the features of the
  InnoDB Plugin are "Fast index creation," table and index
  compression, file format management, new INFORMATION_SCHEMA
  tables, capacity tuning, multiple background I/O threads, and
  group commit.

  For information about these features, see the InnoDB Plugin
  Manual at

http://www.innodb.com/products/innodb_plugin/plugin-documentation

  For general information about using InnoDB in MySQL,
  see Section 13.6, "The InnoDB Storage Engine."

  The InnoDB Plugin is included in source and binary
  distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64,
  ia64), and generic Linux RPM packages.

  To use the InnoDB Plugin, you must disable the built-in
  version of InnoDB that is also included and instruct the
  server to use InnoDB Plugin instead. To accomplish this, use
  the following lines in your my.cnf file:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so

  For the plugin-load option, innodb is the name to associate
  with the plugin and ha_innodb_plugin.so is the name of the
  shared object library that contains the plugin code. The
  extension of .so applies for Unix (and similar) systems. For
  HP-UX on HPPA (11.11) or Windows, the extension should be .sl
  or .dll, respectively, rather than .so.

  If the server has problems finding the plugin when it starts
  up, specify the pathname to the plugin directory. For
  example, if plugins are located in the lib/mysql/plugin
  directory under the MySQL installation directory and you have
  installed MySQL at /usr/local/mysql, use these lines in your
  my.cnf file:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so
    plugin_dir=/usr/local/mysql/lib/mysql/plugin

  The previous examples show how to activate the storage engine
  part of InnoDB Plugin, but the plugin also implements several
  InnoDB-related INFORMATION_SCHEMA tables. (For information
  about these tables, see

http://www.innodb.com/doc/innodb_plugin-1.0/innodb-information-schema.html)

  To enable these tables, include additional name=library
  pairs to the plugin-load option:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so
      ;innodb_trx=ha_innodb_plugin.so
      ;innodb_locks=ha_innodb_plugin.so
      ;innodb_cmp=ha_innodb_plugin.so
      ;innodb_cmp_reset=ha_innodb_plugin.so
      ;innodb_cmpmem=ha_innodb_plugin.so
      ;innodb_cmpmem_reset=ha_innodb_plugin.so

  The plugin-load option here is formatted on multiple lines
  for display purposes but should be written in my.cnf using a
  single line without spaces in the option value. On Windows,
  substitute .dll for each instance of the .so extension.

  After the server starts up, verify that InnoDB Plugin has
  been loaded by using the SHOW PLUGINS statement. For example,
  if you have loaded the storage engine and the
  INFORMATION_SCHEMA tables, the output should include lines
  similar to these:

    mysql> SHOW PLUGINS;
    +---------------------+--------+--------------------+---------------------...
    | Name                | Status | Type               | Library             ...
    +---------------------+--------+--------------------+---------------------...
    | InnoDB              | ACTIVE | STORAGE ENGINE     | ha_innodb_plugin.so ...
    | INNODB_TRX          | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_LOCKS        | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMP          | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMP_RESET    | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMPMEM       | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    +---------------------+--------+--------------------+---------------------...

  If you build MySQL from a source distribution, InnoDB Plugin
  is one of the storage engines that is built by default. Build
  MySQL the way you normally do; for example, by using the
  instructions at Section 2.10, "MySQL Installation Using a
  Source Distribution." After the build completes, you should
  find the plugin shared object file under the
  storage/innodb_plugin directory, and make install should
  install it in the plugin directory. Configure MySQL to use
  InnoDB Plugin as described earlier for binary distributions.

Misc Links:

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-introduction.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-5-5.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-installation.html

 
 


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty