Bug #12986 | Procedure does not works after upgrading from 5.0.11 to 5.0.12 | ||
---|---|---|---|
Submitted: | 5 Sep 2005 10:46 | Modified: | 5 Sep 2005 13:33 |
Reporter: | Paolo Saudin | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.12 | OS: | Windows (WinXP SP2) |
Assigned to: | CPU Architecture: | Any |
[5 Sep 2005 10:46]
Paolo Saudin
[5 Sep 2005 11:38]
Valeriy Kravchuk
So, procedure worked in 5.0.11 and now does not work for you on 5.0.12? Or it was created in 5.0.11, worked there, then you upgraded to 5.0.12 and now it does not work? It is not clear from your description. I was unable to repeat on fresh installation of 5.0.12 on XP: mysql> select version(); +----------------+ | version() | +----------------+ | 5.0.12-beta-nt | +----------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE _master ( -> `Fulldate` datetime NOT NULL default '2000-01-01 00:00:00', -> `yyyymmddhhmm` char(19) default NULL, /* 2000-01-01 00:00:00 fulldate */ -> `yyyyq` char(5) default NULL, /* 2000 1 quarter */ -> `yyyyww` char(6) default NULL, /* 2000 05 week */ -> `yyyyw` char(5) default NULL, /* 2000 4 week-day */ -> PRIMARY KEY (fulldate) -> ) ENGINE=MyIsam DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.11 sec) mysql> delimiter // mysql> CREATE PROCEDURE FillMasterTable() -> NOT DETERMINISTIC -> SQL SECURITY DEFINER -> COMMENT 'Fill the _master table' -> BEGIN -> DECLARE dt_now DATETIME; -> DECLARE dt_last DATETIME; -> DECLARE max_loops INTEGER; -> DECLARE v INTEGER; -> -- gets the last update -> SELECT fulldate INTO dt_last FROM `_master` ORDER BY Fulldate DESC LI MIT 1; -> -- gets the gmt date time -> SET dt_now = DATE_FORMAT(UTC_TIMESTAMP(),'%Y-%m-%d %H:00:00'); -> SET max_loops = 10000; -> SET v = 0; -> WHILE dt_last < dt_now AND v < max_loops DO -> SET v = v + 1; -> SET dt_last = DATE_ADD(dt_last, INTERVAL 1 HOUR); -> INSERT INTO `_master` VALUES ( dt_last, -> (SELECT concat(LEFT(dt_last, 11), LPAD(MID(dt_last,12,2)+1,2,0) -> ,RIGHT(dt_last,6))), -> (SELECT CONCAT(YEAR(dt_last), QUARTER(dt_last))), -> (SELECT CONCAT(YEAR(dt_last), RIGHT(100 + WEEK(dt_last,1),2))), -> (SELECT CONCAT(YEAR(dt_last), WEEKDAY(dt_last)))); -> END WHILE; -> END; -> // Query OK, 0 rows affected (0.04 sec) mysql> delimiter ; mysql> call FillMasterTable(); Query OK, 1 row affected (4.35 sec) mysql> select count(*) from `_master`; +----------+ | count(*) | +----------+ | 10001 | +----------+ 1 row in set (0.00 sec) mysql> select * from `_master` limit 10; +---------------------+---------------------+-------+--------+-------+ | Fulldate | yyyymmddhhmm | yyyyq | yyyyww | yyyyw | +---------------------+---------------------+-------+--------+-------+ | 2000-01-01 00:00:00 | 2000-01-01 01:00:00 | 20001 | 200001 | 20001 | | 2000-01-01 01:00:00 | 2000-01-01 02:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 02:00:00 | 2000-01-01 03:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 03:00:00 | 2000-01-01 04:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 04:00:00 | 2000-01-01 05:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 05:00:00 | 2000-01-01 06:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 06:00:00 | 2000-01-01 07:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 07:00:00 | 2000-01-01 08:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 08:00:00 | 2000-01-01 09:00:00 | 20001 | 200000 | 20005 | | 2000-01-01 09:00:00 | 2000-01-01 10:00:00 | 20001 | 200000 | 20005 | +---------------------+---------------------+-------+--------+-------+ 10 rows in set (0.00 sec) So, looks like the procedure really inserted all the records you wanted. What do you mean by "does not work"?
[5 Sep 2005 12:22]
Paolo Saudin
I added at the procedure end the statement 'SELECT dt_last;' and the return value was NULL. I recreated the table and tried again and now all is ok. The table is filled correctly.
[5 Sep 2005 13:33]
Valeriy Kravchuk
So, is there any bug really? I recreated the table, inserted 1 row (I had forgotten about it first time), and recreated procedure like the following: mysql> CREATE PROCEDURE FillMasterTable() -> NOT DETERMINISTIC -> SQL SECURITY DEFINER -> COMMENT 'Fill the _master table' -> BEGIN -> DECLARE dt_now DATETIME; -> DECLARE dt_last DATETIME; -> DECLARE max_loops INTEGER; -> DECLARE v INTEGER; -> -- gets the last update -> SELECT fulldate INTO dt_last FROM `_master` ORDER BY Fulldate DESC LI MIT 1; -> -- gets the gmt date time -> SET dt_now = DATE_FORMAT(UTC_TIMESTAMP(),'%Y-%m-%d %H:00:00'); -> SET max_loops = 10; -> SET v = 0; -> WHILE dt_last < dt_now AND v < max_loops DO -> SET v = v + 1; -> SET dt_last = DATE_ADD(dt_last, INTERVAL 1 HOUR); -> INSERT INTO `_master` VALUES ( dt_last, -> (SELECT concat(LEFT(dt_last, 11), LPAD(MID(dt_last,12,2)+1,2,0) -> ,RIGHT(dt_last,6))), -> (SELECT CONCAT(YEAR(dt_last), QUARTER(dt_last))), -> (SELECT CONCAT(YEAR(dt_last), RIGHT(100 + WEEK(dt_last,1),2))), -> (SELECT CONCAT(YEAR(dt_last), WEEKDAY(dt_last)))); -> SELECT dt_last; -> END WHILE; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call FillMasterTable(); +---------------------+ | dt_last | +---------------------+ | 2000-01-01 01:00:00 | +---------------------+ 1 row in set (0.01 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 02:00:00 | +---------------------+ 1 row in set (0.05 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 03:00:00 | +---------------------+ 1 row in set (0.08 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 04:00:00 | +---------------------+ 1 row in set (0.10 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 05:00:00 | +---------------------+ 1 row in set (0.10 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 06:00:00 | +---------------------+ 1 row in set (0.10 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 07:00:00 | +---------------------+ 1 row in set (0.10 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 08:00:00 | +---------------------+ 1 row in set (0.10 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 09:00:00 | +---------------------+ 1 row in set (0.11 sec) +---------------------+ | dt_last | +---------------------+ | 2000-01-01 10:00:00 | +---------------------+ 1 row in set (0.11 sec) Query OK, 0 rows affected (0.11 sec) Please, note that dt_last value is selected on each iteration. And it is really increased as expected. So I mark this as "Can't repeat". You may reopen the bug report, but please provide the test case, the description of the results you expected, and real results you got, just as I did.