Problem of schema with mailwatch_db_clean.php and maillog timestamp

[stefaweb]

Hello!

It seems that a problem solved in 1.2.4 is back.

See https://github.com/mailwatch/MailWatch/issues/800

Old mail not cleaned produce a false number of mail in graph and others parts.

[Skywalker-11]

Can you execute show create table maillog;

[stefaweb]

Here it goes!

In my prod server after 1.2.5 upgrade.

CREATE TABLE `maillog` (
 `maillog_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `timestamp` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
 `id` longtext COLLATE utf8mb4_unicode_ci,
 `size` bigint(20) DEFAULT '0',
 `from_address` longtext COLLATE utf8mb4_unicode_ci,
 `from_domain` longtext COLLATE utf8mb4_unicode_ci,
 `to_address` longtext COLLATE utf8mb4_unicode_ci,
 `to_domain` longtext COLLATE utf8mb4_unicode_ci,
 `subject` longtext COLLATE utf8mb4_unicode_ci,
 `clientip` longtext COLLATE utf8mb4_unicode_ci,
 `archive` longtext COLLATE utf8mb4_unicode_ci,
 `isspam` tinyint(1) DEFAULT '0',
 `ishighspam` tinyint(1) DEFAULT '0',
 `issaspam` tinyint(1) DEFAULT '0',
 `isrblspam` tinyint(1) DEFAULT '0',
 `isfp` tinyint(1) DEFAULT '0',
 `isfn` tinyint(1) DEFAULT '0',
 `spamwhitelisted` tinyint(1) DEFAULT '0',
 `spamblacklisted` tinyint(1) DEFAULT '0',
 `sascore` decimal(7,2) DEFAULT '0.00',
 `spamreport` longtext COLLATE utf8mb4_unicode_ci,
 `virusinfected` tinyint(1) DEFAULT '0',
 `nameinfected` tinyint(1) DEFAULT '0',
 `otherinfected` tinyint(1) DEFAULT '0',
 `report` longtext COLLATE utf8mb4_unicode_ci,
 `ismcp` tinyint(1) DEFAULT '0',
 `ishighmcp` tinyint(1) DEFAULT '0',
 `issamcp` tinyint(1) DEFAULT '0',
 `mcpwhitelisted` tinyint(1) DEFAULT '0',
 `mcpblacklisted` tinyint(1) DEFAULT '0',
 `mcpsascore` decimal(7,2) DEFAULT '0.00',
 `mcpreport` longtext COLLATE utf8mb4_unicode_ci,
 `hostname` longtext COLLATE utf8mb4_unicode_ci,
 `date` date DEFAULT NULL,
 `time` time DEFAULT NULL,
 `headers` longtext COLLATE utf8mb4_unicode_ci,
 `quarantined` tinyint(1) DEFAULT '0',
 `rblspamreport` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
 `token` char(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `released` tinyint(1) DEFAULT '0',
 `salearn` tinyint(1) DEFAULT '0',
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`maillog_id`),
 KEY `maillog_datetime_idx` (`date`,`time`),
 KEY `maillog_id_idx` (`id`(20)),
 KEY `maillog_clientip_idx` (`clientip`(20)),
 KEY `maillog_from_idx` (`from_address`(191)),
 KEY `maillog_to_idx` (`to_address`(191)),
 KEY `maillog_host` (`hostname`(30)),
 KEY `from_domain_idx` (`from_domain`(50)),
 KEY `to_domain_idx` (`to_domain`(50)),
 KEY `maillog_quarantined` (`quarantined`),
 KEY `timestamp_idx` (`timestamp`),
 FULLTEXT KEY `subject_idx` (`subject`)
) ENGINE=InnoDB AUTO_INCREMENT=1411584 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

[stefaweb]

Schema in 1.2.4

`timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`

Schema in 1.2.5

`timestamp timestamp NOT NULL,`

[stefaweb]

upgrade.php in 1.2.4

   // Change timestamp to only be updated on creation to fix messages not beeing deleted from maillog
    echo pad(' - Fix schema for timestamp field in `maillog` table');
    if ($link->server_version < 50600) {
        //MySQL < 5.6 cannot handle two columns with CURRENT_TIMESTAMP in DEFAULT
        //First query removes ON UPDATE CURRENT_TIMESTAMP and sets a default different from CURRENT_TIMESTAMP
        //Second query drops the default
        $sql1 = 'ALTER TABLE `maillog` CHANGE `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT 0';
        $sql2 = 'ALTER TABLE `maillog` ALTER COLUMN `timestamp` DROP DEFAULT';
        executeQuery($sql1);
        executeQuery($sql2, true);
    } else {
        $sql = 'ALTER TABLE `maillog` CHANGE `timestamp` `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP';
        executeQuery($sql);
    }

