I issued the following command to create my export file:

mysqldump -u root -p --skip-extended-insert --compact my_db > huge.sql

When I HEAD and TAIL the huge.sql file, I see SQL commands as expected. It's a large file, something like 7.5 GB.

I tried to convert the file with mysql2sqlite:

./mysql2sqlite.sh huge.sql > yh.sql

Looks good so far; no errors generated. And when I execute HEAD and TAIL on the yh.sql file, I see regular SQL commands as expected.

But when I try importing yl.sql into yh.db using SQLiteBrowser, the resulting database is empty.

Am I missing something important? Besides the data, I mean. ;)

When I run this command:table lineorder has 2 columns but 17 values were supplied
I have this error:
Error: near line 60: table lineorder has 2 columns but 17 values were supplied Error: near line 61: table lineorder has 2 columns but 17 values were supplied Error: near line 62: table lineorder has 2 columns but 17 values were supplied Error: near line 63: table lineorder has 2 columns but 17 values were supplied Error: near line 64: table lineorder has 2 columns but 17 values were supplied Error: near line 65: table lineorder has 2 columns but 17 values were supplied Error: near line 66: table lineorder has 2 columns but 17 values were supplied Error: near line 67: table lineorder has 2 columns but 17 values were supplied

Not an expert in awk, but I guess a lot of the patterns should be made to do only a word-only comparison, otherwise examples as in the issue title will result in faulty SQL statements.

Otherwise great and very useful script!!

When setting a default value for text field encoding utf8mb3 encoding character is added.

BEFORE (MYSQL):
TEXT default ('mytext')
AFTER (SQLITE):
text DEFAULT (_utf8mb3'mytext')

The query can't run this way with sqlite3.
when i removed the character the query runs as excpected.

Not sure if I did it in the right way but it showed the line: memory
after conversion is done.
However, when open the file with sqlite3, it doesn't show any tables. Am I missing anything?

Running the script I get the mysterious message "memory".

And that's it, just "memory", nothing else at all.

