Introduction

SQLite-to-MySQL is a program to move SQLite databases to MySQL server. Depending on your privileges on the target MySQL server you can export SQLite data into new database or overwrite the contents of existing MySQL database.

Features

  • All SQLite data types and attributes are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Converts individual tables (see Select Tables article for related information)
  • Exports SQLite database into MySQL script file (see Customize Conversion article for related information)
  • Special approach for VServer users (see VServer User Notes article for related information)
  • Merges SQLite data into an existing MySQL database
  • Stores configuration into profile (see Profile article for related information)
  • Command line support (see Command Line article for related information)
  • Quick Launch
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • Supported OS: Windows XP(SP2 or SP3)/2003/Vista/Server 2008/7/8/10
  • Necessary privileges to write into the destination database

Limitations

  • Does not convert indexes


Command Line

To perform batch conversion or call conversion procedure from an automation script you can use console version of SQLite-to-MySQL converter called L2SAGENT.EXE. Find this file in the installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--dest=... | MySQL database or dump file name
--dump | convert SQLite database into MySQL script file
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process an existing MySQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge)
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--port=... | MySQL port
--profile=... | path to a profile to load conversion settings from
--silent | use this option to disable program output
--src=... | path to SQLite database
--tab_def | convert table definitions only
--tab_file=... | file containing table names to convert (one table name per line)

In the following example the program converts SQLite database located in "c:\db 1.sqlite" folder into MySQL database "db 1" on the remote MySQL server "mysqlhost":

L2SAGENT.EXE --src="c:\db 1.sqlite " --dest="db 1" --mysqlh=mysqlhost --mysqlu=administrator
--mysqlp=the_passsword

Notes:

  1. You can omit 'mysqlh' parameter to connect local MySQL server
  2. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  3. If you omit 'tab_file' parameter, all database tables will be converted
  4. If you omit 'mode' parameter, default mode 'overwrite the entire database' will be used
  5. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. SQLite-to-MySQL converter is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run SQLite-to-MySQL converter from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

You should have the sufficient privileges to create new database on MySQL server. Otherwise you will be able only to import SQLite database contents into an existing MySQL database. See VServer User Notes article for related information.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by SQLite-to-MySQL converter. See Troubleshooting article to learn how to resolve this problem.


Connecting to MySQL over SSH

Database administrators and security people know that it is good practice to open only local access to MySQL server for security reasons. However, if you want to connect any of Intelligent Converters applications to MySQL server hosted on a Linux/Unix system, it is necessary to open remote access from your local IP address on the destination server. Obviously it makes MySQL server less secure. To improve the situation you can use SSH protocol for secure data transfer between local machine running Intelligent Converters application and MySQL server.

The most easy way of connecting MySQL through SSH is to use port-forwarding through an SSH tunnel. This technique is based on creating an alias that makes any client application think it's connecting to "localhost" machine, while it's really connecting to the remote server through the tunnel.

If you are using the command line ssh, the command would look like this:

ssh -L <localport>localhost<remoteport> <username>@<servername>

If you use the standard MySQL port, both "localport" and "remoteport" must be set to 3306. You can do the same via tools having graphical user interface like Putty or SecureCRT.

Once you've got the ssh tunnel going, you can open any of Intelligent Converters application and enter in the details for remote MySQL server, using 'localhost' as the server host. See below example of MySQL connection page for SQLite-to-MySQL converter.


Conversion Settings

On this wizard screen you can customize the conversion process using the following settings:

  • MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations.
  • MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.
  • Convert table definitions only - select this checkbox to convert only table definitions of SQLite database. All the data will not be migrated.
  • Skip converting indexes - select this checkbox if you don't want to convert indexes


Customize Conversion

SQLite-to-MySQL converter provides two options to convert SQLite database into MySQL data source:

  • Move to MySQL server directly - use this approach to export the contents of the source database into a database on the target MySQL server. You need MySQL server launched and also necessary privileges to write into a database on the target server. See Connect to MySQL Server article for related information.
  • Store into dump file - use this approach to perform deferred conversion and get more control over the process. Following this way, the program stores the contents of the source database into a local SQL script file instead of moving it to MySQL server directly. The resulting file contains MySQL statements to create all tables and to fill them with the data. See Importing dump files article for related information.

You can choose one of these approaches on the first page of SQLite-to-MySQL wizard using the corresponding radio buttons.


Edit Table

On this wizard screen users can edit table name and customize types mapping.

