MySQL Bugs: #6076: A colimn name of 'transition_time' causes bin/mysql update to fail

Bug #6076	A colimn name of 'transition_time' causes bin/mysql update to fail
Submitted:	13 Oct 2004 22:27	Modified:	14 Oct 2004 14:11
Reporter:	Robert Bankay	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Spar Sol 9.4
Assigned to:		CPU Architecture:	Any

Description:
Have had problems using interactive bin/mysql to update a column named 'transition_time' with mysql 4.0.13.  This problem does not occur with any API we use for programming.   The column is initially created will NULL and the update value is always 2147483647, no matter what value we try to assign.

A work around is to drop the column and create a new column with a different name like 'transitionx_timex'.  This then takes all updates.

Here is some supporting output:

Database changed
mysql> explain workunit;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      |      | PRI | NULL    | auto_increment |
| create_time          | int(11)      |      |     | 0       |                |
| appid                | int(11)      |      | MUL | 0       |                |
| name                 | varchar(254) |      | UNI |         |                |
| xml_doc              | blob         | YES  |     | NULL    |                |
| batch                | int(11)      |      |     | 0       |                |
| rsc_fpops_est        | double       |      |     | 0       |                |
| rsc_fpops_bound      | double       |      |     | 0       |                |
| rsc_memory_bound     | double       |      |     | 0       |                |
| rsc_disk_bound       | double       |      |     | 0       |                |
| need_validate        | smallint(6)  |      |     | 0       |                |
| canonical_resultid   | int(11)      |      |     | 0       |                |
| canonical_credit     | double       |      |     | 0       |                |
| transition_time      | int(11)      |      | MUL | 0       |                |***
| delay_bound          | int(11)      |      |     | 0       |                |
| error_mask           | int(11)      |      |     | 0       |                |
| file_delete_state    | int(11)      |      | MUL | 0       |                |
| assimilate_state     | int(11)      |      |     | 0       |                |
| workseq_next         | int(11)      |      |     | 0       |                |
| opaque               | double       |      |     | 0       |                |
| min_quorum           | int(11)      |      |     | 0       |                |
| target_nresults      | int(11)      |      |     | 0       |                |
| max_error_results    | int(11)      |      |     | 0       |                |
| max_total_results    | int(11)      |      |     | 0       |                |
| max_success_results  | int(11)      |      |     | 0       |                |
| result_template_file | varchar(63)  |      |     |         |                |
+----------------------+--------------+------+-----+---------+----------------+
26 rows in set (0.00 sec)

mysql> select id, transition_time from workunit;
+----+-----------------+
| id | transition_time |
+----+-----------------+
|  1 |      2147483647 |
|  2 |      2147483647 |
|  3 |      2147483647 |
|  4 |      2147483647 |
|  5 |      2147483647 |
|  6 |      2147483647 |
|  7 |      2147483647 |
|  8 |      2147483647 |
|  9 |      2147483647 |
| 10 |      2147483647 |
+----+-----------------+
10 rows in set (0.00 sec)

mysql> alter table workunit drop transition_time;              
Query OK, 10 rows affected (0.18 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> alter table workunit add transitionx_timex int default 0 after canonical_credit;
Query OK, 10 rows affected (0.18 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> update workunit set transitionx_timex = id;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> select id, transitionx_timex from workunit;
+----+-------------------+
| id | transitionx_timex |
+----+-------------------+
|  1 |                 1 |
|  2 |                 2 |
|  3 |                 3 |
|  4 |                 4 |
|  5 |                 5 |
|  6 |                 6 |
|  7 |                 7 |
|  8 |                 8 |
|  9 |                 9 |
| 10 |                10 |
+----+-------------------+
10 rows in set (0.00 sec)

How to repeat:
Create the table and insert a few rows.  

The initial value will be NULL or 0 depending on DEFAULT vaule setting.

update workunit set transition_time = 1;

(all the rows will contain the value 2147483647..)

alter table workunit drop transition_time;
alter table workunit add transitionx_timex int default 0 after canonical_credit;
update workunit set transitionx_timex = id;
select id, transitionx_timex from workunit;

(then all the column values will have correctly assigned values.

Suggested fix:
Fix token parsing in bin/mysql client parsing.

Hi,

Thank you for the report, but I wasn't able to repeat it using v4.0.22:

mysql> select id, transition_time from t1;
+----+-----------------+
| id | transition_time |
+----+-----------------+
|  1 |               1 |
|  2 |               1 |
|  3 |               1 |
|  4 |               1 |
|  5 |               1 |
|  6 |               1 |
|  7 |               1 |
+----+-----------------+
7 rows in set (0.00 sec)

mysql> update t1 set  transition_time=id;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 7  Changed: 6  Warnings: 0

mysql> select id, transition_time from t1;
+----+-----------------+
| id | transition_time |
+----+-----------------+
|  1 |               1 |
|  2 |               2 |
|  3 |               3 |
|  4 |               4 |
|  5 |               5 |
|  6 |               6 |
|  7 |               7 |
+----+-----------------+
7 rows in set (0.01 sec)

FYI Some additional features of this bug:  If the transtion_time value is at 2147483647, it can then be updated and set to a value around 1,110,000,000; one can then add to the column and it will update correctly, however if an attempt is made to decrement the value to below 1,100,000,000 then it reverts to 2147483647.
Have user alter/modify to change the type to double, the value remains and will do correct arithmetics. THEN using alter/modify and setting the type back to int causes the anomolous behavior to stop.  The int column will then update correctly from 2147483647 after the 2 alter/modifies.

We plan to upgrade to 4.0.22 next week.

Bob Bankay