Strange MySQL/PHP issue: mysql query does not execute and there is no error

Question

I ran into a strange bug in my PHP web app that I'm unable to sort out. The php script fetches a row from the database, it processes it and serializes a new query and writes back the row to the database.

I copied the code that does this:

foreach($a as $key => $value)
{
    $a[$key]="'".mysql_real_escape_string($value)."'";
}
$a['lastUpdate']="$when";
//assembling request
$assignments=array();
foreach($a as $key => $value)
{
    $assignments[]="$key=$value";
}

$q="UPDATE wtfb2_villages SET ".implode(',',$assignments)." WHERE (id=${a['id']})";
logText($q);
logText(mysql_error());
$r=mysql_query($q) or die(__FILE__.':'.__LINE__.':'.mysql_error().':'.$q);
logText(mysql_affected_rows());
logText('Mysql error in query is: '.mysql_error()."\n");

That UPDATE seemingly executes. mysql_error() gives an empty string. mysql_affected_rows says 1 just like when everything is done. But if I look into the table using phpmyadmin I saw that nothing is changed.

I logged the query itself its:

UPDATE wtfb2_villages SET id='22',ownerId='56',villageName='Új falu',x='0',y='1',buildPoints='7.2226273148149',barracksLevel='0',archeryRangeLevel='0',stablesLevel='0',workshopLevel='0',townHallLevel='0',blacksmithLevel='0',goldmineLevel='1',wallLevel='0',spearmen='0',archers='0',knights='0',catapults='0',diplomats='0',spearmanLevel='0',archerLevel='0',knightLevel='0',catapultLevel='0',spearmenTraining='0',archersTraining='0',knightsTraining='0',catapultsTraining='0',diplomatsTraining='0',lastUpdate='2011-01-18 21:56:10' WHERE (id='22')

If I just copy-paste this query directly into the phpmyadmin it executes it and I see the result in the table.

The table is using myIsam engine. So no transactions or anythin such.

I got stuck at this problem more than 2 hours and I'm unable to find out what's going on.

This is the only one query that does this. queries before and after this one executing properly.

Any ideas?

Have you tried only to only UPDATE a single column instead of all of them at once? — scragz
– scragz, Commented Jan 18, 2011 at 21:21
Are you 1000% sure you are looking at the same table on the same server? It's happened to all of us.... — Pekka
– Pekka, Commented Jan 18, 2011 at 21:21

Calmarius · Accepted Answer · 2011-01-18 22:17:49Z

1

I found it out... The bug was elsewhere...

When a village is attacked by an army the event processor script first checks whether the village exists. If exists it fetches the row too. Reads the owner's id from the row, and updates the owner's all villages to determine how much loot can be got from that village. After that the event procesor simulates the battle, calculates the casualties and... ...writes back the row with the old update date... pfff That was it...

My advice: if you got stuck with a completely absurd bug... Have a shower, eat your dinner, feed the dog or run a round around the block etc... Just stop thinking... After you sit back in front of the computer you can almost find any kind of bug immediately...

ps: And only when everything fails start a SO post...

answered Jan 18, 2011 at 22:17

Calmarius

19.7k20 gold badges120 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TommyGunn32 · Accepted Answer · 2011-01-18 21:24:45Z

0

Looking at your query log, is ID a primary key? I'm not sure you can include it in your update query if so. It's been a while since I've done DB work though.

answered Jan 18, 2011 at 21:24

TommyGunn32

9331 gold badge9 silver badges22 bronze badges

2 Comments

jeroen Over a year ago

That shouldn´t be a problem unless you set it to another existing ID, which would generate an error.

Calmarius Over a year ago

If I copy-paste the query directly into the phpMyadmin, it executes. I think I have to sleep one night before I can fix it. But I'm curious what can it be... Maybe a php bug? Who knows...

Collectives™ on Stack Overflow

Strange MySQL/PHP issue: mysql query does not execute and there is no error

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related