Errors on MySQL 8

I recently upgraded MySQL from 5 to 8 but get the error below when running RT. It seems GROUPS is now a reserved keyword ( ) and field names are not always quoted.

rt 4.4.2
mysql 8.0.11

  [/opt/rt4/share/html/autohandler:53] (/opt/rt4/sbin/../lib/RT/Interface/Web/
Trace begun at /opt/rt4/sbin/../lib/ 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/ line 430.^J^JStack:^J  [/opt/rt4/sbin/../lib/RT/]^J  [/opt/rt4/sbin/../lib/RT/Interface/]^J  [/opt/rt4/sbin/../lib/RT/Interface/]^J  [/opt/rt4/share/html/autohandler:53]^J') called at /opt/rt4/sbin/../lib/RT/Interface/Web/ line 208
HTML::Mason::Exception::as_rt_error('HTML::Mason::Exception=HASH(0x5614ae8eddb0)') called at /usr/share/perl5/HTML/Mason/ line 278
HTML::Mason::Exception::as_string('HTML::Mason::Exception=HASH(0x5614ae8eddb0)', undef, '') called at /usr/share/perl5/HTML/Mason/ 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/ line 518
HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0x5614ae6f12a0)') called at /usr/share/perl5/HTML/Mason/ line 96
eval {...} at /usr/share/perl5/HTML/Mason/ line 96
HTML::Mason::Request::PSGI::exec('RT::Interface::Web::Request=HASH(0x5614ae6f12a0)') called at /usr/share/perl5/HTML/Mason/ line 342
HTML::Mason::Interp::exec(undef, undef) called at /usr/share/perl5/HTML/Mason/ line 59
eval {...} at /usr/share/perl5/HTML/Mason/ 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/ line 52
HTML::Mason::PSGIHandler::Streamy::__ANON__('CODE(0x5614ae8f9fd0)') called at /usr/share/perl5/Plack/ line 339
Plack::Util::__ANON__('CODE(0x5614aea0bd58)') called at /usr/share/perl5/Plack/Handler/ line 149
Plack::Handler::FCGI::run('Plack::Handler::FCGI=HASH(0x5614ae725118)', 'CODE(0x5614ae778528)') called at /usr/share/perl5/Plack/ line 84
Plack::Loader::run('Plack::Loader=HASH(0x5614ae724db8)', 'Plack::Handler::FCGI=HASH(0x5614ae725118)') called at /usr/share/perl5/Plack/ line 277
Plack::Runner::run('RT::PlackRunner=HASH(0x5614a7033450)') called at /opt/rt4/sbin/../lib/RT/ line 150
eval {...} at /opt/rt4/sbin/../lib/RT/ line 150
RT::PlackRunner::run('RT::PlackRunner=HASH(0x5614a7033450)') called at /opt/rt4/sbin/rt-server.fcgi line 162

Did you run mysql_upgrade after running the upgrade?

mysql_upgrade -u XXXXX -pXXXXXXXX --force

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)

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’

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

Thanks for the follow up.

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.

I also have this problem…

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
$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/
$query =~ s/([\s,.’])Groups([\s.,’])/$1Groups$2/g;

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?