MySQL Bugs: #17438: alter table alter column set default corrupts tables

Bug #17438	alter table alter column set default corrupts tables
Submitted:	16 Feb 2006 0:32	Modified:	17 Feb 2006 16:45
Reporter:	Colin Kuskie	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Linux (CentOS 4.2)
Assigned to:		CPU Architecture:	Any

Description:
We have a table in an application with this definition:

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| DataForm_fieldId | varchar(22)  |      | PRI |         |       |
| sequenceNumber   | int(11)      |      |     | 0       |       |
| name             | varchar(255) |      |     |         |       |
| status           | varchar(35)  | YES  |     | NULL    |       |
| type             | varchar(30)  |      |     |         |       |
| possibleValues   | text         | YES  |     | NULL    |       |
| defaultValue     | text         | YES  |     | NULL    |       |
| width            | int(11)      | YES  |     | NULL    |       |
| subtext          | mediumtext   | YES  |     | NULL    |       |
| rows             | int(11)      | YES  |     | NULL    |       |
| isMailField      | int(11)      |      |     | 0       |       |
| label            | varchar(255) | YES  |     | NULL    |       |
| DataForm_tabId   | varchar(22)  |      |     | 0       |       |
| vertical         | smallint(1)  | YES  |     | 1       |       |
| extras           | varchar(128) | YES  |     | NULL    |       |
| assetId          | varchar(22)  |      |     |         |       |
+------------------+--------------+------+-----+---------+-------+

It was created in Mysql 4.1

This table was then upgraded to Mysql 5.0.17 and then this statement
was run against it:

alter table DataForm_field alter DataForm_tabId set default 0;

After that statement, many columns had all of their data corrupted.
Some columns were wiped out, some were altered, many were of an
incorrect width.  I'll paste in some sample data below.

We then tried creating the table in Mysql 5.0.17 and running the
same statement, and nothing bad happened.

How to repeat:
see instructions above

Thank you for a problem repoirt. How you upgraded that table? Have you dumped it in 4.1.x and restored in 5.0.18? If no, it can be a duplicate of bug #17001, already verified.

Valeriy, thank you for your prompt response.  Our bug is very similar to 17001, except there's no database crash.  The server stays up the whole time.

We're very anxious to know if there's a way to recover the data from such a corrupted table.  Do you think it is possible?

So, I'll mark this report as a duplicate of bug #17001 and add a coment there.

> We're very anxious to know if there's a way to recover the data from such a
> corrupted table.  Do you think it is possible?

I don't know any universal way. You need a dump, backup, or at least binlog enabled to do something. What storage engine was used for that table?

Anyway, this is not a good place to discuss the recovery issue. Use our forums, mailing lists or commercial support.

The table was MyISAM.  I'll place further comments on bug #17001