Finally ... MySQL workbench sucks less

MySQL workbench has been out for quite a while, under the guise of the people at MySQL. For the longest time, I stuck to using just the individual MySQL Query Browser and Administrator because they weren't too bad, and there really wasn't anything out there that I liked much better for query browsing alternatives. I tried out the old Workbench back when MySQL was standalone, but it really wasn't a very positive experience, so I just dropped it.

However, lately, something drove me to search for better alternatives to the MySQL query browser again, and I don't even know why. In my Google search, the MySQL Workbench came up, and I saw that it was a very recent version that was a good .2 versions up from the last one I had used, so I figured I'd give it a try. The difference was startling. Not only did they completely revamp the interface (at least for the Mac) but the workbench was just generally much more reliable and performant than the old query browser. If you get the chance, give it a shot. The new integrated interface is much more user friendly, and there's a bunch of new "Copy to clipboard" snippets that I personally find incredibly convenient and useful.

Quickly dropping all the tables in a MySQL database without dropping the database itself

I've recently come across a case where I need to drop all the tables in my database (ie effectively truncate the database) but MySQL has no built in command for doing so. This is where the magic of command lines becomes very useful. I found a great little trick here that will very quickly let you get rid of all that annoying data so you can load in new test data into your database :

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

If you've got GnuWin32 or another set of GNU programs installed on your Windows box, you can even do this in Windows without even changing the syntax !

Quickly dumping a MySQL database out to a file

I've found that sometimes, I just need a quick and dirty copy of a database to test changes against, and it doesn't matter if the data is recent, or even consistent for that matter. That's where mysqldump comes in handy with the --single-transaction option. It can be used on a live database because it doesn't lock the tables and prevent your web application from continuing to insert, modify and delete new records. One example would be :

mysqldump -u myusername -h myhost -p --single-transaction mydbname | gzip > mybackupfile.20100803.sql.gz &

This can be made even quicker by combining this dumping into an SSH transfer to copy the output data to another machine.

Piping a MySQL database from one server to another

I know that there's a lot of great, wonderful things that can be done on-the-fly through SSH. It's one of the greatest tools out there for moving data or communicating between two machines. So I thought "Why not try to move my database in the fastest way possible via SSH?", and here's the command I found :

mysqldump -ux -px database | ssh me@newhost "mysql -ux -px database"

This is of course a very basic, stripped down version of the command, which I haven't tested yet, but it's a good start to what seems to be a very common problem among developers.

Dumping just your schema with MySQL dump

A simple one-liner :

mysqldump -u root -p mydatabasename --no-data=true --add-drop-table=false > test_dump.sql

With this command, you'll be prompted for your root password. I got this from here. Simple

Using the MySQL EXPLAIN statement

Recently I had been having trouble with queries on a certain table in a system that I've been maintaining. I had gone through just about every excuse for why queries on the table could be performing so slowly : the machine was slow (DB running on a VM), the webserver was slow (also running on a VM), I wasn't using the native libraries (webserver was Tomcat), I had other processes running in the background (I didn't). Then I ran across a tip on a forum suggesting usage of the MySQL EXPLAIN statement. I had known all about it for the longest time, but it never occurred to me to actually use it (I think I'm that good at writing queries, turns out : I'm wrong). After using the EXPLAIN statement, I found out that the query processor was using a suboptimal query plan which utilized an index I had added with the intent of improving performance (the index had a fairly high arity, so choosing to use it was sketchy at best in the first place). Most DBMSs should have a similar functionality built in. I think that'll be the first place I go in future.

Stupid MySQL tricks

Recently, I found that our secondary server's MySQL partition was starting to get full. After running du -h --max-depth=1 several times recursively, I found that the source was the MySQL binary logs. As it turns out, MySQL comes with a very handy command for purging all log files. If you run :

show master logs

, you'll be given a list of the logs that the MySQL server is currently using. You can clear out old log files (some of which can be very large) by running the command

purge master logs to 'log-name-here -from-display'

. This will remove the old log files from the hard disk and save up some potentially much needed space.

Allowing external root access to your MySQL database

...is recommended against in the MySQL documentation, but it can be oh so handy for running scripts and adding databases, tables and views when you can't (or don't want to) log into that remote system on the command line to update it. However, when you try it (at least on a fresh Ubuntu install), you may be greeted with an Error 2003. This happens when your system does not allow root external access. To enable external access, you can run this query (as root) on your server from the command line (must be localhost) :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ('myrootpassword');

* EDIT * : The above statement does not necessarily copy all privileges over to the new entry for 'root'@'%' in the mysql.user table. A better way to accomplish full external root access is the following :

DELETE FROM `mysql`.`user` WHERE User = 'root' AND Host = '%';
INSERT INTO `mysql`.`user` (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) (SELECT '%', User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections FROM `mysql`.`user` WHERE User = 'root' AND Host = 'localhost');
FLUSH PRIVILEGES;

Setting up MySQL server on Ubuntu

...is really easy. Run the command :

apt-get install -y mysql-server

Once that's done, you'll need to perform some additional configuration and setup the root password. My added configuration involves making table name comparisons all lower case (convenient for queries) and making the server run entirely in utf8. In order to configure the server as such, you'll need to make the following changes :

Add the following lines to the [mysqld] section of the my.cnf configuration :

