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

April 2nd, 2014

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 26th, 2014

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 14th, 2014

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 14th, 2014

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 14th, 2014

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 13th, 2014

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 11th, 2014

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 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

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.

 

MariaDB 5.5.35 Overview and Highlights

February 6th, 2014

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.

 


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty