Errors on MySQL 8


#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)

https://pastebin.com/hTLNCtdF


#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.