I've been doing a few experiments with ENUMs and SQL_MODE.

Not a lot of people realise that in MySQL, an ENUM can actually contain one other value as well as the specified ones - no, not NULL, but ANOTHER value, specifically, the empty string ''.

This can lead to trouble. The empty string is entered by MySQL when it tries to insert an invalid value into the column. It does also give a warning, but nobody takes any notice of those right?

Demo schema:

CREATE TABLE enumtest (
 id int not null auto_increment,
 name varchar(100) NOT NULL,
 status ENUM('ok','broken','decommissioned','narnia'),
 PRIMARY KEY(id),
 KEY(name),
 KEY(status)
);

Now let's try some inserts...

INSERT INTO enumtest (name,status) VALUES
 ('Mark','ok'),
 ('Fred','broken'),
 ('Bob','decommissioned'); …

This was the title of an article back in 2002 by Shelley Doll and other articles with more or less the same concerns including the secretary of the ANSI database committee. In my recent blog post I made the point that I do hope that MySQL AB tries to follow the standards as much as possible (while retaining the freedom to add things as deemed necessary .. LIMIT and friends). In a chat conversation with Jan, he asked me what my thoughts are in regards to the SQL standard in particular. Most people will for example agree that standards compliant SQL routines are no fun to write. Unfortunately I had to agree with him that SQL today isn't what it should be.

Since I am not sure if everybody is aware of this so just let …

mysqlsla v1.6 is ready. It has four new options to allow better isolation/filtering of queries: –only-databases, –only-users, –only-hosts, –only-ids. Each option limits the queries under analysis to their respective properties (i.e., “–only-users root” only analyzes queries executed by root). See the documentation for which options work with which kinds of logs.

One of MMM users reported that they’re experiencing really weird memory leaks in checker processes used by MMM. After a deep investigation I’ve found out that Perl part of the checker and checker modules does not leak (at least I didn’t found these leaks), so I think it could be caused by some problems in MySQL DBD module (client uses Ubuntu server).

So, I’d like to ask all users to check if their checker processes use more memory than expected and if yes, what OS, MySQL libraries versions and Perl version used on their servers.

Thanks in advance for any help.

Some small (but meaningful) updates in HEAD at http://consoleninja.net/code/dpm/dpm.git :

- You can now actually specify a username/password when connecting to a server. A few other similar spots were fixed.
- You can get the remote connection ID from a connection object.
- New myp.close() command for closing connections ;)

I've tested kill -9'ing clients, servers, in various states of transit with no obvious bugs. It's probably still unstable but these changes make a big difference.

I'm not an API designer by any stretch. I'm stuck wondering:

Should connobj:remote_id() (or just remote()) return the object id, or the full object?

On that same line, all callbacks have the affected connection ID as an argument. Should that just be a reference to the actual connection object, or continue to …

Some small (but meaningful) updates in HEAD at http://consoleninja.net/code/dpm/dpm.git :

- You can now actually specify a username/password when connecting to a server. A few other similar spots were fixed.
- You can get the remote connection ID from a connection object.
- New myp.close() command for closing connections ;)

I've tested kill -9'ing clients, servers, in various states of transit with no obvious bugs. It's probably still unstable but these changes make a big difference.

I'm not an API designer by any stretch. I'm stuck wondering:

Should connobj:remote_id() (or just remote()) return the object id, or the full object?

On that same line, all callbacks have the affected connection ID as an argument. Should that just be a reference to the actual connection object, or continue to …

I’ve been trying to [circle back]() and clean up things I left for later in several chapters of High Performance MySQL, second edition. This includes a lot of material in chapter 4, Schema Optimization and Indexing. At some point I’ll write more about the process of writing this book, and what we’ve done well and what we’ve learned to do better, but for right now I wanted to complete the picture of what material we have on schema, index, and query optimization.

Backcountry.com has been using open source for years. Last week, it finally gave something back. Unlike other web properties that have built their businesses on open source but acknowledge little need to contribute back, Backcountry.com has also given little back...but at least takes no pride in it. In fact, its press release announcing the code contribution is hilariously self-flagellating:

After mooching off the open source world for 10 years and making millions, Backcountry.com finally threw the Postgres community a bone in the form of a database synching tool curiously called Bucardo....

...

sysbench - Linux test bench. Easy as pie to test CPU, memory, threads, mysql, and disk performance.

Full description is available here: http://sysbench.sourceforge.net/docs/

install mysql, mysql-devel
wget http://superb-west.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz
tar xvzf sysbench*gz
cd sysbench*
./configure && make install

mysql tests

This will run 10 separate consecutive mysql tests using an InnoDB table type, each with 100 mysql threads, doing a total of 1000 various SQL operations per test. Then it will print the total time they took to finish:

sysbench --test=oltp --mysql-user=USER --mysql-password=PASS --mysql-db=test \\
  --mysql-host='HOST' --mysql-table-engine=innodb prepare
 
time perl -e "foreach(1..10){print \`sysbench --max-requests=1000 --test=oltp \\
  --mysql-user=USER …