Backward-incompatible changes in MySQL 5.0 and up

Tabulated by Artful Software Development

Version-to-version incompatibilities that can break existing SQL code and replication setups.

Versions Url Incompatible changes
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
5.5.3 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.6.32 Load Data... now rejects previously accepted invalid character data.
5.6.34 The variable secure_file_priv can be set to NULL to disable all import and export operations.
5.6.39 The sha256_password authentication plugin now accepts passwords only up to length 256, likewise the PASSWORD() function when old_passwords=2.
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.4 Default sync_binlog setting changed from 0 to 1, improving data protection against disk crashes, but it can seriously worsen write performance.
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. mysql_real_escape_string_quote() replaces mysql_real_escape_string() in C API. 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().
Usernames increase in length from 16 to 32 chars. Run the mysql_upgrade program. Inner InnoDB file changes require clean InnoDB shutodwn in upgrade.
5.7.10 InnoDB 2-phase XA transactions are now always supported. The innodb_support_xa variable is deprecated.
5.7.12 Use the --early-plugin-load option to load a keyring plugin.
5.7.13 The definer and grantor columns in the mysql database increase to 32 chars to match the 5.7.9 change in mysql.user length. Run the mysql_upgrade program.
5.7.16 The ignore_db_dirs variable is deprecated, and removed in 8.0.
5.7.18 PROCEDURE ANALYSE(), the --temp-pool server option, and support for DTrace are deprecated and removed in 8.0.
5.7.19 The innodb_undo_logs option is deprecated, will be removed; use innodb_rollback_segments.
The libmysqld embedded server library is deprecated, will be removed in 8.0.
5.7.20 transaction_isolation and transaction_read_only have been aliases for tx_isolation and tx_read_only; they're now deprecated, will be removed in 8.0. Use transaction_isolation and transaction_read_only instead.

Also deprecated:
  • references to table and column names using leading periods.
  • The query cache, FLUSH QUERY CACHE and RESET QUERY CACHE statements; SQL_CACHE and SQL_NO_CACHE SELECT modifiers; have_query_cache, ndb_cache_check_time, query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type, query_cache_wlock_invalidate system variables; Qcache_free_blocks, Qcache_free_memory, Qcache_hits, Qcache_inserts, Qcache_lowmem_prunes, Qcache_not_cached, Qcache_queries_in_cache, Qcache_total_blocks status variables.
  • Comment stripping by the mysql client program, and its --comments, --skip-comments options
5.7.21 The innodb_undo_tablespaces option is deprecated
5.7.22 DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS sql modes are deprecated, will be removed in 8.0. They will not replicate to 8.0.
5.7.23 Long partition table names required that mysql.innodb_index_stats, mysql.innodb_table columns be lengthened. Run mysql_upgrade.
5.7.24 Deprecated: placing table partitions in shared tablespaces, CREATE TEMPORARY ABKE ... TABLESPACE=innodb_file_per_table || TABLESPACE=innodb_temporary. All will be removed.
8.0 Reserved words, keywords in 8.0.
8.0.0 Grant tables (mysql.user, db, tables_priv, columns_priv, procs_priv, proxies_priv) no longer use the MyISAM storage engine; they now use the InnoDB storage engine. Run mysql_upgrade. All commands that change these tables are transactional; partial completion will not replicate to 8.0. Create|Drop Function... now causes an implicit commit

The utf8mb4 Unicode character set has a new general collation, utf8mb4_0900_ai_ci.

The new data dictionary and the tables in the mysql database now all use InnoDB, so the system variable innodb_read_only now blocks all database writes.

The deprecated mysql_install_db was removed.

Foreign key names now have a maximum length of 64 chars.

ignore_db_dir was removed.

routines, events, parameters tables are no longer in the mysql db; they are in the sysem data dictionary information_schemma.

See here for how to upgrade from 5.7 to 8.0.
performance_schema now instruments server errors and warnings.

The variable validate_password_check_user_name is now enabled by default, so when the plugin is enabled, it by default rejects a password matching the current session username.

Spatial function names Contains(), Disjoint(), Equals(), Intersects(), Overlaps(), Within() have been replaced by their MBR_ versions.

These spatial function names are replaced by their ST_ versions: Area(), AsBinary(), AsText(), AsWKB(), AsWKT(), Buffer(), Centroid(), ConvexHull(), Crosses(), Dimension(), Distance(), EndPoint(), Envelope(), ExteriorRing(), GeomCollFromText(), GeomCollFromWKB(), GeomFromText(), GeomFromWKB(), GeometryCollectionFromText(), GeometryCollectionFromWKB(), GeometryFromText(), GeometryFromWKB(), GeometryN(), GeometryType(), InteriorRingN(), IsClosed(), IsEmpty(), IsSimple(), LineFromText(), LineFromWKB(), LineStringFromText(), LineStringFromWKB(), MLineFromText(), MLineFromWKB(), MPointFromText(), MPointFromWKB(), MPolyFromText(), MPolyFromWKB(), MultiLineStringFromText(), MultiLineStringFromWKB(), MultiPointFromText(), MultiPointFromWKB(), MultiPolygonFromText(), MultiPolygonFromWKB(), NumGeometries(), NumInteriorRings(), NumPoints(), PointFromText(), PointFromWKB(), PointN(), PolyFromText(), PolyFromWKB(), PolygonFromText(), PolygonFromWKB(), SRID(), StartPoint(), Touches(), X(), Y().

