The joy of spam

It’s a sad state of affairs when you come back to your blog after a long period of not having the time to update it – and you find 128 comments – of which 128 are spam.

Not only do we have to deal with the deluge of spam on a daily basis via our email (although, our spam protection is pretty shit hot), through our doors, and over the TV – we now have to deal with it via blogs as well.

I know this has been going on a long time, and it’s nothing new to a lot of people – but I’m going to bitch about it anyway. It enrages me.

Of course, one might call my own rant spam. Sucks to be you. πŸ˜‰

The fun side of MySQL

It’s been a busy few weeks for me whilst I settle in to my post at MySQL, and certainly an interesting time to join!

I came across two bug reports today, that show the fun side of the people that work here at MySQL. First one from Mark Matthews – who looks after connector/j:

Next one from Arjen Lentz, our Community Relations Manager:

I return!

Well, once again it’s been some time since I managed to find any time to write anything for my blog. The past month certainly has been one of the most hectic I’ve had in many many years!

I’m now working for MySQL AB – as a Support Engineer for the Americas Group!

I applied a little over a month ago now, and when I found out that I got the job I had a very short space of time with my old company, Cool-Tools., to do wrap up what I was doing with the monitoring tool that I was working on, and to make sure that everybody was happy with taking over from my old position in support.

I got a weekend off.

I then started directly with MySQL the following Monday (2 weeks after discovering I had gotten the job). From then it’s been a pretty fast learning curve, working with some of the best colleagues I’ve ever had so far. The guys here at MySQL really are a fantastic bunch of people!

I notice that PlanetMySQL has had a facelift! Looks wonderful Arjen! πŸ™‚ Now let’s see if I get one of those revered “Employee” images by my name!

I don’t have any technical content to add at this time – maybe when I get a little extra free time (read, when I’m not working! πŸ˜‰ ) I’ll finish that entry on procedures that I want to add. I’ve done some work on functions lately, but I’ll most probably be using those as examples in a new column that may be arriving soon on – keep an eye out for that!

Stripping Digits – The benefits of Benchmarking and Profiling

Carsten Pedersen (Certification Manager of at MySQL AB) read my entry about stripping digits, and has done a good write up here on how this function could be improved, a great deal.

Have a read of Carstens link, and my previous entry if you didn’t catch it, before we progress.

As you see, I mentioned that “it performed fairly well”, but I admit given the nature of the request (a quick IRC question) I didn’t run a benchmark on it compared to something like Carsten’s REPLACE solution. I actually went with the REGEXP as the original question was actually something along the lines of “If I have a string such as “joe123” how I can I strip the digits from the end to return just “joe”. I wrote the quick function as an example of what you can do in 5.0, as the user was still 4.x anyway, really the answer would have been a little more along the lines of using REVERSE on the string and walking along the digits until you hit the first alpha (or [^0-9]), and then doing a substring up until that location.

However, Carsten was spot on when he picked up on this! This only goes to reinforce the fact that things should not be assumed on first glance, and should be benchmarked and tested rigorously. Carsten provides a great example of how to run a quick benchmark of function calls with the BENCHMARK function, which is the first logical step when testing any new stored functions that you may create. Thanks Carsten for the write up!

To take this one step further – I also read a great write up on “Benchmarking and Profiling” from “Pro MySQL” written by Mike Kruckenberg and Jay Pipes, which has generously been made available on the MySQL AB website here.

This gives some great information on using readily available open source benchmarking tools that allow would allow you to benchmark new functions within a number of different situations, with different data sets etc. It also gives some good information on profiling and diagnostics techniques, all of which is fantastic information.

This seems as good a time as any to introduce what I do for a living. I work for a company that sells and supports database management tools, primarily in the Oracle and SQLServer market place. If you read the section on profiling in the chapter above, a lot of it is exactly the kind of thing that we are putting together at the moment at Cool-Tools for the monitoring tool that we currently distribute – NORAD Surveillance. We’ve seen a huge upsurge of MySQL being picked up in the enterprise space, and we would quite frankly be foolish to sit back and ignore this. So we’re some way down the line of an early beta version of a plugin that Cool-Tools will provide that will monitor MySQL, as well as the underlying OS.

Here’s a quick sneak pic of the “Global Health Window“.. Although it’s a little boring on my little work desktop machine with no activity (that could really do with upgrading!), and still needs a few more variables to fill it out – it’s good enough to give an idea of what we are trying to do.. I ran a couple of the sys-bench scripts (test-insert and test-select) from a remote machine (the host) to give it at least a little bit to look at..

I’ll post a full overview as and when – if there’s anybody out there that would like to try a beta copy of it as well, when it’s ready, feel free to fire me an email – mark at cool hyphen tools dot co dot uk – and I’ll see about getting it out to a few of you. We won’t consider people sending from email addresses such as hotmail πŸ˜‰

Stripping digits

We had a question in #mysql on freenode yesterday, asking if there was a function to strip digits from a string. The answer is of course – not natively.

I’ve been playing around with Functions and Stored Procedures a bit lately though, trying to familiarise myself with the MySQL implementation fully, and wrote this quick function which does the job, although only in 5.0:


DROP FUNCTION strip_digits //

CREATE FUNCTION strip_digits (str VARCHAR(50))

WHILE sub_start < LENGTH(str)+1 DO IF SUBSTRING(str,sub_start,1) REGEXP '[[:alpha:]]' THEN SET res = CONCAT(res,SUBSTRING(str,sub_start,1)); END IF; SET sub_start = sub_start + 1; END WHILE; RETURN res; END; //

Here’s a couple of examples of it in action:

mysql> select strip_digits('foop123')//
| strip_digits('foop123') |
| foop |
1 row in set (0.00 sec)

mysql> select strip_digits('foop123a')//
| strip_digits('foop123a') |
| foopa |
1 row in set (0.00 sec)

mysql> select strip_digits('foop123ahgh63263jsdj')//
| strip_digits('foop123ahgh63263jsdj') |
| foopahghjsdj |
1 row in set (0.00 sec)

It even performs fairly well!

More to come in the “MySQL 5.0” collection of blogs on Functions and Stored Procedures when I get time to sit down and do it justice! Even though I’ll never win a mug any more.. Hohum..

MySQL 5.0 – Triggers

One of the major new pieces of functionality within the 5.0 release of MySQL is something called “Triggers”. Triggers are objects that are related to tables which are executed, or to show where their orginial name came from “fired”, when a triggering event is performed against its associated table. These events are those statements that modify the data within the table that the trigger is associated with – INSERT, UPDATE or DELETE – and can be fired either BEFORE or AFTER row the row is modified – or indeed both.

Triggers are very much like stored procedures, as if you want to execute multiple actions when a trigger is fired you can encapsulate these within a BEGIN … END construct. They also have a couple of extra keywords – OLD and NEW- which refer to the values of the columns before and after the statement was processed, respectively. INSERTs only allow NEW (as no value existed beforehand), UPDATEs allow both NEW and OLD, and DELETEs only allow OLD (as no value exists afterwards).

So back to the old question – “So what use are they to me?” – well let’s take a look at some common scenarios that triggers can be useful for to the DBA. I’ll be using the latest beta version of MySQL (5.0.10), as this brings a couple of new features to the table that I’ll discuss a little later.

One of the questions that gets asked a lot is “How can I tell who changed or deleted a row?”.. With a lack of native database auditing, or spending bunches of time mining log files to see when a DML statement was issued etc. – we could deploy a trigger to a table, that fires AFTER an UPDATE or DELETE statement, that records the values, as well as some other useful info, in to a “log table”. Let’s set up a quick test case:

CREATE TABLE `clients` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`acc_mgr` varchar(10) default NULL,
KEY `name` (`name`)

INSERT INTO clients (name, acc_mgr) VALUES ('Fredericks','Jim'),

So we have a client table, listing the client, and the account manager that looks after those clients. Everyone knows that sales people are ruthless animals that vicously protect their “patch”! ( πŸ˜‰ ) You, the DBA, are sick of getting called by the sales team and getting asked “WHO CHANGED [insert company here]’s ACCOUNT MANAGER!?!”, and trying to do the ground work to discover who changed what, when. You can now employ a trigger here! First, let’s create a simple audit/log table:

CREATE TABLE `client_audit` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`old_acc_mgr` varchar(10) default NULL,
`user` varchar(40) default NULL,
`changed` datetime default NULL,

And now a trigger that will fill this table, every time somebody runs an update against the table. You create triggers with the CREATE TRIGGER statement:

CREATE TRIGGER client_accmgr_audit AFTER UPDATE ON clients
INSERT INTO client_audit (name, old_acc_mgr, user, changed)
VALUES (, OLD.acc_mgr, CURRENT_USER(), NOW());

So, Mark gets greedy, he thinks he’s the best salesman since Jesus sold a great story to the masses, and wants to take over “Acme” as his client as well. Let’s see what happens when Mark updates the row:

UPDATE clients SET acc_mgr = 'Mark' WHERE name = 'Acme';

select * from client_audit;

| id | name | old_acc_mgr | user | changed |
| 1 | Acme | John | Mark@localhost | 2005-07-31 00:05:56 |

As you can see, the trigger fired on the update statement, and added the new row to the audit table – we even have the user that updated the row, and when they performed the update – Nice and easy! We could add an “action” column to the audit table, and insert a constant of “Update” along with the insert as well – and then change the trigger above to AFTER DELETE, and record deletes against the table in the same way as well..

Note: The above is only allowed from 5.0.10 onwards I believe, before hand you would need to explicitly issue LOCK TABLES client_audit WRITE; and UNLOCK TABLES; wrapped around the INSERT, within a BEGIN … END construct.

Note: You can now also call procedures from within triggers as well, another limitation that seems to have been lifted with 5.0.10!

Here’s a quick test case, we’ll set up a table for all sales, a procedure to set the commission rate for all people based upon the sale (a quick procedure sample – more on those later as well!), and a trigger that calls the procedure and sets the commission after the insert to the table:

delimiter //

create table sales (id int auto_increment primary key,
item varchar(10),
value decimal(9,2),
acct_mgr varchar(10),
commission decimal(7,2))//

CREATE PROCEDURE commission( value decimal(9,2) )
SET @comm := value / 10;

CALL commission(NEW.value);
UPDATE sales SET commission = @comm WHERE id = LAST_INSERT_ID();

Now lets insert a new sale and see what we get back:

INSERT INTO sales (item, value, acct_mgr) VALUES ('Cheese',300.00,'Mark')//

select * from sales//

| id | item | value | acct_mgr | commission |
| 2 | Cheese | 300.00 | Mark | 30.00 |

Yay – well done MySQL AB for getting this working! The documentation needs updating again to reflect this πŸ˜‰

Now, quickly, a couple of other things were added in to 5.0.10.. There is now a new INFORMATION_SCHEMA table called INFORMATION_SCHEMA.TRIGGERS which gives us information as such:

select trigger_name, action_statement from information_schema.triggers
where trigger_name = 'sales_comm'//

| trigger_name | action_statement
| sales_comm |
call commission(NEW.value);
update sales set commission = @comm where id = last_insert_id();
end |

You can also get this information, though not as detailed, from the new SHOW TRIGGERS statement.

I think that’s enough for one night! I hope you find this helpful!

MySQL 5.0 – Source Documentation

Wow what a hectic month – just logged on and noticed I haven’t updated the site in a month! I have a part done entry on triggers that I haven’t had time to finish yet, look out for that in a couple of days. Work has been hectic, a lot of work going in to the MySQL Module of NORAD (a system/database monitoring tool that we distribute.. But that’s a story for another day), which I’m responsible for building the collections / window definitions for at the moment.

