Chris on MySQL

Syndicate content
Tips and Solutions for MySQL®, MariaDB®, and other MySQL-related Topics
Updated: 3 min 11 sec ago

WebScaleSQL on Windows? I wish, but not quite yet, it seems …

April 17, 2014 - 8:49pm GMT

For fun, I tried building WebScaleSQL on Windows, even though it’s not [yet?] a support platform.

Using the following (as I would to build MySQL/MariaDB):

cd c:\mysql\webscalesql-5.6.16
mkdir bld
cd bld
cmake ..
cmake --build . --config relwithdebinfo --target package

I end up with:

...
    238 Warning(s)
    110 Error(s)
Time Elapsed 00:05:08.53

Looking through the output, the main error is this:

C:\mysql\webscalesql-5.6.16\include\atomic_stat.h(33):
fatal error C1083: Cannot open include file: 'atomic':
No such file or directory
[C:\mysql\webscalesql-5.6.16\bld\storage\innobase\innobase.vcxproj]

Of course the directory does exist, and permissions are correct.

C:\mysql\webscalesql-5.6.16\include\atomic_stat.h contains the following line:

#include <atomic>

And this exists:

C:\mysql\webscalesql-5.6.16\include\atomic

So there must be some reason Windows doesn’t like that include. As far as I can tell, most other includes specify an exact file, and not a directory.

I tried replacing the single include by specifying each .h file included in atomic\, but that resulted in many more errors (776).

If any ideas, please feel free to share. And/or if this is futile, fine to mention that also.

 

Resolving Error 1918, System Error Code 126, When Installing MySQL ODBC Driver

April 17, 2014 - 8:32pm GMT

If you are installing MySQL ODBC Driver and encounter the following error:

Error 1918. Error installing ODBC driver MySQL ODBC 5.1 Driver,
ODBC error 13: The setup routines for the MySQL ODBC 5.1 Driver
could not be loaded due to system error code 126:
The specified module could not be found.
...\myodbc5S.dll).. Verify...

Then you will need to install the Microsoft Visual C++ 2010 Redistributable Package (select the appropriate one for your OS architecture below):

64-bit version:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

32-bit version:

http://www.microsoft.com/en-gb/download/details.aspx?id=5555

After installing that, then re-attempt installing the MySQL ODBC connector, and things should work smoothly.

Hope this helps.

 

Resolving MySQL ODBC “architecture mismatch” Error

April 17, 2014 - 7:12pm GMT

If you attempt to use ODBC to run a MySQL application and run into the following error:

[Microsoft][ODBC Driver Manager] The specified DSN contains an
architecture mismatch between the Driver and Application

This means there is a 64-bit versus 32-bit mismatch.

Most likely, you’re running 64-bit Windows, as well as 64-bit MySQL ODBC connector, but the application is 32-bit.

If this is the case, you will also need to install the 32-bit MySQL ODBC connector, and then create the connection from the 32-bit ODBC.

odbcad32.exe is the file to create the connections. Both 64-bit and 32-bit files have the same name, just differing locations.

This is the default location for the 64-bit ODBC:

C:\Windows\System32\odbcad32.exe

This is the default location for the 32-bit ODBC:

C:\Windows\SysWOW64\odbcad32.exe

And should you need to install MySQL ODBC, there are good instructions here (both for MSI and Manual installs).

Hope this helps.

 

Looking for Slave Consistency: Say Yes to –read-only and No to SUPER and –slave-skip-errors

April 16, 2014 - 7:04pm GMT

The biggest concern with a slave is to ensure your data is consistent with the master! End of story!

3 of the biggest things I see when dealing with out-of-sync slaves:

  1. Many users do not use the --read-only option on their slaves.
  2. Some of those who do often have numerous users with SUPER who can still perform writes.
  3. Many users simply use --slave-skip-errors=… to avoid common errors.

Of course, if you have a slave, definitely use the --read-only option.

However, SUPER users can still write on slaves with --read-only, so blindly granting SUPER to all users just to save a little time when creating users won’t help. I’d suggest to use SUPER as sparingly as possible (not to mention it’s good for security also).

And the use of --slave-skip-errors=… is generally just a quick fix to avoid errors on the slave, but a sure fire way to end up with inconsistent data. Please don’t just use this blindly, but rather figure out why you are receiving such errors and resolve them the correct way. (We’re happy to assist if you need help with this.)

