https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34494 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |kyle@bywatersolutions.com |ity.org | Severity|enhancement |blocker -- 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=34494 --- Comment #1 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 154315 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=154315&action=edit Bug 34494: Table tmp_holdsqueue fails to be created for MySQL 8 It appears that MySQL 8 rejects creating any table where the primary key is nullable. The table tmp_holdsqueue has a nullable pk ( itemnumber ) but there is no reason for this column to be nullable ( generating a holds queue entry with no itemnumber is not possible ) so it make sense to just remove the nullability for MySQL compatiability. Test Plan: 1) Using MySQL 8.0, attempt to create a database from kohastructure.sql If you use ktd, you can try: DB_IMAGE=mysql:8.0 ktd up 2) Note the failure to create the table 3) Apply this patch 4) Repeat step 1 5) The table is created! -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34494 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #154315|0 |1 is obsolete| | --- Comment #2 from David Nind <david@davidnind.com> --- Created attachment 154317 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=154317&action=edit Bug 34494: Table tmp_holdsqueue fails to be created for MySQL 8 It appears that MySQL 8 rejects creating any table where the primary key is nullable. The table tmp_holdsqueue has a nullable pk ( itemnumber ) but there is no reason for this column to be nullable ( generating a holds queue entry with no itemnumber is not possible ) so it make sense to just remove the nullability for MySQL compatiability. Test Plan: 1) Using MySQL 8.0, attempt to create a database from kohastructure.sql If you use ktd, you can try: DB_IMAGE=mysql:8.0 ktd up 2) Note the failure to create the table 3) Apply this patch 4) Repeat step 1 5) The table is created! Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34494 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #154317|0 |1 is obsolete| | --- Comment #4 from Victor Grousset/tuxayo <victor@tuxayo.net> --- Created attachment 154417 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=154417&action=edit Bug 34494: Table tmp_holdsqueue fails to be created for MySQL 8 It appears that MySQL 8 rejects creating any table where the primary key is nullable. The table tmp_holdsqueue has a nullable pk ( itemnumber ) but there is no reason for this column to be nullable ( generating a holds queue entry with no itemnumber is not possible ) so it make sense to just remove the nullability for MySQL compatiability. Test Plan: 1) Using MySQL 8.0, attempt to create a database from kohastructure.sql If you use ktd, you can try: DB_IMAGE=mysql:8.0 ktd up 2) Note the failure to create the table 3) Apply this patch 4) Repeat step 1 5) The table is created! Signed-off-by: David Nind <david@davidnind.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=34494 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA CC| |victor@tuxayo.net QA Contact|testopia@bugs.koha-communit |victor@tuxayo.net |y.org | --- Comment #6 from Victor Grousset/tuxayo <victor@tuxayo.net> --- weird, with and without the patch I'm hitting this error with MySQL8 before getting to the part relevant to this ticket: koha-koha-1 | DBIx::Class::Storage::DBI::catch {...} (): DBI Connection failed: DBI connect('database=koha_kohadev;host=db;port=3306','koha_kohadev',...) failed: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. at /kohadevbox/koha/Koha/Database.pm line 91. at /kohadevbox/koha/Koha/Database.pm line 139 koha-koha-1 exited with code 1 Does that speak to anyone? ktd repo, koha code, ktd images up to date **Anyway**, it works for Kyle and David and the change is trivial, there is enough confidence to not block on my dev env issue. And with the patch, it still works for me with MariaDB. And this will be the CI which fails on the right issue so it should be fixed. So: Makes sense, QA script happy[1], code looks good, passing QA :) [1] waived this perltidy warning: ``` $dbh->do(q{ ALTER TABLE tmp_holdsqueue CHANGE COLUMN itemnumber `itemnumber` int(11) NOT NULL }); ``` ↓↓↓↓↓ ``` $dbh->do( q{ ALTER TABLE tmp_holdsqueue CHANGE COLUMN itemnumber `itemnumber` int(11) NOT NULL } ); ``` The proposed code is following our two examples: https://wiki.koha-community.org/wiki/Database_updates minor tangent question: @Kyle, @Tomas Maybe they should be changed or it's not a big deal to have this warning on each new db rev? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34494 --- Comment #8 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Upgrading works correctly. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34494 --- Comment #9 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.11. 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=34494 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m --- Comment #11 from Fridolin Somers <fridolin.somers@biblibre.com> --- Depends on Bug 28966 not in 23.05.x -- You are receiving this mail because: You are watching all bug changes.