The complete list of new features in MySQL 8.0

There are over 250 new features in MySQL 8.0. The MySQL Manual is very good, but verbose. This is a list of new features in short bullet form. We have tried very hard to make sure each feature is only mentioned once. Note the similar list for MySQL 5.7.

Please download MySQL 8.0 from dev.mysql.com or from the MySQL  YumAPT, or SUSE repositories.

SQL DML

  1. Non-recursive CTEs [1]
  2. Recursive CTEs [1]
  3. Window functions [1]
  4. ORDER BY and DISTINCT with ROLLUP [1]
  5. LATERAL derived tables [1]
  6. Outer table references in derived tables [1]

SQL DDL

  1. Instant ADD COLUMN [1]
  2. Instant RENAME COLUMN [1]
  3. Instant RENAME TABLESPACE [1]
  4. RESTART statement [1]
  5. SET PERSIST statement [1]
  6. RENAME TABLES under LOCK TABLES [1]
  7. Option to disallow tables without primary keys [1]
  8. Character set conversion as an inplace operation [1 2]
  9. CREATE TABLESPACE without DATAFILE clause [1]
  10. CREATE RESOURCE GROUP [1 2]
  11. ALTER RESOURCE GROUP [1 2]
  12. DROP RESOURCE GROUP [1 2]
  13. Expressions as DEFAULT values [1]
  14. CHECK constraint [1]
  15. CLONE INSTANCE [1]

Indexes

  1. Invisible indexes [1]
  2. Descending indexes [1]
  3. Functional indexes [1]
  4. Index skip scan [1]

Functions

  1. New function REGEXP_INSTR [1]
  2. New function REGEXP_LIKE [1]
  3. New function REGEXP_REPLACE [1]
  4. New function REGEXP_SUBSTR [1]
  5. New function UUID_TO_BIN [1]
  6. New function BIN_TO_UUID [1]
  7. New function IS_UUID [1]
  8. New function GROUPING [1 2]
  9. New function STATEMENT_DIGEST [1]
  10. New function STATEMENT_DIGEST_TEXT [1]
  11. Bit operations allowed on BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB and LONGBLOB [1]

JSON

  1. New function JSON_PRETTY [1]
  2. New function JSON_STORAGE_SIZE [1]
  3. New function JSON_STORAGE_FREE [1]
  4. New function JSON_MERGE_PATCH [1]
  5. New aggregation and window function JSON_ARRAYAGG [1]
  6. New aggregation and window function JSON_OBJECTAGG [1]
  7. New table function JSON_TABLE [1]
  8. Faster sorting of JSON values [1]
  9. Ranges in JSON patch expressions [1 2]
  10. In-place updates of JSON values [1 2]
  11. JSON Schema validation [1]
  12. JSON array indexes [1 2]
  13. New operator MEMBER_OF [1]
  14. New function JSON_OVERLAPS [1]

GIS

  1. Spatial reference systems (SRSs) [1 2 3 4]
  2. CREATE SPATIAL REFERENCE SYSTEM statement [1 2]
  3. DROP SPATIAL REFERENCE SYSTEM statement [1]
  4. SRID type modifier [1 2]
  5. Geographic R-trees [1]
  6. New setter function ST_SRID(geometry, new_srid) [1]
  7. New setter function ST_X(geometry, new_x) [1]
  8. New setter function ST_Y(geometry, new_y) [1]
  9. New function ST_SwapXY [1]
  10. New function ST_Latitude [1]
  11. New function ST_Longitude [1]
  12. New function ST_Transform [1]
  13. Geography support in ST_Distance, 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, ST_IsSimple, ST_IsValid, ST_Length, ST_Validate, ST_Area [1 2 3 4 5 6 7 8]
  14. ST_Distance_Sphere for geographic geometries [1]
  15. Ellipsoidal ST_Distance between any two geometry types [1]
  16. GEOMCOLLECTION as synonym to GEOMETRYCOLLECTION [1 2 3]
  17. Optional SPATIAL keyword in R-tree index clauses [1]
  18. Ability to specify length unit in ST_Distance() [1]
  19. Ability to specify length unit in ST_Length() [1]

