Bug #53441 Cursor does not fetch ts value from int column

• Submitted: 5 May 2010 15:45
• Modified: 6 May 2010 10:46
• Reporter: Stefan Gottschalk
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.1.42
• OS: Any
• CPU Architecture: Any
• Tags: cursor, INT, PROCEDURE, timestamp

[5 May 2010 15:45] Stefan Gottschalk

Description:
Hello,

i have the following problem with a Cursor:

###########################################################
### The test-environment:
###########################################################

### the table which is selected by the cursor
CREATE TABLE IF NOT EXISTS `act_cache` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

### some test data
INSERT INTO `act_cache` (`id`, `ts`) VALUES
(1, 1273072721),
(2, 1273072722),
(3, 1273072723);

### this table is only for writing debug data in it from the testProc()
CREATE TABLE IF NOT EXISTS `log` (
  `ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `valueInt` int(11) DEFAULT NULL,
  `valueDouble` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### the test procedure
DELIMITER $$

CREATE PROCEDURE `testProc` ()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE act_cache_id INT;
    DECLARE ts INT;
    DECLARE cur1 CURSOR FOR SELECT id, ts FROM act_cache;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    
    INSERT INTO log (`name`) VALUE ("testProc.start");
    
    OPEN cur1;
    
    REPEAT
        FETCH cur1 INTO act_cache_id, ts;
        INSERT INTO log (`name`, `value`) VALUE ("testProc.act_cache_id", act_cache_id);
        INSERT INTO log (`name`, `value`) VALUE ("testProc.ts", ts);
        INSERT INTO log (`name`, `value`) VALUE ("testProc.done", done);
        IF NOT done THEN
            INSERT INTO log (`name`) VALUE ("testProc.doSomething");
        END IF;
    UNTIL done END REPEAT;

    CLOSE cur1;
    INSERT INTO log (`name`) VALUE ("testProc.end");
    
END$$

###########################################################
### The problem:
###########################################################

The testProc() should walk through the table `act_cache` and give back the fields `id` and `ts` which are used for further processing.
When i execute

CALL testProc();

the procedure is succesfully executed, but the cursor only fetches the `id` field. The `ts` field is always NULL. Why?
The log content shows the trace of the procedure which looks like this:
----------------------------------------------------------
| TS			| NAME			  | VALUE|
----------------------------------------------------------
| '2010-05-05 17:33:01' | 'testProc.start' 	  | NULL |
| '2010-05-05 17:33:01' | 'testProc.act_cache_id' |    1 |
| '2010-05-05 17:33:01' | 'testProc.ts' 	  | NULL |
| '2010-05-05 17:33:01' | 'testProc.done' 	  |    0 |
| '2010-05-05 17:33:01' | 'testProc.doSomething'  | NULL |
| '2010-05-05 17:33:01' | 'testProc.act_cache_id' |    2 |
| '2010-05-05 17:33:01' | 'testProc.ts' 	  | NULL |
| '2010-05-05 17:33:01' | 'testProc.done' 	  |    0 |
| '2010-05-05 17:33:01' | 'testProc.doSomething'  | NULL |
| '2010-05-05 17:33:01' | 'testProc.act_cache_id' |    3 |
| '2010-05-05 17:33:01' | 'testProc.ts' 	  | NULL |
| '2010-05-05 17:33:01' | 'testProc.done' 	  |    0 |
| '2010-05-05 17:33:01' | 'testProc.doSomething'  | NULL |
| '2010-05-05 17:33:01' | 'testProc.act_cache_id' |    3 |
| '2010-05-05 17:33:01' | 'testProc.ts' 	  | NULL |
| '2010-05-05 17:33:01' | 'testProc.done' 	  |    1 |
| '2010-05-05 17:33:01' | 'testProc.end' 	  | NULL |
----------------------------------------------------------

Thank you for your help!
Stefan Gottschalk

How to repeat:
See description

[5 May 2010 15:54] Valeriy Kravchuk

Your test case is NOT entirely correct (wrong column name is used for the log table at least):

mysql> delimiter ;
mysql> CALL testProc();
ERROR 1054 (42S22): Unknown column 'value' in 'field list'

So, please, check if the problem is repeatable with a newer version of MySQL server, 5.1.46, and if it is, please, send the corrected test case.

[5 May 2010 16:23] Stefan Gottschalk

Thank you Valeriy, you are right.

The right `log`-table is as follows:

CREATE TABLE IF NOT EXISTS `log` (
  `ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[5 May 2010 17:49] Sveta Smirnova

Thank you for the feedback.

This is duplicate of bug #5967.

[6 May 2010 10:46] Stefan Gottschalk

Hello Sveta,

thank you for your reply. You are completely right. After prepending all local variables by a "_" the routine works as expected.

Greetings
Stefan Gottschalk