Bug #9979 Use of CONVERT_TZ in multiple-table UPDATE causes bogus privilege error
Submitted: 18 Apr 2005 17:58 Modified: 21 Jul 2005 4:43
Reporter: Dylan Weed Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10/4.1.12.BK OS:Linux (SuSe 9.2 on AMD64)
Assigned to: Jim Winstead CPU Architecture:Any

[18 Apr 2005 17:58] Dylan Weed
Description:
When the CONVERT_TZ() function is used as part of a multiple-table update, the update fails with an error similar to this:

# ERROR 1143 (42000): UPDATE command denied 
# to user 'agg'@'localhost' for column 'START_TIME' in table 'MEALS'

If the same multiple-table update is run without including the CONVERT_TZ() function, it completes successfully, even though the same tables are present in the query.  Are the timezone tables required to have certain permissions set if they are implicitly included in a multi-table update?

How to repeat:
DROP TABLE IF EXISTS MEALS;
CREATE TABLE MEALS (
  EVENT VARCHAR(40) NOT NULL,
  CHEF_ID INT UNSIGNED NOT NULL,
  START_TIME DATETIME NOT NULL
);

DROP TABLE IF EXISTS CHEFS;
CREATE TABLE CHEFS (
  CHEF_ID INT UNSIGNED NOT NULL,
  NAME VARCHAR(40)
);

INSERT INTO MEALS VALUES ('Breakfast', 1, '2005-01-01 08:00');
INSERT INTO MEALS VALUES ('Lunch', 1, '2005-01-01 12:00');
INSERT INTO MEALS VALUES ('Dinner', 2, '2005-01-01 18:00');

INSERT INTO CHEFS VALUES (1, 'Joe');
INSERT INTO CHEFS VALUES (2, 'Bob');

# Updating a datetime column works
UPDATE MEALS M SET M.START_TIME = '2005-01-01 10:00';

# Updating a datetime to a timezone conversion works also
UPDATE MEALS M SET M.START_TIME = CONVERT_TZ(M.START_TIME, 'EST', 'EST');

# Updating a datetime column in a multitable update works.
UPDATE MEALS M JOIN CHEFS C ON (M.CHEF_ID = C.CHEF_ID)
SET M.START_TIME = '2005-01-01 11:00' WHERE C.NAME='Joe';

# BUG:  Updating a datetime column in a multitable update using
# a timezone conversion fails due to permissions.  (Note that this
# is virtually the same as the statement that ran successfully above.)
# ERROR 1143 (42000): UPDATE command denied 
# to user 'agg'@'localhost' for column 'START_TIME' in table 'MEALS'

UPDATE MEALS M JOIN CHEFS C ON (M.CHEF_ID = C.CHEF_ID) 
SET M.START_TIME = CONVERT_TZ('2005-01-01 11:00', 'EST', 'EST') 
WHERE C.NAME='Joe';
[18 Apr 2005 22:17] MySQL Verification Team
Verified with latest BK.
[17 Jun 2005 15:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26124
[29 Jun 2005 10:43] Magnus BlÄudd
Approved
[15 Jul 2005 23:29] Jim Winstead
Fixed in 4.1.14, and will be fixed in 5.0.10.
[21 Jul 2005 4:43] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 4.1.4 and 5.0.10 changelogs. Closed.