Last week one of my collagues came to me with the following problem. He was not able to create an innodb table. MySQL returned ERROR 1005: Can’t create table (errno: -1). Most of the times this problem is a foreign key related issue but now this wasn’t the case (Foreign key error will return errno: 150). What actually happened is that ibdata already contains the registration of the new table but the frm file wasn’t there. This way when he wanted to create the table the server responded with error. Although he wasn’t able to remove the table either. It was caused the file-by-file cloning of the database.

For better understanding you have to be familiar with the inner operations of MySQL. With the most simplistic explaination I could say that MySQL is just working on a high SQL abstraction level which can use different storage engine for storing datas. Viewing from this aspect MySQL doesn’t care how the files …

On Thursday I went to the Harmony conference arranged by Oracle User Group Finland to speak about Galera clustering. (They chose the topic based on my suggestions.) The slides are now available on SlideShare. I'm pretty satisfied with this talk myself, the slides contain the most important steps you need to know to get started, but also the internal architecture of Galera, how it works, and what kind of replication topologies and load balancing you would want to use with it. And benchmarks of course.

read more

The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions.

XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses.

Internal XA Transactions are used to keep the binary log and InnoDB in sync.

Demo 1: XA Transaction on 1 node:

mysql55-tokudb6> XA START 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01');
Query OK, 1 row affected (0.01 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+--------+
| id | name   |
+----+--------+
|  3 | demo01 |
+----+--------+
1 row in set (0.00 sec)

mysql55-tokudb6> XA END 'demo01';
Query OK, 0 rows affected (0.00 sec) …

Don't forget to join us Tuesday for the MySQL Community Reception.
While the webcast users will have to miss you those who attend will have a great afternoon packed with great sessions , BOFs, a keynote by Jeremy Cole, DBA Team Manager, Twitter and of course the MySQL Community Reception.

You can still register to attend the event in-person !

One of our customers asked me whether it’s safe to enable the so-called USER_STATISTICS features of Percona Server in a heavy-use production server with many tens of thousands of tables.

If you’re not familiar with this feature, it creates some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful information. It lets you run queries to see which tables get the most reads and writes, for example. Another popular use for it is to find unused indexes.

I knew that some of our customers were using the feature enabled in production all the time, and I knew that Google and Facebook and others (the original developers) did also. But I didn’t know the …

This article describes a single MySql function to sanitize strings. The provided options are:

• replace multiple spaces to a single space
• remove Unix and Windows linebreaks and replace them with a space
• remove tabs and replace them for a single space
• remove multiple backslashes
• completely remove htmlentities (&)
• replace htmlentities with the corresponding character
• remove all HTML tags

Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.

The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.

A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.

garfield ~ # lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)'
COMMAND   PID  USER   FD   TYPE      DEVICE  SIZE/OFF     NODE NAME
mysqld  30257 mysql  cwd    DIR       253,1      4096 25346049 /data/mysql
mysqld  30257 mysql  rtd    DIR       253,2 …

As I discussed in the last post there is a virtual requirement for scripting ability in a large scale environment. The following is a simple script that could be used deploy scripts to pool of server.

--------------------------------------------------

#!/bin/bash

script=path/to/script/file.sql

for i in {1..100}
do
  (
  for dc in 'chi' 'la' 'sf'
  do
    for db in `mysql -u dba -h mysql${i}-${dc} -e "SHOW DATABASES"`
 do
      if [[ -z `mysql -u dba -s -rB -h  mysql${i}-${dc} $db  < $script` >> output/mysql.success ]]
      then echo p-cs${i}-${dc} $db >> output/mysql.broken;
      fi
    done;
  done;
  )&
done;

This isn't …

I've been involved in a number of interviews over the last few weeks as a client has been loking for a MySQL DBA. When you are looking for position as a DBA in a large scale environement there are some very important things you have to know.

You absolutely must know a scripting language. In a smaller environment this often isn't necessary. You will live and die by this in a large environment. I asked every applicant one specific question..if you had to change a mysql server variable on a pool of 100 mysql servers how would you do this? It's easy when it's one,two or even a dozen servers. just log in, change the my.cnf and change it "on the fly" if you can. Restart mysql if you can't.

You going to do that to 100 servers? It will take all day and be prone to failures. Scripting is the key here. Even just bash shell scripting can be very powerful. In another post I will cover a simple bash script to loop through a …

In a discussion today about backup strategy it was brought up that restorations of the backups are not made unless there is an emergency.

Huh.

So what happens when you have an emergency and find out that the backup wasn't made properly? Maybe a filesystem was corrupted. Maybe the wrong option was used for the backup tool. Who knows.

It is critical that you perform periodic, planned restorations of your backups. My recommendation is that you script a weekly restore of a full backup and AT LEAST quarterly you manually take one of your backups and perform a restore and then verify that it is working properly.

You will thank me later.

km