Dec
22
2020
--

Redesign of –lock-ddl-per-table in Percona XtraBackup

Redesign of -lock-ddl-per-table in Percona XtraBackup

Redesign of -lock-ddl-per-table in Percona XtraBackupMySQL 5.7, alongside other many improvements, brought bulk load for creating an index (WL#7277 to be specific), which made ADD INDEX operations much faster by disabling redo logging and making the changes directly to tablespace files. This change requires extra care for backup tools. To block DDL statements on an instance, Percona Server for MySQL implemented LOCK TABLES FOR BACKUP. Percona XtraBackup (PXB) uses this lock for the duration of the backup. This lock does not affect DML statements.

MySQL 5.7 doesn’t have an option to block an instance against DDL and to allow all MDL’s. Thus, Percona XtraBackup has also implemented –lock-ddl-per-table. Before we go into other details, let’s understand how –lock-ddl-per-table works up to now:

  1. PXB starts its work by parsing and copying all redo logs after the checkpoint mark.
  2. Fork a dedicated thread that will keep following new redo log entries.
  3. Get a list of all tablespaces that it will require to copy.
  4. Iterate through the list of tablespaces, for each tablespace, it does the following:
    •  Query INFORMATION_SCHEMA.INNODB_SYS_TABLES or in case of a 8.0 server INFORMATION_SCHEMA.INNODB_TABLES check which table or tables belong to that tablespace ID and take an MDL on the underlying table or tables in case of a shared tablespace.
    • Copy the tablespace .ibd file.

This approach works on the promise that if an MLOG_INDEX_LOAD event (Redo log event generated by bulk load operations to notify backup tools that changes to data files have been omitted from redo log) is encountered by the redo follow thread, it’s safe to continue as tablespaces that we have already copied are protected by MDL and the  MLOG_INDEX_LOAD event is for a tablespace that is yet to be copied.

This promise is not always correct and can lead to inconsistent backup; here are a few examples:

Full-Text Index

Full-Text Index has its own tablespace:

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|     1169 | test/FTS_000000000000002e_0000000000000508_INDEX_1 |  1157 |
|     1170 | test/FTS_000000000000002e_0000000000000508_INDEX_2 |  1158 |
|     1171 | test/FTS_000000000000002e_0000000000000508_INDEX_3 |  1159 |
|     1172 | test/FTS_000000000000002e_0000000000000508_INDEX_4 |  1160 |
|     1173 | test/FTS_000000000000002e_0000000000000508_INDEX_5 |  1161 |
|     1174 | test/FTS_000000000000002e_0000000000000508_INDEX_6 |  1162 |
|     1175 | test/FTS_000000000000002e_BEING_DELETED            |  1163 |
|     1176 | test/FTS_000000000000002e_BEING_DELETED_CACHE      |  1164 |
|     1177 | test/FTS_000000000000002e_CONFIG                   |  1165 |
|     1178 | test/FTS_000000000000002e_DELETED                  |  1166 |
|     1179 | test/FTS_000000000000002e_DELETED_CACHE            |  1167 |
+----------+----------------------------------------------------+-------+
11 rows in set (0.01 sec)

With the current approach, PXB will try to run a SELECT on FTS_000000000000002e_0000000000000508_INDEX_1 for example, which is not something we can do. Here the underlying table that this FTS belongs to may or may not have been protected by MDL, which can cause the FTS index to be copied without protection.

Full-Text Index has a defined name schema, on the above situation we can easily extract the table ID by translating the first 16 characters after FTS_ from hex to decimal, which would give us the underlying table that the FTS belongs to:

session 1> CREATE FULLTEXT INDEX full_index on joinit2 (s);
session 2> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE table_id >= 1319;
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|     1321 | test/#sql-ib1320-2000853746                        |  1309 |
|     1322 | test/FTS_0000000000000529_00000000000005b6_INDEX_1 |  1310 |
|     1323 | test/FTS_0000000000000529_00000000000005b6_INDEX_2 |  1311 |
|     1324 | test/FTS_0000000000000529_00000000000005b6_INDEX_3 |  1312 |
|     1325 | test/FTS_0000000000000529_00000000000005b6_INDEX_4 |  1313 |
|     1326 | test/FTS_0000000000000529_00000000000005b6_INDEX_5 |  1314 |
|     1327 | test/FTS_0000000000000529_00000000000005b6_INDEX_6 |  1315 |
|     1328 | test/FTS_0000000000000529_BEING_DELETED            |  1316 |
|     1329 | test/FTS_0000000000000529_BEING_DELETED_CACHE      |  1317 |
|     1330 | test/FTS_0000000000000529_CONFIG                   |  1318 |
|     1331 | test/FTS_0000000000000529_DELETED                  |  1319 |
|     1332 | test/FTS_0000000000000529_DELETED_CACHE            |  1320 |
|     1320 | test/joinit2                                       |  1308 |
+----------+----------------------------------------------------+-------+

FTS_0000000000000529 translates to table_id 1321, however, as you can see above, the FTS in question belongs to a temporary table. That is because when FTS is created for the first time, it has to rebuild the table to create the FTS_DOC_ID column. Again, it is not possible to copy the files under MDL protection.

New Table Added in the Middle of the Backup

Because of the per-table MDL acquisition, if a table has been created after PXB has gathered the list of tablespaces, it’s .ibd will not be copied. Instead, it will be recreated as part of the –prepare phase based on the data added to redo logs. As you can imagine, if the changes are redo-skipped after recreating the table based on redo information, the table will be incomplete.

Shared Tablespaces

Once a shared tablespace is parsed by the lock-ddl-per-table function, it will get a list of all tables created on that tablespace and acquire the MDL on those tables, however, there is no tablespace level MDL, which means there is nothing blocking a new table to be created on this tablespace. If the tablespace has already been copied, this will follow the previous point and be recreated at –prepare phase by parsing redo logs.

Best/Worst Case Scenario

The outcome of such inconsistent backups can be unknown. In the best-case scenario, you will get a crash either in the backup/prepare phase or when using the server. Yes, a crash is a best-case scenario because you will notice the issue right away.

In the worst-case scenario, data will be missed without you noticing it. Here instead of explaining let’s reproduce it.

Get a brand-new instance of MySQL/Percona Server for MySQL 5.7 and download Percona XtraBackup 2.4 prior to 2.4.21 (same can be reproducible with MySQL/PSMySQL 8 and PXB8).

In order to reproduce this scenario, we will be using gdb to pause PXB execution of the backup at a certain time, however, one can do the same by having a big table, which will take some time to copy.

gdb xtrabackup ex 'set args --backup --lock-ddl-per-table --target-dir=/tmp/backup' -ex 'b mdl_lock_table' -ex 'r'

This will start the backup. On a separate session, connect to MySQL and execute:

CREATE DATABASE a;
USE a;
CREATE TABLE tb1 (ID INT PRIMARY KEY, name CHAR(1));
INSERT INTO tb1 VALUES (3,'c'), (4, 'd'), (5, 'e');
CREATE INDEX n_index ON tb1(name);

Back to gdb session, execute:

disa 1
c
quit

Your backup will complete. You can now prepare it and restore it on MySQL. Now try to query a.tb1 using the below queries:

USE a;
SELECT * FROM tb1;
SELECT * FROM tb1 FORCE INDEX(PRIMARY);
SELECT * FROM tb1 FORCE INDEX(n_index);
SELECT * FROM tb1 WHERE name = 'd';

Here is an example:

mysql> SELECT * FROM tb1;
Empty set (0.00 sec)

mysql> SELECT * FROM tb1 FORCE INDEX(PRIMARY);
+----+------+
| ID | name |
+----+------+
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb1 FORCE INDEX(n_index);
Empty set (0.00 sec)

mysql> SELECT * FROM tb1 WHERE name = 'd';
Empty set (0.00 sec)

As you can see, querying using the PK shows that data is indeed present on the data. The index n_index exists but the corresponding index entries are not present, leading to inconsistent results, which may cause a lot of harm before you notice something is wrong.

Redesign of –lock-ddl-per-table

With the above points in mind, –lock-ddl-per-table has been reworked in order to guarantee a consistent backup. Percona XtraBackup 2.4.21 and Percona XtraBackup 8.0.22 have the below main changes:

  • MDL lock is now performed at the beginning of the backup before we do the first part/catch-up of redo logs.
  • Then the first scan of redo logs happens, and in this first scan, we can still have this MLOG_INDEX_LOAD event recorded in case a CREATE INDEX has happened right before the backup started. For now, it’s still safe to parse and accept it.
  • Once the first scan has finished, the thread responsible for following new redo log events is started and this thread will now abort the backup in case of encountering MLOG_INDEX_LOAD events.
  • Gather the list of tablespaces to copy.
  • Start to copy the actual .ibd files.

Other improvements have also been performed:

  • We now skip trying to run a SELECT if the .ibd file belongs to a temporary table as the SELECT query will never work.
  • Since we are taking MDL before we copy individual files, we are also skipping the lock in case we are dealing with an FTS. For FTS we will eventually (or have already) taken an MDL on the base table, making it safe to skip the work for those files too.
  • The query that takes MDL has been improved to not retrieve any data since a SELECT 1 FROM table LIMIT 0 will be enough for acquiring an MDL lock on the table.
  • –lock-ddl-per-table is a workaround for the changes done in WL#7277 when happening in the middle of a backup. In Percona Server 5.7 we have implemented LOCK TABLES FOR BACKUP (that gets executed with –lock-ddl parameter of PXB) which acquires an instance-level MDL lock. If a user is using –lock-ddl-per-table on Percona Server for MySQL 5.7 a warning is trow advising it should be using –lock-ddl instead.
  • For MySQL 8, upstream has implemented LOCK INSTANCE FOR BACKUP, which works similar to Percona Server LOCK TABLES FOR BACKUP, in the case of Percona XtraBackup 8, –lock-ddl-per-table is been deprecated, it still works but a warning is also issued advising users to switch to –lock-ddl.

Summary

As described throughout this post, WL#7277 brought some real challenges that can affect backups in different ways, some of them not easy to spot. Percona XtraBackup will always favor consistency and has been enhanced to work around those limitations when possible (taking MDL earlier in the process) and aborting the backup when an inconsistency is inevitable.

Percona Server for MySQL users and MySQL 8 should always use –lock-ddl which is a more robust and safe lock for those types of situations.

Oct
16
2020
--

Danger of Changing Default of log_error_verbosity on MySQL/Percona Server for MySQL 5.7

Changing Default of log_error_verbosity mysql

Changing Default of log_error_verbosity mysqlChanging the default value (3) of log_error_verbosity in MySQL/Percona Server for MySQL 5.7 can have a hidden unintended effect! What does log_error_verbosity do exactly? As per the documentation:

The log_error_verbosity system variable specifies the verbosity for handling events intended for the error log.

Basically a value of 1 logs only [Errors]; 2 is 1)+[Warnings]; and 3 is 2)+[Notes].

For example, one might be tempted to change the default of log_error_verbosity since the error.log might be bloated with thousands (or millions) of lines with [Notes], such as:

2020-10-08T17:02:56.736179Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2020-10-08T17:04:48.149038Z 4 [Note] Aborted connection 4 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)

(P.S. you can read more about those Notes on this other Percona blog posts):

Fixing MySQL 1045 Error: Access Denied

MySQL “Got an error reading communication packet”

The problem is, after lowering log_error_verbosity to 1 or 2, no messages about server startup or shutdown would be printed to the log! That can really make troubleshooting really hard in the event of issues or system failure.  For completeness, on error.log from 5.7 at startup with default log_error_verbosity, the following should be seen:

2020-10-08T16:36:07.302168Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-10-08T16:36:07.302188Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
...
2020-10-08T16:36:07.303998Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-10-08T16:36:07.307823Z 0 [Note] InnoDB: Completed initialization of buffer pool
...
2020-10-08T16:36:07.497571Z 0 [Note] /usr/sbin/mysqld: ready for connections.

And on shutdown:

2020-10-08T16:36:10.447002Z 0 [Note] Giving 0 client threads a chance to die gracefully
2020-10-08T16:36:10.447022Z 0 [Note] Shutting down slave threads
2020-10-08T16:36:10.447027Z 0 [Note] Forcefully disconnecting 0 remaining clients
…
2020-10-08T16:36:12.104099Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

