MySQL features missing in MariaDB Server

MySQL features missing in MariaDB Server

• 1.
  MySQL Features Missingin MariaDB Server Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter MariaDB Developers Unconference, New York 25 February 2018
• 2.
  Why this matters •MariaDB Server is the “default” MySQL in pretty much every Linux distribution except Ubuntu (shipping MySQL 5.7) • MariaDB isn’t MySQL - there are also many cloud providers that have an option for MariaDB • Amazon Web Services (AWS) RDS MariaDB, Microsoft Azure, Rackspace Cloud
• 3.
  Commitments • http://mariadb.org/about/ •“It is an enhanced, drop-in replacement for MySQL.”
• 4.
  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
• 5.
  What’s documented? • https://mariadb.com/kb/en/the-mariadb-library/mariadb-vs-mysql- compatibility/ • 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/ • whatever is not included (“incompatibilities”), what are plans to include it?
• 6.
  Replication - peoplecan’t attach a MySQL replica
• 7.
  GTID variances • https://mariadb.com/kb/en/library/gtid/#the-domain-id<— good MariaDB Server invention, however MySQL lacks it
• 8.
  Replication • Default binlogformat is now MIXED • ROW in MySQL • Default replicate_annotate_row_events is ON • OFF in MySQL • Multi-source replication in MySQL is limited to 256 channels; in MariaDB Server it is 64, but seemingly expands when needed, and there is no upper limit (really?) • GTIDs in the OK packet - ProxySQL uses —session-track-gtids for GTID consistent reads (see: https://fosdem.org/2018/schedule/event/ proxysql_gtid/)
• 9.
  Synchronous replication • MySQLGroup Replication in MySQL / Percona Server • Galera Cluster for MySQL / Percona Server / MariaDB Server • Group Replication + MySQL Router + mysqlsh = MySQL InnoDB Cluster • Microsoft Windows support? (group replication works there)
• 10.
  MDEV-3953 rows_examined is addedas examined_rows, but missing rows_sent/rows_read
• 11.
  JSON - MySQL8 has syntax additions • 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 • claims faster, but no benchmarks - https://jira.mariadb.org/browse/ MDEV-13777
• 12.
  X Protocol • MariaDBServer has no support for the MySQL X Protocol • This means you cannot use mysqlsh to access MariaDB Server either • mysqlsh to be released as part of MySQL 8; supports JavaScript, Python interfaces to MySQL • Using MySQL as a Document Store? https://dev.mysql.com/doc/ refman/5.7/en/document-store.html • also requires connector support: Connector/J, Connector/Node.js, Connector/Net, Connector/Python, Connector/C++ • admin interface to mysqlsh (e.g. to control InnoDB cluster)
• 13.
  Encryption • MySQL 5.7and 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) • One does not encrypt logs, the other does • MySQL 8 has redo log, undo log encryption, but not binary log encryption • Percona Server 5.7.20-18 has binary log encryption + HashiCorp Vault keyring plugin • 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 • also SSL support now in mysqlbinlog
• 14.
  CJK language support •Add “ngram” support to MariaDB Server: https://jira.mariadb.org/ browse/MDEV-10267 • Add “MeCab” support to MariaDB Server: https://jira.mariadb.org/ browse/MDEV-10268 • gb18030 support: https://jira.mariadb.org/browse/MDEV-7495
• 15.
  PERFORMANCE_SCHEMA • No sysschema - https://jira.mariadb.org/browse/MDEV-9077 • No new PERFORMANCE_SCHEMA instrumentation from 5.7 - https://jira.mariadb.org/browse/MDEV-6114 • e.g. 52 rows in set (0.00 sec) vs. 87 rows in set (0.00 sec) • current mix from 5.6.17/5.6.24/5.6.26 • It can’t be turned on dynamically! (so maybe consider leaving it turned on once 5.7 or 8.0 one is merged?)
• 16.
  Optimiser • Optimiser trace:https://jira.mariadb.org/browse/MDEV-6111 • Optimizer hints - https://jira.mariadb.org/browse/MDEV-9078 • “cost model” information, also in EXPLAIN FORMAT=JSON • Server side query rewriting? - https://jira.mariadb.org/browse/ MDEV-5561 • http://mysqlserverteam.com/write-yourself-a-query-rewrite-plugin- part-1/
