Posts Tagged ‘chris calender’

Building MariaDB 5.1 on Windows

Monday, January 23rd, 2012

Recently, I found myself needing MariaDB 5.1.60 for Windows for some testing purposes. Therefore, I needed to build it from source. I ended up using what I’d call a “blend” of the commands listed in this “how-to” and the readme file INSTALL-WIN-SOURCE, so I thought I’d post those steps.

  1. Download 5.1.60 MariaDB source from here.
  2. cd C:\mariadb-5.1
  3. win\configure.js
  4. cmake .
  5. VS: File -> Open -> Solution -> MySql.sln
  6. VS: Build -> Build Solution
  7. VS: Right-click “PACKAGE” -> Build (in “Solution Explorer” View)

That’s it.

Let’s fire it up:

MariaDB> select version();
+----------------------+
| version()            |
+----------------------+
| 5.1.60-MariaDB-debug |
+----------------------+

MariaDB> show global variables like 'innodb_version';
+----------------+-------------+
| Variable_name  | Value       |
+----------------+-------------+
| innodb_version | 1.0.17-13.0 |
+----------------+-------------+

MariaDB> show engines;
+------------+---------+---------------------------...
| Engine     | Support | Comment		   ...
+------------+---------+---------------------------...
| CSV        | YES     | CSV storage engine	   ...
| InnoDB     | DEFAULT | Percona-XtraDB, Supports t...
| PBXT       | YES     | High performance, multi-ve...
| MARIA      | YES     | Crash-safe tables with MyI...
| MyISAM     | YES     | Default engine as of MySQL...
| FEDERATED  | YES     | FederatedX pluggable stora...
| MRG_MYISAM | YES     | Collection of identical My...
| MEMORY     | YES     | Hash based, stored in memo...
+------------+---------+---------------------------...

..

For reference, here are my full outputs:

C:\Users\Chris>cd ..\..\mariadb-5.1

C:\mariadb-5.1>win\configure.js

C:\mariadb-5.1>cmake .
-- Check for working C compiler: cl
-- Check for working C compiler: cl -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: cl
-- Check for working CXX compiler: cl -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
build CSV as static library (libcsv.lib)
build FEDERATEDX as static library (libfederatedx.lib)
build HEAP as static library (libheap_s.lib)
build MARIA as static library (libmaria_s.lib)
build MYISAM as static library (libmyisam_s.lib)
build MYISAMMRG as static library (libmyisammrg_s.lib)
build PBXT as static library (libpbxt_s.lib)
build XTRADB as static library (libxtradb.lib)
build ARCHIVE as DLL (ha_archive.dll)
build BLACKHOLE as DLL (ha_blackhole.dll)
build EXAMPLE as DLL (ha_example.dll)
build FEDERATED as DLL (ha_federated.dll)
build INNODB_PLUGIN as DLL (ha_innodb_plugin.dll)
-- Configuring done
-- Generating done
-- Build files have been written to: C:/mariadb-5.1

Open Visual Studio -> File -> Open -> Project/Solution -> Select C:\mariadb-5.1.60\MySql.sln

Build Solution Output:

========== Build: 79 succeeded, 0 failed, 2 up-to-date, 2 skipped ==========

Package Build Output:

1>------ Build started: Project: PACKAGE, Configuration: Debug Win32 ------
1>
1>Performing Post-Build Event...
1>CPack: Create package using NSIS
1>CPack: Install projects
1>CPack: - Install project: MySql
1>CPack: -   Install component: Unspecified
1>CPack: -   Install component: headers
1>CPack: -   Install component: mysqltest
1>CPack: -   Install component: runtime
1>CPack: -   Install component: scripts
1>CPack: -   Install component: sqlbench
1>CPack: Compress package
1>CPack: Finalize package
1>CPack: Package C:/mariadb-5.1/MariaDB-5.1.60-win32.exe generated.
1>Build log was saved at "file://c:\mariadb-5.1\PACKAGE.dir\Debug\BuildLog.htm"
1>PACKAGE - 0 error(s), 0 warning(s)
========== Build: 1 succeeded, 0 failed, 81 up-to-date, 0 skipped ==========

 
 