With log_error_verbosity =2, there won’t be messages about MySQL startup, but some warnings that are only printed at startup might give a hint of the time of server restart such as:

2020-10-08T16:30:21.966844Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-08T16:30:22.181367Z 0 [Warning] CA certificate ca.pem is self signed.
2020-10-08T16:30:22.221732Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 6000)

If there is no information about server restart, system logs can be checked for information about restarts:

# cat /var/log/messages 
...
Oct  8 16:31:25 carlos-tutte-latest57-standalone-1 systemd: Starting MySQL Server...
Oct  8 16:31:26 carlos-tutte-latest57-standalone-1 systemd: Started MySQL Server.

If still no clue when was MySQL last started, checking the “Uptime” status variable can help in calculating the last startup.

The problem does NOT occur on MySQL/Percona Server for MySQL 8.0 since even with log_error_verbosity = 1, the following startup/shutdown lines are printed on the error.log:

2020-10-08T16:31:54.532504Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20-11) starting as process 1052
2020-10-08T16:31:54.540342Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-08T16:31:55.026815Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-10-08T16:31:55.136125Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/lib/mysql/mysqlx.sock' bind-address: '::' port: 33060
2020-10-08T16:31:55.270669Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20-11'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 11, Revision 5b5a5d2.
2020-10-08T16:32:01.708932Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-11)  Percona Server (GPL), Release 11, Revision 5b5a5d2.

