[TS3] making teamspeak3 mariadb galera-cluster ready

TeamSpeak Server
1

hello!

i trying to migrate my teamspeak3 database to a maria galera-cluster (v4). to make this working, every table needs a PRIMARY key. see: Limitations of Galera Cluster | FromDual

unfortunately some ts3 tables dont have a PRIMARY key:
group_channel_to_client
perm_client
perm_channel_groups
perm_channel_clients
custom_fields_old
tokens
perm_channel
teamspeak3_metadata
channel_properties
group_server_to_client
perm_server_group
instance_properties
complains
server_properties
client_properties

is it safe to add an id column on every of these tables and make it a PRIMARY key?

best regards
thomas

2

Yes, it should not interfere with the current sql queries.
You can also view and edit all sql queries by changing the files in sql folder.
The templating might look strange at first sight, but should not to hard to get around. Just remember that the queries are cached, and changes are only propagated on restarts of the server.

3 Likes
3

hello Maximilian!

thanks for your reply. i’ve found 3 queries in the sql directory which could be theoretically problematic:
token_get_by_key.sql:select * from tokens where server_id=:server_id: and token_key=:token_key:
token_list.sql:select * from tokens where server_id=:server_id:
complain_get_by_serverid.sql:select * from complains where server_id = :server_id:;

fortunately, after adding the necessacy primary-key’s, ts3 and didn’t show any faulty behavior. also tested the complain and token feature without any problem.

sql queries to make ts3 mariadb-galera ready:
ALTER TABLE teamspeak3.group_channel_to_client ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.perm_client ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.perm_channel_groups ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.perm_channel_clients ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.tokens ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.perm_channel ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.teamspeak3_metadata ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.channel_properties ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.group_server_to_client ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.perm_server_group ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.instance_properties ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.complains ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.server_properties ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE teamspeak3.client_properties ADD COLUMN id_primary INT PRIMARY KEY AUTO_INCREMENT;

thomas