..

Related Build Links:

 
 

Happy Birthday MySQL!

Tuesday, January 10th, 2012

I suppose the true birthdate could be debated, but from the *Community* perspective, it can be either the 14 or 11 year birthdate for MySQL!

*Unless* of course you are Monty or David, or one of the internal testers/users, for which May 95 may hold slightly more significance :)

14 Years ago: The Windows [alpha] was released January 8, 1998.

11 Years ago: The first GA version of 3.23 was released in January 2001

http://en.wikipedia.org/wiki/MySQL#Product_history

Note Monty and David first began working on MySQL in 1994, and the first internal release occurred on May 23, 1995. But, since that was all internal, January works for me.

Happy Birthday MySQL!

 

Upgrading from MySQL to MariaDB is Easy as 1,2,3

Tuesday, January 10th, 2012

This post is just to show how easy it is to upgrade or migrate from MySQL to MariaDB.

I should begin by stating this article is geared more towards MySQL 5.1 and prior, as MySQL 5.5 users will likely want to wait until MariaDB 5.5 is available (which I believe will be in the near future).

As you may or may not know, there are actually 3 flavors of MariaDB currently: 5.1, 5.2, and 5.3.

All three are based off of the 5.1 MySQL code base, just 5.2 and 5.3 have further improvements over 5.1. So, that’s why I say this upgrade is “easy” and there’s no need to be afraid, especially for 5.1 users. But even if you were a 5.0 user, the upgrade to MySQL 5.1 compared to MariaDB 5.1 would not be any different. So, why not give it a go?

You’ll have all of the benefits from using MySQL, but also all of the added improvements from MariaDB and XtraDB+.

I like the fact you can “ease” into it. For instance, if running MySQL 5.1.60 (the most current 5.1) on Linux with the InnoDB Plugin, you can simply move to MariaDB 5.1.60 using the InnoDB Plugin1.

Then basically everything is the same, but now you have access to the new features. For instance, you’d now have the Aria, XtraDB, PBXT, and FederatedX storage engines. You could easily upgrade your InnoDB Plugin to use the XtraDB+ Plugin instead once you have MariaDB installed (basically it’s the same InnoDB but with a number of extra performance-related improvements). It’s all up to you once you make the switch.

I should note that if you are running Windows, then I suggest you migrate straight to MariaDB 5.2, as there are numerous Windows-specific improvements starting with MariaDB 5.2 (and even more Windows performance enhancements in 5.3). (I would imagine that once upgrading, one would want to benefit from the 5.2 improvements anyways, and perhaps benchmark 5.3 in the meantime.)

As for all of the specific details, let me first quote the MariaDB Compatibility page from their manual, just for reference:

“For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 will be compatible with MariaDB 5.5).”

http://kb.askmonty.org/en/mariadb-versus-mysql

The above page goes on in-depth about the compatibility, drop-in binary replacement upgrade, how to replace an rpm, and even the couple incompatibilities (from MySQL 5.1 to MariaDB 5.1, 5.2, and 5.3). Everything is well-documented, and the above is a great reference for anyone planning (or even considering) to upgrade.

As I mentioned, there are currently 3 series of MariaDB: 5.1, 5.2, and 5.3. The latest 5.1 is 5.1.60 (and it is GA), the latest 5.2 is 5.2.10 (also GA), and the latest 5.3 is 5.3.3 (which is “Release Candidate” status – so close to GA).

You can download MariaDB from here (there are links to all 3 series):

http://downloads.askmonty.org/mariadb/

And the improvements are too many to list, but let me post some:

5.1:

o New storage engines: Aria, XtraDB, PBXT, FederatedX 2
o Speed Improvements
o New Extensions & New Features
o Upgrade and Testing improvements

5.2:

o New storage engines: OQGRAPH and SphinxSE
o Virtual columns
o Extended User Statistics
o Segmented MyISAM key cache
o Pluggable Authentication
o Group commit for the Aria engine