In conclusion, if the consistency of the data on your slave matters to you/your business/your customers, and/or if you’ve already had problems with inconsistent data, then add --read-only, remove --slave-skip-errors=…, and remove SUPER from as many users that use the slave as possible. That will be a terrific start.

If you are really serious about having a “read only” slave, and you use InnoDB, then as of MySQL 5.6 and MariaDB 10.0, there is also the innodb_read_only option, which has the following effect:

  • No user will be able to modify. Only DCL (like create user/grant/revoke etc) commands will work for the root user.

Note that when both --read-only and --innodb-read-only are given, then --innodb-read-only takes effect.

There is more information on this variable, and how to set up read only InnoDB on the following two pages:

https://blogs.oracle.com/MySQL/entry/innodb_read_only_mode
http://dev.mysql.com/../innodb-read-only-instance.html

Along this same line, and in addition to InnoDB, MariaDB 10.0 also has a spider_read_only_mode option which will make spider tables read only as well.

Hope this helps.

 

MySQL 5.7.4 Overview and Highlights

April 15, 2014 - 2:47pm GMT

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

April 11, 2014 - 12:01am GMT

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.

 

MariaDB 10.0.10 Overview and Highlights

April 8, 2014 - 9:17pm GMT

MariaDB 10.0.10 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.10/

This is the first GA (“Generally Availability“, aka “recommended for production systems”) release of MariaDB 10.0, and 11th overall release of MariaDB 10.0.

Since this is the initial 10.0 GA release, this is primarily a bug-fix and polishing release.

Here are the main items of note:

  1. The Audit Plugin is now included in MariaDB (MDEV-5584)
  2. Improved XtraDB performance by fixing incorrect calculation of flushed pages (MDEV-5949)
  3. Fix for GTID duplicate key multi-master corruption bug (MDEV-5804)
  4. Default TokuDB compression is now TOKUDB_ZLIB (instead of TOKUDB_UNCOMPRESSED)
  5. Various algorithm improvements for engine-independent table statistics (EITS) (MDEV-5901, MDEV-5917, MDEV-5950, MDEV-5962, MDEV-5926)

You can read more about the 10.0.10 release here:

https://mariadb.com/kb/en/mariadb-10010-release-notes/

And if interested, you can review the full list of changes in 10.0.10 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10010-changelog/

Hope this helps.

 

MySQL 5.5.37 Overview and Highlights

April 3, 2014 - 2:20am GMT

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.

 

Improve your Stored Procedure Error Handling with GET DIAGNOSTICS

April 2, 2014 - 11:37pm GMT

In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5.

However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well.

RESIGNAL just outputs the error, as it comes from the server, for instance:

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

You may not want the error just written to the console, or perhaps you want to at least control how it is written.

It’s common to see exit handler code in the following form:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

Where the SELECT outputs something not very useful in many cases.

With GET DIAGNOSTICS, you can get all of the error information, and you should, if not already.

If you were going to use GET DIAGNOSTICS from the command line, you could use something like this (immediately following your query):

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;

However, since we want the information from within the stored procedure, we must put this within the exit handler code. So the above exit handler code, now becomes (and I added 1 more line for formatting):

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
END;

Which results in:

mysql> call my_proc('abc');
+--------------------------------------------------+
| @full_error                                      |
+--------------------------------------------------+
| ERROR 1146 (42S02): Table 'db1.t1' doesn't exist |
+--------------------------------------------------+
1 row in set (0.01 sec)

For more details on GET DIAGNOSTICS, I would recommend the following 2 pages:

https://mariadb.com/kb/en/get-diagnostics/
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

Hope this helps.

 

Quickly Debugging Stored Procedures, Functions, Triggers, and Events with RESIGNAL

April 2, 2014 - 10:59pm GMT

I was recently debugging a stored procedure and could not easily identify the underlying reason for why it was failing.

It had a standard exit handler catch-all for SQLEXCEPTION, which was:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

When there was an error, it didn’t really output anything useful.

As of MySQL 5.5, there is RESIGNAL:

“RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event.”

http://dev.mysql.com/doc/refman/5.5/en/resignal.html

There is also some good information about it here as well:

https://mariadb.com/kb/en/resignal/

It is very simple to use, just add it (though it is much more robust that just this – see above link):

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
RESIGNAL;
END;