upgrade.php in 1.2.5

    // Change timestamp to only be updated on creation to fix messages not beeing deleted from maillog
    // We don't need a default / on update value for the timestamp field in the maillog table because we only change it in mailwatch.pm
    //  where we use the current system time from perl (not mysql function) as value. So we can remove it. Initial remove because MySQL
    //  in version < 5.6 cannot handle two columns with CURRENT_TIMESTAMP in DEFAULT
    echo pad(' - Fix schema for timestamp field in `maillog` table');
    //First query removes ON UPDATE CURRENT_TIMESTAMP and sets a default different from CURRENT_TIMESTAMP
    //Second query drops the default
    $sql1 = 'ALTER TABLE `maillog` CHANGE `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT 0';
    $sql2 = 'ALTER TABLE `maillog` ALTER COLUMN `timestamp` DROP DEFAULT';
    executeQuery($sql1);
    executeQuery($sql2, true);

[endelwar]

in this commit https://github.com/mailwatch/MailWatch/commit/fcecf26d17d0f9b8a9ff7a46af83b89a50afad2b I changed the default from 0 to CURRENT_TIMESTAMP because MySQL 5.7 and 8.0 returns an error and MariaDB 10.1/10.2/10.3 revert the on update after the second query

These are the reseult of the 2 queries with ALTER TABLE 'maillog' CHANGE 'timestamp' 'timestamp' TIMESTAMP NOT NULL DEFAULT 0 as first query

mysql55 - 5.5.57
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=0000-00-00 00:00:00 - Extra:
After  -> Default= - Extra:
------------------------------------------
mysql56 - 5.6.37
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=0000-00-00 00:00:00 - Extra:
After  -> Default= - Extra:on update CURRENT_TIMESTAMP
------------------------------------------
mysql57 - 5.7.19
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
mysql57: doesn't work [An exception occurred while executing 'ALTER TABLE `maillog` CHANGE `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT 0':

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'timestamp']
------------------------------------------
mysql80 - 8.0.2-dmr
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
mysql80: doesn't work [An exception occurred while executing 'ALTER TABLE `maillog` CHANGE `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT 0':

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'timestamp']
------------------------------------------
mariadb55 - 5.5.57-MariaDB-1~wheezy
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=0000-00-00 00:00:00 - Extra:
After  -> Default= - Extra:
------------------------------------------
mariadb100 - 10.0.32-MariaDB-1~jessie
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=0000-00-00 00:00:00 - Extra:
After  -> Default= - Extra:on update CURRENT_TIMESTAMP
------------------------------------------
mariadb101 - 10.1.26-MariaDB-1~jessie
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=0000-00-00 00:00:00 - Extra:
After  -> Default= - Extra:on update CURRENT_TIMESTAMP
------------------------------------------
mariadb102 - 10.2.8-MariaDB-10.2.8+maria~jessie
Before -> Default=current_timestamp() - Extra:on update current_timestamp()
Middle -> Default=0000-00-00 00:00:00 - Extra:
After  -> Default=current_timestamp() - Extra:on update current_timestamp()
------------------------------------------
mariadb103 - 10.3.0-MariaDB-10.3.0+maria~jessie
Before -> Default=current_timestamp() - Extra:on update current_timestamp()
Middle -> Default=0000-00-00 00:00:00 - Extra:
After  -> Default=current_timestamp() - Extra:on update current_timestamp()
------------------------------------------

