Getting an error with creating database with latest MySQL version 8.0 for Bugzilla installation.
I'm setting up a new server for Bugzilla with below configuration.
Bugzilla version : 5.0.6
Strawberry PERL version : 5.28.2.1
MySQL version : 8.0
My current setup is working fine with older version of MySQL 5.7.27. But somehow have to migrate to newer version of MySQL 8.0 and with this I'm getting an error while creating table entry in database.
On google, what I found is that something related to 'GROUPS'. This keyword is reserved in MySQL 8.0 and bugzilla trying to use that keyword. I don't have a knowledge of MySQL so I couldn't figured out this problem.
Below is the output of checksetup.pl
.... ....
Checking for DBD-mysql (v4.001) ok: found v4.050
Checking for MySQL (v5.0.15) ok: found v8.0.17
Adding new table bz_schema...
Initializing bz_schema...
Creating tables...
Converting attach_data maximum size to 100G...
Setting up choices for standard drop-down fields:
priority op_sys resolution bug_status rep_platform bug_severity
Creating ./data directory...
Creating ./data/assets directory...
Creating ./data/attachments directory...
Creating ./data/db directory...
Creating ./data/extensions directory...
Creating ./data/mining directory...
Creating ./data/webdot directory...
Creating ./graphs directory...
Creating ./skins/custom directory...
Creating ./data/extensions/additional...
Creating ./data/mailer.testfile...
Creating ./Bugzilla/.htaccess...
Creating ./data/.htaccess...
Creating ./data/assets/.htaccess...
Creating ./data/attachments/.htaccess...
Creating ./data/webdot/.htaccess...
Creating ./graphs/.htaccess...
Creating ./lib/.htaccess...
Creating ./template/.htaccess...
Creating contrib/.htaccess...
Creating t/.htaccess...
Creating xt/.htaccess...
Precompiling templates...done.
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(0x34e8cb8)) called at checksetup.pl line 175
Groups can still be used, however, you need to add the database name in front of every call to the "GROUPS" table. In the case of Bugzilla standard install, the database name is 'bugs'. Example from line 1643 in DB.pm:
$dbh->do("ALTER TABLE groups DROP PRIMARY KEY");
Changed to:
$dbh->do("ALTER TABLE bugs.groups DROP PRIMARY KEY");
There are about a dozen SQL statements that make a call to that table in DB.pm. You will have to add it to all of them.