The USA's health care exchange site, healthcare.gov, has had well-publicized initial woes.

The New York Times has said one of the problems was the government's choice of DBMS, namely MarkLogic. A MarkLogic employee has said that "If the exact same processes and analysis were applied to a LAMP stack or an Oracle Exa-stack, the results would have likely been the same."

I don't know why he picked Exastack for comparison, but I too have wondered whether things would have been different if the American government had chosen a LAMP component (MySQL or MariaDB) as a DBMS, instead of MarkLogic.

What is MarkLogic?

The company is a software firm founded in 2001 based in San Carlos California. It has …

Many people have tested whether the TokuDB storage engine runs faster and compresses better than the default storage engine, InnoDB. I am more concerned about TokuDB's features. Or, to put it unfairly: can it do everything that InnoDB can do, in a current version?

Vadim Tkachenko of Percona supplied a binary download for a platform that I happen to have (Ubuntu 12.04), so I got MySQL 5.6 + TokuDB 7.1 up and running within 15 minutes. Then I found that I could not break anything within 60 minutes. I I conclude that "ease of use" and "stability" are okay. (For anyone who thinks I'm rushing: yes, but usually finding a bug in a new version takes less than an hour so I'm judging from experience.)

Almost everything = No problem

I …

In English there are very few words of Japanese origin but I think this one has a great future: mojibake. Mojibake is the garbage you see when MySQL or MariaDB has a column definition saying character set A, stores into it a string that's actually in character set B, then ships it to a client which expects everything to be in character set C.

For some DBMSs (Drizzle and NuoDB spring to mind) it's apparent that the developers decided that users could avoid mojibake, and developers could avoid a lot of extra trouble, if everybody had the same character set: UTF-8. Well, MySQL and MariaDB have web users and UTF-8 is popular on the web. According to Web Technology Surveys, 80% of web …

Ten years ago, MySQL got a "bug report" about trouble with RLIKE. It got marked "won't fix" because MySQL used a regular-expression library that couldn't handle non-ASCII characters reliably. Over time there were multiple similar or duplicate bug reports but the only result was a note in the MySQL manual saying, in effect, "tough luck". Until now.

Actually the fix is in a pre-release of the bzr branch for MariaDB 10.0.5, and I can't predict that the MySQL folks will copy it, but it looks good to me, and I clap for the original coders who made the "Perl Compatible Regular Expressions" library, the "Google Summer of Code" folks who …

I'll show a chart which indicates the level of support for trigger features in major open-source DBMSs.
I'll comment on new features in MySQL 5.7.
I'll show how triggers can be used to abort statements which are taking too long.

Trigger features in major open-source DBMSs

Here are examples comparing MySQL 5.6 against PostgreSQL 9.3 Core Distribution, where MySQL seems to comply with "standard SQL" more closely than PostgreSQL does. The examples are also true for MariaDB 10.0 so whenever I say "MySQL" I mean "MySQL and/or MariaDB". When I say "more closely" I do not mean that MySQL is completely compliant, or that PostgreSQL is completely non-compliant.

Identifiers

Example:

CREATE TABLE          ŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽ (s1 INT); /* 32-character name */
SELECT COUNT(*) FROM information_schema.tables
  WHERE table_name = 'ŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽ';
SELECT COUNT(*) FROM information_schema.tables
  WHERE table_name LIKE LOWER('Ž%');

Result:
PostgreSQL says count(*) is 0. MySQL says it's 1.
Reason:
(1) PostgreSQL maximum identifier length is 63 bytes; MySQL maximum identifier length is 64 …

The MySQL Reference Manual advises:

Use a CREATE TABLE statement to specify the layout of your table:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
...
Several types of values can be chosen to represent sex in animal records, such as 'm' and 'f', or perhaps 'male' and 'female'. It is simplest to use the single characters 'm' and 'f'.

I will supplement that with this post about representing sex (gender) in people records. I have to consider the name, the possible values, the data type, and the constraints. It won't be simplest.

Should the column name be 'sex'?

First consider …

When I worked for MySQL I saw frequent complaints that the UNIQUE constraint didn't stop users from inserting NULLs, multiple times. For example:
CREATE TABLE t (s1 INT, UNIQUE (s1));
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL); /* This does not cause a "unique constraint violation" error */

There are now eleven generously-commented bugs.mysql.com reports:
#5685, #6829, #7479, #8173, #9844, #17825, #19377, …