CURRENT_TIMESTAMP for acc_create_time is necessary. last_login is used by some of our customers but mostly from tools outside Tigase. I do not know what would be most sensible default for this field except NULL which indicates that the user never logged in, which makes sense.

That's the problem: you can't use NULL as default:

create table test1 (
    uid bigint unsigned NOT NULL auto_increment,
    acc_create_time timestamp DEFAULT CURRENT_TIMESTAMP,
    last_login timestamp DEFAULT NULL,
    primary key (uid));

ERROR 1067 (42000): Invalid default value for 'last_login'

This is because of strict mode.

Daniele Ricci wrote:

Starting version 5.7, MySQL has enabled strict mode by default.

Could you share exact version? especially in the context of:

anyway a default value of NULL wouldn't work because of another TIMESTAMP column (acc_create_time) having CURRENT_TIMESTAMP as default. I don't understand why that could affect last_login having a NULL default, but it does. As a matter of fact, if you don't include acc_create_time in the DDL, last_login with default NULL works just fine.

One field should not affect another one so maybe it's a bug in MySQL?

Could you share exact version? especially in the context of:

Actually, after a few more tests I can say the problem happens everytime. It is not related to any other column, the problem is the default NULL value which is invalid.

And it seems like it's not a bug: https://bugs.mysql.com/bug.php?id=80086

Daniele, we will work on a solution, but, do you have any suggestions on, what would be the best way to resolve this?

Artur Hefczyc wrote:

Daniele, we will work on a solution, but, do you have any suggestions on, what would be the best way to resolve this?

I believe converting columns last_login and last_logout to DATETIME (nullable) would be the right fix to this. I tried on MySQL 5.7, it works, however I don't know the impact on Tigase code. JDBC for MySQL returns javax.sql.Timestamp, just like the TIMESTAMP type, so it should not create any issue on reading. I'd focus more on checking the code that writes into that columns.

I think it should be possible. I am assigning this to Wojciech who will be working on the code.

Actually needed change was smaller than expected (thanks Andrzej!)