By just adding RESIGNAL, in addition to what was output by the SELECT, we now see the actual error printed immediately after, for instance:

mysql> call my_proc('abc');
+---------+---------+-------+
| STATUS  | Records | Query |
+---------+---------+-------+
| ....... | NULL    | NULL  |
+---------+---------+-------+
1 row in set (0.01 sec)

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

Notice the last line is what you are looking for (when troubleshooting).

As I mentioned, this is available as of MySQL and MariaDB 5.5.

If you are using MySQL 5.6 or MariaDB 10.0, I would recommend skipping RESIGNAL and using GET DIAGNOSTICS within your exit handler to obtain the exact error code, SQL state, and error message *all* of the time. I cover using GET DIAGNOSTICS here, if interested.

Hope this helps.

 

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN

March 26, 2014 - 8:19pm GMT

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

The query is:

SELECT id1 FROM t1;

This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

mysql> EXPLAIN SELECT id1 FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index

EXPLAIN returns NULL for “possible_keys”. This means there was no relevant index (though we see one was used at some point):

“It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.”

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

So “possible_keys” returns NULL, but key returns `id2`.

Per the above, that means `id2` is a covering index.

This part is a bit strange, as it is not readily intuitive why this is the case.

I mean, the query asks for “id1″, yet EXPLAIN says “id2″ is the covering index.

How is this possible and why isn’t just the Primary Key chosen anyway?

This is possible because of the way InnoDB’s secondary indexes are stored. In InnoDB, secondary indexes are stored along with their corresponding primary key values, so by looking at the secondary index, you can see the primary key values.

As for why this is chosen instead of the primary key, it took some digging, but I found the exact comments as to why in the source code (sql/sql_select.cc) in the find_shortest_key() function:

"As far as
1) clustered primary key entry data set is a set of all record
	fields (key fields and not key fields) and
2) secondary index entry data is a union of its key fields and
	primary key fields (at least InnoDB and its derivatives don't
	duplicate primary key fields there, even if the primary and
	the secondary keys have a common subset of key fields),
then secondary index entry data is always a subset of primary key entry.
Unfortunately, key_info[nr].key_length doesn't show the length
of key/pointer pair but a sum of key field lengths only, thus
we can't estimate index IO volume comparing only this key_length
value of secondary keys and clustered PK.
So, try secondary keys first, and choose PK only if there are no
usable secondary covering keys or found best secondary key include
all table fields (i.e. same as PK):"

So since secondary index entry data is always a subset of primary key entry, scanning the secondary index should generate slightly less IO than scanning the primary key.

Fwiw, this also explains why key_len is less if you specify “USE INDEX (Primary)” to force it to use the PK over the secondary index:

mysql> EXPLAIN SELECT id1 FROM t1 USE INDEX (Primary)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index

Note this reports key_len of 4, whereas the former reported 5.

Hope this helps.

 

MariaDB 10.0.9 Overview and Highlights

March 14, 2014 - 11:22pm GMT

MariaDB 10.0.9 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.9/

This is the second RC (“Release Candidate”) release of MariaDB 10.0, and 10th overall release of 10.0. All features planned for MariaDB 10.0 GA are included in this release.

There were 6 notable changes in MariaDB 10.0.9:

  1. InnoDB upgraded to version 5.6.15
  2. Extended-keys optimization is now enabled by default.
  3. MariaDB can be compiled to use the system’s PCRE library.
  4. Added MASTER_GTID_WAIT() and @@last_gtid.
  5. When a TIME value is casted to a DATETIME, the date part will be the CURRENT_DATE, not 0000-00-00. This is compatible with the SQL standard and MySQL-5.6. One can use @@old_mode=ZERO_DATE_TIME_CAST to revert to the old behavior.
  6. XtraDB is now the default InnoDB implementation, Oracle InnoDB is a plugin that can be dynamically loaded if desired.
  7. Builds for Debian Sid and Ubuntu Trusty are being made available for the first time in the MariaDB repositories. For this release the Trusty packages are considered as alpha releases. The Sid packages will likely always be considered as such. Both were made as part of normal MariaDB development, and we’re making them available for those that want to test or try them out.

