Tips and Tricks

Background: SkySQL is a distributed company. Nearly all of us work from home. To be productive, we need to emulate the best aspects of collaborating as if we were working next to one another. Given that nearly all of us had worked under similar distributed conditions at MySQL AB, we knew what we were getting into when we were founded. Obviously, we wanted to learn from our past experiences when making our choices for tools and processes.

What a relief! A few simple configuration steps gives me new hope for coping with email.

It unlikely matters to you, but it does to me: My inbox is down to zero. That is, both my two inboxes are empty. I get work email to my @skysql.com address and private email to my @arno.fi, both of which have been suffering from bad email habits.

We just ran into a nasty InnoDB bug that only seems to exist in MySQL 5.5.25:

An InnoDB update that modifies a rows primary key can trigger some recursive behavior that creates new rows until all disk space is exceeded. This does not affect all primary key updates in general but only gets triggered when a few other conditions are also met, so you're not too likely to run into it, but if you do your mysqld server will waste a lot of IO bandwidth and storage space on this and will usually eventually run out of disk space.

Every once in a while questions like the one in MySQL Bug #60843 or Bug #19567 come up:

What collation should i use if i want case insensitive behavior but also want all accented letter to be treated as distinct to their base letters?

or shorter, as the reporter of bug #60843 put it:

I need something like utf8_bin + ci

utf8_general_ci and utf8_unicode_ci unfortunately do not provide this behavior and utf8_bin is obviously not case insensitive.

I came across an interesting error reported on #mysql the other day. When I went through it with the reporter it looks like we uncovered up to two bugs in InnoDB (or rather XtraDB as it was Percona Server). I thought it might be useful to go through the error message, including the stack trace, to show that you don't need to be a developer to track down some useful information.

SkySQL has always been committed to the MySQL® community and today we've unveiled a new resource page for the community. This is designed for the community to get a single point of information, assistance, and inspiration. While we are starting quite modestly it is up to you, the community, to make this the best resource for MySQL and related technologies.

You may not realize it, but in MariaDB 5.2 and earlier and in MySQL up to version 5.5, START TRANSACTION WITH CONSISTENT SNAPSHOT does not give any guarantees of consistency between different storage engines.

For example, suppose you have two transactions which run in parallel:

Transaction T1:

BEGIN;
    SET @t = NOW();
    UPDATE xtradb_table SET a= @t WHERE id = 5;
    UPDATE pbxt_table SET b= @t WHERE id = 5;
    COMMIT;

Transaction T2:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    START TRANSACTION WITH CONSISTENT SNAPSHOT;
    SELECT t1.a, t2.b
      FROM xtradb_table t1 INNER JOIN pbxt_table t2 ON t1.id=t2.id
    WHERE t1.id = 5;

In the above case, it is possible, even with a "consistent" snapshot, to see the changes in a transaction only in InnoDB/XtraDB tables, and not in PBXT tables.

Naturally, it would be much better if the changes were visible no matter the storage engine used by the table. Thankfully this is possible, thanks to the introduction in MariaDB 5.3 of group commit and an enhanced storage engine API for COMMIT which allows storage engines to coordinate commit ordering and visibility with each other and with the binary log.