5.3:

o Subquery optimizations
o Semi-join subquery optimizations
o Non-semi-join optimizations
o Subquery Cache
o Optimizations for derived tables and views
o Disk access optimization
o Join optimizations
o Index Merge improvements
o Optimizer control
o Microsecond support
o Windows performance improvements

And for the full list of improvements in each series, please refer to the following links for 5.1, 5.2, and 5.3, respectively:

http://kb.askmonty.org/en/what-is-mariadb-51
http://kb.askmonty.org/en/what-is-mariadb-52
http://kb.askmonty.org/en/what-is-mariadb-53

Thus far, everything sounds like an improvement. But as far as I can tell, if you take a great product and add even more enhancements on top of it, it’s hard to go wrong.

If I were pressed to come up with a “con”, I would say there might be a slight delay (up to a month) from the latest MySQL available version compared to the latest MariaDB version. However, this may not be a “con” anyway. After all, merging monthly gives the MySQL code a couple weeks to mature, basically letting others run into any new bugs before it’s merged into MariaDB. In fact, it reminds me of the old “Quarterly Service Packs” versus the “Monthly Rapid Updates” MySQL used to provide. Therefore, if one were to apply that reasoning, it could be said that you might be less likely to encounter a regression bug in MariaDB (therefore overall stability could be improved).

And if you need support for MariaDB, just contact SkySQL, as we fully support it:

http://www.skysql.com/how-to-buy

Notes:

1: In MariaDB, if you use the built-in InnoDB, then you are using XtraDB+. Otherwise, if you enable the InnoDB Plugin, then you are enabling the standard InnoDB plugin. Note that XtraDB+ is an improvement over XtraDB as it contains even further enhancements.

2: FederatedX is a huge improvement over Federated – a “must have” if you use Federated.

MySQL High Availability Manager (MHA) 0.53 has been Released and Get Support for it at SkySQL

Tuesday, January 10th, 2012

I just wanted to let you all know that MHA for MySQL (Master High Availability Manager and tools for MySQL) version 0.53 has been released.

Yoshinori Matsunobu discusses the release in much more detail here:

http://yoshinorimatsunobu.blogspot.com/2012/01/mha-for-mysql-053-released.html

The full MHA 0.53 changelogs are here:

http://code.google.com/p/mysql-master-ha/wiki/ReleaseNotes

MHA 0.53 can be downloaded from here:

http://code.google.com/p/mysql-master-ha/downloads/list

And if you would like support for MHA, simply contact SkySQL:

http://www.skysql.com/how-to-buy

 
 

Tracking Server Variables, Documentation, Manuals, Changelogs for MySQL, InnoDB, MariaDB, and XtraDB

Monday, January 9th, 2012

I find myself constantly looking up server variables (and manuals and changelogs) for MySQL, MariaDB, and XtraDB, which versions they are in, and so forth. So I finally created a couple pages which contain the links to all of these various bits of information across the various flavors of MySQL.

I’ve been using them every day, so I thought some others might want to bookmark these as well.

I’ve created the following:

o Changelogs
o Documentation
o Server Variables
o InnoDB Plugin Versions

The Changelogs page contains links for MySQL 3.23 up through 5.6, MariaDB 5.1 – 5.3, XtraDB 5.1 – 5.5, Xtrabackup 1.3 – 1.6, Connector/ODBC, Connector/.NET, Connector/J, Connector/C++, Connector/MXJ, and MySQL Proxy.

The Documentation page contains links for the MySQL manuals 3.23-5.6, InnoDB Plugin 1.0 – 1.1, MariaDB 5.1 – 5.3, XtraDB 5.1 – 5.3, and Xtrabackup 1.6.

The Server Variables page contains the links for the MySQL Server variables for all versions, the server variable cross-reference chart, all InnoDB startup variables, and new MariaDB options.

And lastly, the InnoDB Plugin Versions (which I did mention a couple weeks ago) contains all InnoDB Plugin version information, such as which InnoDB plugin is included with which MySQL release, as well as links to changelogs, and other relevant information.

