really weird mysql bug

[yemista]

I am writing a game in php that uses mysql. The weird bug is that I have a table with two fields, gold, and food. When I login, the program runs a query to update both gold and food, however, only food gets updated, not gold. This also only happens on my account, not ony my friends. The other weird part is that when I output this query to the browser, and manually input it using phpmyadmin, it works just fine, it only wont work from the server, and only half of it doesnt work. Any suggestions?

[requinix]

My first suggestion would be to post code.

[yemista]

Which code? mysql query or the php code? My code works fine for a while, but then for some reason just stops working. When I reset my account on the site, it starts working again.

[requinix]

Which code? mysql query or the php code?

Yes.

[yemista]

Here is my source code for the section in question. This runs every time the user logs in:

 

$result = mysql_query("select gold, food, land from castles where userid=".$userid);
	$row = mysql_fetch_row($result);
	mysql_query("update castles set gold=".($row[0] + $gold_per_acre*$row[2]).", food=".($row[1] + $food_per_acre*$row[2])." where userid=".$userid);
	echo "update castles set gold=".($row[0] + $gold_per_acre*$row[2]).", food=".($row[1] + $food_per_acre*$row[2])." where userid=".$userid;

 

I dont think there is an error in the code, but something more with the php or mysql server. It works fine for a while then the gold part of the query suddenly fails. It hasnt happened for my friend yet. the odd part is if I take the query that is echoed, and put it into phpmyadmin, it works just fine. It onyl wont run through the browser, and only half of it. Food gets constantly updated.

[PFMaBiSmAd]

You are probably exceeding the value that your field definition can hold or overflowing the math that php can do. What exactly is the query when you echo it and what is your table definition?

 

Also, you don't need to select the information to update it. You can write just an UPDATE query and I recommend you form the query in a php variable and just put that variable into the msyql_query() statement and echo the variable, rather than repeat the query in two places -

 

$query = "update castles set gold=gold + ($gold_per_acre*land), food=food + ($food_per_acre*land) where userid=$userid";

[yemista]

I dont think I am overflowing the variable either. gold and food are both of type int. Here is an example of a query that gets outputted:

update castles set gold=25050, food=5825500 where userid=1

[PFMaBiSmAd]

Ummmm. You have got to help us out here by suppling some relevant information that you know about the information you have been posting. Is the 25050 value in that query the correct value (the existing gold amount plus the $gold_per_acre*$row[2]) or is it missing the $gold_per_acre*$row[2] quantity (it's the existing gold amount only)? If the 25050 value is correct, but the column is some existing lower value, does the query leave the column as the existing lower value? For your friends account that seems to work, what is his approximate amount of gold?

 

And, I didn't ask for your table definition for the fun of it. While you might think the column is defined correctly, unless you have actually checked or shown us, it could be the source of the problem (25050 is suspiciously close to a 32,768 signed maximum for 16 bits.)

 

This runs every time the user logs in

^^^ Yes, but is that the query that is responsible for updating the amounts as you play the game, after the user has logged in?

 

When I reset my account on the site,

^^^ What exactly does that mean? What values get reset and what are they reset to?

[yemista]

I cant get the whole table definition at the moment, but I did check and both food and gold are defined as int(11). My friend whose account works currently has 5989500 gold. The 25050 is correct, but the existing value of the column is 50, and thats what the query leaves it to. What I meant when I said reset my account is that the row is in a table called castles. When I delete the row associated with my account, and create a new one, it updates just fine until the bug comes back.

[PFMaBiSmAd]

The only guesses I have at this point, assuming that is the only query there is that modifies or sets those two values -

 

1) Your browser might be requesting the page twice and the second query that runs has a zero value for $gold_per_acre and actually looks like - update castles set gold=50, food=5825500 where userid=1

 

2) You have another query that is setting the value back to its initial starting value of 50 somewhere in your code and it is also being executed, possibly due to not having an exit; statement after a header() redirect.

 

It would really help is we saw all the code on the page so that we could tell where $gold_per_acre and $food_per_acre are coming from and if it is possible for the code to have one of those with a correct value and not the other or if there are any other queries on the page that could be causing this (what if the $row values are used in another update query later in the code where it is supposed to have values from a different query that is failing) or if there is a logic error present that could be causing this.

[yemista]

There is no other query that sets the value back to 50. The initial value for this column is actually 0. I just had a thought. $gold_per_acre is declared as GLOBAL within the function, and is defined in another file. Is it possible that for some reason php doesnt see it and assumes its 0? The weird thing is everythign works fine for a while, and just stops working, without changes in code. Currently its working because I reset my account, but I will wait until it happens again to try and fix it this way.

[kickstart]

Hi

 

That is certainly possible if the code setting the value hasn't been executed prior to the function being called.

 

All the best

 

Keith

[PFMaBiSmAd]

You apparently have a logic error that is preventing a value from being set (under some condition) or your code doesn't have error checking logic in it (I'm going to guess that a query is failing or is failing to match a row) to get your code to tell you when something is failing. You are probably also not debugging this on a system that has php's error_reporting/logging set so that php detected errors are reported.

 

To get the quickest solution, you will need to post ALL the code necessary to reproduce the problem. xxxxx out any sensitive information, but if you want someone else to help with what is causing this problem, they must have all the code (data can be simulated) so that they can track down under what conditions the values have and don't have the expected values.

[yemista]

There is alot of code and updates in multiple places, so I dont know what code to post other than the isolated section. The weird part in all of this is, why would only half the query work? Like I said before, food gets updated, but gold does not, so half the query is working. If the query is failing, shouldnt the whole thing fail rather than just half?

[kickstart]

Hi

 

That is going to depend on what is failing within the query, or before the query.

 

I would suggest that you log the query so that when it fails you can finding the failing query and see what the values used were.

 

All the best

 

Keith

[PFMaBiSmAd]

I dont know what code to post

 

^^^ Someone told you what code to post. All the code necessary to reproduce the problem.

 

There's more than a dozen different things your code could be doing that could cause this symptom. Other than the few guesses and suggestions so far, no one is going to sit here trying to guess what your code is actually doing internally, what determines the two programmable values that are going into that query, how that query is getting called and under what conditions, what the code is doing that is dependent on what the browser is doing and make a list of everything they can think of. And no two people are going to think of exactly the same things, due different things they have experience with. A list of possible problems that kickstart makes would have some items in common with a list I would think up, but the actual problem could end up being something that is not on either of our lists because your code could be doing something in an unusual, unexpected, or uncommon way.

 

By providing the actual and complete code that reproduces the problem, all those possible guess are narrowed down to just a few actual things to check out because all the guesses about things your code cannot possibly be doing can be immediately eliminated. This would also allow someone to reproduce the problem or perhaps not reproduce the problem by executing the code on their server. That would further narrow down the cause of the problem.

 

You are the person here who is most familiar with the code. We have only seen 3 actual relevant lines of it. What do you think is causing the problem after nearly 4 days since you started this thread? What could be going on that is due to your browser requesting the code that could cause only the food value in the database table to be updated, while the gold value does not change or is changed and then overwritten with the old value?

 

This is smelling more and more like the browser is requesting the page twice, which someone already guessed/suggested, or your code is calling the code with the query twice, and the first time the query has the correct values, but the second time either there is a negative value involved that subtracts out the amount that was just added or the original starting value is put back as the final value. I was going to suggest the same thing that kickstart posted above, log the microtime and the actual query statement for every query that affects the gold column.