Convert MySQL->SQLite

[AndyGR42]

Hi.

I've had some trouble with MySQL > 4.0.x and TS2. I decided to convert my databases back to SQLite. But this task isn't easy like the other direction.

WARNING: This manual works fine for me! There is NO GARUANTEE that it works for you! Always backup your data! Don’t make me responsibly for anything!


What we need is:

- a linux box
- a SQLite runtime 2.x.x (Download)

For the easiest way extract your sqlite binary to an empty folder and rename it to "sqlite". Change to this folder for all furthers steps.


Step one: CREATE TABLES

The CREATE TABLE syntax of SQLite is a little bit different from MySQL. From the MySQL dump we get something like this (export structure only, without quoting chars):

CREATE TABLE ts2_clients (
  i_client_id int(11) NOT NULL auto_increment,
  i_client_server_id int(11) default NULL,
  b_client_privilege_serveradmin int(11) default NULL,
  s_client_name varchar(40) default NULL,
  s_client_password varchar(80) default NULL,
  dt_client_created varchar(20) default NULL,
  dt_client_lastonline varchar(20) default NULL,
  PRIMARY KEY  (i_client_id)
) TYPE=MyISAM AUTO_INCREMENT=853 ;

We have to change the syntax for the primary key and the autoincrement statement for each table like that:

CREATE TABLE ts2_clients (
  i_client_id INTEGER PRIMARY KEY,
  i_client_server_id int(11) default NULL,
  b_client_privilege_serveradmin int(11) default NULL,
  s_client_name varchar(40) default NULL,
  s_client_password varchar(80) default NULL,
  dt_client_created varchar(20) default NULL,
  dt_client_lastonline varchar(20) default NULL);

(You can download the complete file here: create.sql.)

To create the database run the following command:

./sqlite < create.sql server.dbs

This command should not return anything.


Step two: EXPORT and clean MySQL tables

If you think your MySQL DB is clean you can use the following command to convert it directly (#####=db user, *****=db password, tss is the db name):

 mysqldump --lock-tables=FALSE --complete-insert=TRUE  -t -u##### -p***** tss | ./sqlite server.dbs > log

If your thought was true, the log file will have zero size. If not, your MySQL DB is not clean. :-)

Q: What means "not clean"?

A: There are some bad chars in the dump. At the moment I recognized the char ' is bad. The server welcome message "Andy's TS Server" will cause an error, for example.

To avoid this errors dump your database to a flat file first:

 mysqldump --lock-tables=FALSE --complete-insert=TRUE  -t -u##### -p***** tss > dump

Now look for this string in your dump: \' . The lines where you find that string should be match to the lines from your error log. Remove all of that strings.

After this, delete the server.dbs file, repeat step one and use the following command to import your dump to SQLite:

./sqlite < dump server.dbs

This command should not return anything. If there are some errors continue with cleaning the dump file.

Notes:

- delete the server.dbs and proceed step one every time you try to import the dump!
- if you change strings at the column "s_client_name" the affected user will not be able to connect with his old user name!

Step three:

Remove the whole section [DBEXPRESS] from your server.ini file and try to start your TS Server.

Good Luck!

[hacktrix2006]

could you repost that creat.sql file please as the link is dead