[Bug 24986] New: Maximum row size reached soon for borrowers and deletedborrowers
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Bug ID: 24986 Summary: Maximum row size reached soon for borrowers and deletedborrowers Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: julian.maurice@biblibre.com QA Contact: testopia@bugs.koha-community.org Running updatedatabase.pl I got the following error messages: DBD::mysql::db do failed: 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 [for Statement "ALTER TABLE borrowers ADD COLUMN autorenew_checkouts TINYINT(1) NOT NULL DEFAULT 1 AFTER anonymized"] at installer/data/mysql/updatedatabase.pl li ne 21259 DBD::mysql::db do failed: 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 [for Statement "ALTER TABLE deletedborrowers ADD COLUMN autorenew_checkouts TINYINT(1) NOT NULL DEFAULT 1 AFTER anonymized"] at installer/data/mysql/updatedatabas e.pl line 21262. After re-injecting an old clean dump and replaying the updatedatabase.pl errors are gone, so it was probably because of other columns coming from patches not yet in master. Maybe we should start moving some columns to other tables. An obvious candidate is all the address fields that can be moved in a patron_addresses table. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Magnus Enger <magnus@libriotech.no> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |magnus@libriotech.no --- Comment #1 from Magnus Enger <magnus@libriotech.no> --- I got a similar error when upgrading a straight package install from 18.11.06 to 19.11.06: DBD::mysql::db do failed: 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 [for Statement " ALTER TABLE borrowers ADD COLUMN flgAnonymized tinyint DEFAULT 0 AFTER overdrive_auth_token "] at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 18071. DBD::mysql::db do failed: 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 [for Statement " ALTER TABLE deletedborrowers ADD COLUMN flgAnonymized tinyint DEFAULT 0 AFTER overdrive_auth_token "] at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 18077. Upgrade to 18.12.00.049 done (Bug 21336 - Add field flgAnonymized) -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #2 from Magnus Enger <magnus@libriotech.no> --- When I run those same updates manually they work: MariaDB [koha_hkr]> ALTER TABLE borrowers -> ADD COLUMN flgAnonymized tinyint DEFAULT 0 -> AFTER overdrive_auth_token; Query OK, 0 rows affected (0.006 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [koha_hkr]> ALTER TABLE deletedborrowers -> ADD COLUMN flgAnonymized tinyint DEFAULT 0 -> AFTER overdrive_auth_token; Query OK, 0 rows affected (0.006 sec) Records: 0 Duplicates: 0 Warnings: 0 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |critical CC| |tomascohen@gmail.com -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #3 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- What's the return of show variables where Variable_name = 'innodb_file_per_table'; show variables where Variable_name = 'innodb_default_row_format'; -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |jonathan.druart@bugs.koha-c |ity.org |ommunity.org Status|NEW |ASSIGNED -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #4 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 105548 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=105548&action=edit Bug 24986: Modify some borrowers DB fields to (TINY|MEDIUM)TEXT -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Can you confirm this patch resolves the problem? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #6 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #7 from Magnus Enger <magnus@libriotech.no> --- (In reply to Jonathan Druart from comment #3)
What's the return of show variables where Variable_name = 'innodb_file_per_table'; show variables where Variable_name = 'innodb_default_row_format';
MariaDB [koha]> show variables where Variable_name = 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.001 sec) MariaDB [koha]> show variables where Variable_name = 'innodb_default_row_format'; +---------------------------+---------+ | Variable_name | Value | +---------------------------+---------+ | innodb_default_row_format | dynamic | +---------------------------+---------+ 1 row in set (0.001 sec) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com --- Comment #8 from Kyle M Hall <kyle@bywatersolutions.com> ---
After re-injecting an old clean dump and replaying the updatedatabase.pl errors are gone, so it was probably because of other columns coming from patches not yet in master. Maybe we should start moving some columns to other tables. An obvious candidate is all the address fields that can be moved in a patron_addresses table.
Another option would be to move some columns into a JSON column. MariaDB 10.2.7 and MySQL 5.7 both have JSON data types and functions to use JSON values in a query. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #9 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- borrowers is heavily queried for reports I believe - I am not sure if adding JSON would make this harder. I believe it would at least be a different syntax to learn? What about changing datatypes as the message suggests? We have quite of a mix on the table at the moment that could need a clean-up. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #105548|0 |1 is obsolete| | --- Comment #10 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 105628 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=105628&action=edit Bug 24986: Modify some borrowers DB fields to (TINY|MEDIUM)TEXT Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com Status|Needs Signoff |Signed Off --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- I think the patch proposed here is a good (and back portable) fix for the issue in hand so I'm signing off. However, I do think we should certainly split out addresses into their own table and follow proper 3rd normal form for then.. but I think that should be on it's own bug.. (actually.. i thought there was already a bug for that, but I can't seem to find it). As for using JSON.. I'm not sure what that would win us really.. I like JSON fields and used them in a postgres based system a while ago.. but the querying did end up considerably more complex in the end and I ended up converting some fields back to additional tables and using JOIN queries instead for performance and readability reasons (dbic isn't great with JSON queries yet either).. I'd say for now at least.. we don't have any cases where the advantage of JSON fields outweighs the cost.. yet.. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #12 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- I think we need to recreate the problem and confirm that the patch fixes it. Not only guess it will do the trick (as I did). -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #13 from Magnus Enger <magnus@libriotech.no> --- I forgot to save a dump of the live database that had the problem, and now it has been deleted. I tried to make a fresh install of 18.11.x, then upgrade to master, but this did not trigger the error I saw on the live database. Sorry about that. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #14 from Magnus Enger <magnus@libriotech.no> --- BTW, the server where I saw the problem is running 10.3.23-MariaDB-1:10.3.23+maria~stretch, if that makes any difference. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #15 from Magnus Enger <magnus@libriotech.no> --- Tried again with an upgrade from 18.11.06 to 19.11.06 (so more specific than the first attempt, where I used the latest releases of 18.11.x and 19.11x), but still no luck. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #105628|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #16 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 105769 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=105769&action=edit Bug 24986: Modify some borrowers DB fields to (TINY|MEDIUM)TEXT Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |tomascohen@gmail.com |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl --- Comment #17 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Jonathan Druart from comment #12)
I think we need to recreate the problem and confirm that the patch fixes it. Not only guess it will do the trick (as I did).
Your patch looks good to me too. https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-inn... Shows some examples with adding varchar fields to recreate ? Does this actually mean that we should always better choose for the TEXT variants instead of VARCHAR ? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #18 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Here is a first observation: Not a big production database, but on a small master. I can add 139 varchar(60) fields to borrowers and still create table x like borrowers. But if I add 140 fields, the create fails on row size. Note that 60 is chosen so that the varchar stays on the main data page, not on the overflow. With your patch, I hoped to add more than 140 fields. But it fails exactly at this same point! So the patch seems to make no difference?? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #19 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Well, not completely. Hang on. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #20 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Another thing: If I add the 140 fields before the db revision, the solution in this patch should help me to recover, but unfortunately the db rev will not execute the change on the fields but list the Row size too large on them. So I would be stuck. So the troubleshooting page here gives me a solution that I cant apply. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #21 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- With SET SESSION innodb_strict_mode=OFF; I can do: alter table borrowers modify column streettype varchar(256); -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #22 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Well, my conclusion for now is: I am all for pushing this change to prevent future problems. But my test tells me that when you already ran into this problem, you cant alter table anymore and resolve it like the dbrev does. I needed to set strict mode off to alter the 140 fields to TEXT. And even then it was not sufficient. Only when I dropped field 140, it was gone. Perhaps another limitation? InnoDB should allow 1000 columns, but I already passed some other limitation: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. Not sure, but it might help to combine the separate ALTERs into one statement instead of looping. And it might help to temporarily disable strict mode for the alter, but as I saw in my test, the change might not be enough to recover? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |20.11.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #23 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Pushed to master for 20.11, thanks to everybody involved! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com Version(s)|20.11.00 |20.11.00, 20.05.02 released in| | Status|Pushed to master |Pushed to stable --- Comment #24 from Lucas Gass <lucas@bywatersolutions.com> --- backported to 20.05.x for 20.05.02 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|20.11.00, 20.05.02 |20.11.00, 20.05.02, released in| |19.11.08 Status|Pushed to stable |Pushed to oldstable CC| |aleisha@catalyst.net.nz --- Comment #25 from Aleisha Amohia <aleisha@catalyst.net.nz> --- backported to 19.11.x for 19.11.08 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |victor@tuxayo.net Version(s)|20.11.00, 20.05.02, |20.11.00, 20.05.02, released in|19.11.08 |19.11.08, 19.05.13 Status|Pushed to oldstable |Pushed to oldoldstable --- Comment #26 from Victor Grousset/tuxayo <victor@tuxayo.net> --- Backported to 19.05.x branch for 19.05.13 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #27 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Reported on the ML for "branches" https://lists.katipo.co.nz/pipermail/koha/2020-July/054967.html The solution was to replace the row_format's value with "Dynamic" (this is the default value). -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 --- Comment #28 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Jonathan Druart from comment #27)
Reported on the ML for "branches"
https://lists.katipo.co.nz/pipermail/koha/2020-July/054967.html
The solution was to replace the row_format's value with "Dynamic" (this is the default value).
I am not sure about that. My tables are all on Dynamic. If they would not, this is a first step. But somehow with Dynamic, we could also reproduce (see earlier comments) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=28267 -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org