From 0941836180945cc2aeac13aa6bd9340cc7d18d6d Mon Sep 17 00:00:00 2001
From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Date: Fri, 21 Apr 2023 17:09:08 +0000
Subject: [PATCH] Bug 33584: Fix failures for Commenter.t on MySQL8

The column names from information_schema are uppercase now in MySQL8.
In the arrayref with sliced hashes, we expected lowercase.

See e.g. https://stackoverflow.com/questions/54538448/what-are-the-changes-in-mysql-8-result-rowset-case

Test plan:
Run t/db_dependent/Koha/Database/Commenter.t
Do it again if possible with a MySQL 8 db server.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 Koha/Database/Commenter.pm               | 4 +++-
 t/db_dependent/Koha/Database/Commenter.t | 8 +++++++-
 2 files changed, 10 insertions(+), 2 deletions(-)

diff --git a/Koha/Database/Commenter.pm b/Koha/Database/Commenter.pm
index fb4048f1dc8..c349deffa99 100644
--- a/Koha/Database/Commenter.pm
+++ b/Koha/Database/Commenter.pm
@@ -202,9 +202,11 @@ sub _fetch_schema_comments {
 sub _fetch_stored_comments {
     my ( $self, $params ) = @_; # params: table
     my $sql = q|
-SELECT table_name, column_name, column_comment FROM information_schema.columns
+SELECT table_name AS `table_name`, column_name AS `column_name`, column_comment AS `column_comment`
+FROM information_schema.columns
 WHERE table_schema=? AND table_name=?
 ORDER BY table_name, column_name|;
+# The AS `table_name` etc. is needed for MySQL8 which returns uppercase columns in information_schema
     $sql =~ s/AND table_name=\?// unless $params->{table};
     return $self->{dbh}->selectall_arrayref( $sql, { Slice => {} }, $self->{database}, $params->{table} || () );
 }
diff --git a/t/db_dependent/Koha/Database/Commenter.t b/t/db_dependent/Koha/Database/Commenter.t
index 4ca9180ec3c..25bbeda0107 100755
--- a/t/db_dependent/Koha/Database/Commenter.t
+++ b/t/db_dependent/Koha/Database/Commenter.t
@@ -15,7 +15,7 @@ our $dbh = C4::Context->dbh;
 our $mgr;
 
 subtest '->new, dry_run' => sub {
-    plan tests => 6;
+    plan tests => 9;
     $schema->storage->txn_begin; # just cosmetic, no changes expected in a dry run
 
     # Two exceptions on dbh parameter
@@ -28,6 +28,12 @@ subtest '->new, dry_run' => sub {
     unlink $filename;
     throws_ok { $mgr->reset_to_schema({ dry_run => 1, table => 'biblio' }) } 'Koha::Exceptions::FileNotFound', 'Schema deleted';
 
+    # Check case of columns from information_schema (MySQL8 defaults to uppercase)
+    my $columns = $mgr->_fetch_stored_comments({ table => 'article_requests' });
+    ok( exists $columns->[0]->{table_name}, 'Check table_name column' );
+    ok( exists $columns->[0]->{column_name}, 'Check column_name column' );
+    ok( exists $columns->[0]->{column_comment}, 'Check column_comment column' );
+
     # Clear comments for article_requests in dry run mode
     my $messages = [];
     $mgr->clear( { table => 'article_requests', dry_run => 1 }, $messages );
-- 
2.34.1