Page 1 of 1

Adding new fields to existing table

Posted: Fri Jul 14, 2006 4:46 am
by Benjamin
If I send a query to MySQL from a php script which adds a new field to a table, and the script times out, that won't corrupt the table will it? I am assuming MySQL will complete the transaction, I just want to double check.

It's just a simple..

Code: Select all

ALTER TABLE `table` ADD `newField` CHAR( 1 ) NOT NULL DEFAULT '0';

Posted: Fri Jul 14, 2006 4:56 am
by jamiel
You shouldn't really be making structure changes from a PHP script. Do it from the MySQL Client.

Posted: Fri Jul 14, 2006 4:59 am
by jamiel
It should Lock the tables, but if it time's out I'm not sure what the result would be. I presume it can roll back because the table was locked.

Posted: Fri Jul 14, 2006 4:59 am
by Benjamin
jamiel wrote:You shouldn't really be making structure changes from a PHP script. Do it from the MySQL Client.
That isn't an option in this case.

Posted: Fri Jul 14, 2006 5:14 am
by GM
Are you likely to be doing this often? It really isn't the best way to alter your database structure... Why is the MySQL client not an option?

If the script times out, then it really depends at what point the connection with the DB was broken. I think you'd be extremely unlucky if you were left with a corrupted database.

Posted: Tue Jul 18, 2006 5:18 am
by Benjamin
It's not going to be done horribly often. Perhaps once a month or so.

I'm going to assume that MySQL will complete the transaction.

I'm guessing I should lock the table.

Code: Select all

mysql_query("LOCK TABLES `tableName` WRITE");
mysql_query("SET AUTOCOMMIT = 0");
// changes here...
mysql_query("COMMIT");
mysql_query("UNLOCK TABLES");
Is this the correct format? I'm going to read the manual on it in a bit. Any advice would be good though.