Shinguz

It is Saturday night at 23:19. Time to go to bed after a hard migration day. Just a last clean-up query before finishing: Tap tap tap. Enter! - Oops!

SQL> UPDATE prospect_lists_prospects SET prospect_list_id = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6'; Query OK, 4686 rows affected (0.21 sec) Rows matched: 5666 Changed: 4686 Warnings: 0

A verification query to make sure I am in the mess:

SQL> SELECT prospect_list_id, COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id; +--------------------------------------+----------+ | prospect_list_id | count(*) | +--------------------------------------+----------+ | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 5666 | +--------------------------------------+----------+

And certainly I did not enter the START TRANSACTION; command before. So no ROLLBACK!

Next look at the backup:

# ll backup/daily/bck_schema_crm_2019-06* -rw-rw-r-- 1 mysql mysql 7900060 Jun 1 02:13 backup/daily/bck_schema_crm_2019-06-01_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900061 Jun 2 02:13 backup/daily/bck_schema_crm_2019-06-02_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900091 Jun 3 02:13 backup/daily/bck_schema_crm_2019-06-03_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903126 Jun 4 02:13 backup/daily/bck_schema_crm_2019-06-04_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903192 Jun 5 02:13 backup/daily/bck_schema_crm_2019-06-05_02-13-02.sql.gz -rw-rw-r-- 1 mysql mysql 7903128 Jun 6 02:13 backup/daily/bck_schema_crm_2019-06-06_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7912886 Jun 21 02:13 backup/daily/bck_schema_crm_2019-06-21_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7920566 Jun 22 02:13 backup/daily/bck_schema_crm_2019-06-22_02-13-01.sql.gz

Yes! Backup is there and was done with the FromDual Backup Manager. So I am confident Restore and Point-in-Time-Recovery will work... But the Point-in-Time-Recovery with the Binary Logs for just one schema is a bit tricky and officially not so really supported.

So basically what I want to do is just to undo this UPDATE command. But unfortunately this UPDATE was not a reversible UPDATE command. Then I remembered a presentation about MariaDB 10.2 New Features (p. 41) where the speaker was talking about the flashback functionality in the mysqlbinlog utility.

Undo MySQL Binary Log Events with MariaDB mysqlbinlog utility

First of all I analysed the MySQL Binary Log to find the Binary Log Events to undo:

# mysqlbinlog --start-position=348622898 --verbose mysql-bin.000080 | less # at 348622898 #190622 23:19:43 server id 7 end_log_pos 348622969 CRC32 0xd358d264 Query thread_id=791264 exec_time=0 error_code=0 SET TIMESTAMP=1561238383/*!*/; BEGIN /*!*/; # at 348622969 #190622 23:19:43 server id 7 end_log_pos 348623049 CRC32 0x71340183 Table_map: `crm`.`prospect_lists_prospects` mapped to number 2857 # at 348623049 #190622 23:19:43 server id 7 end_log_pos 348631021 CRC32 0x53d65c9b Update_rows: table id 2857 ... ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 # at 349828089 #190622 23:19:43 server id 7 end_log_pos 349828120 CRC32 0x83f41493 Xid = 8361402 COMMIT/*!*/;

So the relevant part in the MySQL Binary Log is between position 348622898 and 349828120.

Now let us try the reverse operation. But for this we have to solve a little problem. The database is a MySQL 5.7. But the feature --flashback is only available in MariaDB 10.2 and newer. So we have to bring either the MySQL 5.7 Binary Logs to the MariaDB mysqlbinlog utility or the MariaDB mysqlbinlog utility to the MySQL 5.7 Binary Logs.

For a first attempt I moved the MySQL 5.7 Binary Logs to a MariaDB 10.3 testing system and gave it a try if mixing Binary Logs and Utility is working at all:

# mysqlbinlog --start-position=348622898 --stop-position=349828120 -v mysql-bin.000080 | grep -c 'UPDATE `crm`.`prospect_lists_prospects`' 4686

Looks good! Exactly the number of Row changes expected. Then let us look at the statement with --flashback:

# mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 -v | less '/*!*/; ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0

Looks good! Seems to be the reverse query. And now let us do the final repair job:

# /home/mysql/product/mariadb-10.3/mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 \ | /home/mysql/product/mysql-5.7/bin/mysql --user=root --port=3320 --host=127.0.0.1 crm --force ERROR 1193 (HY000) at line 21339: Unknown system variable 'check_constraint_checks'

The --force option was used to motivate mysql utility to continue even if an error occurs. Which was the case in our scenario. This option should usually not be used. We had tried out this step before on a testing system so I was aware what is happening and why this error occurs...

Now the final test on the repaired system shows the situation as it was before the accident:

SQL> SELECT IFNULL(prospect_list_id, 'Total:'), COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id WITH ROLLUP; +--------------------------------------+----------+ | IFNULL(prospect_list_id, 'Total:') | count(*) | +--------------------------------------+----------+ | 1178ec2b-6aa9-43e4-a27e-5d0e264cac4c | 91 | | 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | 946 | | 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | 5 | | 21d9a74f-73af-9a5d-84ba-5d0e280772ef | 107 | | 37230208-a431-f6d8-a428-5d0e28d9ec77 | 264 | | 4b48da8a-33d9-4896-5000-5d0e287ffe39 | 3 | | 5d06f6cc-3fe9-f501-b680-5d0ccfd19033 | 2 | | 5e39a569-3213-cc64-496f-5d0e28e851c9 | 5 | | 680a879c-ff3c-b955-c3b8-5d0e28c833c5 | 315 | | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 980 | | 756c4803-dc73-dc09-b301-5d0e28e69546 | 2 | | 8eb0ec25-6bbb-68de-d44f-5d0e262cd93d | 833 | | 913861f0-a865-7c94-8109-5d0e28d714b6 | 12 | | 96a10d6a-c10e-c945-eaeb-5d0e280aa16c | 74 | | a43147a8-90f2-a5b3-5bcf-5d0e2862248a | 15 | | ae869fb1-dd88-19c0-b0d6-538f7b7e329a | 20 | | b57eb9ba-5a93-8570-5914-5d0e28d975a9 | 25 | | b851169d-5e94-5c43-3593-5d0e2825d848 | 978 | | be320e31-1a5b-fe86-09d7-5d0e28a0fd2e | 7 | | c762abde-bc63-2383-ba30-5d0e28a714c9 | 160 | | cbbd0ba7-dc25-f29f-36f4-5d0e287c3006 | 99 | | d23490c8-99eb-f298-6aad-5d0e28e7fd4f | 52 | | d5000593-836c-3679-ecb5-5d0e28dd076c | 57 | | d81e9aae-ef60-fca2-7d99-5d0e269de1c0 | 421 | | df768570-f9b8-2333-66c4-5a6768e34ed3 | 3 | | e155d58a-19e8-5163-f846-5d0e282ba4b8 | 66 | | f139b6a0-9598-0cd4-a204-5d0e28c2eccd | 120 | | f165c48b-4fc1-b081-eee3-5d0cdd7947d5 | 4 | | Total: | 5666 | +--------------------------------------+----------+

Flashback of MySQL 5.7 Binary Logs with MariaDB 10.3 mysqlbinlog utility was successful!

If you want to learn more about Backup and Recovery strategies contact our MariaDB/MySQL consulting team or book one of our MariaDB/MySQL training classes.

Taxonomy upgrade extras: undoBackupbinary logmysqlbinlogflashbackRestorepitrRecoverypoint-in-time-recovery

It is Saturday night at 23:19. Time to go to bed after a hard migration day. Just a last clean-up query before finishing: Tap tap tap. Enter! - Oops!

SQL> UPDATE prospect_lists_prospects SET prospect_list_id = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6'; Query OK, 4686 rows affected (0.21 sec) Rows matched: 5666 Changed: 4686 Warnings: 0

A verification query to make sure I am in the mess:

SQL> SELECT prospect_list_id, COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id; +--------------------------------------+----------+ | prospect_list_id | count(*) | +--------------------------------------+----------+ | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 5666 | +--------------------------------------+----------+

And certainly I did not enter the START TRANSACTION; command before. So no ROLLBACK!

Next look at the backup:

