MySQL Bugs: #50130: --sysdate-is-now sets no system variable

Bug #50130	--sysdate-is-now sets no system variable
Submitted:	7 Jan 2010 9:24	Modified:	7 Jan 2010 10:10
Reporter:	Jon Stephens	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	server options, SYSDATE(), system variables

Description:
Starting the server with the --sysdate-is-now option does not set a corresponding server variable.

This has the following implications for replication: 

SYSDATE() is normally unsafe for SBR.

However, using --sysdate-is-now on both master and slave makes it so (since it makes SYSDATE() a synonym for NOW(), and NOW() is considered safe).

However, there is no easy way to tell whether the option is in use on a running server, other than using something like 'ps ... | grep ...' which the user ought not have to do. This information ought to be available to MySQL clients.

How to repeat:
The fact that --sysdate-is-now does not set a server variable is documented. This can also easily be verified as shown here:

jon@grindval:~/bin/mysql-5.1/bin> ./mysqld_safe &
[1] 23154
jon@grindval:~/bin/mysql-5.1/bin> 100107 09:59:52 mysqld_safe Logging to '/home/jon/bin/mysql-5.1/var/grindval.err'.
100107 09:59:52 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql-5.1/var

jon@grindval:~/bin/mysql-5.1/bin> ./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.43 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE '%sysdate%';
Empty set (0.00 sec)

mysql> SHOW STATUS LIKE '%sysdate%';
Empty set (0.00 sec)

mysql> exit
Bye
jon@grindval:~/bin/mysql-5.1/bin> ./mysqladmin -uroot shutdown
100107 10:01:14 mysqld_safe mysqld from pid file /home/jon/bin/mysql-5.1/var/grindval.pid ended
[1]+  Done                    ./mysqld_safe
jon@grindval:~/bin/mysql-5.1/bin> ./mysqld_safe --sysdate-is-now &
[1] 23322
jon@grindval:~/bin/mysql-5.1/bin> 100107 10:01:24 mysqld_safe Logging to '/home/jon/bin/mysql-5.1/var/grindval.err'.
100107 10:01:24 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql-5.1/var

jon@grindval:~/bin/mysql-5.1/bin> ./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.43 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW STATUS LIKE '%sysdate%';
Empty set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%sysdate%';
Empty set (0.00 sec)

Suggested fix:
Add a boolean sysdate_is_now system variable. Should be 0|FALSE when server has not started with --sydate-is-now, and 1|TRUE when it has been started with the option.

This is still an issue in MySQL 5.1.36:

mysql> show global variables like 'sysdate%';
Empty set (0.00 sec)

mysql> show global status like 'sysdate%';
Empty set (0.00 sec)

Note that this is a general problem with most/all server options, as shown in:

http://dev.mysql.com/doc/refman/5.0/en/server-options.html

[Workaround]

Easy way to check this as belows.

mysql> select sysdate(),sleep(3),sysdate() into @a,@b,@c;select 'sysdate-is-now'
 as Variable_name, case when @a=@c then 'ON' else 'OFF' end as Value;
Query OK, 1 row affected (3.01 sec)

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| sysdate-is-now | OFF   |
+----------------+-------+
1 row in set (0.00 sec)