MySQL Schema, default TIMESTAMP set to, '0000-00-00 00:00:00' in dada_message_drafts

[justingit]

The, "dada_message_drafts" table has the following schema:

CREATE TABLE IF NOT EXISTS dada_message_drafts (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
list varchar(16),
created_timestamp TIMESTAMP default '0000-00-00 00:00:00', 
last_modified_timestamp TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
name varchar(80), 
screen varchar(80),
role varchar(80),
draft mediumtext
);

But, "0000-00-00 00:00:00" is not a valid default anymore. Attempting to create this table will fail, and will make the entire install fail.

[prbt2016]

Hi @justingit ,

The above change in the schema file /extras/SQL/mysql_schema.sql is causing issue on MYSQL version 5.5 as there can be only one 'TIMESTAMP' column with 'CURRENT_TIMESTAMP' in DEFAULT or ON UPDATE clause. So while installing Dada mail version 11.4.4 i get the following issue :

Attempting to create SQL Tables...
Problems Creating SQL Tables! STOPPING!

So to check with the problem I imported the mysql_schema.sql manually into the database, I encountered the following error on server with MYSQL version 5.5 :

SQL query:

CREATE TABLE IF NOT EXISTS dada_message_drafts(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
list varchar( 16 ) ,
created_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
last_modified_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
name varchar( 80 ) ,
screen varchar( 80 ) ,
role varchar( 80 ) ,
draft mediumtext
);

MySQL said: Documentation
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

However this works on MYSQL 5.6, seems to be a compatibility issue. Your site documentation mentions that the script is tested on MYSQL 5.
https://dadamailproject.com/support/documentation-11_4_4/requirements.pod.html

Could you please fix this issue for MYSQL 5.5?.

[justingit]

I won't be fixing on v5.5 of MySQL, but I will update the system requirement docs.

MySQL 5.5 was released 8 1/2 years ago, so unfortunately I'll have to go with the times. I can't easily fix for v5.5 and make things work for everything newer than 5.5. If you can come up with a scheme that easily works with both v5.5 and newer, I'll be happy to look into accepting it.

The schema used in previous versions of Dada Mail (v11.4.3 would work) will be compatible with v5.5 of MySQL I beleive.