MySQL 8 support

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Post Reply
jordon
Registered User
Posts: 4
Joined: Wed Jan 04, 2006 8:04 pm
Location: Manchester, UK

MySQL 8 support

Post by jordon » Mon Apr 15, 2019 1:03 pm

Good afternoon :)

The latest build of PHPBB3 still doesn't support conversion from phpBB2 to in a MySQL8 environment.

This has been raised previously, e.g. viewtopic.php?t=2419146

As indicated in the topic above, this is due to mySQL 8 making groups a reserved word causing the following query to fail:

Code: Select all

SELECT groups.group_id, groups.group_type, groups.group_name, groups.group_description FROM phpbb_groups groups WHERE (groups.group_single_user = 0) LIMIT 2000
convert_phpbb20.php & functions_phpbb20.php need updating to handle this.

As i see it, we have two options:
  • Wrap groups table alias in mySQL backticks, e.g.

    Code: Select all

    `groups`
    Use an different alias, i.e. grp
I can see that the array for this table is populated in convert_phpbb20.php:

Code: Select all

array(
	'target'		=> GROUPS_TABLE,
	'autoincrement'	=> 'group_id',
	'query_first'	=> array(
		array('target', $convert->truncate_statement . GROUPS_TABLE),
		array('target', $convert->truncate_statement . TEAMPAGE_TABLE),
	),

	array('group_id',				'groups.group_id',					''),
	array('group_type',				'groups.group_type',				'phpbb_convert_group_type'),
	array('group_display',			0,									''),
	array('group_legend',			0,									''),
	array('group_name',				'groups.group_name',				'phpbb_convert_group_name'), // phpbb_set_encoding called in phpbb_convert_group_name
	array('group_desc',				'groups.group_description',			'phpbb_set_encoding'),

	'where'			=> 'groups.group_single_user = 0',
),
Unfortunately, there appears to be no support for table alias in the array.

Presumably this array gets parsed to spit out a finished SQL query - i think the quick win here is to edit that function to use back-ticks ``

Does anyone happen to know what function i should be looking at to this make this change?

Or is there any word on fixing this properly?

Cheers.

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21082
Joined: Fri Aug 29, 2008 9:49 am
Location: Watching cricket - definitely

Re: MySQL 8 support

Post by Mick » Mon Apr 15, 2019 1:44 pm

Rather than spending all your time trying to figure out how to make 8 work just use a lower version until such time as phpBB announces it is compatible. 8 has been round since 2016 so it’s not going anywhere, there’s no rush.
"The more connected we get the more alone we become" - Kyle Broflovski

jordon
Registered User
Posts: 4
Joined: Wed Jan 04, 2006 8:04 pm
Location: Manchester, UK

Re: MySQL 8 support

Post by jordon » Mon Apr 15, 2019 2:08 pm

Hi Mick,

Cheers for the reply.

Usually i would stick with the older version (I already have for 11 months :D ) but some other sites hosted on said server forced an update to 8. I'll need to install MySQL 5.7 on a non-standard port to do what you say, or build a new server of course - i was exploring if tweaking the PHP would be less effort.

Since writing my post above, i can see that the logic in question is in convertor.php in functions convert_data() and build_insert_query() - it's pretty complex - the latter already has alias logic which would need extending.

Suffice to say, i'll be going the 2nd MySQL route

Post Reply

Return to “[3.2.x] Convertors”