mysql event last executed time incorrect by 6 hours

[kathygriffin]

I will run this and it will execute however it shows the incorrect execution time I ran select now() and it shows the correct time how could it be off by 6 hours? Any pointers or suggestions is greatly appreicated.

DELIMITER //
CREATE EVENT testdb
ON SCHEDULE EVERY 1 MINUTE
STARTS '2013-12-06 23:35:00'
DO
BEGIN
UPDATE `test`.`test` SET number = number + 1;
END //
DELIMITER

 

[Psycho]

Sounds like a timezone issue. Where are you seeing that the execution time is wrong? Based upon my understanding that query should fail because you are trying to set an event to start in the past.

[kathygriffin]

I use phpmyadmin and go to mysql > events then browser and in column last_executed. I am using lamp centos 6.4 when I run etc/sysconfig/clock it shows America/New_York which would be eastern time. When I put in date as the command its showing the correct.. Is there maybe something else for mysql somwhere else?

 

 

As for my timezone it is showing system when I run SELECT @ @ Global.time_zone, @ @ session;

 

Thanks

[Psycho]

Well, UTC time is 5 hours behind New York. I was guessing it was maybe the time being returned in UTC instead of the local time. But, that should display a 5 hours time difference. So, I don't have anything else to add at this time. Sorry.

[kathygriffin]

I just double checked.. its 5 hours ahead actually.. Sorry I my math was wrong...  yea maybe another setting somewhere in mysql or something?  Is there any time in just mysql that runs differently then the server itself?

Edited March 5, 2014 by kathygriffin

[Psycho]

I don't know. Maybe the PHPMyAdmin page for the events was specifically coded to display the time in UTC.

 

EDIT: I didn't read the article, but found this wiki item: MySQL What would lead the event scheduler to assume UTC?

 

EDIT #2: Apparently it is a bug which has already been reported: http://bugs.mysql.com/bug.php?id=17835

 

But, it won't be fixed

 

 

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Edited March 5, 2014 by Psycho

[kathygriffin]

Dang.. Yea that looks like thats it. I even went into /etc/my.cnf and tried to put in the default-time-zone and it didn't seem to go... Thank you very much. How did you look up that bug so quickly?

[Psycho]

Dang.. Yea that looks like thats it. I even went into /etc/my.cnf and tried to put in the default-time-zone and it didn't seem to go... Thank you very much. How did you look up that bug so quickly?

 

Google.

 

I searched for "PHPMyAdmin Events UTC". The third result showed a snipped of content relating to UTC time so I clicked it. On that page was a link to the first page I provided. After I posted that I went back to the page and saw a link at the bottom to "View thread with replies". That page only had the original post and two replies. The last one was a guy reporting that it was a bug and linking to the bug report (which was the second page I provided). Took all of about 5 minutes or less.

[kathygriffin]

You have any suggestions or does anyone else have any suggestions on what version doesn't have any bugs? I upgraded to 5.1.73 but that didn't fix anything.

[Psycho]

As I already provided, the notes in the bug state they don't plan on fixing it. It really isn't a bug. In the database, timestamps are not stored specific to a timezone. Typically those records are modified on-the-fly when you query the database for those values based upon the timezone of the server. But, if you were to have an application that is used by users world-wide you can change the behavior at run-time so you can have the data returned based upon the user's timezone. This allows you to show data appropriate to the user. For example, if you have a system to record transactions and you make a new transaction at 10:00AM, I should see that transaction in the system as having occurred at 9:00AM (I'm in central time). Otherwise, I might see transactions as occurring in the future.

 

I'm not 100% sure, but I believe all the timestamps are actually stored in UTC. It's not like it is something that is exposed to users. If it really bothers you, build your own page to display that data and have it modify the time when it is displayed.

Edited March 5, 2014 by Psycho

[kathygriffin]

Yea I like for things to be less confusing.. As for the time_zone there is a column that ays time_zone abd it shows system. I am wondering if I changed that to eastern it would change...

[kathygriffin]

Actually they are executing at the incorrect time...

[jazzman1]

Since you have the EVENT privileges I'm guessing you have a super privilege on this mysql server or at least to this particular schema or am I wrong?  What kind of privileges you have on this database server?

Edited March 5, 2014 by jazzman1

[kathygriffin]

Yes I have the superuser. Actually I am running everything as root@local  Is there any possible way I can take of system time and put in eastern time? I know I am probably missing something stupid.

[Psycho]

There is nothing for you to fix. It is working as expected. I provided a link to the "bug" report and an excerpt of the response to that bug - which stated they were updating the documentation. Did you even take 5 minutes to look up that documentation? I'm not trying to be an ass, but the information I provided should have made it clear that this had nothing to do with a mis-configured setting. If you had looked up the update to the documentation that was referenced in the earlier post you would see why it is correct behavior.

 

 

 

Each session in MySQL has a session time zone (STZ). This is the session time_zone value that is initialized from the server's global time_zone value when the session begins but may be changed during the session.

The session time zone that is current when a CREATE EVENT or ALTER EVENT statement executes is used to interpret times specified in the event definition. This becomes the event time zone (ETZ); that is, the time zone that is used for event scheduling and is in effect within the event as it executes.

For representation of event information in the mysql.event table, the execute_at, starts, and ends times are converted to UTC and stored along with the event time zone. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. The last_executed time is also stored in UTC.

 

