I'm running phpMyAdmin on my website to manage mySQL. My problem is this: the numbers going into my PHP queries are not the numbers that are ending up in the database. This problem has caused some perpetual loops, and thus my host has recently shut me account off. When I get back online I'd like to have some basis for fixing this problem. What on earth would cause a table to have the wrong information inserted into it? The field in question is int type.
Well mysql doesn't have loops so that isn't the problem. it is likely in your programming code that you are using to check the values in mysql.
You're right. MySQL doesn't have loops. PHP does. It's the bad data in the tables that's causing PHP to loop and thus get my account disabled.
What are the specifics of the problem? Do you have the entries that are supposed to be made and the data that actually ends up in mysql?
What is the specific code of PHP or whatever that you are using to input the data? Don't post your entire code but post the part that is relevant in inputting the data into mysql.
Here's the rundown:
A table called "users" stores all data for a user. The original problem was in this table. There are three fields (all int(255)): time_of_last_access, run_count and mp_regen. The first is simply updated with the current time (derived from time()) whenever the user does anything. This information is used by a "Recent Activity" display on the main page. The second tracks the time at which a run was added. There's a loop on the main page, such that with every repitition a run is added and it also adds some time to the time number in storage. The loop repeats until the number gets close the current time (again, compared against time()). The same is the case for mp_regen, but tracks a different statistic. When the user creates an account, the current time (time()) is put into a variable ($cur_time) and a query is written, inserting this same value into time_of_last_access, run_count and mp_regen.
For a long time, this worked fine with no problem. Then, one day, after some updates, something strange happened. I created a new account and discovered the the main page was timing out. I look in the database and discover the following: time_of_last_access and run_count have the correct number in them. mp_regen contains some, much smaller number (I'm guessing it correlates to some time around the late 1980's). Thus, the loop that checks mp_regen was stuck in a not perpetual, but damn long cycle, trying to catch the mis-entry up to the present, 2400 seconds at a time.
This error seems completely absurd to me since $cur_time is the same variable going into all three fields, yet mp_regen ends up with a different value. I ended up deleting all three fields of the table and replacing them annew, and this seemed to fix that specific problem, but the way this new problem cropped up and got my site account disabled would seem to indicate that the same effect has spread to other tables or at least other fields of the user table.
What I would like to know is the cause of this problem, so I can prevent it from happening again. Is it something my users are doing? Is it some flaw in my version of phpMyAdmin? Perhaps I simply screwed something up in a table property somewhere and didn't know it?
"insert into users (time_of_last_access, run_count, mp_regen) values ('$cur_time', '$cur_time', '$cur_time')"
I'm chopping out a lot of other values (because they're irrelevent to this situation) but the fact remains: they are in the right order (I counted twice), and the first time this happened, the problem was fixed not by any changes to the code, but simply by removing and replacing the appropriate fields.
The next mysql query that happens after this one is the check against all three variables and is followed by their respective loops. No other queries have a chance to mess up the values.
The problem is with MySQL, and I want to know what it is.
Either you have mislabeled your mysql columns, or you should post the entire mysql insert statement here to see. There are no loops in mysql as pointed out, the problem is definitely in your application code. Trust Rudy on this!
Please do not assume I can't tell the difference between a query language and a programming language again. It is insulting.
Let me assume for the moment that you are correct: there is a problem in my code. Please explain to me then, how, with no changes to the PHP code, deleting and re-adding those fields fixed the problem (for those three fields only) completely.
The problem is with MySQL, and I want to know what it is.
wait! i remember! there's a special bug that happens once in a while! all of a sudden, too! with no warning! your stored values change completely! it doesn't happen to every row! only sporadically! it has been reported to mysql! hold on, a fix is coming!
I should also apologize. Looking back at my last post, it appears rather hostile.
Thank you for looking that up for. It sounds exactly like what the players of my game reported. They said that their stats seemed to be changing randomly for no reason. Does your source mention anything about the cause of the problem? If I can't fix it (short of dumping the entire database and starting over from scratch) can I at least prevent the same thing from happening again??
I don't know whether this is a cause for your problem. But once i had a int column and i was trying to insert a value greater than the maximum value for int. MySQL executed the query without any error, but the value in the table was different. I was banging my head for more than a week and finally i found the reason.
So just make sure, you are doing anything like this..
Also, you are making MySQL convert your ints to a string and then back again since you are enclosing them in single quotes. Although MySQL allows this, this is non-standard and a VeryBadThing.
values ('$cur_time', '$cur_time', '$cur_time')
values ( $cur_time, $cur_time, $cur_time )
If these are user-supplied values then you should also run them through intval(), too.