# ll backup/daily/bck_schema_crm_2019-06* -rw-rw-r-- 1 mysql mysql 7900060 Jun 1 02:13 backup/daily/bck_schema_crm_2019-06-01_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900061 Jun 2 02:13 backup/daily/bck_schema_crm_2019-06-02_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900091 Jun 3 02:13 backup/daily/bck_schema_crm_2019-06-03_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903126 Jun 4 02:13 backup/daily/bck_schema_crm_2019-06-04_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903192 Jun 5 02:13 backup/daily/bck_schema_crm_2019-06-05_02-13-02.sql.gz -rw-rw-r-- 1 mysql mysql 7903128 Jun 6 02:13 backup/daily/bck_schema_crm_2019-06-06_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7912886 Jun 21 02:13 backup/daily/bck_schema_crm_2019-06-21_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7920566 Jun 22 02:13 backup/daily/bck_schema_crm_2019-06-22_02-13-01.sql.gz

Yes! Backup is there and was done with the FromDual Backup Manager. So I am confident Restore and Point-in-Time-Recovery will work... But the Point-in-Time-Recovery with the Binary Logs for just one schema is a bit tricky and officially not so really supported.

So basically what I want to do is just to undo this UPDATE command. But unfortunately this UPDATE was not a reversible UPDATE command. Then I remembered a presentation about MariaDB 10.2 New Features (p. 41) where the speaker was talking about the flashback functionality in the mysqlbinlog utility.

Undo MySQL Binary Log Events with MariaDB mysqlbinlog utility

First of all I analysed the MySQL Binary Log to find the Binary Log Events to undo:

# mysqlbinlog --start-position=348622898 --verbose mysql-bin.000080 | less # at 348622898 #190622 23:19:43 server id 7 end_log_pos 348622969 CRC32 0xd358d264 Query thread_id=791264 exec_time=0 error_code=0 SET TIMESTAMP=1561238383/*!*/; BEGIN /*!*/; # at 348622969 #190622 23:19:43 server id 7 end_log_pos 348623049 CRC32 0x71340183 Table_map: `crm`.`prospect_lists_prospects` mapped to number 2857 # at 348623049 #190622 23:19:43 server id 7 end_log_pos 348631021 CRC32 0x53d65c9b Update_rows: table id 2857 ... ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 # at 349828089 #190622 23:19:43 server id 7 end_log_pos 349828120 CRC32 0x83f41493 Xid = 8361402 COMMIT/*!*/;

So the relevant part in the MySQL Binary Log is between position 348622898 and 349828120.

Now let us try the reverse operation. But for this we have to solve a little problem. The database is a MySQL 5.7. But the feature --flashback is only available in MariaDB 10.2 and newer. So we have to bring either the MySQL 5.7 Binary Logs to the MariaDB mysqlbinlog utility or the MariaDB mysqlbinlog utility to the MySQL 5.7 Binary Logs.

For a first attempt I moved the MySQL 5.7 Binary Logs to a MariaDB 10.3 testing system and gave it a try if mixing Binary Logs and Utility is working at all:

# mysqlbinlog --start-position=348622898 --stop-position=349828120 -v mysql-bin.000080 | grep -c 'UPDATE `crm`.`prospect_lists_prospects`' 4686

Looks good! Exactly the number of Row changes expected. Then let us look at the statement with --flashback:

# mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 -v | less '/*!*/; ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0

Looks good! Seems to be the reverse query. And now let us do the final repair job:

# /home/mysql/product/mariadb-10.3/mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 \ | /home/mysql/product/mysql-5.7/bin/mysql --user=root --port=3320 --host=127.0.0.1 crm --force ERROR 1193 (HY000) at line 21339: Unknown system variable 'check_constraint_checks'

The --force option was used to motivate mysql utility to continue even if an error occurs. Which was the case in our scenario. This option should usually not be used. We had tried out this step before on a testing system so I was aware what is happening and why this error occurs...

Now the final test on the repaired system shows the situation as it was before the accident:

SQL> SELECT IFNULL(prospect_list_id, 'Total:'), COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id WITH ROLLUP; +--------------------------------------+----------+ | IFNULL(prospect_list_id, 'Total:') | count(*) | +--------------------------------------+----------+ | 1178ec2b-6aa9-43e4-a27e-5d0e264cac4c | 91 | | 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | 946 | | 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | 5 | | 21d9a74f-73af-9a5d-84ba-5d0e280772ef | 107 | | 37230208-a431-f6d8-a428-5d0e28d9ec77 | 264 | | 4b48da8a-33d9-4896-5000-5d0e287ffe39 | 3 | | 5d06f6cc-3fe9-f501-b680-5d0ccfd19033 | 2 | | 5e39a569-3213-cc64-496f-5d0e28e851c9 | 5 | | 680a879c-ff3c-b955-c3b8-5d0e28c833c5 | 315 | | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 980 | | 756c4803-dc73-dc09-b301-5d0e28e69546 | 2 | | 8eb0ec25-6bbb-68de-d44f-5d0e262cd93d | 833 | | 913861f0-a865-7c94-8109-5d0e28d714b6 | 12 | | 96a10d6a-c10e-c945-eaeb-5d0e280aa16c | 74 | | a43147a8-90f2-a5b3-5bcf-5d0e2862248a | 15 | | ae869fb1-dd88-19c0-b0d6-538f7b7e329a | 20 | | b57eb9ba-5a93-8570-5914-5d0e28d975a9 | 25 | | b851169d-5e94-5c43-3593-5d0e2825d848 | 978 | | be320e31-1a5b-fe86-09d7-5d0e28a0fd2e | 7 | | c762abde-bc63-2383-ba30-5d0e28a714c9 | 160 | | cbbd0ba7-dc25-f29f-36f4-5d0e287c3006 | 99 | | d23490c8-99eb-f298-6aad-5d0e28e7fd4f | 52 | | d5000593-836c-3679-ecb5-5d0e28dd076c | 57 | | d81e9aae-ef60-fca2-7d99-5d0e269de1c0 | 421 | | df768570-f9b8-2333-66c4-5a6768e34ed3 | 3 | | e155d58a-19e8-5163-f846-5d0e282ba4b8 | 66 | | f139b6a0-9598-0cd4-a204-5d0e28c2eccd | 120 | | f165c48b-4fc1-b081-eee3-5d0cdd7947d5 | 4 | | Total: | 5666 | +--------------------------------------+----------+

Flashback of MySQL 5.7 Binary Logs with MariaDB 10.3 mysqlbinlog utility was successful!

If you want to learn more about Backup and Recovery strategies contact our MariaDB/MySQL consulting team or book one of our MariaDB/MySQL training classes.

Taxonomy upgrade extras: undoBackupbinary logmysqlbinlogflashbackRestorepitrRecoverypoint-in-time-recovery

It is Saturday night at 23:19. Time to go to bed after a hard migration day. Just a last clean-up query before finishing: Tap tap tap. Enter! - Oops!

SQL> UPDATE prospect_lists_prospects SET prospect_list_id = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6'; Query OK, 4686 rows affected (0.21 sec) Rows matched: 5666 Changed: 4686 Warnings: 0

A verification query to make sure I am in the mess:

SQL> SELECT prospect_list_id, COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id; +--------------------------------------+----------+ | prospect_list_id | count(*) | +--------------------------------------+----------+ | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 5666 | +--------------------------------------+----------+

And certainly I did not enter the START TRANSACTION; command before. So no ROLLBACK!

Next look at the backup:

# ll backup/daily/bck_schema_crm_2019-06* -rw-rw-r-- 1 mysql mysql 7900060 Jun 1 02:13 backup/daily/bck_schema_crm_2019-06-01_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900061 Jun 2 02:13 backup/daily/bck_schema_crm_2019-06-02_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900091 Jun 3 02:13 backup/daily/bck_schema_crm_2019-06-03_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903126 Jun 4 02:13 backup/daily/bck_schema_crm_2019-06-04_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903192 Jun 5 02:13 backup/daily/bck_schema_crm_2019-06-05_02-13-02.sql.gz -rw-rw-r-- 1 mysql mysql 7903128 Jun 6 02:13 backup/daily/bck_schema_crm_2019-06-06_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7912886 Jun 21 02:13 backup/daily/bck_schema_crm_2019-06-21_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7920566 Jun 22 02:13 backup/daily/bck_schema_crm_2019-06-22_02-13-01.sql.gz

Yes! Backup is there and was done with the FromDual Backup Manager. So I am confident Restore and Point-in-Time-Recovery will work... But the Point-in-Time-Recovery with the Binary Logs for just one schema is a bit tricky and officially not so really supported.