With these improvements, the START TRANSACTION WITH CONSISTENT SNAPSHOT statement in MariaDB 5.3 has been enhanced to ensure consistency in-between storage engines which support the new API. Currently the storage engines which support this are XtraDB (MariaDB's enhanced version of InnoDB) and PBXT. In addition, the binary log, while not a storage engine as such, also supports the new API and can provide a binlog position consistent with storage engine transaction snapshots.

This means that with transaction isolation level at least REPEATABLE READ, the START TRANSACTION WITH CONSISTENT SNAPSHOT statement can be used to ensure that queries will see a transaction-consistent view of the database between storage engines.

No longer is it possible for a query to see the changes from some transaction T in XtraDB tables without also seeing the changes T makes to PBXT tables.

More information on this can be found in the AskMonty Knowledgebase.

The information here was adapted from the AskMonty Knowledgebase.

There are two main parts to MariaDB and MySQL: The mysqld server and whatever client you use to interact with the server. The server is absolutely essential and must remain up and running. mysqld is normally very reliable, but there are rare occasions when it will fail. When mysqld fails hard (or core dump) it will, by default, write a stack trace in the 'hostname'.err file in the database directory. However, in some cases this is not enough to find out exactly what happened.

If you ever run into a situation where mysqld crashes and the 'hostname'.err file does not contain enough information for your DBA or support provider to diagnose the problem, you may need to use what is known as a "debug" build to produce a "full stack trace" and a core file. The following instructions describe how to do this on Unix-like systems.

The steps are:

1. Download a source tar.gz file (like mariadb-5.2.9.tar.gz).
2. Compile it for debugging
3. Update your my.cnf file to ensure you get a core and a stack trace.
4. Install the debug mysqld instead of your normal one.
5. Run with the debug version until you get a new crash.
6. Restore your old mysqld version.

Compiling MySQL or MariaDB for debugging

In the following example I use a MariaDB 5.2.9 Source .tar.gz, but the steps apply equally well to a MySQL source .tar.gz file.

Prior to compiling MariaDB or MySQL, your system needs to be set up with all of the correct libraries and developer tools. The Compiling MariaDB from Source section of the AskMonty Knowledgebase has links to several articles to help you get your system set up properly.

Once things are set up, creating a debug version of mysqld is as easy as:

cd ~
mkdir mariadb
cd mariadb
tar xvf mariadb-5.2.9.tar.gz
ln -s mariadb-5.2.9 current
cd current
./BUILD/compile-pentium64-debug-max

The last command will produce a debug version of sql/mysqld. If you have a system other than 64 bit Intel/AMD on Linux you can use a different BUILD/...-debug-max file. If this fails, you can try with:

./BUILD/autorun.sh
./configure --with-debug=full -with-extra-charsets=complex \
--with-plugin-aria --with-aria-tmp-tables --without-plugin-innodb_plugin \
--with-plugins=max \
--with-mysqld-ldflags=-all-static  --with-client-ldflags=-all-static 
make

Updating your my.cnf

Add the following to the end of your ~/.my.cnf file:

[[mysqld]]
--stack-trace
--core-file
[[mysqld-safe]]
--core-file-size=unlimited

These are safe to leave there also in the future.

Note: If you are using safe_mysqld and running mysqld as root, then no core file is created on some systems. The solution is to run mysqld as another user.

Temporarily replacing your standard mysqld with the debug version

This is how to do it on Open SuSE. Other Linux distributions may put ##mysqld## in a different location.

mysqladmin shutdown
cd /usr/local/mysql/libexec
mv mysqld mysqld-orig
cp ~/mariadb/current/sql/mysqld mysqld-debug
ln -s mysqld-debug mysqld

The above commands replace the current mysqld with the recently compiled debug version, but do so in a way which makes it trivial to revert back to the original version (you just have to change the sym-link).

Once the debug version is in place, start mysqld again (with something like):

/etc/rc.d/mysql start

(Use whatever command you normally do. The mysqld binary has been switched to the debug binary, so your startup scripts will automatically use it.)

Restoring your original mysqld version

If you want to restore your original mysqld binary, you can do it with:

cd /usr/local/mysql/libexec
rm mysqld
ln -s mysqld-orig mysqld

Notice that the debug binary was not deleted, all that was removed was the sym-link (and it was immediately recreated and pointed at the original binary). This way, the debug binary is still there if you need it in the future.

Using the core file

If/when the debug version of mysqld crashes, you will be left with:

• A more precise stack trace in the 'hostname'.err file in the data directory.
• A core or core.#### file in your data directory.

To examine the stack trace and other information in the gdb debugger you can do:

cd ~/mariadb/current
cp mariadb-database-directory/core* core
gdb ~/mariadb/current/sql/mysqld core
backtrace full

Of course, if you're like me. As soon as I have a stack trace and core file, my next step is to turn those, and my debug binary, over to the experts. I'm not going to pretend I know how to effectively use a debugger.

More information on troubleshooting the above steps (if you're still having trouble with your crashing mysqld not generating a core file, and so on...) is available at the Knowledgebase entry linked to at the beginning of this article.

Whenever I start a new project with MySQL or MariaDB I am tempted to jump in and start creating my schema and tables with nothing more than a basic outline of the requirements. Often the justification I give myself is that the sooner I have something up and running, the better off I'll be, and besides, there will be plenty of time to sort things out later. Even when I am absolutely sure my ad-hoc schema is only for "development purposes" and will "never ever be used in production" it often is.

A lot of grief can be avoided if time is taken right at the start, before any tables are defined, to plan. This is big picture time and every stakeholder, from IT to the developers to management and even to marketing, needs to be involved. If for no other reason than to set expectations.

Right at the start, everyone involved in the project needs to know that there is no perfect data definition, and no one schema, server, or cluster is capable of being all things to all people. As hard as it may be to believe, this actually may be news to people unfamiliar with databases or technology in general. The more thought and planning that goes into the design of your database, and the more realistically you can set the expectations right and the beginning, and long before real data is stored in it and real users are using it, the better off you'll be.

During the planning stages you need to look at "nuts and bolts" things like data types, indexes, and normalization. You should also look at things like performance requirements, budget, business use cases, and so on. Be thorough, but be pragmatic. For example, if you're creating a schema for the backend database of a simple workgroup app that will never have more than a dozen users and a few dozen megabytes of data, you can get away with things which would make an enterprise-wide system with thousands of concurrent users fall flat on its face.

Following are some things I try to keep in mind whenever I begin a new project:

Know your data. INTs, VARCHARs, CHARs, BLOBs, DATETIMEs, and other datatypes are not exciting to think about (for most people), but defining your tables properly, with the correct (and correctly sized) data types in the correct places can have a measurable impact on the performance of your database. For example, avoid the temptation to stuff everything into large VARCHARs and call it good. It might make development easier, but it could sink you performance-wise. Different data-types exist for a reason. It is to your benefit to learn them, their differences, and how to properly use them.

Normalize only as long as it benefits you. In theory, you should almost always normalize your database. Normalization minimizes duplicated data and can greatly improve performance. However, there are different levels of normalization and for certain data and/or workloads, 2NF (2nd Normal Form) may work better than 3NF or vice-versa. There are also cases where de-normalized data may work better. On a similar note, if terms like "3NF" are unfamiliar to you, pick up a book (or search the web) and learn about the different normal forms. You'll be glad you did.

No database lives in a vacuum. Your database has to live in the real world, so don't forget to consider budgets, business use cases, and the budget for your project. It's a fact of life that compromises will have to be made in almost any project, so try and get out in front and set realistic goals and deadlines. Also, when changes or additions to the project are proposed, communicate clearly how the changes will affect things like execution times, hardware requirements, and so on. Sometimes the reasons behind a change are worth it, but sometimes they're not.

Lastly: Don't be afraid to ask for help. No single person, or small group, can be expected to know everything and there's no shame in asking for or hiring outside help.

Good luck with your next project!

One issue that developers often run into is needing or wanting to run a development version of MariaDB or MySQL on their desktop when there is already a production version installed. You may want to try out a new feature, or experiment with a patch, or maybe you're developing a new storage engine, or just having fun hacking on the code. Whatever the reason, when this happens to me I often want to be able to run the non-standard version without replacing my currently installed version.

Fortunately, the process for doing so is easy. The instructions below are adapted from the Running MariaDB from the source directory article in the AskMonty Knowledgebase.

Before beginning, make sure you shut down your running instance of mysqld before starting your test instance. It is possible to run multiple mysqlds on the same server, but doing so is beyond the scope of this blog post.

With that out of the way, the first step is to download and compile the source code. There are howtos on this for various operating systems and Linux distributions on the AskMonty Knowledgebase.

Follow the compilation instructions up to the point where you would normally do make install and stop before you do that. Instead create a .my.cnf file (notice the leading '.') in your home directory (i.e. '~/.my.cnf'). Start with the example one given in the above Knowledgebase article.

There are two lines in the example file which you must edit if you want things to work: the 'data=' line and the 'language=' line. These two lines must contain hard-coded paths to your test data directory (if you're testing, you should not set it to your 'real' data directory) and the language files.

Once you have your customized .my.cnf file in place, cd over to the source directory and run the following command:

./scripts/mysql_install_db --srcdir=$PWD --datadir=/path/to/data/dir

'$PWD' in the command above is the environment variable which points to your current directory. On some systems you may need to replace it with the path manually. Also, the '--datadir' variable isn't strictly needed if you added it to your ~/.my.cnf file. What the above command does is create the base mysql schema that mysqld needs in order to run.

Now, assuming you compiled MySQL or MariaDB correctly, you can start mysqld with:

cd sql
./mysqld &

You may want to start the server inside the debugger (especially if you are doing development work):

cd sql
ddd mysqld &

Pay attention to any errors as the server daemon starts up. Assuming you compiled thing successfully, an error at this stage often means your '~/.my.cnf' file has a typo or other error in it.

After successfully starting up mysqld using one of the above methods (with the debugger or without), launch the mysql client (as root if you don't have any users setup yet).

../client/mysql

If you successfully connect, congratulations! You're running mysqld from the source directory without installing it!

What about Windows?

The above instructions are for Linux and Unix-like systems. But you can do the same thing on Windows with some alterations. See the AskMonty Knowledgebase article for details.