Correct default timestamp format

[cwmoller]

Correct this error:

2020-08-18 14:55:31 - CMDPHP SQL Backtrace: (/plugins.php[74]:api_plugin_install(), /lib/plugins.php[625]:plugin_mikrotik_install(), /plugins/mikrotik/setup.php[41]:mikrotik_setup_table(), /plugins/mikrotik/setup.php[670]:db_execute(), /lib/database.php[213]:db_execute_prepared())

2020-08-18 14:55:31 - CMDPHP ERROR: A DB Exec Failed!, Error: Invalid default value for 'created'

[lordvalumart]

On my installation, this line causes "Error: Invalid default value for 'created'" when trying to enable the plugin. Choosing a default later than 1970 works, as does the string '0000-00-00 00:00:00'.

My guess is it might be something to do with the current timezone. Mysql converts all TIMESTAMP values to UTC prior to storing them, so the actual value of the default depends on the timezone of the system. Hence it is possible that '1970-01-01 00:00:01' converts to an out-of-range UTC value. See also this link: https://dba.stackexchange.com/questions/139228/how-to-set-timestamp-default-value-to-1970-01-01-000001-utc

Nevertheless this doesn't quite stack up, because my system is in GMT (zero offset to UTC)...

[cwmoller]

Strange, yes. It may come down the version of MySQL/MariaDB in use. I run the official mariadb container.

I can confirm that '0000-00-00 00:00:00' also works for me, so that may be a safer default.

Server version: 10.5.6-MariaDB-1:10.5.6+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table arf (created timestamp not null default '1970-01-01');
ERROR 1067 (42000): Invalid default value for 'created'
MariaDB [test]> create table arf (created timestamp not null default '1970-01-01 00:00:00');
ERROR 1067 (42000): Invalid default value for 'created'
MariaDB [test]> create table arf (created timestamp not null default '1970-01-01 00:00:01');
Query OK, 0 rows affected (0.188 sec)

MariaDB [test]> drop table arf;
Query OK, 0 rows affected (0.135 sec)

MariaDB [test]> create table arf (created timestamp not null default '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.122 sec)

MariaDB [test]> drop table arf;
Query OK, 0 rows affected (0.074 sec)

MariaDB [test]> drop database test;
Query OK, 0 rows affected (0.036 sec)