We had bad experiences with MySQL 5.0.x suite. Some basic functionalities stopped working normally in some situation which are almost common that make me wonder about the stability and future of this database.

When we first changed on our servers the MySQL version from 4.1.x to the MySQL 5.0.15 we discovered that even if we didn't touch the application that use this database everything became slower. They say they improved the indexing engine in the 5.0 release but actually all the subselects and union queries start working very, very slow with the same indexes that we had on the 4.1. We had to rewrite couple of these queries, even to break them into multiple small queries and join them manually from the PHP to be able to achieve the same speed as before. 

After this the hell began. Queries started to not work anymore, data was crashed...

1. A client reported that some pages where empty even if the information existed into the database. Therefore we started investigation and found that a very basic query in unknown situations returned empty results:

SELECT * FROM table_a INNER JOIN table_b ON table_b.fk = table_a.pk

even if information was there. After multiple tries we discovered that this query however works:

SELECT * FROM table_a INNER JOIN table_b ON table_a.pk = table_b.fk

This query using an INNER JOIN the condition order should not count at all. The equality is the same no matter were you start evaluating the expression. But incredible, it does and produce different outputs. We still search for a reason why this problem appeared on those tables.

2. Another query that do was crashing with empty results was:

SELECT * FROM orders_ord WHERE status_ord IN ('Confirmed', 'Closed', 'Initialized');

changing this to:

SELECT * FROM orders_ord HAVING status_ord IN ('Confirmed', 'Closed', 'Initialized');

worked as expected. We managed to reproduce this bug on a very small data subset and reported back to them. We seen they marked the bug as fixed.

3. The most crazy bug we experienced was that a simple query like this:

SELECT * FROM table_a WHERE date_field < '2006-01-01 00:00:00'

do not returned any records but

SELECT * FROM table_a WHERE date_field < '2006-01-01 00:00:00' AND primary_key>0

had 400 results. The cause was a bug fixed in the InnoDB engine:

"Inserting a new row into an InnoDB table could cause DATETIME values already stored in the table to change. (Bug#13900)"

therefore many of the records where crashed with dates like: '?789-56-34' or '2005-12-30 10:23:99'. Because of these invalid dates I suspect that indexes were crashed as well. After upgrading to a newer version we had to take all the dates from the table and manually change them to some values that were at least correct as dates but the original data was lost.

We were curious after this MySQL version upgrade what bugs they fixed. By looking to the change logs we found some amaizing ones:

"A left join on a column that having a NULL value could cause the server to crash. (Bug#15268)"

"RPM packages had an incorrect zlib dependency. (Bug#15223)"

"mysqld would not start on Windows 9X operating systems including Windows Me. (Bug#15209)"

"The server crashed if compiled without any transactional storage engines. (Bug#15047)"

"Invoking a stored procedure within another stored procedure caused the server to crash. (Bug#13549)"

"SELECT with GROUP BY on a view can cause a server crash. (Bug#16382)"

"For a MySQL 5.0 server, using MySQL 4.1 tables in queries with a GROUP BY clause could result in buffer overrun or a server crash. (Bug#16752)"

" The absence of a table in the left part of a left or right join was not checked prior to name resolution, which resulted in a server crash. (Bug#15538)"

"Use of stored functions with DISTINCT or GROUP BY can produce incorrect results when ORDER BY is also used. (Bug#13575)"

"SELECT COUNT(*) for a MyISAM table could return different results depending on whether an index was used. (Bug#14980)"

"A LEFT JOIN with a UNION that selects literal values could crash the server. (Bug#17366)"

"Creating a table in an InnoDB database with a column name that matched the name of an internal InnoDB column (including DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR and DB_MIX_ID) would cause a crash.(Bug#18934)"

and many others. I am disapointed about this version 5.0 which is the worst one we had. Never encoutered in the normal usage experience such problems on 3.23.x, 4.0.x or 4.1.x.