[Bug 33368] New: borrowers.flags is about to reach the limit
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Bug ID: 33368 Summary: borrowers.flags is about to reach the limit Change sponsored?: --- Product: Koha Version: unspecified Hardware: All OS: All Status: ASSIGNED Severity: critical Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: jonathan.druart+koha@gmail.com Reporter: jonathan.druart+koha@gmail.com QA Contact: testopia@bugs.koha-community.org it's a int(11) which means its limit is 2^31 We have currently 29 permissions, there is only one remaining, at 31 permissions the permission system won't support a case where a patron has all permissions. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com, | |nick@bywatersolutions.com, | |tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Jonathan Druart <jonathan.druart+koha@gmail.com> 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=33368 --- Comment #1 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 148952 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=148952&action=edit Bug 33368: Extends borrowers.flags to bigint it's a int(11) which means its limit is 2^31 We have currently 29 permissions, there is only one remaining, at 31 permissions the permission system won't support a case where a patron has all permissions. Test plan: Don't apply this patch Add another permission INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (30, 'new permission 1', '', 0) ; Go to the interface, tick all the boxes select flags from borrowers where borrowernumber=5; => 2113928830 Remember: 2^31 2147483648 2^32 4294967296 Add another one INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (31, 'new permission 2', '', 0) ; Repeat the step before => boom CGI::Compile::ROOT::kohadevbox_koha_members_member_2dflags_2epl::member_2dflags_2epl(): DBI Exception: DBD::mysql::st execute failed: Out of range value for column 'flags' at row 1 at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30 Apply the patch, try again QA note: That's ofc not the best solution, but the easiest for now -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 --- Comment #2 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 148953 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=148953&action=edit Bug 33368: DBIC -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #148952|0 |1 is obsolete| | --- Comment #3 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 148954 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=148954&action=edit Bug 33368: Extends borrowers.flags to bigint it's a int(11) which means its limit is 2^31 We have currently 29 permissions, there is only one remaining, at 31 permissions the permission system won't support a case where a patron has all permissions. Test plan: Don't apply this patch Add another permission INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (30, 'new permission 1', '', 0) ; Go to the interface, tick all the boxes select flags from borrowers where borrowernumber=5; => 2113928830 Remember: 2^31 2147483648 2^32 4294967296 Add another one INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (31, 'new permission 2', '', 0) ; Repeat the step before => boom CGI::Compile::ROOT::kohadevbox_koha_members_member_2dflags_2epl::member_2dflags_2epl(): DBI Exception: DBD::mysql::st execute failed: Out of range value for column 'flags' at row 1 at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30 Apply the patch, try again QA note: That's ofc not the best solution, but the easiest for now Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #148953|0 |1 is obsolete| | --- Comment #4 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 148955 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=148955&action=edit Bug 33368: DBIC Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #148954|0 |1 is obsolete| | --- Comment #5 from Victor Grousset/tuxayo <victor@tuxayo.net> --- Created attachment 149150 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149150&action=edit Bug 33368: Extends borrowers.flags to bigint it's a int(11) which means its limit is 2^31 We have currently 29 permissions, there is only one remaining, at 31 permissions the permission system won't support a case where a patron has all permissions. Test plan: Don't apply this patch Add another permission INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (30, 'new permission 1', '', 0) ; Go to the interface, tick all the boxes select flags from borrowers where borrowernumber=5; => 2113928830 Remember: 2^31 2147483648 2^32 4294967296 Add another one INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (31, 'new permission 2', '', 0) ; Repeat the step before => boom CGI::Compile::ROOT::kohadevbox_koha_members_member_2dflags_2epl::member_2dflags_2epl(): DBI Exception: DBD::mysql::st execute failed: Out of range value for column 'flags' at row 1 at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30 Apply the patch, try again QA note: That's ofc not the best solution, but the easiest for now Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #148955|0 |1 is obsolete| | --- Comment #6 from Victor Grousset/tuxayo <victor@tuxayo.net> --- Created attachment 149151 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149151&action=edit Bug 33368: DBIC Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |victor@tuxayo.net --- Comment #7 from Victor Grousset/tuxayo <victor@tuxayo.net> --- It works! However I don't get why because bigint(11) still has 11 for the number of digits. But it works up to at least 40 bits/flags. And MariaDB's doc doesn't have anything suspicious like the number for bigint being there for compatibility/historical reasons and not being used. https://mariadb.com/kb/en/bigint/ Anyway, second signoff added so it can still be used as a signoff and Nick can pass QA if they know the answer to the above and don't want to wait. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 --- Comment #8 from Tomás Cohen Arazi <tomascohen@gmail.com> --- (In reply to Victor Grousset/tuxayo from comment #7)
It works! However I don't get why because bigint(11) still has 11 for the number of digits. But it works up to at least 40 bits/flags. And MariaDB's doc doesn't have anything suspicious like the number for bigint being there for compatibility/historical reasons and not being used. https://mariadb.com/kb/en/bigint/
Anyway, second signoff added so it can still be used as a signoff and Nick can pass QA if they know the answer to the above and don't want to wait.
(11) i.e. the 'display width' is not relevant to the storage size, which is the problem here. Any INT data type will have the same known limit. It is only relevant if you define things like zero padding, and then you know it will fill with zeroes up to 11 digits. For reference: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html#:~:text=The.... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #149150|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=33368 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #149151|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=33368 --- Comment #9 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 149239 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149239&action=edit Bug 33368: Extends borrowers.flags to bigint it's a int(11) which means its limit is 2^31 We have currently 29 permissions, there is only one remaining, at 31 permissions the permission system won't support a case where a patron has all permissions. Test plan: Don't apply this patch Add another permission INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (30, 'new permission 1', '', 0) ; Go to the interface, tick all the boxes select flags from borrowers where borrowernumber=5; => 2113928830 Remember: 2^31 2147483648 2^32 4294967296 Add another one INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (31, 'new permission 2', '', 0) ; Repeat the step before => boom CGI::Compile::ROOT::kohadevbox_koha_members_member_2dflags_2epl::member_2dflags_2epl(): DBI Exception: DBD::mysql::st execute failed: Out of range value for column 'flags' at row 1 at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30 Apply the patch, try again QA note: That's ofc not the best solution, but the easiest for now Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net> 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=33368 --- Comment #10 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 149240 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149240&action=edit Bug 33368: DBIC Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net> 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=33368 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |23.05.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=33368 --- Comment #11 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.05. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 --- Comment #12 from Victor Grousset/tuxayo <victor@tuxayo.net> --- (In reply to Tomás Cohen Arazi from comment #8)
(11) i.e. the 'display width'
ok!!! Now I really get this part from MariaDB's doc: «If a column has been set to ZEROFILL, all values will be prepended by zeros so that the BIGINT value contains a number of M digits.»
is not relevant to the storage size,
I though it was to have some business domain rules enforced in the DB so it was on top of storage constraints.
For reference: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html#:~: text=The%20maximum%20display%20width%20is,digits%20that%20can%20be%20stored.
thanks, it's clearer -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable Version(s)|23.05.00 |23.05.00,22.11.05 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33368 --- Comment #13 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- Nice work, thanks everyone! Pushed to 22.11.x for the next release. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org