top button
Flag Notify
Site Registration

NOW() is stuck when using mysql

0 votes
191 views

We've been having some issues with one of our MySQL servers lately, and currently the dang thing is "stuck". For at least the last hour, NOW() is returning the same value:

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-06-26 02:27:14 |
+---------------------+

The system variable "timestamp" also has that same time value stored in it. How can we kick this loose so that the values are more current with real time? (it is currently 3:08PM here, despite our MySQL instance thinking it's 2am. The system time on the machine is correct:

$ date
Wed Jun 26 15:08:56 PDT 2013

This is MySQL 5.1.46 running on solaris2.10.

Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day.

posted Jun 26, 2013 by anonymous

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer

This is the expected behavior if you set the timestamp variable in your session. This is the same mechanism that replication uses to execute transactions on the slave with the correct time. Setting timestamp back to default or reopening your connection will fix it.

> set timestamp=**********;
Query OK, 0 rows affected (0.00 sec)

> select now(); select sleep(5); select now();
+---------------------+
| now() |
+---------------------+
| 2013-06-26 21:32:17 |
+---------------------+
1 row in set (0.00 sec)

+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)

+---------------------+
| now() |
+---------------------+
| 2013-06-26 21:32:17 |
+---------------------+
1 row in set (0.00 sec)

> set timestamp=default;
Query OK, 0 rows affected (0.00 sec)

> select now();
+---------------------+
| now() |
+---------------------+
| 2013-06-26 21:33:53 |
+---------------------+
1 row in set (0.00 sec)

> select now();
+---------------------+
| now() |
+---------------------+
| 2013-06-26 21:33:54 |
+---------------------+
1 row in set (0.00 sec)
answer Jun 27, 2013 by anonymous
Similar Questions
+1 vote

I am writing a web application in perl that will create, edit, update and delete data from a MySQL database. I have written a perl module that will manage the connections (issue database handles ). As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password?

+2 votes

How I 'll check how many rows inserted into every second on an average in a table of MySQL database?

0 votes

I am using MySQL Proxy. When I type command like "show databa;" I should get error messages from MySQL server, which is forwarded by MySQL Proxy. How can I access this error message, modify it, and then send the modified error message to the client?

...