Partitioning is now only don by storage engines, and only InnoDB does it, so the options --partition, --skip-partition are removed.

Previously deprecated InnoDB file format variables innodb_file_format, innodb_file_format_check, innodb_file_format_max, innodb_large_prefix were removed.

The mysqlcheck options --fix-db-names, --fix-table-names were removed..

Spatial arguments now elicit errors from CONCAT(), CONCAT_WS().

JSON functions such as JSON_EXTRACT() now acceot a quoted empty string as a key.
8.0.1 The default character set has been changed from latin1 to utf8mb4.

The default collation for utf8mb4 was utf8mb4_general_ci, is now utf8mb4_0900_ai_ci. For the many consequent changes, see the link.

The c/c++ source no longer has the my_bool type. Use bool; deprecated syntax has been removed.

The deprecated libmysqld embedded server and PROCEDURE ANALYSE() have been removed.

MySQL 8.0 requires the Microsoft Visual C++ 2015 Redistributable Package.

Many performance_schema changes, run mysql_upgrade.

global_variables, session_variables, global_status, session_status tables have moved from information_schema to performance_schema.

Instead of the previously deprecated and now removed information_schema tables innodb_locks, innodb_lock_waits tables, use performance_schema.data_locks, data_lock_waits, or sys.innodb_lock_waits, x$innodb_lock_waits Views.

For security reasons, in standalone and win builds the default secure_file_priv setting is now NULL rather than ''.

/bin/bash is required to run mysqld_safe on Solaris.

Server components and plugins can now define privileges dynamically at runtime.

The spatial function ST_Distance() now detects geometry arguments.

These spatial functions now accept just WKB arguments, no longer accept geometry arguments: ST_GeomCollFromWKB(), ST_GeometryCollectionFromWKB(), ST_GeomFromWKB(), ST_GeometryFromWKB(), ST_LineFromWKB(), ST_LinestringFromWKB(), ST_MLineFromWKB(), ST_MultiLinestringFromWKB(), ST_MPointFromWKB(), ST_MultiPointFromWKB(), ST_MPolyFromWKB(), ST_MultiPolygonFromWKB(), ST_PointFromWKB() .

These spatial functions now interpret latitude and longitude in the order specified by the SRS, and accept an optional argument to override the default axis order: ST_GeomCollFromText(), ST_GeometryCollectionFromText(), ST_GeomCollFromTxt(), ST_GeomFromText(), ST_GeometryFromText(), ST_LineFromText(), ST_LinestringFromText(), ST_MLineFromText(), ST_MultiLinestringFromText(), ST_MPointFromText(), ST_MultiPointFromText(), ST_MPolyFromText(), ST_MultiPolygonFromText(), ST_PointFromText(), ST_PolyFromText(), ST_PolygonFromText(), ST_GeomCollFromWKB(), ST_GeometryCollectionFromWKB(), ST_GeomFromWKB(), ST_GeometryFromWKB(), ST_LineFromWKB(), ST_LinestringFromWKB(), ST_MLineFromWKB(), ST_MultiLinestringFromWKB(), ST_MPointFromWKB(), ST_MultiPointFromWKB(), ST_MPolyFromWKB(), ST_MultiPolygonFromWKB(), ST_PointFromWKB(), ST_PolyFromWKB(), ST_PolygonFromWKB(), ST_AsBinary(), ST_AsWKB(), ST_AsText(), ST_AsWKT(). .

INSERT ... SELECT ... ON DUPLICATE KEY UPDATE where UPDATE refers to SELECT columns could produce incorrect results if the SELECT was a UNION, so now they elicit an error. Write the UNION as a derived single table.

Because of a bug fix, a View can no longer be replaced just by reference to information_schema.Views. Use Show Create View.
8.0.2 To log to the system log, you now need to load the log_sink_syseventlog log component and list it in log_error_services.

partition management is much changed, see the link.

Some plugins ,eg Group Replication, X Plugin, now use the mysql.session add d in 8.0, so if the version being upgraded from did not include mysql.session account, you will need to run mysql_upgrade.

The spatial functions ST_Contains(), ST_Crosses(), ST_Disjoint(), ST_Equals(), ST_Intersects(), ST_Overlaps(), ST_Touches(), ST_Within(), MBRContains(), MBRCoveredBy(), MBRCovers(), MBRDisjoint(), MBREquals(), MBRIntersects(), MBROverlaps(), MBRTouches(), MBRWithin() now detect and handle geometry arguments.

MySQL formerly took the first, now takes the last members with the same key name.
8.0.4 Regex now uses International Components for Unicode, multibyte safe with new functions.

The default authentication plugin is now caching_sha2_password.

Some ST_* functions now return errors for out-of-range params.

MySWL now uses binlog as a base name for the binary log.

GeomCollection[()] is a synonym for GeometryCollection[()], and preferred.

8.0.12 Some changes to ST_* functions, see link.
8.0.13 Changes to log_syslog* variable names.

GROUP BY no longer supports ASC | DESC.

8.0.14 Support for secondary passwords, upgrading from 8.0.13 requires running mysql_upgrade.
8.0.16 InnoDB restores automatic generation of foreign key contraint names discontinued in 8.0.
8.0.20 Fixed ST_Contains(), which had returned nothing without > 0. Recreate spatial indexes.
8.0.21 Access to INFORMATION_SCHEMA.FILES requires PROCESS privilege.