How to Install innotop 1.8.0 on CentOS 5

innotop is a fine tool that every MySQL DBA should be familiar with. Although it takes a while to get used to, it's worth it. It's less than intuitive for most people, unless you grew up in Linux.
The other problematic part except the learning curve is the installation. It took me a while last time I had to install it, and this time re-installing it I've decided to write down the steps needed.

Most of the trouble I went through the first time around were with CPAN, and finding out that those Perl modules were available in RPM form saved me lots of time. Avoid CPAN if you can for this purpose.
Also, these steps should work on RedHat Linux as well, of course, but I didn't test it.

1. Download the compatibility version of the shared MySQL libraries. The compatibility package will save you a lot of trouble due to dependencies. The rest won't go smoothly if you don't get this one.
   It should be named something like:

   MySQL-shared-compat-5.1.xx.distribution.platform.rpm

2. Install it:
   

   rpm -Uvh MySQL-shared-compat-5.1.52.rhel5.x86_64.rpm

3. Install these packages:
   

   yum install perl-DBI perl-DBD-mysql perl-TermReadKey

4. Download latest Innotop, and extract it:
   

   wget http://innotop.googlecode.com/files/innotop-1.8.0.tar.gz
   tar zfx innotop-1.8.0.tar.gz

5. We're done with the prerequisites. Go into the directory and install innotop (from now on it's the same as in the INSTALL file):
   

   perl Makefile.pl
   make install

That's it. We're done.
Hope this saves someone some time.

Update:
On Debian, this is also simple due to this little trick (if you have the right sources defined):

sudo aptitude install libdbd-mysql-perl

Recommended Google Chrome Extensions

Not all great Chrome Extensions get to the Featured or Popular lists on the Google Chrome Extensions website. These are 3 extensions I really like, and are not as widely known.

• Chromey Calculator - super useful for quick money/distance conversions
• JoinTabs - unites all the open chrome windows into one window as tabs
• Facebook Photo Zoom - does exactly what the name says, highly recommended

UNHEX for Integers in MySQL

The following is a very, very specific subject matter, but I wish this blog post had existed when I was looking for the solution to a problem I was having. Most likely it has no interest to anyone who is not specifically looking for it, so feel free to stop reading...

MySQL has a few built-in functions for handling binary data. One of them is HEX which converts any data into hexadecimal representation. The function which you would expect to do the opposite (as the manual states) is UNHEX which takes a hexadecimal representation and turns it into characters.

So, if you try to do:

SELECT UNHEX(HEX('a'));

You get an "a" back. But if you try that on a integer:

SELECT UNHEX(HEX(1));

You get back the char corresponding to the ASCII value of 1, which is not what was intended.
The correct way to do it, and the real opposite of HEX is the CONV function which converts betweens bases:

SELECT CONV(HEX(1), 16, 10);

This time the result is the number "1" as expected.

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