If you find yourself wishing there were some other links added, just post me a comment and I’ll try to get it added asap.

 
 

Using SHOW PROCESSLIST and mysqladmin debug Output in Conjunction with SHOW INNODB STATUS

Saturday, November 19th, 2011

When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.

In fact, it’s the first thing I check when InnoDB tables are involved.

However, I just want to iterate how valuable SHOW FULL PROCESSLIST and/or mysqladmin debug outputs can be even when it seems mysqld is hung on on InnoDB table.

Two recent cases I’ve encountered illustrate why.

Case #1:

MySQL appeared hung on the following simple, single-row INSERT:

---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047,
OS thread id 3069426592, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
...
INSERT INTO test (id, parent, text) VALUES (180370, 70122, 'test table')

At least that’s what it seemed per the INNODB STATUS, but unfortunately, there wasn’t any further information to go on.

The next time it occurred, SHOW FULL PROCESSLIST was captured at the time.

Turns out, there was a *very* long SELECT running, but not from the same table, and no foreign keys (FKs) either. Turned out it was some crazy, auto-generated query that self-joined itself 548 times. So there were no locks, per se. This query itself held up everything, and thus also the INSERT.

Case #2:

This was a table that was also hanging on a certain, simple UPDATE. The UPDATE was based on te PK, so only one row was to be updated.

Yet, it hung, and it hung, longer than wait_timeout, interactive_timeout, and innodb_lock_wait_timeout. And there were no other transactions running in the INNODB STATUS.

Turned out, another client had issued a LOCK TABLE command on the table. Since LOCK TABLE is handled outside of the InnoDB storage engine, the lock doesn’t appear in SHOW INNODB STATUS output.

Using mysqladmin debug output, coupled with SHOW PROCESSLIST helped catch this offender.

At any rate, hope this helps, and happy troubleshooting. :)

 
 

Setting Up SSL For MySQL

Friday, November 18th, 2011

I often get asked how to set up and use SSL with MySQL, especially on Windows – I think largely because the manual step-by-step is more geared towards Linux users (though this “how-to” is valid for all – anyone can copy/paste the commands after changing the paths).

So I’ve compiled a concise, yet comprehensive, how-to that I wanted to share with everyone out there who wants to set this up quickly and efficiently.

And for those who haven’t done it, setting up SSL can often seem intimidating, so I want to dispell that myth while I’m at it, and show you how quick and easy it can be to set up SSL for MySQL.

For one, I should mention that I’m using a version where SSL is ‘available’, just ‘DISABLED’ (some versions can be compiled w/out ssl support – so those would not work):

mysql> show global variables like 'have_%ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
+---------------+----------+

This is normal, and what you want to see if you’ve not already set up SSL.

Next, I like to store the SSL files I generate in their own directory, so I created a subdir named ‘certs’ in the MySQL $basedir. But by all means, store these wherever you wish.

Third, you need to have OpenSSL installed/available on your machine. Many have this already, certainly most Linux anyway (check with “where openssl” on Win or “which openssl” on Linux). (These latter commands assume you have it in the $PATH too.)

If you do not have it, you can download it from here:

http://www.slproweb.com/…/Win32OpenSSL.html <-- Windows
http://www.openssl.org/source/ <-- Linux

