Backward-incompatible changes in MySQL 5.0 through 5.7

Tabulated by Artful Software Development

Versions Url Item
5.0 New reserved words in MySQL 5.0
5.0.1   C API change: mysql_shutdown() now requires a second argument.
5.0.2  Precedence of NOT operator, revert to previous behaviour wuth new HIGH_NOT_PRECEDENCE SQL mode
    SHOW STATUS now shows Session status variables, SHOW GLOBAL STATUS shows Global variables
5.0.3  C API ER_WARN_DATA_TRUNCATED warning symbol renamed to WARN_DATA_TRUNCATED.
    DECIMAL and NUMERIC data types handled with fixed-point library (Precision Math)
5.0.6  LOAD DATA INFILE, SELECT ... INTO OUTFILE: columns are read and written with field width wide enough to hold all values
    MyISAM & InnoDB Decimal columns incompatible with those created with 5.0.3 or 5.0.5 
5.0.8  Format of result of conversion by DATETIME+0 used to be YYYYMMDDHHMMSS format, now YYYYMMDDHHMMSS.000000 
5.0.10  Trigger names unique in database namespace rather than table namespace
5.0.12  Joins upgraded to SQL:2003 standard, breaking some comma-join queries written for previous versions. See "Join Processing Changes in MySQL 5.0.12" in 
InnoDB lock wait timeout caused roll back of entire current transaction, now only the most recent SQL statement.
5.0.14  Pad value for Binary columns changed from space to 0x00
5.0.15  Char() now returns a Binary string, accepts a USING charset argument, and breaks arguments > 255 into multiple result bytes, eg Char(65536) means Char(1,0,0) (but the mysql client interprets it incorrectly)
5.0.19  InnoDB now includes trailing spaces when comparing [Var]Binary column values.
New mysql_upgrade progam to check all tables and if necessary update them for backward incompatibility, and new Check Table For Upgrade option
Grant | Revoke ... On * requires a default database, throws an error if there is not
5.0.24 introduced an application binary interface incompatibility, which 5.0.24a reverts. 
5.0.27  5.0.26 introduced an application binary interface incompatibility, which 5.0.27 and Enterprise 5.0.28 revert.
5.0.30sp1  In 5.0.30sp1 and 5.0.32 Enteprise, --innodb_rollback_on_timeout emulates pre-5.0.14 behaviour in causing InnoDB to abort and roll back the entire transaction on a transaction timeout
5.0.33  System variable prepared_stmt_count changed to global status variable Prepared_stmt_count (see SHOW GLOBAL STATUS).
Commas in ENUM column values are no longer mapped internally to  0xff, so tables with true 0xff values must be dumped with a pre-5.0.36 MySQL version, then reloaded with a 5.0.36 or later MySQL version.
Cluster configuration parameter LockPagesInMainMemory changes type and possible values. See LockPagesInMainMemory 
    DATE_FORMAT() returns string using character_set_connection and collation_connection rather than a binary string to support day and month names with non-ASCII characters.
5.0.45  INSERT DELAYED statements not supported for, and no longer corrupts MERGE tables,
Date-DateTime comparison now coerces time portion of Date to 00:00:00 rather than ignoring it or treating the values as strings. For backward compatibility use Cast(datetimeval, Date)
Views invoking stored functions and created by 5.0.40 through 5.0.43 or 5.1.18-5.1.19 must be dropped and recreated
    Mysqldump --delete-master-logs option deleted log files before success is known. Now flushes logs, does the dump, then purges the logs, so log numbering differs from pre-5.0.42.
    Group_Concat() query with Order By or Distinct clause could redisplay results from previous queries. The fix to this may cause truncation, in which case a warning is issued.
5.0.51  Select statements containing unclosed /* now elicit syntax errors, may affect query parsing in stored procedures
5.0.52  In Delete commands, alias declarations may be declared only in table references
    Failing to declare collations may affect indexing 
    If Sql Mode includes Only Full Group By, reject queries invoking Having without Group By
    Ensure that alias names in Views follow column name rules
    Coalesce(), If(), Ifnull(), Least(), Greatest(), Case, Str_To_Date() now return correct result types
5.0.67  Changes in handling of incompatible .frm files
Frac_Second deprecated; with anything other than Timestampadd() or Timestampdiff() produces syntax error.
Federated engine disabled by default in shipped example .cnf files
Changes to initialization code in libmysql.dll
    Status variable Innodb_buffer_pool_pages_latched shown only if UNIV_DEBUG is defined at MySQL build time
Lose unused database directory arc folders for Views
Supplied binary-configure script no longer starts and configures MySQL with --help command-line option.
5.1 New reserved words in MySQL 5.1
5.1.3  New plugin system
    Renamed table_cache system variable to table_open_cache 
5.1.7  C API mysql_stmt_attr_get() function now returns boolean rather than unsigned int for STMT_ATTR_UPDATE_MAX_LENGTH.
    Changes in partition naming rules
    Type=... no longer accepted as synonym for Engine=... in Create Table
5.1.8  cluster_replication database renamed to cluster 
    Changes in semantics for Alter Table for partitions
5.1.11  Changes in Event scheduler and
5.1.12  Cluster: allow different fragments to use different LCPs during restarts, allow access to information_schema.files from all engines
    Support for Berkeley DB (BDB) dropped
    Changes in partitioning syntax, fulltext parser plugin interface, utf8 fulltext whitespace handling
    Events now in database namespaces, not user namespaces
    Changes in sql_mode=IGNORE_SPACE 
    System variable innodb_buffer_pool_awe_mem_mb system removed
5.1.14  Cluster system table changes
    System variable prepared_stmt_count changed to global status variable named Prepared_stmt_count 
    Creating a UDF named the same as a built-in function generates an error, and creating a stored function with the same name as a built-in function generates a warning.
5.1.15  Changes to effects of enabling read_only system variable
    Archive torage engine no longer creates .ARM metadata file for each table. 
5.1.16  NDB API: AbortOption now a member of dbOperation class with different values & behaviour, see The NdbOperation::AbortOption Type 
    Create | Alter Event now use session time zone 
5.1.17  Merge tables now reject Insert Delayed
5.1.18  Cluster: changed storage specs for column specification
    Event tables changed to facilitate replication. Run mysql_upgrade before working with events.
    Cluster: Changed definition of mysql.ndb_apply_status table
    Changes in system variables related to plugins
    Dump events created pre-5.1.18 and restore only after upgrading to 5.1.18 or later. Problem fixed in 5.1.20.
5.1.19  Insert Delayed is changed to normal Insert if Insert uses functions that access tables or triggers, or is called from a function or trigger. 
5.1.20  Log tables can no longer be partitioned
    mysqld_safe now supports error logging to syslog on systems that support logger command
    Error code values adjusted to match 5.0
5.1.21  Default log destination changed from Table to File.
    System variable innodb_log_arch_dir removed
    Rebuild indexes for columns that use eucjpms, euc_kr, gb2312, latin7, macce or ujis charsets
    mysqkd-nt.exe removed from Windows releases. Use mysqld.
    New columns in mysql.proc, and to manage charsets & collations in stored programs
5.1.24  Reverted a 5.1.23 constant name change in NET structure in mysql_com.h
    Reverted a change in handling of Nulls in Updates 
See also 
    Change in plugin location
    Rebuild indexes using utf_general_ci or ucs2_general_ci charsets
5.1.25  Detect metadata changes to tables or views referred to by prepared statements and automatically reprepare
5.1.27  Rename or drop partitioned tables using mixed case names before upgrading to 5.1.27 on Mac OS X
5.1.29  Changed default binary log mode from Mixed to Statement
    Check Table ... For Upgrade now checks for incorrect collation changes made in 5.1.21
5.1.36  Changes in handling of plugins
5.5 New reserved words in MySQL 5.5
5.5.0  New plugin interface
    Changes in error message language and charset
    Changes in access privileges for several statements
5.5.1  Changes in naming of semisynchronous plugins
5.5.2  Changes in plugin.h

log_bin_trust_routine_creators => log_bin_trust_function_creators and several other variable name changes
Create Table(...)Type=... => Create Table(...) Engine=...
Show Table Types => Show Engines
TimeStamp(N) => TimeStamp()
TimeStampAdd(...frac_second...) => TimeStampAdd(...microsecond...)
Show InnoDB Status, Show Mutex Status => Show Engine InnoDB Status, Show Engine InnoDB Mutex
Select wildcard aliases disallowed
String conversions respect collation settings
New columns in information_schema.routines
Reset master | Slave reset Last_IO_Error, Last_IO_Errno, Last_SQL_Error, Last_SQL_Errno in Show Slave Status

5.5.6 Sha2() & other encryption return values now respect connection character set & collation settings
Flush Logs no longer renames old logfile
InnoDB now truncates using Create & Drop Table
In statement-based replication, Insert Delayed now logged as Insert
handler...Read disallows calls to stored functions
5.5.8 performance_schema table names now lower case, some columns renamed
Flush Table With Read Lock redesigned
auto_increment values can no longer reach maximum BigInt Unsigned value
5.5.10 New C API library version
Class member change in audit plugin
5.5.18 Several statements newly marked unsafe for statement-based replication
5.6 New reserved words in MySQL 5.6 (warning: there are very many!)
5.6.1 Several server variables removed
Replication: database-level statements take precedence over table wildcards
Lock Tables blocks Create Table
5.6.2 Flush Master | Slave removed

Changes in timeout handling

5.6.4 Timestamp(n) no longer sets display width, now specifies 0<=n<=6 microsecond digits. InnoDB now supports FullText.
5.6.5 Any number of Timestamp columns may default to Now() on insert and/or on update.
5.6.6 innodb_file_per_table enabled (previously disabled) , innodb_checksum_algorithm now CRC32 (previously InnoDB) , binlog_checksum now CRC32 (previously NONE)
5.6.7 Alter Table commands that would lose data by changing a Foreign Key are now blocked.
Replication: Within a transaction, Change Master To, Start Slave, Stop Slave and Reset Slave induce a Commit.
5.6.9 Several GTID-related variable name changes.
Last_Insert_Id() now returns a Bigint Unsigned value, not a Bigint (signed) value.
5.6.11 backward-incompatible changes in partiton Key syntax and handling
5.6.12 Because IDENTIFIED WITH is intended for Grant statements that create a new user are now prohibited if the named account already exists.
5.6.13 Backward-incompativle performance schema change.
5.6.14 In 5.6.13 the statement/com/ abstract statement instrument in the Performance Schema setup_instruments table was renamed to statement/com/new_packet. That is reverted in 5.6.14.
Several more performance_schema renamings.
5.6.17 AES_DECRYPT() and AES_ENCRYPT() take a vector argument to control block encryption mode.
5.7.1 Key partitioning hashing function changes in 5.5 make tables using key partitioning created on a 5.5 or later server incompatible with a 5.1 server.
5.7.2 mysql.user.plugin now defined as Not Null, run mysql_upgrade.
log_error_verbosity is preferred to log_warnings.
5.7.4 mysql.user table has new columns password_last_changed and password_lifetime, run mysql_upgrade.
5.7.5 mysql_install_db isnow an executable written in C++ rather than a Perl script.
5.7.5 InnoDB engine can no longer be disabled.
5.7.5 Support for pre-4.1 passwords removed.
5.7.5 Many sql_mode changes.
5.7.5 Year(2) removed, use Year(4).

Create User and Alter User commands massively reworked. Grant is also affected, as is mysql.user structureand use, and a semantic incompatibility in Set Password is created.
5.7.6 mysql_real_escape_string_quote() replaces mysql_real_escape_string() in C API.
5.7.6 InnoDB Tablespace files created before 5.1 can fail. Look for " tablespace data files" in the linked page.
5.7.8 Previously created Virtual generated columns must be recreated.
5.7.9 New options in C API function mysql_options() .
5.7.9 Inner InnoDB file changes require clean InnoDB shutodwn in upgrade.