search

phpList / phplist3

Fully functional Open Source email marketing manager for creating, sending, integrating, and analysing email campaigns and newsletters.
https://www.phplist.org
GNU Affero General Public License v3.0
734 stars 268 forks source link

Problem with definition of timestamp columns #1015

Closed bramley closed 2 months ago

bramley commented 5 months ago

Looking into a problem with processqueue I noticed that the modified column of the sendprocess table was null.

The column definition was different to expected. It allowed a null value, didn't have the default value of current_timestamp, and didn't have the on update current_timestamp that it does have in other databases. This line from admin/structure.php is the column definition, nothing about default or on update.

    'modified'  => array('timestamp', 'Modified'),

It turns out that a mysql variable explicit_defaults_for_timestamp affects that.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp and for mariadb https://mariadb.com/docs/server/ref/mdb/system-variables/explicit_defaults_for_timestamp/

When set to OFF, that adds a default and on update of current_timestamp to the first timestamp field within a table. I think that is what has been relied on for all the timestamp fields in the database (no tables have more than one timestamp field).

At some point the default value for explicit_defaults_for_timestamp has been changed to ON

Since Mysql 8 https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/ and Mariadb in community release 10.5

It is simple to fix the column definitions for timestamp fields

    'modified'  => array('timestamp not null default current_timestamp on update current_timestamp', 'Modified'),

and something similar to update the definition of timestamp columns in existing databases.

But it's not clear how to populate sensibly the ten occurrences of timestamp fields that now have null values.

bramley commented 5 months ago

These are the tables which have a timestamp field

user
list
listuser
message
listmessage
sendprocess
user_message_bounce
user_message_forward
admin
linktrack

The user table might be the one most affected. Adding a subscriber manually seems to set the modified field explicitly, whereas a simple import, copy/paste of email addresses, doesn't. This affects exporting because there is a "When the record was changed " option.

The time field in the user_message_forward table seems to be populated explicitly, but the time field in the user_message_bounce table is sometimes populated and sometimes not.

The linktrack table is read-only I think for old link tracking data.

michield commented 5 months ago

Ah, that needs addressing then. We use that a lot I think, and it's crucial functionality.

Should be fairly straightforward to have a step in "upgrade.php" as well though, to apply for existing systems.

bramley commented 2 months ago

Fixed in #1019