Importing MariaDB/MySQL schema fails and proposed fixes.. about glewlwyd

[babelouest]

I'm nit sure of what you want to do with TIMESTAMP NULL, AFAIK, if you don't specify NOT NULL on a column, its value can be NULL, so I think the fix won't change anything. What is the problem you had when you imported the schema?

[vidcentum]

Thanks, @babelouest . When I imported the schema without the fix, the following errors occurred.
DB: MySQL.
...
ERROR 1067 (42000): Invalid default value for 'grp_reset_at'
ERROR 1146 (42S02): Table 'es-users.g_reset_password' doesn't exist
ERROR 1067 (42000): Invalid default value for 'grt_last_seen'
ERROR 1146 (42S02): Table 'es-users.g_refresh_token' doesn't exist
ERROR 1215 (HY000): Cannot add foreign key constraint
ERROR 1067 (42000): Invalid default value for 'gss_last_seen'
ERROR 1146 (42S02): Table 'es-users.g_session' doesn't exist

ERROR 1215 (HY000): Cannot add foreign key constraint
...
The following tables were not created
g_refresh_token_scope
g_client_scope
g_access_token
g_refresh_token
g_session
g_reset_password

Then I executed each SQL statement separately to find out the issue. The error was due to default value of the TIMESTAMP field.

Further, I made explicit TIMESTAMP NULL

Reference:

https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
TIMESTAMP Initialization and the NULL Attribute
By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp. To permit a TIMESTAMP column to contain NULL, explicitly declare it with the NULL attribute.

By setting TIMESTAMP NULL, I was able to import schema successfully.

[babelouest]

yup, I see it, we probably have different configuration on our mariadb servers, but if you want to set the default value to NULL on timestamps, I suggest you use the more explicit DEFAULT NULL instead of NULL only. Can you update your pull request with that?

[vidcentum]

Sure, I have updated the file. Could you please check?

Best Regards
Maruthi

[vidcentum]

@babelouest , I tested with TIMESTAMP DEFAULT NULL, and it did not fix the problem. Importing the schema failed again with DEFAULT NULL.
So, I had to revert it to TIMESTAMP NULL.

You may please check the same in your MySQL servers.

[babelouest]

I may have a different configuration on my server because this problem never occurred, and I use this script a lot to test the application in a clean environment. But this syntax seems correct and fix your problem so I'm totally OK.

[vidcentum]

Thanks @babelouest , I will close the issue.