Forum

Results 1 to 2 of 2
  1. #1
    Join Date
    February 2004
    Location
    Gockeltown
    Posts
    81

    Convert MySQL->SQLite

    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):

    Code:
    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:

    Code:
    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:

    Code:
    ./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):

    Code:
     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:

    Code:
     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:

    Code:
    ./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!
    Last edited by AndyGR42; August 20th, 2006 at 03:49 PM.

  2. #2
    Join Date
    October 2009
    Location
    N/A
    Posts
    2
    could you repost that creat.sql file please as the link is dead

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [Resolved] How convert from MySql to SQLITE Database?
    By hunterpl in forum Server Support
    Replies: 7
    Last Post: September 1st, 2016, 12:09 PM
  2. howto convert my sqlite-DB into a MySQL-DB?
    By Harry.Fox in forum Server Support
    Replies: 6
    Last Post: July 30th, 2012, 03:12 PM
  3. Convert TS3 from sqlite to mysql database
    By sKaya in forum Linux / FreeBSD
    Replies: 6
    Last Post: December 2nd, 2011, 08:06 AM
  4. How to convert sqlitedb to mysql
    By mejker in forum Linux / FreeBSD
    Replies: 0
    Last Post: September 12th, 2011, 11:46 PM
  5. howto convert database SQLITE to MySQL?
    By meliorus in forum Linux / FreeBSD
    Replies: 1
    Last Post: April 5th, 2010, 02:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •