MySQL Bugs: #1177: ALTER TABLE MODIFY timestamp column to datetime type

Bug #1177	ALTER TABLE MODIFY timestamp column to datetime type
Submitted:	2 Sep 2003 3:21	Modified:	16 Mar 2006 13:28
Reporter:	Adam Hardy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (Linux 2.4.20)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
I found that the following commands fails with the error:

ERROR 2013 at line 3: Lost connection to MySQL server during query

ALTER TABLE item MODIFY date_deleted DATETIME;

(the column was a TIMESTAMP). However it did work with:

ALTER TABLE item CHANGE date_deleted date_deleted DATETIME;

Just for the record. Otherwise great DB! Love it. 

How to repeat:
Use this table:

CREATE TABLE category (
       category_id INT PRIMARY KEY,
       owner_id INT,
       date_deleted TIMESTAMP,
       title VARCHAR(255)
);
ALTER TABLE category TYPE=InnoDB;

Put some a few rows with values in and then run the ALTER TABLE ... MODIFY as above. 

Suggested fix:
Sorry, don't know the internal workings of mySQL. The workaround above is fine though.

sorry got the version wrong

Can you provide us with a full repeatable test case? Your current bug
description contains contradicting info - for example, the ALTER TABLE
is done to an "item" table, whereas the CREATE TABLE statement creates
a "category" table.

If you can create a .sql file, that, piped to the mysql client, will always
produce a crash, this will be the best.

Here are the results of my attempt to reproduce the problem (done to
the latest 4.1 development tree, so if you try exactly the same commands
and it crashes on your machine, then it's most probably fixed for 4.1.1):

mysql> CREATE TABLE category (
    ->        category_id INT PRIMARY KEY,
    ->        owner_id INT,
    ->        date_deleted TIMESTAMP,
    ->        title VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE category TYPE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into category values 
(1,1,null,'blah'),(2,1,null,'blah'),(3,1,null,'blah'),(4,1,null,'blah'),(5,1,null,'blah'),(6,1,null,'blah'),(7,1,n
ull,'blah'),(8,1,null,'blah'),(9,1,null,'blah'),(10,1,null,'blah'),(11,1,null,'blah'),(12,1,null,'blah'),(13,1,n
ull,'blah'),(14,1,null,'blah'),(15,1,null,'blah'),(16,1,null,'blah'),(17,1,null,'blah'),(18,1,null,'blah'),(19,1
,null,'blah'),(20,1,null,'blah');
Query OK, 20 rows affected (0.01 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE category MODIFY date_deleted DATETIME;                       
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

I tried to write a small script to reproduce the error for you, but now it is not happening. Sorry. Unreproducible. I guess you can scrub this one.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".