Adding new fields to existing table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Adding new fields to existing table

Post 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';
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

You shouldn't really be making structure changes from a PHP script. Do it from the MySQL Client.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
Post Reply