Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Tables without primary keys
#1
Hey there,

I'm currently in the process of moving to another provider with my forum data and I've noticed there's at least 1 table in the mybb installation without a primary key. One of which being mybb_adminlog.

This is causing errors when trying to import the database at the other provider.

Is it a sustainable solution for me to add it manually for now, or should every table in a mybb installation have a primary key for MySQL 8.0 compatibility?
Founder of Surreal, proud MyBB user for 8 years!
Website | Forums
Reply
#2
Hi, we don't offer support for moving on to different platforms. But it might be useful to get an answer for this.

The mybb_adminlog doesn't need a primary key at first glance and thus would seem superfluous for the purpose of the table.

However, I fail to understand the reason for migrate this table outside MyBB, perhaps you could ignore it completely? If you want to check former logs you can always check your old DB, otherwise it seems unnecessary to migrate.

Edit: Additionally, I recall someone suggesting to add a primary key to this or similar tables (couldn't find it for linking), so it is possible that this is changed in the future or is at least being discussed.
Reply
#3
(2021-04-07, 06:10 PM)Omar G. Wrote: Hi, we don't offer support for moving on to different platforms. But it might be useful to get an answer for this.

The mybb_adminlog doesn't need a primary key at first glance and thus would seem superfluous for the purpose of the table.

However, I fail to understand the reason for migrate this table outside MyBB, perhaps you could ignore it completely? If you want to check former logs you can always check your old DB, otherwise it seems unnecessary to migrate.

Edit: Additionally, I recall someone suggesting to add a primary key to this or similar tables (couldn't find it for linking), so it is possible that this is changed in the future or is at least being discussed.

Perhaps I didn't communicate this that well - but I'm not moving away from MyBB, I'm changing hosting providers, and upgrading some backend packages on the way. 

It just turns out that primary keys are required on all imports from this particular hosting provider, so I'm wondering if it'll be better for me to just add them manually or wait for the MyBB team to do so (or maybe I can contribute to the project if it's something you guys want!)

Just not sure how to approach it really.
Founder of Surreal, proud MyBB user for 8 years!
Website | Forums
Reply
#4
What's the error you're getting? I don't understand what this provider would be doing to require a primary key on a database table. If importing via phpMyAdmin, it should just import. I've never heard of it causing a problem before.
MyReactions - All Plugins

Can you still feel the butterflies?

Free never tasted like pudding.
Reply
#5
(2021-04-07, 07:54 PM)Matt Wrote: What's the error you're getting? I don't understand what this provider would be doing to require a primary key on a database table. If importing via phpMyAdmin, it should just import. I've never heard of it causing a problem before.

Well yeah, I had the same thought. I'll log a ticket with DigitalOcean and see what they say about it. I wasn't using phpmyadmin as I normally would, this time I was trying out a managed MySQL 8 database.

I guess if they can't provide a solution then I'll just manage my own MySQL server.

Also adding the error I got:

ERROR 3750 (HY000) at line 25: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

Edit: Sure enough, they replied and said they can disable that option. If it matters, this is part of the response from them:

Quote:Primary keys are essential for certain management operations also for services that do not have standby or read replica service; any node replacements are performed by first bringing up a standby to which all data from the old master is replicated and without primary keys, this process may take exceedingly long or fail, Also failed nodes are replaced by restoring a backup, which requires playing back binary logs and that may not work if large tables without primary keys have had recent changes.
Founder of Surreal, proud MyBB user for 8 years!
Website | Forums
Reply
#6
Hi, I got confused by the word provider, in part my fault.

It seems this is an issue with your managed DB service, which if I understand correctly by the response they gave you should be related to some backup or synchronization feature they offer.

Disabling the requirement probably won't affect the functionality of MyBB but I won't be sure about their managed service or all / any of their features.
Reply
#7
Ah, okay makes more sense if it's a managed DB service with replication.

It would probably be easiest to just add an auto_increment column to the table manually then. It won't get in the way from MyBB's side and will mean they can keep the setting enabled.
MyReactions - All Plugins

Can you still feel the butterflies?

Free never tasted like pudding.
Reply
#8
Okay, great. Thanks for the info & help Smile I'll see what I end up doing, but this is enough to work with now.

Have a good one guys!
Founder of Surreal, proud MyBB user for 8 years!
Website | Forums
Reply
#9
Just for info and to consider when upgrading MySQL...
Tthis is an option (variable within the MySQL environment) added to MySQL version 8 and can be de/activated:

Quote:Error number: 3750; Symbol: ER_TABLE_WITHOUT_PK; SQLSTATE: HY000
Message: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

ER_TABLE_WITHOUT_PK was added in 8.0.13.
MySQL Error Reference:
https://dev.mysql.com/doc/mysql-errors/8...without_pk

[ExiTuS]
New Forum / MyBB 1.8.26 + innovative full-responsive Theme
Live-Escape-Game-Forum
Reply
#10
I think adding the key manually should also work and cause no issues on the MyBB side as already pointed above.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)