So basically what I want to do is just to undo this UPDATE command. But unfortunately this UPDATE was not a reversible UPDATE command. Then I remembered a presentation about MariaDB 10.2 New Features (p. 41) where the speaker was talking about the flashback functionality in the mysqlbinlog utility.

Undo MySQL Binary Log Events with MariaDB mysqlbinlog utility

First of all I analysed the MySQL Binary Log to find the Binary Log Events to undo:

# mysqlbinlog --start-position=348622898 --verbose mysql-bin.000080 | less # at 348622898 #190622 23:19:43 server id 7 end_log_pos 348622969 CRC32 0xd358d264 Query thread_id=791264 exec_time=0 error_code=0 SET TIMESTAMP=1561238383/*!*/; BEGIN /*!*/; # at 348622969 #190622 23:19:43 server id 7 end_log_pos 348623049 CRC32 0x71340183 Table_map: `crm`.`prospect_lists_prospects` mapped to number 2857 # at 348623049 #190622 23:19:43 server id 7 end_log_pos 348631021 CRC32 0x53d65c9b Update_rows: table id 2857 ... ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 # at 349828089 #190622 23:19:43 server id 7 end_log_pos 349828120 CRC32 0x83f41493 Xid = 8361402 COMMIT/*!*/;

So the relevant part in the MySQL Binary Log is between position 348622898 and 349828120.

Now let us try the reverse operation. But for this we have to solve a little problem. The database is a MySQL 5.7. But the feature --flashback is only available in MariaDB 10.2 and newer. So we have to bring either the MySQL 5.7 Binary Logs to the MariaDB mysqlbinlog utility or the MariaDB mysqlbinlog utility to the MySQL 5.7 Binary Logs.

For a first attempt I moved the MySQL 5.7 Binary Logs to a MariaDB 10.3 testing system and gave it a try if mixing Binary Logs and Utility is working at all:

# mysqlbinlog --start-position=348622898 --stop-position=349828120 -v mysql-bin.000080 | grep -c 'UPDATE `crm`.`prospect_lists_prospects`' 4686

Looks good! Exactly the number of Row changes expected. Then let us look at the statement with --flashback:

# mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 -v | less '/*!*/; ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0

Looks good! Seems to be the reverse query. And now let us do the final repair job:

# /home/mysql/product/mariadb-10.3/mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 \ | /home/mysql/product/mysql-5.7/bin/mysql --user=root --port=3320 --host=127.0.0.1 crm --force ERROR 1193 (HY000) at line 21339: Unknown system variable 'check_constraint_checks'

The --force option was used to motivate mysql utility to continue even if an error occurs. Which was the case in our scenario. This option should usually not be used. We had tried out this step before on a testing system so I was aware what is happening and why this error occurs...

Now the final test on the repaired system shows the situation as it was before the accident:

SQL> SELECT IFNULL(prospect_list_id, 'Total:'), COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id WITH ROLLUP; +--------------------------------------+----------+ | IFNULL(prospect_list_id, 'Total:') | count(*) | +--------------------------------------+----------+ | 1178ec2b-6aa9-43e4-a27e-5d0e264cac4c | 91 | | 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | 946 | | 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | 5 | | 21d9a74f-73af-9a5d-84ba-5d0e280772ef | 107 | | 37230208-a431-f6d8-a428-5d0e28d9ec77 | 264 | | 4b48da8a-33d9-4896-5000-5d0e287ffe39 | 3 | | 5d06f6cc-3fe9-f501-b680-5d0ccfd19033 | 2 | | 5e39a569-3213-cc64-496f-5d0e28e851c9 | 5 | | 680a879c-ff3c-b955-c3b8-5d0e28c833c5 | 315 | | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 980 | | 756c4803-dc73-dc09-b301-5d0e28e69546 | 2 | | 8eb0ec25-6bbb-68de-d44f-5d0e262cd93d | 833 | | 913861f0-a865-7c94-8109-5d0e28d714b6 | 12 | | 96a10d6a-c10e-c945-eaeb-5d0e280aa16c | 74 | | a43147a8-90f2-a5b3-5bcf-5d0e2862248a | 15 | | ae869fb1-dd88-19c0-b0d6-538f7b7e329a | 20 | | b57eb9ba-5a93-8570-5914-5d0e28d975a9 | 25 | | b851169d-5e94-5c43-3593-5d0e2825d848 | 978 | | be320e31-1a5b-fe86-09d7-5d0e28a0fd2e | 7 | | c762abde-bc63-2383-ba30-5d0e28a714c9 | 160 | | cbbd0ba7-dc25-f29f-36f4-5d0e287c3006 | 99 | | d23490c8-99eb-f298-6aad-5d0e28e7fd4f | 52 | | d5000593-836c-3679-ecb5-5d0e28dd076c | 57 | | d81e9aae-ef60-fca2-7d99-5d0e269de1c0 | 421 | | df768570-f9b8-2333-66c4-5a6768e34ed3 | 3 | | e155d58a-19e8-5163-f846-5d0e282ba4b8 | 66 | | f139b6a0-9598-0cd4-a204-5d0e28c2eccd | 120 | | f165c48b-4fc1-b081-eee3-5d0cdd7947d5 | 4 | | Total: | 5666 | +--------------------------------------+----------+

Flashback of MySQL 5.7 Binary Logs with MariaDB 10.3 mysqlbinlog utility was successful!

If you want to learn more about Backup and Recovery strategies contact our MariaDB/MySQL consulting team or book one of our MariaDB/MySQL training classes.

Taxonomy upgrade extras: undoBackupbinary logmysqlbinlogflashbackRestorepitrRecoverypoint-in-time-recovery

If your MariaDB or MySQL database system is swapping it can have a significant impact on your database query performance! Further it can also slow down your database shutdown and thus influence the whole reboot of your machine. This is especially painful if you have only short maintenance windows or if you do not want to spend the whole night with operation tasks.

When we do reviews of our customer MariaDB or MySQL database systems one of the items to check is Swap Space and swapping. With the free command you can find if your system has Swap Space enabled at all and how much of your Swap Space is used:

# free total used free shared buff/cache available Mem: 16106252 3300424 697284 264232 12108544 12011972 Swap: 31250428 1701792 29548636

With the command:

# swapon --show NAME TYPE SIZE USED PRIO /dev/sdb2 partition 29.8G 1.6G -1

you can show on which disk drive your Swap Space is physically located. And with the following 3 commands you can find if your system is currently swapping or not:

# vmstat 1 procs ------------memory------------ ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 1701784 692580 355716 11757864 2 12 685 601 237 146 9 3 86 2 0 0 0 1701784 692472 355720 11757840 0 0 0 196 679 2350 2 1 97 1 0 0 0 1701784 692720 355728 11757332 0 0 0 104 606 2170 0 1 98 1 0 # sar -W 1 15:44:30 pswpin/s pswpout/s 15:44:31 0.00 0.00 15:44:32 0.00 0.00 15:44:33 0.00 0.00 # sar -S 1 15:43:02 kbswpfree kbswpused %swpused kbswpcad %swpcad 15:43:03 29548648 1701780 5.45 41552 2.44 15:43:04 29548648 1701780 5.45 41552 2.44 15:43:05 29548648 1701780 5.45 41552 2.44

Side note: Recent Linux distributions tend to use Swap Files instead of Swap Partitions. The performance impact seems to be negligible compared to the operational advantages of Swap Files... [ 1 ] [ 2 ] [ 3 ] [ 4 ]

What is Swap Space on a Linux system

Modern Operating Systems like Linux manage Virtual Memory (VM) which consists of RAM (fast) and Disk (HDD very slow and SSD slow). If the Operating System is short in fast RAM it tries to write some "old" pages to slow disk to get more free fast RAM for "new" pages and/or for the file system cache. This technique enables the Operating System to keep more and/or bigger processes running than physical RAM is available (overcommitment of RAM).
If one of those "old" pages is needed again it has to be swapped in which technically is a physical random disk read (which is slow, this is also called a major page fault).
If this block is a MariaDB or MySQL database block this disk read to RAM will slow down your SELECT queries but also INSERT, UPDATE and DELETE when you do write queries. This can severely slow down for example your clean-up jobs which have to remove "old" data (located on disk possibly in Swap Space).

Sizing of Swap Space for database systems

A rule of thumb for Swap Space is: Have always Swap Space but never use it (disk is cheap nowadays)!

