| Bug #11081 | Using a CONVERT_TZ function in a stored function or trigger fails | ||
|---|---|---|---|
| Submitted: | 3 Jun 2005 18:34 | Modified: | 9 May 2006 19:00 |
| Reporter: | Mark Smith | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.21-BK, 5.0.6 | OS: | Windows (Win 2K3 Server) |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[3 Jun 2005 20:03]
MySQL Verification Team
Verified on Linux and Windows with server 5.0.7 BK. Indeed the version 5.0.4 on Windows not presents this behavior.
[31 Oct 2005 19:24]
Justin Plock
DELIMITER // DROP TABLE IF EXISTS tz_test; // CREATE TABLE tz_test (normal_time INT(11) UNSIGNED, gmt_time VARCHAR(100)); // DROP TRIGGER BI_tztest_fer; // CREATE TRIGGER BI_tztest_fer BEFORE INSERT ON tz_test FOR EACH ROW BEGIN SET NEW.gmt_time = CONVERT_TZ(FROM_UNIXTIME(NEW.normal_time), 'America/Rainy_River', 'GMT'); END; // INSERT INTO tz_test (normal_time) VALUES (UNIX_TIMESTAMP()); // ERROR 1146 (42S02): Table '.' doesn't exist This also does not work on Win2K in 5.0.15
[22 Dec 2005 2:38]
Justin Plock
Still broken on Win32 5.0.17
[11 Apr 2006 15:39]
Valeriy Kravchuk
The last test case (posted at [31 Oct 2005 20:24] by Justin Plock) is still repeatable on 5.0.21-BK (ChangeSet@1.2148.1.1, 2006-04-10) on Linux.
[19 Apr 2006 20:00]
Dmitry Lenev
Note that with 5.0.21 problem with stored procedures is not repeatable. It exists though for stored functions and triggers (or stored procedures which are called from them.
[19 Apr 2006 21:16]
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/commits/5175
[20 Apr 2006 18:08]
Konstantin Osipov
Approved by email with minor comments.
[24 Apr 2006 14:58]
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/commits/5410
[2 May 2006 22:51]
Dmitry Lenev
Fixed in 5.0.22 and 5.1.10
[9 May 2006 19:00]
Paul DuBois
Noted in 5.0.22, 5.1.10 changelogs. Use of <literal>CONVERT_TZ()</literal> in a stored function or trigger (or in a stored procedure called from a stored function or trigger) caused an error. (Bug #11081)
Description: Simple stored procedures that worked in 5.0.4 fail in 5.0.6. The problem has been isolated from my procedures to be the CONVERT_TZ function. The SQL when used outside a stored procedure works fine. However, executing the SQL through a stored procedure gives the following error: ERROR 1146 (42S02): Table '.' doesn't exist. I have provided a sample table and data in order to duplicate the problem. I have implemented the timezone support from the tables supplied in the timezone-2004e.zip because of the lack of native Windows support. TEE OUTPUT (From my sample provided in 'How to repeat': root@sbsrp01qahq-psc_trp~> DROP PROCEDURE IF EXISTS test; Query OK, 0 rows affected (0.05 sec) root@sbsrp01qahq-psc_trp~> DROP TEMPORARY TABLE IF EXISTS test_date; Query OK, 0 rows affected (0.09 sec) root@sbsrp01qahq-psc_trp~> root@sbsrp01qahq-psc_trp~> CREATE TEMPORARY TABLE test_date (int_dt INT UNSIGNED NOT NULL default 0) ENGINE=MyISAM; Query OK, 0 rows affected (0.08 sec) root@sbsrp01qahq-psc_trp~> root@sbsrp01qahq-psc_trp~> INSERT INTO test_date (int_dt) -> VALUES (1117758889),(1117757925),(1117761679),(1117795287),(1117741186),(1117734772),(1117791602),(1117754510),(1117745928),(1117736569); Query OK, 10 rows affected (0.03 sec) Records: 10 Duplicates: 0 Warnings: 0 root@sbsrp01qahq-psc_trp~> root@sbsrp01qahq-psc_trp~> DELIMITER // root@sbsrp01qahq-psc_trp~> root@sbsrp01qahq-psc_trp~> CREATE PROCEDURE test() -> SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC -> FROM test_date AS t; -> // Query OK, 0 rows affected (0.05 sec) root@sbsrp01qahq-psc_trp~> root@sbsrp01qahq-psc_trp~> DELIMITER ; root@sbsrp01qahq-psc_trp~> root@sbsrp01qahq-psc_trp~> SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC -> FROM test_date AS t; +---------------------+ | Time_UTC | +---------------------+ | 2005-06-03 00:34:49 | | 2005-06-03 00:18:45 | | 2005-06-03 01:21:19 | | 2005-06-03 10:41:27 | | 2005-06-02 19:39:46 | | 2005-06-02 17:52:52 | | 2005-06-03 09:40:02 | | 2005-06-02 23:21:50 | | 2005-06-02 20:58:48 | | 2005-06-02 18:22:49 | +---------------------+ 10 rows in set (0.03 sec) root@sbsrp01qahq-psc_trp~> root@sbsrp01qahq-psc_trp~> call test(); ERROR 1146 (42S02): Table '.' doesn't exist How to repeat: ############## SAMPLE TEST ################ DROP PROCEDURE IF EXISTS test; DROP TEMPORARY TABLE IF EXISTS test_date; CREATE TEMPORARY TABLE test_date (int_dt INT UNSIGNED NOT NULL default 0) ENGINE=MyISAM; INSERT INTO test_date (int_dt) VALUES (1117758889),(1117757925),(1117761679),(1117795287),(1117741186),(1117734772),(1117791602),(1117754510),(1117745928),(1117736569); DELIMITER // CREATE PROCEDURE test() SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC FROM test_date AS t; // DELIMITER ; SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC FROM test_date AS t; call test(); ############### END TEST ###################