Differences between MariaDB 10.3 & MySQL 8.0

Differences between MariaDB and MySQL — MariaDB 10.3 vs. MySQL 8.0 Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter db tech showcase, Tokyo, Japan 19 September 2018

whoami • Chief Evangelist, Percona Inc • Founding team of MariaDB Server (2009-2016) [Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation] • Formerly MySQL AB (exit: Sun Microsystems) • Past lives include The Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014

License • Creative Commons BY-NC-SA 4.0 • https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode

A good base blog post resource • High level, answer to a whitepaper • https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb- reality-check/ • Versions compared: • MySQL 8.0.12 (released: 27 July 2018) • MariaDB Server 10.3.9 (released: 15 August 2018)

http://features.today/ • signup at http://features.today/ for updated information of what is available in each MySQL or MariaDB Server or Percona Server for MySQL release

Define: compatibility (OED) • A state in which two things are able to exist or occur together without problems or conflict.

Why this matters • MariaDB Server is the “default” MySQL in many Linux distributions • not Ubuntu! (shipping MySQL 5.7) • MariaDB isn’t MySQL - there are many cloud providers that have an option for MariaDB • Amazon Web Services (AWS) RDS MariaDB, Microsoft Azure, Rackspace Cloud • There are incompatibilities (e.g. with connectors) • https://github.com/brianmario/mysql2/issues/878 • MariaDB Connector/C for MySQL and MariaDB Server. It is libmysqlclient API compatible. LGPL. OpenSSL/GnuTLS/schannel (no more yaSSL/wolfSSL) • header change in MariaDB 10.2.6 and mysqlclient python binding - https:// lists.launchpad.net/maria-developers/msg10726.html

Commitments • Verbal commitments: “MySQL 5.6, should be comparable to MariaDB Server 10.1. And for 10.2 it should be compatible with MySQL 5.7” — Michael “Monty” Widenius, CTO of MariaDB Corporation and MariaDB Foundation, 7 October 2016, MariaDB Developer’s Meeting, Amsterdam • http://mariadb.org/about/ • “It is an enhanced, drop-in replacement for MySQL.”

Licensing • MariaDB Server: GPLv2 only • MariaDB MaxScale: Business Source License • MariaDB ColumnStore: GPLv2 only • Backup & Restore, ColumnStore Kafka data adapter, MariaDB MaxScale CDC Data Adapter: Business Source License • MySQL has Community (GPLv2) and Enterprise releases

Support • What is the support ecosystem and landscape like? • Training? • MySQL Certification (MariaDB Corporation started certification at M| 17, continued at M|18)

Community Contributions • Oracle Contributor Agreement (OCA) • MariaDB Contributor Agreement (MCA) • BSD New • Who maintains the code? What is the state of community contributed code?

Governance • MariaDB Corporation • MariaDB Foundation • Is there vendor lock-in in open source? • How many users are there, really?

Releases MariaDB MySQL 5.1: 1 Feb 2010 5.1: 14 Nov 2008 5.2: 10 Nov 2010 5.3: 29 Feb 2012 5.5: 11 Apr 2012 5.5: 3 Dec 2010 5.6: 5 Feb 2013 10.0: 31 Mar 2014 10.1: 17 Oct 2015 5.7: 21 Oct 2015 10.2: 23 May 2017 8.0: 19 Apr 2018 10.3: 25 May 2018

What’s documented? • https://mariadb.com/kb/en/the-mariadb-library/mariadb-vs-mysql- compatibility/ • (closed!?)Tracker bug: https://jira.mariadb.org/browse/MDEV-10392 • https://mariadb.com/kb/en/library/system-variable-differences- between-mariadb-and-mysql/ • https://mariadb.com/kb/en/library/incompatibilities-and-feature- differences-between-mariadb-102-and-mysql-57/

GTID variances between MariaDB & MySQL • https://mariadb.com/kb/en/library/gtid/#the-domain-id

MariaDB Replication • Default binlog format is now MIXED (ROW in MySQL) • Default replicate_annotate_row_events is ON • Binlog event compression - log_bin_compress • Time delayed replication (present in MySQL 5.6; arrived in MariaDB 10.2) • read_binlog_speed_limit - restricting the speed at which the slave reads the binlog from the master • DML only Flashback - rollback instances/databases/tables to an older snapshot (via Alibaba!)

5.1/5.2 • mysqld reads [mariadb] part of my.cnf for MariaDB Server only options • Binary-only storage engines won’t work without recompilation due do different THD structure (e.g. commercial engines like ScaleDB) • Extended slow query log statistics (microslow patch from Percona) • More memory utilised: Aria used to handle internal temporary tables, needs configuration • MariaDB only: table elimination