A reasonable Swap Space sizing for database systems is the following:

Amount of RAMSwap Space4 GiB of RAM or lessa minimum of 4 GiB of Swap Space, is this really a Database server?8 GiB to 16 GiB of RAMa minimum of once the amount of RAM of Swap Space24 GiB to 64 GiB of RAMa minimum of half the amount of RAM of Swap Spacemore than 64 GiB of RAMa minimum of 32 GiB of Swap Space

If you have a close look at your Swap usage and if you monitor your Swap Space precisely and if you know exactly what you are doing you can lower these values...

It is NOT recommended to disable Swap Space

Some people tend to disable Swap Space. We see this mainly in virtualized environments (virtual machines) and cloud servers. From the VM/Cloud administrator point of view I can even understand why they disable Swap. But from the MariaDB / MySQL DBA point of view this is a bad idea.

If you do proper MariaDB / MySQL configuration (innodb_buffer_pool_size = 75% of RAM) the server should not swap a lot. But if you exaggerate with memory configuration the system starts swapping heavily. Till to the end the OOM-Killer will be activated by your Linux killing the troublemaker (typically the database process). If you have sufficient Swap Space enabled you get some time to detect a bad database configuration and act accordingly. If you have Swap Space disabled completely you do not get this safety buffer and OOM killer will act immediately and kill your database process when you run out of RAM. This really cannot be in the interest of the DBA.

Some literature to read further about Swap: In defence of swap: common misconceptions

Influence swapping - Swappiness

The Linux kernel documentation tells us the following about swappiness:

swappiness

This control is used to define how aggressive the kernel will swap memory pages. Higher values will increase aggressiveness, lower values decrease the amount of swap. A value of 0 instructs the kernel not to initiate swap until the amount of free and file-backed pages is less than the high water mark in a zone.

The default value is 60.