While the amount of rows per table is correct, and the PRIMARY and UNIQUE keys seem ok, the FOREIGN keys are weird, at least if I open them using SQLPro. They look like this:

  FOREIGN KEY (`fragged_player`) REFERENCES (null) () `pixp_user` (`user_id`,
  FOREIGN KEY (`fragger_player`) REFERENCES (null) () `pixp_user` (`user_id`,

When in an old correct version of the file they looked like this:

  FOREIGN KEY (fragger_player) REFERENCES pixp_user (user_id),
  FOREIGN KEY (fragged_player) REFERENCES pixp_user (user_id),

I don't have a working version of the application right now, so I can't test it yet; in case I'll keep you posted.

Feel free to ask me for clarifications or specific debugging.

PS: keep in mind this is a very small db, so there cannot be a real memory error. I mean, if there is, it's because there is some bad loop of some sorts, certainly not because of actual db size.

Versions:

mysqldump --version
mysqldump  Ver 10.17 Distrib 10.4.10-MariaDB, for osx10.14 (x86_64)

sqlite3 --version
3.24.0 2018-06-04 14:10:15 95fbac39baaab1c3a84fdfc82ccb7f42398b2e92f18a2a57bce1d4a713cbaapl

So it looks like you can't export directly from the database anymore and you have to make a database dump before you export to sqlite? I'm still using the older version of the script like so. ./mysql2sqlite.sh -u root -p DB_20161218 | sqlite3 db.20161218.sqlite

We have a table called transaction which is not escaped an causes an error.

sqlite 3.8.7.1
mysqldump Ver 10.13 Distrib 5.5.59, for debian-linux-gnu (x86_64)

Hello!

I have a mysql-dump with a table:

CREATE TABLE "map" (
  "ID" int(10) unsigned NOT NULL AUTO_INCREMENT,
  "f" int(11) NOT NULL,
  "t" int(11) NOT NULL,
  "direct" bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY ("ID"),
  UNIQUE KEY "UQ_dict__t__t" ("f","t")
);

The problem is in the direct field. Current version of mysql2sqlite converts this table to the

CREATE TABLE "map" (
  "ID" int(10)  NOT NULL ,
  "f" int(11) NOT NULL,
  "t" int(11) NOT NULL,
  "direct" bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY ("ID")
);

And such script is not valid SQL for sqlite:

$ cat x.sql | sqlite3 db.sqlite
Error: near line 1: near "'1'": syntax error

I have manually replaced "direct" bit(1) NOT NULL DEFAULT b'1', with "direct" bit(1) NOT NULL DEFAULT 1, and script works fine for me.

Could you fix mysql2sqlite, or add some instructions to create a valid mysqldump?

There was a pull request https://github.com/dumblob/mysql2sqlite/pull/4 trying to add support for double quotes, but in a very dangerous way and also without supporting nested double quotes (i.e. escaping of double quotes).

It would be nice to implement a tiny parsing function in AWK which would first split the string into an array, find all the occurences, delete them and then join the array again together to build a string for returning.

I'm currently out of time, so volunteers are even more welcome!

You have the code comment

# FIXME detect empty input file and issue a warning

one approach is set a flag in the BEGIN
reset it in any section before the END
check that it was toggled in the END section and warn otherwise

example:

BEGIN{ empty_file = 1}
{   empty_file = 0 }
END{
    if(empty_file)
        print "EMPTY FILE"
    else
        print "GOT STUFF"
}

results in:

$ ./empty_file_report.awk empty_file_report.awk 
GOT STUFF
$ ./empty_file_report.awk /dev/null 
EMPTY FILE
$ touch empty
$ ./empty_file_report.awk empty
EMPTY FILE

A patch to consider

diff --git a/mysql2sqlite b/mysql2sqlite
index e739a28..66dfc49 100755
--- a/mysql2sqlite
+++ b/mysql2sqlite
@@ -50,6 +50,7 @@ BEGIN {
   print "PRAGMA synchronous = OFF;"
   print "PRAGMA journal_mode = MEMORY;"
   print "BEGIN TRANSACTION;"
+  empty_file = 1
 }
 
 # historically 3 spaces separate non-argument local variables
@@ -98,7 +99,7 @@ inTrigger != 0 { print; next }
 inView != 0 { next }
 
 # skip comments
-/^\/\*/ { next }
+/^\/\*/ { empty_file = 0; next }
 
 # skip PARTITION statements
 /^ *[(]?(PARTITION|partition) +[^ ]+/ { next }
@@ -149,6 +150,7 @@ inView != 0 { next }
   if( match( $0, /`[^`]+/ ) ){
     tableName = substr( $0, RSTART+1, RLENGTH-1 )
   }
+  empty_file = 0
   aInc = 0
   prev = ""
   firstInTable = 1
@@ -263,6 +265,10 @@ aInc == 1 && /PRIMARY KEY|primary key/ { next }
 
 END {
   if( no_END ){ exit 1}
+  if( empty_file ){
+    printerr( "EMPTY FILE: " ARGV[1] )
+    exit 1
+  }
   # print all KEY creation lines.
   for( table in key ){ printf key[table] }

results in

$ ./mysql2sqlite /dev/null
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
EMPTY FILE: /dev/null

Which may not as nice as detecting within BEGIN before printing your leading constants
but it is simple and robust.

Hi, thanks for this nice script. Running mysql2sqlite on
https://github.com/pau1ie/bbcmicro.co.uk/blob/master/db/db.sql then piping the result through sqlite3 produces syntax errors (but does convert the database more or less ok).

It looks like it's not dealing with the multiline ALTER TABLE commands (found at the end of this SQL file) properly. If I manually format each ALTER TABLE to be on a single line the script works.

If you have a string that has a long hex number in it, the script will trim it to be 16 characters.

Example:
INSERT INTO `test` VALUES(1,2,'0x00000000000000003');

will result in the warning
1: WARN Hex number trimmed (length longer than 16 chars).
and the output SQL will be
INSERT INTO `test` VALUES(1,2,'0x0000000000000000');

Yesterday I tried to use mysql2sqlite.exe on a windows machine to migrate a dump from a rather big mysql-database to sqlite.

I made the mysql dump as suggested with

./mysqldump --skip-extended-insert --compact DB_name > dump_mysql.sql

(from windows powershell as administrator)

Of course I put in the correct database name for "DB_name". I got a file "dump_mysql.sql" that seemed to match the size of the database, but the sql was not readable by a text editor (only when changing to Hex view).

That notwithstanding I proceeded to your suggested conversion

./mysql2sqlite dump_mysql.sql | ./sqlite3 mysqlite3.db

After about a minute I get multiple error messages

Error:

incomplete SQL: Sqlite version : 3.8.7.4

6608_grenzw_ch
insert or ignore into 6608_grenzw_ch (kurzzeit_ml, reference, gdate, kurzzeit_mg, bemerkung, cdate, > grenz_ml, pk, kurzzeit_t, chemid, grenz_mg) VALUES (NULL, NULL, NULL, NULL, '(einatembares Aerosol)', 2009-01-12, NULL, 3, '15', 6540, '0,01')
Traceback (most recent call last):

File "mysql2sqlite.py", line 330, in convert_mysql_to_sqlite

OperationalError: unrecognized token: "6608_grenzw_ch"

insert or ignore into 6608_grenzw_ch (kurzzeit_ml, reference, gdate, kurzzeit_mg, bemerkung, cdate, grenz_ml, pk, kurzzeit_t, chemid, grenz_mg) VALUES (NULL, NULL, NULL, NULL, NULL, 2008-02-08, '1', 6, '15', 8200, '3')
Traceback (most recent call last):

and so on.
Each error was seemingly located on line 330 of File 'mysql2sqlite.py" and each was about not recognizing the token "6608_grenzw_ch" which happens to be the name of one of the tables in the database.

Did I miss something important on my side?
Or is the number of tables to convert from mysql to sqlite too large ?
(I have 128 tables in that database, all in utf-8 coding)
the table named 6608_grenzw_ch is OK on phpMyAdmin (checked status)
And the whole MySQL database DOES work on my localhost as expected.

Hope one of you can point out my mistake or offer advice how to do better ...

or did I find a bug?

awk version 20070501
Mac OS X 10.15.1

I'm seeing CRLF on CREATE TABLE opening lines, and only those lines, with windows-created (CRLF) files converted on OSX. The CRLF is replaced with (correctly per awk's RS) LF on subsequent lines within each CREATE statement, but reappears on each subsequent CREATE.

Likely not a big deal, but for the sake of detail, I thought I'd point it out.

Example:

CREATE TABLE IF NOT EXISTS `myveryowntable` (<CRLF>
  `ID` INT(11) NOT NULL AUTO_INCREMENT,<CRLF>
  `User_ID` INT(11) NOT NULL,<CRLF>
  PRIMARY KEY (`ID`)<CRLF>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;<CRLF>

CREATE TABLE IF NOT EXISTS `myveryowntable` (<CRLF>
  `ID` integer NOT NULL PRIMARY KEY AUTOINCREMENT<LF>
,  `User_ID` integer NOT NULL<LF>
);<LF>

PS Also note that commas are moved to follow the new line. This is independent of the newline character in the original dump. Not a real problem, just an aesthetic note.

Someone please help me figure this out. I have simplified my database dump to just three tables and I am running into problems when I try to create the sqlite database.

I'm noticing that the CREATE statements are not being terminated with ';' and the "CREATE INDEX" statements are showing "" for the table names.

The input seems simple enough that the script should be able to handle very easily, but I can't figure it out.

I'm running the script on Centos 7 with GNU AWK 4.0.2

============================================
Hi...here is my simplified input, created from mysqldump command:

PRAGMA foreign_keys = ON;
CREATE TABLE "applications" (
"ID" varchar(255) NOT NULL,
"NAME" varchar(255) NOT NULL,
"VERSION" varchar(255) NOT NULL,
PRIMARY KEY ("ID")
);

CREATE TABLE "attribute" (
"ID" varchar(255) NOT NULL,
"NAME" varchar(255) NOT NULL,
PRIMARY KEY ("ID")
);

CREATE TABLE "attributeinstance" (
"ID" varchar(255) NOT NULL,
"ATTRIBUTESETINSTANCE_ID" varchar(255) NOT NULL,
"ATTRIBUTE_ID" varchar(255) NOT NULL,
"VALUE" varchar(255) DEFAULT NULL,
PRIMARY KEY ("ID"),
KEY "ATTINST_SET" ("ATTRIBUTESETINSTANCE_ID"),
INDEX "ATTINST_ATT" ("ATTRIBUTE_ID"),
CONSTRAINT "ATTINST_ATT" FOREIGN KEY ("ATTRIBUTE_ID") REFERENCES "attribute" ("ID"),
CONSTRAINT "ATTINST_SET" FOREIGN KEY ("ATTRIBUTESETINSTANCE_ID") REFERENCES "attributesetinstance" ("ID") ON DELETE CASCADE
);

================================
This is the output I get after running mysql2sqlite:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE "applications" (
"ID" varchar(255) NOT NULL
, "NAME" varchar(255) NOT NULL
, "VERSION" varchar(255) NOT NULL
, PRIMARY KEY ("ID")
CREATE TABLE "attribute" (
"ID" varchar(255) NOT NULL
, "NAME" varchar(255) NOT NULL
, PRIMARY KEY ("ID")
CREATE TABLE "attributeinstance" (
"ID" varchar(255) NOT NULL
, "ATTRIBUTESETINSTANCE_ID" varchar(255) NOT NULL
, "ATTRIBUTE_ID" varchar(255) NOT NULL
, "VALUE" varchar(255) DEFAULT NULL
, PRIMARY KEY ("ID")
,
, CONSTRAINT "ATTINST_ATT" FOREIGN KEY ("ATTRIBUTE_ID") REFERENCES "attribute" ("ID")
, CONSTRAINT "ATTINST_SET" FOREIGN KEY ("ATTRIBUTESETINSTANCE_ID") REFERENCES "attributesetinstance" ("ID") ON DELETE CASCADE
);
CREATE INDEX "idx__" ON "" ();
CREATE INDEX "idx__" ON "" ();
CREATE INDEX "idx__" ON "" ("ATTRIBUTESETINSTANCE_ID");
CREATE INDEX "idx__" ON "" ("ATTRIBUTE_ID");
END TRANSACTION;

======================
This is the output from SQLITE3

Error: near line 4: near "CREATE": syntax error
Error: near line 23: near ")": syntax error
Error: near line 24: near ")": syntax error
Error: near line 25: no such table: main.
Error: near line 26: no such table: main.

Here's a bug converting the create clause:

MySQL:

CREATE TABLE `example` (
  `episodenumber` decimal(10,1) unsigned DEFAULT NULL,
);

Which gets translated to

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `example` (
  `episodtext   DEFAULT NULL
);
END TRANSACTION;

I think it's caused by the table name containing 'number' because changing the type from decimal(10,1) to another type didn't cause the error

Firstly, thanks for this great script 👍

We have a database that after running the mysqldump output through this script, we are left with a part of the SQL dump that looks like this...

CREATE TABLE `news` (
  `id` integer  zerofill NOT NULL PRIMARY KEY AUTOINCREMENT
,  `title` text
,  `content` text
,  `created_at` timestamp NULL DEFAULT NULL
,  `updated_at` timestamp NULL DEFAULT NULL
);

When importing this into sqlite it throws the following error...

Error: near line 367: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

If I place gsub( /ZEROFILL|zerofill/, "" ) into around line 192 of the mysql2lite script, this fixes the issue.

Im not too clued up on any other ways to fix this, but it works fine for what we need.

Any thoughts on this?

I have a generated column and get this in table creation:

Error: near line 3686: near "AS": syntax error

Is it possible to, when converting a dump file from mysql to a sqlite db, to convert the DECIMAL[X,Y) columns to TEXT with TEXT affinity?

I seem to be running into a problem where, when loading rows, the column data is being converted to double because the columns are TEXT with NUMERIC affinity.

This is fine when doing searches, not so much when the columns are part of a computation where the precision is a factor, I think.

Sqlite3 "doesn't support" DECIMAL per se. I'm in the process of looking at lots of source to see where conversion to double is occurring, I don't quite yet have a handle on this.

On exporting a database schema, MySQL Workbench (6.3.10) dumps a single CONSTRAINT in CREATE TABLE on multiple lines:

-- MySQL Script generated by MySQL Workbench
-- Wed Nov 29 14:10:23 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `test_parent`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test_parent` ;

CREATE TABLE IF NOT EXISTS `test_parent` (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_child`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test_child` ;

CREATE TABLE IF NOT EXISTS `test_child` (
  `id` INT NOT NULL,
  `test_parent_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_test_child_test_parent`
    FOREIGN KEY (`test_parent_id`)
    REFERENCES `test_parent` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

CREATE INDEX `fk_test_child_test_parent_idx` ON `test_child` (`test_parent_id` ASC);


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

(Output adapted regarding ) ENGINE = InnoDB lines (see Issue #24))

mysql2sqlite outputs:

 ./mysql2sqlite parentchild_dump.sql
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `test_parent` (
  `id` integer NOT NULL
,  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `test_child` (
  `id` integer NOT NULL
,  `test_parent_id` integer NOT NULL
,  PRIMARY KEY (`id`)
,  CONSTRAINT `fk_test_child_test_parent`
,    FOREIGN KEY (`test_parent_id`)
,    REFERENCES `test_parent` (`id`)
,    ON DELETE NO ACTION
,    ON UPDATE NO ACTION
);
CREATE INDEX `fk_test_child_test_parent_idx` ON `test_child` (`test_parent_id` ASC);
END TRANSACTION;
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".

Apparently mysql2sqlite inserts a comma for each new line, causing a syntax error on SQLite3:

$ ./mysql2sqlite ../../../Spielwiese/parentchild_testdump_fix.sql | sqlite3
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".
memory
Error: near line 8: near ",": syntax error
Error: near line 18: no such table: main.test_child

This script seems to work very well for my needs except for the fact that it isn't generating views. My database only has one view, and it's part of my mysqldump SQL:

When I inspect my SQLite file using "DB Browser for SQLite," it says there are 0 views.

Following is my SQL:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DeviceTypes` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Type` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Type` (`Type`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `DeviceTypes` VALUES (1,'RF');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Devices` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Description` varchar(255) NOT NULL,
  `Type` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID` (`ID`),
  KEY `ID_2` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `RFDevice` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DeviceID` int(11) NOT NULL,
  `OnCode` int(11) NOT NULL,
  `OffCode` int(11) NOT NULL,
  `PulseLength` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `RFDevice_fk0` (`DeviceID`),
  CONSTRAINT `RFDevice_fk0` FOREIGN KEY (`DeviceID`) REFERENCES `Devices` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `UserDevices` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `UserID` int(11) NOT NULL,
  `DeviceID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `UserDevices_fk0` (`UserID`),
  KEY `UserDevices_fk1` (`DeviceID`),
  CONSTRAINT `UserDevices_fk0` FOREIGN KEY (`UserID`) REFERENCES `Users` (`ID`),
  CONSTRAINT `UserDevices_fk1` FOREIGN KEY (`DeviceID`) REFERENCES `Devices` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `UserDevicesView` AS SELECT 
 1 AS `UserDevices_UserID`,
 1 AS `DeviceID`,
 1 AS `Users_Name`,
 1 AS `Email`,
 1 AS `Users_UserID`,
 1 AS `Devices_Name`,
 1 AS `Description`,
 1 AS `Devices_Type`,
 1 AS `DeviceTypes_Type`*/;
SET character_set_client = @saved_cs_client;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `UserID` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UserID` (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `Users` VALUES (1,'Abc','[email protected]','amzn1.account.a8b7c7s888aeff7bc7s7901a1');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT '',
  `data` text,
  `ip` varchar(40) DEFAULT NULL,
  `agent` varchar(255) DEFAULT NULL,
  `stamp` int(11) DEFAULT NULL,
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50001 DROP VIEW IF EXISTS `UserDevicesView`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_unicode_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `UserDevicesView` AS select `UserDevices`.`UserID` AS `UserDevices_UserID`,`UserDevices`.`DeviceID` AS `DeviceID`,`Users`.`Name` AS `Users_Name`,`Users`.`Email` AS `Email`,`Users`.`UserID` AS `Users_UserID`,`Devices`.`Name` AS `Devices_Name`,`Devices`.`Description` AS `Description`,`Devices`.`Type` AS `Devices_Type`,`DeviceTypes`.`Type` AS `DeviceTypes_Type` from (((`UserDevices` join `Users` on((`UserDevices`.`UserID` = `Users`.`ID`))) join `Devices` on((`UserDevices`.`DeviceID` = `Devices`.`ID`))) join `DeviceTypes` on((`Devices`.`Type` = `DeviceTypes`.`ID`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

When I run this script, the only output I get is "memory"

$ ./mysql2sqlite schema.sql | sqlite3 mysqlite.db
memory

When dumping a trigger, mysqldump generated the table names with the scchema name which cuases an error when importing the dump.

sqlite 3.8.7.1
mysqldump Ver 10.13 Distrib 5.5.59, for debian-linux-gnu (x86_64)

I open this issue to talk about my practice about blob and Chinese character handling during the migration. I'm not quite familiar with the character handling in mysql and this migration script but I do meet some problem when migration. I think this message might help others who meet the same question like me.

I run the dump with --compact --skip-extended-insert but the text column, which contains Chinese characters and emoji, in the generated dump is garbled and migration fails.
The character set of mysql server is utf8mb4.
Then I figure out it and try with additional --hex-blob --default-character-set=utf8mb4. Then the text column works right. However the blob column then become 0xaaaaaaa..., which is very long and sqlite can't read them. Then I manually replace them with x'aaaaaa...', which is the blob literal in sqlite. Until now, everything works well and I run the migration script successfully.

Update 1

For curiosity, I run the script without --hex-blob and inspect the generated sql. The Chinese characters and emoji are not garbled but the blob seems also be translated as if it were text. So my question is the script can't deal with blob?

Why i get this error and how can i fixe it?

Lines of the form

/*!50100 PARTITION BY RANGE (YEAR(date))
(PARTITION p6 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (2013) ENGINE = InnoDB)

end up as lines with only ); which causes an error

sqlite 3.8.7.1
mysqldump Ver 10.13 Distrib 5.5.59, for debian-linux-gnu (x86_64)

Trying to convert this .sql db:

https://sourceforge.net/projects/mysqlenglishdictionary/files/

And I'm getting these errors:

Error: near line 9: too many terms in compound SELECT
Error: near line 7129: too many terms in compound SELECT
Error: near line 14218: too many terms in compound SELECT
Error: near line 21284: too many terms in compound SELECT
Error: near line 28313: too many terms in compound SELECT
Error: near line 35612: too many terms in compound SELECT
Error: near line 42786: too many terms in compound SELECT
Error: near line 50057: too many terms in compound SELECT
Error: near line 57471: too many terms in compound SELECT
Error: near line 64757: too many terms in compound SELECT
Error: near line 71968: too many terms in compound SELECT
Error: near line 79414: too many terms in compound SELECT
Error: near line 86707: too many terms in compound SELECT
Error: near line 93873: too many terms in compound SELECT
Error: near line 101345: too many terms in compound SELECT
Error: near line 108689: too many terms in compound SELECT

I've got very little experience with this stuff, and notepad++ doesn't seem to like .sql files so I can't go to those line numbers (if they do indeed correspond to line numbers in the file).

Any ideas what's going on? Thanks for keeping this script alive!

#Hi. I was getting the "file is encrypted or is not a database" because I had the extended inserts in my SQL backup file (values for multiple rows were in one INSERT command). For example:
INSERT INTO `table_name` VALUES (1,'value1'),(2,'values2'),(3,'value3');

So I did this patch, tested it and it worked for me. Please consider for inclusion, I don't know how to submit patches using GIT, sorry:

--- mysql2sqlite        2018-02-07 19:01:00.000000000 +0000
+++ mysql2sqlite_for_extended_insert.awk        2018-03-12 11:41:34.880073736 +0000
@@ -121,6 +121,15 @@
       NR ": WARN Hex number trimmed (length longer than 16 chars)." )
     hexIssue = 0
   }
+
+  # for extended-insert (INSERT command with multiple rows in the same line) - split into rows
+  if ( match ( $0, /^((INSERT|insert)  *(INTO|into)  *.* (VALUES|values)  *\()/, matches ) )
+  {
+#    print "start of INSERT command="matches[1];
+    gsub( /\),\(/, ");\n"matches[1] )  #replace (..),(..) with INSERT.. (..) and and with semicolon
+    gsub( /\)\); *$/, ");" )  #last tuple in the row - get rid of double parenthesis
+  }
+
   print
   next
 }

Hi,

Thanks for the great programme.

Noticed I had this when I piped into sqlite. Any ideas?

INSERT INTO sessions VALUES ('vvueta8ojr7e56m79ktvlqj935','redirect_to|s:180:"http://www.example.org/2012/05/23/android-market-device-restrictions/android-market-device-restrictions";',0,'2014-10-11 17:46:36',NULL);
SQL error: SQL logic error or missing database
CREATE TABLE users (
id integer NOT NULL PRIMARY KEY AUTOINCREMENT
, login varchar(64) DEFAULT ''
, password varchar(60) DEFAULT ''
, full_name varchar(250) DEFAULT ''
, email varchar(128) DEFAULT ''
, website varchar(128) DEFAULT ''
, group_id integer DEFAULT '0'
, joined_at datetime DEFAULT NULL
, UNIQUE (login)
);
SQL error: SQL logic error or missing database

This is running on Debian Squeeze and libsqlite3-0 3.7.13-1~bpo60+1.

Best, Sophie

Hi,
the following table schema make table import fail:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  -- <-- TWO BLANK SPACES BEFORE THE DASHES
  `username` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is what happens:

./mysql2sqlite users.sql | sqlite3 db.sqlite
memory
Error: near line 4: near ",": syntax error

The following one (no blank spaces), instead, works as expected:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
-- <-- NO BLANK SPACES BEFORE THE DASHES
  `username` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Am I doing anything wrong?

Thanks in advance.

Francesco

I tried the same commands as mentioned:
mysqldump --no-data -u user -pxxx dbname > dbname_dump.sql
./mysql2sqlite.sh dbname_dump.sql | sqlite3 dbname.db

But get Error: near line XXX: file is encrypted or is not a database

What is the intermediate step that needs to be done which can avoid this error ??

@dumblob: First I want to apologize for being sooo late to react to your request concerning the licence. Of course I agree with my script being issued under the MIT Licence.

Tell me if this issue is enough to release my script under that licence or do I need to react on the original gist?

Thanks for your effort to improve the script.

At the create table, fields with auto_increment are turned into primary key autoincrement. This is fine but if the field has already the primary key it ends up with two, like this:

`id` integer NOT NULL PRIMARY KEY PRIMARY KEY AUTOINCREMENT

The following input (obtained with mysqldump --skip-extended-insert --compact):

/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytablename` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_time` datetime(6) NOT NULL,
  `record_type` int(11) NOT NULL,
  `value_text` longtext,
  `value_numerical` double DEFAULT NULL,
  `value_enum` int(11) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `medical_record_id` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

Gives this output:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `mytablename` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
,  `event_time` datetime(6) NOT NULL
,  `record_type` integer NOT NULL
,  `value_text` longtext
,  `value_numerical` double DEFAULT NULL
,  `value_text  DEFAULT NULL
,  `created_at` datetime(6) NOT NULL
,  `medical_record_id` integer NOT NULL
);
END TRANSACTION;

This is clearly a syntax error (missing backtick after value_text)

My MySQL Dump had some strange "USING HASH" commands.

Samples:

CREATE TABLE XZY (
  ...
  KEY `IX_InstID` (`InstD`) USING HASH,
  UNIQUE KEY `IX_Name` (`Name`) USING HASH
);

I'm not an mysql expert ... so I just delete the "USING HASH" keywords and the script worked for me. I think this script should do the same. USING HASH seems to be a underlying feature which sqlite does not support.

This is valid sqlite and mysql syntax. These drop table statements should be preserved.

The file actually starts with a comment

-- phpMyAdmin SQL Dump
-- version 4.3.9
-- http://www.phpmyadmin.net
--
...

what is the problem here? Can I use mysql2sqlite with phpmyadmin dumps?

as per "README.md" from author of "mysql2sqlite" we used these:

1. Dump MySQL DB
   mysqldump --skip-extended-insert --compact trading_db crb_portfolios_table

2. Convert the dump to SQLite3 DB
   ./mysql2sqlite crb_portfolios_table | sqlite3 trading_db

This short dump file will reproduce the error:

/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE crb_portfolios_table (
portfolio_name varchar(100) NOT NULL DEFAULT '',
portfolio_selections_string blob,
PRIMARY KEY (portfolio_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO crb_portfolios_table VALUES ('all active futures liquid 15-80 cash 5',_binary 'AA,AD,AZ,BO,BP,C-,CB,CC,CD,CL,CT,DF,EC,ED,ES,FF,FX,GC,HG,HH,HO,HS,JY,KC,KS,KW,LC,LH,LJ,MX,MZ,NG,NH,NN,NO,NP,NX,OX,PI,RB,RJ,S-,SB,SI,SM,SP,SS,SZ,TP,TT,VX,W-,X-,ZM');
INSERT INTO crb_portfolios_table VALUES ('all active futures liquid 5-30 all inclusive',_binary '5L,A2,AA,AD,AZ,B-,BF,BG,BM,BO,BP,BS,BT,C-,CA,CB,CC,CD,CG,CK,CL,CT,DF,DI,DR,DX,EC,ED,ES,F-,FA,FB,FC,FF,FV,FX,FY,GC,GH,GM,GX,HG,HH,HN,HO,HS,IA,IB,IE,II,IL,IT,IX,JY,K6,K7,K8,KB,KC,KD,KL,KM,KS,KW,L-,LC,LF,LH,LJ,LL,LO,LQ,LW,ME,MH,MW,MX,MZ,ND,NE,NF,NG,NH,NI,NK,NN,NO,NP,NV,NX,OT,OX,PA,PI,PL,PO,PP,PU,QB,QE,R-,RB,RE,RJ,S-,SB,SF,SI,SM,SP,SS,SV,SZ,T2,TE,TO,TP,TQ,TS,TT,TU,TY,U0,UG,US,V4,V5,V9,VA,VC,VD,VH,VI,VO,VP,VR,VX,W-,WB,WC,WG,WI,WO,WQ,WR,X-,XO,XT,XU,XV,Y1,Y2,YC,YD,YE,YG,YK,YQ,YS,YT,YV,YW,YX,Z0,Z2,ZM,ZN,ZW');
INSERT INTO crb_portfolios_table VALUES ('all active options liquid 15-80 cash 5',_binary 'AD,BO,BP,C-,CB,CC,CD,CL,CT,DF,EC,ED,ES,FF,FX,GC,HG,HO,HS,JY,KC,KW,LC,LH,MX,MZ,NG,NN,NX,RB,RJ,S-,SB,SI,SM,SP,SS,SZ,W-,X-,ZM');
INSERT INTO crb_portfolios_table VALUES ('all futures active and liquid',_binary '2B,2C,2G,2H,2I,2J,2K,2L,2N,2S,2T,2U,2V,2W,5A,5D,5F,5G,5L,5P,5S,5V,5Z,AA,AD,AZ,B-,BF,BM,BO,BP,BS,BT,C-,CA,CB,CC,CD,CG,CK,CL,DF,EC,ED,EI,ES,F-,FA,FF,FV,FX,GC,GH,GM,HG,HH,HN,HO,HS,IL,JY,K6,K7,KB,KC,KD,KS,KW,L-,LC,LF,LH,LJ,MH,MX,MZ,ND,NG,NH,NN,NO,NP,NX,OT,OX,PI,PO,R-,RB,RE,RJ,S-,SB,SI,SM,SP,SS,SZ,TS,TT,TU,TY,U0,UG,US,VA,VC,VD,VH,VI,VP,VR,VX,W-,WG,WI,WJ,WO,WQ,WS,X-,XO,XT,XU,XV,YC,YG,YK,YQ,YS,Z2,ZM');
INSERT INTO crb_portfolios_table VALUES ('all options active and liquid',_binary 'AD,B-,BF,BO,BP,C-,CB,CC,CD,CK,CL,CT,DF,EC,ED,ES,F-,FF,FV,FX,GC,GH,GM,HG,HO,HS,JY,KC,KW,L-,LC,LF,LH,MH,MX,MZ,NG,NN,NP,NX,R-,RB,RJ,S-,SB,SI,SM,SP,SS,SZ,TU,TY,UG,US,VD,VI,W-,WC,WI,X-,ZM');
INSERT INTO crb_portfolios_table VALUES ('Currencies active and liquid futures',_binary '5A,5D,5F,5G,5P,5V,AA,AD,BP,CD,EC,JY,KD,NP,U0,VD');

On exporting a database schema, MySQL Workbench (6.3.10) puts the create definitions' right parenthesis on the same line as the last create definition:

-- MySQL Script generated by MySQL Workbench
-- Wed Nov 29 11:49:10 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `test_table`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test_table` ;

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` INT NOT NULL,
  `float_col` FLOAT NULL,
  `text_col` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Apparently mysql2sqlite doesn't expect this:

$ ./mysql2sqlite testdump.sql
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `test_table` (
  `id` integer NOT NULL
,  `float_col` FLOAT NULL
,  `text_col` TEXT NULL
END TRANSACTION;
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".

Leading SQLite3 to output an error message:

$ ./mysql2sqlite testdump.sql | sqlite3 testdump.sqlite3
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".
memory
Error: near line 4: near "END": syntax error

Putting ENGINE = InnoDB on the same line as the right parenthesis and inserting a line break between the two right parentheses helped.

CREATE TABLE IF NOT EXISTS `test_table` (
  `table_id` INT NOT NULL,
  `float_col` FLOAT NULL,
  `text_col` TEXT NULL,
  PRIMARY KEY (`table_id`)
) ENGINE = InnoDB;

mysql2sqlite outputs a correct SQLite3 syntax:

$ ./mysql2sqlite testdump.sql
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `test_table` (
  `table_id` integer NOT NULL
,  `float_col` FLOAT NULL
,  `text_col` TEXT NULL
,  PRIMARY KEY (`table_id`)
);
END TRANSACTION;
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".

Hi there. Found another CREATE clause which doesn't translate correctly:

CREATE TABLE `example` (
  `priority` bit(1) NOT NULL DEFAULT b'0',
  KEY `priority` (`priority`)
)

This generates this SQL:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `example` (
  `priority` bit(1) NOT NULL DEFAULT b'0'
CREATE INDEX "idx_example_priority" ON "example" (`priority`);
END TRANSACTION;

Which gives the error:

Error: near line 4: near "'0'": syntax error

BTW - thanks very much for creating this.

CREATE TABLE `measurement_logs` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) DEFAULT NULL,
  `CreatedOn` datetime NOT NULL,
  `MeasuringPointId` int(11) NOT NULL,
  `StartDate` datetime NOT NULL,
  `EndDate` datetime NOT NULL,
  `Complete` int(11) NOT NULL,
  `Incomplete` int(11) NOT NULL,
  `Zeroed` int(11) NOT NULL,
  `Missing` int(11) NOT NULL,
  `ImportType` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `IX_MeasuringPointId` (`MeasuringPointId`) USING HASH,
  CONSTRAINT `FK_measurement_logs_measuringpoints_MeasuringPointId` FOREIGN KEY (`MeasuringPointId`) REFERENCES `measuringpoints` (`MeasuringPointId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=190 DEFAULT CHARSET=latin1;

Is converted to:

CREATE TABLE `measurement_logs` (
  `Id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
,  `UserId` integer DEFAULT NULL
,  `CreatedOn` datetime NOT NULL
,  `MeasuringPointId` integer NOT NULL
,  `StartDate` datetime NOT NULL
,  `EndDate` datetime NOT NULL
,  `Complete` integer NOT NULL
,  `Incomplete` integer NOT NULL
,  `Zeroed` integer NOT NULL
,  `Missing` integer NOT NULL
,  `ImportType` integer NOT NULL
,  CONSTRAINT `FK_measurement_logs_measuringpoints_MeasuringPointId` FOREIGN KEY (`MeasuringPointId`) REFERENCES `measuringpoints` **integer** ON DELETE CASCADE ON UPDATE CASCADE
);

Wrong referecence.

I downloaded mysql2sqlite straight from Github. I'm not sure where to go from there.

I'm using the Mac Terminal. I went to the Downloads folder where my .sql files are. I want to test and see if I can work this. So I typed in mysql2sqlite myTable.sql | sqlite3 mySqlite.db and I get the mysql2sqlite: command not found.

I know I'm not doing this correctly. Let me know what I need to do to be able to convert one or two MySQL tables into an Sqlite3 database.

Hi. I was getting this error:

Error: near line 4: near "USING": syntax error

When I had the following syntax in my SQL backup file:

CREATE TABLE `table_name` (
  UNIQUE KEY `hostname_domain` (`hostname`,`domain`) USING BTREE,
);

This patch gets rid of "USING BTREE" and other "USING". Tested and it works for me. Please consider for inclusion. I don't know how to submit them using GIT, sorry:

--- mysql2sqlite        2018-02-07 19:01:00.000000000 +0000
+++ mysql2sqlite_ignoring_using_in_key.awk      2018-03-12 11:50:38.090073802 +0000
@@ -184,6 +184,10 @@
   if( match($0, ere_bit_field) ){
     sub( ere_bit_field, bit_to_int( substr( $0, RSTART +2, RLENGTH -2 -1 ) ) )
   }
+
+  #remove USING BTREE and other suffixes for USING, for example: "UNIQUE KEY `hostname_domain` (`hostname`,`domain`) USING BTREE,"
+  gsub( / USING [^, ]+/, "" )
+
   # field comments are not supported
   gsub( / (COMMENT|comment).+$/, "" )
   # Get commas off end of line

mysql2sqlite fails with the following error:

./mysql2sqlite terra.sql | sqlite3 terra.db
memory
Error: near line 30013: near ",": syntax error
Error: near line 30015: near ",": syntax error
Error: near line 30017: near ",": syntax error
Error: near line 30018: near ",": syntax error
Error: near line 30024: near ",": syntax error
Error: near line 30027: near ",": syntax error
Error: near line 30028: near ",": syntax error
Error: near line 30030: near ",": syntax error
Error: near line 30031: near ",": syntax error
Error: near line 30033: near ",": syntax error
Error: near line 30035: near ",": syntax error
Error: near line 30037: near ",": syntax error
Error: near line 30038: near ",": syntax error

The issue happened on Ubuntu 19.10 with Sqlite3 v.2.8.17.
An online converter (https://www.rebasedata.com/convert-mysql-to-sqlite-online) did the job well.
Since the SQL-dump is publicly available, I thought this might be a useful test case for the developers.
The database is available through the following link
https://www.inf-schule.de/content/1-information/4-datenbanksysteme/db/terra.zip
appearing in the following the online learning platform
https://www.inf-schule.de/information/datenbanksysteme/terra/selbstreferenz/uebungen

I am using this tool to help create an offline version for my web application. I use this script to generate a sqlite db, send it to my mobile device, restore it and connect to it. This is working great. I want to however add a layer of encryption so if this file ever is outside of my mobile app no one can access it. Is there a way with this tool or with sqlite3 to encrypt my database? Note that the MySQL database it is copied from does NOT have encryption.

I'm working on a project which uses your MySQL to SQLite conversion script as our application currently uses sqlite but we plan on switching to MySQL in the future, so we like having our database cross compatible to handle that future switch. While working on a new feature, I was considering using a "INSERT... ON DUPLICATE KEY UPDATE" query in MySQL to handle a case where we want UPSERT functionality, and the script ran into an error here. I imagine the reason is that the SQLite UPSERT functionality is new since SQLite version 3.24.0 (2018-06-04) and this was never implemented since then.

On the surface, it doesn't seem like too much work to convert this over because the syntax seems pretty similar, but I haven't really looked into the script to figure out how much work adding this functionality would be, or if you were currently thinking about adding it. Here is the link for sqlite describing the new UPSERT functionality:

https://www.sqlite.org/lang_UPSERT.html

For example if i have an INSERT statement in the mysql dump where one of the string values is

'class=\"${props.class.concat(\\\'btn\\\', smth)'

It will be transformed into

'class="${props.class.concat(\''btn\', smth)'

Which misses one ' at the last quote in the string.

In string literals the sequence \\\', is turned into \', instead of the expected \'', . For example, the following MySQL:

INSERT INTO `texts` VALUES (18,'\\\',');

is converted into broken

INSERT INTO `texts` VALUES (18,'\',');

After following the instructions I encountered the following errors....... but the SQLite database is fine.....