In conclusion, if possible, avoid changing the default of log_error_verbosity on MySQL/Percona Server for MySQL 5.7. And if you need to change it, do it online with SET GLOBAL instead of through the config file, since, in the event of a restart, startup messages won’t be logged.

Sep
14
2020
--

How to Upgrade to MySQL 8.0 – Free Course at Percona University Online

upgrade to MySQL 8

upgrade to MySQL 8MySQL 8.0 General Availability release was launched in April 2018, and since then there have been ten versions of MySQL 8 and Percona Server for MySQL released. The MySQL Community expressed a high opinion of the MySQL 8.0 advantages, so a lot of databases have been successfully upgraded to the new version. But many of them still need to be up to date.

Percona has prepared a free course “How to Upgrade to MySQL 8.0” that helps you with this task.

It is a series of useful videos for 3-4 minutes. At the end of the course, you can pass the QUIZ and get a certificate. 

Follow the link to take the course:  https://classroom.google.com/c/MTM2MDIyNDIzMDQy?cjc=zjsst4l

You can also join the course manually. Just open Google Classroom and click “Join class” and enter the code of the class “zjsst4l”.

Why Even Upgrade?

Agenda

  • Lesson 1: How to Upgrade to MySQL 8.0 Overview
  • Lesson 2: How We Always Upgraded?
  • Lesson 3: Useful tools – pt-upgrade
  • Lesson 4: MySQL Shell in a Nutshell
  • Lesson 5: MySQL Upgrade – In-place and Replication & Rolling Upgrade
  • Lesson 6: MySQL 8.0 Packages and Best Practices
  • Lesson 7: Latest News and Links about Upgrading
  • Course Quiz & Certificate of Completion

Complete the course in Google Classroom and get the certificate: https://classroom.google.com/c/MTM2MDIyNDIzMDQy?cjc=zjsst4l

Percona University Online - Google Classroom

Percona University Online - Certificate

Follow the link to take the course:  https://classroom.google.com/c/MTM2MDIyNDIzMDQy?cjc=zjsst4l

Aug
18
2020
--

Aligning Percona XtraBackup Versions with Percona Server for MySQL

Aligning Percona XtraBackup Versions with Percona Server for MySQL

Aligning Percona XtraBackup Versions with Percona Server for MySQLWe are excited to let you know about two upcoming changes to Percona XtraBackup which will align Percona XtraBackup Versions with Percona Server for MySQL.  These changes are to bring Percona XtraBackup release naming line with Percona Server and MySQL and ensure Percona XtraBackup executes complete backups.

