Poor man's schema comparison

Comparing database schemas is one of those DBA tasks that occur all the time. If you can afford the luxury of having a GUI, then MySQL Workbench is the tool for you. It will help you compare structures and eventually synchronizing the differences. If the only thing at your disposal is a command line interface, without the possibility of installing anything (a common occurrence when you are consulting), then this Poor man's schema comparison tool may come handy.

It's a Perl script that doesn't require module installations beyond the most common ones, and produces a simple output, fit for further examination with the tools that you have at your disposal when working at the command line.
By default, it invokes vimdiff, to let you know visually what the differences are. If you change the command to diff, you get a simple output that can tell you at a glance what differences you are dealing with.
For example, after executing

schema_diff mysql://root:mysql@127.0.0.1:3306/crosstab \
mysql://msandbox:msandbox@127.0.0.1:5139/crosstab

I got the difference (a column that I introduced in the first database but not in the second one) clearly visible on screen.

I will be talking about this and other resources for the DBA during my session on schema maintenance at CodeBits in Lisbon (December 3-5, 2009).

Codebits 2009, coders conference and competition in Lisbon

Codebits is approaching. Form December 3rd to 5th, this gathering of 600 developers for a conference, which is also and foremost a competition, will occupy the mind of the best coders in Europe. I will be a speaker, with two sessions: Schema maintenance in MySQL, focusing mainly on MySQL Workbench, but with an introduction to the other choices. Introduction to Gearman - a distributed, language and OS independent system for dynamic programming.

Also Lenz will be there, and quite busy. He will also have two sessions:

• Why you should be using a distributed version control system (DVCS) for your project, with advice that will benefit every developer.
• MySQL High Availability Solutions - Best practices and commonly used HA setups for MySQL

The event is hardly like any other conference. It will be a momentous show, with a part that start like a conference but goes on as a competition.
If you like coding, you must show up!

Gearman for MySQL

If you haven't yet heard about Gearman, it's time to have a look at it. Its distributed client server architecture are a perfect match for today's cloud oriented applications. When talking about Gearman, much stress is often given on its scalability features, such as map/reduce and distributed loads. But Gearman has also a distinctive advantage in the feature department, because of its design that I like to describe as cooperation of the fittest.

While a traditional application is written entirely in a given language, or it must find ways of integrating multiple languages parts, Gearman encourages easy cooperation between parts written in different languages, eliminating the need for library translation, and allowing the best implementation of a feature to be used by another part of the application.
Today, November 12, at 14:00 UTC, there will be a MySQL University presentation on Gearman for MySQL.
One of the components created by Gearman enthusiasts is a MySQL Gearman UDF. Using this extension, you can easily define functions in any language you are familiar with, and then call that function from MySQL. Some examples shown in the presentation include shell access, regular expressions enhancements, storing results incrementally in a file, using language-specific library extensions, and your imagination can do the rest.
Using this sample worker code, you can then call the UDF function from MySQL with something like thge following:

select gman_do('reverse','abcd') as test;
+------+
| test |
+------+
| dcba |
+------+

SELECT gman_do('shell',
concat(' ls -lh ', 
  (select variable_value from information_schema.global_variables 
  where variable_name = "datadir" )))\G 

total 40976
-rw-rw----  1 gmax  staff   5.0M Nov 11 13:34 ib_logfile0
-rw-rw----  1 gmax  staff   5.0M Nov 11 13:34 ib_logfile1
-rw-rw----  1 gmax  staff    10M Nov 11 13:34 ibdata1
-rw-rw----  1 gmax  staff   1.2K Nov 11 13:34 msandbox.err
drwx------  2 gmax  staff   2.4K Nov 11 13:34 mysql
-rw-rw----  1 gmax  staff     6B Nov 11 13:34 mysql_sandbox5140.pid
drwx------  2 gmax  staff    68B Nov 11 13:34 test


select gman_do('eval','2 * 3') ;
+-------------------------+
| gman_do('eval','2 * 3') |
+-------------------------+
| 6                       |
+-------------------------+


select gman_do('eval',
  concat('$_="',host,'";tr/a-z/b-za/; $_')) 
as test from mysql.user;
+-------------+
| test        |
+-------------+
| %           |
| mpdbmiptu   |
+-------------+