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 [...]

I’m quite often jumping between MySQL and SQL Server so remembering the quirks and limitations of each system can be difficult. With MySQL, if you attempt to reference a temporary table more than once in the same query, you will encounter the following error; Error Code : 1137 Can’t reopen table: ‘t1’ The following provides [...]

I’ve previously blogged about the limitations of MySQL Alter Procedure Syntax and I came across a thread on the MySQL forums with a possible solution. I thought it might be handy to wrap this up into a stored procedure akin to SQL Server’s sp_rename. This procedure will allow you to easily rename MySQL Stored Procedures [...]

From time-to-time you may need to manually purge binary logs on your MySQL slaves to free up a bit of disk space. We can achieve this by using the PURGE BINARY LOGS command from the MySQL command line client. MySQL advises the following procedure when purging these logs To safely purge binary log files, follow [...]

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 [...]

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 [...]

Colleague: “Can you have foreign keys referencing other databases?” Me: “Erm… I don’t know, but it I’ll find out.” I’ve always thought tables with these relationships should exist in the same database so I’ve never attempted this. But I don’t like not knowing things so I set to find out. The answer, as it often [...]

I’ve been building utilities with PHP and MySQL command-line tools to clone databases. I ran into an issue when exporting data from multi-gigabyte tables using the mysql client program. mysql: Out of memory (Needed 4179968 bytes) ERROR 2008 (HY000) at line 1: MySQL client ran out of memory The fix for this is easy; just use [...]

Now I’m working mainly with Linux and MySQL I’ve had to learn how to accomplish basic tasks in entirely new ways. As a DBA I like to keep an eye on disk space. I wanted something like my Check disk space with Powershell script, but this only works with Windows, so naturally I turned to [...]