Hello, please update the script SqliteDB to MariaDB

[hunterpl]

Hello, Dear Creator!

Here is ATHP Company we would like thank you for that script, but you need update the script due some records in structures needs to be changed because of errors in later, when instance is active and crash in some cases.

First description of channels have to be changed from value ( varchar ) of 255 to 8192 corrections: CREATE TABLE channel_properties ( server_id integer unsigned, id integer unsigned, ident varchar(100) NOT NULL, value varchar(8192));

The Table " client " need changes in structure: client_month_upload BIGINT = 20 client_month_download BIGINT = 20 client_total_upload BIGINT = 20 client_total_download BIGINT = 20

The Table " servers " need changes in structure: server_month_upload BIGINT = 20 server_month_download BIGINT = 20 server_total_upload BIGINT = 20 server_total_download BIGINT = 20

You can see all structures how it looks like when you start from the scratch MariaDB of Teamspeak 3, thank you.

Best Regards CEO Mateusz Gladosz TS3Bahu LTD

[NotoriousPyro]

Can you provide some examples of the client and servers tables structure changes that are needed and what the script is doing now?

[NotoriousPyro]

I can see what you mean, are your tables also by default INT(10) for the lines you specify?

[NotoriousPyro]

And on the varchar(8192) which part do you specify as my tables look fine? By default the script is only changing varchar with no length to 255, all else with a length is applied with that length. Is it for ident?

[NotoriousPyro]

The changes you request are specific to TS3 but I have put the required changes into an SQL query for you which should do what you need.

ALTER TABLE channel_properties
  MODIFY ident varchar(255),
  MODIFY value varchar(8192)
;

ALTER TABLE server_properties
  MODIFY value varchar(2048)
;

ALTER TABLE clients
  MODIFY client_month_upload bigint(20),
  MODIFY client_month_download bigint(20),
  MODIFY client_total_upload bigint(20),
  MODIFY client_total_download bigint(20)
;

ALTER TABLE servers
  MODIFY server_month_upload bigint(20),
  MODIFY server_month_download bigint(20),
  MODIFY server_total_upload bigint(20),
  MODIFY server_total_download bigint(20)
;

[hunterpl]

Hello, Dear NotoriousPyro

Thank you, yes exactly what i mean to alter proper Query in phpmyadmin manually, some changes are required to convert SqliteDB database into MariaDB format for Teamspeak 3 servers.

[NotoriousPyro]

Can you try the SQL query after running the converter script then importing into MariaDB and see if that works OK? This you would need to run against your tables after running the script to convert it.

[hunterpl]

Hello, Dear Creator!

We doing step by step from this guide: https://pyronexus.com/2016/10/30/teamspeak-3-sqlite3-to-mariadb-mysql-guide/

Yes i did it works well, but there is one problem, if you convert the big public database teamspeak 3, we have to force in script: " MyDBName.mariadb.sql " change the line to " CREATE TABLE channel_properties ( server_id integer unsigned, id integer unsigned, ident varchar(255) NOT NULL, value varchar(8192)); earlier before to convert to MariaDB Database, because while converting we got errors in SSH, can not put descriptions more than 255 characters and stops the converting, after successfully convert we make your query through PHPMyAdmin in SQL for each customer:

ALTER TABLE channel_properties MODIFY ident varchar(255), MODIFY value varchar(8192) ;

ALTER TABLE server_properties MODIFY value varchar(2048) ;

ALTER TABLE clients MODIFY client_month_upload bigint(20), MODIFY client_month_download bigint(20), MODIFY client_total_upload bigint(20), MODIFY client_total_download bigint(20) ;

ALTER TABLE servers MODIFY server_month_upload bigint(20), MODIFY server_month_download bigint(20), MODIFY server_total_upload bigint(20), MODIFY server_total_download bigint(20) ;`

Perfectly works and fix the MariaDB Database for Teamspeak 3, additionally we have to paste SQL Query from file: convert_mysql_to_mariadb.sql:

-- fix some issues from the past that hinder conversion here: ALTER TABLE channel_properties MODIFY ident VARCHAR(255), MODIFY value VARCHAR(8192);

-- do the actual conversions ALTER TABLE channel_properties CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE channel_properties; OPTIMIZE TABLE channel_properties;

ALTER TABLE channels CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE channels; OPTIMIZE TABLE channels;

ALTER TABLE client_properties CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE client_properties; OPTIMIZE TABLE client_properties;

ALTER TABLE clients CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE clients; OPTIMIZE TABLE clients;

ALTER TABLE groups_channel CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE groups_channel; OPTIMIZE TABLE groups_channel;

ALTER TABLE groups_server CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE groups_server; OPTIMIZE TABLE groups_server;

ALTER TABLE group_server_to_client CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE group_server_to_client; OPTIMIZE TABLE group_server_to_client;

ALTER TABLE group_channel_to_client CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE group_channel_to_client; OPTIMIZE TABLE group_channel_to_client;

ALTER TABLE perm_channel CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE perm_channel; OPTIMIZE TABLE perm_channel;

ALTER TABLE perm_channel_clients CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE perm_channel_clients; OPTIMIZE TABLE perm_channel_clients;

ALTER TABLE perm_channel_groups CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE perm_channel_groups; OPTIMIZE TABLE perm_channel_groups;

ALTER TABLE perm_client CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE perm_client; OPTIMIZE TABLE perm_client;

ALTER TABLE perm_server_group CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE perm_server_group; OPTIMIZE TABLE perm_server_group;

ALTER TABLE bindings CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE bindings; OPTIMIZE TABLE bindings;

ALTER TABLE server_properties CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE server_properties; OPTIMIZE TABLE server_properties;

ALTER TABLE servers CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE servers; OPTIMIZE TABLE servers;

ALTER TABLE tokens CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE tokens; OPTIMIZE TABLE tokens;

ALTER TABLE messages CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE messages; OPTIMIZE TABLE messages;

ALTER TABLE complains CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE complains; OPTIMIZE TABLE complains;

ALTER TABLE bans CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE bans; OPTIMIZE TABLE bans;

ALTER TABLE instance_properties CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE instance_properties; OPTIMIZE TABLE instance_properties;

ALTER TABLE custom_fields CONVERT TO CHARACTER SET 'utf8mb4', DEFAULT CHARACTER SET 'utf8mb4'; REPAIR TABLE custom_fields; OPTIMIZE TABLE custom_fields;

CREATE TABLE teamspeak3_metadata( ident varchar(100) NOT NULL UNIQUE, value varchar(255) ) CHARACTER SET 'utf8mb4';

INSERT INTO teamspeak3_metadata(ident,value) VALUES('mysql5.5_ready', '1');

That's all, and then, we can active instance of MariaDB teamspeak 3 with non errors.

[NotoriousPyro]

Can you try running this on one of the files?

If you first run the perl script, and then run this, it should correct the errors and thus will allow you to then import the DB.

Use it like so: script.sh filename.mariadb.sql

#!/usr/bin/env bash

file="$1"

if [ -z "$file" ]; then
    echo "No filename specified. Aborted."
    exit 1
fi

sed -i '/TABLE channel_properties/,/);/d' $file
sed -i '/TABLE server_properties/,/);/d' $file
sed -i '/TABLE clients/,/);/d' $file
sed -i '/TABLE servers/,/);/d' $file

echo -e "CREATE TABLE channel_properties(
  server_id int unsigned,
  id     int unsigned,
  ident  varchar(255) NOT NULL,
  value  varchar(8192)
)  CHARACTER SET 'utf8mb4';
CREATE TABLE clients(
  client_id               int PRIMARY KEY AUTO_INCREMENT NOT NULL,
  server_id               int unsigned,
  client_unique_id        varchar(40),
  client_nickname         varchar(100),
  client_login_name       varchar(20) UNIQUE,
  client_login_password   varchar(40),
  client_lastconnected    int unsigned,
  client_totalconnections int unsigned default 0,
  client_month_upload     bigint unsigned default 0,
  client_month_download   bigint unsigned default 0,
  client_total_upload     bigint unsigned default 0,
  client_total_download   bigint unsigned default 0,
  client_lastip           varchar(45),
  org_client_id           int
) CHARACTER SET 'utf8mb4';
CREATE TABLE server_properties(
  server_id int unsigned,
  id     int unsigned,
  ident  varchar(100) NOT NULL,
  value  varchar(2048)
) CHARACTER SET 'utf8mb4';
CREATE TABLE servers(
  server_id             int PRIMARY KEY AUTO_INCREMENT NOT NULL,
  server_port           int unsigned,
  server_autostart      int unsigned,
  server_machine_id     varchar(50),
  server_month_upload   bigint unsigned default 0,
  server_month_download bigint unsigned default 0,
  server_total_upload   bigint unsigned default 0,
  server_total_download bigint unsigned default 0
) CHARACTER SET 'utf8mb4';\n$(cat $file)" > $file

[hunterpl]

Hello,

i followed your guide and paste the error:

ERROR 1136 (21S01) at line 139022: Column count doesn't match value count at row 1

[NotoriousPyro]

Did you run the script above?

[hunterpl]

Please my output of SSH commands:

root@ts3bahu:/home/hunterpl# sqlite3 ts3server.sqlitedb .dump > MyDBName.sqlite.sql root@ts3bahu:/home/hunterpl# ./mariadb.pl MyDBName.sqlite.sql > MyDBName.mariadb.sql root@ts3bahu:/home/hunterpl# ./convert.sh MyDBName.mariadb.sql /usr/bin/env: 'bash\r': No such file or directory root@ts3bahu:/home/hunterpl# chmod +x convert.sh root@ts3bahu:/home/hunterpl# chown -hR root:root convert.sh root@ts3bahu:/home/hunterpl# ./convert.sh MyDBName.mariadb.sql /usr/bin/env: 'bash\r': No such file or directory root@ts3bahu:/home/hunterpl# nano convert.sh root@ts3bahu:/home/hunterpl# ./convert.sh MyDBName.mariadb.sql bash: ./convert.sh: Permission denied root@ts3bahu:/home/hunterpl# chmod +x convert.sh root@ts3bahu:/home/hunterpl# ./convert.sh MyDBName.mariadb.sql root@ts3bahu:/home/hunterpl# mysql -u root -p test < MyDBName.mariadb.sqle Enter password: ERROR 1146 (42S02) at line 660: Table 'test.channel_properties' doesn't exist root@ts3bahu:/home/hunterpl# ^C root@ts3bahu:/home/hunterpl# mysql -u root -p test < MyDBName.mariadb.sql Enter password: ERROR 1136 (21S01) at line 139022: Column count doesn't match value count at row 1

[NotoriousPyro]

Sorry, try the script above again. Re-copy it.

[hunterpl]

Same error as previous: ERROR 1136 (21S01) at line 139022: Column count doesn't match value count at row 1

Can i contact with you via E-mail? i can send my database for example to convert okay? and It will help you so much better with testing.

Best Regards Mateusz

[NotoriousPyro]

Added script above for TS3-specific changes and advised on the need to manually make some edits to larger and sometimes more complex DBs.

[cknost]

i have to use this.