Columns having default values not recognized by mariadb

[fflorent]

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.

[techouse]

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.

[fwieringen]

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

[techouse]

A PR would be most welcome.

Make sure to include tests with it 😊

[fwieringen]

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.

[techouse]

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.

[techouse]

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.

[techouse]

@fwieringen This here might help https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html