default-character-set=utf8
default-collation=utf8_general_ci
lower_case_table_names=1

Add the following line to the [mysql] section of my.cnf (note that this is not the same as the [mysqld] section, the difference being the 'd' on the end) :

default-character-set=utf8

In order to have the Ubuntu MySQL installation properly run on external hosts, you'll have to comment out the line that looks like :

bind-address     127.0.0.1

After you've configured the my.cnf file, you'll have to stop the server, and restart it with --skip-grant-tables enabled so that you can have free, unfettered access to the system with privileges disabled. This is required so that you can set the initial root password. Run the following commands as root :

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables --skip-networking &

The latter command will start the MySQL server with privileges disabled.
Then log in with the command

mysql -u root -h localhost

Once logged in, run the command :

UPDATE `mysql`.`user` SET Password = PASSWORD('thepassword') WHERE User = 'root';

This will set the user password to 'thepassword' (though I recommend you don't use that exact password). Once you've set the root password, exit mysql and run the command

/etc/init.d/mysql restart

This will restart the server as usual, so that you'll no longer have unlimited privileges (and neither will anyone else).

Configuring MySQL for table case-insensitivity

While trying to add a recent update to our systems, I noticed that our demo system wouldn't start, which was very strange considering I had no problems on my local test system. I eventually traced the problem down to MySQL not being able to find certain tables. This was because I had created the tables in a different case than the Quartz library was expecting. My local testing box is a Windows box whereas our demo and production systems run Linux, the difference being that my Windows MySQL installation has case-insensitivity on by default, whereas the Linux installations do not. In order to remedy this, I had to explicitly add a line to my my.ini / my.cnf configurations :

lower_case_table_names=1

You can determine the case-sensitivity of your own MySQL installation by logging in with the command line client or running the query browser and entering the following command :

SHOW VARIABLES LIKE 'lower_case%'

The greatest MySQL companion ever

I just recently found out about the 'show processlist' command in MySQL that lets you get a resultset of all the processes MySQL is currently using and what query they're running. Today I was working in top monitoring the performance of our servers as I restarted our production server. MySQL popped up briefly on the top list of running processes and it occurred to me, "Wouldn't it be great of there was something like this for MySQL?". So while I was waiting, I popped open google, and ran a query for a MySQL and top. To my surprise, up came mytop. It's literally top for MySQL and it's so ridiculously useful you wouldn't believe it. Even better, it's got a .deb made for it and it's in the Ubuntu repositories, so installing it is as simple as running :

apt-get install mytop

It's a good day today :)

Finding out what MySQL is doing

I was recently updating our production system when I noticed that our main MySQL database was going unusually hard. On a quad-core processor system, it was using up three processors simaltaneously. This prompted me to find out what MySQL is doing, and after being unable to read the logs, I did a Google search and ran across this useful little command :

show processlist

This runs a query (can be run from either the command line or the query browser) and shows you a summary of all the processes that MySQL is currently using.

More random MySQL glitches

I ran into the following exception recently when moving an application from one server to another :

java.lang.NullPointerException
 at com.mysql.jdbc.StringUtils.indexOfIgnoreCaseRespectQuotes(StringUtils.java:959)
 at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1296)
 at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3670)
 at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:702)
 at com.mysql.jdbc.CallableStatement.(CallableStatement.java:513)
 at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4422)
 at com.mysql.jdbc.Connection.prepareCall(Connection.java:4496)
 at com.mysql.jdbc.Connection.prepareCall(Connection.java:4470)
 at org.apache.commons.dbcp.DelegatingConnection.prepareCall(DelegatingConnection.java:275)
 at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareCall(PoolingDataSource.java:292)

As it turns out, there was a permissions issue whereby the function was created with a line specifying the definer and this causes the 'SHOW CREATE FUNCTION' call to fail and return null unless the user calling the statement has permissions for select on the 'mysql' database. Some recommended solutions (that I didn't get around to trying) were changing the call to 'prepareStatement' as opposed to 'prepareCall'. The issue is logged as MySQL Bug #24065. Lately it just seems as if I keep hitting every bug out there, and can't get anything done. I hope this helps somebody.

Cool MySQL tricks

It's been a while since I've written a blog post, and I figured this was a good topic to write about. For the most part, I don't use SQL. There's numerous ORM (Object-Relational Mapping) tools and frameworks out there (ie Hibernate) that abstract away all the boiler-plate CRUD code. When I do have to use SQL (be it on the command line or with a query browser), I'm not afraid to dive right in, and it's interesting to learn new things about SQL because there's so much to learn and it's such a ridiculously powerful query language.

I had to update the configuration in one of my databases, but the configuration required inserting numerous new lines into a table for user roles. I could've written a stored procedure to do it, but given my (admittedly somewhat limited) knowledge of SQL, I figured this would take too much time and I didn't want to do it. (This is what I like to call constructive laziness). I kept Googling and found MySQL's INSERT INTO ... SELECT syntax, which allows you to conveniently insert the results of one query into another table. I'm sure that most other database implementations out there have a similar convenience syntax, but I happen to work with MySQL so there you go. It took what would've been a numerous line script with looping and separate SELECT and INSERT statements down to a one liner. If you use MySQL (especially with LAMP / AJAX) I recommend that you read about it.