Migrating to MySQL default value for Date not null?

Hi all,

I am trying to migrate an application that always was working with Sqlite which contains 2 Date columns that should not be null. While it was working fine with Sqlite which stores dates as text, the creation of these columns is problematic when connecting to a blank MySql database when Myvisualdatabase tries to create the tables. It trows the infamous "Invalid default value for 'Date_Column'.

I have tried to change the default value with "CURRENT_DATE" or "CURRENT_TIMESTAMP" in MyVisualDatabase with no luck, I still get the same error when creating the tables in MySQL. Any experience on anyone side?

Yes, I tried zero and I get the same error saying that it is a wrong default value for a date. However, removing the non null constraint, then modifying the table with the right parameters within MySQL does the trick. It does work also to create the table directly in MySQL before connecting the app, it is just more cumbersome.

@Sparrow

Yes it does but then, if you set a date column not null, you must set a default value and the default value 'now' that works in SQLite does is not accepted by MysQL/MariaDB, there is an error and the table cannot be created,  hence my answer with the correct format above your comment.

Another hiccup when connecting to a fresh database with no tables in MySQL/MariaDB from a MVD project, it tries to create twice the table "_user", throws a duplicate error and creates a useless table "_mvd_roles". This never happens when connecting to an empty database from a compiled project. I tried it multiples times when testing my Delete - Import routine. You can also, as I mentioned before, connect the application to an already filled up database containing the exact structure required with no problem at all.

The subject of this post was really about the default value of a Date column when not null is forced in MySQL.

You are trying to show me that MYSQL does not support 0 or 'now' in date and time. This is known.
But this is not a drawback of MYSQL, but a feature of SQLITE.
For SQLITE, there is no strict correspondence between a given column type and its contents in a cell,
and this is described in the SQLite documents.
As for the default date value, you can assign it in the form in which you define this value.
For example, by writing it in a script.

Brian, Sorry, I didn't look at the sender)))

I'm using a Required parameter but defining the value for the field directly on the form. The value 0 is not informative for me.

p.s. By the way, MARIADB 10.10.3 does not allow you to create from 0.