The first is a change to the naming structure of releases.  This change is something we believe will help when working with Percona products and is designed to position Percona XtraBackup to be in sync with the current release naming strategy of Percona Server for MySQL and Percona XtraDB Cluster.

The current naming structure of releases is x.y.zz where x.y is the upstream major release which is in concert with the Percona XtraBackup release (i.e. 8.0) and zz represents the build.  So 8.0.13 is based on the upstream 8.0 release and is the 13th build by Percona based on 8.0.

The new naming convention will be x.y.aa-zz.b where x.y remain the same – the upstream base (i.e 8.0) and zz will still be the Percona build number.  The aa will be the upstream build number (i.e. 21 in the 8.0.21 release) and the b will be any custom builds created on the base release.

For demonstration purposes here are some examples:

Current Naming Future Naming
8.0.14 8.0.21-14 (where aa would be 21 which is the current upstream release and 14 is the next Percona build based on the upstream release)
8.0.15 8.0.22-15 (where aa would be 22 which is the next upstream release and 15 is the next Percona build)
8.0.15-1 8.0.22-15.1 (where aa would be 22 which is the next upstream release and 15 is the next Percona build and .1 is a custom build based on the 8.0.22-15)

 

We believe it is important to provide advanced notice of this upcoming change so that any required analysis of automated release processing can be done so you are ready when we do make this change.

The second change coming for those who use Percona XtraBackup is a processing change.  With the last two upstream releases have come changes to MySQL which caused Percona XtraBackup processing to be affected.  In order to make sure we are supporting our customers and providing a verified solution we will be implementing changes to Percona XtraBackup as follows:

  1. When a backup is requested a check will be made to ensure the version of Percona XtraBackup is at or above the version of the database being backed up (MySQL, Percona Server for MySQL).  This will be the default configuration.
  2. If the Percona XtraBackup version is lower than the database version, processing will be stopped and Percona XtraBackup will not be allowed to continue UNLESS the default configuration provided by Percona is modified to ignore this check.
  3. Please note if this override is applied our customers will be taking responsibility for the results which can include the appearance that a successful backup has been completed when in fact the backup is actually not viable to be used in a future restoration.

The safest way to ensure your data is backed up and available for restore is to keep the Percona XtraBackup version at or above your database version and implement the new default configuration.

We are providing this information on these upcoming changes so that our customers and the community are aware and can position their environments appropriately.  We intend to make these changes within the next 6 months.  We are also looking for your feedback to ensure that this is considered as we are developing this change.  Feel free to comment below or reach out to us on the Percona XtraBackup Forum.


Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.

Download PDF

Aug
12
2020
--

Deadlock Troubleshooting in Percona Server for MySQL 5.7

Deadlock Troubleshooting MySQL

Disclaimer: the following script only works for Percona Server for MySQL 5.7, and relies on enabling performance schema (PS) instrumentation which can add overhead on high concurrent systems, and is not intended for continuous production usage as it’s a POC (proof of concept).

Introduction

Deadlock Troubleshooting MySQLIn Percona Support, we frequently receive tickets related to deadlocks and even though the deadlock concept is simple, troubleshooting might not be in all cases. 

As explained in How to Deal with MySQL Deadlocks, a deadlock occurs when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. MySQL will detect deadlocks and kill one of the transactions (making it rollback), and the deadlock will be printed in SEIS (show engine innodb status). Limitations of using this approach are that 1) Only one (latest) deadlock will be printed -potentially missing many of the deadlocks that occur if you are not actively checking- (unless enabling innodb_print_all_deadlocks), and 2) only the last executed DML is printed, which might not shed enough light to identify the offending transaction for complex deadlocks.

Using pt-deadlock-logger will overcome the difficulty from number one, as it will log all deadlocks occurring, but what about number two?

For Percona Server for MySQL 5.7, I have developed the following script (that you can find in our support snippets repo) that prints SQL history for two transactions involved in a deadlock, that can help in troubleshooting some complex deadlock cases. Note that most of the time, a deadlock will consist of two transactions, but other times there can be more transactions involved, in which case the script will be partially useful.

Installation

# download script
wget https://raw.githubusercontent.com/percona/support-snippets/master/mysql/deadlock_catcher.sh
chmod a+x deadlock_catcher.sh

