Errors on MySQL 8

RT Users
1

I recently upgraded MySQL from 5 to 8 but get the error below when running RT. It seems GROUPS is now a reserved keyword ( https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-G ) and field names are not always quoted.

rt 4.4.2
mysql 8.0.11

Stack:
  [/opt/rt4/sbin/../lib/RT/Principal.pm:430]
  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:667]
  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:375]
  [/opt/rt4/share/html/autohandler:53] (/opt/rt4/sbin/../lib/RT/Interface/Web/Handler.pm:208)
Trace begun at /opt/rt4/sbin/../lib/RT.pm line 308
Log::Dispatch::__ANON__('Log::Dispatch=HASH(0x5614a64ac818)', 'DBD::mysql::db selectcol_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Groups, Principals, CachedGroupMembers WHERE Principals.Disabled = 0 AND CachedG\' at line 1 at /opt/rt4/sbin/../lib/RT/Principal.pm line 430.^J^JStack:^J  [/opt/rt4/sbin/../lib/RT/Principal.pm:430]^J  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:667]^J  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:375]^J  [/opt/rt4/share/html/autohandler:53]^J') called at /opt/rt4/sbin/../lib/RT/Interface/Web/Handler.pm line 208
HTML::Mason::Exception::as_rt_error('HTML::Mason::Exception=HASH(0x5614ae8eddb0)') called at /usr/share/perl5/HTML/Mason/Exceptions.pm line 278
HTML::Mason::Exception::as_string('HTML::Mason::Exception=HASH(0x5614ae8eddb0)', undef, '') called at /usr/share/perl5/HTML/Mason/Request.pm line 567
HTML::Mason::Request::_handle_error('RT::Interface::Web::Request=HASH(0x5614ae6f12a0)', 'HTML::Mason::Exception=HASH(0x5614ae8eddb0)') called at /usr/share/perl5/HTML/Mason/Request.pm line 518
HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0x5614ae6f12a0)') called at /usr/share/perl5/HTML/Mason/PSGIHandler.pm line 96
eval {...} at /usr/share/perl5/HTML/Mason/PSGIHandler.pm line 96
HTML::Mason::Request::PSGI::exec('RT::Interface::Web::Request=HASH(0x5614ae6f12a0)') called at /usr/share/perl5/HTML/Mason/Interp.pm line 342
HTML::Mason::Interp::exec(undef, undef) called at /usr/share/perl5/HTML/Mason/PSGIHandler.pm line 59
eval {...} at /usr/share/perl5/HTML/Mason/PSGIHandler.pm line 59
HTML::Mason::PSGIHandler::invoke_mason('HTML::Mason::PSGIHandler::Streamy=HASH(0x5614ae6f1e80)', 'HASH(0x5614ae8f4c20)', 'HASH(0x5614adee8290)') called at /usr/share/perl5/HTML/Mason/PSGIHandler/Streamy.pm line 52
HTML::Mason::PSGIHandler::Streamy::__ANON__('CODE(0x5614ae8f9fd0)') called at /usr/share/perl5/Plack/Util.pm line 339
Plack::Util::__ANON__('CODE(0x5614aea0bd58)') called at /usr/share/perl5/Plack/Handler/FCGI.pm line 149
Plack::Handler::FCGI::run('Plack::Handler::FCGI=HASH(0x5614ae725118)', 'CODE(0x5614ae778528)') called at /usr/share/perl5/Plack/Loader.pm line 84
Plack::Loader::run('Plack::Loader=HASH(0x5614ae724db8)', 'Plack::Handler::FCGI=HASH(0x5614ae725118)') called at /usr/share/perl5/Plack/Runner.pm line 277
Plack::Runner::run('RT::PlackRunner=HASH(0x5614a7033450)') called at /opt/rt4/sbin/../lib/RT/PlackRunner.pm line 150
eval {...} at /opt/rt4/sbin/../lib/RT/PlackRunner.pm line 150
RT::PlackRunner::run('RT::PlackRunner=HASH(0x5614a7033450)') called at /opt/rt4/sbin/rt-server.fcgi line 162
2

Did you run mysql_upgrade after running the upgrade?

mysql_upgrade -u XXXXX -pXXXXXXXX --force

https://dev.mysql.com/doc/refman/8.0/en/mysql-upgrade.html

3

Yes and other apps are working. The problem is with the SQL query. Digging further I found the actual failing query, which gives you the same error when run from cli.

(Pastebin since discourse thinks table.field is a url)

4

Was there a resolution to this problem?

I have a similar scenario. I was running RT 4.4.4 and some time later upgraded from MySQL 5.7 to MySQL 8.0.15. I ran mysql_upgrade --force following MySQL upgrade.
When I try to log into RT I get this error:
DBD::mysql::db selectcol_arrayref failed: You have an error in your SQL syntax… near ‘Groups, Principals, CachedGroupMembers WHERE Principals.Disabled = 0 AND CachedG’

5

Doesn’t look like it. I still run a small MySQL 5 instance just for RT

6

Thanks for the follow up.

7

I’ve been struggling as well to set up RT on a MySQL 8 database. The problem is that RT is not using quoted identifiers (backticks) for any literal name like table names or column names. This is bad practice and should be fixed. I’ve tried digging through the code and fixing the missing $dbh->quote_identifier() calls, but it was such a hell that I dropped the efforts. I would advice any user to stay with legacy MySQL or switch to PostgreSQL.

