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:
None 
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
Description:
Procedure does not works any more after upgrading from 5.0.11 to 5.0.12. The SELECT fulldate INTO dt_last FROM `_master` ORDER BY Fulldate DESC LIMIT 1;
 returns NULL instead of the true value.

How to repeat:
1. Create table _master
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;
INSERT INTO _master VALUES ('2000-01-01 00:00:00','2000-01-01 01:00:00','20001','200001','20001' );

2. Create procedure FillMasterTable
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 LIMIT 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;
[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.