a little challenge

How do you make mysqld write a DELETE to the binlog just by entering a SELECT statement ?
No triggers and no stored routines/functions are involved.

partitioning + auto_increment is buggy!

Folks, I just want to warn you how buggy partitioned tables with negative values are. Never use negative values for InnoDB auto_increment columns!!!!

• Assertion failed: next_insert_id >= auto_inc_interval_for_cur_row.minimum()
• partitioned innodb tables end up with duplicate primary key values!
• AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
• Assertion failure in file row/row0mysql.c line 1386

There are more bugs than I listed here..

5.1 doesn't solve all merge table hell from 5.0.

This week I've had to revisit merge tables once again due to customers experiencing problems. Although 5.1 merge table implementation is a huge improvement over 5.0, there still remains some critical bugs.

My list is still growing:

bug #45800: crash when replacing into a merge table and there is a duplicate
bug #45781: infinite hang/crash in "opening tables" after handler tries to open merge table
bug #45796: invalid memory reads and writes when altering merge and base tables
bug #45777: check table doesn't show all problems for merge table compliance in 5.1

Not to mention a few feature requests, and even documentation clarification for some manual sections.

some useful additions to query generator

I've been on vacation this week, and decided to fine-tune some old QA code. Opened the manual to see the syntax for a select statement, and afterwards added to my random select generator the following:

• all index hints (force, use, ignore, for join, for order by, for group by)
• lock in share mode, for update
• key_block_size for individual indexes
• hash, btree, rtree for individual indexes
• unique, fulltext, spatial for indexes

Especially important is the 'lock in share mode' addition. The reason is InnoDB
has many serious bugs with this locking mode (insert ... select, and others) in read committed mode.

So, I don't need multitable delete or update to reproduce those bugs, since I can just do a simple select locking in share mode. For example, the following bugs previously went without proper testcase until I discovered this:

assert btr/btr0pcur.c line 217 -innodb_locks_unsafe_for_binlog or read committed
5.1.35 crashes with Failing assertion: index->type & DICT_CLUSTERED
Strange error messages about locks from InnoDB

some bug stats

So I did some checking at the number of bugs I've reported since start of 2005. Seems I'm at the top of my game here!

• P1 Server bugs: 180 (next runner up PeterG with 127)
• P1 + P2 Server bugs: 321 (next runner up PeterG with 311)

Interesting to note that most of my bug filing happened after 2006, but I started working at MySQL in 2005, so that's why I've used that start date.

The runner ups mostly report bugs in alpha versions, falcon, maria, and beta versions of mysql.
Nearly all of my bugs are in the current GA versions since that is what most of our customers use.

On occasion I go off on a tangent and try break the subquery optimizations in 6.0, but this
is only a small percentage of the total.

been a shocking week for 5.1.35

so last week i started tweaking some of my old 'rainbow' scripts, and found 1 bug for each day of the week. there are a few more in the pipeline still...

Bug #44774 (load_file function produces valgrind warnings)
Bug #44768 (SIGFPE crash when selecting rand from a view containing null)
Bug #44767 (invalid memory reads in password() and old_password() functions)
Bug #44766 (valgrind error when using convert() in a subquery)
Bug #44684 (valgrind reports invalid reads in Item_func_spatial_collection::val_str)
Bug #44672 (Assertion failed: thd->transaction.xid_state.xid.is_null())
Bug #44664 (valgrind warning for COMMIT_AND_CHAIN and ROLLBACK_AND_CHAIN)
Bug #44633 (Automatic search depth and nested join's results in server crash - v2)

so, it seems i am still useful for bug finding, even with old tools i created pre-5.1 GA

MXit !

Today I started writing a PC client for MXit, because the existing ones suck. Since the company uses a proprietary variation of the jabber protocol to reduce bandwidth usage, I have to decode the protocol myself.. Will post details of it later...

Get MXit http://www.mxit.co.za/web/downloadmxit.htm

Kilimanjaro preparations

So I have started training to get fit enough to gracefully reach the top of Kilimanjaro in 2009.
Information on the various routes here.

Here I will keep a log of certain walks I do, and their timings.
Note that time_down usually includes time spent at the summit.

+------------+------------+------------+-----------+------------+------------------------------------------------------+
| date       | start_time | time_there | time_back | time_total | venue                                                |
+------------+------------+------------+-----------+------------+------------------------------------------------------+
| 2008-12-27 | 16:37:00   | 00:47:00   | 00:32:00  | 01:19:00   | Lions Head                                           |
| 2008-12-28 | 05:47:00   | 00:48:00   | 00:35:00  | 01:23:00   | Lions Head                                           |
| 2008-12-30 | 12:57:00   | 00:42:00   | 00:30:00  | 01:12:00   | Lions Head                                           |
| 2008-12-31 | 12:24:00   | 00:39:00   | 00:30:00  | 01:09:00   | Lions Head                                           |
| 2009-01-02 | 12:09:00   | 00:37:00   | 00:28:00  | 01:05:00   | Lions Head                                           |
| 2009-01-05 | 12:45:00   | 00:39:00   | 00:22:00  | 01:01:00   | Lions Head                                           |
| 2009-01-07 | 17:19:00   | 00:37:00   | 00:21:00  | 00:58:00   | Lions Head                                           |
| 2009-01-14 | 12:10:00   | 01:01:00   | 01:29:00  | 02:30:00   | Home->La Med (via KloofNek)->Joburg (via Sea Point)  |
| 2009-01-24 | 06:00:00   | 01:05:00   | 00:47:00  | 01:52:00   | Platteklip                                           |
| 2009-01-29 | 13:30:00   | 00:38:00   | 00:27:00  | 01:05:00   | Lions Head                                           |
| 2009-02-21 | 07:26:00   | 00:58:00   | 01:31:00  | 02:29:00   | Home->La Med (via KloofNek)->Joburg) (via Sea Point) |
+------------+------------+------------+-----------+------------+------------------------------------------------------+
11 rows in set (0.03 sec)