Character sets and collations

  1. UTF-8 (utf8mb4) as default character set [1 2 3 4 5 6 7 8 9 10 11]
  2. General Unicode 9.0 collations covering German (dictionary order), Austrian German (dictionary order), English, French (including accent insensitive Canadian French), Irish, Indonesian, Italian, Luxembourgian, Malay, Dutch (including Flemish), Portuguese (including Brazilian Portuguese), Swahili, and Zulu [1 2]
  3. Unicode 9.0 collation for Czech
  4. Unicode 9.0 collation for Danish (also valid for Norwegian)
  5. Unicode 9.0 collation for German (phonebook order)
  6. Unicode 9.0 collation for Esperanto
  7. Unicode 9.0 collation for Spanish
  8. Unicode 9.0 collation for Spanish (traditional)
  9. Unicode 9.0 collation for Estonian
  10. Unicode 9.0 collation for Croatian (also valid for Serbian with latin characters, and Bosnian)
  11. Unicode 9.0 collation for Hungarian
  12. Unicode 9.0 collation for Icelandic
  13. Unicode 9.0 collation for Lithuanian
  14. Unicode 9.0 collation for Latvian
  15. Unicode 9.0 collation for Polish
  16. Unicode 9.0 collation for Romanian
  17. Unicode 9.0 collation for Slovak
  18. Unicode 9.0 collation for Slovenian
  19. Unicode 9.0 collation for Swedish
  20. Unicode 9.0 collation for Turkish
  21. Unicode 9.0 collation for Vietnamese
  22. Unicode 9.0 collation for Japanese (including kana sensitive collation)
  23. Unicode 9.0 collation for Chinese
  24. Unicode 9.0 collation for Russian (also valid for Bulgarian)
  25. Unicode support in RLIKE and REGEXP [1]
  26. Unicode 9.0 code point order collation (utf8mb4_0900_bin) [1]

Information Schema

  1. Information Schema implemented as views over data dictionary tables [1]
  2. VIEW_TABLE_USAGE [1]
  3. VIEW_ROUTINE_USAGE [1]
  4. KEYWORDS [1]
  5. COLUMN_STATISTICS [1]
  6. ST_GEOMETRY_COLUMNS [1]
  7. ST_SPATIAL_REFERENCE_SYSTEMS [1]
  8. ST_UNITS_OF_MEASURE [1]
  9. RESOURCE_GROUPS  [1]
  10. CHECK_CONSTRAINTS [1, 2]

Performance Schema

  1. Performance Schema Indexes [1]
  2. Instrument server errors  [1]
  3. Statements latency histograms [1]
  4. Instrument data locks [1]
  5. Pluggable performance schema tables [1]
  6. Added QUERY_SAMPLE_TEXT  [1]
  7. Added Thread Pool Tables  [1] (Enterprise)

SHOW

  1. SHOW now lists hidden columns [1]
  2. SHOW now lists index information [1]

Optimizer

  1. Histograms [1 2 3]
  2. Adaptive scan buffer size [1]
  3. IO costs separation between memory and disk [1]
  4. Default values in cost tables [1]
  5. Sampling interface in storage engine API [1 2 3 4]
  6. NOWAIT and SKIP LOCKED [1 2]
  7. Avoid unnecesary index dives with FORCE INDEX [1 2 3]
  8. Optimizer switch to use invisible indexes [1 2 3]
  9. Increased default optimizer trace buffer size [1]
  10. New hint MERGE [1]
  11. New hint INDEX_MERGE [1]
  12. New hint NO_INDEX_MERGE [1]
  13. New hint JOIN_FIXED_ORDER [1]
  14. New hint JOIN_ORDER [1]
  15. New hint JOIN_PREFIX [1]
  16. New hint JOIN_SUFFIX [1]
  17. New hint SET_VAR [1]
  18. Consider covering prefix indexes for LIKE [1]
  19. Transformed statement in EXPLAIN of INSERT/UPDATE/REPLACE/DELETE [1]
  20. EXPLAIN ANALYZE [1]
  21. HASH JOIN [1]

