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.


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