While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP

They had substituted * for a .+ in a metasequence. A metasequence is a parenthetical expression that evaluates based on multiple alternative conditions, and the pipe (|) acts as an OR operator. The full code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer Handbook. The student’s change would have worked without an …

I guest-posted on Fusion-io’s blog about the database’s working set size and the interplay with fast Flash storage. It’s written from a MySQL point of view, but it’s applicable to many types of systems.

Further Reading:

• 6 ways to stay sane while working from home
• How to understand SQL joins
• How to understand key length limitations in MySQL
• …

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | …

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn’t matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you’ve seen it, it’s really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I’d capture it here in case it’s helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here’s the staff table from the database (today’s imaginary data isn’t particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | …

Here’s your spammy day-of reminder about tonight’s free MySQL meetup. The topic is MySQL Monitoring Bonanza. There will be beer, pizza, pretty charts and graphs, and friends. Free as in beer (and pizza, did I mention that?)!

Further Reading:

• New Central Virginia MySQL Meetup
• Free webinar on monitoring MySQL
• Speaking at MySQL Meetup in Northern Virginia
• …

If you follow what Percona is up to, you might have noticed that I’ve created a set of high-quality monitoring and graphing plugins for MySQL and related systems. Currently they support Nagios and Cacti. I’ll give a free webinar on March 28th discussing these, and more broadly, discussing how to monitor MySQL successfully to avoid common problems like spammy alerts about nonexistent problems.

Further Reading:

• Free webinar on preventing MySQL downtime
• Free webinar on MySQL performance this Thursday
• …

My earlier blog post was wrong, our block of rooms for the conference is still available past the 12th of March. However, there are apparently zero rooms available in the Hyatt other than our reserved block, and our block discount will expire soon (I’m a little hazy on the exact details, because I thought it was the 12th). What I have been told in no uncertain terms is this: “If you know anyone who plans to attend the conference and needs a hotel room, they should book IMMEDIATELY in our room block or they will not be able to get a room in the Hyatt either at a reasonable rate or at all.“

Please don’t ask me to help — I’m not involved in this, just trying to get the information out there. The logistics are beyond my ken.

Update: apparently the room discount rate ends on the 19th when our reservation on the block of rooms …

I’ll speak at the Shenandoah Ruby Users Group meeting next week in Harrisonburg, Virgina. I’m preparing a brand-new talk for this, called “Seven Things To Know About MySQL Performance.” Hopefully my copies of the new High Performance MySQL, 3rd Edition will have arrived by then and I can give one away!

The meetup is free and friendly. If you’re in the area, you oughta be there!

Further Reading:

• New Ruby Conference: Ruby on Ales 2011
• Speaking at the Southern Computer Measurement Group …

I was trying to parse the data from the common platform enumeration which is a big dictionary of all or most of commercially or freely available applications, operating systems and hardware (the hardware part I didn't really understand).

Here is an example for "Microsoft Access 2000 sp2"
cpe:/a:microsoft:access:2000:sp2

As you can see, the data is separated by colons.
To get the parts that I want in that string, I used the following SQL statement:

*edit from Scot's comment*
mysql> set @v="cpe:/a:microsoft:access:2000:sp2";
Query OK, 0 rows affected (0.00 sec)mysql> set @p=2 /*the part I want */;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);
+---------------------------------------------------------------------+
| SELECT …

We’re running Birds of a Feather (BoF) sessions and lightning talks this year at the MySQL conference, similarly to previous years. You can help by submitting proposals ASAP. The deadline is Monday, and it is a hard deadline.

Submit a BoF Session • Submit a Lightning Talk

Why is this a big deal? Why the hard deadline? It turns out that this is one of the things I always underestimated. Planning for these is actually quite difficult and they are very expensive. That’s because we have to ensure that there is room for them to take place, doors are unlocked, and the lights are turned on. This really surprised me — I always thought it was simple to let people gather together in the evening. Well, nothing is simple at …