Table phpbb_acl_groups does not have any PRIMARY KEY.

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Foxina
Registered User
Posts: 24
Joined: Mon Jan 03, 2022 9:20 am

Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Foxina »

Hi.

I was moving my service (running 3.2.11) to an other server system, which requires:
- The tables must be InnoDB (successfully converted all tables which were still MyISAM)
- And all tables must have a primary key set. I get an error when restoring a backup on the new server:
ERROR 3098 (HY000) at line 46: The table does not comply with the requirements by an external plugin.
And /var/log/mysql/error.log:
[ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table phpbb_acl_groups does not have any PRIMARY KEY. This is not compatible with Group Replication.'
So, I see two options:

- Is this already fixed in 3.3? Maybe someone can just say yes or no, and save me some precious time.
- Add the primary key to the table? Is this really wise? Found this article about it:
https://forum.inductiveautomation.com/t ... le/19962/5
User avatar
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by david63 »

Foxina wrote: Mon Jan 03, 2022 10:53 am Is this already fixed in 3.3?
No

Not sure why
Foxina wrote: Mon Jan 03, 2022 10:53 am all tables must have a primary key set
This is only the first table that you have hit the problem with - there are many tables within phpBB that do not have a primary key and as I am not database expert I cannot say why this is the case nor can I comment on what the impact would be if you were to add a primary key to all tables.

It may be better if you were to raise this in the Bug Tracker where it will be seen by the developers and any appropriate action that is need will be taken
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by 3Di »

Foxina wrote: Mon Jan 03, 2022 10:53 am I was moving my service (running 3.2.11) to an other server system, which requires:
- The tables must be InnoDB
Foxina wrote: Mon Jan 03, 2022 10:53 am - And all tables must have a primary key set.
Foxina wrote: Mon Jan 03, 2022 10:53 am [ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table phpbb_acl_groups does not have any PRIMARY KEY. This is not compatible with Group Replication.'
Wow, that's indeed an issue with InnoDB and some plugin your host uses, and the conversion.

FYI that table phpbb_acl_groups has never had a Primary key AFAIK.

The requisites required by your new host seem to me to be a bit excessive.

Do you think all phpBB admins will have to convert their tables to InnoDB from now on?
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Foxina
Registered User
Posts: 24
Joined: Mon Jan 03, 2022 9:20 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Foxina »

Do you think all phpBB admins will have to convert their tables to InnoDB from now on?
Hi.
This I do not know, but as far as I know InnoDB provides more performance and most mysql clustering systems seem to require InnoDB:
https://hevodata.com/learn/myisam-vs-innodb/

I had no problem converting tables to InnoDB, after changing sql_mode parameter in the server to accept zero default value for dates (again something which is present in phpbb)
And this new system for me is using Mysql Group Replication, which again requires Primary Key in all tables.

And other possibility would be Percona XtraDB, which also seems to require InnoDB, but can survive without the Primary Key although it is not recommendable:

https://forums.percona.com/t/limitation ... -keys/2764
User avatar
Lumpy Burgertushie
Registered User
Posts: 69228
Joined: Mon May 02, 2005 3:11 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Lumpy Burgertushie »

if all of that was really an issue with phpBB then I would expect this place to be slammed with posts about it.
this is something in the way your host is setup.
as to your statement about most hosting requiring InnoDB etc., that is simply not true. maybe you only checked the few that do.

robert
Premium phpBB 3.3 Styles by PlanetStyles.net

I am pleased to announce that I have completed the first item on my bucket list. I have the bucket.
Foxina
Registered User
Posts: 24
Joined: Mon Jan 03, 2022 9:20 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Foxina »

as to your statement about most hosting requiring InnoDB etc., that is simply not true. maybe you only checked the few that do.
This depends of course the replication method on clustering environment.
Percona, which I believe is quite used technology for clustering has this:
However, it’s still generally a best practice to have explicit PKs. If you don’t define them, then you will have in implicit hidden 6-byte PK in your Innodb table taking up space that you can’t use for any querying. Innodb is very much optimized towards PK lookups, so it benefits you to have one you can query by.
https://forums.percona.com/t/limitation ... -keys/2764

So, one issue is the no Primary Keys, which apparently not fixed.
Other is the issue when converting MyIsam to more effective InnoDB:
Some tables have 0 as default for date columns, and at least mysql will not change them to InnoDB without changing thes sql_mode first.

https://ixnfo.com/en/the-solution-of-er ... labla.html
https://stackoverflow.com/questions/385 ... nto-innodb

There was an advice in this topic, to open bug tickets:
I have not done those for phpbb, but I do not mind if someone wants to do that.
Foxina
Registered User
Posts: 24
Joined: Mon Jan 03, 2022 9:20 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Foxina »

More recent topic on primary keys.
Digital Ocean seems to have an issue with tables now having them as well:

viewtopic.php?p=15787316
"Our systems have indicated that your MySQL cluster, forumdb, has tables without primary keys. We have identified that MySQL tables without primary keys can lead to service replication issues that jeopardize performance and availability. If primary keys are not present for database tables exceeding 5,000 rows, data-loss can occur."
Maybe this really is something you might want to look at?
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6029
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by thecoalman »

I would suggest the reason the issue has been non issue is because very few are using database replication. Presumably this is for backup services provided by the host? You can open a ticket here:

http://tracker.phpbb.com/

You can recheck but I didn't find anything similar to your issue.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
Foxina
Registered User
Posts: 24
Joined: Mon Jan 03, 2022 9:20 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Foxina »

thecoalman wrote: Mon Jan 03, 2022 11:40 pm I would suggest the reason the issue has been non issue is because very few are using database replication. Presumably this is for backup services
Thanks for the answer.
The issue is not about backups anyway: For this we have arbitrary methods of course.
The issue regards clustering: I am doing this as a contract to a busy site, where sometimes number of users exceeds the resources of a single LAMP server. Even if backed up by Cloudflare or similar CDN.
The volume of database queries exceeds the server resources.

After some investigating came up with the issues of MyIsam to InnoDB and the lack of primary keys in tables.
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6029
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by thecoalman »

I wish I had better answer for you but I don't. AFAIK the primary key should be incremental value otherwise you may have performance issues. Adding a column would solve that but introduces potential issues with future releases of phpBB where they are added by update. I'm taking a wild guess this is no small issue with replication.

Hopefully someone else will have better answer. ;)
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 52783
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by stevemaury »

ERROR 3098 (HY000) at line 46: The table does not comply with the requirements by an external plugin.
This^^

So this does not appear to be a phpBB issue. Do we think database replication is the external plugin involved?
I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by 3Di »

stevemaury wrote: Fri Jan 07, 2022 4:08 am
ERROR 3098 (HY000) at line 46: The table does not comply with the requirements by an external plugin.
This^^

So this does not appear to be a phpBB issue. Do we think database replication is the external plugin involved?
Looks like something I posted here about that
viewtopic.php?p=15795471#p15795471
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Foxina
Registered User
Posts: 24
Joined: Mon Jan 03, 2022 9:20 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Foxina »

colnector
Registered User
Posts: 186
Joined: Tue Aug 19, 2008 5:04 pm

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by colnector »

As I'm not on the Jira issue, I'd just like to add two things:
1/ Query to show all tables missing primary keys can be found on viewtopic.php?t=2609161
2/ InnoDB tables always have a primary key (you may wanna read about "cluster index") and not declaring one is a bad practice as it may result in a bigger hidden primary key behind the scenes.

As it's very simply to fix this issue with a surrogate primary key that's never referred to from code, I see no reason to further delay this fix. Of course, if a proper primary key can be selected, even better.
Foxina
Registered User
Posts: 24
Joined: Mon Jan 03, 2022 9:20 am

Re: Table phpbb_acl_groups does not have any PRIMARY KEY.

Post by Foxina »

colnector wrote: Fri Dec 16, 2022 12:16 pm
As it's very simply to fix this issue with a surrogate primary key that's never referred to from code, I see no reason to further delay this fix. Of course, if a proper primary key can be selected, even better.
I agree and would appreciate if this improvement would take place:
With the current situation, it is not possible to deploy more than one db server, which is an issue with really busy boards.

Return to “[3.2.x] Support Forum”