5.3 • Error numbers for MariaDB are at 1900+; MySQL has to deal: https:// bugs.mysql.com/bug.php?id=72062 • Microseconds arrived (MariaDB 10.1 follows the MySQL 5.6 format) • SHOW PROCESSLIST with progress reporting • New features: dynamic columns, virtual columns (5.7), HandlerSocket plugin, Cassandra storage engine (now deprecated) • Huge changes in optimiser + replication • https://mariadb.com/kb/en/library/optimizer-feature-comparison- matrix/

JSON • 5.7 has a binary data type, MariaDB chooses not to implement it this way, choosing to not “violate the SQL standard” - https:// jira.mariadb.org/browse/MDEV-9144 • As fast, awaiting benchmarks - https://jira.mariadb.org/browse/ MDEV-13777

X Protocol • MariaDB Server has no support for the MySQL X Protocol • This means you cannot use mysqlsh to access MariaDB Server

Encryption • MySQL 5.7 and MariaDB Server 10.1+ implement encryption differently (one is fully tablespace encryption, the other is based on the Google patch for tablespace encryption in addition to having table encryption via Eperi) • MySQL 8 encrypts redo/undo logs via configuration options; temporary tablespace or binary log encryption does not exist (MariaDB supports binary log encryption, and temporary table encryption) • MySQL requires innodb_file_per_table • MySQL implementation works fully with Percona XtraDB Cluster • MariaDB Galera Cluster gcache is unencrypted - https://jira.mariadb.org/browse/MDEV-9639 • mysqlbinlog cannot read encrypted binary logs - https://jira.mariadb.org/browse/MDEV-8813 • workaround is use —read-from-remote-server as the server has access to the keys via the encryption plugin API. This adds load to the server! • Backups: MariaDB Backup for encrypted backups • Key management: Percona has Vault, MariaDB has an AWS Key Management Plugin, MySQL has several but they are part of the Enterprise release

PERFORMANCE_SCHEMA • No sys schema by default (you can install the MySQL 5.6 version yourself) - https://jira.mariadb.org/browse/MDEV-9077 • No new PERFORMANCE_SCHEMA instrumentation from 5.7/8.0 - https://jira.mariadb.org/browse/MDEV-6114 • e.g. 52 rows in set (0.00 sec) vs. 87 rows in set (0.00 sec)

Security • MySQL: caching_sha256_password • MariaDB: ed25519 password plugin • Needless to say the above are incompatible with each other • validate_password is on by default in MySQL 5.7+

Other bits • MariaDB: SHOW EXPLAIN FOR <thread_id> • MySQL: EXPLAIN FOR CONNECTION <thread_id> • https://jira.mariadb.org/browse/MDEV-10000 • MySQL has SUPER READONLY, missing in MariaDB - https:// jira.mariadb.org/browse/MDEV-9458 • Replication crash-safety for non-GTID slaves - https://jira.mariadb.org/ browse/MDEV-8946 • Minimal/NOBLOB Binlog Row Image replication fails when tables from master have different PK in slave - https://jira.mariadb.org/browse/ MDEV-8398

mysql.user table changes • MariaDB Server and MySQL differ here (not just by addition of roles) - mysql.user.password is just mysql.user.authentication_string • Password expiry? https://jira.mariadb.org/browse/MDEV-7597 • Password last changed? Lifetime? • ACCOUNT LOCK/UNLOCK • VALIDATE_PASSWORD_STRENGTH() SQL function doesn’t work in MariaDB Server

More 5.7 • Optimiser trace (5.6): https://jira.mariadb.org/browse/MDEV-6111 • Optimizer hints - https://jira.mariadb.org/browse/MDEV-9078 • RENAME INDEX - https://jira.mariadb.org/browse/MDEV-7318 • Query rewriting? - https://jira.mariadb.org/browse/MDEV-5561 • GIS: GeoJSON functions? Geohash functions? • SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)’),2); [this works in MariaDB Server 10.3] • SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15); [still missing in MariaDB Server 10.3]

Niggling usability bits • https://jira.mariadb.org/browse/MDEV-14448

Key focus points for MariaDB 10.3 • Oracle compatibility • More storage engines • Temporal data (system versioned tables) • Plus some of the features from 10.2+10.1+10.0+5.5+5.3+5.2+5.1 that may not be in stock MySQL

