mailtrain
mailtrain copied to clipboard
Mailtrain-org
TIMEZONE not showing local time
I am using
- /etc/localtime:/etc/localtime:ro
- /etc/timezone:/etc/timezone:ro
in docker-compose.yml
executing the date command in the container gives the correct time.
But after I did today an import of subscribers it shows in the list in the column created in 2 hours (meaning the future), as I live in UTC+2, it seems to just use UTC, not recalculating either only in the frontend UTC not to the correct local time of the server or also in the backend. The second could be problamatic when using schedules. But maybe schedules are set to respect the local set TIMEZONE of each subscriber, e.g. that everyone gets a newsletter in the morning at 8 a.m. in their local time.
I noticed the same thing a while ago and can confirm, all the steps!
@jonathanmmm Did you find a solution for the bug? Maybe setting System time equal to UTC? My guess is also, that this bug prevents triggered campaigns from being fired. @bures Could that be the issue for it?
Update 1 Commenting out the time related volumes for all containers fixes the creation time problem! Lets see if automation works now...
...
volumes:
# - /etc/timezone:/etc/timezone:ro
# - /etc/localtime:/etc/localtime:ro
...
Heureka It does work now :)
My guess when commenting them out, is that the container now internally also uses UTC (could you check that with date command inside the container)?
And that way no shift happens (at least in the web ui, with creation).
well this was what I was hoping for and it indeed does have that effect. It is important to do it on all containers though!
bash-5.0# date
Mon May 16 18:56:00 UTC 2022
And by Heureka I ment, that the result is that automation triggers now work a intended! :smile:
well this was what I was hoping for and it indeed does have that effect. It is important to do it on all containers though!
bash-5.0# date Mon May 16 18:56:00 UTC 2022And by Heureka I ment, that the result is that automation triggers now work a intended! :smile:
Great, does schedules work with the time of the recipient or with UTC?
What do you mean with all containers?
Do you mean database?
UTC, if I understand your question correctly. I mean I commented out the time related volumes on all containers of the mailtrain stack. Like so...
version: '3'
networks:
web:
external: true
internal:
external: false
driver: bridge
inbound:
external: true
services:
mailtrain:
# image: mailtrain/mailtrain:latest
build: .
depends_on:
- mysql
- redis
- mongo
links:
- mysql
- redis
- mongo
environment:
# - PORT_TRUSTED=80
# - WITH_LDAP=true
# - LDAP_HOST=ldap://ldap.${DOMAIN}
# - LDAP_BIND_USER=dn=admin,dc=domain,dc=tld
# - LDAP_BIND_PASS=${LDAP_PASS}
# - LDAP_UIDTAG=cn
# - LDAP_BASEDN=ou=people,dc=domain,dc=tld
# - LDAP_FILTER=(objectclass=AsteriskSIPUser)
- WWW_PROXY=true
- ADMIN_PASSWORD=${ADMIN_PASSWORD}
- DEFAULT_LANGUAGE=${LOCALE}
- URL_BASE_TRUSTED=https://mailtrain.${DOMAIN}
- URL_BASE_SANDBOX=https://sbox.${DOMAIN}
- URL_BASE_PUBLIC=https://lists.${DOMAIN}
- MYSQL_HOST=mysql
- MYSQL_PORT=3306
- MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
- MYSQL_DATABASE=${MYSQL_DATABASE}
- MYSQL_USER=${MYSQL_USER}
- MYSQL_PASSWORD=${MYSQL_PASSWORD}
# ports:
# - "3000:3000"
# - "3003:3003"
# - "3004:3004"
volumes:
# - /etc/timezone:/etc/timezone:ro
# - /etc/localtime:/etc/localtime:ro
- mailtrain-files:/app/server/files
networks:
- internal
- web
- inbound
labels:
- "traefik.enable=true"
- "traefik.http.routers.mailtrain.rule=Host(`mailtrain.${DOMAIN}`)"
- "traefik.http.routers.mailtrain.entrypoints=websecure"
- "traefik.http.routers.mailtrain.service=mailtrain"
- "traefik.http.routers.mailtrain.tls.certresolver=myresolver"
- "traefik.http.services.mailtrain.loadbalancer.server.port=3000"
- "traefik.http.routers.sbox.rule=Host(`sbox.${DOMAIN}`)"
- "traefik.http.routers.sbox.entrypoints=websecure"
- "traefik.http.routers.sbox.service=sbox"
- "traefik.http.routers.sbox.tls.certresolver=myresolver"
- "traefik.http.services.sbox.loadbalancer.server.port=3003"
- "traefik.http.routers.lists.rule=Host(`lists.${DOMAIN}`)"
- "traefik.http.routers.lists.entrypoints=websecure"
- "traefik.http.routers.lists.service=lists"
- "traefik.http.routers.lists.tls.certresolver=myresolver"
- "traefik.http.services.lists.loadbalancer.server.port=3004"
- "com.centurylinklabs.watchtower.enable=true"
mysql:
image: mariadb:10.4
environment:
- MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
- MYSQL_DATABASE=${MYSQL_DATABASE}
- MYSQL_USER=${MYSQL_USER}
- MYSQL_PASSWORD=${MYSQL_PASSWORD}
volumes:
- mysql-data:/var/lib/mysql
# - /etc/timezone:/etc/timezone:ro
# - /etc/localtime:/etc/localtime:ro
networks:
- internal
labels:
- "com.centurylinklabs.watchtower.enable=true"
mongo:
image: mongo:4-xenial
volumes:
- mongo-data:/data/db
# - /etc/timezone:/etc/timezone:ro
# - /etc/localtime:/etc/localtime:ro
networks:
- internal
labels:
- "com.centurylinklabs.watchtower.enable=true"
redis:
image: redis:5
volumes:
- redis-data:/data
# - /etc/timezone:/etc/timezone:ro
# - /etc/localtime:/etc/localtime:ro
networks:
- internal
labels:
- "com.centurylinklabs.watchtower.enable=true"
volumes:
mysql-data:
redis-data:
mongo-data:
mailtrain-files:
Ok, problem in my case: I use mysql for a bunch of other services, so I won't put mysql into UTC.
But your setup just tells that something is wrong, it is more of a quick fix, than a real fix. Mailtrain should (or would be nice) work with different time zones.
Well since mysql in docker-compose is deployed as a dedicated microservice which only handles mailtrain data, in my case I don't see a huge problem in chaging system time to utc.
Well since mysql in docker-compose is deployed as a dedicated microservice which only handles mailtrain data, in my case I don't see a huge problem in chaging system time to utc.
Still have not commented these lines out.
I found/saw the following in server/services/tzupdate.js:
// This script re-calculates timezone offsets once a day.
// We need this to be able to send messages using subscriber's local time
// The best option would be to use built-in timezone data of MySQL but
// the availability of timezone data is not guaranteed as it's an optional add on.
// So instead we keep a list of timezone offsets in a table that we can use to
// JOIN with subscription table. Subscription table includes timezone name for
// a subscriber and tzoffset table includes offset from UTC in minutes
If this is true, maybe ones have to wait a day to show the correct time. I created a new subscriber in a list, I filled in the first name and last name with the current time (in my region - UTC+2) and set the TIMEZONE to the ones the server and I are in. Will wait a day, if it shows the correct creation time then (not like right now "in 2 hours" [translated]). Will report.
As you can see in the text schedules should send corresponding to the local timezone of the subscriber. This way everybody could get a newsletter at 8 a.m. in their local timezone.
Well my experience tells me, it does not reset to the correct time after 24 hours. But curious what you will report back!
I don't think that running the server in UTC affects users' time zone. If any I would suggest it helps the system in doing it right, since there seems to be something wrong with calculating 'mailtrain time' when not in UTC time zone. I would think user's set time zones get misscalculated by the same offset just as system time does. Will do a test and report back as well :wink:
In the UI I only can see something like "1 day ago", which is almost correct. I looked in the database and found the following:
| status_change | created | updated |
|---|---|---|
| 2022-05-17 11:19:09 | 2022-05-17 13:19:09 | 2022-05-17 13:19:09 |
13:09 is close to the time I entered into the first name field (13:18), being the UTC+2 time. It seems created and updated are entered correctly, but status_change are not.
Ok, the time shows correctly, after I commented these two lines out and put mysql time to +02:00 (for UTC+2) into /app/server/config/default.yaml instead of local (keeping the two lines not commented out in the database container).
But this seems to be not a correct way to do it.
@bures What do you think could be the problem and how to fix it or is Mailtrain V2 not supported to run on anything else (and only UTC as the timezone)?
I quite struggled to configure the MySQL client to make sure we get the time right based on timezone of the client. So eventually I forced UTC wherever possible. If anyone wants to try to figure out how to configure the MySQL client so that it does the timezone translation correctly, I'll be happy to accept the PR. But it has be thoroughly tested.
I don't recall exactly the issues any more, but I would guess the DB or the DB client is the culprit.
@bures So you basically run your mailtrain instances in pure UTC as well and mailtrain features all work as expected concerning the timing?
I tested it the following way: UTC+2 is my local time I set the timezone for the subscriber as Europe/Berlin (UTC+2) and in the schedule in the campaign I set it also to Europe/Berlin.
mailtrain was running UTC and database running UTC+2 (but put the +02:00 inside the yaml config file of mailtrain, like said, if database is running on a different server with a different timezone).
I scheduled this afternoon a campaign for 17:00 and it arrived at 17:00 (local time). So scheduling seems to work, but as everything was UTC+2 in my case (device I accessed the WebUI of Mailtrain) and such, I don't know how it would work with different subscribers in different timezones and putting the campaign in a different timezone (and having the WebUI from a device in a completely different timezone).
I don't recall exactly the issues any more, but I would guess the DB or the DB client is the culprit.
the DB runs perfectly fine with UTC+2, I even used the select now (found in another issue) via mysql command inside the database container and it showed the correct local time. So it is either the db client in mailtrain (as status_change seems to be set incorrectly, but other values are fine), like mentioned here: https://github.com/Mailtrain-org/mailtrain/issues/1221#issuecomment-1129886316
Will test now again everything UTC+2 and sending a campaign, maybe this "created" time is just visually off (and everything, especially scheduling) works as expected.
Ok, so I sent a campaign to one that had UTC in his subscriber profile and one that had Europe/Berlin (Germany - UTC+2) in his profile. Both E-Mails arrived at 22:00 o'clock (which was the scheduled time of the campaign). One E-Mail (even both emails are being in the same timezone) should have arrived at 20:00 o'clock (the times in my email clients are UTC+2). The timezone in the campaign itself was also Europe/Berlin.
So something is wrong, probably the "use the timezone of the subscriber" doesn't work correctly (or did I have to wait at least a day for this script to calculate the offset correctly? If yes, this script should be executed everytime a scheduled campaign is started).
I'm not sure if we don't have some misunderstanding here. Do I understand correctly that you are using "Schedule send" feature on the regular campaign and that you expect this to work based on each recipients timezone? Or is there anything else. In any case, the "Schedule send" delivers the campaign at once at scheduled time (with timezone), it does not take into account the timezone of the recipient.
I'm not sure if we don't have some misunderstanding here. Do I understand correctly that you are using "Schedule send" feature on the regular campaign and that you expect this to work based on each recipients timezone? Or is there anything else. In any case, the "Schedule send" delivers the campaign at once at scheduled time (with timezone), it does not take into account the timezone of the recipient.
Correct, I sued this "send later/scheduled send" feature in a campaign. Ok, so it only takes into account the timezone I enter in the "scheduled send" field?
for what is the timezone useful (in recipients), if not for such a feature (like sending a newsletter to everyone in their local time at morning)?
Correct. That's why the timezone field in the Schedule send. If it was using the timezone of the recipient, the tz field in Schedule send would make no sense.
Regarding why the TZ is there for the recipient, I don't know. I just adopted the feature from V1. I guess it can still be useful if you use it in segments. But you would have to schedule several campaigns - one per each segment. So far this is simply not automated.
Correct. That's why the timezone field in the Schedule send. If it was using the timezone of the recipient, the tz field in Schedule send would make no sense.
Regarding why the TZ is there for the recipient, I don't know. I just adopted the feature from V1. I guess it can still be useful if you use it in segments. But you would have to schedule several campaigns - one per each segment. So far this is simply not automated.
Ok, then probably the comment in server/services/tzupdate.js should be updated.
We are going to start with the development and testing of mailtrain v3 in the next weeks.
You are welcome to help us with the testing as soon as the first release candidate is available.