(And note my commands below assume either 'openssl' in the $PATH or that you're in the directory where the 'openssl' binary/exe is located.)

Now that that’s out of the way, you’re basically looking at a 5 step process:

  1. Create CA Certificates (following 2 commands create 2 files: ca-cert.pem and ca-key.pem):
    openssl genrsa 2048 > "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem"
    openssl req -new -x509 -nodes -days 3600 -key
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
  2. Create Server Certificates (following 2 commands create 3 files: server-cert.pem, server-key.pem, and server-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem"
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 >
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-cert.pem"
  3. Create Client Certificates (following 2 commands create 3 files: client-cert.pem, client-key.pem, and client-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem"
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 >
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-cert.pem"
  4. Create SSL User(s):
    GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;
  5. Update my.cnf and restart mysqld (add following 3 options to [mysqld] section of my.cnf file)
    [mysqld]
    ssl-ca		= "C:/Program Files/MySQL/mysql-5.5.16/certs/ca-cert.pem"
    ssl-cert	= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-cert.pem"
    ssl-key		= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-key.pem"

After restart, connect via SSL with your newly created SSL user (this is the minimum you need to specify – note the “–ssl-key=”, as-is .. refer to prev. post and related bug for further discussion on that if interested):

mysql -ussluser -pssluser -P3430 --ssl-key=

Connected!

You can verify with the ‘status’ command once connected (note the SSL “cipher in use is DHE-RSA-AES256-SHA” means SSL is being used):

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.16, for Win32 (x86)

Connection id:          11
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.5.16-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3430
Uptime:                 35 min 26 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 0
Queries per second avg: 0.011

Comments:

If you’re knowledgeable abut SSL (and MySQL), or if you’ve read my prior post discussing SSL, you’ll know you do not need all of the files I created above (8 in total).

In fact, for the most basic SSL setup, (“gateway SSL”, we’ll say), you only technically need these 3 files: ca-cert.pem, server-cert.pem, and server-key.pem.

But, why not create all you’d ever need, since it’s only a couple commands, and then you’re set in case you want to move beyond the “gateway SSL” setup into an even more secure SSL setup.

If you’re interested in the even-more-secure SSL setups, you’ll want to read up on the REQUIRE X509, REQUIRE ISSUER, REQUIRE SUBJECT, and REQUIRE CIPHER specifications on the following page:

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

As always, hope this helps. :)

 
 
 

Manually Deleting file and DISCARD TABLESPACE are not the only ways to encounter the “ibd file does not exist” Error

Friday, November 18th, 2011

I’m sure most of you have seen the following error, and in many cases it’s quite easy to diagnose, as the error message is clear:

111111 11:11:11 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table test/t does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

From the client side, you may see something like:

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

However, if you know you (or some backup/archive script) did not delete the file, nor did you run ALTER TABLE table DISCARD TABLESPACE, then what else could cause this?

Well, I recently found a case where it could occur when a truncate table (on an InnoDB table using the plugin with no foreign keys – so fast truncate method is used, and innodb_file_per_table is enabled) is issued on a table.

Then, if a crash occurs in the middle of this (after the .ibd file has been deleted, but before the re-creation process completes), then you can end up with this error (after a restart and a subsequent SELECT on this table).

I expect this is quite rare, but can happen nonetheless, so I wanted to add this for those searching for more possibilities of missing .ibd files.

Fwiw, I did add this as a user comment in the manual where this topic is discussed:

http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html

 
 
 

Advanced InnoDB Deadlock Troubleshooting – What SHOW INNODB STATUS Doesn’t Tell You, and What Diagnostics You Should be Looking At

Thursday, November 10th, 2011

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65839 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 65838, ACTIVE 26 sec, OS thread id 768 starting index read,
thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 2
MySQL thread id 4, query id 75 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

Now, we do see a lot about what caused the deadlock above, but we are only seeing *half* of the picture.

Allow me to explain ..

First of all, note transaction #1 has been running for 19 seconds, while transaction #2 for 26 seconds. So, the output is referring to the newer transaction as #1 and the older as #2 (also somewhat counter-intuitive, but good to be aware of).

Now, what we can see clearly is this:

Transaction #1 (“UPDATE parent .. WHERE id=1″) is waiting on a lock from Transaction #2 (“UPDATE parent .. WHERE id=2″).

Thus TX #2 holds a lock (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 2), but is waiting on (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 3 – held by TX #1).

Clearly the 2 updates should not cause a conflict in and of themselves. Thus we know something must have happened earlier in the transaction(s).

For reference, here is how to reproduce it:

CREATE TABLE `parent` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
`parent_id` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB;

INSERT INTO parent (id, age) VALUES (1, 50);
INSERT INTO parent (id, age) VALUES (2, 60);
INSERT INTO child (id, age, parent_id) VALUES (1, 20, 1);
INSERT INTO child (id, age, parent_id) VALUES (2, 20, 1);

Then, open 2 connections (T1 and T2 – note order is opposite compared to what is shown in SHOW INNODB STATUS):

T1:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=1;
UPDATE parent SET age=age+1 WHERE id=1;

T2:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=2;
UPDATE parent SET age=age+1 WHERE id=1;

<-- T2 Hangs

T1:

UPDATE parent SET age=age+1 WHERE id=2;

<-- Deadlock (T1 completes, T2 is rolled back)

But why does this deadlock? Well, it is due to the foreign key. In fact, this example would not deadlock at all if no foreign key was defined on `parent`.`id`.

So then what exactly is preventing T2 from completing?

Here is what is happening behind the scenes, so-to-speak:

T1#1:

Obtains the following 2 locks:

X lock on `child`  where id=1 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK on parent.id

(Note this S lock means other S locks can be obtained on this row, but not X locks -- which is the crux of this issue).

T1#2:

Obtains the following lock:

X lock on `parent` where id=1 <-- due to the actual "UPDATE parent" statement itself

T2#1:

Obtains the following 2 locks:

X lock on `child`  where id=2 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK (again, this is okay since it is also a S-lock)

T2#2:

Tries to obtain the following, but hangs due to the existing X-lock from T1#2:

X lock on parent where id=1

T1#3:

Tries to obtain the following lock:

X lock on parent where id=2

However, since there are 2 S-locks on this row already (one from T1 and T2), and T1 now wants an X-lock on the same row, then there is a conflict.

Now this would normally just wait for the S-locks to be released, but since T2 is already "hung" waiting on the other lock to be released from T1, we now have the deadlock.

T1 wins the dispute, T2 rolls back thus releasing its locks, and T1 completes.

So all in all, quite a bit is going on there, but you only see about half of this information from the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS output. And had I not posted the SHOW CREATE TABLE status (and prior tx statements), it'd be unclear as to what happened exactly.

Well, so how do you find out exactly what happened when locking problems happen to you?

SHOW INNODB STATUS only tells you so much. Furthermore, once the deadlock occurs, the winner moves on, and the loser is rolled back. *Meaning*, there is no longer any information about these "transactions" in the output as they are in the "past" now.

Therefore, in general, if you're having any locking issues (deadlocks, lock wait timeouts, hangs due to semaphore waits, and so forth), do yourself a favor and capture all of the following outputs at the time, if possible, to give you the best likelihood in tracking down the issue:

  1. SHOW ENGINE INNODB STATUS
    • This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
  2. Enable InnoDB Lock Monitor (enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor)
    • This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output, but it can get truncated too.
  3. Run "mysqladmin debug"
    • Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table, because LOCK TABLE is external to the InnoDB storage engine. Not so great because a bit cryptic to read, and I wouldn't solely reply on it, as it's often most helpful in conjuntion with other details).
  4. SHOW FULL PROCESSLIST
    • This will show all connected threads. Specifically, when it comes to 'hidden' locks, it would show a user that has been connected, but idle (but who could have issued a LOCK TABLE command).
  5. Error log
    • Of course, always check out the error log for messages and/or anything out of the ordinary. (Not to mention extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor.)
  6. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables
  7. SHOW CREATE TABLE outputs for each table involved

 
 
 

Added a Table of Contents

Wednesday, November 9th, 2011

Not a big deal, but I just added a “Table of Contents” page to my blog to make finding older articles much easier.

I noticed most of my posts are quite lengthy, and it can take a bit of searching/clicking to find an older entry. So unless you happen to recall the ‘month/year’ it was published, which I don’t even remember that, then hopefully this will help.

Really simple, and looks just like this:

My hopes are that this will aid in making some posts easier to find (such as ones about InnoDB Recovery, Recovery with an Individual .ibd, Proxy-related articles, Error-related articles, How-to posts, and so forth).

You can see the full “table of contents” here:

http://www.chriscalender.com/?page_id=399

Happy reading :)

 
 
 


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty