Backward-incompatible changes in MySQL 5.0 through 5.7

Tabulated by Artful Software Development

Versions Url Item
5.0 http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html New reserved words in MySQL 5.0
5.0.1 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-1.html   C API change: mysql_shutdown() now requires a second argument.
5.0.2 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-2.html  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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-3.html  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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-6.html  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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-8.html  Format of result of conversion by DATETIME+0 used to be YYYYMMDDHHMMSS format, now YYYYMMDDHHMMSS.000000 
5.0.10 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-10.html  Trigger names unique in database namespace rather than table namespace
5.0.12 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html  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 http://dev.mysql.com/doc/refman/5.0/en/join.html 
5.0.13 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-13.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-15.html
 
InnoDB lock wait timeout caused roll back of entire current transaction, now only the most recent SQL statement.
5.0.14 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-15.html  Pad value for Binary columns changed from space to 0x00
5.0.15 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-15.html  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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html  InnoDB now includes trailing spaces when comparing [Var]Binary column values.
5.0.19
5.1.7
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-7.html 
New mysql_upgrade progam to check all tables and if necessary update them for backward incompatibility, and new Check Table For Upgrade option
5.0.23
5.1.12
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-23.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html
Grant | Revoke ... On * requires a default database, throws an error if there is not
5.0.24a
5.1.12
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-24a.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html 
5.0.24 introduced an application binary interface incompatibility, which 5.0.24a reverts. 
5.0.27 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-27.html  5.0.26 introduced an application binary interface incompatibility, which 5.0.27 and Enterprise 5.0.28 revert.
5.0.30sp1 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-30sp1.html  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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-33.html  System variable prepared_stmt_count changed to global status variable Prepared_stmt_count (see SHOW GLOBAL STATUS).
5.0.36
5.1.15
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-36.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-15.html 
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.
5.0.37
5.1.15
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-37.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-15.html
 
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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-45.html  INSERT DELAYED statements not supported for, and no longer corrupts MERGE tables,
5.0.45
5.1.18
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-45.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-18.html
 
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)
5.0.45
5.1.20
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-45.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-20.html 
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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-51.html  Select statements containing unclosed /* now elicit syntax errors, may affect query parsing in stored procedures
5.0.52 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-52.html  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 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-67.html  Changes in handling of incompatible .frm files
5.0.67
5.1.24
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-67.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-24.html
 
Frac_Second deprecated; with anything other than Timestampadd() or Timestampdiff() produces syntax error.
5.0.67
5.1.26
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-67.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-26.html
Federated engine disabled by default in shipped example .cnf files
5.0.75
5.1.28
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-75.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-28.html
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
5.0.77
5.1.29
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-77.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-29.html
Lose unused database directory arc folders for Views
5.0.87 http://dev.mysql.com/doc/refman/5.0/en/news-5-0-87.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-40.html 
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-0.html 
Supplied binary-configure script no longer starts and configures MySQL with --help command-line option.
5.1 http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html New reserved words in MySQL 5.1
5.1.3 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-3.html  New plugin system
    Renamed table_cache system variable to table_open_cache 
5.1.7 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-7.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-8.html  cluster_replication database renamed to cluster 
    Changes in semantics for Alter Table for partitions
5.1.11 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-11.html  Changes in Event scheduler and information_schema.events
5.1.12 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-14.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-15.html  Changes to effects of enabling read_only system variable
    Archive torage engine no longer creates .ARM metadata file for each table. 
5.1.16 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-16.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html  Merge tables now reject Insert Delayed
5.1.18 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-18.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-19.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-20.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-21.html  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, mysql.events and information_schema.events to manage charsets & collations in stored programs
5.1.24 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-24.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-32.html 
    Change in plugin location
    Rebuild indexes using utf_general_ci or ucs2_general_ci charsets
5.1.25 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-25.html  Detect metadata changes to tables or views referred to by prepared statements and automatically reprepare
5.1.27 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-27.html  Rename or drop partitioned tables using mixed case names before upgrading to 5.1.27 on Mac OS X
5.1.29 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-29.html  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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-36.html  Changes in handling of plugins
5.5 http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html New reserved words in MySQL 5.5
5.5.0 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-0.html  New plugin interface
    Changes in error message language and charset
    Changes in access privileges for several statements
5.5.1 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-1.html  Changes in naming of semisynchronous plugins
5.5.2 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-2.html  Changes in plugin.h
5.5.3 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html

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 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-6.html Sha2() & other encryption return values now respect connection character set & collation settings
5.5.7 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html
http://dev.mysql.com/doc/refman/5.6/en/news-5-6-1.html
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 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-8.html performance_schema table names now lower case, some columns renamed
Flush Table With Read Lock redesigned
5.5.9 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-9.html
http://dev.mysql.com/doc/refman/5.6/en/news-5-6-2.html
auto_increment values can no longer reach maximum BigInt Unsigned value
5.5.10 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-10.html New C API library version
5.5.14 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-14.html
http://dev.mysql.com/doc/refman/5.6/en/news-5-6-3.html
Class member change in audit plugin
5.5.18 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-18.html Several statements newly marked unsafe for statement-based replication
5.6 http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html New reserved words in MySQL 5.6 (warning: there are very many!)
5.6.1 http://dev.mysql.com/doc/refman/5.6/en/news-5-6-1.html Several server variables removed
Replication: database-level statements take precedence over table wildcards
Lock Tables blocks Create Table
5.6.2 http://dev.mysql.com/doc/refman/5.6/en/news-5-6-2.html Flush Master | Slave removed
5.6.3 http://dev.mysql.com/doc/refman/5.6/en/news-5-6-3.html

Changes in timeout handling

5.6.4 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html Timestamp(n) no longer sets display width, now specifies 0<=n<=6 microsecond digits. InnoDB now supports FullText.
5.6.5 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html Any number of Timestamp columns may default to Now() on insert and/or on update.
5.6.6 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-6.html innodb_file_per_table enabled (previously disabled) , innodb_checksum_algorithm now CRC32 (previously InnoDB) , binlog_checksum now CRC32 (previously NONE)
5.6.7 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-7.html 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 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-9.html Several GTID-related variable name changes.
Last_Insert_Id() now returns a Bigint Unsigned value, not a Bigint (signed) value.
5.6.11 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-11.html backward-incompatible changes in partiton Key syntax and handling
5.6.12 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-12.html 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 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-13.html Backward-incompativle performance schema change.
5.6.14 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-14.html 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.
5.6.15 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-15.html
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
Several more performance_schema renamings.
5.6.17 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html AES_DECRYPT() and AES_ENCRYPT() take a vector argument to control block encryption mode.
5.7.1 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-1.html 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 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html mysql.user.plugin now defined as Not Null, run mysql_upgrade.
log_error_verbosity is preferred to log_warnings.
5.7.4 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html mysql.user table has new columns password_last_changed and password_lifetime, run mysql_upgrade.
5.7.5 http://dev.mysql.com/doc/refman/5.7/en/mysql-install-db.html mysql_install_db isnow an executable written in C++ rather than a Perl script.
5.7.5 http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#option_mysqld_innodb InnoDB engine can no longer be disabled.
5.7.5 http://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html Support for pre-4.1 passwords removed.
5.7.5 http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-full Many sql_mode changes.
5.7.5 http://dev.mysql.com/doc/refman/5.7/en/year.html Year(2) removed, use Year(4).
5.7.6

http://dev.mysql.com/doc/refman/5.7/en/create-user.html

http://dev.mysql.com/doc/refman/5.7/en/alter-user.html

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 http://dev.mysql.com/doc/refman/5.7/en/mysql-real-escape-string-quote.html mysql_real_escape_string_quote() replaces mysql_real_escape_string() in C API.
5.7.6 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html InnoDB Tablespace files created before 5.1 can fail. Look for " tablespace data files" in the linked page.
5.7.8 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html Previously created Virtual generated columns must be recreated.
5.7.9 http://dev.mysql.com/doc/refman/5.7/en/mysql-options.html New options in C API function mysql_options() .
5.7.9 http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html Inner InnoDB file changes require clean InnoDB shutodwn in upgrade.