MySQL Server 8.0.18: Thanks for the Contributions

In my blog series about external contributions to MySQL 8 we have reached version 8.0.18 which was released Monday 14 October 2019. Again the community has contributed to make MySQL better. Thank you.

The contributions to MySQL 8.0.18 includes several patches from Facebook as well as patches from Gillian Gunson, Przemysław Skibiński (Percona), Daniel Black, and Satya Bodapati (also Percona). The contributions are:

  • MySQL now provides more control over the use of compression to minimize the number of bytes sent over connections to the server. Previously, a given connection was either uncompressed or used the zlib compression algorithm. Now, it is also possible to use the zstd algorithm (zstd 1.3), and to select a compression level for zstd connections. The permitted compression algorithms can be configured on the server side, as well as on the connection-origination side for connections by client programs and by servers participating in master/slave replication or Group Replication. For more information, see Connection Compression Control.

    Connection compression using the zstd algorithm requires that the server be built with zstd library support. The new WITH_ZSTD CMake option indicates whether to use the bundled or system zstd library.

    Legacy compression-control parameters, such as the --compress client option, are deprecated and will be removed in a future MySQL version.

    Thanks to Facebook for a contribution on which some of this work was based.
  • The sys.schema_unused_indexes view now filters out unique indexes. Thanks to Gillian Gunson for the contribution. (Bug #24798995, Bug #83257)
  • The new innodb_idle_flush_pct variable permits placing a limit on page flushing during idle periods, which can help extend the life of solid state storage devices. See Limiting Buffer Flushing During Idle Periods.

    Thanks to Facebook for the contribution. (Bug #27147088, Bug #88566)
  • Replication: The heartbeat interval for a replication slave, which is controlled by the MASTER_HEARTBEAT_PERIOD option of the CHANGE MASTER TO statement, can be specified with a resolution in milliseconds. Previously, the master’s binary log dump thread used a granularity of seconds to calculate whether a heartbeat signal should be sent to the slave, causing excessive heartbeat activity in the case of multiple skipped events. To remove this issue, all heartbeat-related calculations by the master and slave are now carried out using a granularity of nanoseconds for precision. Thanks to Facebook for the contribution. (Bug #29363787, Bug #94356)
  • When generating C source from SQL scripts, Some utf8-encoded characters were split across lines. Thanks to Przemysław Skibiński from Percona for the patch. (Bug #30152555, Bug #96449)
  • With strict SQL mode enabled, the STR_TO_DATE() function did not properly handle values with time parts only. Thanks to Daniel Black for the contribution. (Bug #18090591, Bug #71386)
  • InnoDB: A long running ALTER TABLE … ADD INDEX operation with concurrent inserts caused semaphore waits. Thanks to Satya Bodapati from Percona for the patch. (Bug #29008298)

If you have patches you would like to contribute you can do so from MySQL’s GitHub repository (requires signing the Oracle Contributor Agreement).

Thank you for the contributions.

Create MySQL Test Instance with Oracle Cloud Free Tier

Oracle announced this week at Oracle OpenWorld that it has introduced a new cloud offer called Oracle Cloud Free Tier. As the name suggest, it allows you to run a few limited instances in Oracle Cloud for free. I will in this blog show how you can use the free tier to setup a MySQL test instance.

Tip

If you want to read more about Oracle Cloud Free Tier see https://www.oracle.com/cloud/free/ and the FAQ at https://www.oracle.com/cloud/free/faq.html.

The first step is to sign up for the cloud service which you do by opening https://www.oracle.com/cloud/free/ and click on the Start for free button near the top of the page:

Click on Start for free to get started

This will take you through a series of pages where you create your Oracle Cloud account. The steps are straight forward. You will need to provide a valid mobile number and credit card (but no fees are charges provided you stick to the always free services). At the end you are directed to the Oracle Cloud login page:

Oracle Cloud Login Screen
Oracle Cloud Login Screen

Enter the email address and password from the registration process, and you are ready to use create Oracle Cloud services. You will need a compute instance which you create by choosing the Compute Create a VM Instance quick action:

The Oracle Cloud front page after logging in the first time

Notice how there is a label Always Free Eligible which tells you can create instances in the free tier.

On the next screen, you can fill in the details for the instance. You can choose all the default values which will create a VM.Standard.E2.1.Micro virtual machine which is one of the shapes that are included in the free tier. The shape includes 1 OCPU (1 CPU with hyperthreading, so two virtual CPUs) and 1GiB of memory. It will also set everything up for you including the network with ssh access.

To be able to ssh to the instance, you need to add the public ssh key for your ssh key pair. If you do not already have an ssh key, then https://docs.oracle.com/en/cloud/paas/event-hub-cloud/admin-guide/generate-ssh-key-pair-using-puttygen.html has an example of creating one on Microsoft Windows.

Once you click create, a workflow is created and launched. While the workflow is running, the icon in the top left corner is yellow/orange to indicate that the instance is being worked on. Once the workflow has completed, the instance is available and the icon turns green.

You will need the Public IP Address which you can find in the Primary VNIC Information section when viewing the instance details:

With that and your ssh key, you can connect to the instance using the opc user, for example (this assumes you have the private key in OpenSSH format):

shell$ ssh -i ~/.ssh/id_rsa opc@<ip address of vm>

The first step to install MySQL is to install the MySQL yum repository:

[opc@mysql ~]$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpmLoaded plugins: langpacks, ulninfo                                                                  
mysql80-community-release-el7-3.noarch.rpm                                    |  25 kB  00:00:00     
Examining /var/tmp/yum-root-4Yk8Ev/mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch                                                                                     
Marking /var/tmp/yum-root-4Yk8Ev/mysql80-community-release-el7-3.noarch.rpm to be installed          
Resolving Dependencies                                                                               
--> Running transaction check                                                                        
---> Package mysql80-community-release.noarch 0:el7-3 will be installed                              
--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================
 Package                      Arch      Version     Repository                                  Size
=====================================================================================================
Installing:
 mysql80-community-release    noarch    el7-3       /mysql80-community-release-el7-3.noarch     31 k

Transaction Summary
=====================================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-3.noarch                                            1/1
  Verifying  : mysql80-community-release-el7-3.noarch                                            1/1

Installed:
  mysql80-community-release.noarch 0:el7-3

Complete!

Now you can install any of the MySQL products using the yum command. For example, to install MySQL Server, the MySQL client programs, and MySQL Shell:

[opc@mysql ~]$ sudo yum install mysql-community-server mysql-community-client mysql-shell
Loaded plugins: langpacks, ulninfo                                                       
Resolving Dependencies                                                                   
--> Running transaction check                                                            
---> Package mysql-community-client.x86_64 0:8.0.17-1.el7 will be installed              
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.17-1.el7.x86_64                                                                                 
---> Package mysql-community-server.x86_64 0:8.0.17-1.el7 will be installed                          
--> Processing Dependency: mysql-community-common(x86-64) = 8.0.17-1.el7 for package: mysql-community-server-8.0.17-1.el7.x86_64                                                                          
---> Package mysql-shell.x86_64 0:8.0.17-1.el7 will be installed                                     
--> Running transaction check                                                                        
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted                                    
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64  
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64                                                                                      
---> Package mysql-community-common.x86_64 0:8.0.17-1.el7 will be installed                          
---> Package mysql-community-libs.x86_64 0:8.0.17-1.el7 will be obsoleting                           
--> Running transaction check                                                                        
---> Package mysql-community-libs-compat.x86_64 0:8.0.17-1.el7 will be obsoleting                    
--> Finished Dependency Resolution                                                                   

Dependencies Resolved

=====================================================================================================
 Package                           Arch         Version            Repository                   Size 
=====================================================================================================
Installing:                                                                                          
 mysql-community-client            x86_64       8.0.17-1.el7       mysql80-community            32 M 
 mysql-community-libs              x86_64       8.0.17-1.el7       mysql80-community           3.0 M 
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7                                                   
 mysql-community-libs-compat       x86_64       8.0.17-1.el7       mysql80-community           2.1 M 
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7                                                   
 mysql-community-server            x86_64       8.0.17-1.el7       mysql80-community           415 M 
 mysql-shell                       x86_64       8.0.17-1.el7       mysql-tools-community        15 M 
Installing for dependencies:                                                                         
 mysql-community-common            x86_64       8.0.17-1.el7       mysql80-community           589 k 

Transaction Summary
=====================================================================================================
Install  5 Packages (+1 Dependent package)                                                           

Total download size: 468 M
Is this ok [y/d/N]: y     
Downloading packages:     
warning: /var/cache/yum/x86_64/7Server/mysql80-community/packages/mysql-community-common-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY                                    
Public key for mysql-community-common-8.0.17-1.el7.x86_64.rpm is not installed                       
(1/6): mysql-community-common-8.0.17-1.el7.x86_64.rpm                         | 589 kB  00:00:00     
(2/6): mysql-community-libs-8.0.17-1.el7.x86_64.rpm                           | 3.0 MB  00:00:01     
(3/6): mysql-community-libs-compat-8.0.17-1.el7.x86_64.rpm                    | 2.1 MB  00:00:00     
Public key for mysql-shell-8.0.17-1.el7.x86_64.rpm is not installed] 5.0 MB/s |  44 MB  00:01:25 ETA 
(4/6): mysql-shell-8.0.17-1.el7.x86_64.rpm                                    |  15 MB  00:00:06     
(5/6): mysql-community-client-8.0.17-1.el7.x86_64.rpm                         |  32 MB  00:00:13     
(6/6): mysql-community-server-8.0.17-1.el7.x86_64.rpm                         | 415 MB  00:01:29     
-----------------------------------------------------------------------------------------------------
Total                                                                5.1 MB/s | 468 MB  00:01:31     
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql                                        
Importing GPG key 0x5072E1F5:                                                                        
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"                               
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5                                      
 Package    : mysql80-community-release-el7-3.noarch (@/mysql80-community-release-el7-3.noarch)      
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql                                                     
Is this ok [y/N]: y                                                                                  
Running transaction check                                                                            
Running transaction test                                                                             
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-8.0.17-1.el7.x86_64                                        1/7
  Installing : mysql-community-libs-8.0.17-1.el7.x86_64                                          2/7
  Installing : mysql-community-client-8.0.17-1.el7.x86_64                                        3/7
  Installing : mysql-community-server-8.0.17-1.el7.x86_64                                        4/7
  Installing : mysql-community-libs-compat-8.0.17-1.el7.x86_64                                   5/7
  Installing : mysql-shell-8.0.17-1.el7.x86_64                                                   6/7
  Erasing    : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                7/7
  Verifying  : mysql-community-libs-8.0.17-1.el7.x86_64                                          1/7
  Verifying  : mysql-community-server-8.0.17-1.el7.x86_64                                        2/7
  Verifying  : mysql-community-common-8.0.17-1.el7.x86_64                                        3/7
  Verifying  : mysql-community-client-8.0.17-1.el7.x86_64                                        4/7
  Verifying  : mysql-shell-8.0.17-1.el7.x86_64                                                   5/7
  Verifying  : mysql-community-libs-compat-8.0.17-1.el7.x86_64                                   6/7
  Verifying  : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                7/7

Installed:
  mysql-community-client.x86_64 0:8.0.17-1.el7        mysql-community-libs.x86_64 0:8.0.17-1.el7
  mysql-community-libs-compat.x86_64 0:8.0.17-1.el7   mysql-community-server.x86_64 0:8.0.17-1.el7
  mysql-shell.x86_64 0:8.0.17-1.el7

Dependency Installed:
  mysql-community-common.x86_64 0:8.0.17-1.el7

Replaced:
  mariadb-libs.x86_64 1:5.5.64-1.el7

Complete!

There are some dependencies that are pulled in and existing libraries are upgraded.

That is it. All that remains is to start MySQL and set the root password. You start MySQL through systemd like:

[opc@mysql ~]$ sudo systemctl start mysqld

Since it is the first time MySQL is started, the data directory (/var/lib/mysql) is initialized and the root account is created with a random password. You can find the random password in the error log:

[opc@mysql ~]$ sudo grep password /var/log/mysqld.log
2019-09-18T09:59:55.552745Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: i(Y5Nkko>S.t

The password you see will of course be different. Use the password the first time you authenticate, then use the ALTER USER statement to set the new password. For example, using MySQL Shell:

[opc@mysql ~]$ mysqlsh --user=root --sql
Please provide the password for 'root@localhost': ************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): No
MySQL Shell 8.0.17

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 10 (X protocol)
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  SQL > ALTER USER CURRENT_USER() IDENTIFIED BY 'New$secureP@ssw0rd';
Query OK, 0 rows affected (0.0061 sec)

Information

The password validation component is installed by default when installing MySQL using RPMs. This means that the password must be at least eight characters long and include at least one lower case, one upper case, one digit, and one special character.

You are now ready to use MySQL. Have fun.

Tip

Since the free compute instance only has one gigabyte of memory, you may want to reduce the memory footprint of MySQL. Lefred has a blog with some suggestions at https://lefred.be/content/mysql-8-0-memory-consumption-on-small-devices/.

MySQL Server 8.0.17: Thanks for the Contributions

MySQL 8.0.17 was released Monday and it includes great features such as the Clone feature and multi-valued indexes. There are also several nice contributions from the community. These are the changes that this blog is about.

The contributions to MySQL Server 8.0.17 include patches from Facebook, Daniël van Eeden, Mattias Jonsson, and Simon Mudd (all from Booking.com), Daniel Black, Yibo Cai (from Arm Technology), Josh Braden, and Zhou Mengkang. The larger contributions are:

  • The mysql client program now sends os_user and os_sudouser connection attributes, when available, to indicate the name of the operating system user running the program and the value of the SUDO_USER environment variable, respectively. For general information about connection attributes, see Performance Schema Connection Attribute Tables. Thanks to Daniël van Eeden for the contribution on which this feature was based. (Bug #29210935, Bug #93916)
  • The mysqldump option –set-gtid-purged controls whether or not a SET @@GLOBAL.gtid_purged statement is added to the mysqldump output. The statement updates the value of gtid_purged on a server where the dump file is reloaded, to add the GTID set from the source server’s gtid_executed system variable. A new choice –set-gtid-purged=COMMENTED is now available. When this value is set, if GTIDs are enabled on the server you are backing up, SET @@GLOBAL.gtid_purged is added to the output (unless gtid_executed is empty), but it is commented out. This means that the value of gtid_executed is available in the output, but no action is taken automatically when the dump file is reloaded. With COMMENTED, you can control the use of the gtid_executed set manually or through automation. For example, you might prefer to do this if you are migrating data to another server that already has different active databases. Thanks to Facebook for this contribution. (Bug #94332, Bug #29357665)
  • MySQL now uses open(O_TMPFILE) whenever applicable when creating a temporary file that is immediately unlinked. This is more efficient than previously and avoids the small possibility of a race condition. Thanks to Daniel Black for the contribution. (Bug #29215177, Bug #93937)
  • When clients were terminated for inactivity exceeding the wait_timeout value, the message written to the error log was unclear. Now ER_NET_WAIT_ERROR is written, which is more specific about the cause of the problem. Thanks to Mattias Jonsson for the contribution. (Bug #28940167, Bug #93240)
  • InnoDB: Insufficient memory barriers in the rw-lock implementation caused deadlocks on ARM. Thanks to Yibo Cai from Arm Technology for the contribution. (Bug #29508001, Bug #94699)
  • Replication: When events generated by one MySQL server instance were written to the binary log of another instance, the second server implicitly assumed that the first server supported the same number of binary log event types as itself. Where this was not the case, the event header was handled incorrectly. The issue has now been fixed. Thanks to Facebook for the contribution. (Bug #29417234)
  • Replication: When binary logging is enabled on a replication slave, the combination of the –replicate-same-server-id and –log-slave-updates options on the slave can cause infinite loops in replication if the server is part of a circular replication topology. (In MySQL 8.0, binary logging is enabled by default, and slave update logging is the default when binary logging is enabled.) However, the use of global transaction identifiers (GTIDs) prevents this situation by skipping the execution of transactions that have already been applied. The restriction on this combination of options has therefore now been removed when gtid_mode=ON is set. With any other GTID mode, the server still does not start with this combination of options. As a safeguard against creating the problem situation after the server has started, you now cannot change the GTID mode to anything other than ON on a running server that has this combination of options set. Thanks to Facebook for the contribution. (Bug #28782370, Bug #92754)
  • Replication: When a MEMORY table is implicitly deleted on a master following a server restart, the master writes a DELETE statement to the binary log so that slaves also empty the table. This generated event now includes a comment in the binary log so that the reason for the DELETE statement is easy to identify. Thanks to Daniël van Eeden for the contribution. (Bug #29157796, Bug #93771)

There are also a number of smaller patches that has helped improve the comments and messages in the MySQL source code. These are:

  • Bug 29403708 – CONTRIBUTION: FIX TYPO IN AUTHENTICATION METHODS DOCUMENTATION
    Thanks to Daniël van Eeden.
  • Bug 29428435 – CONTRIBUTION: FIX TYPOS IN MYSQLDUMP.CC
    Thanks to Josh Braden.
  • Bug 29262200 – CONTRIBUTION: FIX TYPOS IN COMMENTS FOR COM_XXX COMMANDS
    Thanks to Simon Mudd.
  • Bug 29468128 – CONTRIBUTION: UPDATE HANDLER.CC
    Thanks to Zhou Mengkang.

Thank you for your contributions. Feel free to keep submitting ideas to the MySQL bugs database with ideas how to improve MySQL.

Performance Schema Functions

The sys schema was created to make it easier to use the Performance Schema. This included several functions, for example to convert the picoseconds used by the Performance Schema into human readable strings. In MySQL 8.0.16, three of these functions have been implemented as native functions in MySQL Server.

SELECT FORMAT_BYTES(1073741824)

Why do away with the sys schema functions? There are two reasons: performance and ease of use. The native functions are written in C++ like the rest of the server whereas the sys schema functions were written as stored functions. Function written in C++ are inherently faster than stored functions. Additionally, that the functions are native means you no longer need to prefix them with sys. to tell MySQL where to find the functions.

The table below shows the mapping from the sys schema functions to the new native functions.

sys SchemaNativeDescription
format_bytes()FORMAT_BYTES()Scale bytes, for example 1024 bytes to 1 kiB.
format_time()FORMAT_PICO_TIME()Convert picoseconds to human readable strings.
ps_thread_id()PS_THREAD_ID()
PS_CURRENT_THREAD_ID()
Find the Performance Schema thread ID belonging to a connection. The PS_CURRENT_THREAD_ID() function does not take any arguments and returns – as the name suggest, the thread ID for the connection itself.

A simple example using the new functions is:

mysql> SELECT PS_THREAD_ID(CONNECTION_ID()),
              PS_CURRENT_THREAD_ID(),
              TIMER_WAIT,
              FORMAT_PICO_TIME(TIMER_WAIT),
              SUM(CURRENT_NUMBER_OF_BYTES_USED),
              FORMAT_BYTES(SUM(CURRENT_NUMBER_OF_BYTES_USED))
         FROM events_statements_current
              INNER JOIN memory_summary_by_thread_by_event_name
                    USING (THREAD_ID)
        WHERE THREAD_ID = PS_CURRENT_THREAD_ID()
       GROUP BY THREAD_ID, EVENT_ID\G
*************************** 1. row ***********************
                  PS_THREAD_ID(CONNECTION_ID()): 59
                         PS_CURRENT_THREAD_ID(): 59
                                     TIMER_WAIT: 346074677
                   FORMAT_PICO_TIME(TIMER_WAIT): 346.07 us
              SUM(CURRENT_NUMBER_OF_BYTES_USED): 1423655
FORMAT_BYTES(SUM(CURRENT_NUMBER_OF_BYTES_USED)): 1.36 MiB
1 row in set (0.0011 sec)

The example requires the performance_schema to be the default schema.

As you can see, the PS_THREAD_ID() function with the result of the CONNECTION_ID() (returning the connection ID for the current connection) is the same as using PS_CURRENT_THREAD_ID(). You can of course use the function with any connection ID. If the connection ID does not exist, the function returns NULL.

The FORMAT_PICO_TIME() function returns 346.07 us (u is used instead of here instead of μ for microseconds) instead of 346074677 which is the raw value for TIMER_WAIT (the duration of the query at the time the data is retrieved). Similarly FORMAT_BYTES() converts the bytes to MiB.

If you use the sys.ps_thread_id() or sys.format_bytes() functions, then the first time for each connection, there will be a warning that the function name is the same as for a native function, for example:

mysql> SELECT sys.format_bytes(1423655);
+---------------------------+
| sys.format_bytes(1423655) |
+---------------------------+
| 1.36 MiB                  |
+---------------------------+
1 row in set, 1 warning (0.0008 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1585
Message: This function 'format_bytes' has the same name as a native function
1 row in set (0.0003 sec)

If you get these warnings from your own objects, you should update them to use the new native functions.

That’s it. Hope you like the new functions.

Connector/Python Connection Attributes

MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.

In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.

Example of the built-in connection attributes provided by MySQL Connector/Python
The built-in MySQL Connector/Python connection attributes

Connection Attributes in MySQL Server

The connection attributes are made available in MySQL Server through two tables within the Performance Schema: session_account_connect_attrs and session_connect_attrs. The two tables have the same definition – the only difference is for which connections they show the connection attributes.

The session_account_connect_attrs table includes the attributes for connections using the same user account as for the one querying the table. This is useful if you want to grant permission for a user to check the attributes for their own connections but not for other connections.

On the other hand, session_connect_attrs shows the attributes for all connections. This is useful for the administrator to check the attributes for all users.

Information

It is up to the client what attributes they want to expose and the values they provide. In that sense, you can only trust the attributes to the same extend that you trust the clients to submit correct values.

The tables have four columns:

  • PROCESSLIST_ID: This is the same ID as in SHOW PROCESSLIST or the PROCESSLIST_ID column in performance_schema.threads.
  • ATTR_NAME: This is the name of the attribute, for example _client_name.
  • ATTR_VALUE: This is the value for the attribute, for example mysql-connector-python.
  • ORDINAL_POSITION: The attributes have an order. The ordinal position specifies the position of the attribute. The first attribute for the connection has ordinal position 0, the next 1, and so forth.

The PROCESSLIST_ID and ATTR_NAME together form the primary key of the tables.

Now that you know how to query the table, let’s take a look at how it works with MySQL Connector/Python.

Connector/Python Attributes

There are essentially three different ways to use the connection attributes from MySQL Connector/Python. You can have them disabled. This is the default and means that no attributes will be provided. You can enable them and use the default attributes. And finally, there is support for providing custom attributes. Let’s look at each of the two cases where attributes are enabled.

MySQL Connector/Python Revealed

Book

If you want to learn more about MySQL Connector/Python, then I have written MySQL Connector/Python Revealed published by Apress. The book both covers the traditional Python Database API (PEP 249) and the X DevAPI which is new as of MySQL 8.

The book is available from Apress (print and DRM free ePub+PDF), Amazon (print and Kindle), Barnes & Noble (print), and others.

Enabled with Default Attributes

If you want your Python program to connect to MySQL using the default attributes, all you need to do is to set the connection-attributes option to True. You can do that in one of two ways depending on how you set the connection arguments

If you use a dictionary of arguments, you add connection-attributes as a key with the value set to True:

import mysqlx

connect_args = {
    "host": "127.0.0.1",
    "port": 33060,
    "user": "pyuser",
    "password": "Py@pp4Demo",
    "connection-attributes": True,
};

db = mysqlx.get_session(**connect_args)
p_s = db.get_schema("performance_schema")
attr = p_s.get_table("session_account_connect_attrs")
stmt = attr.select()
stmt.where("PROCESSLIST_ID = CONNECTION_ID()")
stmt.order_by("ORDINAL_POSITION")
result = stmt.execute()

print("+------+-----------------+------------------------+---------+")
print("| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |")
print("+------+-----------------+------------------------+---------+")
fmt = "| {0:4d} | {1:<15s} | {2:<22s} | {3:7d} |"
for row in result.fetch_all():
    print(fmt.format(row[0], row[1], row[2], row[3]))
print("+------+-----------------+------------------------+---------+")

db.close()

The program creates the connection, then queries the performance_schema.session_account_connect_attrs table using the crud methods. Finally, the result is printed (note that the PROCESSLIST_ID and ORDINAL_POSITION columns have had their names shortened in the output to make the output less wide – the process list ID will differ in your output):

+------+-----------------+------------------------+---------+
| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |
+------+-----------------+------------------------+---------+
|   45 | _pid            | 19944                  |       0 |
|   45 | _platform       | x86_64                 |       1 |
|   45 | _os             | Windows-10.0.17134     |       2 |
|   45 | _source_host    | MY-LAPTOP              |       3 |
|   45 | _client_name    | mysql-connector-python |       4 |
|   45 | _client_version | 8.0.16                 |       5 |
|   45 | _client_license | GPL-2.0                |       6 |
+------+-----------------+------------------------+---------+

Notice that all of the attribute names start with an underscore. That means it is a built-in attribute. Attribute names starting with an underscore are reserved and can only be set by MySQL itself.

You can also connect using a URI, in that case the connection is made like the following example:

import mysqlx
import urllib

uri = "mysqlx://{0}:{1}@{2}:{3}/?connection-attributes=True".format(
    "pyuser",
    urllib.parse.quote('Py@pp4Demo', safe=''),
    "127.0.0.1",
    "33060"
)
db = mysqlx.get_session(uri)

The username, password, host, and port are added through the format() function to make the code less wide. The important thing here is the connection-attributes=True. You can also leave out =True as mentioning the connection-attributes option without any value is the same as enabling it.

What do you do, if you want to add your own customer attributes? That is supported as well.

Custom Attributes

This far the connection-attributes option has just been set to True. However, it also supports taking a list or dictionary as the argument. That can be used to set your own custom attributes.

The easiest way to understand this is to see an example:

import mysqlx

attributes = {
    "application": "my_test_app",
    "foo": "bar",
    "foobar": "",
}
connect_args = {
    "host": "127.0.0.1",
    "port": 33060,
    "user": "pyuser",
    "password": "Py@pp4Demo",
    "connection-attributes": attributes,
};

db = mysqlx.get_session(**connect_args)
p_s = db.get_schema("performance_schema")
attr = p_s.get_table("session_account_connect_attrs")
stmt = attr.select()
stmt.where("PROCESSLIST_ID = CONNECTION_ID()")
stmt.order_by("ORDINAL_POSITION")
result = stmt.execute()

print("+------+-----------------+------------------------+---------+")
print("| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |")
print("+------+-----------------+------------------------+---------+")
fmt = "| {0:4d} | {1:<15s} | {2:<22s} | {3:7d} |"
for row in result.fetch_all():
    value = row[2] if row[2] is not None else ""
    print(fmt.format(row[0], row[1], value, row[3]))
print("+------+-----------------+------------------------+---------+")

db.close()

Notice in line 29 that it is checked whether the attribute value is None (NULL in SQL). When the attribute value is an empty string or no value like for the foobar attribute, it is returned as None in the result set.

Alternatively, you can specify the same three connection attributes using a list:

attributes = [
    "application=my_test_app",
    "foo=bar",
    "foobar",
]
connect_args = {
    "host": "127.0.0.1",
    "port": 33060,
    "user": "pyuser",
    "password": "Py@pp4Demo",
    "connection-attributes": attributes,
};

You can also use an URI of course. You need to use the list syntax for that:

uri = "mysqlx://{0}:{1}@{2}:{3}/" \
    + "?connection-attributes=[application=my_test_app,foo=bar,foobar]".format(
    "pyuser",
    urllib.parse.quote('Py@pp4Demo', safe=''),
    "127.0.0.1",
    "33060",
)

These all give the same result (the process list ID will differ):

+------+-----------------+------------------------+---------+
| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |
+------+-----------------+------------------------+---------+
|   74 | _pid            | 20704                  |       0 |
|   74 | _platform       | x86_64                 |       1 |
|   74 | _os             | Windows-10.0.17134     |       2 |
|   74 | _source_host    | MY-LAPTOP              |       3 |
|   74 | _client_name    | mysql-connector-python |       4 |
|   74 | _client_version | 8.0.16                 |       5 |
|   74 | _client_license | GPL-2.0                |       6 |
|   74 | application     | my_test_app            |       7 |
|   74 | foo             | bar                    |       8 |
|   74 | foobar          |                        |       9 |
+------+-----------------+------------------------+---------+

Notice that the built-in attributes are still included and the custom attributes have been added at the end.

That concludes this introduction to connection attributes with the MySQL Connector/Python X DevAPI. I will recommend to enable them by default even if just for the built-in attributes. The attributes can be very handy when debugging issues on the server.

MySQL Server 8.0.16: Thanks for the Contributions

MySQL 8.0.16 was released last week and includes many great features including support for CHECK constraints and upgrades without the need of mysql_upgrade. As usual there are also several contributions from the community. These are the ones, I would like to highlight in this blog to say “thank you for the contributions”.

Facebook has contributed with another two patches and so has Daniel Black. Additionally, there are patches from Yuhui Wang, Wei Zhao, Yan Huang, and Dirkjan Bussink. The contributions are:

  • mysql-test-run.pl now supports the MTR_UNIQUE_IDS_DIR environment variable, which may be set to specify a unique-IDs directory to be used as the common location for all chroot environments by multiple simultaneous mysql-test-run.pl instances. This enables those instances to avoid conflicts when reserving port numbers. Thanks to Facebook for the contribution. (Bug #29221085, Bug #93950)
  • Changes to the all_persisted_variables test. Thanks to the Facebook team for the contribution. (Bug #29013375, Bug #93478)
  • InnoDB: Write-ahead did not work as expected due to an incorrectly initialized variable. Thanks to Yuhui Wang for the contribution. (Bug #29028838, Bug #93442)
  • InnoDB: A Linux AIO handler function failed to check if completed I/O events succeeded. Thanks to Wei Zhao for the contribution. (Bug #27850600, Bug #90402)
  • InnoDB: A function called by a CREATE TABLE thread attempted to access a table object after it was freed by a background thread. Thanks to Yan Huang for the patch. (Bug #27373959, Bug #89126)
  • Replication: If the WAIT_FOR_EXECUTED_GTID_SET() function was used with a timeout value including a fractional part (for example, 1.5), an error in the casting logic meant that the timeout was rounded down to the nearest whole second, and to zero for values less than 1 second (for example, 0.1). The casting logic has now been corrected so that the timeout value is applied as originally specified with no rounding. Thanks to Dirkjan Bussink for the contribution. (Bug #29324564, Bug #94247)
  • Determination of the number of online CPUs available to the mysqld process is now more accurate. Thanks to Daniel Black for the contribution. (Bug #28907677, Bug #93144)
  • Made a comparison in the internal method Item_result::item_cmp_type() more efficient. Our thanks to Daniel Black for the contribution. (Bug #92784, Bug #28796107)

It sometimes happen that we receive a contribution that we really like, but – for one reason or another – choose not to use the patch. One such case is the new feature to allow reconfiguration of the SSL settings at runtime. Thank you Facebook for submitting a contribution for a similar feature even though we ended up not using the patch.

Thank you for your contributions. Feel free to keep submitting ideas to the MySQL bugs database with ideas how to improve MySQL

Install MySQL 8 on Linux with lower_case_table_names = 1

MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is preserved but the operating system by default makes it look like it is case insensitive.

Which convention that is the correct depends on your personal preference and use case. Between case sensitivity and case insensitivity, it basically boils down to whether mydb, MyDB, and MYDB should be the same identifier or three different ones. Since MySQL originally relied on the file system for its data dictionary, the default was to rely on the case sensitivity of the file system. The option lower_case_table_names was introduced to override the behaviour. The most common use is to set lower_case_table_names to 1 on Linux to introduce case insensitive schema and table names.

Dolphin with lower_case_table_names

This blog will first discuss how lower_case_table_names work in MySQL 8 – it is not the same as in earlier versions. Then it will be shown how MySQL 8 can be initialized on Linux to use case insensitive identifiers.

Advice

To use case insensitive identifiers in MySQL 8, the main thing is that you must set lower_case_table_names = 1 in your MySQL configuration file before you initialize the data directory (this happens on the first start when using systemd).

MySQL 8 and lower_case_table_names

In MySQL 8, it is no longer allowed to change the value of the lower_case_table_names option after the data directory has been initialized. This is a safety feature – as described in the reference manual:

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_lower_case_table_names

If you try to start MySQL 8 with a different value of lower_case_table_names than MySQL was initialized, you will get an error like (from the MySQL error log):

2019-04-14T03:57:19.095459Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2019-04-14T03:57:19.097773Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-04-14T03:57:19.098425Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-04-14T03:57:20.784893Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15)  MySQL Community Server - GPL.

So what are the steps to initialize MySQL 8 with lower_case_table_names = 1? Let’s go through them.

Installing MySQL 8 with Case Insensitive Identifier Names

There are several ways to install MySQL 8 on Linux. The steps that will be shown here are:

  1. Install the MySQL repository.
  2. Remove previous installations of MySQL or one of its forks.
  3. Clean the data directory.
  4. Install MySQL 8.
  5. Initialize with lower_case_table_names = 1.

The example commands are from Oracle Linux 7 and also works on Red Hat Enterprise Linux (RHEL) 7 and CentOS 7. The MySQL YUM repository will be used. On other Linux distributions the steps will in general be different, but related.

Tip – Debian and Ubuntu

On Debian and Ubuntu, the MySQL data directory is initialized as part of installation. In MySQL 8.0.17 you can use the debconf-set-selection utility to enable lower_case_table_names (set lower_case_table_names=1) prior to installing MySQL using APT.

1. Install the MySQL Repository

MySQL provides repositories for several Linux distributions for the Community Edition. You can download the repository definition files from MySQL Community Downloads. The files can also be accessed directly. For this example the YUM repository definition will be downloaded using wget and then installed using yum:

shell$ wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
--2019-04-14 12:28:31--  https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
...
HTTP request sent, awaiting response... 200 OK
Length: 25892 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql80-community-release-el7-2.noarch.rpm’

100%[===========================================================>] 25,892      --.-K/s   in 0.01s   

2019-04-14 12:28:33 (1.76 MB/s) - ‘mysql80-community-release-el7-2.noarch.rpm’ saved [25892/25892]

shell$ yum install mysql80-community-release-el7-2.noarch.rpm
...

Dependencies Resolved

=====================================================================================================
 Package                      Arch      Version     Repository                                  Size
=====================================================================================================
Installing:
 mysql80-community-release    noarch    el7-2       /mysql80-community-release-el7-2.noarch     31 k

Transaction Summary
=====================================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
...
  Installing : mysql80-community-release-el7-2.noarch                                            1/1 
  Verifying  : mysql80-community-release-el7-2.noarch                                            1/1 

Installed:
  mysql80-community-release.noarch 0:el7-2                                                           

Complete!

You can now remove the previous installation (if present) and its files.

2. Remove Previous Installations

MySQL or one of its forks may have been installed beforehand. This may even happen as a dependency of another package. You should never have more than one MySQL or fork installed using the package system (yum or rpm on Oracle Linux, RHEL, and CentOS).

Tip

If you need to install different versions of MySQL side by side, use the tarball distributions.

You want to uninstall the existing packages in such a way that you do not remove the programs that depend on it – otherwise you will have to re-install those later. One option is to use the rpm command with the --nodeps option. On Oracle Linux 7, RHEL 7, and CentOS 7 this may look like:

shell$ rpm -e --nodeps mariadb-server-5.5.56-2.el7.x86_64 mariadb-5.5.56-2.el7.x86_64 mariadb-libs-5.5.56-2.el7.x86_64

You can find out which packages are installed using rpm -qa and pass the output through grep to search for the packages of interest.

The next step is to clean out any existing files left behind.

3. Clean the Data Directory

In order to be able to initialize MySQL in step 5., the data directory must be empty. You can choose to use a non-default location for the data directory, or you can re-use the default location which use /var/lib/mysql. If you want to preserve your old data directory, make sure you back it up first!

Warning

Important: If you want to keep your old data files, make sure you back them up before proceeding! All existing files will be permanently lost during this step.

The data directory may have been removed in step 2., but if it has not, you can remove it using the following command:

shell$ rm -rf /var/lib/mysql

Optionally, you can also remove the error log, and if you store files outside the data directory (for example the binary log files or InnoDB log files), you should also remove those. The error log is located in /var/log/; for other files, you will need to check your configuration file (usually /etc/my.cnf).

You are now ready to install the MySQL 8.

4. Install MySQL 8

You can choose between several packages and patch releases (maintenance releases). It is recommended to install the latest patch release. You can see from the release notes which release is the latest. By default, yum will also install the latest release. Which packages you want to install depends on your requirements. The MySQL reference manual includes a list of the available packages with a description of what they include.

In this example, the following packages will be installed:

  • mysql-community-client: Client applications such as the mysql command-line client.
  • mysql-community-common: Some common files for MySQL programs.
  • mysql-community-libs: Shared libraries using the latest version of the API.
  • mysql-community-libs-compat: Shared libraries using the version of the API corresponding to what RPM packages from the Oracle Linux/RHEL/CentOS repositories that depend on MySQL uses. For Oracle Linux 7, RHEL 7, and CentOS 7 this means version 18 (e.g. libmysqlclient.so.18).
  • mysql-community-server: The actual MySQL Server.
  • mysql-shell: MySQL Shell – the second generation command-line client with devops support. This RPM is not listed in the above reference as it is not part of the MySQL Server RPM bundle, however when using the MySQL YUM repository, it can be installed in the same way as the other RPMs.

The yum command thus becomes:

shell$ yum install mysql-community-{client,common,libs,libs-compat,server} mysql-shell
...

Dependencies Resolved

=====================================================================================================
 Package                           Arch         Version            Repository                   Size
=====================================================================================================
Installing:
 mysql-community-client            x86_64       8.0.15-1.el7       mysql80-community            25 M
 mysql-community-common            x86_64       8.0.15-1.el7       mysql80-community           566 k
 mysql-community-libs              x86_64       8.0.15-1.el7       mysql80-community           2.2 M
 mysql-community-libs-compat       x86_64       8.0.15-1.el7       mysql80-community           2.1 M
 mysql-community-server            x86_64       8.0.15-1.el7       mysql80-community           360 M
 mysql-shell                       x86_64       8.0.15-1.el7       mysql-tools-community       9.0 M

Transaction Summary
=====================================================================================================
Install  6 Packages

Total download size: 400 M
Installed size: 1.8 G
Is this ok [y/d/N]: y
Downloading packages:
...
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql80-community-release-el7-2.noarch (installed)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-8.0.15-1.el7.x86_64                                        1/6 
  Installing : mysql-community-libs-8.0.15-1.el7.x86_64                                          2/6 
  Installing : mysql-community-client-8.0.15-1.el7.x86_64                                        3/6 
  Installing : mysql-community-server-8.0.15-1.el7.x86_64                                        4/6 
  Installing : mysql-community-libs-compat-8.0.15-1.el7.x86_64                                   5/6 
  Installing : mysql-shell-8.0.15-1.el7.x86_64                                                   6/6 
  Verifying  : mysql-community-libs-compat-8.0.15-1.el7.x86_64                                   1/6 
  Verifying  : mysql-community-common-8.0.15-1.el7.x86_64                                        2/6 
  Verifying  : mysql-community-server-8.0.15-1.el7.x86_64                                        3/6 
  Verifying  : mysql-shell-8.0.15-1.el7.x86_64                                                   4/6 
  Verifying  : mysql-community-client-8.0.15-1.el7.x86_64                                        5/6 
  Verifying  : mysql-community-libs-8.0.15-1.el7.x86_64                                          6/6 

Installed:
  mysql-community-client.x86_64 0:8.0.15-1.el7   mysql-community-common.x86_64 0:8.0.15-1.el7       
  mysql-community-libs.x86_64 0:8.0.15-1.el7     mysql-community-libs-compat.x86_64 0:8.0.15-1.el7  
  mysql-community-server.x86_64 0:8.0.15-1.el7   mysql-shell.x86_64 0:8.0.15-1.el7                  

Complete!

Notice how the GPG key for the MySQL YUM repository is downloaded, and you are requested to verify it is the correct key. This happens, because it is the first time the repository is used. You can also manually add the GPG key using the instructions in Signature Checking Using GnuPG.

You are now ready to the final step: configuring and starting MySQL Server for the first time.

5. Initialize with lower_case_table_names = 1

As mentioned in the introduction to this blog, you need to ensure that lower_case_table_names is configured when MySQL initializes its data directory. When you use systemd to start MySQL, it will happen automatically when you start MySQL with an empty data directory. This means, you should update the MySQL configuration file with the desired value of lower_case_table_names before the first start.

The default location for the MySQL configuration file is /etc/my.cnf. Open this file with your favourite editor and ensure the line lower_case_table_names = 1 is listed in the [mysqld] group:

[mysqld]
lower_case_table_names = 1

Optionally, you can make other changes to the configuration as needed.

Tip

Other than a few capacity settings such as innodb_buffer_pool_size and the configuration of the InnoDB redo logs, the default configuration is a good starting point for most installations.

Now, you can start MySQL:

shell$ systemctl start mysqld

This will take a little time as it includes initializing the data directory. Once MySQL has started, you can retrieve the temporary password for the root account from the MySQL error log:

shell$ grep 'temporary password' /var/log/mysqld.log 
2019-04-14T03:29:00.122862Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: aLxwMUQr%7C,

The temporary password is randomly generated during the initialization to avoid MySQL being left with a known default password. Use this temporary password to log in and set your permanent root password:

shell$ mysql --user=root --host=localhost --password
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER root@localhost IDENTIFIED BY 'n3w_$tr0ng_P@s$word';
Query OK, 0 rows affected (0.12 sec)

By default for RPM installations, MySQL has the password validation component installed using the MEDIUM strength policy. This means you will need to use a relatively strong password.

You can now verify that MySQL uses case insensitive schema and table identifiers:

mysql> SELECT @@global.lower_case_table_names;
+---------------------------------+
| @@global.lower_case_table_names |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_SOURCE, VARIABLE_PATH
         FROM performance_schema.variables_info
        WHERE VARIABLE_NAME = 'lower_case_table_names';
+-----------------+---------------+
| VARIABLE_SOURCE | VARIABLE_PATH |
+-----------------+---------------+
| GLOBAL          | /etc/my.cnf   |
+-----------------+---------------+
1 row in set (0.01 sec)

mysql> CREATE SCHEMA db1;
Query OK, 1 row affected (0.03 sec)

mysql> use DB1;
Database changed
mysql> CREATE TABLE t1 (id int unsigned NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.47 sec)

mysql> INSERT INTO T1 VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

The query in lines 9-11 queries what the source of the value of the lower_case_table_names option is. This shows that the value of 1 (from the previous query) is picked up from the /etc/my.cnf file. The rest of the queries show how the db1 schema and the db1.t1 table can be accessed both using lower and upper case.

That is it. Now you can use MySQL Server without having to remember which case was used when a schema object was created.

Easy Execution of Dynamic Queries with the sys Schema

When you write stored procedures in MySQL, you sometimes need to generate queries on the fly, for example as you process the result of another query. This is supported using prepared statements. This blog explores how you can take advantage of the sys schema to simplify the use of dynamic queries.

Executing a query using the sys.execute_prepared_stmt() procedure.
Executing a query using the sys.execute_prepared_stmt() procedure.

The sys schema includes several stored procedures and functions as well as views to make the database administrator’s life easier. One of these is the execute_prepared_stmt() procedure which can be used to execute a query given as a string. An example is:

mysql> CALL sys.execute_prepared_stmt(
    ->     'SELECT * FROM world.city WHERE ID = 130');
+-----+--------+-------------+-----------------+------------+
| ID  | Name   | CountryCode | District        | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS         | New South Wales |    3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Let’s take a step back and look at how it works when you execute a dynamic query in MySQL before looking at a more realistic example.

Dynamic Queries and Prepared Statements

A prepared statement is created by providing the query in a string. Optionally, the query can include placeholders, so you can reuse the prepared statement with different parameters. However, here I will look at queries without placeholders.

You prepare the query with the PREPARE statement. The query can be specified either using a literal string or in a user variable. The above example uses a literal string, but when you work with dynamic queries in a stored procedure, you will assign the query to a user variable.

Once you have prepared the query, you execute it using the EXECUTE statement. If you need to execute the query several times, you can do this without preparing it again, however typically that is not the case with dynamic queries.

Finally, you should tell MySQL that you are done with the prepared statement, so the memory used to store the statement can be freed. If you do not do this, you will end up using more memory than necessary, and you can potentially run out of prepared statements (the maximum number allowed can be configured with max_prepared_stmt_count option). You tell MySQL to forget about the prepared statement with the DEALLOCATE PREPARE statement.

If you combine it all, you have the following sequence of steps to generate and execute the dynamic query:

mysql> SET @sql = CONCAT('SELECT COUNT(*) FROM ',
    ->                   sys.quote_identifier('world'),
    ->                   '.',
    ->                   sys.quote_identifier('city'));
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt_count FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt_count;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.01 sec)

mysql> DEALLOCATE PREPARE stmt_count;
Query OK, 0 rows affected (0.00 sec)

In the example, the query is created by concatenating the different parts of the query. The sys.quote_identifier() function is used to quote the schema and table names. Then the query is executed, and finally deallocated.

This is simple enough but can become tedious, if you execute many dynamic queries, and there is always the chance that you forget to include DEALLOCATE PREPARE causing higher memory usage than necessary. That is where sys.execute_prepared_stmt() comes into the picture.

sys.execute_prepared_stmt()

The sys.execute_prepared_stmt() procedure was born when Mark Leith and I were working on the sys schema in preparation to include it into MySQL 5.7. In the end we got tired of the repeated pattern with preparing, executing, and deallocating the statements. So, we decided to create a utility procedure for it.

Building on the above example, consider a stored procedure that takes a schema name as a parameter and goes through all the tables in the schema executing SELECT COUNT(*) to find the number of rows in each table. An example of a procedure that does this is:

DELIMITER $$
CREATE PROCEDURE row_count(IN in_schema varchar(64) CHARSET utf8mb4)
   SQL SECURITY INVOKER
   DETERMINISTIC
   READS SQL DATA
BEGIN
   DECLARE v_table varchar(64) CHARSET utf8mb4;
   DECLARE v_sql varchar(271);
   DECLARE v_done bool DEFAULT FALSE;
   DECLARE c_tables CURSOR FOR
      SELECT TABLE_NAME
        FROM information_schema.TABLES
       WHERE TABLE_SCHEMA = in_schema
             AND TABLE_TYPE = 'BASE TABLE';
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

   OPEN c_tables;
   c_tables: LOOP
      FETCH c_tables INTO v_table;
      IF v_done THEN
         LEAVE c_tables;
      END IF;

      SET v_sql = CONCAT(
         'SELECT ',
         QUOTE(v_table), ' AS TableName, '
         'COUNT(*) AS RowCount FROM ',
         sys.quote_identifier(in_schema),
         '.',
         sys.quote_identifier(v_table)
      );
      CALL sys.execute_prepared_stmt(v_sql);
   END LOOP;
   CLOSE c_tables;
END$$
DELIMITER ;

The most interesting part of the procedure with respect to this discussion is the loop where the information_schema.TABLES view is queried to get all tables in the provided schema. Once the table name has been fetched into the v_table variable, the query is generated in lines 24 to 31 in a very similar way to the previous example. Then the generated query is executed using sys.execute_prepared_stmt() in line 32.

If you are using dynamic queries, I hope you will find sys.execute_prepared_stmt() useful. Happy coding.

Using SQLAlchemy with MySQL 8

I few months ago, I wrote about using the Django framework with MySQL 8. There are also other Python frameworks that are worth considering. In this blog, I will look at using SQLAlchemy with MySQL 8.

In order for you to be able to use MySQL 8 with SQLAlchemy, you need three pieces of software: MySQL Server, MySQL Connector/Python, and SQLAlchemy. I will go through the installations, then I will look at a code example.

Information

The examples in this blog uses MySQL Server 8.0.15, MySQL Connector/Python 8.0.15, and SQLAlchemy 1.2.18. It should be possible to reuse the instructions with other MySQL versions as well, except in older MySQL versions you need to explicitly use the utf8mb4 character set.

If you do not want to read through the whole blog, the main thing for using MySQL and MySQL Connector/Python with SQLAlchemy is to create the engine with dialect set to mysql and the driver to mysqlconnector:

engine = sqlalchemy.create_engine(
    'mysql+mysqlconnector://<user>:<password>@<host>:<port>/<default_db>...')

Installing MySQL Server

There are several ways to install MySQL Server and which one is the best depends on your circumstances and preferences. For the sake of this blog, I will show how MySQL Server can be installed on Oracle Linux/RHEL/CentOS 7 using RPMs and on Microsoft Windows using MySQL Installer. For more options, see the installation chapter in the reference manual. Let’s look at the Linux installation first.

RPM Install on Enterprise Linux

MySQL provides repositories for several Linux distributions including the Oracle Linux/RHEL/CentOS family. This makes it easy to install MySQL. The step to install the repository definition is:

shell$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpmLoaded plugins: langpacks, ulninfo
Repository ol7_developer_EPEL is listed more than once in the configuration
mysql80-community-release-el7-1.noarch.rpm                                     |  25 kB  00:00:00     
Examining /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm: mysql80-community-release-el7-1.noarch
Marking /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
======================================================================================================
 Package                       Arch       Version   Repository                                   Size
======================================================================================================
Installing:
 mysql80-community-release     noarch     el7-1     /mysql80-community-release-el7-1.noarch      31 k
 
Transaction Summary
======================================================================================================
Install  1 Package
 
Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-1.noarch                                             1/1
  Verifying  : mysql80-community-release-el7-1.noarch                                             1/1
 
Installed:
  mysql80-community-release.noarch 0:el7-1
 
Complete!

Now, you can install MySQL Server. There are several RPMs to choose from and which you need depends on which other features you need to use. A common set of RPMs can be installed as:

shell$ sudo yum install mysql-community-server mysql-community-libs \
            mysql-community-libs-compat mysql-community-common mysql-community-client
...

Note

If you have another MySQL installation, it will be upgraded to the latest release (at the time of writing 8.0.15).

On the first start, the data directory will be initialized:

shell$ sudo systemctl start mysqld

To keep a fresh installation secure, a random password has been set for the root user. This can be found from the MySQL error log:

shell$ sudo grep password /var/log/mysqld.log 
2018-11-05T08:05:09.985857Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: KWNfuA!1r:PF

Use this password to connect to MySQL and update the password (please use a strong password):

shell$ mysql --user=root --password
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER root@localhost IDENTIFIED BY 'Kij0@jDi~Faf4';
Query OK, 0 rows affected (0.13 sec)

MySQL is now ready for use. Before continuing, I will show an example of installing MySQL on Microsoft Windows.

Microsoft Windows

On Microsoft Windows an easy way to install MySQL is to use the MySQL Installer. The installer can be downloaded from the MySQL download site. The MySQL Installer can be used to install most MySQL products. If you start MySQL Installer for the first time, you will be taken directly to the screen to choose the products to install; if you already have installed products, you will need to choose to add new products.

On the Select Products and Features screen, choose to install MySQL Server 8.0 (MySQL Installer will list the latest release from the list of available products):

Installing MySQL Server 8.0.13 from MySQL Installer
Installing MySQL Server from MySQL Installer – The screen shot is for 8.0.13, but other than the version number, it is the same for 8.0.15.

Optionally, you can filter the list of products. Feel free to choose other products you want. MySQL Notifier can be useful for starting and stopping MySQL, if you do not plan to have MySQL running at all times. You can also install MySQL Connector/Python this way, however for this blog a different method will be used.

Follow the installation wizard. For this blog, the default choices will work, though during the configuration you may want to ensure Open Windows Firewall ports for network access is unchecked unless you need remote access.

Preparing MySQL Server

While MySQL is now ready to work with SQLAlchemy, you will likely want to do a few more preparation steps. Here creating the MySQL user and schema (database) used by your application will be covered.

An example of creating the user pyuser@localhost and give it all privileges to the sqlalchemy schema and to create the sqlalchemy schema is:

mysql> CREATE USER pyuser@localhost IDENTIFIED BY 'Py@pp4Demo';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON sqlalchemy.* TO pyuser@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE SCHEMA sqlalchemy;
Query OK, 1 row affected (0.01 sec)

This will allow the pyuser user to connect from the same host as MySQL Server is installed by authenticating with the password Py@app4Demo.

Installing MySQL Connector/Python and SQLAlchemy

Both MySQL Connector/Python and SQLAlchemy can be installed in a platform independent way using the pip command. Since Python 2.7 is soon end of life, I will assume Python 3.6 in this blog. (MySQL Connector/Python 8.0.13 and later also supports Python 3.7.)

If you do not have Python 3.6 installed on Oracle Linux/RHEL/CentOS 7, you can easily install it for example from for EPEL repository. Assuming you have configured the EPEL repository, the following steps install Python 3.6, enable pip, and update pip to the latest version:

shell$ yum install python36
shell$ python3.6 -m ensurepip
shell$ python3.6 -m pip install --upgrade pip

You can now use python3.6 to invoke Python 3.6. In the following, replace python with python3.6 if you have installed Python 3.6 in this way.

To install the latest MySQL Connector/Python release (currently 8.0.15):

PS> python -m pip install mysql-connector-python
Collecting mysql-connector-python
  Using cached https://files.pythonhosted.org/packages/31/45/ef8cf013918108f508a1a1bb5539abaff5f78f3a569f7fa30232967713c9/mysql_connector_python-8.0.15-cp36-cp36m-win_amd64.whl

Collecting protobuf>=3.0.0 (from mysql-connector-python)
  Downloading https://files.pythonhosted.org/packages/5d/5c/476f473c2efc0a8d9fd7185e6c08dcbd21c469698e2a80487fa054b8c5ba/protobuf-3.7.0-cp36-cp36m-win_amd64.whl (1.1MB)
    100% |████████████████████████████████| 1.1MB 6.6MB/s
Requirement already satisfied: six>=1.9 in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python)
 (1.11.0)
Requirement already satisfied: setuptools in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-connector-pytho
n) (28.8.0)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.15 protobuf-3.7.0

Similar for installing SQLAlchemy:

PS> python -m pip install SQLAlchemy
Collecting SQLAlchemy
  Downloading https://files.pythonhosted.org/packages/21/ed/7eb53305b43ca51774a458d7c292f3bc7664d7a9bbb5bac4149fa34756b9/SQLAlchemy-1.2.18.tar.gz (5.7MB)
    100% |████████████████████████████████| 5.7MB 3.3MB/s
Installing collected packages: SQLAlchemy
  Running setup.py install for SQLAlchemy ... done

That’s it. Now you are ready to use SQLAlchemy with MySQL Connector/Python 8 and MySQL Server 8.

SQLAlchemy Example

With MySQL and SQLAlchemy installed, it is straight forward to use SQLAlchemy in you application. Here, I will just show a very simple example based on the tutorial in the official SQLAlchemy manual.

The first step is to import the necessary parts of SQLAlchemy. In this example, only sqlalchemy itself and declarative_base from sqlalchemy.ext.declarative are required:

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

The second step is to define MySQL as the engine and that you want to connect using MySQL Connector/Python:

# Define the MySQL engine using MySQL Connector/Python
engine = sqlalchemy.create_engine(
    'mysql+mysqlconnector://pyuser:Py@pp4Demo@localhost:3306/sqlalchemy',
    echo=True)

The definition of MySQL and MySQL Connector Python happens in line 6. mysql defines that you are using MySQL as the database (the dialect), and mysqlconnector tells that you want to use MySQL Connector/Python as the driver. The rest of the line defines the connection options. In this case you have user:password@host:port/default_db. You can add more options if you need it. So, in this case the following options have been set:

  • User: pyuser
  • Password: Py@app4Demo
  • Host: localhost
  • Port: 3306
  • Default database: sqlalchemy

Avoid

Never hard code the connection parameters into your application. Particularly the password is an absolutely no go. It is done here to keep the example simple, but doing so in an actual application makes deployments hard and pose a severe security issue.

See also the documentation of the engine configuration in the SQLAlchemy manual for more information.

The argument echo=True makes SQLAlchemy print each SQL statement it executes. This can be useful when testing.

The third step is to define and create a table – in this example the users table:

# Define and create the table
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(length=50))
    fullname = sqlalchemy.Column(sqlalchemy.String(length=50))
    nickname = sqlalchemy.Column(sqlalchemy.String(length=50))

    def __repr__(self):
        return "<User(name='{0}', fullname='{1}', nickname='{2}')>".format(
                            self.name, self.fullname, self.nickname)

Base.metadata.create_all(engine)

MySQL requires that you specify the maximum number of characters for varchar columns, which is the data type used when specifying sqlalchemy.String(). So, the length argument is passed. (The length argument is also the first argument to sqlalchemy.String(), so you do not need to specify explicitly that it is the length.)

The call to Base.metadata.create_all() tells SQLAlchemy to create the underlying database table, if it does not already exist. Otherwise the existing table will be used.

The fourth step is to add a user. This requires a session. Once the session is created, you can add the user to it:

# Create a session
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
session = Session()

# Add a user
jwk_user = User(name='jesper', fullname='Jesper Wisborg Krogh', nickname='&#x1f42c;')
session.add(jwk_user)
session.commit()

There are two things, I will like you to pay attention to here. When defining the jwk_user in line 31, the nickname is set to be the dolphin emoji. This emoji requires four bytes in UTF-8 (0xF09F90AC). In older versions of MySQL (5.7 and earlier), you would need to explicitly change the character set to handle UTF-8. In MySQL 8, however, the four byte variant of UTF-8 (utf8mb4) is the default, so the dolphin emoji will work out of the box.

Tip

If you are interested in more information about the recommendation of which character set to use in MySQL, I wrote a blog about that last year: Which Character Set Should You Use in MySQL?

The other thing is that in order to persist the new user, you need to call session.commit(). The session works as a transaction here, so the changes will not be persisted until it is explicitly committed.

The fifth – and final – step is to query the data just saved:

# Query the user
our_user = session.query(User).filter_by(name='jesper').first()
print('\nOur User:')
print(our_user)
print('Nick name in hex: {0}'.format(our_user.nickname.encode('utf-8')))

The query finds the first user with the name set to “jesper”. Because the dolphin emoji tends not to be displayed correctly in many shells, the byte sequence in hex is also printed, so it can be confirmed it is indeed the dolphin emoji that was retrieved.

The output of the entire program is:

2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine ROLLBACK
2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(50),
        fullname VARCHAR(50),
        nickname VARCHAR(50),
        PRIMARY KEY (id)
)


2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine COMMIT
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, nickname) VALUES (%(name)s, %(fullname)s, %(nickname)s)
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine {'name': 'jesper', 'fullname': 'Jesper Wisborg Krogh', 'nickname': '&#x1f42c;'}
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine COMMIT
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname
FROM users
WHERE users.name = %(name_1)s
 LIMIT %(param_1)s
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine {'name_1': 'jesper', 'param_1': 1}

Our User:
<User(name='jesper', fullname='Jesper Wisborg Krogh', nickname='&#x1f42c;')>
Nick name in hex: b'\xf0\x9f\x90\xac'

As it can be seen from the last two lines, the dolphin emoji was saved and retrieved correctly.

Complete Example Code

For completeness, here is the entire example program:

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

# Define the MySQL engine using MySQL Connector/Python
engine = sqlalchemy.create_engine(
    'mysql+mysqlconnector://pyuser:Py@pp4Demo@localhost:3306/sqlalchemy',
    echo=True)

# Define and create the table
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(length=50))
    fullname = sqlalchemy.Column(sqlalchemy.String(length=50))
    nickname = sqlalchemy.Column(sqlalchemy.String(length=50))

    def __repr__(self):
        return "<User(name='{0}', fullname='{1}', nickname='{2}')>".format(
                            self.name, self.fullname, self.nickname)

Base.metadata.create_all(engine)

# Create a session
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
session = Session()

# Add a user
jwk_user = User(name='jesper', fullname='Jesper Wisborg Krogh', nickname='&#x1f42c;')
session.add(jwk_user)
session.commit()

# Query the user
our_user = session.query(User).filter_by(name='jesper').first()
print('\nOur User:')
print(our_user)
print('Nick name in hex: {0}'.format(our_user.nickname.encode('utf-8')))

Enjoy using MySQL, MySQL Connector/Python, and SQLAlchemy.

The Format for Timestamps in MySQL Logs

MySQL changed the timestamp format in the log files in MySQL 5.7. Since then, I have a few times seen questions about the new format, and how to change the time zone that is used. Latest in a comment to my blog about log_slow_extra in 8.0.14, where the question was what T and Z in the timestamp (for example 2019-01-31T07:24:06.100447Z) means. In this blog, I will discuss the timestamp format and show you how you can change the time zone used.

Examples of the timestamps from the MySQL error log when restarting MySQL (click to enlarge).

The MySQL 5.7 Change

In MySQL 5.7 it was decided to make two changes to the timestamps affecting the format and time zone:

  • The format was changed to use the ISO 8601 / RFC 3339 format: YYYY-MM-DDThh:mm:ss.uuuuuu plus a tail value to signify the time zone.
  • The time zone used for the timestamps defaults to UTC (in 5.6 it defaulted to system time).

Tip

A couple of useful free resources, if you want to know more about the ISO 8601 / RFC 3339 format is the RFC specification and Wikipedia. The actual ISO 8601 specification can be purchased.

So there are two slightly different ways the timestamps can be displayed. The following two examples are for the default where the timestamp is in UTC and one where it is in UTC+11:00 (Australian Eastern Daylight Time):

2019-03-02T02:53:39.781592Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.15) starting as process 1516

2019-03-02T13:54:17.155810+11:00 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.15) starting as process 2812

Tip

In MySQL 8.0, you can also change the format itself, e.g. to JSON. See the MySQL reference manual on the error log format for for more information.

Changing the Time Zone

How do you change between the UTC and system time zones? You do that using the log_timestamps option which can take one of two values:

  • UTC: Use UTC as the time zone. This is the default.
  • SYSTEM: Use the same time zone as the operating system.

The option affects both the error log, slow query log, and general query log. The the slow and general query logs, though, it is only the case when logging to a file.

From XKCD comic 1179.

That Do T and Z Mean?

Back to the T and Z in the timestamps – what do they mean? The T is simply used as a separator between the data and time part. Think of it as T for Time. The Z means Zulu – another word for using the UTC time zone. That is:

  • T: Separator between the data and time parts.
  • Z: Zulu – the UTC time zone.

Thanks for reading.