Another slightly disappointing article regarding MySQL, this one from a printed magazine. Below are my comments to the editor of Linux Format. The Dear Editor is an email link should others wish to make any comments. (Previous article comments What makes your blood boil?, Review of Database Magazine Article - ?The Usual Suspects?)

Dear Editor,

I’ve recently subscribed to LXF, and have generally been very happy with the content in past months. I’m disappointed in your recent LXF77 article “Harness a database” Pg 57. Being a strong MySQL supporter, your article includes a number of practices which are less then ideal, and especially for the newly initiated, overly complicated when simplier alternatives exist.

I am happy to see that you had the …

It's Wednesday at DevCon 2006 and the dinner activity tonight was pizza and karaoke.  The pizza was pretty good but the entertainment was even better.  Sans alcohol I'm not much of a singer, but I found out tonight that MySQL has tons of great vocal talent. :-p   Patrick G was the star of the night!

Patrik B, of course, did it "his way".

Mark (left) and Jim (right) were my non-singer compadres.

And Eric (in the tye dyed shirt) shows us what happens when you apply a little alcohol.  :-)

This evening I received some very important information on how today’s decision of the European Parliament against the mutual recognition of national patents came about:

Piia-Noora Kauppi MEP, who heads the Finnish delegation to the conservative EPP-ED group (the largest group in the European Parliament), took the key initiative in the EPP-ED group meeting last evening in Strasbourg. A “group” in the European Parliament is, simply speaking, an international group of likeminded political parties from multiple European countries. The German CDU/CSU (MEP Lehne’s party) and British Conservatives are particularly well-known member parties of the EPP-ED group.

After we had alerted her yesterday to the …

This one hardly seemed worth a project page. MySQL lacks base64 encode/decode functions, and I needed them, so here they are in procedural SQL. You’ll need MySQL 5.0.19/5.1.8 if you don’t want it to segfault under you when you run them (bug #16887).

I requested this urgently for 5.1 and Brian made it happen: An information_schema table called processlist. This is the table version of the "SHOW FULL PROCESSLIST" output, and it is very cool. Using this table, you can create SQL to dig the processlist, and write stored procedures that spot, log and handle long running queries.

Even better: With the new log formats in 5.1, you can access the logs as CSV engine based SQL tables as well, and join the processlist table against the general query log or other logs. This allows you plenty of easy session tracing and easy profiling.

Even better: With the processlist table and 5.1 new feature "events" you can create new threads, monitor their execution, and in case of problems forcibly terminate them using other events or procedures. Or you can write a stored procedure using DROP …

When you are a road warrior, you will be using a lot of different internet connections from a lot of different providers. Sending your mail can be an adventure, because more and more services block outgoing connections using port 25 in order to prevent infected machines from sending spam.

Fortunately, there are alternatives, and I am not talking webmailers here. Most company mailers and freemail servers now offer their services on ports 587 (submission) and 465 (smtps) as well.

Submission is basically SMTP with AUTH SMTP requirement moved to a different port. Very often it is combined with STARTTLS for encryption to protect the passwords transported in authentication. You can test submission manually using the openssl command line client, if you are using openssl 0.9.7 or higher: $ openssl s_client -starttls smtp -connect …

I was asked about features in 5.1 this morning and one popped out that one of Elliot's new developers completed, which is the "PROCESSLIST" table.

As an example:

mysql> select * from PROCESSLIST WHERE command="query";
+----+------+------+--------------------+---------+------+----------- +-------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+------+--------------------+---------+------+----------- +-------------------------------------------------+
| 2 | root | | information_schema | Query | 0 | preparing | select * from PROCESSLIST WHERE command="query" |
+----+------+------+--------------------+---------+------+----------- +-------------------------------------------------+
1 row in set (0.00 sec)

Adding show commands and turning them into information_schema table is pretty simple. I'll be leading …

LOL!

I noticed that in one of my previous blog entries (Importing XML data into MySQL using ExtractData()
) some of the literal XML snippets did not render at the Planet MySQL blog aggregate.

Then, the trouble was limited to my own entry: the escaped tags in the string arguments passed to the XML functions were not rendered.
Yesterday I did another little entry concerning the MySQL XML functions, but this time, my entry seems to wreck the remainder of the PlanetMySQL.org page! I guess this would be called 'XML injection vulnerability'.

Ironically, I entered a little warning in my blog entry for those that would be interested in reading the source snippets. For those in doubt - It was never my intention to manipulate or …

One of the things I learnt a year ago when I first started working with MySQL stored procedures was that you really need to have the latest version of MySQL running. By the time you have identified a possible bug and noted down the specifics a new version was available which invariably fixed the problem.

This is also true with release 5.1, I have recently started writing content on the event scheduler over at www.mysqldevelopment.com and found a few bugs early on. Given that 5.1 is still very much in development I tend not to raise bugs these days without first checking on the forums and then making sure I have the very latest release to test against.

One of the biggest issues I had was that one off events worked fine, but events that fired at intervals didn't. I say they didn't but one of the big problem with testing events is that you have to wait a long …