One of the windows I was building today was for “Open Tables” – sourced from SHOW OPEN TABLES; – yet I wasn’t entirely sure on what all of the output meant. Specifically “Name_locked”. The documentation doesn’t comment on this at all (the documentation for SHOW OPEN TABLES is particularly poor, even noting a “Comment” field that simply doesn’t exist (on 4.1 at least). “In_use” was not entirely clear either at first glance, “Does it mean being selected/inserted etc. from, waiting for a lock..?“.

After a little testing it’s easy to see that “In_use” is incremented for each current table lock, or lock request against each table. For instance, if you “LOCK TABLE t1 WRITE;” in session 1, and then “LOCK TABLE t1 READ;” in session 2, “In_use” will show a value of 2 for t1, even though session 2 is still hanging waiting to actually use the table. Fair enough..

However, through all of the playing around I did, I couldn’t get “Name_locked” to deviate from 0. So I had a chat with Dean (Therion) Ellis to see if he might know, who messed around in the source, and had a chat with some of the other MySQL AB guys, and eventually came to the conclusion all round that Name_locked is used for specific operations such as RENAME TABLE – when they need a totally exclusive lock against the table to perform the administrative function.

This peeked my interest, and to get back to the title of this entry, tired of poking around “blindly” in the source I decided to load up Doxygen, a GPL documentation system for C, C++ etc. projects, and run the MySQL source through it for a better look around. Rather than having copies of this at work and home, I’ve decided to upload it here, and provide a link so that the community can use it as well. You can browse it here.

I’ll try to keep this as up to date as possible, following fully released beta versions. If I don’t have the latest version feel free to send me an email (leithal at, and I’ll fire an update up. It’s currently about 450mb, with ~9500 files, so we’ll see what bandwidth usage is like as well πŸ˜‰

So did it help?” – “Yup!

Here’s where the open table list is generated. I’ll let you poke around to see if you can figure out how locked_by_name is set. Some interesting files to look at, to start with, are /sql/, and going back to RENAME TABLE /sql/

Hopefully it helps all of you other curious people out there!


I mentioned “fuzzy foreign key” matches in my post the other day, and thought that I’d give it a crack tonight. The aim of the fuzzy match is to try and find relationships when a) They are InnoDB and haven’t been created or b) Foreign keys are not avilable to the storage engine that is being used.

Most DBA’s will know their schemas pretty well, this however should be pretty handy for consultants or anyone starting fresh at a new job / on a new database etc.

It should be noted that this will only “fuzzy” match against a schema that follows strict naming conventions – Where the tablename and “id” is used for the primary key column, and the same name is used for the foreign key in the child table. For instance, given the following example tables:

mysql> desc company; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | company_id | int(11) | NO | PRI | NULL | auto_increment | | company_name | varchar(100) | YES | MUL | NULL | | +--------------+--------------+------+-----+---------+----------------+ mysql> desc contacts; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | contact_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | company_id | int(11) | YES | MUL | NULL | | | direct_line | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+

Here’s what I came up with:

SELECT t1.table_name as parent_table,
t1.column_name as primary_key,
t2.table_name as child_table,
t2.column_name as foreign_key
FROM information_schema.columns t1
JOIN information_schema.columns t2 USING (column_name,data_type,table_schema)
WHERE t1.table_name != t2.table_name
AND t1.column_key = 'PRI'
AND t1.table_schema NOT IN ('information_schema','mysql')
ORDER BY t1.table_name

| parent_table | primary_key | child_table | foreign_key |
| company | company_id | contacts | company_id |


You can change the table_schema WHERE clause as required, or swap it out for “AND t1.table_name LIKE ‘wp%'” fpr example..
This makes use of the INFORMATION_SCHEMA.COLUMNS view.

Of course, there’s no guarentee this will work in all cases. People seem to have an in-built urge to use some of the most outrageously stupid names for their objects in so many cases…

Edit 21-06-05 Removed extra comma in second query (left from my own testing where I excluded extra db names) – Thanks inviso


Arjen Lentz (“Community Relations Manager” for MySQL AB) posted “The MySQL 5.0 Beta Challenge” on his blog today – so in the spirit of community I decided to give it a crack, by putting together some posts on the new features of MySQL 5.0 up here to my humble blog. Who knows – I may win a mug! Then I can have a list of ALL the people to bug over at MySQL AB!