# you need to set correct user/password before executing, i.e 
# MYSQL="mysql -u root -psekret"
./deadlock_catcher.sh

Executing the script will show the following prompts:

# the above script will prompt you about enabling the needed instrumentation:
SET GLOBAL innodb_print_all_deadlocks=1
...
Need to enable all statement/ instruments in performance schema, write YES to continue
YES
--------------
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%'
...
Need to enable all event_statements/ consumers, write YES to continue
YES
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%'

Then, the operating system prompt won’t be returning, as the script will keep running, scanning for deadlocks. (You can execute with “&” to run in the background.)

Note: deadlock_logger script will start logging deadlocks occurring only AFTER the script execution.

Generating a Deadlock

You need to open two sessions and execute the following interleaved:

Tx1:
drop table if exists t1;
create table t1 (id int auto_increment primary key);
begin; select * from t1;

Tx2:
begin; select * from t1;
insert into t1 values (1);

Tx1:
insert into t1 values (2);
insert into t1 values (1);

Tx2:
insert into t1 values (2);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Checking “deadlock_logger.sh” output we can see the history of transactions for above threads (you need to read from bottom to the top):

Tx1:
# Adding column names for better readability
# ev.thread_id, ev.EVENT_ID, ev.END_EVENT_ID, ev.EVENT_NAME, ev.SQL_TEXT
42 10 10 statement/sql/insert insert into t1 values (1)
42 9 9 statement/sql/insert insert into t1 values (2)
42 8 8 statement/sql/select select * from t1
42 7 7 statement/sql/begin begin
42 6 6 statement/sql/create_table create table t1 (id int auto_increment primary key)
42 5 5 statement/sql/drop_table drop table if exists t1
42 4 4 statement/sql/select select @@version_comment limit 1
42 3 3 statement/com/Field List NULL
42 2 2 statement/sql/show_tables show tables
42 1 1 statement/sql/show_databases show databases

Tx2:
# Adding column names for better readability
# ev.thread_id, ev.EVENT_ID, ev.END_EVENT_ID, ev.EVENT_NAME, ev.SQL_TEXT
43 8 8 statement/sql/insert insert into t1 values (2)
43 7 7 statement/sql/insert insert into t1 values (1)
43 6 6 statement/sql/select select * from t1
43 5 5 statement/sql/begin begin
43 4 4 statement/sql/select select @@version_comment limit 1
43 3 3 statement/com/Field List NULL
43 2 2 statement/sql/show_tables show tables
43 1 1 statement/sql/show_databases show databases

It can be seen that the entire history transaction is being fetched from Performance Schema tables, which helps identify deadlocks in complex transactions.

Limitations of the Script

  • Currently only works for Percona Server for MySQL 5.7
  • Enabling Performance Schema instrumentation will add some overhead to MySQL
  • The script works by tailing the error.log, so modifying the location or content at runtime can have unexpected results
  • The script relies on “performance_schema.events_statements_history” whose max rows are limited by  performance_schema_events_statements_history_long_size and performance_schema_events_statements_history_size. For high activity servers, the history table might rotate before the deadlock occurs, resulting in a partially incomplete print of statements, instead of printing the entire life of the thread.

Conclusion

Troubleshooting deadlocks can be hard, but after checking the entire history of statements executed for the threads involved in the deadlock, it can be easier to understand why a deadlock happened. Test the script and performance degradation before going to high activity and/or critical production systems!


Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.

Download PDF

Nov
01
2019
--

Use MySQL Without a Password (And Still Be Secure)

Use MySQL Without a Password

Use MySQL Without a PasswordSome say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket.

Neither of these plugins is new, and some words have been written about the auth_socket on this blog before, for example: how to change passwords in MySQL 5.7 when using plugin: auth_socket. But while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them because in MariaDB 10.4 a single user can have more than one authentication plugin, as explained in the Authentication from MariaDB 10.4 document).

As already mentioned this is not news, and even when one installs MySQL using the .deb packages maintained by the Debian team, the root user is created so it uses the socket authentication. This is true for both MySQL and MariaDB:

root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>

