1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Warning: PDOStatement::execute(): MySQL server has gone away in

Discussion in 'Programming' started by readezarchive, May 22, 2015.

  1. #1
    I am trying to convert my site to PDO and keep getting this error:
    SEMrush
    Warning: PDOStatement::execute(): MySQL server has gone away in
    Code (markup):
    and

    Warning: PDO::query(): Error reading result set's header in 
    Code (markup):
    and

    Fatal error: Call to a member function fetchAll() on a non-object in 
    Code (markup):
    Any ideas?

    I've tried increasing max_allowed_packet. Strange thing is it doesn't give the errors every time. Only occasionally. Not sure what the deal is.
     
    readezarchive, May 22, 2015 IP
    SEMrush
  2. Anveto

    Anveto Well-Known Member

    Messages:
    685
    Likes Received:
    36
    Best Answers:
    17
    Trophy Points:
    195
    #2
    Can you share some of the code where you think this problem is occuring? Your fatal error just means that $pdo is not an object at that point which is probably caused by a typo or something.
     
    Anveto, May 22, 2015 IP
  3. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    724
    Best Answers:
    152
    Trophy Points:
    470
    #3
    You should read up here: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
    Usually, the "mysql server has gone away"-error is due to timeouts or problems with the MySQL-server - not with PDO or PHP. It might help if you tell us how you're connecting, whether or not the sql-server is on the same network as the webserver (or same server), etc.
     
    PoPSiCLe, May 23, 2015 IP
  4. readezarchive

    readezarchive Active Member

    Messages:
    601
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    80
    #4
    readezarchive, May 23, 2015 IP
  5. JEET

    JEET Notable Member

    Messages:
    2,447
    Likes Received:
    144
    Best Answers:
    3
    Trophy Points:
    215
    #5
    First error is happening because your script is taking too long to finish "select" or "insert" statements.

    Increasing mysql.connect_timeout 300 and default_socket_timeout 300 in the PHP.ini is not a good method of handling this error.
    You are simply putting more load on the server, instead of writing a better and faster query to handle the request in the given time slot.

    Other 2 errors are happening because there is no data in your results variable, or it doesn't even exists, and you are still trying to process it without checking if it exists or not.

    Your first query failed, so resultset was not generated.
    Your second query which uses the resultset from previous query, is running blindly, without checking anything, on just an assumption that everything is all right...
     
    JEET, Nov 16, 2019 IP
  6. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,041
    Likes Received:
    1,670
    Best Answers:
    236
    Trophy Points:
    515
    #6
    Which makes me think painfully convoluted queries, a lack of indexes on fields with common access, stringing together too many "join" choking the system out on RAM, etc, etc.

    That last one being some of the WORST advice out there, with people combining queries to avoid database connections, but in the process making larger result sets that can choke out the server.

    Really in any decent database if you're banging heads with the default timeout, there's something either horrifically wrong with the server, the database, or the queries being run. Upping the timeout is NOT the answer. That's like drilling holes in a man's head rather than repairing an artery.

    As others have said though, without knowing what the queries are and what the database structure is, it's all wild guesses.
     
    deathshadow, Nov 17, 2019 IP
    JEET likes this.
  7. malky66

    malky66 Acclaimed Member

    Messages:
    3,634
    Likes Received:
    2,060
    Best Answers:
    83
    Trophy Points:
    515
    #7
    It's a 4 year old thread, it didn't really need bumping...:)
     
    malky66, Nov 17, 2019 IP
    SpacePhoenix and JEET like this.
  8. JEET

    JEET Notable Member

    Messages:
    2,447
    Likes Received:
    144
    Best Answers:
    3
    Trophy Points:
    215
    #8
    I agree with @deathshadow 100%
    By increasing timeout, you are simply taking a pain killer pill, when the real problem is a fracture (which you are ignoring completely.)

    Check your query properly, either your selected resultset is very large, or indexes are not being followed.
    To avoid first reason, use "limit" in your queries.

    In your case I think second one is the issue, i.e query taking too long to execute.
    This means that number of rows in database are too many and query is not following proper indexes.
    Does your table has one or more indexes?

    If it does not, you can still create one now, like this:
    "Create index indexName on tableName ( columnName )"

    or like this, in case of multiple columns:
    "Create index indexName on tableName ( columnName, columnName2 )"

    You can do this in phpMyadmin

    Your query can be like this:

    "select columnName from tableName where columnName='abc' "

    "select columnName from tableName where columnName='abc' or columnName2='abc' "

    but not like this:
    "select columnName from tableName where columnName2='abc' or columnName='abc' "

    This last one will not follow index.

    by the way, you have increased your timeout "5" times, to 300 (5 minutes, instead of default 1 minute)...

    Who will wait that long, waiting for your website to open?
    Not to mention, your server will give up on a high traffic day, or your webhost will suspend your account.
     
    JEET, Nov 17, 2019 IP
  9. JEET

    JEET Notable Member

    Messages:
    2,447
    Likes Received:
    144
    Best Answers:
    3
    Trophy Points:
    215
    #9
    @malky66
    Oops sorry.
    I saw this in the popular threads section on the homepage...
    Really sorry.
     
    JEET, Nov 17, 2019 IP
  10. Spoiltdiva

    Spoiltdiva Prominent Member

    Messages:
    6,528
    Likes Received:
    1,917
    Best Answers:
    51
    Trophy Points:
    395
    #10
    A very common occurance, personally I wish the "popular threads" thingie would be done away with so that these old threads do not get inadvertently bumped up.
     
    Spoiltdiva, Nov 17, 2019 IP
    SpacePhoenix, JEET and deathshadow like this.
  11. JEET

    JEET Notable Member

    Messages:
    2,447
    Likes Received:
    144
    Best Answers:
    3
    Trophy Points:
    215
    #11
    @Spoiltdiva
    Ya, but may be the info will become useful to someone else facing similar issue...
     
    JEET, Nov 17, 2019 IP
  12. SpacePhoenix

    SpacePhoenix Well-Known Member

    Messages:
    129
    Likes Received:
    15
    Best Answers:
    1
    Trophy Points:
    120
    #12
    I know it's an old thread - I wonder if the OP was creating a new instance of PDO for every query instead of just creating one instance of PDO and sharing that instance of PDO around all scripts that need a database connection. I saw some code once where one page would hit the database about 80 times, each time creating a new connection! It was taking forever for a page to load (think it was on a shared server). When changed to use a single database connection shared between all scripts that needed a database connection, page load times plummeted.
     
    SpacePhoenix, Nov 17, 2019 IP
  13. JEET

    JEET Notable Member

    Messages:
    2,447
    Likes Received:
    144
    Best Answers:
    3
    Trophy Points:
    215
    #13
    @SpacePhoenix
    That is valid for mysqli and old MySQL too. Opening one connection and keeping it open till needed is better than connecting again and again. Huge difference in page load time.
     
    JEET, Nov 18, 2019 IP