8

I also have this problem…

9

Hi, I faced the same issue and after looking at the logs it is possible to fix this (although slowly) by replacing Groups within query strings by Groups.

I had to change the following lines to get the back on track for a really simple use (create ticket, reply ticket, resolve ticket…)

At line 549 of
/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm
Add
$QueryString =~ s/([\s,.’])Groups([\s.,’])/$1Groups$2/g;

Here I guess it will depend on where you installed RT
At line 430 of
vi /opt/rt4/sbin/…/lib/RT/Principal.pm
Add
$query =~ s/([\s,.’])Groups([\s.,’])/$1Groups$2/g;

10

We just see the same errors on a Percona XtraDB Cluster but already with Version 5.7.28-31 and not 8.

Are there are already any fixes to this?

Torsten

11

I’ve also run up against this bug. I’m using RT 4.4.2 and upgraded MySQL from 5.7 to 8.0. I don’t really want to supply a downgraded DB version. I’d prefer to upgrade RT. Does anyone know if this bug still exists?

12

You could add backquotes around:
Groups in schema.mysql file to solve this problem, like this:

CREATE TABLE `Groups`

13

There is also a branch remotes/origin/4.4/mysql8-quoted-tables which should do the trick. I’d expect to see it in 4.4.5 and 5.0.2 (hint, hint. :slight_smile: )

14

Having upgraded to Ubuntu 20.04, this still bugged me. I worked around it by simply changing the query in Principal.pm:

--- /usr/share/request-tracker4/lib/RT/Principal.orig   2022-03-16 08:21:54.061556232 +0100
+++ /usr/share/request-tracker4/lib/RT/Principal.pm     2022-03-16 08:23:39.812792911 +0100
@@ -427,6 +427,7 @@
             . $self->_HasRoleRightQuery(
                 EquivObjects => $args{'EquivObjects'}
             );
+       $query =~ s/([\s,.])Groups([\s.,])/$1`Groups`$2/g;
         $roles = $RT::Handle->dbh->selectcol_arrayref($query);
         unless ($roles) {
             $RT::Logger->warning( $RT::Handle->dbh->errstr );
2 Likes
15

Thanks a lot, valentyn. Ubuntu 22.04 LTS here running MySQL 8.0.32, moving RT 5.0.2 to 5.0.3 on a new database server and was getting this error when logging in with LDAP authentication:

[error]: DBD::mysql::db selectcol_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Groups, Principals, CachedGroupMembers WHERE Principals.Disabled = ‘0’ AND Cache’ at line 1 at /opt/rt5/sbin/…/lib/RT/Principal.pm line 435.

Your fix seems to have done the trick, hoping I don’t run into this issue with other queries and have to make other changes. Not sure how Best Practical missed this during testing; hopefully it is fixed at some point.

16

As noted in the thread here, RT previously did not support MySQL 8 because of changes made in MySQL. RT 5.0.4 beta1, now available for testing, now adds support for MySQL 8. We’re happy to hear any feedback if anyone can test.

1 Like
17

I’ve also implemented valentyn’s fix above in Principal.pm’s _HasRoleRightQuery function and Record.pm’s LockForUpdate function while getting my RT 5.0.3 instance running with MySQL 8:

*** rt-5.0.3/lib/RT/Principal.pm        2022-07-13 09:28:34.000000000 -0400
--- /opt/rt5/lib/RT/Principal.pm        2023-05-23 15:46:46.805091969 -0400
***************
*** 628,633 ****
--- 629,635 ----
          . "AND Principals.id = CachedGroupMembers.GroupId "
          . "AND CachedGroupMembers.MemberId IN (" . ( join ',', ('?') x (1 + @{ $groups->ItemsArrayRef } ) ) . ") "
      ;
+     $query =~ s/([\s,.])Groups([\s.,])/$1`Groups`$2/g;
      my @bind_values = ( 0, 0, 'Group', $self->Id, map { $_->id } @{ $groups->ItemsArrayRef } );

      if ( $args{'Roles'} ) {

The Record.pm LockForUpdate function had to be rewritten a little since the query was written out instead of being part of a variable:

*** rt-5.0.3/lib/RT/Record.pm   2022-07-13 09:28:34.000000000 -0400
--- /opt/rt5/lib/RT/Record.pm   2023-05-24 12:56:03.458711724 -0400
***************
*** 1615,1623 ****
              "UPDATE " .$self->Table.
                  " SET $pk = $pk WHERE 1 = 0");
      } else {
          return $self->_LoadFromSQL(
!             "SELECT * FROM ".$self->Table
!                 ." WHERE $pk = ? FOR UPDATE",
              $id,
          );
      }
--- 1615,1625 ----
              "UPDATE " .$self->Table.
                  " SET $pk = $pk WHERE 1 = 0");
      } else {
+       my $query = "SELECT * FROM ".$self->Table
+                 ." WHERE $pk = ? FOR UPDATE";
+       $query =~ s/([\s,.])Groups([\s.,])/$1`Groups`$2/g;
          return $self->_LoadFromSQL(
!               $query,
              $id,
          );
      }

I am glad to hear that 5.0.4 beta is planning to support MySQL 8, but I already have my installation up and am under a time constraint to complete my migration, so I want to avoid any other potential issues with upgrading to a new version right now. Thanks to the RT team for addressing the need for supporting MySQL 8.