Using the Debian packages of MySQL, the root is authenticated as follows:

root@app:~# whoami
root=
root@app:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)

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> select user, host, plugin, authentication_string from mysql.user where user = 'root';
+------+-----------+-------------+-----------------------+
| user | host      | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket |                       |
+------+-----------+-------------+-----------------------+
1 row in set (0.01 sec)

Same for the MariaDB .deb package:

10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

MariaDB [(none)]> show grants;
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

For Percona Server, the .deb packages from the official Percona Repo are also setting the root user authentication to auth_socket. Here is an example of Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04:

root@app:~# whoami
root
root@app:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312'

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
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> select user, host, plugin, authentication_string from mysql.user where user ='root';
+------+-----------+-------------+-----------------------+
| user | host      | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket |                       |
+------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)

So, what’s the magic? The plugin checks that the Linux user matches the MySQL user using the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux. The SO_PEERCRED socket option allows retrieving the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid.

Here’s an example with the user “vagrant”:

vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'

Since no user “vagrant” exists in MySQL, the access is denied. Let’s create the user and try again:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket;
Query OK, 0 rows affected (0.00 sec)

vagrant@mysql1:~$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+---------------------------------------------------------------------------------+
| Grants for vagrant@localhost                                                    |
+---------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Success!

Now, what about on a non-debian distro, where this is not the default? Let’s try it on Percona Server for MySQL 8 installed on a CentOS 7:

mysql> show variables like '%version%comment';
+-----------------+---------------------------------------------------+
| Variable_name   | Value                                   |
+-----------------+---------------------------------------------------+
| version_comment | Percona Server (GPL), Release 7, Revision 613e312 |
+-----------------+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded

Failed. What is missing? The plugin is not loaded:

mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)

Let’s add the plugin in runtime:

mysql> nopager
PAGER set to stdout
mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)

mysql> pager grep socket; show plugins;
PAGER set to 'grep socket'
| auth_socket                     | ACTIVE | AUTHENTICATION | auth_socket.so | GPL     |
48 rows in set (0.00 sec)

We got all we need now. Let’s try again:

mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost';
Query OK, 0 rows affected (0.01 sec)

And now we can log in as the OS user “percona”.

[percona@ip-192-168-1-111 ~]$ whoami
percona
[percona@ip-192-168-1-111 ~]$ mysql -upercona
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312


Copyright (c) 2009-2019 Percona LLC and/or its affiliates
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> select user, host, plugin, authentication_string from mysql.user where user ='percona';
+---------+-----------+-------------+-----------------------+
| user    | host   | plugin   | authentication_string |
+---------+-----------+-------------+-----------------------+
| percona | localhost | auth_socket |                       |
+---------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)

Success again!

Question: Can I try to log as the user percona from another user?

