DESC default column presenting incorrect value

[Ronald Bradford]

drizzle> desc stat;
+-------------+-----------+-------+---------+-----------------+-----------+
| Field | Type | Null | Default | Default is NULL | On Update |
+-------------+-----------+-------+---------+-----------------+-----------+
| stat_id | INTEGER | FALSE | | FALSE | |
| client_id | INTEGER | FALSE | | FALSE | |
| instance_id | INTEGER | FALSE | | FALSE | |
| name | VARCHAR | FALSE | | FALSE | |
| value | BIGINT | FALSE | | FALSE | |
| created | TIMESTAMP | FALSE | NOW() | FALSE | |
| generated | TIMESTAMP | TRUE | NOW() | TRUE | |
+-------------+-----------+-------+---------+-----------------+-----------+
7 rows in set (0 sec)

The Default for the generated column is not correct, it shows a value of NOW() which appears to maybe a copy of the previous value.

Table definition there is not default, and loading and retrieving data indicates the definition and operation as expected, the DESC output being misleading.

CREATE TABLE stat (
  stat_id INT NOT NULL AUTO_INCREMENT,
  client_id INT NOT NULL,
  instance_id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  value BIGINT NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  generated TIMESTAMP NULL,
  PRIMARY KEY(stat_id),
  INDEX (instance_id,client_id),
  UNIQUE INDEX (name,created)
) ENGINE=InnoDB;

INSERT INTO stat(client_id,instance_id,name,value,created)
SELECT 1,1,variable_name,variable_value,NOW() FROM data_dictionary.global_status
UNION
SELECT 1,1,variable_name,variable_value,NOW() FROM data_dictionary.global_statements;

drizzle> select * from stat limit 1;
+---------+-----------+-------------+-----------------+-------+---------------------+-----------+
| stat_id | client_id | instance_id | name | value | created | generated |
+---------+-----------+-------------+-----------------+-------+---------------------+-----------+
| 1 | 1 | 1 | Aborted_clients | 6 | 2010-03-11 22:27:34 | NULL |
+---------+-----------+-------------+-----------------+-------+---------------------+-----------+
1 row in set (0 sec)

[Brian Aker (brianaker)]

Hi!

The table definition done by "SHOW CREATE" is not the same code path
as what is used for "desc". I need to dig into this though to see
what is at fault. It could be the table port, or we could be doing an
explicit default of now() on the field.

Cheers,
 -Brian

On Mar 11, 2010, at 2:33 PM, Ronald Bradford wrote:

> Public bug reported:
>
> drizzle> desc stat;
> +-------------+-----------+-------+---------+-----------------
> +-----------+
> | Field | Type | Null | Default | Default is NULL | On
> Update |
> +-------------+-----------+-------+---------+-----------------
> +-----------+
> | stat_id | INTEGER | FALSE | | FALSE
> | |
> | client_id | INTEGER | FALSE | | FALSE
> | |
> | instance_id | INTEGER | FALSE | | FALSE
> | |
> | name | VARCHAR | FALSE | | FALSE
> | |
> | value | BIGINT | FALSE | | FALSE
> | |
> | created | TIMESTAMP | FALSE | NOW() | FALSE
> | |
> | generated | TIMESTAMP | TRUE | NOW() | TRUE
> | |
> +-------------+-----------+-------+---------+-----------------
> +-----------+
> 7 rows in set (0 sec)
>
> The Default for the generated column is not correct, it shows a
> value of
> NOW() which appears to maybe a copy of the previous value.
>
> Table definition there is not default, and loading and retrieving data
> indicates the definition and operation as expected, the DESC output
> being misleading.
>
> CREATE TABLE stat (
> stat_id INT NOT NULL AUTO_INCREMENT,
> client_id INT NOT NULL,
> instance_id INT NOT NULL,
> name VARCHAR(50) NOT NULL,
> value BIGINT NOT NULL,
> created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
> generated TIMESTAMP NULL,
> PRIMARY KEY(stat_id),
> INDEX (instance_id,client_id),
> UNIQUE INDEX (name,created)
> ) ENGINE=InnoDB;
>
> INSERT INTO stat(client_id,instance_id,name,value,created)
> SELECT 1,1,variable_name,variable_value,NOW() FROM
> data_dictionary.global_status
> UNION
> SELECT 1,1,variable_name,variable_value,NOW() FROM
> data_dictionary.global_statements;
>
> drizzle> select * from stat limit 1;
> +---------+-----------+-------------+-----------------+-------
> +---------------------+-----------+
> | stat_id | client_id | instance_id | name | value |
> created | generated |
> +---------+-----------+-------------+-----------------+-------
> +---------------------+-----------+
> | 1 | 1 | 1 | Aborted_clients | 6 |
> 2010-03-11 22:27:34 | NULL |
> +---------+-----------+-------------+-----------------+-------
> +---------------------+-----------+
> 1 row in set (0 sec)
>
> ** Affects: drizzle
> Importance: Undecided
> Status: New
>
> ** Description changed:
>
> drizzle> desc stat;
> +-------------+-----------+-------+---------+-----------------
> +-----------+
> | Field | Type | Null | Default | Default is NULL | On
> Update |
> +-------------+-----------+-------+---------+----...

[Stewart Smith (stewart)]

I'm pretty sure this is fixed and really a duplicate of a bug I filed ages ago (although it shows up a bit differently)

[Patrick Crews (patrick-crews)]

As Stewart noted above, this is no longer happening / has been fixed. Closing this bug out.

[Patrick Crews (patrick-crews)]

Fixed via a duplicate / slightly different bug. No longer able to duplicate this issue.