[Bug 38906] New: REGEXP_REPLACE not in MySQL < 5.8 DB update 24.06.00.064 fails
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Bug ID: 38906 Summary: REGEXP_REPLACE not in MySQL < 5.8 DB update 24.06.00.064 fails Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org In bug 38436 Katrin mentions that REGEXP_REPLACE was a problem, it still can be if using MySQL 5.7 or below We had a test site update fail Seems to be easily fixed by changing to: UPDATE columns_settings SET columnname=SUBSTRING(columnname,LOCATE("_",columnname)+1) WHERE module="catalogue" AND page="detail" AND ( tablename="holdings_table" OR tablename="otherholdings_table"); -- 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=38906 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |38436 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38436 [Bug 38436] Adjust code for column visibility (after DataTables upgrade) -- 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=38906 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- 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=38906 --- Comment #1 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 176659 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=176659&action=edit Bug 38906: Use SUBSTRING and LOCATE over REGEXP_REPLACE To test: 1 - Check out an old version of Koha and set some columns settings for holdings tables, or: UPDATE columns_settings SET columnname = CONCAT("holdings_",columnname) WHERE module = 'catalogue' AND page='detail' AND tablename = "holdings_table"; UPDATE columns_settings SET columnname = CONCAT("otherholdings_",columnname) WHERE module = 'catalogue' AND page = 'detail' AND tablename = "otherholdings_table"; 2 - perl -e 'use C4::Installer; my $result = C4::Installer::run_db_rev("/kohadevbox/koha/installer/data/mysql/db_revs/240600064.pl"); warn Data::Dumper::Dumper($result)' 3 - SELECT columnname FROM columns_settings WHERE module="catalogue" AND page="detail" AND ( tablename="holdings_table" OR tablename="otherholdings_table"); 4 - Confirm column names are correct -- 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=38906 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@gmail.com See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=29805 Summary|REGEXP_REPLACE not in MySQL |REGEXP_REPLACE not in MySQL |< 5.8 DB update |< 5.7b DB update |24.06.00.064 fails |24.06.00.064 fails -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 --- Comment #2 from Jonathan Druart <jonathan.druart@gmail.com> --- Which OS? -- 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=38906 --- Comment #3 from Jonathan Druart <jonathan.druart@gmail.com> --- Are you sure the patch is correct? select REGEXP_REPLACE(columnname, '.*holdings_', ''), SUBSTRING(columnname,LOCATE("_",columnname)+1) from columns_settings WHERE module = 'catalogue' AND page='detail' AND tablename = "hold ings_table"; +-----------------------------------------------+------------------------------------------------+ | REGEXP_REPLACE(columnname, '.*holdings_', '') | SUBSTRING(columnname,LOCATE("_",columnname)+1) | +-----------------------------------------------+------------------------------------------------+ | actions | holdings_actions | | barcode | holdings_barcode | | booksellerid | holdings_booksellerid | | ccode | holdings_ccode | | checkbox | holdings_checkbox | | copynumber | holdings_copynumber | | course_reserves | holdings_course_reserves | | cover_image | holdings_cover_image | | dateaccessioned | holdings_dateaccessioned | | datelastborrowed | holdings_datelastborrowed | | enumchron | holdings_enumchron | | holdingbranch | holdings_holdingbranch | | homebranch | holdings_homebranch | | hostrecord | holdings_hostrecord | | issues | holdings_issues | | item_group | holdings_item_group | | itemcallnumber | holdings_itemcallnumber | | itemnotes | holdings_itemnotes | | itemnotes_nonpublic | holdings_itemnotes_nonpublic | | itype | holdings_itype | | lastseen | holdings_lastseen | | localuse | holdings_localuse | | materials | holdings_materials | | renewals | holdings_renewals | | spinelabel | holdings_spinelabel | | status | holdings_status | | stocknumber | holdings_stocknumber | | uri | holdings_uri | | usedin | holdings_usedin | +-----------------------------------------------+------------------------------------------------+ -- 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=38906 --- Comment #4 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Wondering: Do we still support MySQL and should this be updated? https://wiki.koha-community.org/wiki/System_requirements_and_recommendations -- 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=38906 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #5 from David Nind <david@davidnind.com> --- I thought we still supported the default versions of MySQL that are installed on the supported LTS operating systems. I'm not sure what they are, but they should be added. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 --- Comment #6 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to David Nind from comment #5)
I thought we still supported the default versions of MySQL that are installed on the supported LTS operating systems.
I'm not sure what they are, but they should be added.
I think Debian replaced MySQL with MariaDB - but I am really no expert there. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Magnus Enger <magnus@libriotech.no> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |magnus@libriotech.no Status|Needs Signoff |Failed QA --- Comment #7 from Magnus Enger <magnus@libriotech.no> --- (In reply to Jonathan Druart from comment #3)
Are you sure the patch is correct?
Setting to FQA since this has not been answered. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 --- Comment #8 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- (In reply to Jonathan Druart from comment #3)
Are you sure the patch is correct?
What versions are you on? Your MariaDB connection id is 59988 Server version: 10.5.28-MariaDB-ubu2004 mariadb.org binary distribution Debian GNU/Linux 12 (bookworm) When I run your SQL: MariaDB [koha_kohadev]> select REGEXP_REPLACE(columnname, '.*holdings_', ''), SUBSTRING(columnname,LOCATE("_",columnname)+1) from columns_settings WHERE module = 'catalogue' AND page='detail' AND tablename = "holdings_table"; +-----------------------------------------------+------------------------------------------------+ | REGEXP_REPLACE(columnname, '.*holdings_', '') | SUBSTRING(columnname,LOCATE("_",columnname)+1) | +-----------------------------------------------+------------------------------------------------+ | actions | actions | | barcode | barcode | | booksellerid | booksellerid | | ccode | ccode | | checkbox | checkbox | | copynumber | copynumber | | course_reserves | course_reserves | | cover_image | cover_image | | dateaccessioned | dateaccessioned | | datelastborrowed | datelastborrowed | | enumchron | enumchron | | holdingbranch | holdingbranch | | homebranch | homebranch | | hostrecord | hostrecord | | issues | issues | | item_group | item_group | | itemcallnumber | itemcallnumber | | itemnotes | itemnotes | | itemnotes_nonpublic | itemnotes_nonpublic | | itype | itype | | lastseen | lastseen | | localuse | localuse | | location | location | | materials | materials | | renewals | renewals | | spinelabel | spinelabel | | status | status | | stocknumber | stocknumber | | uri | uri | | usedin | usedin | +-----------------------------------------------+------------------------------------------------+ 30 rows in set (0.010 sec) -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 --- Comment #9 from Jonathan Druart <jonathan.druart@gmail.com> --- (In reply to Nick Clemens (kidclamp) from comment #8)
(In reply to Jonathan Druart from comment #3)
Are you sure the patch is correct?
2 months later, I have no idea! -- 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=38906 --- Comment #10 from Jonathan Druart <jonathan.druart@gmail.com> --- It seems to work now... There may be a problem however if the query is run several times, for columns with underscore in the name | columnname | REGEXP_REPLACE(columnname, '.*holdings_', '') | SUBSTRING(columnname,LOCATE("_",columnname)+1) | | cover_image | cover_image | image | | item_group | item_group | group | | itemnotes_nonpublic | itemnotes_nonpublic | nonpublic | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |caroline.cyr-la-rose@inlibr | |o.com Assignee|koha-bugs@lists.koha-commun |nick@bywatersolutions.com |ity.org | -- 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=38906 --- Comment #11 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- (In reply to Jonathan Druart from comment #9)
(In reply to Nick Clemens (kidclamp) from comment #8)
(In reply to Jonathan Druart from comment #3)
Are you sure the patch is correct?
2 months later, I have no idea!
I do believe so, we have used it in production several times with no issues -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 --- Comment #12 from Jonathan Druart <jonathan.druart@gmail.com> --- (In reply to Nick Clemens (kidclamp) from comment #11)
(In reply to Jonathan Druart from comment #9)
(In reply to Nick Clemens (kidclamp) from comment #8)
(In reply to Jonathan Druart from comment #3)
Are you sure the patch is correct?
2 months later, I have no idea!
I do believe so, we have used it in production several times with no issues
What about the previous comment about the underscores in column names? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Bug 38906 depends on bug 38436, which changed state. Bug 38436 Summary: Adjust code for column visibility (after DataTables upgrade) https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38436 What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #176659|0 |1 is obsolete| | --- Comment #13 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 185855 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=185855&action=edit Bug 38906: Use SUBSTRING and LOCATE over REGEXP_REPLACE To test: 1 - Check out an old version of Koha and set some columns settings for holdings tables, or: UPDATE columns_settings SET columnname = CONCAT("holdings_",columnname) WHERE module = 'catalogue' AND page='detail' AND tablename = "holdings_table"; UPDATE columns_settings SET columnname = CONCAT("otherholdings_",columnname) WHERE module = 'catalogue' AND page = 'detail' AND tablename = "otherholdings_table"; 2 - perl -e 'use C4::Installer; my $result = C4::Installer::run_db_rev("/kohadevbox/koha/installer/data/mysql/db_revs/240600064.pl"); warn Data::Dumper::Dumper($result)' 3 - SELECT columnname FROM columns_settings WHERE module="catalogue" AND page="detail" AND ( tablename="holdings_table" OR tablename="otherholdings_table"); 4 - Confirm column names are correct 5 - repeat 2 6 - Confirm column names are not affected on subsequent runs (items_group does not become group) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Jonathan Druart <jonathan.druart@gmail.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=38906 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #185855|0 |1 is obsolete| | --- Comment #14 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 186018 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=186018&action=edit Bug 38906: Use SUBSTRING and LOCATE over REGEXP_REPLACE To test: 1 - Check out an old version of Koha and set some columns settings for holdings tables, or: UPDATE columns_settings SET columnname = CONCAT("holdings_",columnname) WHERE module = 'catalogue' AND page='detail' AND tablename = "holdings_table"; UPDATE columns_settings SET columnname = CONCAT("otherholdings_",columnname) WHERE module = 'catalogue' AND page = 'detail' AND tablename = "otherholdings_table"; 2 - perl -e 'use C4::Installer; my $result = C4::Installer::run_db_rev("/kohadevbox/koha/installer/data/mysql/db_revs/240600064.pl"); warn Data::Dumper::Dumper($result)' 3 - SELECT columnname FROM columns_settings WHERE module="catalogue" AND page="detail" AND ( tablename="holdings_table" OR tablename="otherholdings_table"); 4 - Confirm column names are correct 5 - repeat 2 6 - Confirm column names are not affected on subsequent runs (items_group does not become group) Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com QA Contact|testopia@bugs.koha-communit |jonathan.druart@gmail.com |y.org | 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=38906 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |25.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 --- Comment #15 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 25.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to main |Pushed to stable Version(s)|25.11.00 |25.11.00,25.05.04 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 --- Comment #16 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Nice work everyone! Pushed to 25.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |Pushed to oldstable Version(s)|25.11.00,25.05.04 |25.11.00,25.05.04,24.11.10 released in| | CC| |fridolin.somers@biblibre.co | |m --- Comment #17 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 24.11.x for 24.11.10 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldstable |Needs documenting --- Comment #18 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Not backporting to 22.11.x as it wasn't backported to 24.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38906 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED --- Comment #19 from David Nind <david@davidnind.com> --- Bug fix, no changes to the manual required. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org