Today’s question comes courtesy of Diego Medina.

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

When using a 1-byte character set like Latin-1, a VARCHAR(250) field requires 251 bytes of storage. A VARCHAR(260) field requires 262 bytes of storage. Why?
(more…)

If you want to filter a query for further usage, you need first to identify the query as the one to process, and then you need to isolate the elements to use for the task.

Both needs can be satisfied using the Lua string.match(string,pattern) function. Since Lua is an object orieneted language, this function can also be used as stringvar:match(pattern).

Let's see an example. We need to do something with with the UPDATE statements to the employees table.

function read_query( packet )
   if packet:byte() ~= proxy.COM_QUERY then
       return
   end
   local query = packet:sub(2)
   local cmd, table_name = query:match('^(%w+)%s+(%w+)')
   if cmd and cmd:lower() == 'update' then
       print( 'updating table ' .. table_name)
       if table_name == …

I woke up this morning to a nice surprise. Peter Zaitsev from MySQL Performance Blog mentioned my fix for a crash in MySQL. Thanks Peter! That reminded me that I need to write this post about another fix.

A storage engine is called a “handler” in MySQL internals. Handler events are per row at the storage engine layer. For example an insert query that inserts 3 rows will cause one Com_insert increment and 3 handler_write increments. The way these statistics are implemented the storage engine is responsible for incrementing statistics itself. Innodb is missing function calls to increment the handler_delete and handler_update status variables. I’ve filed …

On March 4th I wrote about an observation we made concerning InnoDB locking behavior in combination with foreign key constraints. Since then we have been in contact with MySQL support and they in turn with the InnoDB developers. One of the conclusion I can already draw from the whole thing is that another level of indirection (InnoDB being a part of Oracle, i. e. an external resource to MySQL,

To be really successful at working with databases, there are many different disciplines to become proficient in - things like disaster recovery, security management, data integration, and more. But there are less than a handful of things you want to be really good at; becoming super-skilled in them will help you enormously in your database career. And believe it or not, one of these key areas is data modeling and its kissing cousin, physical database design.

In reviewing some of my notes I thought it was relevant to ensure this small MySQL gem is documented for my history purposes.

Historically I’ve seen people do a complex SELECT statement twice, first to get a COUNT(*) and then to retrieve the data, perhaps in a paginated format. In MySQL you can combined both SELECT statements into 1 SQL. You still need a second SQL statement, but it is less impactful to your system.

Here is how?

mysql> select sql_calc_found_rows * from mysql.user limit 2;
...
2 rows in set (0.01 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

Shortly after getting RC1 out we have now released RC2. We fixed a few issues that were reported after RC1 was out, thanks for that! In addition to that we included a first version of the GRT Scripting IDE that will play a key role for the MySQL Workbench community in the future.

The GRT Scripting IDE allows to easily create scripts, libraries and modules that extend the core Workbench functionality. We will add detailed documentation and tutorials that will get people started. Vlad is hosting a tutorial on this topic at the upcoming MySQL Users Conference as well.

We also plan to create an online repository where people can share their Workbench extensions - think Firefox Extensions - but also including things like diagram-snippets, sample models and much more.

Please give RC2 a try and report your findings on the forums or via the bug system.

…

As half of the world population already knows, the MySQL conference is coming in less than 3 weeks. Since this event only happens once a year, lasts only 4 days, and costs more than a Russian mail-order bride, I'd really like to get the most out of it. Considering that the schedule is completely packed, with 8 (!!) events going on in parallel, I imagine things can get a little frantic. Additionally, I've never been to a conference of such size before and I'm not sure what to expect.

…

LugRadio is possibly the best Linux/Free Software related podcast out there - and it has been for a very long time. LugRadio Live is a live event where lots of people gather together to love freedom.

This year, there’s a LugRadio Live USA - and it’s in San Francisco. So, are you going to be (or are able to be) around SF the weekend before the MySQL UC? If, there is no excuse not to drop in on LugRadio Live USA - it should be awesome! (oh, and it doesn’t cost much… like $10 or something)