So, all events are scheduled based upon UTC time. After reading the above, it makes perfect sense, but it may not be blatantly obvious. You first need to understand timestamps. A timestamp represents the number of seconds since the Unix epoch (Jan 1, 1970 midnight). It is the number of seconds in UTC time. So, a particular timestamp for my would represent March 3rd, 2014 10:45 AM. But, that exact same timestamp would represent March 3rd, 2014 11:45 AM to you because you are 1 hour ahead of me. A timestamp is different than a datetime record in the database. A datetime would be used for something like recoding business hours. If a chain of stores exists in multiple timezones, you might store a single start and end times for hours of operations on a particular day. On that day, all the stores will open at 10:00AM (without any respect to the timezone).

 

So, with that in mind, the events in MySQL are scheduled using a timestamp. However, it is always based on UTC rather than the servers local timezone. And, that makes sense. Let's say you have an event that should run every 30 minutes and the server is located in the Eastern timezone (UTC-5). So, every 30 minutes that event is executed. Then after an event is executed at 10:00AM the next execution would be at 10:30AM. Then assume at 10:15 AM the server's local timezone is change to Pacific time (UTC - 8 ). The server's local time would be 7:15AM. If it used its local time for the events it would think it did not need to run the event for another 3 hours and 15 minutes.

 

I really don't understand why this is such an issue. You want the event to run every minute, so what is the fixation on what timezone the events are displayed in. As I said before, if you don't like it, build your own page to display the event details and changing the displayed time to your liking.

Edited March 6, 2014 by Psycho

[kathygriffin]

There is nothing for you to fix. It is working as expected. I provided a link to the "bug" report and an excerpt of the response to that bug - which stated they were updating the documentation. Did you even take 5 minutes to look up that documentation? I'm not trying to be an ass, but the information I provided should have made it clear that this had nothing to do with a mis-configured setting. If you had looked up the update to the documentation that was referenced in the earlier post you would see why it is correct behavior.

 

 

 

 

So, all events are scheduled based upon UTC time. After reading the above, it makes perfect sense, but it may not be blatantly obvious. You first need to understand timestamps. A timestamp represents the number of seconds since the Unix epoch (Jan 1, 1970 midnight). It is the number of seconds in UTC time. So, a particular timestamp for my would represent March 3rd, 2014 10:45 AM. But, that exact same timestamp would represent March 3rd, 2014 11:45 AM to you because you are 1 hour ahead of me. A timestamp is different than a datetime record in the database. A datetime would be used for something like recoding business hours. If a chain of stores exists in multiple timezones, you might store a single start and end times for hours of operations on a particular day. On that day, all the stores will open at 10:00AM (without any respect to the timezone).

 

So, with that in mind, the events in MySQL are scheduled using a timestamp. However, it is always based on UTC rather than the servers local timezone. And, that makes sense. Let's say you have an event that should run every 30 minutes and the server is located in the Eastern timezone (UTC-5). So, every 30 minutes that event is executed. Then after an event is executed at 10:00AM the next execution would be at 10:30AM. Then assume at 10:15 AM the server's local timezone is change to Pacific time (UTC - 8 ). The server's local time would be 7:15AM. If it used its local time for the events it would think it did not need to run the event for another 3 hours and 15 minutes.

 

I really don't understand why this is such an issue. You want the event to run every minute, so what is the fixation on what timezone the events are displayed in. As I said before, if you don't like it, build your own page to display the event details and changing the displayed time to your liking.

 

 

No your not being a ass. I think we are just on two separate pages. I did forget to  mention I was using a different version because I did upgrade to a higher version. I will double check everything and post back.

 

Thanks for all your help so far.

[kathygriffin]

Ok just bare with me on this question. In phpmyadmin Is there anyway to change the last_execution time to my eastern standard time? Instead of showing the UTC time? Can I change the time zone in system to say like eastern or what would it be UTC - 5 instead of saying system? Is something like that even possible?

 

Thank you

[Psycho]

Again, no. This has nothing to do with PHPMyAdmin. Let me repeat again the following from the MySQL manual:

 

 

For representation of event information in the mysql.event table, the execute_at, starts, and ends times are converted to UTC . . .

 

This is how MySQL reports the events. Period. PHPMyAdmin is simply reporting the information reported by MySQL. I'm still at a loss as to why this is such an issue. PHPMyAdmin is an administrative tool. It sounds as if you want some sort of reporting utility to see/manage the events. So, as I've said a couple of times, you can create your own interface to display the data however you wish.

[jazzman1]

Yes I have the superuser. Actually I am running everything as root@local  Is there any possible way I can take of system time and put in eastern time? I know I am probably missing something stupid.

 

Then it's easy If you want to run the MySQL server under different time zone of the computer (linux machine), you just need to find the mysql configuration file, named my.cnf ( under CentOS the path is /etc/my.cnf) and to add the default time zone. Since you're using a linux machine, you can use the usual international denotations for time zone and their abbreviations. So, open up the mysql config file and add the following inside [mysqld] section: 

 

default-time-zone = America/New_York

 

You can also use coordinated Universal Time (UTC) like this:

 

default-time-zone = -5:00

 

Restart MySQL.

 

If you want run only the event schedule under different time zone, try next:

DELIMITER //
CREATE EVENT testdb
ON SCHEDULE EVERY 1 MINUTE
STARTS UTC_TIMESTAMP() - INTERVAL 5 HOUR
DO
BEGIN
UPDATE `test`.`test` SET number = number + 1;
END //
DELIMITER

[kathygriffin]

Thank you.. I just ended up making a timezone cheat sheet for easy cross refrence. Thanks though