#1
  1. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62

    Internal server error during inserts only


    Hey guys. I have a little weird issue here. I have a website and pages load just fine. However, whenever someone tries to make any inserts, my pages hang and I eventually get an internal server error. This started just this morning and I haven't changed anything from yesterday until today.

    I'm on a VPS. I restarted mysql to no avail. I even tried restarting apache just for S&Gs. The load on the site looks fine. All reads work just fine. As a matter of fact, as I write this I have 7 people on my site solving chess puzzles. Again, those are only reads. They make a puzzle guess and an ajax calls does a select to see if their gusess was correct. But all of my users who are trying to play games? The inserts SOMETIMES happen, but in most cases, the page doesn't return even when it does work. I looked in the apache error logs and didn't see anything that would tip me off as to what's going on and my hosting company is moving REAL SLOW.

    Anyway, considering this is a basic code base I've had for years and I haven't changed anything from yesterday til today, have any of you guys heard of anything like this (reads no problem, writes VERY difficult)? I don't get that much traffic. Maybe 1500 page views a day. 2000 tops. I probably have no more than 15 people on my site at any one time. Anyway, thanks for any help you can give solving this mystery.
    Correspondence chess
    nothingbutchess.com
  2. #2
  3. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62
    Just to give a little more information, the slowness is happening when I attempt to insert into a longtext field. I create an object of the game, serialize it, then insert it into the database. I've been doing this for years.
    Correspondence chess
    nothingbutchess.com
  4. #3
  5. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,054
    Rep Power
    9398
    Drive problems?

    Make a script/SQL that tries to insert some longtext data into a test table, see if that causes any problems.
  6. #4
  7. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62
    Ok, this looks like it's not my issue. Looks to be a hosting issue. Annoying when you have users on your site and this happens. Makes you look bad, but what are you going to do? Thanks guys.
    Correspondence chess
    nothingbutchess.com
  8. #5
  9. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62
    Ok, the problem still persists. I save all errors to the database, so I checked to see if there was anything. Sure enough, the insert that I thought was causing the problem is throwing the following error:

    Cannot insert into...(huge sql statement)

    because: Lock wait timeout exceeded; try restarting transaction

    This huge statement really just has two columns, one of them being a longtext field. Bottom line, it's a serialized object that I store as a longtext. I've been doing this for years without incident. Have I some how created a lock that just needs to be released? Again, the field is longtext, so I don't see the size of what's being updated (48kb worth of stuff, I just checked) being an issue. Again, I've been doing this same insert for years with no issue. Any thoughts?
    Correspondence chess
    nothingbutchess.com
  10. #6
  11. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,054
    Rep Power
    9398
    Some query somewhere is locking the table. It's probably running for a long time; try a SHOW FULL PROCESSLIST, scan down the queries returned, and see what's running. It also includes the running time.

    Once you identify the query you can figure out why it's taking so long. Typically it's a SELECT query with really bad performance.
  12. #7
  13. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62
    I get back

    Code:
    mysql> SHOW FULL PROCESSLIST;
    +------+------+-----------+------+---------+------+-------+-----------------------+
    | Id   | User | Host      | db   | Command | Time | State | Info                  |
    +------+------+-----------+------+---------+------+-------+-----------------------+
    | 1699 | root | localhost | (my database name)  | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
    +------+------+-----------+------+---------+------+-------+-----------------------+
    1 row in set (0.00 sec)
    And again, considering this is the code that's been running for years and I didn't make any changes yesterday and it was running fine yesterday, only to wake up this morning and see this issue (under at the time, light load)...I'm a little stumped here.
    Correspondence chess
    nothingbutchess.com
  14. #8
  15. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,054
    Rep Power
    9398
  16. #9
  17. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62
    Ok, I did it during a simple delete statement involving the two tables that seem to be the ones where I'm experiencing most of my issues (although there have been others) and received the following:

    Code:
    mysql> SHOW FULL PROCESSLIST;
    +-------+------+-----------+------+---------+------+-------+-----------------------+
    | Id    | User | Host      | db   | Command | Time | State | Info                  |
    +-------+------+-----------+------+---------+------+-------+-----------------------+
    | 12015 | root | localhost | nbc  | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
    | 12076 | root | localhost | nbc  | Sleep   |   (number) |       | NULL
    Where (number) went from 3 and slowly crept it's way up to 61. I kept running the query as it hung. At one point, three processes showed up, with the third one being

    12145 | root | localhost | nbc | Sleep | 0 | | NULL

    but that one went away the moment I ran the command again. After about 20 seconds or so, it FINALLY did the delete. I mean, we're talking to simple delete statements. Now again, one of the deletes has a field that has roughly 48k worth of data in it, but I've been doing this literally for years with nary a hitch. Considering these same commands run blazing fast in the mysql client, I'm beginning to think that apache is somehow screwed. However, I've restarted this before to no avail. I'm unfortunately stumped here.
    Correspondence chess
    nothingbutchess.com
  18. #10
  19. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62
    Ok, I've isolated the error and it has nothing to do with the mysql database, my code nor the apache server. It appears that the problem is with the sendmail server.

    I created a test php page with inserts, updates and deletes of this large data and had no issues at all. I finally realized that the only thing that every failed operation had in common was that after every operation, it would send out an email. "Hey, it's your turn to move." "Hey, you lost the game." Took those out and everything is running fine. Now...to contact my hosting provider and together figure out what to do here. I guess restarting sendmail would be the first step. Obviously this is no longer a mysql issue. Thanks a ton, requinix.
    Correspondence chess
    nothingbutchess.com

IMN logo majestic logo threadwatch logo seochat tools logo