Open Bug 1592129 Opened 2 years ago Updated 6 months ago

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

Categories

(Bugzilla :: Database, defect)

5.0.4
defect
Not set
normal

Tracking

()

UNCONFIRMED

People

(Reporter: jeczmien, Assigned: dylan)

References

(Blocks 1 open bug)

Details

Attachments

(7 files, 1 obsolete file)

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)

Summary: Bugzilla failed after mysql upgrade to 8.0.17 → Bugzilla failed after mysql upgrade to 8.0.17 due to "groups" keyword in mysql
Blocks: 1604051
Assignee: database → dylan
Attached file 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.

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.

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`

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

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

Ok, this one is just about 100%.

Note most of the other PRs are merged.

Attachment #9116041 - Attachment is obsolete: true

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

(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

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

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.

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.

You need to log in before you can comment on or make changes to this bug.