CODEWITHSUNDEEP
phpmysql
good_evening
good_evening

Reputation: 21759

Is it possible that mysql would fail to update some data?

Ok, let say we have two rows:

member_id, name

Let say member_id = 15 and name = 'John';

I want to UPDATE this data and do the following query:

mysql_query("UPDATE members SET member_id = 14, name = 'Peter' WHERE member_id = 15

This is just an example, but is it possible that mysql would fail and UPDATE for example only name row. So, after completing mysql_query above, it would become member_id = 15 and name = 'Peter';

It is just an example. Today, a similar situation happened in my website and I checked my code hundred times and I see no errors and there hadn't been no same errors before it at all.

So, should I recheck my code one hundred times more, or it can happen?

Thank you very much.

Upvotes: 2

Views: 142

Answers (4)

Ted Hopp
Ted Hopp

Reputation: 234847

I'm not as sure as everyone else that single-row updates are atomic. MySQL differs from standard SQL in that it does the column assignments of a single-table UPDATE left-to-right. That means member_id = 14 is done before name = 'Peter'. If name = 'Peter' violates a constraint, or triggers another update that fails, then that assignment will fail. However, whether the statement as a whole fails or not may depend on various factors, including whether the table is using a transaction-safe engine.

Upvotes: 0

Shoe
Shoe

Reputation: 76280

I think it should not happen that it would become member_id = 15 and name = 'Peter'. The SQL syntax is right as far as i can see and it seems all good. If something wrong happens to your database and your query is executed in that moment God only knows what could happen. Despite this, most of the time either the query is executed entirely or it is not, making mysql_query() return false.

You should, as already suggested at least check if there where any error with a code such as the following if you are in an online business website:

mysql_query($sql) or die('An error occurred');

or something like as follows if you are in debug mode:

mysql_query($sql) or die(mysql_error());

Upvotes: 0

NullUserException
NullUserException

Reputation: 85478

According to the spec, single UPDATE statements are atomic; ie: either it updates all columns or it doesn't update any of them.

So no, it shouldn't happen. But of course there could be a bug with MySQL.

Upvotes: 2

Patrick Beardmore
Patrick Beardmore

Reputation: 1032

Put or die(mysql_error()); after your query so that if this really is happening then you would at least know about it.

Upvotes: 0

Related Questions