MariaDB storage engine offerings • MyRocks: for write-intensive workloads • SPIDER: for scalability and sharding • InnoDB: default for read/write operations (no longer Percona XtraDB since MariaDB 10.2) • ColumnStore: analytical purposes (not included in MariaDB Server 10.3 — still a separate download) • OQGRAPH: leaves algorithm • note: requires libJudy • PARTITION: updates to make SPIDER work better • Cassandra: still around, requires libthrift • CONNECT: for ETL operations • TokuDB: requires jemalloc and transparent hugepages to be never (not always)

Storage Engines • InnoDB 5.7 is now included in MariaDB Server 10.2 (there is no longer Percona XtraDB since 10.2) • you need to remove XtraDB related options in my.cnf or the server won’t start • https://lists.launchpad.net/maria-discuss/msg04708.html • More testing of InnoDB required, thank you community! • Test cases still need merging - https://jira.mariadb.org/browse/MDEV-13626 • Slow starts - https://jira.mariadb.org/browse/MDEV-13869 / https:// lists.launchpad.net/maria-discuss/msg04922.html • Hangs on startup - https://jira.mariadb.org/browse/MDEV-9843 • Persistent statistics - https://lists.launchpad.net/maria-discuss/msg04937.html

MyRocks • RocksDB (Facebook) is a fork of LevelDB (Google). MyRocks is the interface to it from MySQL/MariaDB • Write optimised • Focus on the endurance of flash devices to gain better lifetime (10x less write amplification) • Better compression than InnoDB (at least 2x) • Ability to load data fast, avoiding compaction overheads • Read-free replication (no random reads for updating secondary keys, only for unique indexes; RFR does away with it all, with row-based binlog) • Recommended read: https://mariadb.com/kb/en/library/differences-between- myrocks-variants/

SPIDER • Transparent sharding and re-sharding via SQL • Partition by range/key/hash/list • vertical partitioning engine, allows partition by columns • Condition pushdown to the storage engine layer • JOIN, GROUP BY done internally (on the data nodes/shards) • direct updates/deletes (pushdown to data nodes) • direct aggregates (sums, min, max, avg through partition engine) • Partition improvements: full-text support, multi-range read (MRR) • Read the docs, please! https://mariadb.com/kb/en/library/spider-storage- engine-overview/

