sqlite3-to-mysql
sqlite3-to-mysql copied to clipboard
techouse
Columns having default values not recognized by mariadb
Describe the bug I have a sqlite file with this table:
CREATE TABLE IF NOT EXISTS "credentials_entity" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(128) NOT NULL, "data" text NOT NULL, "type" varchar(32) NOT NULL, "nodesAccess" text NOT NULL, "createdAt" datetime(3) NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')), "updatedAt" datetime(3) NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')));
When using sqlite3-to-mysql, I get an error
Expected behaviour I don't expect error. However, I don't have a clear idea how to fix it properly.
Actual result I get the following error:
2022-03-29 15:41:12 ERROR MySQL failed creating table credentials_entity: 1901 (HY000): Function or expression '`STRFTIME`()' cannot be used in the DEFAULT clause of `createdAt`
1901 (HY000): Function or expression '`STRFTIME`()' cannot be used in the DEFAULT clause of `createdAt
System Information
$ sqlite3mysql --version
| software | version |
|------------------------|-------------------------------------------------------------------------------------------|
| sqlite3-to-mysql | 1.4.14 |
| | |
| Operating System | Linux 4.19.0-18-amd64 |
| Python | CPython 3.7.3 |
| MySQL | mysql Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 |
| SQLite | 3.27.2 |
| | |
| click | 8.1.0 |
| mysql-connector-python | 8.0.28 |
| pytimeparse | 1.1.8 |
| simplejson | 3.17.6 |
| six | 1.12.0 |
| tabulate | 0.8.9 |
| tqdm | 4.63.1 |
Additional context Add any other context about the problem here.
In case of errors please run the same command with --debug. This option is only available on v1.4.12 or greater.
So basically your default is a function call.
This default here is SQLite specific and won't work in MySQL
"createdAt" datetime(3) NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
The MySQL/MariaDB equivalent would be
`createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
Obviously, this won't work out of the box and would have to be properly translated and/or adapted.
Translating custom functions like that is way out of scope for this package, however, feel free to submit a PR (with tests) for this if you really need the feature.
Hi all,
I have the same issue with a domoticz database which I am trying to get to MySQL. All DATETIME fields in the domoticz sqlite database have a default value of datetime('now', 'localtime').
I will try to fork the project and see if I can create a workaround for this.
Cheers, Friso
How would you like to receive those tests?
Possible solution to this issue: thinking of creating a list of ways to set the default date in sqlite to the current timestamp. In the example of @fflorent , this is done using strftime. In my own example, it's done with datetime. As soon as the script detects a datetime and a default to the current timestamp, it will use the 'DEFAULT CURRENT_TIMESTAMP' code.
How would you like to receive those tests?
Written in the same manner as those in the tests directory.
You'll probably have to mock a lot of stuff and make fake tables using SQLAlchemy just as I've done in tests/models.py for example.
Possible solution to this issue: thinking of creating a list of ways to set the default date in sqlite to the current timestamp. In the example of @fflorent , this is done using strftime. In my own example, it's done with datetime. As soon as the script detects a datetime and a default to the current timestamp, it will use the 'DEFAULT CURRENT_TIMESTAMP' code.
Not sure I'd even go down this route, to be honest.
These things aren't just incompatible but a function call can be anything, so you'd have to make a crazy long list and keep it up to date. Even doing the data type translation was annoying.
If you ask me, I'd just skip the transfer of the default value on these specific columns and once the transfer is complete add it manually using some proper DB management tool like DBeaver.
@fwieringen This here might help https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html