General query logging can be very handy in MySQL on profuction server for various debugging needs. Unfortunately you can't switch it on and off without restarting server until MySQL 5.0.

What can you do in MySQL 5.0 and below ?

Use Our Patch - With this patch you can get all queries logged as slow queries (with times) and as you can change long-query-time online you can effectively enable and disable debug logging live. Note however this is not 100% equivalent for general query log - for example connects or queries with syntax errors will not be logged.

Enable logging to /dev/null You can enable queries to say "all_queries" log and symlink that to /dev/null. So when you will need to enable queries you can symlink it to something else and run "flush logs" so logs are …

If you like to compile MySQL from sources by yourself, for different needs, like debugging, testing etc, you probably can face this issue.

What I usually do to fast compile and test is

PLAIN TEXT SQL:

1. ./configure --prefix=/dir/to/mysql
2. make
3. make install

and then, for example, load the dump of InnoDB from previous version:

mysql testdatabase < dump.sql


I bet you will not notice all your tables now is MyISAM. Why?

PLAIN TEXT SQL:

1. Welcome TO the MySQL monitor.  Commands end WITH ; OR \g.
2. Your MySQL connection id IS 1
3. Server version: 5.1.21-beta-log Source distribution
4.  
5. Type 'help;' OR '\h' FOR help. Type '\c' TO clear the buffer.
6.  
7. mysql> SHOW engines;
8. …

We had our first ever MySQL User Conference in Japan this week, put on by our very own Larry Stefonic (Larry-san as we call him) and our all-star Japanese sales & marketing team.  We had several hundred people at this multi-day event and it was standing room only.  There were keynotes by Marten Mickos as well as Yukihiro Matsumoto (Matz) the author of Ruby, the director of HP Japan and the General Manager of Sumisho, one of our top …

I’m working on a project at the moment where we’re doing an upgrade and need to do the dump-and-restore method. It’s 300G, so that’s never going to be fun, but I found a tool today that helped significantly.

Multi threaded mysqldump is not an utopia any more. mysqlpdump can dump all your tables and databases in parallel so it can be much faster in systems with multiple cpu?s.

I ran mysqlpdump (with one patch I’ll send in soon to put quotes around table names) today with 16 threads on a 4 core system and did all 300G in ~3.5 hours. Additionally, since it wraps mysqldump but iterates over the tables, I got a sql file for each table, which is going to make writing a script to restore a piece of cake. It understands that I wanted to do –master-data and it had an option to gzip each sql file as it went.

All in all, I’m thrilled. kudos! And thanks for the tool.

…

I didn't realize InfoWorld was doing open source awards, until I read about it in Dave's blog.  But here they are.  And looks like MySQL won as best database.  Check out the "BOSSIE" awards.

• InfoWorld: Blog, …

Microsoft and Sun expand partnership. QNX open sources realtime operating system. Sun acquires Lustre file system. (and more)

Microsoft and Sun Expand Strategic Alliance, Microsoft / Sun Microsystems (Press Release)

QNX Publishes Neutrino Source Code and Opens Development Process, QNX Software Systems (Press Release)

Sun Microsystems Expands High Performance Computing Portfolio with Definitive Agreement to Acquire Assets of Cluster File Systems, Including the Lustre File System, Sun Microsystems (Press Release)

Microsoft and Novell Open …

I thought of a brilliant QA plan. Create sql rainbow tables! Just trust me, this system of QA will be very powerful in bug finding. Especially important will be the comparison of old/new versions of mysql. Writing a prototype now, on my day off.

Just during prototype development, I've discovered a handful of bugs. I'm testing all functions that are documented in the manual. This is alot that I've written up:

mysql> select count(*),category from func group by category;
+----------+--------------+
| count(*) | category     |
+----------+--------------+
|        6 | arithmetic   |
|        7 | bit          |
|       60 | casting      |
|       47 | comparison   |
|       52 | datetime     |
|       18 | encryption   |
|      127 | geometry     |
|       17 | information  |
|        7 | logical      |
|       32 | mathematical |
|        9 | misc         |
| …

What if you have to customize the sequence of order by clause on a column. Say, you have a integer column 'weight' that can have values ranging from 1 to 5 and you want to get the rows in the sequence 4,2,5,1,3. So what do you do? Here is a simple trick using the field function.

1: mysql> SELECT * FROM table ORDER BY FIELD(weight, 4, 2, 5, 1, 3);

Now what does it do. Field function returns the index of the first argument in the rest of the list. So, effectively the order by logic will be the same, just that the values that are being ordered are generated on the fly.

Other approach can be to use CASE.. WHEN .. END statement as mentioned in the MySQL Documentation. The above code can be rewritten as:

   1: mysql> SELECT *, CASE 

   2:     -> WHEN weight = 1 THEN 4

 …

Quite frequently I run into question like this "I'm using SQL_NO_CACHE but my query is still much faster second time I run it, why is that ?

The answer to this question is simple - because SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are
MySQL Caches - Innodb Buffer Pool and Key Buffer are best example though Falcon, PBXT and other storage engines have similar buffers. There is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed.
OS Caches Operation Systems typically cache file IO unless you explicitely bypass it by using O_DIRECT flag or mounting file system in direct IO mode.
Hardware Caches State of CPU cache may affect query execution speed but only lightly, the hardware IO cache may however cause dramatic difference. Hardware RAID cache is the one but more important …

So you use InnoDB, have indexes on your table, think of row level locking and concurrent queries, feel good and go to sleep. All this while forgetting that even UPDATE and SELECT .... FOR UPDATE statements will (or may) also use the same index for scanning or updating. Then what? You may ask.

Well, InnoDB row level locking works in a somewhat different manner when using indexes. In this case, InnoDB locks index records in shared or exclusive mode when searching or scanning an index. Therefore, as mentioned in MySQL Documentation, the row level locks are actually index record locks.

To complicate matters (or resolve issues) further, the lock is a gap lock. A gap lock refers to a lock that only locks a gap before some index record.

As per the example in …