SQLite-to-MySQL converts SQLite types into the most appropriate MySQL types. However, sometime it is necessary to change type for one or multiple columns. To customize type mapping select the appropriate item in the listview on the left and specify new name, type and default value for the field using "Field info" group of controls. Please note that listview on the left contains column attributes for the original SQLite database, new custom types and attributes will be displayed in "Field info" controls when the corresponding item is highlighted in the listview.


Frequently Asked Questions

On this page you can find the most frequently asked questions regarding SQLite-to-MySQL. If your question is not answered here, please contact us at service@convert-in.com

  • What kind of SQLite data is converted to MySQL?
  • What are the system requirements for SQLite-to-MySQL?
  • Does SQLite-to-MySQL support automation?
  • How to bypass error "The program does not support authentication protocol requested by server..."
  • How to specify tables to convert in the command line version of SQLite-to-MySQL (l2sagent.exe)?
  • It is possible to connect to MySQL over SSH with SQLite-to-MySQL?
  • How to uninstall SQLite-to-MySQL converter?

What kind of SQLite data is converted to MySQL?
SQLite-to-MySQL converts table structures with all necessary attributes and the data. The program also converts indexes and foreign key constraints. Demo version converts only 50 records per table.
Go to Top

What are the system requirements for SQLite-to-MySQL?
SQLite-to-MySQL reads SQLite data files and connects to MySQL server directly, that's why it does not require any 3rd party software installation. The product does not use ODBC or any other middleware libraries.
Go to Top

Does SQLite-to-MySQL support automation?
SQLite-to-MySQL converter does not support automation directly. However, there is console version of the program l2sagent.exe in the installation directory. It supports command line arguments and can be called from any script. So, it is possible to write a simple batch script and execute it recurrently using Windows Task Scheduler. Please refer to 'Command line' article of the product documentation to learn more about l2sagent.exe.
Go to Top

How to bypass error "The program does not support authentication protocol requested by server..."
MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by SQLite-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with SQLite-to-MySQL. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;
Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with SQLite-to-MySQL.
Go to Top

How to specify tables to convert in the command line version of SQLite-to-MySQL (l2sagent.exe)?
You can specify certain tables to convert via parameter '--tab_file=...' that points to the file containing list of tables as one name per line. For exmaple:

table_name_1
table_name_2
...
table_name_N
Go to Top

It is possible to connect to MySQL over SSH with SQLite-to-MySQL?
Yes, SQLite-to-MySQL supports SSH connection to MySQL server. Please refer to this article for the further information.
Go to Top

How to uninstall SQLite-to-MySQL converter?
If you need to remove SQLite-to-MySQL product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel.
Go to Top


Importing dump files

This article explains how to move dump file contents to MySQL server using the most popular tools.

1. MySQL console client (mysql)
Syntax of the command line depends on the structure of the dump file. If the dump file contains 'CREATE DATABASE' statement you should call MySQL console client as follows:

mysql --host=... --user=... --password=... -vvf < dump_file_name

Otherwise:

mysql --host=... --user=... --password=... -vvf MySQL_database_name < dump_file_name

where 'MySQL_database_name' is a name of an existing database.

2. phpMyAdmin
Note, that phpMyAdmin allows to import dump file into an existing MySQL database only, so the dump file should not contain 'CREATE DATABASE' statement.

Step 1. Click on the appropriate database name in the left frame.

Step 2. Click 'SQL' tab on the top of the screen.

Step 3. Enter full path to the dump file in 'Location of the textfile' field or click 'Browse' button to locate it through the directory tree.

Step 4. Click 'Go' button.


Merging and Overwriting

If you try to convert SQLite database into an existing MySQL database, SQLite-to-MySQL converter allows you to choose one of the following options: overwrite, merge or specify another destination name.

  • Select 'Overwrite' option to replace contents of the destination MySQL table with SQLite data. Note that all data contained in MySQL table will be lost.
  • Select 'Merge' option to merge SQLite data into the destination MySQL database. SQLite-to-MySQL converter implements quite simple merging algorithm. It appends data to the corresponding MySQL table. Note that structures of SQLite table and destination MySQL table must be identical.

If you decide to enter another destination name just click 'Cancel' button.


MySQL dump file settings