• 17.
  Other bits • MariaDB:SHOW EXPLAIN FOR <thread_id> • MySQL: EXPLAIN FOR CONNECTION <thread_id> • https://jira.mariadb.org/browse/MDEV-10000 (simple, easy fix) • 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
• 18.
  Installation… • MySQL: https://dev.mysql.com/downloads/repo/yum/- grab a package — e.g. mysql57-community-release-el7-9.noarch.rpm • MariaDB Server: https://downloads.mariadb.org/mariadb/ repositories/ - copy/paste, edit a file, then install • Percona Server: https://www.percona.com/doc/percona-server/5.7/ installation/yum_repo.html - yum install Percona-Server-server-57 • Think its time proper repository files are provided for ease of installation
• 19.
  Security • MySQL: sha256_password& MariaDB: ed25519 password plugin • MySQL 8: caching_sha2_password default instead of mysql_native_password • uses caching to address latency @ connect time • how will MySQL 8 users be migrated to MariaDB Server? • validate_password is on by default in MySQL 5.7 (not in MariaDB) • re-written for MySQL 8 infrastructure • No more test database or anonymous users created on installation
• 20.
  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 is coming? 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 • MySQL 8: password history information saved (prevent password reuse)
• 22.
  More 5.7 • RENAMEINDEX - https://jira.mariadb.org/browse/MDEV-7318 • ALTER TABLE .. RENAME COLUMN also in 8.0 (https:// dev.mysql.com/worklog/task/?id=10761) • GIS: GeoJSON functions? Geohash functions? • SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)’),2); • SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
• 23.
  Tools • including newtools like mysql_ssl_rsa_setup ? mysqlpump? • When merging XtraDB (pre-10.2), why isn’t it complete, with backup locks? https://jira.mariadb.org/browse/MDEV-5336 • tools that require MySQL GTID don’t work with MariaDB Server (e.g. mysqlfailover, mysqlrpladmin, MHA, MySQL Router, etc.) • vitess, started life on just MariaDB Server, but note they support 5.6/5.7 and generally only 10.0
• 24.
  Storage Engines • InnoDB5.7 is now included in MariaDB Server 10.2 (there is no longer Percona XtraDB for the first time) • you need to remove XtraDB related options in my.cnf or the server won’t start • https://lists.launchpad.net/maria-discuss/msg04708.html • Is InnoDB fully tested? • Test cases still need merging - https://jira.mariadb.org/browse/MDEV-13626 • Hangs on startup - https://jira.mariadb.org/browse/MDEV-9843 • BLACKHOLE, FEDERATED (now FederatedX) require you to actually load the plugins • was a “surprise” - https://jira.mariadb.org/browse/MDEV-11942 (now fixed) • Status of other engines: OQGRAPH, SphinxSE? Cassandra deprecated
• 25.
  MySQL 8 (whatMariaDB Server 10.3 will be compared to?) • Roles, including ROLES_GRAPHML() function • Common Table Expressions • Window Functions • Transactional data dictionary + FRM files obsoleted • atomic DDLs • Resource groups - assign threads, control CPU resources • InnoDB? Serialized Dictionary Information (SDI)? “auto-configuration” via innodb_dedicated_server • Invisible indexes - https://jira.mariadb.org/ browse/MDEV-7317 • Regex changes - using ICU (full unicode, multibyte safe) not PCRE like MariaDB Server • internal temporary tables in TempTable storage engine • Error logging rewritten, now you can also get JSON logs • Query cache removed • No more mysql_install_db (use mysqld — initialize) • storage engine needs to support PARTITIONING
• 26.
  Open ended… • Howoften are upgrades from 5.6/5.7 tested? • What is the status of community contributions, e.g. HandlerSocket (e.g. deprecated in Percona Server 5.7) • status of QA - people don’t like “surprises” when they upgrade point releases • e.g. InnoDB slow starts - https://jira.mariadb.org/browse/MDEV-13869 / https://lists.launchpad.net/maria-discuss/msg04922.html • status of merges to avoid “merge errors” (e.g. introducing unwanted fsync()’s that go undetected for quite sometime)
• 27.
  Thank you! Colin Charles colin.charles@percona.com/ byte@bytebot.net http://bytebot.net/blog | @bytebot on twitter slides: slideshare.net/bytebot