DROP TABLE IF EXISTS `tablename`; statements removed

[disperse]

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

[dumblob]

That's weird as the script is not removing any drop statements. Please provide some test sql input as otherwise I can't reproduce it.

[disperse]

Sure thing, here's a repro case, this dump from MySQL was created with mysqldump -uroot -p --compact --add-drop-table --skip-extended-insert test_database pet > pet.sql

DROP TABLE IF EXISTS `pet`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Running ./mysql2sqlite pet.sql > pet.sqlite results in:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL
,  `owner` varchar(20) DEFAULT NULL
,  `species` varchar(20) DEFAULT NULL
,  `sex` char(1) DEFAULT NULL
,  `birth` date DEFAULT NULL
,  `death` date DEFAULT NULL
);
END TRANSACTION;

This is run on an ubuntu virtual server with the following mysql and mysqldump versions:

vagrant@vagrant:~$ mysql -V
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper
vagrant@vagrant:~$ mysqldump -V
mysqldump  Ver 10.13 Distrib 5.7.21, for Linux (x86_64)

[disperse]

Could anyone with some awk / mawk expertise help debug this issue? It would make a big difference for our project if we could preserve drop table statements when converting from mysql to sqlite. Thanks!

[dumblob]

I have this issue in my TODO list as I fully understand the importance, but I'm currently completely out of time :cry:. The argument --add-drop-table is currently unfortunately not supported.

Right now I would suggest just manually writing down all the table names and then adding drop statements using concatenation to the top of the output of this awk script.

[newmips]

Adding lines

# DROP TABLE
/^.*(DROP.*TABLE|drop.*table)/ {
  print
  next
}

Just after function "bit_to_int" worked for me

[colmmcmullan]

It would be great to get support for DROP TABLE included.

@newmips - perhaps you could submit it via a pull request?