Author: Frederic Descamps

Drupal and MySQL 8.0.11 – are we there yet ?

Now that MySQL 8.0 GA is out for almost 3 months, let’s see the status of how it’s integrated with Drupal, a very popular CMS using MySQL.
For people having already a Drupal site and that wants to upgrade to MySQL 8.0, please check this post.
Now if you want to use MySQL 8.0 with a fresh new Drupal 8, let’s have a look how does that work.
Drupal 8.5
Drupal 8.5.5 is the latest available stable release from July 4th 2018.
There is no notes about supporting MySQL 8.0. So let’s try it.
First I install MySQL 8.0.11 and unpack Drupal 8.5.5 in the apache’s datadir. Don’t forget to install php-mysqlnd.
Then I create the drupal8 database and setup an account:
mysql> create database drupal8;
mysql> create role drupal_site;
mysql> grant all on drupal8.* to drupal_site;
mysql> create user drupal_web identified by ‘drUp4l’;
mysql> grant drupal_site to drupal_web;
mysql> alter user drupal_web default role drupal_site;

As you can see, I’m using the new ROLES feature added in MySQL 8.
We can use our browser and point it to the Drupal url and follow the installation procedure until we arrive to the database form:

The first problem is related to the charset:
So by default, Drupal 8.5 is not compatible with MySQL 8.0 and it’s also written on this page:

Before we move forward and try on Drupal 8.6, let me show you how to configure MySQL to also work on Drupal 8.5.
In my.cnf, please add those lines in [mysqld] section:
character-set-server = utf8
default_authentication_plugin = mysql_native_password

And you can change the authentication plugin for the account used for Drupal:
mysql> ALTER USER drupal_web IDENTIFIED WITH ‘mysql_native_password’ BY ‘drUp4l’;

And reload the Drupal page….

… to have our next error
SQL_MODE doesn’t allow anymore ‘NO_AUTO_CREATE_USER’ (please check here for other similar changes)
To fix this, we need to modify some Drupal source code and remove it from the line 183 in core/lib/Drupal/Core/Database/Driver/mysql/Connection.php:
$connection_options[‘init_commands’] += [
-‘sql_mode’ => “SET sql_mode = ‘ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY'”,
+’sql_mode’ => “SET sql_mode = ‘ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY'”,
];

You can then continue the install process and it works:

Drupal 8.6-dev
As Drupal website states that Drupal 8.6 should work with MySQL 8.0, let’s try it
Also as the PDO Documentation states it (here), the version before 7.1.16 will return errors even with mysql_native_password authentication method, so I update PHP to 7.1.18.
But don’t forget that currently no version of mysqlnd supports caching_sha2_password and if your account uses that authentication method you will get such error:

The only PHP connector for MySQL supporting it is mysql_xdevapi.
So I change again the authentication method to mysql_native_password and reload the configuration wizard of Drupal:

Again this error ?! We already saw how to fix it. So even if Drupal 8.6 should support MySQL 8.0, all is not yet finished as it states here too: issue 2966523
So please check this issue for updates. Currently few tasks are remaining.
Then you can keep running the wizard:

Conclusion
Not everything is already net and smooth but some progress have been made. To have a full support of MySQL 8.0 out of the box, we need to wait for Drupal 5.6 and a version of mysqlnd supporting the new authentication method used in MySQL 8.0 by default.
Since then, some minor changes are required.
If you need help with your community tool or product to support MySQL 8.0, please don’t hesitate to reach me out, it will be a pleasure to help.
 

MySQL InnoDB Cluster : MySQL Shell and the AdminAPI

As promised, here is a post more detailed on how to create a MySQL InnoDB Cluster using MySQL Shell and the AdminAPI.
First of all, as a good practice is never enough repeated, whatever the version of MySQL you are using, please use the latest MySQL Shell ! So if you are using 5.7, please use MySQL Shell 8.0.11. See this compatibility matrix or this official one.
dba class
The AdminAPI can be accessed by the MySQL Shell via the dba object. The reference manual for this class is here. The Shell provides you a very useful method for mostly all objects: help() (this method is not yet documented).
Let’s call the help() in the Shell and see the output:
MySQL JS> dba.help()

The global variable ‘dba’ is used to access the AdminAPI functionality and
perform DBA operations. It is used for managing MySQL InnoDB clusters.

The following properties are currently supported.

– verbose Enables verbose mode on the Dba operations.

The following functions are currently supported.

– checkInstanceConfiguration Validates an instance for MySQL InnoDB
Cluster usage.
– configureInstance Validates and configures an instance for
MySQL InnoDB Cluster usage.
– configureLocalInstance Validates and configures a local instance
for MySQL InnoDB Cluster usage.
– createCluster Creates a MySQL InnoDB cluster.
– deleteSandboxInstance Deletes an existing MySQL Server instance on
localhost.
– deploySandboxInstance Creates a new MySQL Server instance on
localhost.
– dropMetadataSchema Drops the Metadata Schema.
– getCluster Retrieves a cluster from the Metadata Store.
– help Provides help about this class and it’s
members
– killSandboxInstance Kills a running MySQL Server instance on
localhost.
– rebootClusterFromCompleteOutage Brings a cluster back ONLINE when all
members are OFFLINE.
– startSandboxInstance Starts an existing MySQL Server instance on
localhost.
– stopSandboxInstance Stops a running MySQL Server instance on
localhost.

For more help on a specific function use: dba.help(”)

e.g. dba.help(‘deploySandboxInstance’)

It’s also possible to get more info as explained above, let’s try:
MySQL JS> dba.help(‘configureInstance’)

Validates and configures an instance for MySQL InnoDB Cluster usage.

SYNTAX

.configureInstance([instance][, options])

WHERE

instance: An instance definition.
options: Additional options for the operation.

RETURNS

A descriptive text of the operation result.

DESCRIPTION

This function auto-configures the instance for InnoDB Cluster usage.If the
target instance already belongs to an InnoDB Cluster it errors out.

The instance definition is the connection data for the instance.

For additional information on connection data use \? connection.

Only TCP/IP connections are allowed for this function.

The options dictionary may contain the following options:

– mycnfPath: The path to the MySQL configuration file of the instance.
– outputMycnfPath: Alternative output path to write the MySQL configuration
file of the instance.
– password: The password to be used on the connection.
– clusterAdmin: The name of the InnoDB cluster administrator user to be
created. The supported format is the standard MySQL account name format.
– clusterAdminPassword: The password for the InnoDB cluster administrator
account.
– clearReadOnly: boolean value used to confirm that super_read_only must be
disabled.
– interactive: boolean value used to disable the wizards in the command
execution, i.e. prompts are not provided to the user and confirmation
prompts are not shown.
– restart: boolean value used to indicate that a remote restart of the target
instance should be performed to finalize the operation.

The connection password may be contained on the instance definition, however,
it can be overwritten if it is specified on the options.

This function reviews the instance configuration to identify if it is valid for
usage in group replication and cluster. An exception is thrown if not.

If the instance was not valid for InnoDB Cluster and interaction is enabled,
before configuring the instance a prompt to confirm the changes is presented
and a table with the following information:

– Variable: the invalid configuration variable.
– Current Value: the current value for the invalid configuration variable.
– Required Value: the required value for the configuration variable.
– Required Value: the required value for the configuration variable.

EXCEPTIONS

ArgumentError in the following scenarios:

– If ‘interactive’ is disabled and the instance parameter is empty.
– If the instance definition is invalid.
– If the instance definition is a connection dictionary but empty.
– If the instance definition is a connection dictionary but any option is
invalid.
– If ‘interactive’ mode is disabled and the instance definition is missing
the password.
– If ‘interactive’ mode is enabled and the provided password is empty.

RuntimeError in the following scenarios:

– If the configuration file path is required but not provided or wrong.
– If the instance accounts are invalid.
– If the instance is offline.
– If the instance is already part of a Replication Group.
– If the instance is already part of an InnoDB Cluster.
– If the given instance cannot be used for Group Replication.

For this setup, we will use MySQL 8.0.11 servers having one user created on each one (clusteradmin) like this:
create user ‘clusteradmin’@’%’ identified by ‘fred’;
grant all privileges on *.* to ‘clusteradmin’@’%’ with grant option;
reset master;
This user can be created from your GUI interface if you are using nodes in the cloud. But we can also create such user in the Shell as explained in the next chapter.
If you want to grant only the required privileges the list is availble here :
GRANT SELECT ON mysql_innodb_cluster_metadata.* TO your_user@’%’;
GRANT SELECT ON performance_schema.global_status TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_applier_configuration TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_applier_status TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_connection_configuration TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_connection_status TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_group_member_stats TO your_user@’%’;
GRANT SELECT ON performance_schema.replication_group_members TO your_user@’%’;
GRANT SELECT ON performance_schema.threads TO your_user@’%’ WITH GRANT OPTION
Configure the servers
The first step will be to configure the MySQL servers (mysql1, mysql2 and myslq3) that will take part in the cluster. By default, a new instance of MySQL that has been just installed, needs some configuration changes to be able to create or join an InnoDB Cluster.
Even if this is not required, it’s recommended to verify the configuration of the instances. This needs to be done against running mysqld. From the Shell we will verify the configuration of all the nodes using dba.checkInstanceConfiguration():
MySQL JS> dba.checkInstanceConfiguration(‘clusteradmin@mysql1’)
Please provide the password for ‘clusteradmin@mysql1’: ****
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster…

This instance reports its own address as mysql1
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements…
No incompatible tables detected

Checking instance configuration…

Some configuration options need to be fixed:
+————————–+—————+—————-+————————————————–+
| Variable | Current Value | Required Value | Note |
+————————–+—————+—————-+————————————————–+
| binlog_checksum | CRC32 | NONE | Update the server variable |
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
+————————–+—————+—————-+————————————————–+

Please use the dba.configureInstance() command to repair these issues.

{
“config_errors”: [
{
“action”: “server_update”,
“current”: “CRC32”,
“option”: “binlog_checksum”,
“required”: “NONE”
},
{
“action”: “restart”,
“current”: “OFF”,
“option”: “enforce_gtid_consistency”,
“required”: “ON”
},
{
“action”: “restart”,
“current”: “OFF”,
“option”: “gtid_mode”,
“required”: “ON”
}
],
“errors”: [],
“status”: “error”
}

As we can see, 3 settings must be changed. GTID must be enabled which is expected as Group Replication is based on GTID. We also need to disable the checksum of binlogs as GCS/XCOM already use its internal checksum of events.
We can also notice that some changes require a restart of mysqld.
You can also verify the two other nodes:
MySQL JS> dba.checkInstanceConfiguration(‘clusteradmin@mysql2’)

