Monitor NDB memory usage with Nagios

We are planning to put online a MySQL NDB Cluster soon (more on this in another post), and one thing you have to do before putting anything in production is to monitor it for problems. In the case of a NDB cluster, you should care about monitoring your limited resources – basically because is a in-memory database – and be alerted when your developers are filling up the dedicated tablespace.

You can do this in two ways: performing a SELECT on the ndbinfo database through the MySQL interface to NDB or parsing the ndb_mgm output. I prefer the latter because maybe I’m using another frontend to the data (native NDB API, memcached etc) and I don’t want to maintain a MySQL server frontend just to check how much space I still have free.

So, you can use this script on GitHub

to parse its output and know if your tablesapce is OK, warning or critical. Feel free to post any comment, fork it and send patches! :)


/etc/hosts and the thousand-characters-long line

This is a self-note in the case I encounter another strange behaviour like this. We were experiencing a strange problem with MySQL and DNS. I was trying to do this:

$ mysql -h server.mysql
Unknown MySQL server host 'server.mysql' (-1)

but both dig and a normal ping (which in turns uses libc and nsswitch to do the name resolving) were working:

$ dig +short server.mysql

$ ping server.mysql
PING server.mysql ( 56(84) bytes of data.
64 bytes from server.mysql ( icmp_req=1 ttl=64 time=0.399 ms

and obviously connecting using the MySQL client and the IP address worked. So, what was happening? The smarter amongst you maybe already know the problem: a very very large line in /etc/hosts was driving the mysql client crazy (but not ping). Removing the “files” database fron the hosts entry in /etc/nsswitch.conf showed where the problem lied, and fixing the bad-ass line fixed the problem

Column names with trailing spaces in tables and views

Just found out a bizarre feature of MySQL: you simply cannot create a table with a trailing space in a column name. Something like this, doesn’t work:

create table a (`a ` int);
ERROR 1166 (42000): Incorrect column name 'a '

There’s a thread in MySQL’s lists explaining it

Ok, it seems there’s a technical limitation but this solution seems like a lazy workaround to me.
The problem is with CREATE VIEW. In recent MySQL builds you cannot either create a view with a trailing space in a column name. But recent means, at least, greater than 5.0.51a, cause this version DOES let you create a view with a trailing space.

# mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

mysql> create view b as select a `a ` from a;
Query OK, 0 rows affected (0.00 sec)

There’s a big problem here: 5.0.51a is the default and supported version in Debian Lenny! So, if you have Lenny as a master of a more recent MySQL installation, be careful cause a CREATE VIEW with a trailing space in a column name will break your replication. Grrrr. MySQL, I hate you, really.

MySQL slaves and the “corrupted” relay log/binlog problem

When trying to pull up to date a new slave, maybe using a binlog server (a MySQL server with no true tablespace, just acting as master for slaves, feeding them with replication data), you may encounter an error like this in your mysql.err file, after noticing the replication has just broken

091028 11:32:49 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
091028 11:32:49 [Warning] Slave: Field 'owner' doesn't have a default value Error_code: 1364
091028 11:32:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysqld-bin.000780' position 786513508

Well, everything on the web points to a “OMG, my file is corrupt!! My disk is freaking out!”. Probably, instead, it’s just simpler: you have forgotten a FLUSH LOGS; before backing up your binary logs from the original master. So, when you are recreating the binlog server from the backup, the final slave arrives to the last statement wich obviously is not complete/correct and booom, it stops.

The solution is to copy the correct and complete binlog file (in my case mysqld-bin.000780) again in the binlog server, then issue a
CHANGE MASTER TO master_log_file="mysqld-bin.000780", master_log_pos=786513508;

and… problem solved!
(remember to adjust file name and position to suite your needs)

Lesson learned (once again): always test your backups and do not hesitate to flush with MySQL :)

HOWTO: Install Mysql 5.1 for SPARC64 under Debian Lenny

If you happen to own a SPARC64 box, you’ll probably already know that even if the kernel is 64bit the userland comes from the normal SPARC Debian port, so it’s 32bit. Mysql is no exception, with all the 32bit limitations – mainly the 4GB RAM per process limit.

This is really¬† a PITA because if you have a SPARC64 box probably it has got plenty of RAM and you want to use it at its full potential, without having to messing around with Solaris (yeah, I don’t like it very much, I’m sorry).

This guide covers Mysql 5.1 installation in Debian Lenny, so we have to use SID repositories.

# echo "deb sid main" >> /etc/apt/sources.list
# echo "deb-src sid main" >> /etc/apt/sources.list

then let’s edit our apt preferences to avoid massive update on next dist-upgrade :)

# vim /etc/apt/preferences
Package: *
Pin: release a=stable
Pin-Priority: 900
Package: *
Pin: release a=sid
Pin-Priority: 100

and then update our repo list

# aptitude update

And here we go:

# apt-get build-dep mysql-server-5.1
# mkdir /tmp/mysql-build; cd /tmp/mysql-build
# apt-get source mysql-server-5.1
# vim mysql-dfsg-5.1*/debian/rules

here we touch a little the rules for compiling cause there are a couple of things that are not going to work by default.

The MAKE_J variable doesn’t work very well, so you can modify the grep to look for “CPU” instead of “processor” or you could hardcode it to the number of processor you have. This will make compilation a lot faster.

MAKE_J = -j$(shell if [ -f /proc/cpuinfo ] ; then grep -c CPU* /proc/cpuinfo ; else echo 1 ; fi)

then edit the CFLAGS variable because it’s used to compile some library that will ignore the environment variables we are going to set later in this howto.


it should be about line 73. Please note that -m64 will make it 64bit so it’s mandatory while the mcpu flag it’s to optimize the executable for your CPU. In my case it’s a niagara2 chip but you can use another CPU as well. Check the GCC documentation for more details
Save and quit and then we can start with the compilation process:

# export CFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export CXXFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export CPPFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export LDFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export DEB_BUILD_OPTIONS="nocheck"
# debuild -us -uc --preserve-env

that’s it. After some minutes (depending on your HW), you should have in /tmp/mysql-build all your new DEBs which you can install with dpkg -i. I advice to install the stock mysql-server-5.1 with aptitude before to get all dependencies installed, then you can use dpkg with your new DEBs.

Mysql and integers: INT, TINYINT and all that jazz

Today I stumbled on a discussion here where I work about what’s the best INT field in Mysql to represent boolean values and about what’s the real meaning of the *INT(number) definition.

So, I ended looking in the online Mysql manual for answer but also in the “High Performance MySQL 2nd edition” written by worldwide-fame MySQL hackers (and published by O’Really).

To resume, as Mysql Manual says: BOOLEAN is an alias for TINYINT(1), so you can use both, although BIT could be a better suited solution.

But what about the parenthesis thingie? Here there are two different opinions on the matter. According to High Performance Mysql’s authors (page 82, emphasis’ mine):

MySQL lets you specify a “width” for integer types, such as INT(11). This is meaningless for most applications: it does not restrict the legal range of values, but simply specifies the number of characters MySQL’s interactive tools (such as commandline client) will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20)

but now let’s look at this other page in Mysql Manual (emphasis mine):

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.

Now…who’s right? I use to trust in the Percona & OpenQuery crew but anyway the official Mysql Manual seems pretty clear about some cases in which the INT(x) value is important.
Any idea?

Kill every MySQL SELECT older than X seconds

If you’re managing large MySQL installations where lot of queries (written by lot of people) are executed, this is going to sound familiar.¬† Sometimes due to some faulty query or to heavy traffic, your MySQL server could be overloaded and queries start to stall there, making your PROCESS LIST grow and grow and grow…

So, here it is a quick script that kills every SELECT (we don’t want to kill other query types) older than a certain amount of second and that can help you when you find yourself in one of these situations.

It’s rather quick’n’dirty, but for it’s small use case, it works. It expects GNU userland

if [[ $SEC -lt 1 ]]; then
echo "Usage: $0 SECONDS"
exit 1
mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
if [ $qsec -gt $SEC ]; then
echo "Killing query $qid..."
mysqladmin kill $qid