1592129 - Bugzilla failed after mysql upgrade to 8.0.17 due to "groups" keyword in mysql

Status: UNCONFIRMED

(Bugzilla :: Database, defect)

Description

[[e-mail]]

User Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:70.0) Gecko/20100101 Firefox/70.0

Steps to reproduce:

run ./checksetup.pl

Actual results:

error:

Fixing file permissions...
DBD::mysql::db selectrow_array 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 where name = ''' at line 1 [for Statement "SELECT id FROM groups where name = ''"] at Bugzilla/Install/DB.pm line 2497.
Bugzilla::Install::DB::_fix_group_with_empty_name() called at Bugzilla/Install/DB.pm line 358
Bugzilla::Install::DB::update_table_definitions(HASH(0x562aaa321460)) called at ./checksetup.pl line 175

Expected results:

checksetup should finish without error.

(groups is new keyword in mysql, so table 'groups' should be renamed)

Comment 1

[Dylan Hardison [:dylan] (he/him)]

Attachment: GitHub Pull Request

So the obvious fix here is to use quote_identifier around all tables and columns.
This is pretty easy in Bugzilla::Object or Bugzilla::DB or Bugzilla::Install::DB as we have a reference to the database connection. However, in Bugzilla::DB::Schema we don't.

So the first step would be make it so the Schema has access to the database connection.
The way Schema is structured is... let's say it is weird. So here I want to make it less weird.

Comment 2

[Dylan Hardison [:dylan] (he/him)]

Attachment: GitHub Pull Request: use Moo for the Schema classes

This is the next step, making the classes use Moo. The rationale for this is that I need a weak_ref attribute to for referencing the DB connection.

Comment 3

[Dylan Hardison [:dylan] (he/him)]

Attachment: GitHub Pull Request: Pass in Bugzilla::DB to Bugzilla::DB::Schema

Comment 4

[Dylan Hardison [:dylan] (he/him)]

Attachment: GitHub Pull Request: Shortcut for calling quote_identifier in strings

Since we have a lot of strings that need to have identifiers quoted, this is a bit of a hack.

Bugzilla->dbh->qi returns a hash that turns its keys into quote_identifier-quoted strings.
For instance, in mysql Bugzilla->dbh->qi->{pants} will return `pants`

Comment 5

[Dylan Hardison [:dylan] (he/him)]

Attachment: WIP: GitHub Pull Request: Begin using the identifier quoting shortcut

This begins using the identifier quoting shortcut. it's still a work in progress because the weekend is drawing to an end.

Comment 6

[Dylan Hardison [:dylan] (he/him)]

Attachment: GitHub Pull Request: Fix regexp compatibility error

MySQL 8 also updated regexp syntax (possibly this is pcre?), so that an unmatched { is now an error. This patch fixes that.

Comment 7

[Dylan Hardison [:dylan] (he/him)]

Attachment: GitHub Pull Request: Quote column and table names in most cases

Ok, this one is just about 100%.

Note most of the other PRs are merged.

Comment 8

[Bill Barry]

Trying to upgrade an existing install on a new machine:

DBD::mysql::db selectrow_array 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 where name = ''' at line 1 [for Statement "SELECT id FROM groups where name = ''"] at Bugzilla/DB.pm line 63.
Bugzilla::DB::ANON(Bugzilla::DB::Mysql=HASH(0xb978668), "SELECT id FROM groups where name = ''") called at Bugzilla/Install/DB.pm line 2497
Bugzilla::Install::DB::_fix_group_with_empty_name() called at Bugzilla/Install/DB.pm line 358
Bugzilla::Install::DB::update_table_definitions(HASH(0x329ffc8)) called at .\checksetup.pl line 175

Comment 9

[Bill Barry]

(not used to modern bugzilla, sorry about the previous comment trying again with md syntax to make it nicer)

DBD::mysql::db selectrow_array 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 where name = ''' at line 1 [for Statement "SELECT id FROM groups where name = ''"] at 
Bugzilla/DB.pm line 63.
        Bugzilla::DB::__ANON__(Bugzilla::DB::Mysql=HASH(0xb978668), "SELECT id FROM groups where name = ''") called at Bugzilla/Install/DB.pm line 2497
        Bugzilla::Install::DB::_fix_group_with_empty_name() called at Bugzilla/Install/DB.pm line 358
        Bugzilla::Install::DB::update_table_definitions(HASH(0x329ffc8)) called at .\checksetup.pl line 175

Comment 10

[Bill Barry]

Attachment: quote identifier groups during install/update in checksetup.pl

Comment 11

[Rogers.Doug]

I am getting this error for a new install. Is there a fix for this?

Comment 12

[bjoern]

oh, for a new install, you don't need this bug to be fixed. Just read

https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434

Best hint is 2. of the take-away lessons - If you need a database, don’t use MySQL or MariaDB. Use PostgreSQL.

Comment 13

[Jacob Lear]

If anyone is trying to figure out how to fix the code to get around this bug on the current stable release, 5.0.6, you need to fully qualify the groups table name in FROM and JOIN queries and INSERT, DELETE, ALTER, and UPDATE statements.

I found that the following files needed modified to successfully get my old Bugzilla site upgraded and working with MySQL 8:
Bugzilla/Bug.pm
Bugzilla/Group.pm
Bugzilla/Search.pm
Bugzilla/User.pm
Bugzilla/Product.pm
Bugzilla/Object.pm
Bugzilla/DB.pm
Bugzilla/DB/Schema.pm
Bugzilla/Install/DB.pm
editgroups.cgi
editproducts.cgi
editusers.cgi
sanitycheck.cgi
userprefs.cgi
whine.pl

In Bugzilla/Object.pm I fully qualified the $table variable on lines 131, 162, 389, 568, 648, and 747.
In Bugzilla/DB.pm I fully qualified the $table variable on lines 1523 (two places), 1533, 1541, 1554, 1563, and 1579.
In Bugzilla/DB/Schema.pm I fully qualified the $table variables on lines 2000, 2046, 2041, 2062.
In Bugzilla/Install/DB.pm in addition to fully qualifying groups table references, you need to fix the regular expression on line 4118 to be "'^[^,]+\\{'"

I hope these notes are helpful to someone facing similar challenges.