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: Nick Clemens (kidclamp)
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-03-31 14:40 UTC (History)
5 users (show)

See Also:
GIT URL:
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.
Comment 7 Magnus Enger 2025-02-14 15:48:12 UTC
(In reply to Jonathan Druart from comment #3)
> Are you sure the patch is correct?

Setting to FQA since this has not been answered.
Comment 8 Nick Clemens (kidclamp) 2025-03-24 12:20:41 UTC
(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)
Comment 9 Jonathan Druart 2025-03-24 13:54:27 UTC
(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!
Comment 10 Jonathan Druart 2025-03-24 14:00:15 UTC
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                                      |