Random SQL issue

Discussion in 'Performance Tweaking' started by krustymk, May 10, 2015.

  1. Hi guys, i hope someone can help me with this.. seems completely random, absolutly fine sometimes then the following will occur out of the blue.
    Any help would be greatly appreciated

    Its an OVH dedicated server with local SQL databases


    20:05:44 CONSOLE: [INFO] SQLDataStorage.executeQuery
    20:05:45 CONSOLE: [INFO] SQLException: Communications link failure
    20:05:45 CONSOLE: packet successfully received from the server was 300,105 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
    20:05:45 CONSOLE: [INFO] SQLState: 08S01
    20:05:45 CONSOLE: [INFO] VendorError: 0
    20:10:45 CONSOLE: [INFO] SQLDataStorage.executeQuery
    20:10:45 CONSOLE: [INFO] SQLException: Communications link failure
    20:10:45 CONSOLE: packet successfully received from the server was 300,096 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
    20:10:45 CONSOLE: [INFO] SQLState: 08S01
    20:10:45 CONSOLE: [INFO] VendorError: 0
    20:15:08 Server: Saving world data. Expect lag for a short while.
    20:15:08 CONSOLE: [INFO] Saving...
    20:15:08 CONSOLE: [INFO] Saved the world
    20:15:08 Server: Save Complete
    20:15:45 CONSOLE: [INFO] SQLDataStorage.executeQuery
    20:15:45 CONSOLE: [INFO] SQLException: Communications link failure
    20:15:45 CONSOLE: packet successfully received from the server was 300,116 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
    20:15:45 CONSOLE: [INFO] SQLState: 08S01
    20:15:45 CONSOLE: [INFO] VendorError: 0
    20:20:45 CONSOLE: [INFO] SQLDataStorage.executeQuery
    20:20:45 CONSOLE: [INFO] SQLException: Communications link failure
     
  2. Try to telnet into your mysql port (by default it's 3306). If you can't, try via SSH. If that also fails to work then the mysql server is either not running or the firewall has blocked it.

    If neither is the case, the sql connection in SQLDataStorage is improperly stored and/or retrieved and you should consult whoever developed the plugin.
     
    • Like Like x 1
  3. J
    5.5.41|*☺IVS"K3[☻ǧoS4HN-6h,?76mysql_native_password

    Connection to host lost.

    Press any key to continue...

    I tried to telnet into the host (never done it before) and this was the result.
    Is it safe to assume that simply because i got a response the firewall is open?
    Spigot and the SQL server are on the same box anyway, is the firewall between the box and the network or can it block programs on the same box?

    Thanks for helping
     
  4. A firewall can block local connections if improperly configured. If you were able to telnet into mysql from outside your box, you also have an improperly configured firewall (unless this is your intention; because opening mysql up to the public is bad).

    From your machine (in SSH) try to login to mysql, using the "mysql -u <user> -p" command (without quotes).

    I'm starting to think the problem is with the plugin that provides SQLDataStorage, the connections may not have an auto refresh value, thus they close themselves and you're trying to use a closed connection also resulting in this error.
     
  5. This is ultimately due to stale connections iirc.
    I had this before and I believe it was due to c3p0 (a really old Connection Pool). What library are you using or are you using raw connections?
    You NEED to use a connection pool. Connections WILL die naturally. So you must implement reconnect handling in your connection .

    I am now using HikariCP w/o ANY issues, 100% reliable connections.

    https://gist.github.com/aikar/2c2bf51e81a3944a9562
     
  6. Well... you guys are clearly way better at this than me to the point you're losing me.

    I run a small server with very little knowledge, mostly ask some questions and find what to learn about as things go wrong or problems pop up. I installed and configured CentOS on my own and the minecraft bits but thats about as deep as my knowledge goes. and to be fair it may be configured all wrong ..

    I have Mcmmo, Stats, Prism and Autorank installed as plugins. Disabling Autorank has cured the problem in as far as no errors in the log any more. I have contacted the author of AutoRank to help

    However.. i seem to be the only one experiencing the issue so obviously my setup is contributing towards it?

    So as a host using a publicly supplied plugin, any advice or pointers on what to check and tools to use? Ill then get on to learning about them and figuring things out..

    Im using vestcp to manage MySQL, perhaps not the best choice? but its learning curve is pretty easy and time is something i have little of..

    Thanks again