Bug 38906 - REGEXP_REPLACE not in MySQL < 5.7b DB update 24.06.00.064 fails
Summary: REGEXP_REPLACE not in MySQL < 5.7b DB update 24.06.00.064 fails
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on: 38436
Blocks:
  Show dependency treegraph
 
Reported: 2025-01-16 13:07 UTC by Nick Clemens (kidclamp)
Modified: 2025-01-24 08:05 UTC (History)
2 users (show)

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


Attachments
Bug 38906: Use SUBSTRING and LOCATE over REGEXP_REPLACE (1.77 KB, patch)
2025-01-16 13:30 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens (kidclamp) 2025-01-16 13:07:52 UTC
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");
Comment 1 Nick Clemens (kidclamp) 2025-01-16 13:30:03 UTC
Created attachment 176659 [details] [review]
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
Comment 2 Jonathan Druart 2025-01-16 13:30:41 UTC
Which OS?
Comment 3 Jonathan Druart 2025-01-16 13:32:55 UTC
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                                |
+-----------------------------------------------+------------------------------------------------+
Comment 4 Katrin Fischer 2025-01-16 15:18:47 UTC
Wondering: Do we still support MySQL and should this be updated?
https://wiki.koha-community.org/wiki/System_requirements_and_recommendations
Comment 5 David Nind 2025-01-23 20:26:16 UTC
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.
Comment 6 Katrin Fischer 2025-01-24 08:05:54 UTC
(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.