Here is the results with ALTER TABLE 'maillog' CHANGE 'timestamp' 'timestamp' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP as first query

mysql55 - 5.5.57
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=CURRENT_TIMESTAMP - Extra:
After  -> Default= - Extra:
------------------------------------------
mysql56 - 5.6.37
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=CURRENT_TIMESTAMP - Extra:
After  -> Default= - Extra:
------------------------------------------
mysql57 - 5.7.19
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=CURRENT_TIMESTAMP - Extra:
After  -> Default= - Extra:
------------------------------------------
mysql80 - 8.0.2-dmr
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=CURRENT_TIMESTAMP - Extra:
After  -> Default= - Extra:
------------------------------------------
mariadb55 - 5.5.57-MariaDB-1~wheezy
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=CURRENT_TIMESTAMP - Extra:
After  -> Default= - Extra:
------------------------------------------
mariadb100 - 10.0.32-MariaDB-1~jessie
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=CURRENT_TIMESTAMP - Extra:
After  -> Default= - Extra:
------------------------------------------
mariadb101 - 10.1.26-MariaDB-1~jessie
Before -> Default=CURRENT_TIMESTAMP - Extra:on update CURRENT_TIMESTAMP
Middle -> Default=CURRENT_TIMESTAMP - Extra:
After  -> Default= - Extra:
------------------------------------------
mariadb102 - 10.2.8-MariaDB-10.2.8+maria~jessie
Before -> Default=current_timestamp() - Extra:on update current_timestamp()
Middle -> Default=current_timestamp() - Extra:
After  -> Default=current_timestamp() - Extra:
------------------------------------------
mariadb103 - 10.3.0-MariaDB-10.3.0+maria~jessie
Before -> Default=current_timestamp() - Extra:on update current_timestamp()
Middle -> Default=current_timestamp() - Extra:
After  -> Default=current_timestamp() - Extra:
------------------------------------------

MariaDB 10.2 and 10.3 still keep the default after dropping it

[stefaweb]

On the prod, I'm using MariaDB 10.0.32 and after 1.2.5 upgrade I have:

timestamp timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,

On the dev, I'm using MariaDB 10.1.26 and after 1.2.5 I have:

timestamp timestamp NOT NULL,

[Skywalker-11]

It should not a problem if MariaDB keeps the default because we never leave the field empty when inserting a mail entry.

Here MariaDB 10.0.29 and 10.1.23 have timestamp timestamp NOT NULL after the 1.2.5 update

[stefaweb]

Sorry, a little lost.

What is the right structure to have to solve the problem?

[Skywalker-11]

The timestamp column cannot have the ON UPDATE CURRENT_TIMESTAMP because this would result in the problem you described in this issue. It is because the daily cron scripts will update other fields for the mails and so would change all timestamps of effected mails to one single date and time.

For MySQL with version < 5.6 we also have to make sure to not have a default value as this would result in an error (two columns with on update/default timestamp in a table). For MySQL >= 5.6 it is not a problem.

[stefaweb]

Hello @Skywalker-11

When I apply

"ALTER TABLE maillog CHANGE timestamp timestamp TIMESTAMP NOT NULL"

I got in structure:

timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Not found a way to only have:

timestamp timestamp NOT NULL,

A problem with MariaDB 10.0.32 ?

[stefaweb]

I finally found a way. I used phpmyadmin to drop default as "ALTER TABLE maillog ALTER COLUMN timestamp DROP DEFAULT" don't want to work interactively.

The update ran for 2 minutes and now its ok.

[stefaweb]

Problem over here with timestamp timestamp NOT NULL, altered manualy.

But upgrade.php was not good to alter the table wth MariaDB 10.0.32. How to solve this for others people?

[stefaweb]

New PR for this: https://github.com/mailwatch/MailWatch/commit/3155d6bc9077693aa4a17bf5119d27662b66c947

[endelwar]

With this a4f03057b9816b63fe8ed8d2e195fa34f039a380 commit there should be no more problems upgrading from any sql schema version, at least for maillog.timestamp column (famous last words :))

[stefaweb]

Will see... 😀

I close it now.