MySQL JS> dba.checkInstanceConfiguration(‘clusteradmin@mysql3’)

Now we need to make those configuration changes and restart the mysqld instances. As we use MySQL 8.0, we can configure and restart mysqld remotely !
Let’s configure and restart mysql1 using dba.configureInstance() but in case you don’t have yet create a user to manage your cluster, it’s also possible to do it now !
We have then 2 options, configure the instance creating the user:
MySQL JS> dba.configureInstance(‘mysql1’,{clusterAdmin: ‘newclusteradmin@%’,clusterAdminPassword: ‘mypassword’})

Or using a user we already created earlier:
MySQL JS> dba.configureInstance(‘clusteradmin@mysql1’)
Please provide the password for ‘clusteradmin@mysql1’: ****
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster…

This instance reports its own address as mysql1
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Some configuration options need to be fixed:
+————————–+—————+—————-+————————————————–+
| Variable | Current Value | Required Value | Note |
+————————–+—————+—————-+————————————————–+
| binlog_checksum | CRC32 | NONE | Update the server variable |
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
+————————–+—————+—————-+————————————————–+

Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Configuring instance…
The instance ‘mysql1:3306’ was configured for cluster usage.
Restarting MySQL…
MySQL server at mysql1:3306 was restarted.

We can verify in the datadir the modification made by the server on its configuration:
# cat mysqld-auto.cnf | jq
{
“Version”: 1,
“mysql_server”: {
“mysql_server_static_options”: {
“binlog_checksum”: {
“Value”: “NONE”,
“Metadata”: {
“Timestamp”: 1530087517651637,
“User”: “clusteradmin”,
“Host”: “mysql1”
}
},
“enforce_gtid_consistency”: {
“Value”: “ON”,
“Metadata”: {
“Timestamp”: 1530087516620027,
“User”: “clusteradmin”,
“Host”: “mysql1”
}
},
“gtid_mode”: {
“Value”: “ON”,
“Metadata”: {
“Timestamp”: 1530087517697838,
“User”: “clusteradmin”,
“Host”: “mysql1”
}
}
}
}
}

We can also configure mysql2 and mysql3 using the same MySQL Shell session and we can also avoid the prompt:
MySQL JS> dba.configureInstance(‘clusteradmin:fred@mysql2’,{‘restart’: true, ‘interactive’: false})
Configuring MySQL instance at mysql2:3306 for use in an InnoDB cluster…

This instance reports its own address as mysql2
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Some configuration options need to be fixed:
+————————–+—————+—————-+————————————————–+
| Variable | Current Value | Required Value | Note |
+————————–+—————+—————-+————————————————–+
| binlog_checksum | CRC32 | NONE | Update the server variable |
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
+————————–+—————+—————-+————————————————–+

Configuring instance…
The instance ‘mysql2:3306’ was configured for cluster usage.
Restarting MySQL…
MySQL server at mysql2:3306 was restarted.

MySQL JS> dba.configureInstance(‘clusteradmin:fred@mysql2’,{‘restart’: true, ‘interactive’: false})

After the restart of the instances, we can of course verify the configuration again:
MySQL JS> dba.checkInstanceConfiguration(‘clusteradmin@mysql1’)
Please provide the password for ‘clusteradmin@mysql1’: ****
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster…

This instance reports its own address as mysql1
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements…
No incompatible tables detected

Checking instance configuration…
Instance configuration is compatible with InnoDB cluster

The instance ‘mysql1:3306’ is valid for InnoDB cluster usage.

{
“status”: “ok”
}

This is perfect, we can now create our MySQL InnoDB Cluster !
Cluster creation
To create the cluster, we need to connect to one of the 3 nodes in the Shell and use this connection to create the cluster using dba.createCluster() method that returns a cluster object. It’s also at this time that we can set if we want to use a Single-Primary, default or a Multi-Primary cluster.
Let’s create our Single-Primary Cluster using mysql1:
MySQL JS> \c clusteradmin@mysql1
Creating a session to ‘clusteradmin@mysql1’
Enter password: ****
Fetching schema names for autocompletion… Press ^C to stop.
Your MySQL connection id is 10 (X protocol)
Server version: 8.0.11 MySQL Community Server – GPL
No default schema selected; type \use to set one.

MySQL [mysql1+ ssl] JS> cluster = dba.createCluster(‘lefredCluster’)
A new InnoDB cluster will be created on instance ‘clusteradmin@mysql1:3306’.

Validating instance at mysql1:3306…

This instance reports its own address as mysql1

Instance configuration is suitable.
Creating InnoDB cluster ‘lefredCluster’ on ‘clusteradmin@mysql1:3306’…
Adding Seed Instance…

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

As you can see, the returned object is now in the variable ‘cluster’. In case you forgot to set a variable like this, you can also do it later using the dba.getCluster() method:
MySQL [mysql1+ ssl] JS> cluster = dba.getCluster()

As for dba object, the cluster object has also a help() method available:
MySQL [mysql1+ ssl] JS> cluster.help()

The cluster object is the entry point to manage and monitor a MySQL InnoDB
cluster.

A cluster is a set of MySQLd Instances which holds the user’s data.

