Due to a conflict between the new reserved word GROUPS in mySQL 8 and a table named Groups in RT it is currently not possible to use RT with mySQL 8
However, I have managed to do it anyway by a few changes in RT 4.4.4
In the RT perl code I have changed the tablename “Groups” to “Groups” (enclosed in backticks) a number of places and in SearchBuilder.pm _GetAlias added a statement to remove backticks when generating an alias.
/usr/share/perl5/vendor_perl/DBIx/SearchBuilder.pm:1283: my $table = shift; $table =~ s/`//g;
rt4/share/html/Elements/ShowMemberships:66: TABLE2 => ‘Groups’,
rt4/lib/RT/Interface/Web.pm:4109: TABLE2 => ‘Groups’,
rt4/lib/RT/SearchBuilder/Role/Roles.pm:123: TABLE2 => ‘Groups’,
rt4/lib/RT/Report/Tickets.pm:547: push @{ $res{’Groups’} }, $group_by->{‘NAME’};
rt4/lib/RT/Test.pm:757: FIELD1 => ‘GroupId’, TABLE2 => ‘Groups’, FIELD2 => ‘id’,
rt4/lib/RT/Group.pm:79:sub Table {’Groups’}
rt4/lib/RT/User.pm:1735: TABLE2 => ‘Groups’,
rt4/lib/RT/User.pm:2838: TABLE2 => ‘Groups’,
rt4/lib/RT/Principal.pm:578: " FROM Groups, Principals, CachedGroupMembers WHERE "
rt4/lib/RT/Groups.pm:79:sub Table { ‘Groups’}
I have not done sufficient testing to verify, that thís is sufficient, but I do have access to the messages.
The rt-validator will need some careful updating.
2 Likes
I ran into a problem, but adding this solved it.
/usr/share/perl5/vendor_perl/DBIx/SearchBuilder/Handle.pm
sub SimpleQuery {
my $QueryString = shift; $QueryString =~ s/``//g;
1 Like
I’m doing this as well, I’m having a trouble with the sub Table { 'Groups' } because it’s used in some queries as the alias too and then shows up like `Groups`_2
Much less change if you upgrade SearchBuilder to 1.74
cpan
upgrade DBIx::SearchBuilder
Then the changes are these as follows for 4.4.4
diff -ru /var/bk-rt4.2023/rt4/lib/RT/Principal.pm /opt/rt4/lib/RT/Principal.pm
--- /var/bk-rt4.2023/rt4/lib/RT/Principal.pm 2023-03-24 09:08:28.329881117 -0700
+++ /opt/rt4/lib/RT/Principal.pm 2023-03-24 10:20:02.110088559 -0700
@@ -423,10 +423,11 @@
my $roles;
{
my $query
- = "SELECT DISTINCT Groups.Name "
+ = "SELECT DISTINCT `Groups`.Name "
. $self->_HasRoleRightQuery(
EquivObjects => $args{'EquivObjects'}
);
+
$roles = $RT::Handle->dbh->selectcol_arrayref($query);
unless ($roles) {
$RT::Logger->warning( $RT::Handle->dbh->errstr );
@@ -556,7 +557,7 @@
my @roles = $self->RolesWithRight(%args);
return 0 unless @roles;
- my $query = "SELECT Groups.id "
+ my $query = "SELECT `Groups`.id "
. $self->_HasRoleRightQuery( %args, Roles => \@roles );
$self->_Handle->ApplyLimits( \$query, 1 );
@@ -575,13 +576,13 @@
);
my $query =
- " FROM Groups, Principals, CachedGroupMembers WHERE "
+ " FROM `Groups`, Principals, CachedGroupMembers WHERE "
# Never find disabled things
. "Principals.Disabled = 0 " . "AND CachedGroupMembers.Disabled = 0 "
# We always grant rights to Groups
- . "AND Principals.id = Groups.id "
+ . "AND Principals.id = `Groups`.id "
. "AND Principals.PrincipalType = 'Group' "
# See if the principal is a member of the group recursively or _is the rightholder_
@@ -594,7 +595,7 @@
if ( $args{'Roles'} ) {
$query .= "AND (" . join( ' OR ',
- map $RT::Handle->__MakeClauseCaseInsensitive('Groups.Name', '=', "'$_'"),
+ map $RT::Handle->__MakeClauseCaseInsensitive('`Groups`.Name', '=', "'$_'"),
@{ $args{'Roles'} }
) . ")";
}
Hi,
We ran into this same (or similar) problem a good while ago, see RT 5.0.1 initialize-database fails on Ubuntu 20 LTS - #2 by rtshouldberewritten. Instead of patching the code here and there to make mysql8 work, we switched to mariadb. We started off with v10.5 and now run on v10.7. No code adaptations were needed.