Emulating The Missing RENAME DATABASE Command in MySQL

I'll approach this topic from another direction.

I had to perform a task which required moving tables between schemas on the same MySQL server.

One way of doing this would be:

1. Dumping the original table to disk.
2. Creating new empty table on the target schema.
3. Importing table from disk into the target schema.

But, a simpler way exists!

The manual entry for RENAME TABLE casually describes this feature:

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

Which works almost instantaneously and does what it is supposed to do. This command could also be called MOVE TABLE.

Using this command, it is easy to emulate the missing RENAME DATABASE command.

1. Create the new schema.
2. Perform a query to generate the move commands from the source to the target schema:
   

   SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name,
   ' TO ','new_schema.',table_name,';')
   FROM information_schema.TABLES
   WHERE table_schema LIKE 'old_schema';

   Run those by copying them from your editor, or whatnot. If you're using SQLyog, CTRL-L switches the results tab to text mode for easy copying.
3. Drop the old schema

Some notes:

If you're using InnoDB foreign keys, worry not. The constraints are kept across databases, and moving the tables does not affect them - no need to move the tables in any specific order.

The move command also moves the .idb files between the database directories, if you're using innodb_file_per_table.

Hope this helps.

A Free Text Editor for Very Large / Huge Files on Windows

Every once in a while a programmer finds himself in need of a tool that allows him to edit very large text files. By large, I mean several gigabytes. For DBAs it is common, especially if you’re using MySQL dumps a lot. What do you do if you’re doing this on Windows?
If you’re using Notepad++ or any other Scintilla derivatives, you’re out of luck - those editors are not cut out for this kind of work. Using Visual Studio also won’t work. There are some partial solutions just for viewing, like LTFViewer – but it cannot handle large files without line breaks, something common in MySQL dumps. So what do you do?
The answer is simple and somewhat unexpected – use Vim for Windows. It opens a file of any size effortlessly and almost instantly – then you can browse through the file like it was a small script - it just works.
Few things to notice:
For files with very long lines, like dumps, consider using “set nowrap”.
Another thing is the fact that Vim keeps a temporary copy of the file for backup when saving an edited file, so not only it takes a while to save since it writes the entire buffer to the disk, but you also need to delete the temp file afterwards. To disable this behavior, use “set nobackup”.

Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005

Sometimes, the small things make all the difference. Last month, a friend of mine that works at Intel asked me how to do something seeming easy – concatenate the rows eliminated during a GROUP BY and present them in a column of their own. This is actually something typical when trying to do all sorts of reports, and just makes thing easier for everyone.
The answer? GROUP_CONCAT of course. This could have been easy. Alas, they were working with SQL Server 2005, which does not have such a function.
Now, all of the above isn’t exactly news – there are tons of articles and blog posts about it on the web. Some give you the code for special UDFs and some give you solutions that use cursors. A 3 year old post by Xaprb suggests using local variables.
I suggest a different approach based on a “hack” which utilizes the new xml functions in SQL Server 2005 to concatenate the values in a column.
It goes like this:

SELECT my_column AS [text()]
FROM   my_table
FOR XML PATH('')

This gives you the concatenated values of all of the values of this column in the table. From here on, it’s all just simple SQL. I have made two complete examples, in the order I’ve tried them. I’ve used the information_schema tables, since they exists everywhere.
First intuitive version:

SELECT table_name,
       LEFT(column_names,LEN(column_names) - 1)   AS column_names
FROM   (SELECT table_name,
               (SELECT column_name + ',' AS [text()]
                FROM   information_schema.columns AS internal
                WHERE  internal.table_name = table_names.table_name
                FOR xml PATH ('')
               ) AS column_names
        FROM   (SELECT   table_name
                FROM     information_schema.columns
                GROUP BY table_name) AS table_names) AS pre_trimmed;

Second version (admittedly inspired by this post, which I stumbled on after writing the first version):

SELECT table_name,
       LEFT(column_names,LEN(column_names) - 1)   AS column_names
FROM   information_schema.columns AS extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   information_schema.columns AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH('')
                   ) pre_trimmed (column_names)
GROUP BY table_name,column_names;

The second CROSS APPLY version is supposedly slower according to the execution plan, but I didn’t conduct any benchmarks at all.

I hope this saves someone several hours of work :)

Problems Uninstalling MySQL Connector .NET

This is a short post that might save someone some valuable time, if Google decides to rank it high enough.

I've tried to install a newer version of the MySQL Connector .NET, namely 5.2.3 instead of the old 5.1.3 I had installed.
When trying to install 5.2.3, I got this error message:

Apparently the connector does not support upgrades from 5.1.x to 5.2.x. We should just remove the old one.

Here lies the problem: when I tried removing the old 5.1.3, I got a weird error of which I took no screenshot. It consisted of a blank error message showing a computer screen with a icon of a moon on it. Something resembling a "sleep mode" icon. Huh?

Anyway, to make a long story short, nothing I did to remove the 5.1.3 gracefully worked. I tried using the old .MSI file, tried reinstalling, changing the install configuration, editing registry keys... I also tried using msizap.exe. Nothing worked.

The solution was to use this tool from Microsoft - called "Windows Installer CleanUp Utility". It's supposed to be a wrapper for msizap, but it did the job, while msizap did not.

Regardless of MySQL, this is a good tool to be familiar with. It removed 3 other entries in my "Programs and Features" list that refused to leave by themselves.

Update: I've had to solve this problem a few more times since posting. As it seems, this "Windows Installer CleanUp Utility" is basically a wrapper for msizap, so it probably used some other parameters that made it work. In addition, I've encountered a time where none of the tools worked, but a registry "Seek & Destroy" for "MySQL Connector" did the job. Go figure...

Recommendation: PuTTY Connection Manager

Linux desktop users: this post is probably not for you. You've got like a gazillion of these tools available.

Windows users: If you're like me, and use PuTTY all the time to manage your Linux MySQL servers, you'll appreciate this gem. It's called PuTTY Connection Manager and it, obviously, manages PuTTY connections. Not a very creative name, but a fine piece of software.

It's actively developed (latest alpha version out about two months ago), and boasts these features (taken from the developer website):

• Tabs and dockable windows for PuTTY instances.
• Fully compatible with PuTTY configuration (using registry).
• Easily customizable to optimize workspace (fullscreen, minimze to tray, add/remove toolbar, etc...).
• Automatic login feature regardless to protocol restrictions (user keyboard simulation).
• Post-login commands (execute any shell command when logged).
• Connection Manager : Manage a large number of connections with specific configuration (auto-login, specific PuTTY Session, post-command, etc...).
• Quick connect toolbar to quickly launch a PuTTY connection.
• Import/Export whole connections informations to XML format (generate your configuration automatically from another tool and import it, or export your configuration for backup purpose).
• Encrypted configuration database option available to store connections informations safely (external library supporting AES algorithm used with key sizes of 128, 192 and 256 bits, please refer for the legal status of encryption software in your country).
• Standalone executable, no setup required.
• Completely free for commercial and personal use : PuTTY Connection Manager is freeware.

 Things that I personally liked about it:

1. It's fast, since it's only a reasonably lightweight wrapper for the already fast PuTTY.
2. It can save a portable connection database in a file, unlike PuTTY, which uses the registry for it's connections.
   
3. It looks and feels like the Visual Studio window manager, so you can arrange and dock your PuTTY windows in all sorts of creative rectangular ways.
4. It is relatively feature rich, but it doesn't get in your way, and just works most of the time.
5. It's completely free.
   

Get it here.

Alternatives:

If you don't like it, there is another alternative that I personally like less for SSH-only work, which is called mRemote. It is much more advanced, has support for VNC and RDP, but I prefer the simpler and more flexible interface of PuTTY Connection Manager.

How to Truncate All or Some of the Tables in a MySQL Database

Truncating all tables in a database is a common problem which arises a lot during testing or debugging.
One of the most common answers to this question is to drop & recreate the database, most likely utilizing the shell. For example, something like this:

mysqldump --add-drop-table --no-data [dbname] | mysql [dbname]

This dumps the entire schema structure to disk, without dumping any data, and with commands for dropping existing tables. When loading it back into mysql, it essentially truncates all the tables in the database. Basically, this is a decent solution for many uses.

We had a requirement for a solution that needed these additional features:

1. Does not require shell (We work with both Linux and Windows)
2. Resides inside the MySQL Server (To minimize outside dependencies - for example - the mysql command line client)
3. Can truncate only specified tables using a some sort of filter

In other words, the main requirement here is encapsulation and simplicity of use for the developers.
Basically, this means I've got no other choice except writing a stored procedure for this purpose, ugly as it may be. Specifically, this will require using the pseudo-dynamic SQL in MySQL (more about this later) and a server-side cursor.

The code of the Stored Procedure:

DELIMITER $$
CREATE PROCEDURE TruncateTables()
BEGIN
DECLARE done BOOL DEFAULT FALSE;
DECLARE truncate_command VARCHAR(512);
DECLARE truncate_cur
 CURSOR FOR /*This is the query which selects the tables we want to truncate*/
  SELECT CONCAT('TRUNCATE TABLE ',table_name)
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'prefix_%';
DECLARE
  CONTINUE HANDLER FOR
  SQLSTATE '02000'
   SET done = TRUE;

