Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Unanswered: Very Odd Database Entry Error

    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.

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    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.

    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    6
    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?
    Last edited by IkimashoZwei; 11-24-04 at 14:23.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is almost certainly a problem with your application code logic

    for the time being, you could cut off the loop from going on too long if the difference in times is "unreasonable"

    meanwhile, you need to track down where the bad low time is coming form, and the place to start looking is not mysql, not phpmyadmin, but your code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    6
    "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.

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    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!

  7. #7
    Join Date
    Nov 2004
    Posts
    6
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by IkimashoZwei
    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!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i must apologize for the previous post

    sometimes my evil twin takes over, and i am powerless to stop him

    of course, there is no fix coming from mysql

    they are still trying to identify the cause of the problem

    sorry for raising your hopes, even if only briefly

    i have locked my evil twin up in the attic and he will be getting no dinner tonight
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Posts
    6
    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??

  11. #11
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    hai,

    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..
    Sudar

    --
    My Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good one, sudar!!

    the "int(255)" should have tipped me off but i totally missed it

    well, this has been a fun thread

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Wrong way:
    values ('$cur_time', '$cur_time', '$cur_time')

    Right way:
    values ( $cur_time, $cur_time, $cur_time )

    If these are user-supplied values then you should also run them through intval(), too.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •