Primary keys and MySQL cluster

[matejzero]

We are migrating to MySQL cluster and one of the requirements is that every table has a primary key so deletes work across the cluster.

I noticed a few tables don't have primary keys:

• phplist_i18n
• phplist_linktrack_userclick
• phplist_user_blacklist
• phplist_user_blacklist_data

My SQL knowledge is poor to honest, but here is my go at the issue:

• not sure if we could just add auto-incremented id field here. Looked around and all queries are using field names so extra field should work
• add new id auto increment field for linktrack_userclick. I only found one query (https://github.com/phpList/phplist3/blob/main/public_html/lists/admin/convertstats.php#L105) and it uses select fields so it should be OK to add id field to the table and not break anything.
• we can use email field in user_blacklist and user_blacklist_data as primary key since it's only unique field and not null

Is there any chance primary keys could be added so that phplist is compatible with cluster version.

[michield]

Interesting. If you're interested in the DB structure, it's outlined here:

https://github.com/phpList/phplist3/blob/main/public_html/lists/admin/structure.php

I guess adding an ID with auto_increment may work. If you do so, it would be interesting to report your findings so that more people are aware of this.

[matejzero]

We moved our phplist to a cluster mysql instance.

We added primary key to phplist_i18n and phplist_linktrack_userclick. phplist_user_blacklist and phplist_user_blacklist_data tables didn't need any change.

So far it's been running OK in prod for the last 10 days, but we have low traffic phplist install.