[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user 'percona'@'localhost'

No, you can’t.

Conclusion

MySQL is flexible enough in several aspects, one being the authentication methods. As we see in this post, one can achieve access without passwords by relying on OS users. This is helpful in several scenarios, but just to mention one: when migrating from RDS/Aurora to regular MySQL and using IAM Database Authentication to keep getting access without using passwords.

Oct
23
2019
--

ProxySQL 2.0.7 and proxysql-admin Tool Now Available

ProxySQL

ProxySQLProxySQL 2.0.7, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.7 which introduces many new features and enhancements and also fixes a number of bugs.

The proxysql-admin tool now supports MariaDB 10.4.

New Features

Improvements

  • PSQLADM-195: A new option --with-stats-reset has been added to the proxysql-status script to display the  *_reset tables from the stats database. If this option is not specified, these tables are not displayed by default.

Bugs Fixed

  • PSQLADM-157: In some cases, the proxysql-status script used the cat command to display a file without checking if the file existed and was readable.
  • PSQLADM-181: When run with --update-cluster --write-node=<node_name>, the proxysql-admin tool now verifies that the writer nodes are not read-only.

The ProxySQL 2.0.7 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.7 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL is available under Open Source license GPLv3.

Aug
22
2019
--

Percona Server for MySQL 5.7.27-30 Is Now Available

Percona Server for MySQL 5.7

Percona Server for MySQLPercona announces the release of Percona Server for MySQL 5.7.27-30 on August 22, 2019 (downloads are available here and from the Percona Software Repositories). This release merges changes of MySQL 5.7.27, including all the bug fixes in it. Percona Server for MySQL 5.7.27-30 is now the current GA release in the 5.7 series. All of Percona’s software is open-source and free.

Bug Fixes:

  • Parallel doublewrite buffer writes must crash the server on an I/O error occurs. Bug fixed #5678.
  • On a server with two million or more tables using foreign keys and AUTOINC columns, the shutdown may take a measurable length of time. Bug fixed #5639 (Upstream #95895).
  • If large pages are enabled on the MySQL side, the maximum size for  innodb_buffer_pool_chunk_size is effectively limited to 4GB. Bug fixed #5517 (Upstream #94747).
  • The TokuDB hot backup library continually dumps TRACE information to the Server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.
  • Key rotation is redesigned to allow  Select rotate_system_key("percona_redo"). The currently used key version is displayed in the innodb_redo_key_version status. Bug fixed #5565.
  • The TokuDBBackupPlugin is optional at cmake time. Bug fixed #5748.
  • A multi-table  DELETE with a foreign key breaks replication. Bug fixed #3845.
  • TRUNCATEwith any table and interfacing with Adaptive Hash Index (AHI) can cause server stalls due to the interaction with AHI, whether the AHI is enabled or not.
    Bug fixed #5576 (upstream #94610).
  • In specific configurations and with log_slow_verbosity set to log InnoDB statistics, memory usage increases while running a stored procedure. Bug fixed #5581
  • Thread pool ability to track network I/O was disabled. Bug fixed #5723.
  • An instance started with the default values but setting the redo log to encrypt without specifying the keyring plugin parameters does not fail or throw an error.  Bug fixed #5476.
  • Setting the encryption to ON for the system tablespace generates the encryption key and encrypts system temporary tablespace pages. Resetting encryption to OFF , all subsequent pages are written to the temporary tablespace without encryption. To allow any encrypted tables to be decrypted, the generated keys are not erased. Modifying the innodb_temp_tablespace_encrypt does not affect file-per-table temporary tables. This type of table is encrypted if ENCRYPTION =’Y’ is set during the table creation. Bug fixed #5736
  • After resetting the innodb_temp_tablespace_encrypt to OFF during runtime, the subsequent file-per-table temporary tables continue to be encrypted. Bug fixed #5734

Other bugs fixed: #5752, #5749, #5746, #5744, #5743, #5742, #5740, #5711, #5695, #5681, #5669, #5645, #5638, #5593, #5532, #3970, #5696, #5689, #5146, #5715, #5662, #5420, #5149, #5686, #5688, #5697, #5716, #5725, #5773, #5775, #5820, and #5839.

Find the release notes for Percona Server for MySQL 5.7.27-30 in our online documentation. Report bugs in the Jira bug tracker.

Aug
21
2019
--

ProxySQL 2.0.6 and proxysql-admin tool Now Available

ProxySQL

ProxySQL 1.4.14

ProxySQL 2.0.6, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.6 which introduces many new features and enhancements and also fixes a number of bugs. The proxysql-admin tool has not changed since the previous release.

The ProxySQL 2.0.6 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.6 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL is available under Open Source license GPLv3.

Aug
20
2019
--

Percona Server for MySQL 5.6.45-86.1 Now Available

Percona Server for MySQL 5.6.45-86.1

Percona Server for MySQL 5.6.45-86.1Percona announces the release of Percona Server for MySQL 5.6.45-86.1 on August 20, 2019. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.6.45, and including all the bug fixes in it, Percona Server for MySQL 5.6.45-86.1 is the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL.

Bugs Fixed:

  • The TokuDB hot backup library continually dumps TRACE information to the server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.
  • The TokuDBBackupPlugin is optional at cmake time. Bug fixed #5748.

Other bugs fixed: #5531, #5146 #5638, #5645, #5669, #5749 #5752, #5780, #5833, #5725 #5742, #5743, and #5746.

Release notes are available in the online documentation. Please report any bugs on the JIRA bug tracker.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com