mysql-to-sqlite3: SQLite failed creating table device_type: near "'1'": syntax error
Describe the bug This bug happens when I run the transfer method on the converter object. It happens at line 409 in transporter.py, and the issue appears to be in the use of ‘b’0’‘/‘b’1’’ as the default values for certain columns (those using MySQL BIT type). So a column spec entry like this returned from _build_create_table_sql causes the error:
"do_correction" BLOB NOT NULL DEFAULT 'b'1'' ,
Modifying it so that it instead returns the statement below also does not work:
"do_correction" BLOB NOT NULL DEFAULT b'1' ,
However, if I change the return for this default type to give plain ‘0’ or ‘1’ (or 0/1 as int works but is shady), then the transfer works correctly:
"do_correction" BLOB NOT NULL DEFAULT '1' ,
My understanding of SQLite is that it doesn’t use any kit of strict bit or boolean types, but would store as an integer anyway. Having the byte string as specified I would think would eventually still cast it as an int (if it worked), so might be worth an additional filter to strip out the byte designation in _translate_default_from_mysql_to_sqlite. Though it does surprise me that the b’0’ style doesn’t work, so probably something else at play.
Expected behaviour Completed transfer of MySQL table to SQLite db file.
Actual result The entitled error where it fails to do so due to a syntax error.
System Information Windows 10 Python 3.8.10 mysql-to-sqlite3==1.4.14
About this issue
- Original URL
- State: closed
- Created a year ago
- Comments: 40 (19 by maintainers)
Commits related to this issue
- :bug: #35 Fix BLOB default value — committed to techouse/mysql-to-sqlite3 by techouse a year ago
- :bug: #35 Fix BLOB default value — committed to techouse/mysql-to-sqlite3 by techouse a year ago
- :bug: #35 Fix BLOB default value — committed to techouse/mysql-to-sqlite3 by techouse a year ago
Haha unfortunately not my choice. Just trying to keep the peace between a SQL Server, MySQL server, and various SQLite files… adding in another flavour with an admittedly excellent mascot would break me long before it breaks prod 😉.
Hi @techouse, yeah I have seen that StackOverFlow answer, which does suggest not to use that notation - though they don’t really explain why it is not valid. I think the MySQL documentation could be a lot clearer on this.
And knowing how the default value is stored in MySQL I see that the problem isn’t the connector as you say. Unfortunately I don’t see this getting fixed on our end either in application or via MySQL for a long time in production, so I will have to continue to use the fork that parses it and fixes it (even though I agree it probably shouldn’t exist in that form).
Thanks for spending your time to look at this issue. Hard to say whether it is worth having that notation parsable since it technically works in MySQL, even if it might not be the cleanest way to represent the default value in the DDL.
Hi! Thanks for having a look whenever you get ther chance. I will try to have a look into it a bit further and see if I can find the underlying issue, which I think I traced as far as sqlite3 package itself so I don’t think it’s a bug in this repo. So might have to start looking into that package itself when I get some free time… but a quicker fix for sure would be something to catch it here so I will look to make a PR for that if I don’t get anywhere with sqlite3. 😃