I’m a DBA at heart, so whilst I’ll take a look at the new features that are available for developers, I’m going to start from the DBA tack, and take a look at INFORMATION_SCHEMA.

So what is the INFORMATION_SCHEMA? Those that have used other RDBMS’ in the past will recognize it as MySQL’s “Data Dictionary”. An RDBMS Data Dictionary is a collection of tables or views (that appear as tables), that utilize the database metadata to give easy access to information about the objects (tables, indexes, procedures, functions, views etc.) within the database, as well as information on things such security/privileges, performance statistics, auditing trails etc.

On installing MySQL 5.0, and issuing “SHOW DATABASES;” you will immediately see “information_schema” listed. The INFORMATION_SCHEMA is implemented to look like a database in it’s own right. However, it doesn’t conform with the conventional database privilege system. You can “USE” it, but you can’t run any type of DML against it. GRANT does seem to work against it, but attempting to run a DELETE results in an error.

mysql> use information_schema;
Database changed
mysql> show grants;
| Grants for mark@localhost                                            |
| GRANT USAGE ON *.* TO 'mark'@'localhost'                             |
| GRANT SELECT, DELETE ON `information_schema`.* TO 'mark'@'localhost' |
2 rows in set (0.00 sec)

mysql> delete from information_schema.tables;
ERROR 1288 (HY000): The target table tables of the DELETE is not updatable

“What good are they to me?”

Well, one question I’ve seen a lot around IRC is “Can I use the output of a SHOW command in a statement, such as a subquery?”. This mostly comes down to something to do with SHOW TABLES – getting databases sizes, table sizes etc. SHOW TABLES still works in 5.0, as do all other SHOW commands, however, INFORMATION_SCHEMA.TABLES now also parses the exact same output. Think of it of as view (more on these later) against the SHOW TABLES command. The great thing about this is we can now use conventional SQL to use these values – we no longer have to write external scripts to do what SUM() can do, for example. So here’s one for those guys on IRC that want to find out how big their databases are from SQL:

SELECT s.schema_name,
       CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),'Mb') total_size,
       CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),'Mb') data_used,
       CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),'Mb') data_free,
       IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
       COUNT(table_name) total_tables
  FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
 WHERE s.schema_name != 'information_schema'
GROUP BY s.schema_name

*****************1. row **************
 schema_name: test
  total_size: 0.06Mb
   data_used: 0.06Mb
   data_free: 0.00Mb
    pct_used: 100.00
total_tables: 2
*****************2. row **************
 schema_name: flightstats
  total_size: 2950.14Mb
   data_used: 2949.71Mb
   data_free: 0.43Mb
    pct_used: 99.99
total_tables: 81

So now we can get the information we want, in the format we want. We can also JOIN the INFORMATION_SCHEMA tables to create better reports – such as above, if there are no actual tables in a database, they won’t be shown within INFORMATION_SCHEMA.TABLES, so to get a full report on all databases, we LEFT JOIN from INFORMATION_SCHEMA.SCHEMATA.

This also leads us in to getting helpful information on any object optimizations easily. Perhaps you have performed an archive of a lot of data, and want to reclaim freespace. You want to find all tables with > 40% of the datafile free, so that you can perform an OPTIMIZE TABLE against them:

SELECT table_name,
       ROUND(((t.data_length+t.index_length)-t.data_free)/(t.data_length+t.index_length)*100) pct_used
  FROM information_schema.tables t
 WHERE table_schema = 'flightstats'
HAVING pct_used < 60
ORDER BY table_name;</blockquote>

Let’s take this one step further – now we can actually use MySQL to generate the scripts that we’ve had to use an external scripting language to achieve. Let’s focus on another question often asked on IRC – “How do I drop all my tables, without dropping my database?” – previous to MySQL 5.0 the answer is “get table_name from the output of SHOW TABLES, and loop through this with your scripting language of choice, running a DROP TABLE command for each one”.

Now let’s use the INFORMATION_SCHEMA, and SQL to do the job for us:

SELECT CONCAT('DROP TABLE ',table_name,';')
  INTO OUTFILE '/sql/drop_tables.sql'
  FROM information_schema.tables
 WHERE table_schema = 'test';

