Bug 32556 - borrower_message_preference_id reaches limit
Summary: borrower_message_preference_id reaches limit
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-01-03 09:22 UTC by Magnus Enger
Modified: 2023-04-24 00:06 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Magnus Enger 2023-01-03 09:22:35 UTC
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?
Comment 1 David Cook 2023-01-03 23:15:46 UTC
> 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.
Comment 2 Magnus Enger 2023-01-04 08:12:53 UTC
After reading https://stackoverflow.com/questions/10833530/how-to-change-mysql-primary-key-from-signed-to-unsigned 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")
Comment 3 Galen Charlton 2023-01-04 15:34:51 UTC
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;
Comment 4 Galen Charlton 2023-01-04 15:38:27 UTC
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;
Comment 5 Galen Charlton 2023-01-04 15:50:11 UTC
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).
Comment 6 Galen Charlton 2023-01-04 15:57:13 UTC
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
Comment 7 Magnus Enger 2023-01-05 07:30:33 UTC
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?
Comment 8 Hans Pålsson 2023-01-05 08:18:59 UTC
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!
Comment 9 Hans Pålsson 2023-04-20 11:10:58 UTC
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?
Comment 10 David Cook 2023-04-24 00:01:30 UTC
(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).
Comment 11 David Cook 2023-04-24 00:06:27 UTC
(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.