Source: Documentation for /proc/sys/vm/*

A informative article on StackExchange: Why is swappiness set to 60 by default?

To change your swappiness the following commands will help:

# sysctl vm.swappiness vm.swappiness = 60 # sysctl vm.swappiness=1 # sysctl vm.swappiness vm.swappiness = 1

To make these changes persistent you have to write it to some kind of configuration file dependent on your Operating System:

# # /etc/sysctl.d/99-sysctl.conf # vm.swappiness=1
Who is using the Swap Space?

For further analysing your Swap Space and to find who is using your Swap Space please see our article MariaDB and MySQL swap analysis.

What if your system is still swapping? - NUMA!

If you did everything correctly until here and your system is still swapping you possibly missed one point: NUMA systems behave a bit tricky related to Databases and swapping. The first person who wrote extensively about this problem in the MySQL ecosystem was Jeremy Cole in 2010 in his two well written articles which you can find here:

• The MySQL “swap insanity” problem and the effects of the NUMA architecture and
• A brief update on NUMA and MySQL

What NUMA is you can find here: Non-uniform memory access.

If you have spent your money for an expensive NUMA system you can find with the following command:

# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 56 On-line CPU(s) list: 0-55 Thread(s) per core: 2 Core(s) per socket: 14 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2690 v4 @ 2.60GHz Stepping: 1 CPU MHz: 2600.000 CPU max MHz: 2600.0000 CPU min MHz: 1200.0000 BogoMIPS: 5201.37 Virtualization: VT-x Hypervisor vendor: vertical Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 35840K NUMA node0 CPU(s): 0-13,28-41 NUMA node1 CPU(s): 14-27,42-55

If you are now in the unfortunate situation of having such a huge box with several sockets you can do different things:

• Configuring your MariaDB / MySQL database to allocate memory evenly on both sockets with the parameter innodb_numa_interleave. This works since MySQL 5.6.27, MySQL 5.7.9 and MariaDB 10.2.4 but there were various bugs in this area in Debian and CentOS packages (e.g. #80288, #78953, #79354 and MDEV-18660).
• Disable NUMA support in your BIOS (Node Interleaving = enabled). Then there is no NUMA presentation to the Operating System any more.
• Start your MariaDB / MySQL database with numactl --interleave all as described here: MySQL and NUMA.
• Set innodb_buffer_pool_size to 75% of half of your RAM. Sad for having too much of RAM.
• Playing around with the following Linux settings could help to decrease swapping: vm.zone_reclaim_mode=0 and kernel.numa_balancing=0.

Literature

Some further information about Swap Space you can find here:

• An introduction to swap space on Linux systems
• Linux Swap Space
• Linux Kernel Memory Management: Swap Space
• Linux RAM & Swap
• All about Linux swap space
• Swap Management

Taxonomy upgrade extras: swapnumaperformancedatabasepostgresql

If your MariaDB or MySQL database system is swapping it can have a significant impact on your database query performance! Further it can also slow down your database shutdown and thus influence the whole reboot of your machine. This is especially painful if you have only short maintenance windows or if you do not want to spend the whole night with operation tasks.

When we do reviews of our customer MariaDB or MySQL database systems one of the items to check is Swap Space and swapping. With the free command you can find if your system has Swap Space enabled at all and how much of your Swap Space is used:

# free total used free shared buff/cache available Mem: 16106252 3300424 697284 264232 12108544 12011972 Swap: 31250428 1701792 29548636

With the command:

# swapon --show NAME TYPE SIZE USED PRIO /dev/sdb2 partition 29.8G 1.6G -1

you can show on which disk drive your Swap Space is physically located. And with the following 3 commands you can find if your system is currently swapping or not:

# vmstat 1 procs ------------memory------------ ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 1701784 692580 355716 11757864 2 12 685 601 237 146 9 3 86 2 0 0 0 1701784 692472 355720 11757840 0 0 0 196 679 2350 2 1 97 1 0 0 0 1701784 692720 355728 11757332 0 0 0 104 606 2170 0 1 98 1 0 # sar -W 1 15:44:30 pswpin/s pswpout/s 15:44:31 0.00 0.00 15:44:32 0.00 0.00 15:44:33 0.00 0.00 # sar -S 1 15:43:02 kbswpfree kbswpused %swpused kbswpcad %swpcad 15:43:03 29548648 1701780 5.45 41552 2.44 15:43:04 29548648 1701780 5.45 41552 2.44 15:43:05 29548648 1701780 5.45 41552 2.44

Side note: Recent Linux distributions tend to use Swap Files instead of Swap Partitions. The performance impact seems to be negligible compared to the operational advantages of Swap Files... [ 1 ] [ 2 ] [ 3 ] [ 4 ]

What is Swap Space on a Linux system

Modern Operating Systems like Linux manage Virtual Memory (VM) which consists of RAM (fast) and Disk (HDD very slow and SSD slow). If the Operating System is short in fast RAM it tries to write some "old" pages to slow disk to get more free fast RAM for "new" pages and/or for the file system cache. This technique enables the Operating System to keep more and/or bigger processes running than physical RAM is available (overcommitment of RAM).
If one of those "old" pages is needed again it has to be swapped in which technically is a physical random disk read (which is slow, this is also called a major page fault).
If this block is a MariaDB or MySQL database block this disk read to RAM will slow down your SELECT queries but also INSERT, UPDATE and DELETE when you do write queries. This can severely slow down for example your clean-up jobs which have to remove "old" data (located on disk possibly in Swap Space).

Sizing of Swap Space for database systems

A rule of thumb for Swap Space is: Have always Swap Space but never use it (disk is cheap nowadays)!

A reasonable Swap Space sizing for database systems is the following:

Amount of RAMSwap Space4 GiB of RAM or lessa minimum of 4 GiB of Swap Space, is this really a Database server?8 GiB to 16 GiB of RAMa minimum of once the amount of RAM of Swap Space24 GiB to 64 GiB of RAMa minimum of half the amount of RAM of Swap Spacemore than 64 GiB of RAMa minimum of 32 GiB of Swap Space

If you have a close look at your Swap usage and if you monitor your Swap Space precisely and if you know exactly what you are doing you can lower these values...

It is NOT recommended to disable Swap Space

Some people tend to disable Swap Space. We see this mainly in virtualized environments (virtual machines) and cloud servers. From the VM/Cloud administrator point of view I can even understand why they disable Swap. But from the MariaDB / MySQL DBA point of view this is a bad idea.

If you do proper MariaDB / MySQL configuration (innodb_buffer_pool_size = 75% of RAM) the server should not swap a lot. But if you exaggerate with memory configuration the system starts swapping heavily. Till to the end the OOM-Killer will be activated by your Linux killing the troublemaker (typically the database process). If you have sufficient Swap Space enabled you get some time to detect a bad database configuration and act accordingly. If you have Swap Space disabled completely you do not get this safety buffer and OOM killer will act immediately and kill your database process when you run out of RAM. This really cannot be in the interest of the DBA.

Some literature to read further about Swap: In defence of swap: common misconceptions

Influence swapping - Swappiness

The Linux kernel documentation tells us the following about swappiness:

swappiness

This control is used to define how aggressive the kernel will swap memory pages. Higher values will increase aggressiveness, lower values decrease the amount of swap. A value of 0 instructs the kernel not to initiate swap until the amount of free and file-backed pages is less than the high water mark in a zone.

The default value is 60.

Source: Documentation for /proc/sys/vm/*

A informative article on StackExchange: Why is swappiness set to 60 by default?

To change your swappiness the following commands will help:

# sysctl vm.swappiness vm.swappiness = 60 # sysctl vm.swappiness=1 # sysctl vm.swappiness vm.swappiness = 1

To make these changes persistent you have to write it to some kind of configuration file dependent on your Operating System:

# # /etc/sysctl.d/99-sysctl.conf # vm.swappiness=1
Who is using the Swap Space?

For further analysing your Swap Space and to find who is using your Swap Space please see our article MariaDB and MySQL swap analysis.

What if your system is still swapping? - NUMA!

If you did everything correctly until here and your system is still swapping you possibly missed one point: NUMA systems behave a bit tricky related to Databases and swapping. The first person who wrote extensively about this problem in the MySQL ecosystem was Jeremy Cole in 2010 in his two well written articles which you can find here:

• The MySQL “swap insanity” problem and the effects of the NUMA architecture and
• A brief update on NUMA and MySQL

What NUMA is you can find here: Non-uniform memory access.

If you have spent your money for an expensive NUMA system you can find with the following command:

# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 56 On-line CPU(s) list: 0-55 Thread(s) per core: 2 Core(s) per socket: 14 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2690 v4 @ 2.60GHz Stepping: 1 CPU MHz: 2600.000 CPU max MHz: 2600.0000 CPU min MHz: 1200.0000 BogoMIPS: 5201.37 Virtualization: VT-x Hypervisor vendor: vertical Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 35840K NUMA node0 CPU(s): 0-13,28-41 NUMA node1 CPU(s): 14-27,42-55

If you are now in the unfortunate situation of having such a huge box with several sockets you can do different things:

• Configuring your MariaDB / MySQL database to allocate memory evenly on both sockets with the parameter innodb_numa_interleave. This works since MySQL 5.6.27, MySQL 5.7.9 and MariaDB 10.2.4 but there were various bugs in this area in Debian and CentOS packages (e.g. #80288, #78953, #79354 and MDEV-18660).
• Disable NUMA support in your BIOS (Node Interleaving = enabled). Then there is no NUMA presentation to the Operating System any more.
• Start your MariaDB / MySQL database with numactl --interleave all as described here: MySQL and NUMA.
• Set innodb_buffer_pool_size to 75% of half of your RAM. Sad for having too much of RAM.
• Playing around with the following Linux settings could help to decrease swapping: vm.zone_reclaim_mode=0 and kernel.numa_balancing=0.

Literature

Some further information about Swap Space you can find here:

• An introduction to swap space on Linux systems
• Linux Swap Space
• Linux Kernel Memory Management: Swap Space
• Linux RAM & Swap
• All about Linux swap space
• Swap Management

Taxonomy upgrade extras: swapnumaperformancedatabasepostgresql

If your MariaDB or MySQL database system is swapping it can have a significant impact on your database query performance! Further it can also slow down your database shutdown and thus influence the whole reboot of your machine. This is especially painful if you have only short maintenance windows or if you do not want to spend the whole night with operation tasks.

When we do reviews of our customer MariaDB or MySQL database systems one of the items to check is Swap Space and swapping. With the free command you can find if your system has Swap Space enabled at all and how much of your Swap Space is used:

# free total used free shared buff/cache available Mem: 16106252 3300424 697284 264232 12108544 12011972 Swap: 31250428 1701792 29548636

With the command:

# swapon --show NAME TYPE SIZE USED PRIO /dev/sdb2 partition 29.8G 1.6G -1

you can show on which disk drive your Swap Space is physically located. And with the following 3 commands you can find if your system is currently swapping or not:

# vmstat 1 procs ------------memory------------ ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 1701784 692580 355716 11757864 2 12 685 601 237 146 9 3 86 2 0 0 0 1701784 692472 355720 11757840 0 0 0 196 679 2350 2 1 97 1 0 0 0 1701784 692720 355728 11757332 0 0 0 104 606 2170 0 1 98 1 0 # sar -W 1 15:44:30 pswpin/s pswpout/s 15:44:31 0.00 0.00 15:44:32 0.00 0.00 15:44:33 0.00 0.00 # sar -S 1 15:43:02 kbswpfree kbswpused %swpused kbswpcad %swpcad 15:43:03 29548648 1701780 5.45 41552 2.44 15:43:04 29548648 1701780 5.45 41552 2.44 15:43:05 29548648 1701780 5.45 41552 2.44

Side note: Recent Linux distributions tend to use Swap Files instead of Swap Partitions. The performance impact seems to be negligible compared to the operational advantages of Swap Files... [ 1 ] [ 2 ] [ 3 ] [ 4 ]

What is Swap Space on a Linux system

Modern Operating Systems like Linux manage Virtual Memory (VM) which consists of RAM (fast) and Disk (HDD very slow and SSD slow). If the Operating System is short in fast RAM it tries to write some "old" pages to slow disk to get more free fast RAM for "new" pages and/or for the file system cache. This technique enables the Operating System to keep more and/or bigger processes running than physical RAM is available (overcommitment of RAM).
If one of those "old" pages is needed again it has to be swapped in which technically is a physical random disk read (which is slow, this is also called a major page fault).
If this block is a MariaDB or MySQL database block this disk read to RAM will slow down your SELECT queries but also INSERT, UPDATE and DELETE when you do write queries. This can severely slow down for example your clean-up jobs which have to remove "old" data (located on disk possibly in Swap Space).

Sizing of Swap Space for database systems

A rule of thumb for Swap Space is: Have always Swap Space but never use it (disk is cheap nowadays)!

A reasonable Swap Space sizing for database systems is the following:

Amount of RAMSwap Space4 GiB of RAM or lessa minimum of 4 GiB of Swap Space, is this really a Database server?8 GiB to 16 GiB of RAMa minimum of once the amount of RAM of Swap Space24 GiB to 64 GiB of RAMa minimum of half the amount of RAM of Swap Spacemore than 64 GiB of RAMa minimum of 32 GiB of Swap Space

If you have a close look at your Swap usage and if you monitor your Swap Space precisely and if you know exactly what you are doing you can lower these values...

It is NOT recommended to disable Swap Space

Some people tend to disable Swap Space. We see this mainly in virtualized environments (virtual machines) and cloud servers. From the VM/Cloud administrator point of view I can even understand why they disable Swap. But from the MariaDB / MySQL DBA point of view this is a bad idea.

If you do proper MariaDB / MySQL configuration (innodb_buffer_pool_size = 75% of RAM) the server should not swap a lot. But if you exaggerate with memory configuration the system starts swapping heavily. Till to the end the OOM-Killer will be activated by your Linux killing the troublemaker (typically the database process). If you have sufficient Swap Space enabled you get some time to detect a bad database configuration and act accordingly. If you have Swap Space disabled completely you do not get this safety buffer and OOM killer will act immediately and kill your database process when you run out of RAM. This really cannot be in the interest of the DBA.

Some literature to read further about Swap: In defence of swap: common misconceptions

Influence swapping - Swappiness

The Linux kernel documentation tells us the following about swappiness:

swappiness

This control is used to define how aggressive the kernel will swap memory pages. Higher values will increase aggressiveness, lower values decrease the amount of swap. A value of 0 instructs the kernel not to initiate swap until the amount of free and file-backed pages is less than the high water mark in a zone.

The default value is 60.

Source: Documentation for /proc/sys/vm/*

A informative article on StackExchange: Why is swappiness set to 60 by default?

To change your swappiness the following commands will help:

# sysctl vm.swappiness vm.swappiness = 60 # sysctl vm.swappiness=1 # sysctl vm.swappiness vm.swappiness = 1

To make these changes persistent you have to write it to some kind of configuration file dependent on your Operating System:

# # /etc/sysctl.d/99-sysctl.conf # vm.swappiness=1
Who is using the Swap Space?

For further analysing your Swap Space and to find who is using your Swap Space please see our article MariaDB and MySQL swap analysis.

What if your system is still swapping? - NUMA!

If you did everything correctly until here and your system is still swapping you possibly missed one point: NUMA systems behave a bit tricky related to Databases and swapping. The first person who wrote extensively about this problem in the MySQL ecosystem was Jeremy Cole in 2010 in his two well written articles which you can find here:

• The MySQL “swap insanity” problem and the effects of the NUMA architecture and
• A brief update on NUMA and MySQL

What NUMA is you can find here: Non-uniform memory access.

If you have spent your money for an expensive NUMA system you can find with the following command:

# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 56 On-line CPU(s) list: 0-55 Thread(s) per core: 2 Core(s) per socket: 14 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2690 v4 @ 2.60GHz Stepping: 1 CPU MHz: 2600.000 CPU max MHz: 2600.0000 CPU min MHz: 1200.0000 BogoMIPS: 5201.37 Virtualization: VT-x Hypervisor vendor: vertical Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 35840K NUMA node0 CPU(s): 0-13,28-41 NUMA node1 CPU(s): 14-27,42-55

If you are now in the unfortunate situation of having such a huge box with several sockets you can do different things:

• Configuring your MariaDB / MySQL database to allocate memory evenly on both sockets with the parameter innodb_numa_interleave. This works since MySQL 5.6.27, MySQL 5.7.9 and MariaDB 10.2.4 but there were various bugs in this area in Debian and CentOS packages (e.g. #80288, #78953, #79354 and MDEV-18660).
• Disable NUMA support in your BIOS (Node Interleaving = enabled). Then there is no NUMA presentation to the Operating System any more.
• Start your MariaDB / MySQL database with numactl --interleave all as described here: MySQL and NUMA.
• Set innodb_buffer_pool_size to 75% of half of your RAM. Sad for having too much of RAM.
• Playing around with the following Linux settings could help to decrease swapping: vm.zone_reclaim_mode=0 and kernel.numa_balancing=0.

Literature

Some further information about Swap Space you can find here:

• An introduction to swap space on Linux systems
• Linux Swap Space
• Linux Kernel Memory Management: Swap Space
• Linux RAM & Swap
• All about Linux swap space
• Swap Management

Taxonomy upgrade extras: swapnumaperformancedatabase

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1 to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz ERROR: /opt/mysql/product/5.7.26/bin/mysqldump command failed (rc=253). mysqldump: [Warning] Using a password on the command line interface can be insecure. Error: Couldn't read status information for table m_report_builder_cache_157_20190521035354 () mysqldump: Couldn't execute 'show create table `m_report_builder_cache_157_20190521035354`': Table 'totara.m_report_builder_cache_157_20190521035354' doesn't exist (1146)

There are various strategies to work around this problem:

• If the table is only temporary create it with the CREATE command as a TEMPORARY TABLE instead of a normal table. This workaround would not work in this case because the table is a caching table which must be available for other connections as well.
• Try to schedule your application job or your bman job in the way they do not collide. With bman that is quite easy but sometimes not with the application.
• Try to create the table in its own schema (e.g. cache) which is excluded from bman backup. So you can easily do a bman backup without the cache schema. For example like this: $ bman --target=brman@127.0.0.1:3306 --type=schema --schema=-cache --policy=daily
  
• If this strategy also does not work (because you cannot change the application behaviour) try to ignore the table. The underlying command mysqldump knows the option --ignore-table: mysqldump --help ... --ignore-table=name Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table. Each table must be specified with both database and table names, e.g., --ignore-table=database.table.
  This option can be used in bman as well. Options to the underlying application are passed through FromDual Backup Manager as follows: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--ignore-table=totara.m_report_builder_cache_157_20190521035354'
  
• The problem here is, that this table contains a timestamp in its table name (20190521035354). So the table name is changing all the time. To pass through wildcards with --ignore-table is not possible with mysqldump. The tool mysqldump does not support (yet) this feature. The only solution we have in this case is, to ignore the error message with the risk that possible other error messages are also ignored. This is achieved again with the --pass-through option: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--force'

I hope with this few tricks we can help you to make your FromDual Backup Manager (bman) backups hassle-free.

Taxonomy upgrade extras: Backuptablebmandroperror

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1 to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz ERROR: /opt/mysql/product/5.7.26/bin/mysqldump command failed (rc=253). mysqldump: [Warning] Using a password on the command line interface can be insecure. Error: Couldn't read status information for table m_report_builder_cache_157_20190521035354 () mysqldump: Couldn't execute 'show create table `m_report_builder_cache_157_20190521035354`': Table 'totara.m_report_builder_cache_157_20190521035354' doesn't exist (1146)

There are various strategies to work around this problem:

• If the table is only temporary create it with the CREATE command as a TEMPORARY TABLE instead of a normal table. This workaround would not work in this case because the table is a caching table which must be available for other connections as well.
• Try to schedule your application job or your bman job in the way they do not collide. With bman that is quite easy but sometimes not with the application.
• Try to create the table in its own schema (e.g. cache) which is excluded from bman backup. So you can easily do a bman backup without the cache schema. For example like this: $ bman --target=brman@127.0.0.1:3306 --type=schema --schema=-cache --policy=daily
  
• If this strategy also does not work (because you cannot change the application behaviour) try to ignore the table. The underlying command mysqldump knows the option --ignore-table: mysqldump --help ... --ignore-table=name Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table. Each table must be specified with both database and table names, e.g., --ignore-table=database.table.
  This option can be used in bman as well. Options to the underlying application are passed through FromDual Backup Manager as follows: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--ignore-table=totara.m_report_builder_cache_157_20190521035354'
  
• The problem here is, that this table contains a timestamp in its table name (20190521035354). So the table name is changing all the time. To pass through wildcards with --ignore-table is not possible with mysqldump. The tool mysqldump does not support (yet) this feature. The only solution we have in this case is, to ignore the error message with the risk that possible other error messages are also ignored. This is achieved again with the --pass-through option: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--force'

I hope with this few tricks we can help you to make your FromDual Backup Manager (bman) backups hassle-free.

Taxonomy upgrade extras: Backuptablebmandroperror

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1 to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz ERROR: /opt/mysql/product/5.7.26/bin/mysqldump command failed (rc=253). mysqldump: [Warning] Using a password on the command line interface can be insecure. Error: Couldn't read status information for table m_report_builder_cache_157_20190521035354 () mysqldump: Couldn't execute 'show create table `m_report_builder_cache_157_20190521035354`': Table 'totara.m_report_builder_cache_157_20190521035354' doesn't exist (1146)

There are various strategies to work around this problem:

• If the table is only temporary create it with the CREATE command as a TEMPORARY TABLE instead of a normal table. This workaround would not work in this case because the table is a caching table which must be available for other connections as well.
• Try to schedule your application job or your bman job in the way they do not collide. With bman that is quite easy but sometimes not with the application.
• Try to create the table in its own schema (e.g. cache) which is excluded from bman backup. So you can easily do a bman backup without the cache schema. For example like this: $ bman --target=brman@127.0.0.1:3306 --type=schema --schema=-cache --policy=daily
  
• If this strategy also does not work (because you cannot change the application behaviour) try to ignore the table. The underlying command mysqldump knows the option --ignore-table: mysqldump --help ... --ignore-table=name Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table. Each table must be specified with both database and table names, e.g., --ignore-table=database.table.
  This option can be used in bman as well. Options to the underlying application are passed through FromDual Backup Manager as follows: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--ignore-table=totara.m_report_builder_cache_157_20190521035354'
  
• The problem here is, that this table contains a timestamp in its table name (20190521035354). So the table name is changing all the time. To pass through wildcards with --ignore-table is not possible with mysqldump. The tool mysqldump does not support (yet) this feature. The only solution we have in this case is, to ignore the error message with the risk that possible other error messages are also ignored. This is achieved again with the --pass-through option: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--force'

I hope with this few tricks we can help you to make your FromDual Backup Manager (bman) backups hassle-free.

Taxonomy upgrade extras: Backuptablebmandroperror

FromDual has the pleasure to announce the release of the new version 0.9.1 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.1

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 or 0.9.0 to 0.9.1

Upgrade from 0.3 or 0.9.0 to 0.9.1 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.1 Upgrade

• Sever upgrade bug fixed which prohibited installation of v0.9.0.

Build and Packaging

• RPM package for RHEL/CentOS 7 is available now.
• DEB package for Ubuntu 18.04 LTS is available now.
• SElinux Policy Package file added.
• COMMIT tag was not replaced correctly during build. This is fixed now.

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestorefocmm

FromDual has the pleasure to announce the release of the new version 0.9.1 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.1

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 or 0.9.0 to 0.9.1

Upgrade from 0.3 or 0.9.0 to 0.9.1 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.1 Upgrade

• Sever upgrade bug fixed which prohibited installation of v0.9.0.

Build and Packaging

• RPM package for RHEL/CentOS 7 is available now.
• DEB package for Ubuntu 18.04 LTS is available now.
• SElinux Policy Package file added.
• COMMIT tag was not replaced correctly during build. This is fixed now.

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestore

FromDual has the pleasure to announce the release of the new version 0.9.1 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.1

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 or 0.9.0 to 0.9.1

Upgrade from 0.3 or 0.9.0 to 0.9.1 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.1 Upgrade

• Sever upgrade bug fixed which prohibited installation of v0.9.0.

Build and Packaging

• RPM package for RHEL/CentOS 7 is available now.
• DEB package for Ubuntu 18.04 LTS is available now.
• SElinux Policy Package file added.
• COMMIT tag was not replaced correctly during build. This is fixed now.

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestore

Caution: We have introduced an evil bug which prohibits installation of focmm. Sorry! Somehow it did pass our QA. To fix this bug update file lib/Upgrade.inc on Line 1965 as follows:

- $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s'), ('%s', '%s')" + $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s')"

In the meanwhile we prepare a new release.

FromDual has the pleasure to announce the release of the new version 0.9 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 to 0.9

Upgrade from 0.3 to 0.9 should happen automatically. Please do a backup of you Ops Center Instance befor you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9

Everything has changed!

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestorefocmmFromDual Ops Center

Caution: We have introduced an evil bug which prohibits installation of focmm. Sorry! Somehow it did pass our QA. To fix this bug update file lib/Upgrade.inc on Line 1965 as follows:

- $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s'), ('%s', '%s')" + $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s')"

In the meanwhile we prepare a new release.

FromDual has the pleasure to announce the release of the new version 0.9 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 to 0.9

Upgrade from 0.3 to 0.9 should happen automatically. Please do a backup of you Ops Center Instance befor you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9

Everything has changed!

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestore

FromDual has the pleasure to announce the release of the new version 0.9 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 to 0.9

Upgrade from 0.3 to 0.9 should happen automatically. Please do a backup of you Ops Center Instance befor you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9

Everything has changed!

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestore

Last week at the MariaDB/MySQL Developer Training we had one participant asking some tricky questions I did not know the answer by heart.

Also MariaDB documentation was not too verbose (here and here).

So time to do some experiments:

Prepared Statements and Multi-Row Inserts SQL> PREPARE stmt1 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli'; SQL> SET @d2 = 'Bla'; SQL> SET @d3 = 'Blub'; SQL> EXECUTE stmt1 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.010 sec) Records: 3 Duplicates: 0 Warnings: 0 SQL> DEALLOCATE PREPARE stmt1; SQL> SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | Bli | 2019-04-15 17:26:22 | | 2 | Bla | 2019-04-15 17:26:22 | | 3 | Blub | 2019-04-15 17:26:22 | +----+------+---------------------+
Prepared Statements and Transactions SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt2 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?)'; Statement prepared SQL> SET @d1 = 'BliTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> SET @d1 = 'BlaTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'BlubTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> ROLLBACK; SQL> DEALLOCATE PREPARE stmt2; SQL> SELECT * FROM test; +----+---------+---------------------+ | id | data | ts | +----+---------+---------------------+ | 10 | BliTrx | 2019-04-15 17:33:30 | | 11 | BlaTrx | 2019-04-15 17:33:39 | +----+---------+---------------------+
Prepared Statements and Transactions and Multi-Row Inserts SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt3 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli1Trx'; SQL> SET @d2 = 'Bla1Trx'; SQL> SET @d3 = 'Blub1Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli2Trx'; SQL> SET @d2 = 'Bla2Trx'; SQL> SET @d3 = 'Blub2Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> ROLLBACK; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli3Trx'; SQL> SET @d2 = 'Bla3Trx'; SQL> SET @d3 = 'Blub3Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.001 sec) SQL> COMMIT; SQL> DEALLOCATE PREPARE stmt3; SQL> SELECT * FROM test; +----+----------+---------------------+ | id | data | ts | +----+----------+---------------------+ | 1 | Bli1Trx | 2019-04-15 17:37:50 | | 2 | Bla1Trx | 2019-04-15 17:37:50 | | 3 | Blub1Trx | 2019-04-15 17:37:50 | | 7 | Bli3Trx | 2019-04-15 17:38:38 | | 8 | Bla3Trx | 2019-04-15 17:38:38 | | 9 | Blub3Trx | 2019-04-15 17:38:38 | +----+----------+---------------------+

Seems all to work as expected. Now we know it for sure!

Taxonomy upgrade extras: transactioninsertprepared statementsmulti-row insert

Last week at the MariaDB/MySQL Developer Training we had one participant asking some tricky questions I did not know the answer by heart.

Also MariaDB documentation was not too verbose (here and here).

So time to do some experiments:

Prepared Statements and Multi-Row Inserts SQL> PREPARE stmt1 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli'; SQL> SET @d2 = 'Bla'; SQL> SET @d3 = 'Blub'; SQL> EXECUTE stmt1 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.010 sec) Records: 3 Duplicates: 0 Warnings: 0 SQL> DEALLOCATE PREPARE stmt1; SQL> SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | Bli | 2019-04-15 17:26:22 | | 2 | Bla | 2019-04-15 17:26:22 | | 3 | Blub | 2019-04-15 17:26:22 | +----+------+---------------------+
Prepared Statements and Transactions SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt2 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?)'; Statement prepared SQL> SET @d1 = 'BliTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> SET @d1 = 'BlaTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'BlubTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> ROLLBACK; SQL> DEALLOCATE PREPARE stmt2; SQL> SELECT * FROM test; +----+---------+---------------------+ | id | data | ts | +----+---------+---------------------+ | 10 | BliTrx | 2019-04-15 17:33:30 | | 11 | BlaTrx | 2019-04-15 17:33:39 | +----+---------+---------------------+
Prepared Statements and Transactions and Multi-Row Inserts SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt3 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli1Trx'; SQL> SET @d2 = 'Bla1Trx'; SQL> SET @d3 = 'Blub1Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli2Trx'; SQL> SET @d2 = 'Bla2Trx'; SQL> SET @d3 = 'Blub2Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> ROLLBACK; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli3Trx'; SQL> SET @d2 = 'Bla3Trx'; SQL> SET @d3 = 'Blub3Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.001 sec) SQL> COMMIT; SQL> DEALLOCATE PREPARE stmt3; SQL> SELECT * FROM test; +----+----------+---------------------+ | id | data | ts | +----+----------+---------------------+ | 1 | Bli1Trx | 2019-04-15 17:37:50 | | 2 | Bla1Trx | 2019-04-15 17:37:50 | | 3 | Blub1Trx | 2019-04-15 17:37:50 | | 7 | Bli3Trx | 2019-04-15 17:38:38 | | 8 | Bla3Trx | 2019-04-15 17:38:38 | | 9 | Blub3Trx | 2019-04-15 17:38:38 | +----+----------+---------------------+

Seems all to work as expected. Now we know it for sure!

Taxonomy upgrade extras: transactioninsertprepared statementsmulti-row insert

Last week at the MariaDB/MySQL Developer Training we had one participant asking some tricky questions I did not know the answer by heart.

Also MariaDB documentation was not too verbose (here and here).

So time to do some experiments:

Prepared Statements and Multi-Row Inserts SQL> PREPARE stmt1 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli'; SQL> SET @d2 = 'Bla'; SQL> SET @d3 = 'Blub'; SQL> EXECUTE stmt1 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.010 sec) Records: 3 Duplicates: 0 Warnings: 0 SQL> DEALLOCATE PREPARE stmt1; SQL> SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | Bli | 2019-04-15 17:26:22 | | 2 | Bla | 2019-04-15 17:26:22 | | 3 | Blub | 2019-04-15 17:26:22 | +----+------+---------------------+
Prepared Statements and Transactions SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt2 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?)'; Statement prepared SQL> SET @d1 = 'BliTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> SET @d1 = 'BlaTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'BlubTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> ROLLBACK; SQL> DEALLOCATE PREPARE stmt2; SQL> SELECT * FROM test; +----+---------+---------------------+ | id | data | ts | +----+---------+---------------------+ | 10 | BliTrx | 2019-04-15 17:33:30 | | 11 | BlaTrx | 2019-04-15 17:33:39 | +----+---------+---------------------+
Prepared Statements and Transactions and Multi-Row Inserts SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt3 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli1Trx'; SQL> SET @d2 = 'Bla1Trx'; SQL> SET @d3 = 'Blub1Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli2Trx'; SQL> SET @d2 = 'Bla2Trx'; SQL> SET @d3 = 'Blub2Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> ROLLBACK; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli3Trx'; SQL> SET @d2 = 'Bla3Trx'; SQL> SET @d3 = 'Blub3Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.001 sec) SQL> COMMIT; SQL> DEALLOCATE PREPARE stmt3; SQL> SELECT * FROM test; +----+----------+---------------------+ | id | data | ts | +----+----------+---------------------+ | 1 | Bli1Trx | 2019-04-15 17:37:50 | | 2 | Bla1Trx | 2019-04-15 17:37:50 | | 3 | Blub1Trx | 2019-04-15 17:37:50 | | 7 | Bli3Trx | 2019-04-15 17:38:38 | | 8 | Bla3Trx | 2019-04-15 17:38:38 | | 9 | Blub3Trx | 2019-04-15 17:38:38 | +----+----------+---------------------+

Seems all to work as expected. Now we know it for sure!

Taxonomy upgrade extras: transactioninsertprepared statementsmulti-row insert

A while ago somebody on Google Groups asked for the Uptime of a Galera Cluster. The answer is easy... Wait, no! Not so easy... The uptime of a Galera Node is easy (or not?). But Uptime of the whole Galera Cluster?

My answer then was: "Grep the error log." My answer now is still: "Grep the error log." But slightly different:

$ grep 'view(view_id' * 2019-03-07 16:10:26 [Note] WSREP: view(view_id(PRIM,0e0a2851,1) memb { 2019-03-07 16:14:37 [Note] WSREP: view(view_id(PRIM,0e0a2851,2) memb { 2019-03-07 16:16:23 [Note] WSREP: view(view_id(PRIM,0e0a2851,3) memb { 2019-03-07 16:55:56 [Note] WSREP: view(view_id(NON_PRIM,0e0a2851,3) memb { 2019-03-07 16:56:04 [Note] WSREP: view(view_id(PRIM,6d80bb1a,5) memb { 2019-03-07 17:00:28 [Note] WSREP: view(view_id(NON_PRIM,6d80bb1a,5) memb { 2019-03-07 17:01:11 [Note] WSREP: view(view_id(PRIM,24f67954,7) memb { 2019-03-07 17:18:58 [Note] WSREP: view(view_id(NON_PRIM,24f67954,7) memb { 2019-03-07 17:19:31 [Note] WSREP: view(view_id(PRIM,a380c8cb,9) memb { 2019-03-07 17:20:27 [Note] WSREP: view(view_id(PRIM,a380c8cb,11) memb { 2019-03-08 7:58:38 [Note] WSREP: view(view_id(PRIM,753a350f,15) memb { 2019-03-08 11:31:38 [Note] WSREP: view(view_id(NON_PRIM,753a350f,15) memb { 2019-03-08 11:31:43 [Note] WSREP: view(view_id(PRIM,489e3c67,17) memb { 2019-03-08 11:31:58 [Note] WSREP: view(view_id(PRIM,489e3c67,18) memb { ... 2019-03-22 7:05:53 [Note] WSREP: view(view_id(NON_PRIM,49dc20da,49) memb { 2019-03-22 7:05:53 [Note] WSREP: view(view_id(PRIM,49dc20da,50) memb { 2019-03-26 12:14:05 [Note] WSREP: view(view_id(NON_PRIM,49dc20da,50) memb { 2019-03-27 7:33:25 [Note] WSREP: view(view_id(NON_PRIM,22ae25aa,1) memb {

So this Cluster had an Uptime of about 18 days and 20 hours. Why can I seed this? Simple: In the brackets there is a number at the very right. This number seems to be the same as wsrep_cluster_conf_id which is reset by a full Galera Cluster shutdown.

So far so good. But, wait, what is the definition of Uptime? Hmmm, not so helpful, how should I interpret this for a 3-Node Galera Cluster?

I would say a good definition for Uptime of a Galera Cluster would be: "At least one Galera Node must be available for the application for reading and writing." That means PRIM in the output above. And we still cannot say from the output above if there was at least on Galera Node available (reading and writing) at any time. For this we have to compare ALL 3 MariaDB Error Logs... So it does not help, we need a good Monitoring solution to answer this question...

PS: Who has found the little fake in this blog?

Taxonomy upgrade extras: galera clusteruptime

A while ago somebody on Google Groups asked for the Uptime of a Galera Cluster. The answer is easy... Wait, no! Not so easy... The uptime of a Galera Node is easy (or not?). But Uptime of the whole Galera Cluster?

My answer then was: "Grep the error log." My answer now is still: "Grep the error log." But slightly different:

$ grep 'view(view_id' * 2019-03-07 16:10:26 [Note] WSREP: view(view_id(PRIM,0e0a2851,1) memb { 2019-03-07 16:14:37 [Note] WSREP: view(view_id(PRIM,0e0a2851,2) memb { 2019-03-07 16:16:23 [Note] WSREP: view(view_id(PRIM,0e0a2851,3) memb { 2019-03-07 16:55:56 [Note] WSREP: view(view_id(NON_PRIM,0e0a2851,3) memb { 2019-03-07 16:56:04 [Note] WSREP: view(view_id(PRIM,6d80bb1a,5) memb { 2019-03-07 17:00:28 [Note] WSREP: view(view_id(NON_PRIM,6d80bb1a,5) memb { 2019-03-07 17:01:11 [Note] WSREP: view(view_id(PRIM,24f67954,7) memb { 2019-03-07 17:18:58 [Note] WSREP: view(view_id(NON_PRIM,24f67954,7) memb { 2019-03-07 17:19:31 [Note] WSREP: view(view_id(PRIM,a380c8cb,9) memb { 2019-03-07 17:20:27 [Note] WSREP: view(view_id(PRIM,a380c8cb,11) memb { 2019-03-08 7:58:38 [Note] WSREP: view(view_id(PRIM,753a350f,15) memb { 2019-03-08 11:31:38 [Note] WSREP: view(view_id(NON_PRIM,753a350f,15) memb { 2019-03-08 11:31:43 [Note] WSREP: view(view_id(PRIM,489e3c67,17) memb { 2019-03-08 11:31:58 [Note] WSREP: view(view_id(PRIM,489e3c67,18) memb { ... 2019-03-22 7:05:53 [Note] WSREP: view(view_id(NON_PRIM,49dc20da,49) memb { 2019-03-22 7:05:53 [Note] WSREP: view(view_id(PRIM,49dc20da,50) memb { 2019-03-26 12:14:05 [Note] WSREP: view(view_id(NON_PRIM,49dc20da,50) memb { 2019-03-27 7:33:25 [Note] WSREP: view(view_id(NON_PRIM,22ae25aa,1) memb {

So this Cluster had an Uptime of about 18 days and 20 hours. Why can I seed this? Simple: In the brackets there is a number at the very right. This number seems to be the same as wsrep_cluster_conf_id which is reset by a full Galera Cluster shutdown.

So far so good. But, wait, what is the definition of Uptime? Hmmm, not so helpful, how should I interpret this for a 3-Node Galera Cluster?

I would say a good definition for Uptime of a Galera Cluster would be: "At least one Galera Node must be available for the application for reading and writing." That means PRIM in the output above. And we still cannot say from the output above if there was at least on Galera Node available (reading and writing) at any time. For this we have to compare ALL 3 MariaDB Error Logs... So it does not help, we need a good Monitoring solution to answer this question...

PS: Who has found the little fake in this blog?

Taxonomy upgrade extras: galera clusteruptime