Bug 29805

Summary: Database update fails in MySQL 5.7 because it's missing REGEXP_REPLACE
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: Installation and upgrade (web-based installer)Assignee: Bugs List <koha-bugs>
Status: Failed QA --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: gmcharlt, jonathan.druart, nick, victor
Version: master   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32162
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on: 24387, 29778    
Bug Blocks:    
Attachments: Bug 29805: Fix 21.05.00.016 for MySQL 5.7
Bug 29805: Fix 21.05.00.016 for MySQL 5.7

Description Katrin Fischer 2022-01-06 10:19:24 UTC
It looks like this important database update is failing for MySql 5.7 because it lacks support of REGEXP_REPLACE.  

Upgrade to 21.05.00.016 [14:16:28]: Bug 24387 - Rename opac_news with additional_contents

ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: FUNCTION koha.biblioteca.REGEXP_REPLACE does not exist at /usr/share/koha/lib/C4/Installer.pm line 738

Are we still supporting 5.7?

If not, should we highlight that somewhere?

We currently only list MariaDB versions on the wiki linked from release notes:
https://wiki.koha-community.org/wiki/System_requirements_and_recommendations

We are still listing MySQL here:
https://koha-community.org/download-koha/
Comment 1 Katrin Fischer 2022-01-06 10:27:37 UTC
5.7 is in Ubuntu Bionic/18.04:
https://packages.ubuntu.com/search?keywords=mysql&searchon=names&suite=all&section=all
Comment 2 Jonathan Druart 2022-01-06 10:58:53 UTC
Created attachment 129050 [details] [review]
Bug 29805: Fix 21.05.00.016 for MySQL 5.7

MySQL 5.7 is in Ubuntu 18.04 and does not support REGEXP_REPLACE. We can
easily rewrite it in Perl instead of dropping support on this Ubuntu LTS
version.

Test plan:
checkout a commit prior to 21.05.00.016
Create a notice template with
  <<opac_news.content>>
  [%opac_news.content %]
  << opac_news.content>>
  [% opac_news.content %]

Note that ISSUESLIP has already occurrence of 'opac_news.'

git checkout a branch with master + this patch
Run `updatedatabase` and notice that the 'opac_news.' occurrences have
been replaced with 'additional_contents.'
Comment 3 Jonathan Druart 2022-01-06 13:28:37 UTC
Created attachment 129074 [details] [review]
Bug 29805: Fix 21.05.00.016 for MySQL 5.7

MySQL 5.7 is in Ubuntu 18.04 and does not support REGEXP_REPLACE. We can
easily rewrite it in Perl instead of dropping support on this Ubuntu LTS
version.

Test plan:
checkout a commit prior to 21.05.00.016
Create a notice template with
  <<opac_news.content>>
  [%opac_news.content %]
  << opac_news.content>>
  [% opac_news.content %]

Note that ISSUESLIP has already occurrence of 'opac_news.'

git checkout a branch with master + this patch
Run `updatedatabase` and notice that the 'opac_news.' occurrences have
been replaced with 'additional_contents.'
Comment 4 Katrin Fischer 2022-01-24 22:55:06 UTC
Tried to test but having trouble with ktd :( 
Checked out v20.11.13 and did a reset_all but ending up with errors not being able to access the GUI.
Comment 5 Katrin Fischer 2022-01-24 23:19:48 UTC
(In reply to Katrin Fischer from comment #4)
> Tried to test but having trouble with ktd :( 
> Checked out v20.11.13 and did a reset_all but ending up with errors not
> being able to access the GUI.

Maybe fixed - will try again.
Comment 6 Victor Grousset/tuxayo 2022-01-25 03:52:54 UTC
> Tried to test but having trouble with ktd :( 

Was this ran before starting ktd?

export KOHA_IMAGE=20.11-buster;

So it's starts the Linux install OS (here Debian 10, Buster) with the perl deps installed that match the right Koha version.

When switching from master to an older branch of Koha, one must stop ktd. Then sent the right KOHA_IMAGE because the default one if for master. And then start ktd.
reset_all doesn't allow major version changes.
Comment 7 Katrin Fischer 2022-01-25 08:00:25 UTC
(In reply to Victor Grousset/tuxayo from comment #6)
> > Tried to test but having trouble with ktd :( 
> 
> Was this ran before starting ktd?
> 
> export KOHA_IMAGE=20.11-buster;
> 
> So it's starts the Linux install OS (here Debian 10, Buster) with the perl
> deps installed that match the right Koha version.
> 
> When switching from master to an older branch of Koha, one must stop ktd.
> Then sent the right KOHA_IMAGE because the default one if for master. And
> then start ktd.
> reset_all doesn't allow major version changes.

Hi Victor, thx for your help! I just switched to ktd and still learning. In this case, it was wrong permissions on my git repository that seemed to have cause the issue, something I need to figure out (but not on this bug :) )
Comment 8 Jonathan Druart 2022-03-22 14:00:46 UTC
Signoff please?
Comment 9 Nick Clemens 2022-06-07 17:39:13 UTC
We tested this on a sandbox we were upgrading - the upgrade failed but the DB still had opac_news in the letter content
Comment 10 Victor Grousset/tuxayo 2022-07-30 19:50:55 UTC
(In reply to Katrin Fischer from comment #0)
> Are we still supporting 5.7?
> 
> If not, should we highlight that somewhere?
> 
> We currently only list MariaDB versions on the wiki linked from release
> notes:
> https://wiki.koha-community.org/wiki/System_requirements_and_recommendations
> 
> We are still listing MySQL here:
> https://koha-community.org/download-koha/

Woops I only understand now why I was CCed here.
The only CI running MySQL is for Master with MySQL 8
Since more than a year anything could break with MySQL 5.7 and we wouldn't notice it in any release. So maybe we didn't formally decided to not support it but the current state is not a support one.

I added the question to the next meeting: https://wiki.koha-community.org/wiki/Development_IRC_meeting_3_August_2022