InnoDB

  1. Highly scalable latch free redo log implementation [1 2].
  2. Redesign of LOB infrastructure for better performance [1 2 3]
  3. State of the art lock scheduler using Contention Aware Transaction Scheduling (CATS) (Contribution from University of Michigan) [1]
  4. Infrastructure to do non locking parallel reads (currently used by CHECK TABLE) [1]
  5. Instant add column and virtual column [1]
  6. Report pages cached in the buffer pool by indexes via the information schema [1]
  7. Persistent auto increment [1]
  8. Manage UNDO tablespaces using SQL syntax [1]
  9. New in-memory temptable storage engine for use by optimiser [1]
  10. Support for BLOBs in new temptable engine [1 2]
  11. Dedicated server mode, automatically configures the buffer pool and redo log size [1 2]
  12. Remove the buffer pool mutex (Percona contribution) [1]
  13. Improved purge [1]
  14. Dynamically enable/disable the deadlock detector [1]
  15. IO layer is now more scalable and efficient [1]
  16. Extended locking semantics with skip and no-waits [1 2]
  17. Use the new error logging infra structure [1]
  18. System data dictionary is now stored in InnoDB [1]
  19. New configuration to generate smaller core files [1]
  20. Deprecate Shared tablespaces in partitioned table [1]
  21. Reclaim temporary tablespace disk space online [1]
  22. New option to control write IOPs when idle [1]

Data Dictionary and Upgrade

  1. Transactional Data Dictionary [1]
  2. Store all meta data in InnoDB, no FRMs, TRG etc [1,2]
  3. Store redundant copy of meta data in SDI [1]
  4. Tablespace version support for better upgrade/downgrade experience [1]
  5. Self describing tablespaces with Serialized Dictionary Information (SDI) [1]
  6. Tools to manage the SDI [ 1]
  7. Atomic and crash safe DDL [1]
  8. Automatic upgrade of dictionary tables, and enhanced checks [1]
  9. Automatic upgrade, no need for mysql_upgrade script [1]

Network

  1. Support multiple addresses for the –bind-address command option [1]
  2. Add Admin Port [1 2 3 4 5]
  3. Remove mutex bottlenecks for connect/disconnect performance [1]
  4. Support host names longer than 60 characters [1]

Error logging

  1. Improved error logging in 8.0 [1]
  2. Defaults change: log_error_verbosity=2 [1]
  3. Added severity, error code, subsystem to error messages [1]
  4. Filtering the error log [1]
  5. Error logging in JSON format [1]
  6. Force-print specific non-error messages to error log [1]
  7. Suppress error logs of type warning or note [1]
  8. New “syseventlog” settings [1]
  9. Added –log-slow-extra, for richer slow logging [1]

Replication

  1. Multi-source Replication Per Channel Filters [1]
  2. Atomic DDL Recovery With The Binary Log [1]
  3. Write-set Based Transaction Dependency Tracking [2 ]
  4. Reduced Contention Between Receiver and Applier Threads [1]
  5. GTID Support for Temporary Tables Inside Transactions [1]
  6. Partial JSON Update Replication [1]
  7. Extended table metadata in the binary log [1 2]
  8. RESET MASTER TO ‘x’ [1]
  9. Settable GTID_PURGED When GTID_EXECUTED is Not Empty [1]
  10. Sub-second Binary Logs Expiration Settings [1]
  11. Non-Blocking Replication Monitoring even when Disk is Full [1]
  12. Transaction Byte Length Metadata in Binary log [1]
  13. Server Versions for each Transaction in the Binary Log  [1, 2, 3]
  14. Support for START SLAVE UNTIL for Multi-Threaded Applier [1]
  15. Delayed Replication in Microseconds [1]
  16. binlog-row-event-max-size system variable [1]
  17. PFS: Applier Lag and Queues Monitoring [1 2 3]
  18. PFS: Read Consistent Log Positions for Backups [1]
  19. PFS: Row-based Replication Applier Thread Progress [1]
  20. PFS: Counters for Replication Applier Retries [1]
  21. Rotating binary log master key online [1]
  22. Partition metadata in the Binary Log [1]
  23. Encrypt binary log caches [1]
  24. Protocol compression support for mysqlbinlog [1]
  25. Replication with privilege checks [1 2]

