Upgrade to 3.3.2 fails
I've just upgraded python to 3.7, so now I can upgrade mailman from 3.3.1 to 3.3.2. Take it down, "pip install --upgrade mailman", and try a "mailman info". It fails hard, and what I think are the important parts of the stack trace are:
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1118, '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') [SQL: ALTER TABLE mailinglist ADD COLUMN archive_rendering_mode INTEGER] (Background on this error at: http://sqlalche.me/e/13/e3q8)
Looks to me like the mailinglist table has been using awfully close to the default mysql max row length, and this added column pushes me over? Is there some other fix other than a terrifyingly gigantic process of dumping all my databases, changing my page size, and restoring them all? This DB server isn't serving just mailman...
Thanks
-- Joel Lord
I am also using MySQL and successfully upgraded to 3.3.2, the additional column was successfully added.
See this link: https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/column-count-limit....
MySQL-Workbenchs shows me an innodb_page_size of 16384, so my guess is why you are hit by this limit and I am not is, that you might be using utf8mb4 where my DB is still on utf8!?
I see a lot (20 if I counted right) columns with VARCHAR(255) and 4 with VARCHAR(510) in table "mailinglist". Feels to me that this could bring the rows near or above to the limit.
But these are just rough guesses.
Kind regards Torge
Am 26.12.20 um 18:58 schrieb Joel Lord:
I've just upgraded python to 3.7, so now I can upgrade mailman from 3.3.1 to 3.3.2. Take it down, "pip install --upgrade mailman", and try a "mailman info". It fails hard, and what I think are the important parts of the stack trace are:
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1118, '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') [SQL: ALTER TABLE mailinglist ADD COLUMN archive_rendering_mode INTEGER] (Background on this error at: http://sqlalche.me/e/13/e3q8)
Looks to me like the mailinglist table has been using awfully close to the default mysql max row length, and this added column pushes me over? Is there some other fix other than a terrifyingly gigantic process of dumping all my databases, changing my page size, and restoring them all? This DB server isn't serving just mailman...
Thanks
innodb_page_size of 16384 is the default, so that is what I'm working with as well. The row size limit is half the page size minus some overhead, so it comes out to 8126 and I bet that is yours as well.
Yes, I am utf8mb4 at the database and table levels, and I bet you are right.
-Joel
On 12/26/2020 1:51 PM, Torge Riedel wrote:
I am also using MySQL and successfully upgraded to 3.3.2, the additional column was successfully added.
See this link: https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/column-count-limit....
MySQL-Workbenchs shows me an innodb_page_size of 16384, so my guess is why you are hit by this limit and I am not is, that you might be using utf8mb4 where my DB is still on utf8!?
I see a lot (20 if I counted right) columns with VARCHAR(255) and 4 with VARCHAR(510) in table "mailinglist". Feels to me that this could bring the rows near or above to the limit.
But these are just rough guesses.
Kind regards Torge
Am 26.12.20 um 18:58 schrieb Joel Lord:
I've just upgraded python to 3.7, so now I can upgrade mailman from 3.3.1 to 3.3.2. Take it down, "pip install --upgrade mailman", and try a "mailman info". It fails hard, and what I think are the important parts of the stack trace are:
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1118, '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') [SQL: ALTER TABLE mailinglist ADD COLUMN archive_rendering_mode INTEGER] (Background on this error at: http://sqlalche.me/e/13/e3q8)
Looks to me like the mailinglist table has been using awfully close to the default mysql max row length, and this added column pushes me over? Is there some other fix other than a terrifyingly gigantic process of dumping all my databases, changing my page size, and restoring them all? This DB server isn't serving just mailman...
Thanks
Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
-- Joel Lord
On 12/26/20 11:28 AM, Joel Lord wrote:
innodb_page_size of 16384 is the default, so that is what I'm working with as well. The row size limit is half the page size minus some overhead, so it comes out to 8126 and I bet that is yours as well.
Yes, I am utf8mb4 at the database and table levels, and I bet you are right.
Just to confirm, between 3.2.1 and 3.2.2 the column archive_rendering_mode was added to the mailinglist table. This column is type - integer (4 bytes), but could be enough to push the row over the limit.
As far as utf8mb4 vs. utf8 is concerned. There will be issues with the hyperkitty_email table subject and content columns in particular and maybe others if email messages contain 4-byte utf-8 unicodes. However, as I read the MySQL docs, for columns like VARCHAR(255) and VARCHAR(510), the difference affects only the number of characters that can be stored in the column and not the actual column width. I.e., a VARCHAR(255) column is actually up to 256 bytes wide (1 byte for length and up to 255 butes of data) and can thus hold up to 85 3-byte utf-8 encodings or up to 63 4-byte utf-8 encodings, but since any string normally contains only a few if any (probably none in the case of the mailinglist table) the actual limit is much closer to 85.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
On 12/26/2020 3:07 PM, Mark Sapiro wrote:
As far as utf8mb4 vs. utf8 is concerned. There will be issues with the hyperkitty_email table subject and content columns in particular and maybe others if email messages contain 4-byte utf-8 unicodes. However, as I read the MySQL docs, for columns like VARCHAR(255) and VARCHAR(510), the difference affects only the number of characters that can be stored in the column and not the actual column width. I.e., a VARCHAR(255) column is actually up to 256 bytes wide (1 byte for length and up to 255 butes of data) and can thus hold up to 85 3-byte utf-8 encodings or up to 63 4-byte utf-8 encodings, but since any string normally contains only a few if any (probably none in the case of the mailinglist table) the actual limit is much closer to 85.
Earlier I had attempted changing the encoding and collation and manually adding the new column, and it failed with the same error. This fits with Mark's explanation of how the column size and encoding go together.
I just ran an "optimize table mailinglist;" and then the update ran clean.
Thanks for the eyeballs today, no idea why that would have worked but now we're up and running.
-- Joel Lord
Am 26.12.20 um 23:00 schrieb Joel Lord:
but now we're up and running
Nice to hear, but I fear you could be hit by this issues again, depending on changes in the future on the table. Either when more columns are added or on runtime when rows are added / updated.
I've read the following documentation, but I do not clearly understand whether innodb_page_size can be increased later via config or not:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb...
@Mark: Would it be a good approach to split the table 'mailinglist'? I see some groups of columns like "bounce_*" or "dmarc_*" which could be managed in separate tables.
Whereas reaching limits on upgrade is already annoying enough, reaching limits on runtime can be fatal. I guess.
Kind regards Torge
innodb_page_size can not be increased after the fact. In order to actually make that change would involve dumping all the databases and re-initializing mysql with the new page size in place, then reloading from all the dumps. I'm not working with a dedicated database server, it's got quite a lot on it other than mailman, so that would be an enormous undertaking.
-Joel
On 12/27/20 3:12 AM, Torge Riedel wrote:
Am 26.12.20 um 23:00 schrieb Joel Lord:
but now we're up and running
Nice to hear, but I fear you could be hit by this issues again, depending on changes in the future on the table. Either when more columns are added or on runtime when rows are added / updated.
I've read the following documentation, but I do not clearly understand whether innodb_page_size can be increased later via config or not:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb...
@Mark: Would it be a good approach to split the table 'mailinglist'? I see some groups of columns like "bounce_*" or "dmarc_*" which could be managed in separate tables.
Whereas reaching limits on upgrade is already annoying enough, reaching limits on runtime can be fatal. I guess.
Kind regards Torge
Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
-- Joel Lord
On 12/27/20 12:12 AM, Torge Riedel wrote:
Nice to hear, but I fear you could be hit by this issues again, depending on changes in the future on the table. Either when more columns are added or on runtime when rows are added / updated.
It seems that is a possibility with MySQL. It may not be an issue with MariaDB. It isn't completely clear to me from <https://mariadb.com/kb/en/innodb-limitations/#limitations-on-size>, but it seems that VARBINARY, VARCHAR, BLOB and TEXT columns are only counted as a few bytes in the "roughly half the page size" calculation.
@Mark: Would it be a good approach to split the table 'mailinglist'? I see some groups of columns like "bounce_*" or "dmarc_*" which could be managed in separate tables.
Currently, those list attributes which are stored in tables separate from the mailinglist table are (some of) those that have lists of values rather than a simple value, but it would certainly be possible to separate groups of related attributes into separate tables. Care to submit a merge request?
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
participants (3)
-
Joel Lord -
Mark Sapiro -
Torge Riedel