1drop tables if exists t1, t2; 2drop view if exists v1; 3delete from mysql.user where user like 'mysqltest\_%'; 4delete from mysql.db where user like 'mysqltest\_%'; 5delete from mysql.tables_priv where user like 'mysqltest\_%'; 6delete from mysql.columns_priv where user like 'mysqltest\_%'; 7flush privileges; 8create table t1 (a int, b datetime); 9create table t2 (c int, d datetime); 10create user mysqltest_1@localhost; 11grant all privileges on test.* to mysqltest_1@localhost; 12connect tzuser, localhost, mysqltest_1,,; 13connection tzuser; 14show grants for current_user(); 15Grants for mysqltest_1@localhost 16GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` 17GRANT ALL PRIVILEGES ON `test`.* TO `mysqltest_1`@`localhost` 18set time_zone= '+00:00'; 19set time_zone= 'Europe/Moscow'; 20select convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC'); 21convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC') 222004-10-21 15:00:00 23select convert_tz(b, 'Europe/Moscow', 'UTC') from t1; 24convert_tz(b, 'Europe/Moscow', 'UTC') 25update t1, t2 set t1.b = convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC') 26where t1.a = t2.c and t2.d = (select max(d) from t2); 27select * from mysql.time_zone_name; 28ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' 29select Name, convert_tz('2004-10-21 19:00:00', Name, 'UTC') from mysql.time_zone_name; 30ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' 31connection default; 32disconnect tzuser; 33connection default; 34delete from mysql.db where user like 'mysqltest\_%'; 35flush privileges; 36grant all privileges on test.t1 to mysqltest_1@localhost; 37grant all privileges on test.t2 to mysqltest_1@localhost; 38connect tzuser2, localhost, mysqltest_1,,; 39connection tzuser2; 40show grants for current_user(); 41Grants for mysqltest_1@localhost 42GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` 43GRANT ALL PRIVILEGES ON `test`.`t2` TO `mysqltest_1`@`localhost` 44GRANT ALL PRIVILEGES ON `test`.`t1` TO `mysqltest_1`@`localhost` 45set time_zone= '+00:00'; 46set time_zone= 'Europe/Moscow'; 47select convert_tz('2004-11-31 12:00:00', 'Europe/Moscow', 'UTC'); 48convert_tz('2004-11-31 12:00:00', 'Europe/Moscow', 'UTC') 49NULL 50Warnings: 51Warning 1292 Incorrect datetime value: '2004-11-31 12:00:00' 52select convert_tz(b, 'Europe/Moscow', 'UTC') from t1; 53convert_tz(b, 'Europe/Moscow', 'UTC') 54update t1, t2 set t1.b = convert_tz('2004-11-30 12:00:00', 'Europe/Moscow', 'UTC') 55where t1.a = t2.c and t2.d = (select max(d) from t2); 56select * from mysql.time_zone_name; 57ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' 58select Name, convert_tz('2004-11-30 12:00:00', Name, 'UTC') from mysql.time_zone_name; 59ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' 60drop table t1, t2; 61create table t1 (a int, b datetime); 62create table t2 (a int, b varchar(40)); 63update t1 set b = '2005-01-01 10:00'; 64update t1 set b = convert_tz(b, 'UTC', 'UTC'); 65update t1 join t2 on (t1.a = t2.a) set t1.b = '2005-01-01 10:00' where t2.b = 'foo'; 66update t1 join t2 on (t1.a = t2.a) set t1.b = convert_tz('2005-01-01 10:00','UTC','UTC') where t2.b = 'foo'; 67connection default; 68disconnect tzuser2; 69delete from mysql.user where user like 'mysqltest\_%'; 70delete from mysql.db where user like 'mysqltest\_%'; 71delete from mysql.tables_priv where user like 'mysqltest\_%'; 72flush privileges; 73drop table t1, t2; 74create table t1 (a int, b datetime); 75insert into t1 values (1, 20010101000000), (2, 20020101000000); 76create user mysqltest_1@localhost; 77grant all privileges on test.* to mysqltest_1@localhost; 78connect tzuser3, localhost, mysqltest_1,,; 79create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1; 80select * from v1; 81a lb 821 2001-01-01 03:00:00 832 2002-01-01 03:00:00 84select * from v1, mysql.time_zone; 85ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone' 86drop view v1; 87create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1, mysql.time_zone; 88ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 'time_zone' 89connection default; 90disconnect tzuser3; 91drop table t1; 92drop user mysqltest_1@localhost; 93