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;

Replication: mysqldump --single-transaction --master-data is now able to take an online (nonblocking) 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”.

Replication: Two new 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, “Server System Variables”.

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.

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 system variables. These enable 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 innodb_locks_unsafe_for_binlog option in my.cnf, for an UPDATE or a DELETE, InnoDB locks only 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, “Server 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.

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

Added --start-datetime, --stop-datetime, --start-position, and --stop-position options to mysqlbinlog. These make 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 19, INFORMATION_SCHEMA Tables.

Replication: A problem introduced in MySQL 4.0.21 caused replication slaves to stop (error 1223) where a connection started a transaction, performed updates, then issued a FLUSH TABLES WITH READ LOCK followed by a COMMIT. This issue occurred when using the InnoDB innobackup script. (Bug#5949)

Replication: SET COLLATION_SERVER... statements replicated by the slave SQL thread no longer advance its position. This is so that, if the thread is interrupted before the update is completed, it later performs the SET again. (Bug#5705)

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

Replication: A CREATE TABLE ... TYPE=HEAP ... AS SELECT... statement caused the replication slave to stop. (Bug#4971)

mysqlbinlog was unable 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)

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 nontransactional update, that update automatically committed the transaction (thus releasing InnoDB's row-level locks etc). (Bug#5714)

If the slave SQL thread finds a syntax error in a query (which should be rare, as the master parsed it successfully), it now stops immediately. (Bug#5711)

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

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

mysql-test-run failed the rpl_trunc_binlog test when running the test from the installation directory. (Bug#5050)

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)

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)

KILLing a connection while it was performing START SLAVE caused the server to crash. (Bug#4827)

A deadlock could happen under certain rare circumstances when using KILL. (Bug#4810)

mysql-test-run failed the grant_cache test when run as Unix root user. (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)