MySQL Bugs: #15313: Dropping Default Date Corrupts Field

Bug #15313	Dropping Default Date Corrupts Field
Submitted:	29 Nov 2005 13:38	Modified:	30 Dec 2005 13:57
Reporter:	Trevor White	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15	OS:	Other (Sco Unix)
Assigned to:		CPU Architecture:	Any

Description:
If you attempt to drop the default value for a TIMESTAMP field it corrupt it, causing the MySQL Server to crash and restart evey time you access it.

This was caused when a shell script of ours detected a change to the table create syntax between MySQL 4 and 5 and issued a DROP DEFAULT command on a TIMESTAMP field.

How to repeat:
Create a TIMESTAMP field and issue a ALTER TABLE DROP DEFAULT command to it.  

Now if you attempt to do anything with that field or that table the MySQL engine crashes.  Re-saving the table defination in MyCC sometimes fixes the problem, but I've found normally the table has to be dropped and re-created.

Suggested fix:
Don't allow the user to DROP the default on a TIMESTAMP field.

Thank you for a problem report. Sorry, but I was not able to repeat the problem you described with 5.0.17-BK on Linux:

mysql> create table tt1 (c1 int, c2 timestamp default '2005-01-01 01:00:00');
Query OK, 0 rows affected (0,00 sec)

mysql> insert into tt1 (c1) values(1);
Query OK, 1 row affected (0,01 sec)

mysql> select * from tt1;
+------+---------------------+
| c1   | c2                  |
+------+---------------------+
|    1 | 2005-01-01 01:00:00 |
+------+---------------------+
1 row in set (0,00 sec)

mysql> alter table tt1 alter column c2 drop default;
Query OK, 0 rows affected (0,01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tt1;
+------+---------------------+
| c1   | c2                  |
+------+---------------------+
|    1 | 2005-01-01 01:00:00 |
+------+---------------------+
1 row in set (0,00 sec)

mysql> insert into tt1 values(1, '2005-01-02 02:00:00');
Query OK, 1 row affected (0,00 sec)

mysql> select * from tt1;
+------+---------------------+
| c1   | c2                  |
+------+---------------------+
|    1 | 2005-01-01 01:00:00 |
|    1 | 2005-01-02 02:00:00 |
+------+---------------------+
2 rows in set (0,00 sec)

mysql> show create table tt1;
+-------+--------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                             |
+-------+--------------------------------------------------------------------------------------------------------------------+
| tt1   | CREATE TABLE `tt1` (
  `c1` int(11) default NULL,
  `c2` timestamp NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

So, please, try po perform the same actions on your server and inform about the results. Any ideas on what shell I do to repeat the problem you described are welcomed too.

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".