Incompatible Change: The precedence of NOT operator has changed so that expressions such as NOT a BETWEEN b AND c are parsed correctly as NOT (a BETWEEN b AND c) rather than as (NOT a) BETWEEN b AND c. The pre-5.0 higher-precedence behavior can be obtained by enabling the new HIGH_NOT_PRECEDENCE SQL mode.

Incompatible Change: SHOW STATUS now shows the session (thread-specific) status variables and SHOW GLOBAL STATUS shows the status variables for the whole server.

Before MySQL 5.0.2, SHOW STATUS returned global status values. Because the default as of 5.0.2 is to return session values, this is incompatible with previous versions. To issue a SHOW STATUS statement that will retrieve global status values for all versions of MySQL, write it like this:

SHOW /*!50002 GLOBAL */ STATUS;

If a write to a MyISAM table fails because of a full disk or an exceeded disk quota, it now prints a message to the error log every 10 minutes, and waits until disk space becomes available. (Bug#3248)

Made the MySQL server ignore SIGHUP and SIGQUIT on Mac OS X 10.3. This is needed because under this OS, the MySQL server receives lots of these signals. (Bug#2030)

If the server finds that the user table has not been upgraded to include the view-related privilege columns, it treats each account as having view privileges that are the same as its CREATE privilege.

A connection doing a rollback now displays "Rolling back" in the State column of SHOW PROCESSLIST.

Renamed the sql_updatable_view_key system variable to updatable_views_with_limit. This variable now can have only two values:

Reverted output format of SHOW TABLES to old pre-5.0.1 format that did not include a table type column. To get the additional column that lists the table type, use SHOW FULL TABLES now.

CHECK TABLE now works for views.

Modify DROP USER so that it drops the account, including all its privileges. Formerly, it removed the account record only for an account that had all privileges revoked.

mysqldump --single-transaction --master-data is now able to take an online (non-blocking) dump of InnoDB and report the corresponding binary log coordinates, which makes a backup suitable for point-in-time recovery, roll-forward or replication slave creation. See Section 4.5.4, “mysqldump — A Database Backup Program”.

0 or NO: Prohibit update of a VIEW, which does not contain a key in the underlying table and the query uses a LIMIT clause (usually get from GUI tools).

We now detect too-large floating point numbers during statement parsing and generate an error messages for them.

New --auto-increment-increment and --auto-increment-offset startup options. These allow you to set up a server to generate auto-increment values that don't conflict with another server.

Added the CREATE USER and RENAME USER statements.

MySQL now by default checks dates and in strict mode allows only fully correct dates. If you want MySQL to behave as before, you should enable the new ALLOW_INVALID_DATES SQL mode.

Added NO_AUTO_CREATE_USER SQL mode to prevent GRANT from automatically creating new users if it would otherwise do so, unless a password also is specified.

MySQL now remembers which columns were declared to have default values. In STRICT_TRANS_TABLES/STRICT_ALL_TABLES mode, you now get an error if you do an INSERT without specifying all columns that don't have a default value. A side effect of this is that when you do SHOW CREATE for a new table, you no longer see a DEFAULT value for a column for which you didn't specify a default value.

InnoDB: If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB in an UPDATE or a DELETE only locks the rows that it updates or deletes. This greatly reduces the probability of deadlocks.

A HAVING clause in a SELECT statement now can refer to columns in the GROUP BY clause, as required by standard SQL.

The SCHEMA and SCHEMAS keywords are now accepted as synonyms for DATABASE and DATABASES.

Added several InnoDB status variables. See Section 5.1.6, “Status Variables”.

Added STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and TRADITIONAL SQL modes. The TRADITIONAL mode is shorthand for all the preceding modes. When using mode TRADITIONAL, MySQL generates an error if you try to insert a wrong value in a column. It does not adjust the value to the closest possible legal value.

The mysql_fix_privilege_tables script now initializes the global CREATE VIEW and SHOW VIEW privileges in the user table to the value of the CREATE privilege in that table.

1 or YES: Don't issue an error message (warning only) if a VIEW without presence of a key in the underlying table is used in queries with a LIMIT clause for updating. (This is the default value.)

The compilation flag DONT_USE_DEFAULT_FIELDS was removed because you can get the same behavior by setting the sql_mode system variable to STRICT_TRANS_TABLES.

mysqlbinlog now prints an informative commented line (thread id, timestamp, server id, and so forth) before each LOAD DATA INFILE, like it does for other queries; unless --short-form is used.

Two new server system variables were introduced. auto_increment_increment and auto_increment_offset can be set locally or globally, and are intended for use in controlling the behavior of AUTO_INCREMENT columns in master-to-master replication. Note that these variables are not intended to take the place of sequences. See Section 5.1.3, “System Variables”.

Added IS [NOT] boolean_value syntax, where boolean_value is TRUE, FALSE, or UNKNOWN.

Added --start-datetime, --stop-datetime, --start-position, --stop-position options to mysqlbinlog (makes point-in-time recovery easier).

Added initial support for rudimentary triggers (the CREATE TRIGGER and DROP TRIGGER statements).

Added basic support for read-only server side cursors.

Implemented the WITH CHECK OPTION clause for CREATE VIEW.

Added support for the INFORMATION_SCHEMA “information database” that provides database metadata. See Chapter 24, INFORMATION_SCHEMA Tables.

Fixed a bug which prevented mysqlbinlog from being able to read from stdin, for example, when piping the output from zcat to mysqlbinlog. (Bug#7853)

If a connection was interrupted by a network error and did a rollback, the network error code got stored into the BEGIN and ROLLBACK binary log events; that caused superfluous slave stops. (Bug#6522)

Fixed problem introduced in MySQL 4.0.21 where a connection starting a transaction, doing updates, then FLUSH TABLES WITH READ LOCK, followed by COMMIT, would cause replication slaves to stop (error 1223). This issue occurred when using the InnoDB innobackup script. (Bug#5949)

If a connection had an open transaction but had done no updates to transactional tables (for example if had just done a SELECT FOR UPDATE then executed a non-transactional update, that update automatically committed the transaction (thus releasing InnoDB's row-level locks etc). (Bug#5714)

Fixed that if the slave SQL thread found a syntax error in a query (which should be rare, as the master parsed it successfully), it stops. (Bug#5711)

Fixed slave SQL thread so that the SET COLLATION_SERVER... statements it replicates don't advance its position (so that if it gets interrupted before the actual update query, it later redoes the SET). (Bug#5705)

OPTIMIZE TABLE, REPAIR TABLE, and ANALYZE TABLE are now replicated without any error code in the binary log. (Bug#5551)

Fixed that disable-local-infile option had no effect if client read it from a configuration file using mysql_options(...,MYSQL_READ_DEFAULT,...). (Bug#5073)

Fixed that SET GLOBAL SYNC_BINLOG did not work on some platforms (Mac OS X). (Bug#5064)

Fixed that mysql-test-run failed on the rpl_trunc_binlog test if running test from the installed (the target of 'make install') directory. (Bug#5050)

Fixed that mysql_options(...,MYSQL_OPT_LOCAL_INFILE,...) failed to disable LOAD DATA LOCAL INFILE. (Bug#5038)

The counter for an AUTO_INCREMENT column was not reset by TRUNCATE TABLE if the table was a temporary one. (Bug#5033)

Fixed that CREATE TABLE ... TYPE=HEAP ... AS SELECT... caused replication slave to stop. (Bug#4971)

FLUSH TABLES WITH READ LOCK now blocks COMMIT statements if the server is running with binary logging enabled; this ensures that the binary log position is trustable when doing a full backup of tables and the binary log. (Bug#4953)

Fixed a crash when one connection got KILLed while it was doing START SLAVE. (Bug#4827)

Fixed an unlikely deadlock which could happen when using KILL. (Bug#4810)

Fixed that mysql-test-run failed on the grant_cache test when run as Unix user 'root'. (Bug#4678)

mysqlbinlog --read-from-remote-server sometimes could not accept 2 binary logs in a single invocation. (Bug#4507)

mysqlbinlog --position --read-from-remote-server had incorrect output for # at log_pos. (Bug#4506)