Many people are caught out by MySQL’s implementation of GROUP BY. By default MySQL does not require that you GROUP BY all non-aggregated columns. For example the following is an illegal query in SQL Server (as well as rather nonsensical); ?View Code MYSQLSELECT * FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA; This will generate the following error; [...]

I’ve been busy setting up the Percona Nagios MySQL Plugins  but ran into an issue with the pmp-check-mysql-deleted-files plugin; UNK could not list MySQL’s open files After a little debugging we tracked this down to a problem caused by running multiple instances of mysqld. This is something the script author (Baron Schwartz) mentions in his [...]

Here’s a stored procedure I use to perform some simple benchmarking of inserts for MySQL. It takes three parameters; p_table_type which should be set to the storage engine you wish to benchmark i.e. ‘MyISAM’, ‘InnoDB’, p_inserts; set this to the number of inserts to perform. p_autocommit; set the autocommit variable (relevant to InnoDB only) to [...]

I’ve been meaning to check out Maatkit for a while now. Today I had a reason to as one of our MySQL slaves got out of sync with the master. I’d heard about mk-table-sync, a tool that synchronizes tables, so I thought I’d give it a shot. As it turns out it’s this easy; mk-table-sync [...]

Today I wanted a quick and easy way to generate a backup of the structure of all MySQL databases in one easy hit. Here’s a couple of ways you can do this with the tools you’re likely to find everywhere. Firstly, we can use the following query to generate a list of mysqldump commands. The [...]

Here’s a bash script that you can use to parse multiple MySQL Slow Query Log files, in one sweep, into something much more understandable. The script uses the handy utility mysqlsla so make sure this is in your path.  mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary and microslow patched logs in order [...]

If you encounter the following error when trying to create a partitioned table in MySQL Error Code : 1492 For RANGE partitions each partition must be defined Assuming you have defined your partitions then you probably have a syntax error. Take the following incorrect example. ?View Code MYSQLCREATE TABLE People ( PersonId INTEGER NOT NULL [...]

Just a very quick post today! If you encounter this error when attempting to drop a MySQL database; Error dropping database (can’t rmdir ‘./database’, errno: 39) Then you probably have some rogue files in the folder where the database files are located. If you cd into this directory you will be able to view these [...]

Not the sexiest blog title in the world but I thought I’d knock up a little post on the behaviour of MySQL and SQL Server with integer subtraction. How would you expect a database system to behave with positive and negative data types? Microsoft SQL Server doesn’t really have unsigned data types. All integer types [...]

I usually use SQLYog to write any stored procedures for MySQL. Whenever you alter a procedure the editor essentially generates SQL to drop and then recreate it. ?View Code MYSQLDELIMITER $$   USE `db`$$   DROP PROCEDURE IF EXISTS `my_proc`$$   CREATE DEFINER=`root`@`%` PROCEDURE `my proc`() MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT ‘Just an [...]