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 …

It looks like this bug finally got some attention, and a patch is queued. http://bugs.mysql.com/bug.php?id=38187
In brief, the bug happens when you alternate and reuse names of savepoints. Quoting from the bug page:

InnoDB treats the savepoints like a stack, e.g.,    SAVEPOINT a;   SAVEPOINT b;   SAVEPOINT c;   SAVEPOINT b; 

Filed  GCC bug 39228:

#include <stdio.h>
#include <math.h>
int main()
{
        double a= 10.0;
        double b= 1e+308;
        printf("%d %d %dn", isinf(a*b), __builtin_isinf(a*b), __isinf(a*b));
        return 0;
}

mtaylor@drizzle-dev:~$ gcc -o test test.c
mtaylor@drizzle-dev:~$ ./test
0 0 1
mtaylor@drizzle-dev:~$ gcc -o test test.c -std=c99
mtaylor@drizzle-dev:~$ ./test
1 0 1
mtaylor@drizzle-dev:~$ gcc -o test test.c   -mfpmath=sse -march=pentium4
mtaylor@drizzle-dev:~$ ./test
1 1 1
mtaylor@drizzle-dev:~$ g++ -o test test.c
mtaylor@drizzle-dev:~$ ./test
1 0 1

Originally I found the simple isinf() case to be different on x86 than x86-64, ppc32 and sparc (32 and 64).

After more research, I found that x86-64 uses the sse …

Here the division between td1.c1 and td2.c2 is correct:

select td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03    
  from testdata td1,         
       testdata td2   
 where td1.c1 = -99 
   and td2.c2 = 0.03 
 limit 1;

+------+------+---------------+------------+
| c1   | c2   | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
|  -99 | 0.03 |    -3300.0000 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.00 sec)

Here DISTINCT is added to the query.  The result is incorrect:

select distinct td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03    
  from testdata td1,         
       testdata td2   
 where td1.c1 = -99 
   and td2.c2 = 0.03 
 limit 1;

+------+------+---------------+------------+
| c1   | c2   | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
|  -99 | 0.03 |     -999.9999 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.00 sec)

…