Compression • Row compression (ROW_FORMAT=COMPRESSED), to page compression (PAGE_COMPRESSED=1), now to column compression • Bonus? Storage engine independent CREATE TABLE ùsers` ( ìd` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, `blurb` text /*!100301 COMPRESSED*/ DEFAULT NULL, PRIMARY KEY (ìd`) ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1

Compression show status like 'column_%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Column_compressions | 1 | | Column_decompressions | 0 | +-----------------------+-------+

Invisible columns CREATE TABLE ùser` ( ìd` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, `secret` varchar(10) INVISIBLE DEFAULT NULL, PRIMARY KEY (ìd`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 insert into user (id,name,secret) values ("1","colin","yes"); select * from user; +----+-------+ | id | name | +----+-------+ | 1 | colin | +——+-------+ select id,name,secret from user; +----+-------+--------+ | id | name | secret | +----+-------+--------+ | 1 | colin | yes | +----+-------+--------+

System versioned tables • SQL 2011 standard. Stores history of all changes. • Can alter a table to enable/disable/remove system versioned data • Queries? • AS OF to select data as of a point in time • BETWEEN .. AND to select data between two points in time • Partition data BY SYSTEM_TIME • Just ALTER .. ADD SYSTEM VERSIONING or create a table WITH SYSTEM VERSIONING

System versioned tables create table employees (name varchar(10), salary int, department varchar(10)) with system versioning; insert into employees values ("colin", 1000, “mktg"); update employees set salary=10000 where name=“colin"; update employees set department="eng" where name=“colin" select * from employees where name="colin"; +-------+--------+------+ | name | salary | dept | +-------+--------+------+ | colin | 10000 | eng | +———+--------+------+ select *, ROW_START, ROW_END from employees for SYSTEM_TIME ALL; +-------+--------+------------ +---------------------------- +----------------------------+ | name | salary | department | ROW_START | ROW_END | +-------+--------+------------ +---------------------------- +----------------------------+ | colin | 10000 | eng | 2018-06-26 13:00:53.772241 | 2038-01-19 03:14:07.999999 | | colin | 1000 | mktg | 2018-06-26 13:00:03.656662 | 2018-06-26 13:00:24.251594 | | colin | 10000 | mktg | 2018-06-26 13:00:24.251594 | 2018-06-26 13:00:53.772241 | +-------+--------+------------ +---------------------------- +----------------------------+

Oracle compatibility - Sequences • Sequences to create a sequence of numeric values • Not to be confused with replacing AUTO_INCREMENT, is an alternative to creating unique identifiers • With a sequence, you can compute the last number created by all existing sequences, whereas AUTO_INCREMENT can only compute its own last number created

Sequences create sequence seq start with 10 increment by 10; select nextval(seq); +--------------+ | nextval(seq) | +--------------+ | 10 | +--------------+ select nextval(seq); +--------------+ | nextval(seq) | +--------------+ | 20 | +--------------+ select nextval(seq); +--------------+ | nextval(seq) | +--------------+ | 30 | +--------------+ select lastval(seq); +--------------+ | lastval(seq) | +--------------+ | 30 | +--------------+ select previous value for seq; +------------------------ + | previous value for seq | +------------------------ + | 30 | +------------------------ +

Oracle PL/SQL • PL/SQL compatibility parser added for easier migration from Oracle to MariaDB • sql_mode=‘oracle’ • Data types (have synonyms in MariaDB): VARCHAR2 (VARCHAR), NUMBER (DECIMAL), DATE (DATETIME), RAW (VARBINARY), BLOB (LONGBLOB), CLOB (LONGTEXT) • CURRVAL, NEXTVAL • EXECUTE IMMEDIATE • Existing stored procedures, triggers • ROW datatype for stored routines • Cursors with parameters • Packages • https://mariadb.com/kb/en/library/sql_modeoracle-from-mariadb-103/

Other bits • INTERSECT and EXCEPT to UNION • Stored aggregate functions - functions that are computed over a sequence of rows and return one result for the sequence of rows • see https://jira.mariadb.org/browse/MDEV-16315 • Idle transaction timeouts • idle_transaction_timeout, idle_readonly_transaction_timeout, idle_write_transaction_timeout

What about the rest? • Instant ADD COLUMN is in MySQL 8 and MariaDB Server 10.3 • PROXY protocol support has been in Percona Server for MySQL 5.6/5.7, but is new to MariaDB Server and missing in MySQL 8.0

Using MySQL 8.0? Some features missing in MariaDB Server 10.3 • JSON is not stored as a binary data type • GTIDs are different in MariaDB (e.g. no GTID in OK packet) • No X Protocol, mysqlsh support • No group replication • PERFORMANCE_SCHEMA from MySQL 5.6 • No caching_sha256_password (ed25519) • mysql.user.password now is mysql.user.authentication_string • No password expiry, last changed, etc. however there is cracklib_password_check • No optimiser hints, optimiser trace • No SET PERSIST • No Native data dictionary in MySQL 8 (atomic, crash-safe DDLs, faster INFORMATION_SCHEMA, no more MySQL system tables) • Not as fast utf8mb4 • No persistent auto increment • No automatically managed UNDO tablespace • No InnoDB self-tuning (since InnoDB is from 5.7) • No invisible indexes • No TempTable Storage engine • No backup locks • No InnoDB native partitioning • No resource groups

Using MariaDB Server 10.3? MySQL 8.0 community edition is missing these features • More storage engines • MyRocks, TokuDB, CONNECT, SPIDER. MyISAM user? Segmented key caches will help • Threadpool • PAM authentication • GSSAPI authentication (Kerberos, Active Directory) • Optimistic parallel replication • ANALYZE <statement> • cracklib_password_check • Oracle compatibility • Temporal data (system versioned tables) • PAM/GSSAPI/SSPI authentication • AWS Key Management Plugin • Table elimination • User statistics • Dynamic columns • Invisible columns • Query cache

When to use MySQL? • http://www.thecompletelistoffeatures.com/ (which is MySQL 5.7 based, but really, many of those features may not quite be in MariaDB Server 10.3 even) • Performance…: http://dimitrik.free.fr/blog/index.html • Think about a MySQL InnoDB Cluster, based on group replication that is configured in the MySQL Shell, with the MySQL Router?

Don’t forget: http://features.today/ • signup at http://features.today/ for updated information of what is available in each MySQL or MariaDB Server or Percona Server for MySQL release • slides will be sent to the mailing list

Thank you! Colin Charles colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog | @bytebot on twitter slides: slideshare.net/bytebot

Differences between MariaDB 10.3 & MySQL 8.0

More Related Content

What's hot

Similar to Differences between MariaDB 10.3 & MySQL 8.0

More from Colin Charles

Recently uploaded

In this document

Differences between MariaDB 10.3 & MySQL 8.0