Description:
Hello,
I have experienced and reproduced a Memory leak on MySQL 5.6 with InnoDB:
When a transaction exceeds 10% of the total redo log size, then an error is sent to the client, but memory increases and never releases.
Error message in the session:
ERROR 1118 (42000) at line 1: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
Error message in mysql error log:
2017-04-17 08:52:00 31718 [ERROR] InnoDB: The total blob data length (16774400) is greater than 10% of the total redo log size (100663296). Please increase total redo log size.
The problem disappears when innodb log file size is increased.
Tested with self-compiled and official binaries (tarball).
I could not reproduce with 5.7.
Let me know if more testing is needed,
Thanks for checking!
Regards,
Joffrey
How to repeat:
Start MySQL with default options and --max_allowed_packet=16M
To reproduce faster, 4 sessions have been used. Each session uses a different transaction size.
# Session 1 (MySQL):
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE test.`wp_options` (
`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`option_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`option_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`autoload` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
PRIMARY KEY (`option_id`),
UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB;
INSERT INTO test.wp_options VALUES (1,'option1','not yet',0);
INSERT INTO test.wp_options VALUES (2,'option2','not yet',0);
INSERT INTO test.wp_options VALUES (3,'option3','not yet',0);
INSERT INTO test.wp_options VALUES (4,'option4','not yet',0);
# Session 1 (Shell):
while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16177216) where option_id=1"; done
# Session 2 (Shell):
while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16277216) where option_id=1"; done
# Session 3 (Shell):
while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16377216) where option_id=1"; done
# Session 4 (Shell):
while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16477216) where option_id=1"; done
Watch mysql error log, and memory usage.
Suggested fix:
InnoDB Redo log files should be configured properly, but still lot of users are running production systems with default values.
When experiencing this error, memory should be released the same way as when it is successful.
Description: Hello, I have experienced and reproduced a Memory leak on MySQL 5.6 with InnoDB: When a transaction exceeds 10% of the total redo log size, then an error is sent to the client, but memory increases and never releases. Error message in the session: ERROR 1118 (42000) at line 1: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size. Error message in mysql error log: 2017-04-17 08:52:00 31718 [ERROR] InnoDB: The total blob data length (16774400) is greater than 10% of the total redo log size (100663296). Please increase total redo log size. The problem disappears when innodb log file size is increased. Tested with self-compiled and official binaries (tarball). I could not reproduce with 5.7. Let me know if more testing is needed, Thanks for checking! Regards, Joffrey How to repeat: Start MySQL with default options and --max_allowed_packet=16M To reproduce faster, 4 sessions have been used. Each session uses a different transaction size. # Session 1 (MySQL): CREATE DATABASE IF NOT EXISTS test; CREATE TABLE test.`wp_options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '', `option_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `autoload` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`) ) ENGINE=InnoDB; INSERT INTO test.wp_options VALUES (1,'option1','not yet',0); INSERT INTO test.wp_options VALUES (2,'option2','not yet',0); INSERT INTO test.wp_options VALUES (3,'option3','not yet',0); INSERT INTO test.wp_options VALUES (4,'option4','not yet',0); # Session 1 (Shell): while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16177216) where option_id=1"; done # Session 2 (Shell): while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16277216) where option_id=1"; done # Session 3 (Shell): while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16377216) where option_id=1"; done # Session 4 (Shell): while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16477216) where option_id=1"; done Watch mysql error log, and memory usage. Suggested fix: InnoDB Redo log files should be configured properly, but still lot of users are running production systems with default values. When experiencing this error, memory should be released the same way as when it is successful.