MySQL Bugs: #85971: New undo files are created after each restart with keeping old files

Bug #85971	New undo files are created after each restart with keeping old files
Submitted:	17 Apr 2017 9:00	Modified:	12 Jun 2017 18:57
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	8.0.1	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi dear all,
Here is the situation where it is quite weird for me.

Started MySQL with following settings:

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/home/shahriyar.rzaev/sandboxes/msb_8_0_1/tmp/keyring

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=77
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

Then just want to restart and restarted several times and noticed that the undo files are growing and old files are also kept.

2017-04-17T08:34:26.906480Z 1 [Note] InnoDB: Setting file .//undo619 size to 10 MB
2017-04-17T08:34:26.906483Z 1 [Note] InnoDB: Physically writing the file full
2017-04-17T08:34:27.023674Z 1 [Note] InnoDB: Undo log encryption is enabled.
2017-04-17T08:34:27.023752Z 1 [Note] InnoDB: Creating UNDO Tablespace Data file .//undo620
2017-04-17T08:34:27.023760Z 1 [Note] InnoDB: Setting file .//undo620 size to 10 MB
2017-04-17T08:34:27.023763Z 1 [Note] InnoDB: Physically writing the file full
2017-04-17T08:34:27.157344Z 1 [Note] InnoDB: Undo log encryption is enabled.
2017-04-17T08:34:27.157423Z 1 [Note] InnoDB: Creating UNDO Tablespace Data file .//undo621
2017-04-17T08:34:27.157431Z 1 [Note] InnoDB: Setting file .//undo621 size to 10 MB
2017-04-17T08:34:27.157434Z 1 [Note] InnoDB: Physically writing the file full
2017-04-17T08:34:27.274359Z 1 [Note] InnoDB: Undo log encryption is enabled.
2017-04-17T08:34:27.274413Z 1 [Note] InnoDB: Created 77 undo tablespaces.
2017-04-17T08:34:27.274421Z 1 [Note] InnoDB: Opened 77 undo tablespaces
2017-04-17T08:34:27.274424Z 1 [Note] InnoDB: 77 undo tablespaces made active

The datadir:

shahriyar.rzaev@qaserver-06:~/sandboxes/msb_8_0_1/data$ du -hs
6.4G	

shahriyar.rzaev@qaserver-06:~/sandboxes/msb_8_0_1/data$ ls | grep undo | wc -l
539

After new restart:

shahriyar.rzaev@qaserver-06:~/sandboxes/msb_8_0_1/data$ ls | grep undo | wc -l
616

How to repeat:
See the description.

Hello Shahriyar,

Thank you for the report.
I'm not seeing the reported issue even after multiple restarts etc, could you please provide exact conf file, OS details (other than the details available in the report if any), start up command, also details such as any operation performed in between restart etc

Thanks,
Umesh

Hi Umesh, yes it seems to be I have missed some valuable info there.

The full config:

[mysql]
prompt='mysql [\h] {\u} (\d) > '
#

[client]
user               = msandbox
password           = msandbox
port               = 8001
socket             = /tmp/mysql_sandbox8001.sock

[mysqld]
user               = shahriyar.rzaev
port               = 8001
socket             = /tmp/mysql_sandbox8001.sock
basedir            = /home/shahriyar.rzaev/Percona_Servers/8.0.1
datadir            = /home/shahriyar.rzaev/sandboxes/msb_8_0_1/data
tmpdir             = /home/shahriyar.rzaev/sandboxes/msb_8_0_1/tmp
lower_case_table_names = 0
pid-file           = /home/shahriyar.rzaev/sandboxes/msb_8_0_1/data/mysql_sandbox8001.pid
bind-address       = 127.0.0.1
# slow-query-log = on
# slow-query-log-file=/home/shahriyar.rzaev/sandboxes/msb_8_0_1/data/msandbox-slow.log
# general-log = on
# general-log-file=/home/shahriyar.rzaev/sandboxes/msb_8_0_1/data/msandbox-general.log
#
# additional options passed through 'my_clause' 
#
log-error=msandbox.err

early-plugin-load=keyring_file.so
keyring_file_data=/home/shahriyar.rzaev/sandboxes/msb_8_0_1/tmp/keyring

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=77
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

So what I did to reproduce this:

1. Started server with:

early-plugin-load=keyring_file.so
keyring_file_data=/home/shahriyar.rzaev/sandboxes/msb_8_0_1/tmp/keyring

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=77
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

2. then killed it with -9 and got the error:

2017-05-08T08:54:07.174124Z 1 [ERROR] InnoDB: Expected to open 77 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0
2017-05-08T08:54:07.174147Z 1 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-05-08T08:54:07.674794Z 1 [ERROR] Failed to initialize DD Storage Engine
2017-05-08T08:54:07.674922Z 0 [ERROR] Data Dictionary initialization failed.
2017-05-08T08:54:07.674932Z 0 [ERROR] Aborting

3. To overcome this issue I have changed config as:

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=0
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

And it is started as expected

4. Then changed back the config:

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=77
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

It will create new undo files. After that if you will restart the server, it will create new files each time.

Thank you for the feedback.
I'm able to reproduce the issue once reaching the scenario in Bug #85969 (which is expected when innodb_undo_tablespaces reconfigured afterward).
Observed that, for each restart undo* files are increasing.

8.0.1 - test results

Attachment: 85971.results (application/octet-stream, text), 33.79 KiB.

Fixed in the upcoming 8.0.2 release.