Software Update: MySQL 4.0.5 Beta

The MySQL developers today released version 4.0.5 of the MySQL database. MySQL 4.0 is still in beta stage. Meanwhile, MySQL AB has also started developing MySQL 4.1, which will include support for subqueries. You can download the source code and binaries for the various operating systems here to download. It changelog of version 4.0.5 is as follows:

Functionality added or changed:

• Fixed the bug that caused IGNORE INDEX and USE INDEX sometimes to be ignored.
• Added –use-frm option to mysqlcheck. When used with REPAIR, it gets the table structure from the .frm file, so the table can be repaired even if the .MYI header is corrupted.
• Fixed bug in MAX() optimization when used with JOIN and ON expressions.
• Added support for reading of MySQL 4.1 table definition files.
• BETWEEN behavior changed (see section 6.3.1.2 Comparison Operators). Now datetime_col BETWEEN timestamp AND timespamp should work as expected.
• One can create TEMPORARY MERGE tables now.
• DELETE FROM myisam_table now shrinks not only `.MYD’ but also `.MYI’ file.
• When one uses the –open-files-limit=# option to mysqld_safe it’s now passed on to mysqld
• Changed output from EXPLAIN from ‘where used’ to ‘Using where’ to make it more in line with other output.
• Removed variable safe_show_database as it was not used anymore.
• Updated source tree to be built using automake 1.5 and libtool 1.4.
• Fixed an inadvertently changed option (–ignore-space) back to the original –ignore-spaces in mysqlclient. (Both syntaxes will work).
• Don’t require UPDATE privilege when using REPLACE.
• Allow braces in joins in all positions. Formerly, things like SELECT
• FROM (t2 LEFT JOIN t3 USING (a)), t1 worked, but not SELECT
• FROM t1, (t2 LEFT JOIN t3 USING (a)). Note that braces are simply removed, they do not change the way the join is executed.

Bug fixed:

• Corrected test for root user in mysqld_safe.
• Fixed error message issued when table handler cannot do CHECK or REPAIR.
• Fixed rare core dump problem in complicated GROUP BY queries that didn’t return any result.
• Fixed that mysqlshow works properly with wildcarded database names and with database names that do contain underscore(s) in it
• Added support for DROP TEMPORARY TABLE …, to be used to make replication safer.
• When transcations are enabled all commands that update temporary tables inside a BEGIN/COMMIT are now stored in the binary log on COMMIT and not stored if one does ROLLBACK. This fixes some problems with not transactional temporary tables used inside transactions.
• Portability fixes to get MySQL to compile cleanly with Sun Forte 5.0.
• Fixed MyISAM crash when using dynamic-row tables with huge numbers of packed fields
• Fixed query cache behavior with BDB transactions.
• Fixed possible floating point exception in MATCH relevance calculations.
• Fixed bug in fulltext search IN BOOLEAN MODE that made MATCH to return incorrect relevance value in some complex joins.
• Fixed a bug that limited MyISAM key length to a value slightly less that 500. It is exactly 500 now.
• Fixed that GROUP BY on columns that may have a NULL value doesn’t always use disk based temporary tables.
• Read –des-key-file relative to database directory.
• Removed a condition that temp table with index on column that can be NULL has to be MyISAM. This was OK for 3.23, but not needed in 4.*. This resulted in slowdown in many queries since 4.0.2.
• Small code improvement in multi-table updates
• Fixed a newly introduced bug that caused ORDER BY … LIMIT # to not return all rows.
• Fixed a bug in multi-table deletes when outer join is used on an empty table, which get’s first to be deleted
• Fixed a bug in multi-table updates when a single table is updated
• Fixed bug that caused REPAIR TABLE and myisamchk to corrupt FULLTEXT indexes.
• Fixed bug with caching the mysql grant table database. Now queries in this database are not cached in the query cache.
• Small fix in mysqld_safe for some shells.
• Give error if a MyISAM MERGE table has more than 2 ^ 32 rows and MySQL was not compiled with with -DBIG_TABLES. * Fixed some ORDER BY … DESC problems with InnoDB tables.

MySQL/InnoDB-4.0.5, November 18, 2002

• InnoDB now supports also transaction isolation levels READ COMMITTED and READ UNCOMMITTED. READ COMMITTED more closely emulates Oracle and makes porting of applications from Oracle to MySQL easier.
• Deadlock resolution is now selective: we try to pick as victims transactions with less modified or inserted rows.
• FOREIGN KEY definitions are now aware of the lower_case_table_names setting in my.cnf.
• SHOW CREATE TABLE does not output the database name to a FOREIGN KEY definition if the referred table is in the same database as the table.
• InnoDB does a consistency check to most index pages before writing them to a data file.
• If you set innodb_force_recovery > 0, InnoDB tries to jump over corrupt index records and pages when doing SELECT
• FROM table. This helps in dumping.
• InnoDB now again uses asynchronous unbuffered i/o in Windows 2000 and XP, but not in NT, 95/98/ME.
• Fixed a bug: the InnoDB range estimator greatly exaggerated the size of a short index range if the paths to the endpoints of the range in the index tree happened to branch already in the root. This could cause unnecessary table scans in SQL queries. The fix will also be backported to 3.23.54.
• Fixed a bug present in 3.23.52, 4.0.3, 4.0.4: InnoDB startup could take very long or even crash on some Win 95/98/ME computers.
• Fixed a bug: the AUTO-INC lock was held to the end of the transaction if it was granted after a lock wait. This could cause unnecessary deadlocks.
• Fixed a bug: if SHOW INNODB STATUS, innodb_monitor, or innodb_lock_monitor had to print several hundred transactions in one report, and the output became truncated, InnoDB would hang, printing to the error log many waits for a mutex created at srv0srv.c, line 1621.
• Fixed a bug: SHOW INNODB STATUS on Unix always reported average file read size as 0 bytes.
• Fixed a potential bug in 4.0.4: InnoDB now does ORDER BY … DESC like MyISAM.
• Fixed a bug: DROP TABLE could cause crash or a hang if there was a rollback concurrently running on the table. The fix will only be backported to 3.23 if this appears a real problem for users.
• Fixed a bug: ORDER BY could fail if you had not created a primary key to a table, but had defined several indexes of which at least one was a UNIQUE index with all its columns declared as NOT NULL.
• Fixed a bug: a lock wait timeout in connection with ON DELETE CASCADE could cause corruption in indexes.
• Fixed a bug: if a SELECT was done with a unique key from a primary index, and the search matched to a delete-marked record, InnoDB could return the NEXT record.
• Outstanding bugs: in 4.0.4 two bugs were introduced to AUTO_INCREMENT. REPLACE can cause the counter to be left 1 too low. A deadlock or a lock wait timeout can cause the same problem.