How to alter MySQL table without losing data?

Question

In my application, I make some changes and upload them to a testing server. Because I have no access to the server database I run ALTER commands to make changes on it.

Using a method I ran the following command on server:

ALTER TABLE `blahblahtable` ADD COLUMN `newcolumn` INT(12) NOT NULL

After that, I found that the all the data of the table has been removed. Now the table is blank.

So I need to alter the table without removing his data. Is there any way to do that?

Answer 1

Your question is quite obvious. You're adding a new column to the table, and setting it to NOT NULL.
To make things clearer, I will explain the reaction of the server when you run the command:

1. You add a new column, so every row of the table has to set a value for that column.

2. As you don't declare any default value, all the rows set null for this new column.

3. The server notices that the rows of the table have a null value on a column that doesn't allow nulls. This is illegal.

4. To solve the conflict, the invalid rows are deleted.

There are some good fixes for this issue:

• Set a default value (recommended) for the column you're creating.

• Create the column without the NOT NULL, set the appropiate values, and then make the column NOT NULL.