Now all we have to do is “SOURCE /sql/drop_tables.sql;” and all of the tables in the test database will be gone, and we didn’t have to leave the mysql command line interface! Don’t try this on your production schemas kids!

Or to go back to our OPTIMIZE TABLE:

  INTO OUTFILE '/sql/optimize_tables.sql'
  FROM information_schema.tables
 WHERE table_schema = 'flightstats'
AND ROUND(((data_length+index_length)-data_free)/(data_length+index_length)*100) < 60;<

This makes life a lot easier! There are a number of other INFORMATION_SCHEMA tables, but I won’t go in to all of those now. We can use these to get information on grants, build grant scripts, get information about indexes, columns, foreign keys etc. For instance – it’s probably possible now to perform “fuzzy” foreign key matches – searching for columns that are named the same within different tables, that may not necessarily be the InnoDB engine. As I put together more scripts in readiness for 5.0, I’ll post them here, and to the other site that I run – (which I haven’t really started filling yet!! But it’s wiki! ;))

It should be noted that all users have access to the INFORMATION_SCHEMA. They will however only be able to see the objects for which they have some privileges on.

Oracle Buys Times Ten

This seems like an interesting move from Oracle. Times Ten’s Cache is an in-memory cache type database that works very much like MySQL Cluster – with read/write functionality – along with the ability to “divert” to a backend (Oracle) database when the data needed is not memory resident.

It will be interesting over the next few months to see what Oracle does with this. On the surface it’s quite obvious that it’s an attempt to get their foot in the door with the likes of telcos and financial services companies, for their “frontline” systems, where they are rather lacking due to the cumbersome nature of the beast for “real time” data.

So you may have read this through the MySQL category on my blog, and wondered “How the hell does this relate to MySQL”? As any seasoned MySQL user would know, MySQL Cluster has been around for a while now, and whilst it’s great for “smaller” apps – that require very fast “real time” access, but don’t necessarily store huge amounts of data – many have noted the serious lack of any disk based “second stage” storage for older data that may not be used very often. MySQL Cluster is already geared towards the areas where Oracle is trying to get to right now through acquisitions. MySQL Cluster however has fallen down on the VLDB back end requirements of some of these systems.

Until 5.1 it seems. Today I saw a commit to the internals email list from Pekka Nousiainen that refers to “Pgman” – which seems to handle buffer pool caching and flushing from/to disk based storage – for the NDB storage engine. I’ll save you all the code, but this comment seems to sum it up fairly well for now:

+ *
+ *
+ * Central structure is “page entry”. It corresponds to a disk page
+ * identified by file and page number (file_no, page_no).
+ *
+ * A page entry is created by first request for the disk page.
+ * Subsequent requests are queued under the same page entry.
+ *
+ * A new entry must wait to be “bound” to an available buffer page
+ * (called “real page” here). If the disk page is not “new” (empty),
+ * the entry must also wait to be “mapped” via “pagein” from disk.
+ *
+ * Entries are released on demand when page requests arrive for unknown
+ * pages. Release candidates are entries which point to a disk page
+ * which is “clean and not used”. They are ordered by some variant of
+ * least recently used (LRU).
+ *
+ * A background clean-up process makes “dirty” pages clean via “pageout”
+ * to disk. UNDO log entries are first flushed up to the maximum log
+ * sequence number (LSN) of the page. This is called write ahead
+ * logging (WAL). The clean-up process prefers “dirty and not used”
+ * pages and lower LSN values.
+ *
+ * A local check point (LCP) performs complete pageout of dirty pages
+ * since given LSN. It needs a stable entry list to iterate over.
+ *
+ * Page entries are put on ordered lists accordingly:
+ *
+ * The “main” list contains all entries. It is ordered by its unique
+ * key (file_no, page_no). It is used to look up page entries. LCP
+ * iterates over it to cover all candidate pages.
+ *
+ * Each entry also belongs to exactly one “sublist”. These drive page
+ * processing. The sublist is determined by page “state”. The state
+ * contains page properties such as discussed above.