Group Replication

  1. Transaction Savepoint Support [1]
  2. Disallow Writes to Isolated Members in Group Replication [1 2 3]
  3. Group-wide Certification and Applier Stats Monitoring [1 2]
  4. Options to Fine-tune the Flow-Control [1 2 3 4 5 6 7 8]
  5. Support for Hostnames in the Whitelist [1]
  6. Shutdown Server When Server Drops Out of the Group [1]
  7. Online and User-Triggered Primary Switchover/Election [1 2]
  8. Online and User-Triggered Single-to-Multi Primary Switchover [1 2]
  9. Configurable Messaging Pipelining [1 2]
  10. Relaxed Member Eviction [1]
  11. Consistent Reads [1]
  12. Consistent Reads on Primary Fail-over [1]
  13. IPv6 Support [1 2]
  14. Tracing for Message Passing [1]
  15. Configure primary failover candidates priority [1 2]
  16. PFS: Instrumented Threads [1 2]
  17. PFS: Instrumented Mutexes and Condvars [1 2]
  18. PFS: Instrumented Memory Used for the Message Cache [1 2]
  19. Better support for large transactions in GR [1 1]
  20. Ability to configure the GR communication protocol [1 1]
  21. Changed EXIT STATE ACTION default to READ_ONLY [1]
  22. Auto-rejoin of Group Members [1 2]
  23. New fencing mode for Group Replication: OFFLINE_MODE [1 2]
  24. Group Replication distributed recovery with automated instance cloning [1]
  25. Enhanced cross-version inter-operability for Group Replication  [1]
  26. TLS 1.3 support for Group Communication System (GCS/XCom) [1 2]
  27. Group Delivery Message Service [1]

Security – Authentication

  1. Caching sha2 authentication plugin  [1]
  2. Introduce delays in authentication based on failed logins [1]
  3. Password rotation policy enforcement [1]
  4. Old password required for SET PASSWORD for some users [1]
  5. Support 2 active passwords per user account  [1]
  6. Identification by RANDOM PASSWORD [1 2]
  7. Option to display passwords as hexadecimal strings [1]
  8. Extra authentication to allow SET PERSIST for security sensitive variables  [1]
  9. SASL authentication for LDAP on windows   [1]  (Enterprise)
  10. LDAP authentication plugin (client and server)  [1] (Enterprise)
  11. Add support for users with multiple LDAP groups  [1] (Enterprise)

Security – Authorization

  1. SQL Roles [1]
  2. Break the SUPER privilege into dynamic privileges [1]
  3. Granting default roles when new users are created [1 2 3]
  4. Additional safety to –skip-grant-tables  [1]
  5. Secure session variable setting (MYSQL_SESSION_ADMIN privilege) [1]
  6. Checking authorization for rolling back XA-transactions [1]
  7. Added Partial Revokes [1]
  8. Added the SYSTEM_USER Dynamic Privilege [1]

Security – Auditing

  1. Audit log: abort queries on rule based conditions   [1] (Enterprise)
  2. Audit log: JSON format, compression and encryption  [1]  (Enterprise)
  3. Audit log: SQL function to inject data into the Audit log  [1] (Enterprise)
  4. Audit log: Multiple encryption passwords [1] (Enterprise)

Security – Encrypt Data at Rest

  1. Control Table encryption [1]
  2. Redo log encryption [1]
  3. Undo log encryption [1]
  4. General Tablespace encryption [1]
  5. System Tablespace encryption [1]
  6. Binary Log encryption [1]

Security – Keyring

  1. Server as a keyring backend migration tool  [1]
  2. Keyring plugin for AWS KMS [1] (Enterprise)
  3. Keyring plugin for Hashicorp Vault [1] (Enterprise)

Security – Data Masking

  1. Data masking functions [1] (Enterprise)

Security – SSL & Other

  1. Atomic ACL statements  [1]
  2. OpenSSL Only [1]
  3. Support for FIPs enabled OpenSSL library  [1 2]
  4. Ensure that foreign key error does not reveal information about parent table [1]
  5. Added support for TLS 1.3 [1]
  6. Allow switching SSL options for a running server [1]
  7. The –ssl-mode client side option to streamline SSL checking [1]

Router

  1. Persist last known metadata-server addresses [1]
  2. Reset max_connect_errors on successful connections [1]
  3. Build Router as part of the MySQL Server source-tree [1]
  4. Added mysqlrouter_plugin_info tool [1]
  5. Reduced metadata-cache TTL from 300s to 500ms [1]
  6. Added routing strategies [1]
  7. Added bootstrap option for –report-host [1]
  8. Added bootstrap option for –account-host [1]
  9. Disconnect clients to server-nodes that changed from PRIMARY to SECONDARY [1]
  10. Log-rotation via SIGHUP [1]
  11. HTTP server plugin [1]
  12. Flexible support for single/multi-PRIMARY Group Replication [1]
  13. New mysqlrouter_keyring utility [1]
  14. Sub-second timestamp resolution in router log [1]

