The MySQL 8.0.19 Maintenance Release is Generally Available

The MySQL Development team is very happy to announce that MySQL 8.0.19 is now available for download at dev.mysql.com. In addition to bug fixes there are a few new features added in this release. Please download 8.0.19 from dev.mysql.com or from the MySQL Yum, APT, or SUSE repositories. The source code is available at GitHub. You can find the full list of changes and bug fixes in the 8.0.19 Release Notes. Here are the highlights. Enjoy!

InnoDB ReplicaSet

Following InnoDB Cluster as our first, fully integrated MySQL HA solution based on Group Replication, InnoDB ReplicaSet delivers another complete solution, this time based on MySQL Replication. The basic idea for InnoDB ReplicaSet is to do the same for classic MySQL Replication as InnoDB Cluster did for Group Replication. We take a strong technology that is very powerful but can be complex, and provide an easy-to-use AdminAPI for it in the MySQL Shell.

In just a few easy to use Shell commands, a MySQL Replication database architecture can be configured from scratch, including data provisioning using CLONE, setting up replication and performing manual switchover/failover. MySQL Router understands the topology and will automatically load balance/redirect traffic.

Router

Add support for ReplicaSet in Router (WL#13188) This work by Andrzej Religa extends the MySQL Router to understand the concept of InnoDB ReplicaSets. This means loading and caching the topology information from the metadata schema as well as regularly updating the status information of the setup in order to perform the correct routing operations.

Handle the metadata upgrade in the Router (WL#13417) This work by Andrzej Religa extends the Router to understand and properly handle the “0.0.0” version and the “metadata_upgrade_in_progress” lock.

New bootstrap option (–account) to allow reuse of an existing account (WL#13177) This work by Pawel Mroszczyk adds a startup option to specify the account the router should use when talking to the server. The router needs a server account to be able to talk to the InnoDB Cluster. Until now, this account is always automatically created during bootstrapping.

SQL

Implement ALTER TABLE … DROP/ALTER CONSTRAINT syntax (WL#12798) This work by Praveenkumar Hulakund adds the SQL DROP CONSTRAINT and ALTER CONSTRAINT clauses to the ALTER TABLE statement. This work supplements WL#929 where the SQL CHECK CONSTRAINT clauses were added to the CREATE TABLE and ALTER TABLE statements and fixes Bug#3742.

Table with JSON Schema validation constraint should return error for concrete row (WL#13195) This work by Praveen Hulakund adds an SQL condition in the Diagnostics area with an error code pinpointing the schema violation in the form of “The JSON document location X failed requirement Y at JSON Schema location Z”. See also WL#13196 below.

TIMESTAMP/DATETIME values can include timezone details (WL#10828) This work by Martin Hansson makes it possible to include time zone details along with the TIMESTAMP value. Without explicit time zone information or offset, there might exist date/times which in some time zones that are not well defined, like when Daylight Saving Time changes the time backwards. This is a feature request from Booking.com (Bug#83852).

Support LIMIT in recursive common table expression (WL#12534) This work by Guilhem Bichot adds support for LIMIT in CTEs. The LIMIT is on the total number of rows in the CTE, not on the number of rows that each iteration may produce. See also Bug#92857.

Implement table value constructors: VALUES (WL#10358) This work by Thomas Aven implements standard SQL syntax for table value constructors. But due to a conflict with the VALUES() function, a MySQL non-standard feature, we decided to use the SQL standard verbose form of the table value constructors: VALUES ROW(1, 2),… See also Bug#77639.

Referencing old/new row in INSERT … ON DUPLICATE KEY UPDATE (WL#6312) This work by Thomas Aven extends the INSERT … VALUES/SET … ON DUPLICATE KEY UPDATE syntax to make it possible to declare an alias for the new row and columns in that row, and refer to those aliases in the UPDATE expression. The intention with this new feature is to be able to replace VALUES() clauses with row and column alias names.

Optimizer

Enhance group-by loose index scan (WL#13066) This work by Sergey Gluhov improves the loose index scan (LIS) used for GROUP BY queries by lifting the current limitation on the number of infix ranges (range on non-grouping column) that can be present in the query. This improvement will make the optimizer choose LIS for more queries. This feature is based on a contribution by Facebook (Bug#90951).

BKA in iterator executor (WL#13002) This work by Steinar H. Gunderson implements BKA (Batch Key Access) in the iterator executor. For now, only inner joins are supported, other join types will be added later.

Information Schema

INFORMATION_SCHEMA views for roles (WL#10895) This work by Gopal Shankar implements SQL Standard Information Schema views for SQL Roles. The APPLICABLE_ROLES view shows the roles that are applicable for the current user. The ADMINISTRABLE_ROLE_AUTHORIZATIONS view shows the roles that are applicable for the current user, which can also be granted to other users. The ENABLED_ROLES view shows the roles that are enabled for the current user. The ROLE_TABLE_GRANTS view shows the table grants for the roles that are enabled for the current user. The ROLE_COLUMN_GRANTS view shows the column grants for the roles that are enabled for the current user. And finally, the ROLE_ROUTINE_GRANTS view shows the routine grants for the roles that are enabled for the current user.

Security

FAILED_LOGIN_ATTEMPTS/PASSWORD_LOCK_TIME counters per user (WL#13515) This work by Georgi Kodinov enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements. See CREATE USER Password-Management Options.

Store secret data in keyring (WL#12859) This work by Harin Vadodaria enhances the existing keyring plugins to support a new type of data called SECRET. This allows users to store, retrieve, and manage any opaque data in the keyring (max 16K). The keyring backend will not interpret the secret but simply treat it as a BLOB. This functionality will typically be used to store secret data such as passwords and certificates.

InnoDB

InnoDB: Support for sampling table data for generating histograms (WL#8777) This work by Darshan M N implements the handler API defined in WL#9127 by sampling data pages from the clustered index and providing the records stored in these pages.

Innodb: Case insensitive names for partition tables (WL#13352) This work by Debarun Banerjee solves user issues related to alphabetical case differences in partition name and separators. The solution is to always handle partition name, partition separator (#p#), sub-partition name and sub-partition separator (#sp#) in a case insensitive manner so that the difference in case doesn’t matter for file names on disk and metadata stored in the data dictionary. This includes handling of partitioned tablespace file names during import.

Replication

Configure replication applier to require row-based replication (WL#12968) This work by Pedro Gomes ensures that a replication channel only accepts row-based replication. This allows restricting the type of instruction executed on the slave, and as a side effect, limits the number of privileges a user needs when associated to the replication applier.

MTS: slave-preserve-commit-order when log-slave-updates/binlog is disabled (WL#7846) This work by Hemant Dangi implements applier commit order, even when the worker threads are not logging the changes to the replicas binary log (–log-slave-updates=off). The main motivation for this is to be able to turn on parallel replication on a replica that has no binary log enabled without having different commit histories between the master and the slave. In other words, the transaction commit history, as observed in the replication stream, is preserved because the applier executes incoming transactions in parallel but commits them in the order that they appear in the stream.

Support for TLS 1.3 in Asynchronous Replication (WL#13392) This work by Nuno Carvalho supplements previous work to support TLS 1.3 in the connections between MySQL clients and servers (WL#12361) by adding support for TLS 1.3 to Replication as well. This work implements the MASTER_TLS_CIPHERSUITES option on CHANGE MASTER command, the group_replication_recovery_tls_version plugin option and the group_replication_recovery_tls_ciphersuites plugin option.

X Protocol

Connection Compression (WL#9252) This work by Lukasz Kotula implements client-server data compression over the X Protocol. The compression algorithm is negotiable based on server capabilities.

Zstd compression (WL#13442) This work by Grzegorz Szwarc implements zstd compression in the X Protocol. WL#9252 added deflate and lz4 compression support to xprotocol. The X Protocol now supports three compression algorithms: deflate, lz4, and zstd. The zstd algorithm has been found to perform better than deflate and has a compression ratio much better than lz4.

Add schema validation to the create collection method (WL#12965) This work by Tomasz Stepniak implements a document schema validator in the X Plugin. From the DevAPI perspective, the user calls “var coll = schema.createCollection(“mycollection”, {validator: validationDoc})”. The createCollection() is implemented in the X Plugin and this implementation is extended with a call to the JSON_SCHEMA_VALID(, ) function introduced by WL#11999.

Collection with json-schema validation must return error for concrete row (WL#13196) This work by Tomasz Stepniak extends WL#12965 by identifying the exact location where a document violates a schema constraint. See WL#13195 above.

MTR testsuite

Move testcases in binlog, sys_vars, and funcs_1 suites that needs MyISAM to a separate .test file (WL#13410 and WL#13241) This work by Pooja Lamba moves the sections that need MyISAM to a separate .test file. This allows the MTR test suite to run on a server that is built without the MyISAM engine.

Other

Make mysql command line tool’s –binary-as-hex be on by default for interactive terminals (WL#13038) This work by Georgi Kodinov enables the option –binary-as-hex by default for command line tools running in “interactive mode”. The current default of –binary-as-hex (OFF) is kept for non-interactive runs. Historically, the MySQL command line tools have asked the server to convert all the data it receives to text so it can display them. But for some data (e.g. GEOMETRY data types) the conversion spawns binary strings that might contain non-printable symbols which can garble certain terminals. This is avoided by printing the hex dump instead of text.

Specifying Character Sets in the UDF API (WL#12370) This work by Rahul Sisondia extends the User Defined Functions (UDF) API to let the UDF creator specify which character sets to use in the arguments and the return value. Previously the UDF API simply assumed ASCII for both string arguments and the return value.

Sys Schema, Replace Stored Functions With Native Functions (WL#13439) This work by Chris Powers replaces stored functions with performance schema native functions in SYS Schema stored programs. The performance schema native functions are: format_bytes(), format_pico_time(), ps_current_thread_id(), and ps_thread_id(connection_id). The original SYS Schema functions are deprecated and will be removed in some future release.

Enable the “system” mysql command line command for windows (WL#13391) This work by Georgi Kodinov enables the system command in the MySQL command line tool on Windows. The system command executes its arguments as an OS command and displays the result. It has been working for Linux in the past and since the MSVC CRT now supports all the necessary APIs it is time to enable it for Windows as well. This fixes Bug#58680.

Component service to add “admin” session (WL#13378) This work by Georgi Kodinov adds a new component service to allow its caller to create a special kind of SQL session that is not a subject to the max_connection limit. This is done to ensure that internal mechanisms are not limited by max_connections. For example, group replication cannot operate when the server hits max_connections limit (see Bug#94987).

Add more information to Duplicate Key Error (WL#12589) This work by Jens Even Blomsøy extends the error information given in cases of DUP_ENTRY_KEY to include the table name along with the key name and key value in the error message. This work is based on a contribution from Facebook (Bug#92530). See also Bug#47207.

Use signal SIGUSR1 to flush logs (WL#13689) This work by Jens Even Blomsøy redefines signal SIGUSR1 to be a light version of SIGHUP. The SIGUSR1 will cause the server to flush error log, general log and slow query log, but not send the MySQL status report.

Split the errmgs-utf8.txt file (WL#13423) This work by Tatjana Nurnberg splits the errmgs-utf8.txt file into one file for messages to clients and another for messages to the error log (messages_to_clients.txt and messages_to_error_log.txt). This is a code refactoring helping developers to distinguish between client messages and error log messages.

Deprecation and Removal

Deprecate display width and UNSIGNED for the YEAR datatype (WL#13537) This work by Jon Olav Hauglid deprecates the syntax YEAR(4). Use the equivalent YEAR instead. This work also deprecates the use of UNSIGNED with YEAR. UNSIGNED is not documented to work with YEAR and has no effect.

Remove integer display width from SHOW CREATE output (WL#13528) This work by Jon Olav Hauglid changes SHOW CREATE to not output integer display width unless ZEROFILL is also used. Without ZEROFILL, integer display width has no effect. This work is a logical consequence of deprecating the display width attribute for integer types (WL#13127).

XProtocol: Removal of the deprecated namespace of Mysqlx.Sql.StmtExecute message (WL#13057) This work by Grzegorz Szwarc removes the namespace xplugin from the list of supported namespaces. Thus, the xplugin namespace becomes an unknown identifier and any administrative commands sent within this namespace will not be handled. If the user continues to use this namespace, they will be notified by a standard error message. The xplugin namespace in the Mysqlx.Sql.StmtExecute message has been deprecated since 5.7.14.

Thank you for using MySQL!

跑步七年之痒

跑步七年回顾。

1. 跑步经历

我是2012年8月从北京回到福州工作的。

刚到福州没多久,就和同事龚千万约好去公司旁边的公园跑步。当时的出发点很简单,因为马上快要当爹了,就是为了能有个能抗住半夜起来泡奶的好身体,同时也是为了持续改善神经衰弱的毛病。

为了跑步大业,我还特地去买了一双跑鞋,好像是安踏还是阿迪之类的,反正是很不专业的那种,穿着并不是很舒服。刚开始起步,我跑个一公里都有点困难,经过一两个月的练习,慢慢能跑到三公里左右了。也慢慢发展了几位一起跑步健身的同事。

就这么没什么系统性的跑了一阵后,正好同事和勇同学从北京也来到福州工作,他带着我们几个人更加科学的跑,我也跟着买了个佳明620运动手表,跑步的水平也得到慢慢提升。

第一次参加马拉松(半程)有点机缘巧合,当时正好赶上武夷山马拉松报名快截止了,心里还有点忐忑,不太敢报名,最后还是咬咬牙报名了,心想也跑步有阵子了,试着参加一次看看成绩如何。几个小伙伴一起报名,最后跑下来成绩也都还算符合预期。此后,就陆陆续续参加几场省内的马拉松,有宁德霞浦的三沙山地马拉松,三明泰宁大金湖马拉松,厦门马拉松,以及福州本地的赛事,还有一次凑巧去台北,也顺便跑了一场,这时候还都只是敢报半程的,还跑过一次15公里的越野马拉松,那个时期刚接触马拉松,还挺热衷参加的,也正是国内马拉松赛事刚刚兴起的时候。

第一次跑全程马拉松是2016年在厦门,当时检录还没有很严格,我用了别人的名额去跑的(此处特别强调,不要顶替别人名额去跑马,容易出事,且要背锅),那次成绩也还算理想。此后也参加过武夷山、北京的全马,但成绩一次比一次差。

时间进入2018年,我开始重视提高腰背腿的核心力量训练(使得在长距离跑步后期还能有体力),同时可以提高步频并调低步幅(有点小步快跑的意思,可以降低触底时长,减少能量消耗)。此后成绩开始逐步提升了。再到2019下半年,实现2个小时内的半马成绩小突破。再到2020年初的厦门马拉松,实现了4小时17分的PB成绩,进步还是挺明显的。

特别提一下2020年的厦马,我在前面的38公里,除了稍微停顿补给外,几乎都在跑动,没有什么特别的疲劳和不适感(特别备了髌骨带也都还没用上)。在38-41公里期间,差不多是跑800米就改快走几步的节奏,到了最后1.5公里时,看到430的兔子(此兔子至少比我早出发10分钟)追上来了,就又咬着牙持续跑完没再改快走,直到终点。

2. 关于跑步损伤

事实上,我在几年的跑步生涯中也是有受过伤的。说几次比较严重的。

第一次是参加厦门半马,人太多了,跑不开,于是左冲右突式前进,最后造成右脚踝受伤,大概休息了半年才好。

第二次是抱娃时用力过猛,腰扭伤了,趴在床上一整天,再贴虎皮膏药以及撒隆巴斯之类的,两三天后才基本恢复。

第三次是跳绳导致膝盖受伤,可能是因为没热身到位,或者跳绳的姿势不对,也休了快两个月才恢复。

幸好都不是什么太严重的伤痛,没影响到跑步。在这里要给各位认真的建议,如果遇到运动损伤,一定要确认休息到位了,才能继续运动,当然最好是去找专业的医生检查下更放心。

3. 关于跑步配套

关注公众号「老叶茶馆」(imysql_wx),发送 “跑步装备” 四个字到后台,即可得到推送,我这里不多赘述。

4. 关于马拉松

很多人可能不理解,跑完马拉松,可能整个人都快虚脱了,要死要活的有什么意思呢。就说说我个人的体验吧。

上面提到了,一开始跑步是为了有健康的体魄,第一次参加半马是为了检验跑步成果。后来的几次参赛,则是对一种新事物刚迷上时的热衷心态。到最近几次参赛,则是为了磨练意志力了。试想下,那么艰难(当然对有些人来说是小菜一碟)的全马都能咬牙跑完,还有什么事是做不来的呢。我现在工作的MacBook Pro就是完成第一场全马的到的自我奖励。

人生亦是一场马拉松,没挂之前,都得努力奔跑,一停下就会被身后的人超越,甚至被迫登上收容车。

最后,广告是不能少滴。

我的新课程《MySQL性能优化》已经在腾讯课堂发布,本课程讲解读几个MySQL性能优化的核心要素:合理利用索引,降低锁影响,提高事务并发度。下面是报名小程序码,厚着脸皮请求大家推荐给需要的小伙伴们。

下面是本课程内容目录

更多关于本课程内容还可以点击文末“阅读原文”查看。

厦马归来,我做了个任性的决定

厦马前我发了个愿。

今天,我参加了厦门马拉松,老天爷给面子,在我跑完之前还没出太阳,虽然湿度略高,不过对我这种长期生活在福建的人而言,这都不是事。 2019年我平时的拉练还算努力,所以这次前面的38公里整体状态还不错。过了38公里后,才有点疲了,偶尔改成快走。到了最后一公里还能坚持慢跑到底,也是挺佩服自己的。 这是本次的服装和奖牌

这是跑前状态

这是跑后状态,感觉还好,还没到累死累活的那种

这是成绩详情

说完厦马,再说说另一个事。在赛前,我在内部说了下,如果这次能PB(跑出个人最好成绩,Personal Best),我主理的MySQL优化班就搞优惠促销活动。最近的整体环境不太好,大家手头的钱也比较紧张,所以才有这个想法。这次的优惠促销活动计划把MySQL优化班单班价格从4600元调整到4200元,如果报双班则还是8000元不变。更具体的规则请扫码详询知数堂助教妹子。希望这次的优惠活动能帮到那些有心学习MySQL优化知识的同学,也感谢大家对我们一贯的支持。

好了,就这些。

最后,放个俺家茶叶小店的二维码,欢迎下单选购便宜不贵的口粮茶。