[Bug 32556] New: borrower_message_preference_id reaches limit
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32556 Bug ID: 32556 Summary: borrower_message_preference_id reaches limit Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: magnus@libriotech.no QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle.m.hall@gmail.com Symptom: Adding a new patron gives a 500 error. plack-error.log: C4::Members::Messaging::SetMessagingPreference(): DBI Exception: DBD::mysql::st execute failed: Out of range value for column 'borrower_message_preference_id' at row 1 at /usr/share/koha/lib/C4/Form/MessagingPreferences.pm line 98 MariaDB [koha]> select max(borrower_message_preference_id) from borrower_message_preferences; +-------------------------------------+ | max(borrower_message_preference_id) | +-------------------------------------+ | 2147475728 | +-------------------------------------+ 1 row in set (0.000 sec) MariaDB [koha]> show create table borrower_message_preferences\G *************************** 1. row *************************** Table: borrower_message_preferences Create Table: CREATE TABLE `borrower_message_preferences` ( `borrower_message_preference_id` int(11) NOT NULL AUTO_INCREMENT, `borrowernumber` int(11) DEFAULT NULL, `categorycode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `message_attribute_id` int(11) DEFAULT 0, `days_in_advance` int(11) DEFAULT 0, `wants_digest` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`borrower_message_preference_id`), KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), KEY `message_attribute_id` (`message_attribute_id`), CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.000 sec) The site where this happens runs a nightly import from a student registration system, so patrons are updated frequently. Two questions: 1. How do we fix this for sites that reach the limit? I tried to disable foreign key checks and alter the column from signed to unsigned, but that did not work: MariaDB [koha]> SET FOREIGN_KEY_CHECKS=0; Query OK, 0 rows affected (0.000 sec) MariaDB [koha]> ALTER TABLE borrower_message_preferences MODIFY borrower_message_preference_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT; ERROR 1025 (HY000): Error on rename of './koha_hkr/#sql-3d8_41ab0' to './koha_hkr/borrower_message_preferences' (errno: 150 "Foreign key constraint is incorrectly formed") 2. How do we make sure this never happens? To extend the period before this happens, it might be a quick fix to change the borrower_message_preference_id column from SIGNED (the default) to UNSIGNED. As far as I can tell, this would double the number of IDs the column can hold. A better solution might be to make the borrower_message_preferences update in place, and not generate new borrower_message_preference_id's every time they are updated? -- 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=32556 Hans Pålsson <hans.palsson@hkr.se> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |hans.palsson@hkr.se -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32556 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #1 from David Cook <dcook@prosentient.com.au> ---
Two questions:
1. How do we fix this for sites that reach the limit?
2. How do we make sure this never happens?
To extend the period before this happens, it might be a quick fix to change the borrower_message_preference_id column from SIGNED (the default) to UNSIGNED. As far as I can tell, this would double the number of IDs the column can hold.
It might make more sense to switch from SIGNED INT to SIGNED BIGINT as the maximum value for SIGNED BIGINT is 9,223,372,036,854,775,807 That would make it much less likely to hit the max value limit.
A better solution might be to make the borrower_message_preferences update in place, and not generate new borrower_message_preference_id's every time they are updated?
That's probably a better solution although it would involve more elaborate coding. -- 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=32556 --- Comment #2 from Magnus Enger <magnus@libriotech.no> --- After reading https://stackoverflow.com/questions/10833530/how-to-change-mysql-primary-key... I tried to: - Dump the database - Edit the CREATE TABLE statements for borrower_message_preferences.borrower_message_preference_id and borrower_message_transport_preferences.borrower_message_preference_id to make them BIGINT - Load the database back in But alas, this also gave a foreign key error: ERROR 1005 (HY000) at line 2442: Can't create table `koha_hkr`.`borrower_message_preferences` (errno: 150 "Foreign key constraint is incorrectly formed") -- 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=32556 --- Comment #3 from Galen Charlton <gmcharlt@gmail.com> --- This could do it in-place: ALTER TABLE borrower_message_transport_preferences DROP FOREIGN KEY borrower_message_transport_preferences_ibfk_1; ALTER TABLE borrower_message_preferences0 MODIFY borrower_message_preference_id bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE borrower_message_transport_preferences MODIFY borrower_message_preference_id bigint(11) UNSIGNED NOT NULL DEFAULT 0; ALTER TABLE borrower_message_transport_preferences ADD CONSTRAINT borrower_message_transport_preferences_ibfk_1 FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- 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=32556 --- Comment #4 from Galen Charlton <gmcharlt@gmail.com> --- Whoops, syntax error. I meant: ALTER TABLE borrower_message_transport_preferences DROP FOREIGN KEY borrower_message_transport_preferences_ibfk_1; ALTER TABLE borrower_message_preferences MODIFY borrower_message_preference_id bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE borrower_message_transport_preferences MODIFY borrower_message_preference_id bigint(11) UNSIGNED NOT NULL DEFAULT 0; ALTER TABLE borrower_message_transport_preferences ADD CONSTRAINT borrower_message_transport_preferences_ibfk_1 FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- 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=32556 --- Comment #5 from Galen Charlton <gmcharlt@gmail.com> --- By the way, I wonder if the error 150 you're seeing isn't about borrower_message_preference_id but rather one of the three foreign key contraints on borrower_message_preferences (i.e., borrowernumber, message_attribute_id, or categorycode). -- 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=32556 --- Comment #6 from Galen Charlton <gmcharlt@gmail.com> --- As a side note, I ran across this query that identifies cases where an auto_increment column is at risk of hitting its maximum value: SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME FROM information_schema.COLUMNS AS c JOIN information_schema.TABLES AS t USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME) WHERE c.EXTRA LIKE '%auto_increment%' AND t.AUTO_INCREMENT / CASE c.DATA_TYPE WHEN 'TINYINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 255, 127) WHEN 'SMALLINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 65535, 32767) WHEN 'MEDIUMINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 16777215, 8388607) WHEN 'INT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 4294967295, 2147483647) WHEN 'BIGINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', '18446744073709551615', 9223372036854775807) # need to quote because column type defaults to unsigned. ELSE 0 END > .9; # 10% buffer Source: https://stackoverflow.com/a/45171880 -- 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=32556 --- Comment #7 from Magnus Enger <magnus@libriotech.no> --- Thanks for your input, Galen! I found a way to make my quickfix work: - Dump the database - Edit the dumped database and fix CREATE TABLE statements for borrower_message_preferences.borrower_message_preference_id and borrower_message_transport_preferences.borrower_message_preference_id to make them BIGINT - Drop the Koha database - Create the Koha database - Load the database-dump back in The problem was that I was trying to run the dumped database against a non-empty database. Next question then is: Should we change the schema and run Galen's solution as part of an upgrade, or should we just document the quickfix for those that run into the problem? And/or change how these message preferences are handled? -- 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=32556 --- Comment #8 from Hans Pålsson <hans.palsson@hkr.se> --- If there is a solution i.e. use BIGINT instead of INT it is better to use it in the schema instead of letting users run in to problems and having to find this solution here. Good job everyone! -- 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=32556 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart+koha@gmail. | |com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32556 --- Comment #9 from Hans Pålsson <hans.palsson@hkr.se> --- We ran into this problem again recently on our test server (22.05). It seems it is not a problem for most libraries I guess, because then the issue would have been solved. But we use a cronjob to set message preferences for all users which probably leads to a large number of autoincrements that eventually fills the table. A possible solution for us would be to set the patron message preferences when creating patrons with the API. Is this possible with the API now? -- 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=32556 --- Comment #10 from David Cook <dcook@prosentient.com.au> --- (In reply to Hans Pålsson from comment #8)
If there is a solution i.e. use BIGINT instead of INT it is better to use it in the schema instead of letting users run in to problems and having to find this solution here. Good job everyone!
Interestingly enough we ran into this same problem on a different system recently. The autonumber hit the ceiling on INT and we switched to BIGINT (using PostgreSQL rather than MySQL). -- 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=32556 --- Comment #11 from David Cook <dcook@prosentient.com.au> --- (In reply to Magnus Enger from comment #7)
Next question then is: Should we change the schema and run Galen's solution as part of an upgrade, or should we just document the quickfix for those that run into the problem? And/or change how these message preferences are handled?
I think we need to change the schema as part of the upgrade. However, I think we could just use "FOREIGN_KEY_CHECKS" to disable the foreign key checks while changing from INT to BIGINT. That should make it a bit easier. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org