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.