[Maria-discuss] Row size too large
after upgrade the first testserver to 10.0.15: InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size. * previously no problems * stopped server * increased innodb_log_file_size to 128MB * deleted ib_logfile* * started server * the same again why - that's only a small test-vm with not much data? ____________________________________________ ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs MariaDB [dbmail]> show create table dbmail_mimeparts; +------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | dbmail_mimeparts | CREATE TABLE `dbmail_mimeparts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `hash` char(128) NOT NULL, `data` longblob NOT NULL, `size` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `hash` (`hash`) ) ENGINE=InnoDB AUTO_INCREMENT=43391 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED | +------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
It seems that the limitation has been introduced on MySQL 5.6.20 : http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html Le 20/01/2015 01:03, Reindl Harald a écrit :
InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size.
WTF: "the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables" - how is that maintainable for a sysadmin? the whole (file_per_table) database folder is 129 MB, innodb_log_file_sizeis 128 MB and nobody can seriously explain me that i need a innodb_log_file_size with magnitudes of the whole datasize Am 20.01.2015 um 03:04 schrieb Jean Weisbuch:
It seems that the limitation has been introduced on MySQL 5.6.20 : http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html
Le 20/01/2015 01:03, Reindl Harald a écrit :
InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size
Hello, Since I was the author of the bug this addressed, MySQL Bug 69477 <http://bugs.mysql.com/bug.php?id=69477> I could comment here. The InnoDB redo log (innodb_log_file_size) needs to be at least 10x larger than the largest single BLOB value you intend to store, not larger than the sum of BLOB data. If you are seeing this error with a log file size of 128 MB, that implies that you have some BLOB column containing at least 12.8 MB. Is that the case? Your use is probably quite strange if your total data size is only 129 MB and you have BLOBs of that size, but be aware: this bug fix was for a very serious bug. With the previous behavior usage of such large BLOBs could result in silent and unrecoverable data loss after a crash due to overwriting the most recent checkpoint with oversized BLOB data. My understanding is that the bug was addressed in 5.7 without introducing a limitation. Regards, Jeremy On Tue, Jan 20, 2015 at 1:22 AM, Reindl Harald <h.reindl@thelounge.net> wrote:
WTF: "the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables" - how is that maintainable for a sysadmin?
the whole (file_per_table) database folder is 129 MB, innodb_log_file_sizeis 128 MB and nobody can seriously explain me that i need a innodb_log_file_size with magnitudes of the whole datasize
Am 20.01.2015 um 03:04 schrieb Jean Weisbuch:
It seems that the limitation has been introduced on MySQL 5.6.20 :
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html
Le 20/01/2015 01:03, Reindl Harald a écrit :
InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 20.01.2015 um 20:53 schrieb Jeremy Cole:
Since I was the author of the bug this addressed, MySQL Bug 69477 <http://bugs.mysql.com/bug.php?id=69477> I could comment here.
The InnoDB redo log (innodb_log_file_size) needs to be at least 10x larger than the largest single BLOB value you intend to store, not larger than the sum of BLOB data. If you are seeing this error with a log file size of 128 MB, that implies that you have some BLOB column containing at least 12.8 MB. Is that the case? Your use is probably quite strange if your total data size is only 129 MB and you have BLOBs of that size, but be aware: this bug fix was for a very serious bug. With the previous behavior usage of such large BLOBs could result in silent and unrecoverable data loss after a crash due to overwriting the most recent checkpoint with oversized BLOB data.
this is a tiny dbmail-testserver there are a few testmessages, the largest one is 8.0 MB on the other hand we support up to 35 MB mail size the whole "dbmail_mimeparts" is around 90 MB so *no*, i don't get any reason why i need > 128 MB "innodb_log_file_size" for a "optimize table" AKA re-create no idea what that possibly implies on a server storing larger files for attachments in a web-app to "passthru" them with a PHP application because the current error leads to make "innodb_log_file_size" very large and clearly waste storage on a virtualized environment ending innodb logs larger then the whole dataset what i *really* hate in that behavior change is that you can't happily change that config var at all on a production server and the idea to increase it for safety to huge values larger then the data itself
My understanding is that the bug was addressed in 5.7 without introducing a limitation.
On Tue, Jan 20, 2015 at 1:22 AM, Reindl Harald wrote:
WTF: "the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables" - how is that maintainable for a sysadmin?
the whole (file_per_table) database folder is 129 MB, innodb_log_file_size is 128 MB and nobody can seriously explain me that i need a innodb_log_file_size with magnitudes of the whole datasize
Am 20.01.2015 um 03:04 schrieb Jean Weisbuch:
It seems that the limitation has been introduced on MySQL 5.6.20 : http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-__6-20.html <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html>
Le 20/01/2015 01:03, Reindl Harald a écrit :
InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size
If you don't care about recoverability (which is kind of what you're saying here), you could always use MyISAM... Regards, Jeremy On Tue, Jan 20, 2015 at 12:05 PM, Reindl Harald <h.reindl@thelounge.net> wrote:
Am 20.01.2015 um 20:53 schrieb Jeremy Cole:
Since I was the author of the bug this addressed, MySQL Bug 69477 <http://bugs.mysql.com/bug.php?id=69477> I could comment here.
The InnoDB redo log (innodb_log_file_size) needs to be at least 10x larger than the largest single BLOB value you intend to store, not larger than the sum of BLOB data. If you are seeing this error with a log file size of 128 MB, that implies that you have some BLOB column containing at least 12.8 MB. Is that the case? Your use is probably quite strange if your total data size is only 129 MB and you have BLOBs of that size, but be aware: this bug fix was for a very serious bug. With the previous behavior usage of such large BLOBs could result in silent and unrecoverable data loss after a crash due to overwriting the most recent checkpoint with oversized BLOB data.
this is a tiny dbmail-testserver
there are a few testmessages, the largest one is 8.0 MB on the other hand we support up to 35 MB mail size
the whole "dbmail_mimeparts" is around 90 MB
so *no*, i don't get any reason why i need > 128 MB "innodb_log_file_size" for a "optimize table" AKA re-create
no idea what that possibly implies on a server storing larger files for attachments in a web-app to "passthru" them with a PHP application because the current error leads to make "innodb_log_file_size" very large and clearly waste storage on a virtualized environment ending innodb logs larger then the whole dataset
what i *really* hate in that behavior change is that you can't happily change that config var at all on a production server and the idea to increase it for safety to huge values larger then the data itself
My understanding is that the bug was addressed in 5.7 without
introducing a limitation.
On Tue, Jan 20, 2015 at 1:22 AM, Reindl Harald wrote:
WTF: "the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables" - how is that maintainable for a sysadmin?
the whole (file_per_table) database folder is 129 MB, innodb_log_file_size is 128 MB and nobody can seriously explain me that i need a innodb_log_file_size with magnitudes of the whole datasize
Am 20.01.2015 um 03:04 schrieb Jean Weisbuch:
It seems that the limitation has been introduced on MySQL 5.6.20 : http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-_ _6-20.html <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html>
Le 20/01/2015 01:03, Reindl Harald a écrit :
InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 20.01.2015 um 21:17 schrieb Jeremy Cole:
If you don't care about recoverability (which is kind of what you're saying here), you could always use MyISAM...
no you can't use MyISAM for DBMail but the point is that the affected table itself has 90 MB, the log file size is 128 MB, the largest message is below 10 MB and so there is no valif reason at all to need more than 128 MB log file size with 256 MB it works, fine given that there are 2 innodb logs that means for one of many tiny VM's 256 MB overhead at a way smaller dataset in other words: given the numbers above 128 MB is for sure enough, the largest message is 8 MB x 10 = 80 MB - so why would the logfiles need to be 256 MB large?
On Tue, Jan 20, 2015 at 12:05 PM, Reindl Harald wrote:
Am 20.01.2015 um 20:53 schrieb Jeremy Cole:
Since I was the author of the bug this addressed, MySQL Bug 69477 <http://bugs.mysql.com/bug.__php?id=69477 <http://bugs.mysql.com/bug.php?id=69477>> I could comment here.
The InnoDB redo log (innodb_log_file_size) needs to be at least 10x larger than the largest single BLOB value you intend to store, not larger than the sum of BLOB data. If you are seeing this error with a log file size of 128 MB, that implies that you have some BLOB column containing at least 12.8 MB. Is that the case? Your use is probably quite strange if your total data size is only 129 MB and you have BLOBs of that size, but be aware: this bug fix was for a very serious bug. With the previous behavior usage of such large BLOBs could result in silent and unrecoverable data loss after a crash due to overwriting the most recent checkpoint with oversized BLOB data.
this is a tiny dbmail-testserver
there are a few testmessages, the largest one is 8.0 MB on the other hand we support up to 35 MB mail size
the whole "dbmail_mimeparts" is around 90 MB
so *no*, i don't get any reason why i need > 128 MB "innodb_log_file_size" for a "optimize table" AKA re-create
no idea what that possibly implies on a server storing larger files for attachments in a web-app to "passthru" them with a PHP application because the current error leads to make "innodb_log_file_size" very large and clearly waste storage on a virtualized environment ending innodb logs larger then the whole dataset
what i *really* hate in that behavior change is that you can't happily change that config var at all on a production server and the idea to increase it for safety to huge values larger then the data itself
My understanding is that the bug was addressed in 5.7 without introducing a limitation.
On Tue, Jan 20, 2015 at 1:22 AM, Reindl Harald wrote:
WTF: "the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables" - how is that maintainable for a sysadmin?
the whole (file_per_table) database folder is 129 MB, innodb_log_file_size is 128 MB and nobody can seriously explain me that i need a innodb_log_file_size with magnitudes of the whole datasize
Am 20.01.2015 um 03:04 schrieb Jean Weisbuch:
It seems that the limitation has been introduced on MySQL 5.6.20 : http://dev.mysql.com/doc/____relnotes/mysql/5.6/en/news-5-____6-20.html <http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-__6-20.html>
<http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-__6-20.html <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html>>
Le 20/01/2015 01:03, Reindl Harald a écrit :
InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size
It looks like MariaDB has ported from a version of 5.6 with a buggy implementation of this fix, the 5.6.22 changelog notes: InnoDB: The MySQL 5.6.20 patch for Bug #16963396 / MySQL Bug #69477 limited
the size of redo log BLOB writes to 10% of the redo log file size. This limitation has been relaxed. Redo log BLOB writes are now limited to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group).
As a result, innodb_log_file_size * innodb_log_files_in_group should be 10
times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if innodb_log_file_size * innodb_log_files_in_group is already sufficiently large or if your tables contain no BLOB data. (Bug #73707, Bug #19498877)
On Tue, Jan 20, 2015 at 12:24 PM, Reindl Harald <h.reindl@thelounge.net> wrote:
Am 20.01.2015 um 21:17 schrieb Jeremy Cole:
If you don't care about recoverability (which is kind of what you're saying here), you could always use MyISAM...
no you can't use MyISAM for DBMail
but the point is that the affected table itself has 90 MB, the log file size is 128 MB, the largest message is below 10 MB and so there is no valif reason at all to need more than 128 MB log file size
with 256 MB it works, fine given that there are 2 innodb logs that means for one of many tiny VM's 256 MB overhead at a way smaller dataset
in other words: given the numbers above 128 MB is for sure enough, the largest message is 8 MB x 10 = 80 MB - so why would the logfiles need to be 256 MB large?
On Tue, Jan 20, 2015 at 12:05 PM, Reindl Harald wrote:
Am 20.01.2015 um 20:53 schrieb Jeremy Cole:
Since I was the author of the bug this addressed, MySQL Bug 69477 <http://bugs.mysql.com/bug.__php?id=69477
<http://bugs.mysql.com/bug.php?id=69477>> I could comment here.
The InnoDB redo log (innodb_log_file_size) needs to be at least 10x larger than the largest single BLOB value you intend to store, not larger than the sum of BLOB data. If you are seeing this error with a log file size of 128 MB, that implies that you have some BLOB column containing at least 12.8 MB. Is that the case? Your use is probably quite strange if your total data size is only 129 MB and you have BLOBs of that size, but be aware: this bug fix was for a very serious bug. With the previous behavior usage of such large BLOBs could result in silent and unrecoverable data loss after a crash due to overwriting the most recent checkpoint with oversized BLOB data.
this is a tiny dbmail-testserver
there are a few testmessages, the largest one is 8.0 MB on the other hand we support up to 35 MB mail size
the whole "dbmail_mimeparts" is around 90 MB
so *no*, i don't get any reason why i need > 128 MB "innodb_log_file_size" for a "optimize table" AKA re-create
no idea what that possibly implies on a server storing larger files for attachments in a web-app to "passthru" them with a PHP application because the current error leads to make "innodb_log_file_size" very large and clearly waste storage on a virtualized environment ending innodb logs larger then the whole dataset
what i *really* hate in that behavior change is that you can't happily change that config var at all on a production server and the idea to increase it for safety to huge values larger then the data itself
My understanding is that the bug was addressed in 5.7 without introducing a limitation.
On Tue, Jan 20, 2015 at 1:22 AM, Reindl Harald wrote:
WTF: "the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables" - how is that maintainable for a sysadmin?
the whole (file_per_table) database folder is 129 MB, innodb_log_file_size is 128 MB and nobody can seriously explain me that i need a innodb_log_file_size with magnitudes of the whole datasize
Am 20.01.2015 um 03:04 schrieb Jean Weisbuch:
It seems that the limitation has been introduced on MySQL 5.6.20 : http://dev.mysql.com/doc/____relnotes/mysql/5.6/en/news-5-_ ___6-20.html <http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-_ _6-20.html>
<http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-_ _6-20.html <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html
Le 20/01/2015 01:03, Reindl Harald a écrit :
InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, Jeremy! On Jan 20, Jeremy Cole wrote:
It looks like MariaDB has ported from a version of 5.6 with a buggy implementation of this fix, the 5.6.22 changelog notes:
InnoDB: The MySQL 5.6.20 patch for Bug #16963396 / MySQL Bug #69477 limited the size of redo log BLOB writes to 10% of the redo log file size. This limitation has been relaxed. Redo log BLOB writes are now limited to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group).
As a result, innodb_log_file_size * innodb_log_files_in_group should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if innodb_log_file_size * innodb_log_files_in_group is already sufficiently large or if your tables contain no BLOB data. (Bug #73707, Bug #19498877)
Yes, I believe you're right. 10.0.15 had InnoDB as of 5.6.21. Now as 5.6.22 is released, it'll be in 10.0.16. (it actually already *is* in 10.0.16, depending on when you read it, 10.0.16 might be already announced) Regards, Sergei
participants (4)
-
Jean Weisbuch -
Jeremy Cole -
Reindl Harald -
Sergei Golubchik