convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
alex555
Registered User
Posts: 4
Joined: Fri Jun 14, 2024 9:57 am

convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by alex555 »

Good morning,

I am trying to update phpBB 2 (yes, its very old) to latest version 3.3.12. Convert does not work on MySQL 8 because 'groups' (table name) is reserved word.
I assume the same problem will be raised with any other version 3.x.x on MySQL 8

I am receiving the following error
{"errors":[{"title":"General Error:SQL ERROR [ mysqli ]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\\x0AWHERE (groups.group_single_user = 0)\\x0A LIMIT 2000' at line 2 [1064]SQLSELECT groups.group_id, groups.group_type, groups.group_name, groups.group_description \nFROM groups\nWHERE (groups.group_single_user = 0)\n LIMIT 2000 in file .../phpbb\/db\/driver\/driver.php on line 1031"}],"over":true}
this query fails

Code: Select all

SELECT groups.group_id, groups.group_type, groups.group_name, groups.group_description FROM groups WHERE (groups.group_single_user = 0) LIMIT 2000
Fix should be very simple, use quotation marks like

Code: Select all

SELECT groups.group_id, groups.group_type, groups.group_name, groups.group_description FROM 'groups' WHERE (groups.group_single_user = 0) LIMIT 2000
This fix also may help in the future for some other databases.
But I am not sure which file should be corrected, tried to understand how it works but failed so far. Its not driver.php file where error is catched
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53551
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}

Re: convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by Brf »

phpBB does not have a table named "groups". It would have a prefix and be like phpbb_groups
alex555
Registered User
Posts: 4
Joined: Fri Jun 14, 2024 9:57 am

Re: convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by alex555 »

There is such table in phpBB version 2
CREATE TABLE groups(
group_id mediumint(8) NOT NULL auto_increment,
group_type tinyint(4) DEFAULT '1' NOT NULL,
group_name varchar(40) NOT NULL,
group_description varchar(255) NOT NULL,
group_moderator mediumint(8) NOT NULL,
group_single_user tinyint(1) DEFAULT '1' NOT NULL,
PRIMARY KEY (group_id),
KEY group_single_user (group_single_user)
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53551
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}

Re: convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by Brf »

alex555 wrote: Fri Jun 14, 2024 12:23 pm There is such table in phpBB version 2
CREATE TABLE groups(
That would only be if you omitted the table_prefix. By default the table_prefix is phpbb_ but can be changed when you installed.
alex555
Registered User
Posts: 4
Joined: Fri Jun 14, 2024 9:57 am

Re: convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by alex555 »

Yes, but that installation was 20 years ago.

I have phpBB_groups and other phpBB_tables* as well but all these tables empty.
I think I can remove all empty tables, rename tables* -> phpBB_tables* and start Update process again.

But still, it would be better to use quotation marks for table names in the scripts, this will help me and may help other users that for some reason did not use any prefix.
Any idea, where to add '' marks?
alex555
Registered User
Posts: 4
Joined: Fri Jun 14, 2024 9:57 am

Re: convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by alex555 »

Ok, renamed tables, converted database, removed install/ folder.
But still, this code modification (quotation marks) may help other users.

My new phpBB 3.3.12 still does not work - 500 Internal server error
but that's probably different story.
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 28858
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier

Re: convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by Paul »

I am pretty sure that quoting table names is different over the different database systems phpBB support, so it is not really possible to simply quote all table names.
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26849
Joined: Fri Aug 29, 2008 9:49 am

Re: convert from phpBB does not work on MySQL 8 because 'groups' is reserved word. Where to add quotation marks?

Post by Mick »

What is the 500 error you’re seeing? Usually 500 errors are suppressed PHP issues.
  • "The more connected we get the more alone we become” - Kyle Broflovski© 🇬🇧

Return to “[3.3.x] Convertors”