I am developing a concurrent application that uses TokuDB to store its database. Sometimes, one of my SQL statements returns with a ‘lock wait timeout exceeded’ error. How do I identify the cause of this error? First, I need to understand a little bit about how TokuDB transactions use locks. Then, I need to understand how to use the MySQL information schema to look at the current state of the locks.

Transactions and Locks

TokuDB uses key range locks to implement serializable transactions. These locks are acquired as the transaction progresses. The locks are released when the transaction commits or aborts.

TokuDB stores these locks in a data structure called the lock tree. The lock tree stores the set of range locks granted to each transaction. In addition, the lock tree stores the set of locks that are not granted due to a conflict with locks granted to some other transaction.

TokuDB 7.1 provides access to …

Like many others, I’ll be taking the MySQL 5.6 certification exam at the end of this month. As Todd blogged, the quick reference pages are available online for the Developer Exam and the DBA Exam. I’ve long considered myself more of a DBA, so I know the the developer exam that I need to study up on these topics:

…

One day, after taking a job using MySQL, I was writing a query quietly at my desk. It was around quittin’ time, and I was whipping up a new report on the monthly sales figures that the CEO needed for his board meeting in the morning. I, in my ignorance, wrote a nice query with a simple little sum() function. What I did not realize is this caused all my rows to sum up to one row when it really should’ve show several rows. This made my report completely inaccurate. Blindly trusting me as I had blindly trusted MySQL, the CEO presented my report to the board who decided that the company was grossly unprofitable and voted to shut it down. Only when I was waiting in the bread line at my local workhouse did I realize my mistake.

That’s me in the bowler.

Okay, so that’s totally made up. Mostly. I caught the error …

October 16, 2013 By Severalnines

Galera uses a preallocated file with a specific size called gcache, used to store the writesets in circular buffer style. By default, its size is 128MB. In this post, we are going to explore how to leverage gcache to improve the operation of a Galera cluster.

Test Case

We have a four node Galera cluster, using the latest release 23.2.7(r157). We have a table called t1 that is replicated by Galera on all nodes. The cluster nodes have allocated the default 128MB gcache.size, and we’ll try to execute a large writeset to see how gcache responds.

mysql> SHOW VARIABLES LIKE …

I have blogged on using MariaDB Dynamic Columns already, and I hope this was useful and introduction. I have a few more things on this subject though, but one so far little known and used feature is the Client API for Dynamic Columns, see the MariaDB Knowledge Base for details. What this is all about is that Dynamic Columns were originally envisioned as a means of managing the "dynamic columns" used by Cassandra when using the MariaDB Cassandra Storage Engine. But as we realize, this is the server side of things (the Storage Engine) but there is a corresponding client library also, that is part of the MariaDB Client API.

As you have seen if you have read my previous blog on this subject, or whatever is written about MariaDB Dynamic Columns elsewhere, …

Here’s a problem some or most of us have encountered. You have a latin1 table defined like below, and your application is storing utf8 data to the column on a latin1 connection. Obviously, double encoding occurs. Now your development team decided to use utf8 everywhere, but during the process you can only have as little to no downtime while keeping your stored data valid.

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
master> SET NAMES latin1;
master> INSERT INTO t (c) VALUES ('¡Celebración!');
master> SELECT id, c, HEX(c) FROM t;
+----+-----------------+--------------------------------+
| id | c               | HEX(c)                         |
+----+-----------------+--------------------------------+
|  3 | ¡Celebración!   | C2A143656C656272616369C3B36E21 |
+----+-----------------+--------------------------------+
1 row in set (0.00 sec)
master> SET …

I’ll be in Hong Kong for the upcoming OpenStack Summit Nov 5-8. I’d be thrilled to talk database things with others present, especially around Trove DBaaS (DataBase as a Service) and high availability MySQL for OpenStack deployments.

I was last in Hong Kong in 2010 when I worked for Rackspace. The closest office to me was in Hong Kong so that’s where I did my HR onboarding training. I remember telling friends on the Sunday night before leaving for Hong Kong that I may be able to make dinner later in the week purely depending on if somebody got back to me on if I was going to Hong Kong that week. I was, and I went. I took some photos while there.

Walking from the hotel where we were staying to the Rackspace office could be done pretty much entirely through buildings without going outside. There were bits of art around too, which is just kind of awesome – I’m …

Like TokuDB, InfiniDB is now a fully open source server product. In the past infiniDB was “almost open source”. The open source version was an old release with no access to the advance functions like MPP multi-server execution. This is no more the case. With InfiniDB 4 the open source version is the latest release [...]

I often find myself in conversations with long time MySQL users who tell me about little annoyances in MySQL that make the product harder to use. Sometimes these annoyances can stay in a product for seemingly forever, because they require a change in behaviour (breaking backwards compatibility) or they are the result of architectural limitations not easily fixed.

In either case, I think it’s important to commend the MySQL engineering team at Oracle, because as well introducing a number of big ticket features for MySQL 5.6, they actually invested a lot of time into closing out a lot of old bugs.

Here is my list of historical gripes, now resolved:

1. innodb-file-per-table ON by default means it is easier to reclaim space caused by run-away tables.
2. When you change innodb_log_file_size in configuration, InnoDB will now resize logs to match automatically. …

As noted in earlier posts, exams for the MySQL 5.6 Developer and DBA certifications are entering a beta period, allowing candidates to register for the exams at steep discounts from normal certification exam fees.  I’ve registered to take both exams late this month, and – like Moritz – I’m starting to prepare for the exams now.  For those considering sitting for the exams, my plan is to document my exam preparation in a series of blog entries.  This won’t be a formal or official study guide, but I hope it will prove useful to those preparing for …