Thursday Oct 29, 2009

Makin changes to many tables at once

This question came up in Freenode's #mysql today

help | how can i truncate all the tables in my database in one go ?

I have had this question a few times from #mysql and from customers. Truth is, there is no simple or official way to do this in MySQL - some third party utilities let you do this - such as phpMyAmin - but one trick I commonly employ is making use of some SQL queries and the INFORMATION_SCHEMA database to generate the commands I need for me.

Within the INFORMATION_SCHEMA database, there is a table called, oddly enoguh, 'TABLES'.. In 'TABLES' we find columns representing the database and table names - as well as some other interesting information such as the engine type. With the use of the mysql command line tool and 'CONCAT' we can exploit this to generate queries for us.

You can read more about INFORMATION_SCHEMA here:

For example, to solve solofight's problem, we can run the following query:


This gives us some output like this (note in this example I only have one table, the benefit here is it generates commands for all tables, even if there are hundreds of them

| cmd |
| TRUNCATE TABLE `test`.`test`; |
1 row in set (0.00 sec)

However, in this form we need to do some editing.. in the past I have used 'vim' to do this, some simple column-based deletes (deleting the first few characters of every line) and some simple regexes solved it - but we can expand on this with the mysql command line utility to output it in a better form.

If we pass two options, "batch" and "skip-column-names" it removes the table formatting (-, |, etc) and removes the header line with column names

shell> mysql -u root --skip-column-names --skip-line-numbers -e 'SELECT CONCAT("TRUNCATE TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "`;") AS cmd FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "test"'

Then we get the following output, simple and plain. This can be pasted straight into mysql, or piped in (careful doing that, though! always good to check the output first) or saved to a file and sourced later.

TRUNCATE TABLE `test`.`test`;

You can imagine this has many uses - another case I have used personally is some stray tables had been created as MyISAM - and I wanted them all InnoDB. So I made a query to make that changes for me.

In this case, we need to select on ENGINE=MyISAM.. but we also want to excude two databases - mysql since MySQL will get very upset if you change it's own tables to InnoDB, and information_schema since it is simply not possible to change their type - they are virtual tables.

mellie:~ lathiat$ mysql -u root -B --skip-column-name -e 'SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "` ENGINE=InnoDB;") AS cmd FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != "mysql" AND TABLE_SCHEMA != "information_schema" AND ENGINE = "MyISAM"'
ALTER TABLE `blah`.`host` ENGINE=InnoDB;
ALTER TABLE `test`.`test` ENGINE=InnoDB;

So, next time you need to perform operations on a bunch of tables at once - consider this option!

Tuesday Sep 01, 2009

Must-have / essential applications on Mac OS X (10.6 - Snow Leopard)

This is a bit of a side-step from directly MySQL related information .. but I found myself installing Snow Leopard on my MacBook Pro today (which I use for pretty much everything) and every-time I do I have to try and remember all the applications I use. And I thought I would share them as I've found it useful you can pick up on things other people use. So here it is!

I have marked the applications which cost money with a green dollar symbol ($) and hyperlinked all the project names for convenience.

  • iLife 2009 - iTunes, iPhoto, iMovie $

  • Built-in Mac Applications - Mail, Safari, Terminal

  • iWork 2009 - Office Suite - Pages, Numbers, Keynote $

  • TextMate - Text Editor $

  • VisualHub - Video Conversion - Use this for converting videos to watch on my iPhone/iPod touch .. no longer for sale but apparently you can download it for free now $

  • Transmit - FTP client $

  • VMware Fusion - Virtualization - for testing and Windows $

  • World of Goo - Game - Possibly the most awesome game ever (via Macheist) $

  • Skype - VoIP - We use SkypeOut alot in the support team for calling customers world-wide.

  • Adium - Instant Messaging - MSN, Google Talk, Jabber

  • X-Chat Aqua - IRC - used for work, prefer a graphical client with sound and growl capability for work.. use irssi on a remote server for personal IRC

  • MacPorts - BSD Ports-style for Mac - Applications: bzr, mtr

  • Growl - Desktop notifications - also Prowl for forwarding them to my iPhone

  • Perian - Video Codecs - for Quicktime

  • VLC - Video Player - I use this for watching most videos anyway

  • Cisco VPN Client - Used for work (not freely distributed)

  • Arduino - For all my Arduino electronics hacking.. for more details see my Geek My Ride page

  • EAGLE - PCB Design

  • SeaShore - Image Editor - based on the GIMP, but was a Mac UI.. great for simple edits.

  • iStat Menus - Menubar System Stats - not yet working on Snow Leopard (will soon) - highly recommended... also lets you replace the menu clock with one that drops down to show a calendar and other timezones.

That's it! I hope some of you find some useful applications in there.

- Trent

Tuesday Aug 25, 2009

Watch out for hostname changes when using replication!

For one reason or another, many times we find ourselves changing the hostname of a machine. It's been repurposed or moved - or perhaps the original installer didn't know what name it should have. To achieve this on most modern Linux distributions there are 2 key files you need to update.

  1. /etc/hostname needs to be updated with correct hostname to be set on boot, and

  2. /etc/hosts needs to be updated for DNS lookups of the local hostname. This is more important than you might think and will break many applications if not updated.

Some people also take the third step of updating the hostname on the fly with the 'hostname' tool, which if you do that means the gotcha I'm about to describe take you completely unaware in some weeks or months.

If you are using MySQL replication, there are two key options which depend on the hostname. These are the 'log-bin' and 'relay-log' for the binary log and binary log replication log paths respectively. The problem is not only do the logs themselves depend on the hostname, so does the index which tells you where to find them. So if you restart the server, it will look for a new index file and won't find it - causing errors such as:

090825 17:17:15 [ERROR] Failed to open the relay log './mellie-relay-bin.000002' (relay_log_pos 339)
090825 17:17:15 [ERROR] Could not find target log during relay log initialization

There are several possible solutions to this, one involves combining the old and new files (which you can find documented here - but that's a more pro-active approach. The second is to completely restart the replication process - which in my opinion, is cleaner. So I will detail that approach.

First we need to stop the slave process, just to make sure.

mysql> STOP SLAVE;

Then we can get the slave status, to see what position the master is currently. You can see what that looks like here. The important values to note are the following two:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Master_Host: localhost
Master_User: root
Master_Port: 3306
Relay_Master_Log_File: gbichot-bin.005
Exec_Master_Log_Pos: 79

These values tell us what the current position in the master's binary logs the slave has executed up to as well as the basic master details. The reason this is important is we are going to tell the slave to completely forget about it's current replication information and fetch the data fresh.

While normally you could just change the master log file and position, since it can't open the relay log at all - the slave replication does not start and we must completely reset and specify all of the details again. The above information contains everything you need except the password for the replication user. You can find that by reading either the '' file or by prior knowledge.

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

To reset that, we run RESET SLAVE like so:


Then we run we need to construct a CHANGE MASTER statement with the above information.

In our case the statement is

mysql> CHANGE MASTER TO MASTER_HOST="localhost", MASTER_USER="root", MASTER_PASSWORD="test", MASTER_LOG_FILE="gbichot-bin.005", MASTER_LOG_POS=79;

... but be sure to use your own values from the SLAVE STATUS and make sure the log and position are from Relay_Master_Log_File and Exec_Master_Log_Pos - there are other values that look similar so don't confuse them. Once this is done we can start the slave, and check the status to make sure it is replicating correctly.

\*wait a few moments\*

When the slave status is displayed, make sure that both the IO and SQL threads are running and there are no errors.

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 8

Also keep an eye on the Seconds_Behind_Master value and make sure that it is reducing to 0.

That's this weeks situation. If you do change your hostname, it pays to reboot your machine to make sure everything has worked and that issues will not pop up down the track when your machine is unexpectedly rebooted by a crash or some other circumstance. You don't need unexpected changes causing problems!

This applies to any situation where you might change a configuration file, but making it's effects current are done separately. This is very common in MySQL, where you might want to change a dynamic variable and also edit the my.cnf file. If you make a syntax error, you won't know until your server reboots. So it helps to be very careful and preferably do the actual MySQL or server reboot.

Tuesday Feb 10, 2009

Today's tip: The little known AS112 project

I ran into a problem today where a web application and a suite of servers relying on a MySQL server were not responding, or responding very slowly.

Upon investigation of the issue, we found a high number of "unauthenticated connections" in the MySQL process list. A common cause of this is that MySQL cannot lookup the reverse DNS of the servers connecting, but it was strange this "suddenly" started happening. The servers in question used private IP addresses in the range 10.10.10.x.

The problem with this is if you do not setup a local zone for "10.10.10.x" reverse DNS, and poison your local caches to force those zones to be looked up locally, it heads out to the global internet to try and find a DNS server responsible for the 10.10.10.x reverse DNS (this translates into DNS name

Because the root servers spent a lot of time fielding requests for these zones specifically with NULL replies, it was taking a lot of resources and putting pressure on the root name servers. Thus, along came the AS112 project (

This project was setup to make a distributed network of servers that will answer requests for these zones (specifically, the reverse DNS for all RFC1918 private addresses). The root servers "delegate" these zones off to the project and thus DNS caches around the world will remember that it was delegated and stop interrogating the root servers for all of these lookups.

AS112 uses a routing method called "Anycast" where multiple servers will advertise to the internet that they own a single IP address, for example And then through the natural routing processes of the internet, it will find the closest server to respond to it's request. In this way the load of these queries can be spread among multiple servers on the internet, it will also find a more local server, with less latency, which means the DNS responses are faster.

What happened for this particular site, is the local AS112 server had gone off-line, and there are no others to field the queries. Thus rather than the convenience of the AS112 sending back negative queries, the queries were now timing out trying to reach an anycast server ( which was not available.

MySQL's DNS resolver can take some time to timeout and thus held up all of the connections and made the application "very" slow .. so much that actually all the servers stopped responding to any application requests in any useful amount of time.

So now we know why this problem occurred, how do we solve it?

This problem is not MySQL specific, but very often MySQL is one of the few places it is really noticed as a connection will not be accepted until the DNS is resolved which may take a long time, and many applications dependent on MySQL are latency-sensitive. Users do not wish to sit for 30 seconds while their web-pages load, for example.

So, to fix this problem at a global level, you need to reconfigure your local CACHE dns server to respond to these queries.

In the case of most BIND DNS servers, you can simply setup a blank zone for Alternatively you can actually populate the zone with the correct information, but a blank zone will at least prevent the delays above.

For DJBDNS' dnscache, you need to add a file /service/dnscache/root/servers/ and in that file you can specify the IP address of a DNS server to speak to for this zone. You will then need to setup either a DJB tinydns or some other DNS server to respond to these requests on the IP(s) you entered into that file.

Note that depending on your setup you may also need to setup zones or or whatever other private IP range you make use of.

To solve this problem specifically in MySQL, you can configure MySQL with the 'skip_name_resolve' option, which should be placed in the [mysqld] section of my.cnf. This will prevent the server from attempting DNS lookups on connect.

There are two side effects to this option, the first is your "PROCESSLIST" output will not include hostnames of connected users, and only IP addresses. The second is if you have any MySQL GRANTs based on a hostname, these will no longer work as the hostname is not looked up. All grants must use IP addresses, 'localhost' or a simple '%' wildcard.

In this case we decided to go for both approaches.

In summary, always make sure that all of your servers have an authoritative DNS server for both your local reverse DNS for LAN addresses, and also the search domain you have in your resolv.conf (for similar reasons).

Monday Jan 19, 2009

Arrived at, Hello Planet MySQLers

Greetings Planet MySQL readers,

I am new to Planet MySQL.. so I thought I should introduce myself.

I am Trent Lloyd (some may know me online as 'lathiat'), based in Perth, Western Australia and presently working for Sun Microsystems as a MySQL Support Engineer providing support to Sun's MySQL customers. I have been with Sun for 12 months, and previous to that was working for MySQL AB before it was acquired for 8 months. My background before that is in the ISP industry working for HostAway in a combination System/Network administration and support role.

I have also given a number of papers, often related to either Avahi or IPv6 at a few conferences.. mainly - you can view them on my web-site

I have an open-source/free-software community background.. I co-authored Avahi (a mDNS/DNS-SD/Link-Local IP stack for Linux and other \*nixes) with Lennart Poeterring and dabbled my hand in the likes of the Ubuntu MOTU and GNOME communities.

Working in the MySQL Support Team, and having a very large customer base to source problems from, we run into many problems and gotcha's on our customer systems which people don't hit every day but are very difficult to track down when you do. I hope that by sharing some of these experiences

With that introduction out of the way, I have just landed in Hobart, Tasmania, Australia to attend (which was a very last minute arrangement). I have attended this conference every year since 2003 (thats 7 of them!), it's always great and I am glad I am able to attend again.

I am sitting at the "Open Source Databases" mini-conference which has a strong MySQL presence. There are quite a few Sun folk here, which is fantastic - unfortunately a few others were unable to make it but it should be great never the less.

I will be bringing you more updates throughout the week and MySQL tips into the future!


This blog is a set of tips, tricks and articles from Trent Lloyd - MySQL Support Engineer @ Sun from Perth, Australia working in the APAC team.


« October 2016