Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Fri Jul 14, 2006 4:46 am
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 » Fri Jul 14, 2006 4:56 am
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 » Fri Jul 14, 2006 4:59 am
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.
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Fri Jul 14, 2006 4:59 am
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 » Fri Jul 14, 2006 5:14 am
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.
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Tue Jul 18, 2006 5:18 am
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.