Also, if you read my MariaDB 10.0.8 Overview post, you will have noticed there was a build issue with Percona’s XtraDB that was preventing it from being the default in MariaDB 10.0. I’m glad I posted about it, as the MariaDB and Percona Devs began working together immediately, and resolved the issue in days, and now XtraDB+ is back the default InnoDB in MariaDB 10.0. (This is mentioned in the bullet point #6 above, but I wanted to explicitly mention it as well, so it did not go unnoticed.)

You can read more about the 10.0.9 release here:

https://mariadb.com/kb/en/mariadb-1009-release-notes/

And if interested, you can review the full list of changes in 10.0.9 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1009-changelog/

Hope this helps.

 

MariaDB 5.5.36 Overview and Highlights

March 14, 2014 - 10:36pm GMT

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

https://downloads.mariadb.org/mariadb/5.5.36/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a couple items worth mentioning:

  1. Includes all bugfixes and updates from: MySQL 5.5.36
  2. TokuDB is now included in RPM packages for CentOS 6 on x86-64

Note this release did not contain the bugfixes from XtraDB 5.5.36 yet. There is one bug fix in XtraDB 5.5.36 that is fairly serious, so if you are a *heavy* XtraDB+ user, I might wait for MariaDB 5.5.37 to be released, so you get this fix. I should note the bug was partially fixed in 5.5.35, and I did not see reports of the same issues in 5.5.35, so perhaps that covered it, or at least the majority. 5.5.34 had the worst of this bug, so if you are running 5.5.34, then I *would* definitely recommend upgrading.

Additionally, if you are using MySQL’s InnoDB in MariaDB, then I would definitely recommend upgrading to MariaDB 5.5.36 as well.

If interested, there is more about the 5.5.36 release here:

https://mariadb.com/kb/en/mariadb-5536-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.36 can be found here:

https://mariadb.com/kb/en/mariadb-5536-changelog/

Hope this helps.

 

Correlating OS Thread IDs from SEMAPHORES Section to TRANSACTIONS Section

February 14, 2014 - 9:58pm GMT

I’m frequently tracking semaphores waits, and if you’ve examined them before, it can be a little matching up the threads listed in the SEMAPHORES section with the transactions in the TRANSACTIONS section.

Semaphore waits are related to internal synchronization between threads in mysqld, and not directly to row locks or other items associated with user queries, so that’s why the SEMAPHORES section only reports the OS thread id.

Fortunately, the TRANSACTIONS sections also reports the OS thread handle, but in hex format.

Here is an example semaphore wait:

--Thread 1079654736 has waited at ibuf0ibuf.c line 3549
for 943.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f2a48830bf8 '&block->lock'
a writer (thread id 1079654736) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.c line 1318

So how do you match them up?

SELECT LOWER(HEX(1079654736));

This returns:

405a3950

Now, examine the TRANSACTIONS section, and look at the value for “OS thread handle”.

Here is the corresponding entry in the TRANSACTIONS section (note “OS thread handle 0x405a3950″) for the semaphore wait:

---TRANSACTION 99FAB3092, ACTIVE 283 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x405a3950, query id 3546555576 update
REPLACE INTO `t1` (id,`date`,`col3`,`col4`,`col5`,`col6`,`col7`,col8)
VALUES (352937385,'2012-08-27 06:16:58','1056665','3755','0','0','0',101)
TABLE LOCK table `db1`.`t1` trx id 99FAB3092 lock mode IX
RECORD LOCKS space id 3680 page no 156295 n bits 456 index `PRIMARY` of table `db1`.`t1`
trx id 99FAB3092 lock_mode X locks rec but not gap

Since we see “0x405a3950″, we know this is the transaction waiting for the semaphore.

Hope this helps.

 

Getting Started with the Spider Storage Engine

February 13, 2014 - 10:53am GMT

If you’re like me, you’ve probably heard of the Spider storage engine, but not used it yet.

While it has been available for some years now, I just simply haven’t used it before until now.

I suspect that has to do with ease of installation. Previously, one had to compile it with MySQL in order to use it, which excludes a lot of people. However, in MariaDB 10.0 (as of 10.0.4), it is very easy to add and use.

And with MariaDB 10.0.8 being declared RC, combined with Spider’s sheer usefulness, I only suspect its usage will become more and more widespread.

What is the Spider storage engine, and why will it be useful?

“The Spider storage engine is a storage engine with built-in sharding features. It supports partitioning and xa transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance. It refers to one possible implementation of ISO/IEC 9075-9:2008 SQL/MED. When a table is created with the Spider storage engine, the table links to the table on a remote server. The remote table can be of any storage engine. The table link is concretely achieved by the establishment of the connection from a local MariaDB server to a remote MariaDB server. When more than one Spider storage engine table can be used by the same connection, the connection is shared.”

https://mariadb.com/kb/en/spider-storage-engine-overview/

This is quite flexible, and given that, you can achieve a lot with spider, including federated, sharding, high availability, and background search (the last expected to be fully implemented by the time MariaDB 10.0 is declared GA).

How to install spider?

This is quite simple as the files needed are now included with the MariaDB distribution. In the /share directory, you will see a file named: install_spider.sql

You just need to import this, and there you go:

mysql -uroot -p -P3315 < install_spider.sql

For those interested in the behind-the-scenes, this creates the following 6 system tables (in 'mysql' schema):

spider_link_failed_log
spider_link_mon_servers
spider_tables
spider_xa
spider_xa_failed_log
spider_xa_member

And it automatically loads the .so or .dll plugin (depending if you're running on Linux or Windows), thus afterward, SHOW ENGINES should report the following:

mysql> show engines;
+--------+---------+------------+--------------+------+------------+
| Engine | Support | Comment    | Transactions | XA   | Savepoints |
+--------+---------+------------+--------------+------+------------+
| SPIDER | YES     | Spider ... | YES          | YES  | NO         |
...

The most simple example is accessing a table on another instance.

Let's say you have this remote table you want to access:

CREATE TABLE s(id INT NOT NULL AUTO_INCREMENT, code VARCHAR(10), PRIMARY KEY(id));

Then on the local server, where Spider is enabled, issue:

CREATE TABLE s(id INT NOT NULL AUTO_INCREMENT, code VARCHAR(10), PRIMARY KEY(id))
ENGINE=SPIDER
COMMENT 'host "127.0.0.1", user "chris", password "xxxxx", port "3314"';

Now you can SELECT from this table, or INSERT into it (which will write to the remote instance), etc.

INSERT INTO s(code) VALUES ('a');

This is very straight-forward, and really it's just the tip of the iceberg. The MariaDB KB article on the Spider storage engine has quite a bit of info and examples, so if you're interested, please see:

https://mariadb.com/kb/en/spider/

Hope this helps.

 

MariaDB 10.0.8 Overview and Highlights

February 11, 2014 - 8:14pm GMT

MariaDB 10.0.8 was recently released as RC (“Release Candidate”), and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.8/

This is the first RC release of MariaDB 10.0. It is primarily a bug-fix and polishing release, and all features planned for MariaDB 10.0 GA are included in this release.

There were 6 notable changes in MariaDB 10.0.8:

  1. InnoDB upgraded to version 5.6.14
  2. FLUSH … FOR EXPORT works
  3. Added a new server variable, old_mode, to allow selectively restoring old behavior, in contrast to the old “all-or-nothing” approach of the –old command-line option. See OLD MODE for more.
  4. Added a new read-only server variable malloc_library
  5. Bundled PCRE library upgraded to version 8.34
  6. The CREATE OR REPLACE TABLE statement was added. It is used internally for replicating CREATE … SELECT statements (MDEV-5491), but it is also can be used by clients as any other SQL statement.

You can read more about the 10.0.8 release here:

https://mariadb.com/kb/en/mariadb-1008-release-notes/

And if interested, you can review the full list of changes in 10.0.8 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1008-changelog/

Hope this helps.

 

MySQL 5.6.16 Overview and Highlights

February 11, 2014 - 7:52pm GMT

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

February 9, 2014 - 8:12pm GMT

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.

 

MariaDB 5.5.35 Overview and Highlights

February 6, 2014 - 10:07pm GMT

MariaDB 5.5.35 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.35/

This is a maintenance release, and so there are not too many big changes of note. However, there are a couple items worth mentioning:

  1. Includes all bugfixes and updates from: MySQL 5.5.35
  2. Includes all bugfixes and updates from: XtraDB from Percona-Server-5.5.35-rel33.0
  3. New SQL_MODE: OLD_MODE – to emulate behavior from old MySQL/MariaDB versions

You can read more about OLD_MODE here, if interested:

https://mariadb.com/kb/en/old_mode/

Also, there is more about the 5.5.35 release here:

https://mariadb.com/kb/en/mariadb-5535-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.35 can be found here:

https://mariadb.com/kb/en/mariadb-5535-changelog/

Hope this helps.

 

Exploring MySQL Metadata Lock Instrumentation in Closer Detail

February 5, 2014 - 10:42pm GMT

I recently wrote a post on tracking metadata locks (MDL) in MySQL 5.7, and I wanted to take a moment to expand on it by explaining a couple of the associated variables in more detail.

First off, once you have enabled the performance_schema *and* the metadata lock instrumentation, you can verify it with:

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES     | YES   |
+----------------------------+---------+-------+

“ENABLED” will report “YES” if it is enabled properly, and “NO” if not.

“TIMED” (referring to event timing) reports “YES” if it was enabled via the my.cnf or my.ini file, and it reports “NO” if it is enabled dynamically *and* you don’t explicitly set TIMED to YES. If you want to set it dynamically, *and* you want to enable event timing, then use this statement to enable the instrumentation:

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

As for variables:

mysql> show global variables like '%metadata%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| ...                                   | ...   |
| metadata_locks_cache_size             | 1024  |
| metadata_locks_hash_instances         | 8     |
| performance_schema_max_metadata_locks | 10000 |
+---------------------------------------+-------+

The first two are metadata_locks_cache_size and metadata_locks_hash_instances. In looking up more about these two variables, I found out they are deprecated as of 5.7.4. So, they were only in 5.7.3. Therefore, no need to worry about these either. This sometimes happens with new features.

For both variables, the manual says:

“In MySQL 5.7.4, metadata locking implementation changes make this variable unnecessary, so it is deprecated and will be removed in a future MySQL release.”

http://dev.mysql.com/..metadata_locks_cache_size
http://dev.mysql.com/..metadata_locks_hash_instances

That only leaves us with performance_schema_max_metadata_locks, which the manual says this:

“The maximum number of metadata lock instruments. This value controls the size of the metadata_locks table. If this maximum is exceeded such that a metadata lock cannot be instrumented, the Performance Schema increments the Performance_schema_metadata_lock_lost status variable.”

http://dev.mysql.com/doc/refman/5.7/.._max_metadata_locks

And here is the status variable the above refers to (incremented if performance_schema_max_metadata_locks is reached/exceeded), and note it is the only metadata-related status variable:

mysql> show global status like '%metadata%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Performance_schema_metadata_lock_lost | 0     |
+---------------------------------------+-------+

So if this is ever greater than 0, you’ll need to increase performance_schema_max_metadata_locks. However, the default is 10,000 which is quite high, so unless you have very high traffic loads, this should probably not ever be an issue (or perhaps it would indicate some larger issue at hand).

I searched the page for any possible new variables, and didn’t see any mention of any. However, with it being such a new feature, I’ll probably keep an eye out for any changes.

Lastly, the only other item I can think to cover here is the actual output from the metadata_locks table. Here is one full sample row (generated from a “BEGIN”):

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 395624368
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:5542
      OWNER_THREAD_ID: 21
       OWNER_EVENT_ID: 14
1 row in set (0.00 sec)

And here is output from a metadata lock hang with several rows:

mysql> select * from metadata_locks;
+-------------+..+---------------------+---------------+..+-----------------+..+
| OBJECT_TYPE |..| LOCK_TYPE           | LOCK_DURATION |..| OWNER_THREAD_ID |..|
+-------------+..+---------------------+---------------+..+-----------------+..+
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              21 |..|
| GLOBAL      |..| INTENTION_EXCLUSIVE | STATEMENT     |..|              22 |..|
| SCHEMA      |..| INTENTION_EXCLUSIVE | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_UPGRADABLE   | TRANSACTION   |..|              22 |..|
| TABLE       |..| EXCLUSIVE           | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              23 |..|
+-------------+..+---------------------+---------------+..+-----------------+..+

I think the rows are all described well, and self-explanatory, but I wanted to post the outputs for completeness. The second example above gives you an idea of the various LOCK_TYPES you might see, as well as LOCK_DURATIONS. I think it’s worth noting the OWNER_THREAD_ID corresponds to THREAD_ID in the performance_schema.threads table, in case you need to correlate the Process Id to the thread id listed here.

Hope this helps.