DESC default column presenting incorrect value
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Drizzle |
Fix Released
|
Low
|
Patrick Crews |
Bug Description
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_
UNIQUE INDEX (name,created)
) ENGINE=InnoDB;
INSERT INTO stat(client_
SELECT 1,1,variable_
UNION
SELECT 1,1,variable_
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)
description: | updated |
Changed in drizzle: | |
importance: | Undecided → Low |
status: | New → Triaged |
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: ------- +------ -----+- ------+ ------- --+---- ------- ------ ------- +------ -----+- ------+ ------- --+---- ------- ------ ------- +------ -----+- ------+ ------- --+---- ------- ------ id,client_ id), id,instance_ id,name, value,created) name,variable_ value,NOW( ) FROM .global_ status name,variable_ value,NOW( ) FROM .global_ statements; ---+--- ------- -+----- ------- -+----- ------- -----+- ------ ------- ------- -+----- ------+ ---+--- ------- -+----- ------- -+----- ------- -----+- ------ ------- ------- -+----- ------+ ---+--- ------- -+----- ------- -+----- ------- -----+- ------ ------- ------- -+----- ------+ ------- +------ -----+- ------+ ------- --+---- ------- ------ ------- +------ -----+- ------+ ------- --+---- ...
>
> 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_
> UNIQUE INDEX (name,created)
> ) ENGINE=InnoDB;
>
> INSERT INTO stat(client_
> SELECT 1,1,variable_
> data_dictionary
> UNION
> SELECT 1,1,variable_
> data_dictionary
>
> 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 |
> +------