Schedule Meeting

a

MySQL 8.0.33 : Quick Overview

by | May 5, 2023 | MySQL

The MySQL Team has released MySQL 8.0.33 very recently on April 18, 2023. This release contains a number of bug fixes along with some interesting improvements. In this blog post, I’ll cover the most important changes in MySQL 8.0.33.

Variables can be set while installing the components

Starting from MySQL 8.0.33, it is allowed to SET the system variables while installing components. It has become possible to use both GLOBAL|PERSIST options. Previously, we had to install the components prior to setting the configurations manually:

Before MySQL 8.0.33

mysql> INSTALL COMPONENT 'file://component_validate_password'
-> SET GLOBAL validate_password.policy=2,
-> PERSIST validate_password.length=25;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET GLOBAL validate_password.policy=2,
PERSIST validate_password.length=25' at line 2

From MySQL 8.0.33:

mysql> INSTALL COMPONENT 'file://component_validate_password'
-> SET GLOBAL validate_password.policy=2,
-> PERSIST validate_password.length=25;
Query OK, 0 rows affected (0.02 sec)
mysql> select @@validate_password.length, @@validate_password.policy;
+----------------------------+----------------------------+
| @@validate_password.length | @@validate_password.policy |
+----------------------------+----------------------------+
| 25 | STRONG                                             |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

Changes in old replication terminology

The MySQL product terms “master to source”, “slave to replica” ,”MTS to MTA” are replaced in error messages. Currently, all the error messages are listed in the following files. These files can be found under the base directory.

  • messages_to_clients.txt
  • messages_to_error_log.txt
root@vm-mysql:/usr/share/mysql-8.0# pwd
/usr/share/mysql-8.0
root@vm-mysql:/usr/share/mysql-8.0# ls -lrth | grep -i message
-rw-r--r-- 1 root root 384K Mar 16 17:22 messages_to_error_log.txt
-rw-r--r-- 1 root root 615K Mar 16 17:22 messages_to_clients.txt
oot@vm-mysql:/usr/share/mysql-8.0# less messages_to_error_log.txt | grep MTA | head -n1
ER_RPL_MTA_RECOVERY_STARTING_COORDINATOR
root@vm-mysql:/usr/share/mysql-8.0# less messages_to_error_log.txt | grep SOURCE | head -n1
ER_RPL_BINLOG_SOURCE_SENDS_HEARTBEAT
root@vm-mysql:/usr/share/mysql-8.0# less messages_to_error_log.txt | grep REPLICA | head -n1
ER_SHUTTING_DOWN_REPLICA_THREADS

The present task does not perform this replacement for messages used in other contexts.

Performance_schema server telemetry trace service

The Performance_schema server telemetry trace service has been introduced in MySQL 8.0.33. With this interface, plugins and components can retrieve notifications related to SQL statements’ lifetime. Currently, the related information is available in the MySQL source code documentation.

The following two new changes have recently been added:

  • New “telemetry_traces_supported” parameter
  • New “telemetry_active” column in the performance_schema.threads table
mysql> show global status like '%tele%';
+----------------------------+-------+
| Variable_name | Value              |
+----------------------------+-------+
| Telemetry_traces_supported | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> select table_schema, table_name, column_name from information_schema.columns where table_name='threads' and column_name like 'tele%' ;
+--------------------+------------+------------------+
| TABLE_SCHEMA.      | TABLE_NAME | COLUMN_NAME      |
+--------------------+------------+------------------+
| performance_schema | threads | TELEMETRY_ACTIVE    |
+--------------------+------------+------------------+
1 row in set (0.00 sec)

User-defined COLLATIONS will create warnings

User-defined collations are now deprecated from MySQL 8.0.33.

  • COLLATE followed by the name of a user-defined collation in a SQL statement
  • Use of the name of a user-defined collation as the value of collation_server, collation_database, or collation_connection

Example

I have created a new collation “latin1_sri_ci” and tried to use it in this SQL statement. It gives the warning as below as,

mysql> SHOW COLLATION WHERE Collation = 'latin1_sri_ci';
+---------------+---------+------+---------+----------+---------+---------------+
| Collation.    | Charset | Id   | Default | Compiled | Sortlen | Pad_attribute |
+---------------+---------+------+---------+----------+---------+---------------+
| latin1_sri_ci | latin1  | 1024 |         |          | 0       | PAD SPACE     |
+---------------+---------+------+---------+----------+---------+---------------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE herc CHARACTER SET latin1 COLLATE latin1_sri_ci;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings\G
* 1. row *
Level: Warning
Code: 4162
Message: 'latin1_sri_ci' is a user defined collation. User defined collations are deprecated and will be removed in a future release. Consider using a compiled collation instead.
1 row in set (0.00 sec)

Other Important features

  • mysqlbinlog option –start-position now accepts values up to 18446744073709551615, unless the –read-from-remote-server or –read-from-remote-source option is also used, in which case the maximum is 4294967295. Previously it was limited to 4GB. If the binary log size exceeds 4GB ( due to bulk transaction ), “–start-position” will not accept any value higher than 4G. See more details in this bug report.
  • Binary packages that include curl rather than linking to the system curl library have been upgraded to use curl 7.88.1.

Note. It looks like there is a confusion about “InnoDB: Parallel Index Build” In MySQL 8.0.33 release notes. I have verified it in the MySQL community Slack channel. It was mentioned mistakenly in MySQL 8.0.33 release notes, and later it was removed. So, there is NO change with “InnoDB: Parallel index build” in MySQL 8.0.33.

Conclusion

Like previous releases, MySQL 8.0.33 came up with a lot of bug fixes and significant changes. Consider upgrading to MySQL 8.0.33 with proper testing. At Vettabase, we provide help for nearly all tasks related to MySQL administration (contact us via sa***@ve*******.com if you need assistance).

Join our next webinar on MySQL 8.0 asynchronous replication improvements at 4 PM BST on May 30, registration is now open!

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Sri Sakthivel
Sri Sakthivel M.D. is an Oracle certified MySQL DBA with 7+ years of work experience in the MySQL ecosystem. At work, Sri leverages MySQL and the related technologies to ensure maximum database performance in the customer setup. Sri has vast practical experience with MySQL / Percona Server / MariaDB and ClickHouse database environments. His professional interests include MySQL clustering technologies like Galera and Group replication. Sri is a prolific MySQL blogger and author of many YouTube videos currently working for Vettabase Ltd. as a MySQL consultant. He has also spoken at conferences including Percona Live and OSA.

Recent Posts

Getting Started with MindsDB and MySQL

Getting Started with MindsDB and MySQL

If you have not already heard, Vettabase is now a partner of MindsDB, working on improving MySQL compatibility. In this post we shall take a look at getting started with MindsDB by connecting to MySQL and some of the improvements to date. We have a ready to go example...

MariaDB/MySQL: working with storage engines

MariaDB/MySQL: working with storage engines

MariaDB and MySQL support several storage engines. See MariaDB and MySQL storage engines: an overview for a discussion about existing MariaDB and MySQL storage engines. Here we'll see how to work with them. We'll see how to obtain information about storage engines,...

MariaDB and MySQL storage engines: an overview

MariaDB and MySQL storage engines: an overview

MySQL was the first DBMS to introduce the concept of storage engines in the early 2000s. This was one of its main features. Later, MariaDB extended the storage engine API and included some storage engine maintained by third parties as part of its official...

Services

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *