Incorrect MySQL date04 Jul 2011
Several ingame time calculation we made in game are based on Paris time. Some weeks ago, we decided to make a pass on all our servers to always use Paris Time (GMT +1).
Today, I spotted that logs we save in the DB have some date inaccuracy. It appears that our mysql server and instances weren't always updated to the correct date. Some hours later, here is what I learned :
Finding and updating MySQL date
You can tell what timezone mysql should use when you start the service. If you
don't specify anything, it will use the system time. Once loaded, you can get
its time by running
This is the easiest way to spot errors.
To know the defined timezone, run
SELECT @@global.time_zone. If not defined,
SYSTEM, which is not very helpful.
Note here that even if you changed the system date AFTER you started mysql, mysql will still use the date that was in effect when you first launched it.
It means that changing your server time will not affet running mysql
processes. You'll have to restart mysql to do that :
Finding and updating the server date
Even after restarting mysql on some servers, the mysql date was still
incorrect. After connecting the the sql server, I found that it was the server
time that was incorrectly set (I just type
To update the current time zone, I had to call
sudo dpkg-reconfigure tzdata
(I'm using ubuntu) and choose the correct city
Updating mysql running through ndb_mgm
I'm no server expert, so this part was a little trickier. Some of our databases are using ndb cluster for replication. Reloading those configurations was harder.
First, I had to connect to the server running the ndb management and call
ndb_mgm. In the later prompt, I typed show and this get me the list of all
servers currently managed.
I then shut them down typing
The, I reloaded the management and the node running on this server by doing
sudo /etc/init.d/mysql-ndb-mgm restart and
Finally, I had to connect to all the servers I saw earlier (with the show
command) and run
sudo /etc/init.d/mysql-ndb restarton each of them
Want to add something ? Feel free to get in touch on Twitter : @pixelastic