OPEN truncate_cur;

truncate_loop: LOOP
 FETCH truncate_cur INTO truncate_command;
 SET @truncate_command = truncate_command;

 IF done THEN
  CLOSE truncate_cur;
  LEAVE truncate_loop;
 END IF;

 /*Main part - preparing and executing the statement*/
 PREPARE truncate_command_stmt FROM @truncate_command;
 EXECUTE truncate_command_stmt;

END LOOP;
END$$

DELIMITER ;

This is a generalized version, you can (and should) modify it to suit your needs.

Regarding dynamic SQL. Initially I've tried to make this query a lot simpler, and use something along the lines of the previous blog post. This would look something like:

DELIMITER $$
CREATE PROCEDURE TruncateTables()
BEGIN   /*Build a long user variable containing the varchars*/
 SELECT GROUP_CONCAT('TRUNCATE TABLE ',table_name SEPARATOR ';')
 INTO @truncate_command
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'prefix_%';

 SET @truncate_command = CONCAT(@truncate_command,';');

 PREPARE truncate_command_stmt FROM @truncate_command;
 EXECUTE truncate_command_stmt;

END$$

DELIMITER ;

The main (and unsolvable) problem with this code is that the PREPARE cannot prepare more than one statement. In other words, while in other databases you could have sent an entire batch (real dynamic SQL), in MySQL the only way to do this is to run statement by statement. On the other hand, there is no GROUP_CONCAT() in other databases (even though it can apparently be created with ease in PostgreSQL).

The second and less worrying problem is the fact that by default, GROUP_CONCAT() result tends to be limited to a certain length. This is easily solved by setting group_concat_max_len to something higher than default.

End Transmission.

How To Perform a Connection Massacre

Occasionally, you find yourself in the need to kill problematic connections to the database.
Usually if it's only one or two connections, you can use the combination of "SHOW PROCESSLIST" command to identify the problematic connection ID, and run a "KILL ID" command.

What do you do if you need to kill 10 connections? Or 56? I wouldn't want to type in all those kill commands, it's just dirty work. What we need is a more neat manner to perform those kills. Mass kill, if you wish.

Alternative way: use the INFORMATION_SCHEMA's PROCESSLIST table, to construct the kill statements semi-automatically.

SELECT CONCAT('kill ',id,';') AS kill_list
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE command='Sleep';


This select will return something like this (when using the command line client):

+-----------+
| kill_list |
+-----------+
| kill 28;  |
| kill 1;   |
+-----------+
2 rows in set (0.04 sec)

You can also use GROUP_CONCAT() to get the kill commands in the same line, which may be more useful when you can't copy&paste easily:

SELECT GROUP_CONCAT('kill ',id SEPARATOR '; ') AS kill_list
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE command='Sleep';

Returns:

+------------------+
| kill_list        |
+------------------+
| kill 28;,kill 1; |
+------------------+
1 row in set (0.03 sec)

Note, that you can use any filter in the WHERE clause, such as "WHERE db IN('dbname1','dbname2')", "WHERE user = 'appuser'" or "WHERE time>600". If you got any more clever uses, feel free to post them in the comments.

Now, all needed is to copy&paste those kill commands into the mysql command console (whichever you're using: GUI, command line, etc) and you're done.

Since most programs are kind enough to format the output in some way that prevents convenient copying/pasting, here's another tip. The mysql command line client can be asked to strip some of the output by using the "-s" optional parameter, which stands for "silent". You can use it once, and remove the ASCII art, timing and row count, and if you use it again (-s -s), you only get the actual rows.

An example doing just this (copy&paste friendly!):

mysql -s -s -e "SELECT CONCAT('kill ',id,';') AS kill_list FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command='Sleep';"

Gets you this (with different numbers of course):

kill 28;
kill 1;

Another way to do this, is to use SQLyog or any other GUI for MySQL, which can output the results in text. Most of them have this as a configurable option (Ctrl-L keyboard shortcut in SQLyog).

Hope this is will save someone some precious time.

Update:
I didn't include a suggestion on doing this automatically, since I personally prefer to run kill commands manually most of the time. Plus, some of the comments gave me an idea. Why not just pipe the stripped kill_list into mysql?

mysql -s -s -e "SELECT GROUP_CONCAT('kill ',id SEPARATOR '; ') AS kill_list FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command='Sleep';" | mysql