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 at lists.koha-community.org
Reporter: magnus at libriotech.no
QA Contact: testopia at bugs.koha-community.org
CC: gmcharlt at gmail.com, kyle.m.hall at 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.