Shell

  1. MySQL 8.0 support for InnoDB clusters [1]
  2. Remote MySQL server configuration and re-configuration for InnoDB clusters [1]
  3. Extended cluster status display, including replication lag times [1]
  4. Manual primary switch-over and topology re-configuration in InnoDB clusters [1]
  5. Advanced cluster customizations for more use-cases and environments
  6. MySQL server upgrade checker [12]
  7. Import JSON and JSON serialized BSON data  [12]
  8. Updated X DevAPI support
  9. Secure password management [12]
  10. Display column metadata for query results  [1]
  11. Direct command line execution of shell APIs [1]
  12. Improved built-in help [1]
  13. Screen paging [1]
  14. Auto-completion [1]
  15. Persisted command history by Shell mode. [1]
  16. Custom prompts [1]
  17. The \sql shell command also works in JavaScript or Python mode [1]
  18. Support for server monitoring through user defined reports and the \show and \watch shell commands [1]
  19. Parallel Data Import Utility [1]
  20. Support for Shell Extensions [1]
  21. Support for Shell Plugins [1 2]
  22. Support for different result formats: Table, tabbed, vertical, json/raw, json/pretty [1]

Misc

  1. Added mysqld_safe-functionality to server [1 2 3]
  2. Defaults change: explicit_defaults_for_timestamp= ON [1]
  3. Defaults change: max_error_count=1024 [1]
  4. Renamed tx_read_only variable to transaction_read_only [1]
  5. Renamed tx_isolation variable to transaction_isolation [1]
  6. Defaults change: max_allowed_packet=67108864 [1]
  7. Defaults change: event_scheduler=ON [1]
  8. Defaults change: back_log=-1 (auto-sized) [1]
  9. Defaults change: table_open_cache=4000 [1 2]
  10. New Backup Lock [1]
  11. Server version stored in InnoDB tablespaces [1]
  12. Enable MDL Locking for Recovered and Detached Prepared XA Transactions [1]
  13. Support meta data locking for Foreign Keys [1]
  14. The service registry and the component infrastructure [1]
  15. CLI interface to read the replication stream  [1]
  16. UDF registration service to allow components to auto-register UDFs  [1]
  17. MySQL server strings component service  [1]
  18. Make result set metadata transfer optional  [1]
  19. Status variables service for components [1]
  20. Performance schema instrumentation via a component service [1]
  21. System variables service for components  [1]
  22. Password validation plugin implemented as a component  [1]
  23. Component service to deliver signals to the host application
  24. Allow plugins to use prepared statements [1]
  25. INSERT/UPDATE/DELETE in query rewrite plugin [1]
  26. Dynamic allocation of sort buffer [1]
  27. Variable length sort keys for NO PAD collations [1]
  28. Faster SELECT COUNT (*) without grouping [1]
  29. Source code improvements [1]
  30. Small tarball download option [1]
  31. CAST to FLOAT and DOUBLE [1]
  32. OS user as a connection attribute [1]
  33. gtid_purged as a comment in mysqldump files [1]
  34. Early plugin load flag [1]
  35. Current thread component service [1]
  36. Mutex lock order tool [1]
  37. Connection compression control [1]
  38. Dynamic linking of Protobuf [1]
  39. Increase max value for max_prepared_stmt_count [1]
  40. Move sys Schema to the mysql server tree [1]
  41. Compile time check of error message arguments [1]

Thank you for using MySQL !

About Geir Hoydalsvik

Geir Høydalsvik has been working with MySQL Database team since 2008. He is currently employed by Oracle, based in Norway. He is Senior Software Development Director and responsible for the development and maintenance of MySQL Database. He has a background in the database industry, working for the database startup company Clustra Inc. on the Clustra database and for Sun Microsystems on Java DB. He has a Master degree in Computer Science and a PhD in Software Engineering from the Norwegian University of Science and Technology.

One thought on “The complete list of new features in MySQL 8.0

Leave a Reply