On this wizard screen you can specify how the resulting dump file should be constructed. There are following settings available:

  • MySQL version - this parameter determines the syntax of MySQL statements being used in output dump file. 'Old' versions of MySQL (before 3.23.6) put more restrictions on database objects names. Note that 'new' syntax is not accepted by 'old' versions of MySQL. Use 'old' syntax whenever you are not sure about the version of your MySQL server.
  • Line breaks - this parameter specifies how to separate text lines in the dump file. If the destination MySQL server is running on MS Windows platform, you should select 'MS Windows style' option. Otherwise select 'Unix style'.
  • Add lock for write around 'INSERT' statements - it makes importing the contents of the dump file into a database much faster. To use this feature in MySQL 4.0 and higher you need global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier you need to have SELECT, INSERT, DELETE and UPDATE privileges for the tables.
  • Insert 'CREATE DATABASE' statement - select this option to put 'CREATE DATABASE' statement in the resulting dump file. This setting doesn't take effect without the option below activated.
  • Insert 'CREATE TABLE' statements - select this option to put 'CREATE TABLE' statements in the resulting dump file. You should always select it in order to obtain a valid dump file. The only exception is appending the data to existing tables.
  • Insert comments - select this option to add brief comments. It makes the resulting dump file more readable.

See Importing dump files article to learn how to move dump file contents to MySQL server.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL connection characteristics (host, user name, password) and other conversion settings. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Quick Launch

Once you stored conversion settings into profile you can run the program in Quick Launch mode. Just launch Windows Explorer and double-click on the profile description (.l2s) file with left mouse button. This will cause SQLite-to-MySQL wizard to launch conversion loading the necessary settings from the specified profile. Then you will be taken right to the screen with progress of conversion without entering all conversion settings.

See Profile article for more information about profiles.


Select Databases

On this wizard screen you should enter path to SQLite database, MySQL database name or path to SQL script file (depending on which conversion scenario you choose).

Also, you can specify a log file to enable the program write execution traces. It is used to analyze possible error messages and get more control over the conversion process. If no log file is specified, the logging is disabled.


Select Tables

On this wizard page you can manage the list of SQLite tables to convert.

In the left part of the wizard page you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new SQLite table for converting into MySQL format you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available SQLite tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.

To edit selected table double click the corresponding item in "Selected tables" list box. Then the dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information.


Troubleshooting

While working with SQLite-to-MySQL converter you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the result you want. This summary is organized as a list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: Error message "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by SQLite-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with SQLite-to-MySQL converter. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with SQLite-to-MySQL converter.

P: Error message "Duplicate field values being used for an unique index were found..."
R: This error means that an attempt has been made to insert a record with a duplicate unique key. When migrating SQLite data into a new MySQL database, you can either edit source database in order to resolve the conflict manually or restart the program with "Skip converting indexes" checkbox selected. When appending SQLite data to an existing MySQL database, you should remove the conflicting unique index in the destination MySQL database.

P: Error message "Can't connect to MySQL server on <host name or IP>".
R: Make sure MySQL server is launched on the target machine. Also please check the port number field to verify that you have specified the correct number.

P: Error message "Access denied for user: <user-name>"
R: You will not be able to connect to the target MySQL server until you have the appropriate permissions. Ask your MySQL server administrator to give you sufficient permissions to create new databases on the target MySQL server. See VServer User Notes article for related information.

P: I cannot uninstall SQLite-to-MySQL.
R: If you need to remove SQLite-to-MySQL product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel.

P: Some table names and/or field names in the resulting MySQL database differ from the original.
R: SQLite and MySQL accept different sets of characters for table names and field names. When SQLite-to-MySQL converts a name containing prohibited characters it replaces them with '_' in the destination database.

P: Error message "Access denied for user: <user name> to database <database>"
R: If the destination database already exists, you should have necessary privileges to add/remove tables. If the destination database doesn't exist, you should have sufficient privileges to create new databases on the target MySQL server. See VServer User Notes article for related information.

If you came across some other kind of problem while using SQLite-to-MySQL converter, please contact our support department at service@convert-in.com


VServer User Notes

If you are using Virtual Server hosting you probably have no sufficient privileges to create new databases on the target MySQL server. For this case we recommend the following approach:

  1. ask your Virtual Server administrator to create blank database for you
  2. run SQLite-to-MySQL converter and select this database as the conversion target
  3. click "Yes" when you will be asked if you'd like to overwrite existing MySQL database

When you complete these steps, the entire contents of the source SQLite database will be imported into the specified MySQL database.


Contents

Command Line

Connecting to MySQL over SSH

Connect to MySQL Server

Conversion Settings

Customize Conversion

Edit Table

Frequently Asked Questions

Importing dump files

Introduction

Merging and Overwriting

MySQL dump file settings

Profile

Quick Launch

Select Databases

Select Tables

Troubleshooting

VServer User Notes