It provides high-availability and scalability for the user’s data.

The following properties are currently supported.

– name Cluster name.

The following functions are currently supported.

– addInstance Adds an Instance to the cluster.
– checkInstanceState Verifies the instance gtid state in relation
with the cluster.
– describe Describe the structure of the cluster.
– disconnect Disconnects all internal sessions used by the
cluster object.
– dissolve Dissolves the cluster.
– forceQuorumUsingPartitionOf Restores the cluster from quorum loss.
– getName Retrieves the name of the cluster.
– help Provides help about this class and it’s members
– rejoinInstance Rejoins an Instance to the cluster.
– removeInstance Removes an Instance from the cluster.
– rescan Rescans the cluster.
– status Describe the status of the cluster.

For more help on a specific function use: cluster.help(”)

e.g. cluster.help(‘addInstance’)

We can verify our cluster using the status() method:
{
“clusterName”: “lefredCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “mysql1:3306”,
“ssl”: “REQUIRED”,
“status”: “OK_NO_TOLERANCE”,
“statusText”: “Cluster is NOT tolerant to any failures.”,
“topology”: {
“mysql1:3306”: {
“address”: “mysql1:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
},
“groupInformationSourceMember”: “mysql://clusteradmin@mysql1:3306”
}

We can now add a new instance to cluster. But first, we can check the compatibility of the dataset (check if the new node doesn’t have any transactions, gtid, that are unknown in the cluster). We will use the cluster.checkInstanceState() method to achieve this task:
MySQL [mysql1+ ssl] JS> cluster.checkInstanceState(‘clusteradmin@mysql2’)
Please provide the password for ‘clusteradmin@mysql2’: ****
Analyzing the instance replication state…

The instance ‘clusteradmin@mysql2’ is valid for the cluster.
The instance is new to Group Replication.

{
“reason”: “new”,
“state”: “ok”
}
The state reported can be one of the following (we will see another example in the next chapter):

new: if the instance doesn’t have any transactions

recoverable: if the instance executed GTIDs are not conflicting with the executed GTIDs of the cluster instances

diverged: if the instance executed GTIDs diverged with the executed GTIDs of the cluster instances

lost_transactions: if the instance has more executed GTIDs than the executed GTIDs of the cluster instances

So for now, we can see that there are no problem and that the instance can be added, let’s do so using cluster.addInstance():
MySQL [mysql1+ ssl] JS> cluster.addInstance(‘clusteradmin@mysql2:3306’)
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for ‘clusteradmin@mysql2:3306’: ****
Adding instance to the cluster …

Validating instance at mysql2:3306…

This instance reports its own address as mysql2

Instance configuration is suitable.
The instance ‘clusteradmin@mysql2:3306’ was successfully added to the cluster

You could notice that the port is required ! We can verify the cluster using status() again:
MySQL [mysql1+ ssl] JS> cluster.status()
{
“clusterName”: “lefredCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “mysql1:3306”,
“ssl”: “REQUIRED”,
“status”: “OK_NO_TOLERANCE”,
“statusText”: “Cluster is NOT tolerant to any failures.”,
“topology”: {
“mysql1:3306”: {
“address”: “mysql1:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“mysql2:3306”: {
“address”: “mysql2:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
},
“groupInformationSourceMember”: “mysql://clusteradmin@mysql1:3306”
}

Wrong Initial State
I’ve added a transaction in mysql3 to show what happens when there is an error:
MySQL [mysql1+ ssl] JS> cluster.checkInstanceState(‘clusteradmin@mysql3’)
Please provide the password for ‘clusteradmin@mysql3’: ****
Analyzing the instance replication state…

The instance ‘clusteradmin@mysql3’ is invalid for the cluster.
The instance contains additional transactions in relation to the cluster.

{
“reason”: “diverged”,
“state”: “error”
}

Router
The router configuration is very easy. The bootstrap option can be used and it will configure itself:
[root@mysql1 mysqlrouter]# mysqlrouter –user mysqlrouter –bootstrap clusteradmin@mysql1
Please enter MySQL password for clusteradmin:

Bootstrapping system MySQL Router instance…
MySQL Router has now been configured for the InnoDB cluster ‘lefredCluster’.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster ‘lefredCluster’:
– Read/Write Connections: localhost:6446
– Read/Only Connections: localhost:6447
X protocol connections to cluster ‘lefredCluster’:
– Read/Write Connections: localhost:64460
– Read/Only Connections: localhost:64470

Of course, don’t forget to start MySQL Router:
# systemctl start mysqlrouter
# systemctl status mysqlrouter
● mysqlrouter.service – MySQL Router
Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2018-06-27 09:57:42 UTC; 1s ago
Main PID: 14955 (main)
CGroup: /system.slice/mysqlrouter.service
└─14955 /usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

Jun 27 09:57:42 mysql1 systemd[1]: Started MySQL Router.
Jun 27 09:57:42 mysql1 systemd[1]: Starting MySQL Router…

Don’t forget to use latest mysql-router 8.x even with 5.7 !
Links to the MySQL Shell APIs documentation:

javascript
python

 

MySQL Shell for MySQL 8.0: your best friends in the cloud !

MySQL 8.0.11 seems to be around the corner and the new MySQL Shell will take advantage of all the new improvements made in MySQL 8.0 like SET PERSIST, RESTART, … see this previous post.
In the video below, I show you how easy it’s to deploy a MySQL InnoDB Cluster using the Shell that connects remotely to all the instances:

I really think the Shell is an amazing tool that will make MySQL DBA’s life easier
I used Oracle cloud for the demo, I invite you to try it, you get a USD 300 free credits for 30 days.

MySQL 8.0 on Intel Optane SSDs

If you are interested in MySQL Performance and you are looking for an optimal storage, you should have a look at this presentation by Frank Ober where you will be able to see how MySQL behaves on Intel Optane SSDs:

This session was recorded during the Silicon Valley MySQL Group meetup of last March 28th.
You will also find an overview of the tools you can use to test your environment.
Thank you to Intel for testing MySQL 8.0 and having collaborated with Dimitri.
Dimitri will also explain more about MySQL on  Intel Optane during his presentation at Percona Live in two weeks.

MySQL 8.0: Statements Latency Histograms

At the end we all agree that what really matters is the Query Response Time, isn’t it ?
MySQL 8.0 engineers have added more visibility in the summary tables as explained in this previous post. But to add more details, now the Performance_Schema also collects the statements latency and provide their distribution thanks to the collected histogram data.
Two tables have been added to Performance_Schema:

events_statements_histogram_by_digest: details about latency related to schema and query digest

events_statements_histogram_global: global latency summary across all schemas and queries

Let’s have a look at what we can see:
mysql> SELECT t1.SCHEMA_NAME, CONCAT(ROUND(BUCKET_QUANTILE*100,2),”% under “,
BUCKET_TIMER_HIGH/1000000000,” milliseconds”) fact,
LEFT(QUERY_SAMPLE_TEXT,64) as QUERY_SAMPLE, t1.DIGEST,
COUNT(t1.DIGEST)
OVER(PARTITION BY t1.DIGEST) as TOT
FROM events_statements_histogram_by_digest t1
JOIN events_statements_summary_by_digest t2
ON t2.DIGEST = t1.DIGEST
WHERE COUNT_BUCKET >1
ORDER BY t1.DIGEST, BUCKET_TIMER_HIGH DESC
LIMIT 10\G
*************************** 1. row ***************************
SCHEMA_NAME: sbtest
fact: 86.84% under 100.0000 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 2. row ***************************
SCHEMA_NAME: sbtest
fact: 81.58% under 95.4993 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 3. row ***************************
SCHEMA_NAME: sbtest
fact: 73.68% under 87.0964 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 4. row ***************************
SCHEMA_NAME: sbtest
fact: 68.42% under 83.1764 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 5. row ***************************
SCHEMA_NAME: sbtest
fact: 63.16% under 75.8578 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 6. row ***************************
SCHEMA_NAME: sbtest
fact: 57.89% under 63.0957 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 7. row ***************************
SCHEMA_NAME: sbtest
fact: 47.37% under 50.1187 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 8. row ***************************
SCHEMA_NAME: sbtest
fact: 39.47% under 45.7088 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 9. row ***************************
SCHEMA_NAME: sbtest
fact: 26.32% under 39.8107 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
*************************** 10. row ***************************
SCHEMA_NAME: sbtest
fact: 18.42% under 36.3078 milliseconds
QUERY_SAMPLE: INSERT INTO sbtest1(k, c, pad) VALUES(49929, ‘83868641912-287739
DIGEST: 0b515c3fcc7c2095d2b2dccf0040f79254a6fabcf96c7bb9bd597398abba69fb
TOT: 10
10 rows in set (0.02 sec)

The LIMIT should match the amount of TOT.
We can see (row 7), that only 47.37% of this query is executed in 50ms or less and only 18.42% are executed in less than 36ms.
It’s also possible to illustrate those results like this:
mysql> SELECT CONCAT(‘<‘,ROUND(BUCKET_TIMER_HIGH/1000000000,2),’ms’) as ‘QRT’,
CONCAT(RPAD(”,ROUND(BUCKET_QUANTILE*100),’*’),ROUND(BUCKET_QUANTILE*100,2),”%”) bar,
COUNT(t1.DIGEST) OVER(PARTITION BY t1.DIGEST) as TOT
FROM events_statements_histogram_by_digest t1
JOIN events_statements_summary_by_digest t2
ON t2.DIGEST = t1.DIGEST
WHERE COUNT_BUCKET >1
ORDER BY t1.DIGEST, BUCKET_TIMER_HIGH DESC
LIMIT 10;
+———–+———————————————————————————————–+—–+
| QRT | bar | TOT |
+———–+———————————————————————————————–+—–+
| <100.00ms | ***************************************************************************************86.84% | 10 |
| <95.50ms | **********************************************************************************81.58% | 10 |
| <87.10ms | **************************************************************************73.68% | 10 |
| <83.18ms | ********************************************************************68.42% | 10 |
| <75.86ms | ***************************************************************63.16% | 10 |
| <63.10ms | **********************************************************57.89% | 10 |
| <50.12ms | ***********************************************47.37% | 10 |
| <45.71ms | ***************************************39.47% | 10 |
| <39.81ms | **************************26.32% | 10 |
| <36.31ms | ******************18.42% | 10 |
+———–+———————————————————————————————–+—–+
10 rows in set (0.02 sec)

Additionally to these tables, in SYS schema, we have added a procedure illustrating the latency histograms too, it’s called: ps_statement_avg_latency_histogram()
mysql> CALL sys.ps_statement_avg_latency_histogram()\G
*************************** 1. row ***************************
Performance Schema Statement Digest Average Latency Histogram:

. = 1 unit
* = 2 units
# = 3 units

(0 – 348ms) 64 | ********************************
(348 – 696ms) 7 | …….
(696 – 1044ms) 2 | ..
(1044 – 1393ms) 2 | ..
(1393 – 1741ms) 1 | .
(1741 – 2089ms) 0 |
(2089 – 2437ms) 0 |
(2437 – 2785ms) 0 |
(2785 – 3133ms) 0 |
(3133 – 3481ms) 1 | .
(3481 – 3829ms) 0 |
(3829 – 4178ms) 0 |
(4178 – 4526ms) 0 |
(4526 – 4874ms) 0 |
(4874 – 5222ms) 0 |
(5222 – 5570ms) 0 |

Total Statements: 78; Buckets: 16; Bucket Size: 348 ms;

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

As you can see, MySQL 8.0 added more visibility in many domains to analyze and understand better your workload.
 

Migrating to MySQL 8.0 without breaking old application

Recently I blogged about the new default authentication plugin in MySQL 8.0 and I got some comments complaining that this new authentication plugin is breaking half of applications.
So first of all, if you are using an old connector or a connector (like the one for Go) not yet supporting caching_sha2_passwordas authentication plugin, you are still able to use the oldone. If you have created a new user for your application not supporting the new authentication method, you just have to run the following command (please use the right user account):
ALTER USER ‘username’@’hostname’ IDENTIFIED WITH ‘mysql_native_password’ BY ‘password’;
Let’s got back to the blog post now.
Situation
The exercise of this blog consists in the migration of the MySQL server 5.5.59 used by Druapl 6.2  to MySQL 8.0 without migrating to the latest Drupal version.
This is what we have now:
So far so good
MySQL Upgrade
In the MySQL Manual, we propose 2 different strategies:

Logical Upgrade
In-Place Upgrade

The logical method consists of making a logical dump and I restore it, I won’t cover it here. The in-place method is as far as I know the most common one. However, there is something very important that people tend to forget: “Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.”
So know that this is clarified again, let’s continue with our plan.
As we are using 5.5.59, the latest 5.5 version, we don’t need to upgrade the binaries to the latest 5.5, if we would use a older version of 5.5, I would have recommended to upgrade first to the latest version of the same major version too. Our first step is then to put our site in maintenance and then upgrade to the latest 5.6.
MySQL 5.6
# yum update –enablerepo=mysql56-community –disablerepo=mysql57-community mysql-community-server

Updated:
mysql-community-server.x86_64 0:5.6.39-2.el7
Dependency Updated:
mysql-community-client.x86_64 0:5.6.39-2.el7
mysql-community-common.x86_64 0:5.6.39-2.el7
mysql-community-libs.x86_64 0:5.6.39-2.el7
Complete!
Perfect, let’s run the mandatory mysql_upgrade command:
Looking for ‘mysql’ as: mysql
Looking for ‘mysqlcheck’ as: mysqlcheck
Running ‘mysqlcheck with default connection arguments
Running ‘mysqlcheck with default connection arguments
mysql.columns_priv OK
mysql.db OK
mysql.event OK

mysql.time_zone_transition_type OK
mysql.user OK
Running ‘mysql_fix_privilege_tables’…
Running ‘mysqlcheck with default connection arguments
Running ‘mysqlcheck with default connection arguments
drupal.access OK
drupal.actions OK
drupal.actions_aid OK

drupal.watchdog OK
OK
We are good, let’s put back the site online and check the home page again:

MySQL 5.7
OK, let’s move on and upgrade to the latest 5.7:
# yum upgrade mysql-community-server
Dependency Installed:
mysql-community-libs-compat.x86_64 0:5.7.21-1.el7
Updated:
mysql-community-server.x86_64 0:5.7.21-1.el7
Dependency Updated:
mysql-community-client.x86_64 0:5.7.21-1.el7
mysql-community-common.x86_64 0:5.7.21-1.el7
mysql-community-libs.x86_64 0:5.7.21-1.el7
Complete!
# mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK

mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
drupal.access OK
drupal.actions OK

sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
Once again, nothing wrong here, let’s check the website:

MySQL 8.0
It’s time now to upgrade to MySQL 8.0 !
Let’s perform like we did for the previous version:
# yum update –enablerepo=mysql80-community –disablerepo=mysql57-community mysql-community-server
Updated:
mysql-community-server.x86_64 0:8.0.4-0.1.rc.el7
Dependency Updated:
mysql-community-client.x86_64 0:8.0.4-0.1.rc.el7
mysql-community-common.x86_64 0:8.0.4-0.1.rc.el7
mysql-community-libs.x86_64 0:8.0.4-0.1.rc.el7
Complete!
[root@mysql1 drupal-6.2]# mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK

mysql.time_zone_transition_type OK
mysql.user OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
drupal.access OK
drupal.actions OK

drupal.watchdog OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
Again, no problem here ! Let’s see the website:

OH! We have a problem it seems… Did my user’s authentication method changed and my old PHP connector doesn’t support it ?
Let’s verify:
mysql> select Host, User, plugin from mysql.user where User like ‘drup%’;
+——+———+———————–+
| Host | User | plugin |
+——+———+———————–+
| % | drupal | mysql_native_password |
+——+———+———————–+
1 rows in set (0.00 sec)
So that’s not the problem. As I said before, users authentication method is not changed. So this new default doesn’t break old applications…. but my site is still not working…
What’s wrong then ?
In fact, this old Drupal, uses a table name that is now part of the reserved keywords. It’s always advised to verify what are the new keywords reserved for MySQL itself. New features can also mean new keywords sometimes.
I searched in the code and I replaced all the calls to system table by `system` and now the result:

Conclusion
If you are using an old application, no the new authentication plugin doesn’t break your application, until you don’t create a new user for it and not specify an authentication method compatible with your connector. But of course other things, like reserved keywords in this case, can be problematic. This is why an major release upgrade always need to be tested in advance. Not only for schema and syntax compatibility but also for performance as the query execution plan might not be the one you expect event if in most cases the MySQL Optimizer becomes smarter and smarter with the releases and has the support of new features like the histograms.
And don’t forget that the new MySQL Shell includes a new utility checking your current environment to identify possible issues like the one covered in this article.

MySQL 8.0 : New Error Logging

With MySQL 8.0, the error logging subsystem has been redesigned to use the new component architecture.
Thanks to this new redesign, now the log events can be filtered, the output can be sent to multiple destinations (different formats like JSON). All that is controlled by system variables.
This work gives the possibility for a log event to become the raw material for log processing by more modern and automated systems like filebeat for beats, kibana, etc…
Let’s check the default configuration:
mysql> select * from global_variables where VARIABLE_NAME like ‘log_error_%’;
+———————+—————————————-+
| VARIABLE_NAME | VARIABLE_VALUE |
+———————+—————————————-+
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_verbosity | 2 |
+———————+—————————————-+

This means that log events will follow the following flow: first pass through log_filter_internal (built-in filter component), then through log_sink_internal (buit-in log writer component).
To enable a log component you need to use INSTALL COMPONENT command and set the log_error_services global variable as wished. To disable it use  UNINSTALL COMPONENT.
Currently the available log components are in lib/plugins:

component_log_filter_dragnet.so
component_log_sink_json.so
component_log_sink_syseventlog.so
component_log_sink_test.so

LOG OUTPUT
To specify a new output format, you need to use a log writer component (sink). So let’s try to use one.
To load a component , you need its URN. This is ‘file://‘ + the filename without the .so extension. Example, to load the writer to json component, you enable it like this:
mysql> INSTALL COMPONENT ‘file://component_log_sink_json’;
Query OK, 0 rows affected (0.14 sec)
mysql> SET GLOBAL log_error_services = ‘log_filter_internal; log_sink_internal; log_sink_json’;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from global_variables where VARIABLE_NAME like ‘log_error_%’;
+———————+——————————————————-+
| VARIABLE_NAME | VARIABLE_VALUE |
+———————+——————————————————-+
| log_error_services | log_filter_internal; log_sink_internal; log_sink_json |
| log_error_verbosity | 2 |
+———————+——————————————————-+
2 rows in set (0.00 sec)

Now if I generate an entry, I will have the error in the standard error log file and also in a new json file (having the name of the error log, specified in log_error variable) with a number and the .json extension. More info here in the manual.
Let’s have a look at the new entry generated by loading the group_replication plugin in this sandbox instance.
in the traditional error log:
2018-03-13T09:13:45.846708Z 24 [ERROR] [MY-011596] [Repl] Plugin group_replication reported: ‘binlog_checksum should be NONE for Group Replication’
2018-03-13T09:13:45.853491Z 24 [ERROR] [MY-011660] [Repl] Plugin group_replication reported: ‘Unable to start Group Replication on boot’

and in the json error log:
{
“prio”: 1,
“err_code”: 11596,
“subsystem”: “Repl”,
“component”: “plugin:group_replication”,
“SQL_state”: “HY000”,
“source_file”: “plugin.cc”,
“function”: “check_if_server_properly_configured”,
“msg”: “Plugin group_replication reported: ‘binlog_checksum should be NONE for Group Replication'”,
“time”: “2018-03-13T09:13:45.846708Z”,
“thread”: 24,
“err_symbol”: “ER_GRP_RPL_BINLOG_CHECKSUM_SET”,
“label”: “Error”
}
{
“prio”: 1,
“err_code”: 11660,
“subsystem”: “Repl”,
“component”: “plugin:group_replication”,
“SQL_state”: “HY000”,
“source_file”: “plugin.cc”,
“function”: “plugin_group_replication_init”,
“msg”: “Plugin group_replication reported: ‘Unable to start Group Replication on boot'”,
“time”: “2018-03-13T09:13:45.853491Z”,
“thread”: 24,
“err_symbol”: “ER_GRP_RPL_FAILED_TO_START_ON_BOOT”,
“label”: “Error”
}

FILTERS
The new error log service gives you the possibility to use components to filter the events.
The default built-in, log_filter_internal, filters the events only based on their priority, you can specify it using the global variable log_error_verbosity (default is 2).
But there is another component available that allows you to filter on rules that you define: log_filter_dragnet
Let’s try to setup this last one:
mysql> INSTALL COMPONENT ‘file://component_log_filter_dragnet’
mysql> SET GLOBAL log_error_services = ‘log_filter_dragnet; log_sink_internal; log_sink_json’;

mysql> SELECT * from global_variables where VARIABLE_NAME like ‘log_error_ser%’;
+——————–+——————————————————+
| VARIABLE_NAME | VARIABLE_VALUE |
+——————–+——————————————————+
| log_error_services | log_filter_dragnet; log_sink_internal; log_sink_json |
+——————–+——————————————————+

and we can already check the available dragnet rule:
mysql> select * from global_variables where VARIABLE_NAME like ‘dragnet%’\G
*************************** 1. row ***************************
VARIABLE_NAME: dragnet.log_error_filter_rules
VARIABLE_VALUE: IF prio>=INFORMATION THEN drop. IF EXISTS source_line THEN unset source_line.
1 row in set (0.30 sec)

You can find much more information about the dragnet rule language in the manual.
CONCLUSION
As you can see the new Error Logging Service is much more powerful than prior of MySQL 8.0. It’s again another example of a new feature in 8.0.
And for those that want even more or something very specific, don’t forget that now you have also the possibility to modify directly the error logging as you have the possibility to create your own components.

MySQL : command delimiter curiosity – go & ego

Recently, I received a question related to GO as delimter to send a query. The user got some generated statements from a third party tool that looked like this:
/* CreateTable VersionInfo */

CREATE TABLE VersionInfo (Version BIGINT NOT NULL) ENGINE = INNODB

GO

/* VersionMigration migrated */

/* VersionUniqueMigration migrating ========================================== */

/* CreateIndex VersionInfo (Version) */

CREATE UNIQUE INDEX UC_Version ON VersionInfo (Version ASC)

GO

and so on…
To be honest I was lost ?! I never heard about that syntax and I was convinced that this was not valid (and you?)…
But in fact it is ! It’s the long command name for \g and EGO is the one for \G.
You can try help in the client (see the manual) and you will see it:
ego (\G) Send command to mysql server, display result vertically.

go (\g) Send command to mysql server.

I wasn’t aware of this… So I tried of course:
node1 [localhost] {root} ((none)) > select now()
-> go
->

so it failed, it expected ‘;‘, ‘\g‘ or ‘\G‘ to send the command…
Then I found this bug #69534 where the solution was explained: –named-commands. In fact the client needs to be started with this option to enable the long named commands, see the manual. The manual is not that clear as it takes for example \q and quit… but in fact both are working even when –named-commands is not enabled.
Let’s try starting the client with that option to see if it works:
node1 [localhost] {msandbox} ((none)) > select now()
-> go
+———————+
| now() |
+———————+
| 2018-03-09 22:49:04 |
+———————+
1 row in set (0.00 sec)

Wuuhuuu It works \o/
So I learned something about MySQL that was completely NOT new. I don’t know how useful this can be other than for a quiz but at least if you have a tool generating statements like these ones, now you know how to use them even without replacing all GO by ‘;‘.

MySQL 8.0: Listing Roles

As you may already know, MySQL 8.0 is coming with SQL Roles . I’ve already quoted them before in some posts (here and here). Giuseppe Maxia is doing a great job testing and promoting them (thank you for that!). Of course he also made some remarks on things he would have done differently. The main point is about making the difference between users and roles.
In most OpenSource RDBMS, a role is in fact an alias for a user but without a login.
As I always like to say, if there is not solution, there is no problem, isn’t it ?
So let’s have a look at how we could differentiate our roles and list them.
ROLES & USERS Creation
I will first create two different roles and one user. As the syntax is very easy, I think this doesn’t require more explanation.
Firs the roles, one to read the data and one able to write data:
mysql> CREATE ROLE dbt3_reader;
mysql> GRANT SELECT ON dbt3.* to dbt3_reader;

mysql> CREATE ROLE dbt3_writer;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON dbt3.* TO dbt3_writer;

Now the user and I will assign it the dbt3_reader role:
mysql> CREATE USER dbt3_user1 IDENTIFIED BY ‘password’;
mysql> GRANT dbt3_reader TO dbt3_user1;

Listing the ROLES
So now the “real problem”, how could we list only the roles and not the users ?
It’s possible to achieve this with the following query:
mysql> SELECT DISTINCT User ‘Role Name’, if(from_user is NULL,0, 1) Active
FROM mysql.user LEFT JOIN role_edges ON from_user=user
WHERE account_locked=’Y’ AND password_expired=’Y’ AND authentication_string=”;
+————-+——–+
| Role Name | Active |
+————-+——–+
| dbt3_reader | 1 |
| dbt3_writer | 0 |
+————-+——–+
2 rows in set (0.00 sec)

Indeed ROLES are locked accounts, without passwords and expired. It’s also possible to list a user in it, but this means that you removed the password of a user that you have locked and that the password expired…. a bit too much isn’t it ?
On the query above, Active means that there is at least one user having that role assigned.
This query might be a good candidate for a new SYS view.
What is a ROLE ?
But the real question, is “what is a role ?” Because we can also grant a user to another like this:
mysql> CREATE USER dbt3_user2 IDENTIFIED BY ‘password2’;
mysql> GRANT dbt3_user1 TO dbt3_user2;

It can become very complicated… the manual answers this question very well:
A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.
And for better usability, I encourage you to prefix all your roles with ‘r_‘, so db3_reader becomes r_db3_reader.
Then listing the roles becomes much easier.

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569