By default MySQL allows you to create user accounts and privileges with no password. In my earlier MySQL Best Practices: User Security I describe how to address the default installation empty passwords.

For new user accounts, you can improve this default behavior using the SQL_MODE variable, with a value of NO_AUTO_CREATE_USER. As detailed via the 5.1 Reference Manual

NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified.

Having set this variable I attempted to show the error of operation to demonstrate in my upcoming “MySQL Idiosyncrasies that bite” presentation. …

4 min 20 sec

So next time somebody complains about NDB taking a long time in CREATE TABLE, you’re welcome to point them to this :)

• A single CREATE TABLE statement
• It had ONE column
• It was an ENUM column.
• With 70,000 possible values.
• It was 605kb of SQL.
• It ran on Drizzle

This was to test if you could create an ENUM column with greater than 216 possible values (you’re not supposed to be able to) – bug 589031 has been filed.

How does it compare to MySQL? Well… there are other problems (Bug 54194 – ENUM limit of 65535 elements isn’t true filed). Since we don’t have any limitations in Drizzle due to the FRM file …

Been at the MySQL conference the last few days, and I have to say, I’m really blown away by MySQL 5.5.4‘s improvements.  Last year I keynoted and I begged Oracle on stage to realize that MySQL and InnoDB under one roof represented opportunity.  It’s clear they heard the community – this is some serious progress, and right when we needed it.

Jeremy Zawodny’s blog post covers most of the stuff I’m really excited about, and there are some great detailed technical slides …

Since Ubuntu 8.04 aka Hardy Heron, I've had issues with every new release. As Ubuntu evolves into being a viable desktop OS alternative, its complexity has been growing and with the new and improved looks new challenges arise. This bug in particular has been very difficult to diagnose and I can't imagine anyone without enough Linux experience to overcome it on their own, so I decided to summarize the steps I took to fix it ... and vent my frustration at the end.
The SymptomI came across the issue for the first time while trying Ubuntu's Karmic Netbook remix. After overcoming the typical Broadcom wifi driver, Network Manager would connect, but Firefox would fail to load the web pages 90% of the time. Using ping in the command line worked just fine. Maybe I needed to update the software packages to get the latest patches, surprise, apt-get was having similar problems and timing out. So the problem …

Do you know if your production MySQL servers will come back up when restarted? A recent support episode illustrates a number of best practices. The task looked trivial: Update a production MySQL server (replication master) with a configuration tuned and tested on a development server. Clean shutdown, change configuration, restart. Unfortunately, the MySQL daemon did not just ‘come back’, leaving 2 sites offline. Thus begins an illuminating debugging story. First place to look is the daemon error log, which revealed that the server was segfaulting, seemingly at the end of or just after InnoDB recovery. Reverting to the previous configuration did not help, nor did changing the InnoDB recovery mode. Working with the client, we performed a failover to a replication slave, while I got a second opinion from a fellow engineer to work out what had gone wrong on the server. Since debug symbols weren’t shown in the stack trace, we needed …

edit: I filed MySQL bug#46867 about this issue.

Ask anyone who has learned to count the following two questions. The answer to both of which should be yes.

Q:Is 5 between 1 and 10? A: Yes

Q:Is 5 between 10 and 1? A: Yes

Ask MySQL those same questions:

mysql>  (select 'Yes' 
           from dual 
          where 5 between 1 and 10
        ) 
        union 
        (select 'No'
        ) 
        limit 1;
+-----+
| Yes |
+-----+
| Yes |
+-----+
1 row in set (0.00 sec)

mysql>  (select 'Yes' 
          from dual 
         where 5 between 10 and 1 
        ) 
        union 
        (select 'No') 
        limit 1;
+-----+
| Yes |
+-----+
| No  |
+-----+
1 row in set (0.00 sec)

This is a problem because applications may produce BETWEEN clauses. I don't think most …

I just filed a very annoying bug when trying to compile with plugin engines using the 5.1.xx source tarball.

Description

I am trying to test SphinxSE as a plugin instead of getting it statically linked and came across an annoying bug. When using the configure --with-plugins option only once, the engine is statically linked. When using it twice, the first engine is created as a plugin, and the 2nd one is linked statically. Here are a couple of examples:./configure –prefix=/usr/local/mysql-5.1.33 –with-plugins=innobase –with-plugins=sphinx

plugin_innobase_shared_target='ha_innodb.la'   <-- plugin
plugin_innobase_static_target=''
plugin_sphinx_shared_target=''
plugin_sphinx_static_target='libsphinx.a'       <-- static
./configure --prefix=/usr/local/mysql-5.1.33 --with-plugins=sphinx …

The MySQL manual for str_to_date states:
If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An
illegal value also produces a warning.

Surely "I'm_not_a_valid_date" is not a valid date, time or datetime value.

mysql> select str_to_date("I'm_not_a_valid_date","I'm_not_a_valid_date");
+------------------------------------------------------------+
| str_to_date("I'm_not_a_valid_date","I'm_not_a_valid_date") |
+------------------------------------------------------------+
| 0000-00-00                                                 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

The problem here is that values in the format string which are not preceded by the percent sign (%) are treated as constant characters which must match the input string exactly. Normally these characters are used as delimiters. For example, …

From the MySQL manual:
BIT_COUNT(N)

Returns the number of bits that are set in the argument N.

-------

I thought that meant that it returned the number of bits which needed to be set in order to store the value. To store 2^32 you need 33 bits of storage, with only one bit /set/. I expected 33 instead of 1.

#tested on 5.0.45 and 5.1.32-community
mysql> select i, pow(2,i), bit_count(pow(2,i)) from pow2;
+----+------------+---------------------+
| i  | pow(2,i)   | bit_count(pow(2,i)) |
+----+------------+---------------------+
|  1 |          2 |                   1 |
|  2 |          4 |                   1 |
|  3 |          8 |                   1 |
|  4 |         16 |                   1 |
|  5 |         32 |                   1 |
|  6 |         64 |                   1 |
|  7 |        128 |                   1 |
|  8 |        256 |                   1 |
|  9 |        512 | …

If you are using InnoDB Hot Backup utility and the innobackup.pl wrapper script, be very careful if you are not running backups under the system mysql user. There is a bug which causes InnoDB Hot Backup to sometimes report a successful backup when it actually failed.

The last few dozen lines of the output was from the backup was (after generalizing the db and table names):

InnoDB: File name /var/lib/mysql/data/dbname/TABLE_A.frm
InnoDB: File operation call: 'stat'.
090210Â  3:55:00Â  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name /var/lib/mysql/data/dbname/TABLE_B.frm
InnoDB: File operation call: 'stat'.
090210Â  3:55:00Â  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name …