Syntax error in output (missing backtick)

[PetterS]

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)

[dumblob]

Thanks for reporting! If you'll find some time, could you please try it with patch https://github.com/dumblob/mysql2sqlite/pull/71 ? The pull request changes some INT handling and I'd be interested whether the patch should be extended or not. Either way I think we could incorporate the input and output SQL code you provided as an additional unit test.

@vdechef thoughts?

(I hope I'll find some more time for mysql2sqlite few weeks later, but now I can only quickly comment with my ideas)

[vdechef]

@PetterS I think I saw something similar in previous issues : there is a bug with the way enums are handled. I should have fixed it in #71, so you should give it a try (beware to add --hex-blob option to mysqldump).

[PetterS]

I was able to work around my issue by changing one instance of int(11) to integer in the input. After that, I got output that sqlite accepted.

[vdechef]

Nice 👍 I confirm that your problem is due to the "enum" bug : your field named value_enum was parsed incorrectly by mysql2sqlite. So using #71 would solve it.
I added a unit test in PR #71 with your example.

[PetterS]

Cheers! 👍