Skip navigation
PeteComcar
Currently Being Moderated

Coldfusion 9 MySQL communications link failure

Jan 4, 2011 9:10 AM

've just installed Coldfusion 9, and everything is now working fine and it's connected to my MySQL databse via the Coldfusion Administrator as a datasrouce, but I get this error occuring infrequently, maybe 5 - 10 times a day. I don't know how to recreate it, and if you get it and refresh the page it goes away.

 

Error Executing Database Query. Communications link failure Last packet sent to the server was 0 ms ago

 

The error is being caught by coldfusion as it's displaying a user friendly message and emailing me the problem.

Almost all of the time the pages load and work fine, and this problem has only happened since going from CFMX7 to CF9, both using MySQL.

The MySQL version I am using is "5.0.77"

 

I've tried localhost and 127.0.0.1 as the host in the datasource, it makes no difference.

 

Does anyone have any idea what this is and how to fix it? I've seen some similar posts for Java but I don't see how to translate the fix across into Coldfusion?

And could it be a MySQL or Apache problem anyway?

 
Replies
  • Currently Being Moderated
    Jan 4, 2011 11:07 AM   in reply to PeteComcar

    Since the driver for MySQL 5 wasn't built into ColdFusion MX 7, but does

    come with ColdFusion 9, what driver were you using with CFMX7?  You could

    try to configure that same driver as type "Other" in the CF9 CFAdmin, then

    name it the same as your production datasource name.  Does that make a

    difference?

     

    ColdFusion 8 was the first version of CF to ship the JDBC driver for MySQL

    4/5 (I'm actually the one on the Adobe CF team that added the driver and

    updated CF Admin to show the driver option).  The driver we used was the

    mysql commercial driver, and with CFMX 7 you may have been using the MySQL

    community driver.

     

    I wrote this technote a few years back.  You could use it still in CF9 to

    add a different driver: http://kb2.adobe.com/cps/191/tn_19170.html   Its

    possible that because the driver class name is probably the same in the

    custom driver as it is in the built-in driver (com.mysql.jdbc.Driver) you

    may have to stop ColdFusion first, then move the existing

    ColdFusion/lib/mysql-connector-java-commercial-5.1.11-bin.jar to a safe,

    temporary location while you test your custom MySQL driver.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 7, 2011 5:45 PM   in reply to PeteComcar

    Have you thought about the possibility of a network issue?  Yes it is over

    local host, but I'd be curious if you could reproduce it when MySQL is on

    another host so that you could use a packet sniffer like Wireshark to see if

    packets are getting refused or reset or dropped.  But if you're seeing this

    in production then you probably won't be able to do that.

     

    How about enabling MySQL logging.  Its pretty simple to enable, but logs

    could grow quite large before you see it.  The point is that you can pair up

    events on the CF side with events on the MySQL side to see what's going on

    there at the same time.

     

    I'll give it some more thought.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 8, 2011 5:47 AM   in reply to PeteComcar

    The fact that it occurs intermittently makes me think it could be caused by a middle-man application -- firewall, proxy, antivirus, that sort of thing.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 11, 2011 12:14 PM   in reply to PeteComcar

    PeteComcar wrote:

     

    Well I've got the same set up twice (almost), I've got Ubuntu locally with CF9 and MySQL 5.1.37, and the live site is RHEL5 with CF9 and MySQL 5.0.77 and they both do it.

     

    I could set up a db in my workstation add a new datasource to the local CF9 to access that, but I'm not sure what you mean about the packet sniffing I'm afraid.

    I wasn't suggesting anything malicious. Just that firewalls, load-balancers and antiviruses are known to interrupt the communication betwen the Coldfusion and MySQL servers.

     

    The extra information you have provided seems to rule that out. It's becoming apparent that the issue has to do with the way CF 9 communicates with MySQL. That boils down to the drivers and settings.

     

    First, the settings. Verfy in the Coldfusion Administrator that the setting "Maintain Connections" is checked.

     

    Next, the drivers. I would stick to the choice 'MySQL 4/5' in the administrator. It comes optimized for use with Coldfusion. My advice is therefore to delete any drivers you yourself installed in the lib directory, and let Coldfusion revert to its original factory settings.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 11, 2011 11:56 PM   in reply to PeteComcar

    Let's get the misunderstanding out of the way. I don't think you consider me malicious. Since you mentioned packet sniffing, it got me thinking you meant malicious software. Anyway, that's that.

     

    I am back using MySQL 4/5, as it made no difference switching to the one I custom installed.

     

    My suggestion is:

    Use no custom-installed MySQL drivers. Delete any you might have installed(Stop Coldfusion before doing so).

    Choose MySQL 4/5 in the Administrator.

    Check the option to maintain connections.

     

    These settings have at least 2 advantages.  Firstly, they are the factory settings, and so would have been elaborately tested and optimized before going to market. Secondly, they are the default settings that developers use. You can therefore rely on the collective experience of others.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 12, 2011 2:47 AM   in reply to PeteComcar

    PeteComcar wrote:

     

    I didn't think you were being malicious, I just don't know what packet sniffing is... that's all!

     

    Anyway, before reading this post I actually UNCHECKED maintain connections.

     

    The reason being I got a different error at 11am yesterday

     

    "The server encountered an internal error and was unable to complete your request. Application server is busy. Either there are too many concurrent requests or the server is still starting up"

     

    I checked the error log and it was a JRun error, did some Googling and found some suggestions to UNCHECK maintain connections, so I did that, and it fixed both errors - so it was indeed to do with the CF9 settings.

     

    So sorry to contradict you BKBK but I'm leaving maintain connections unchecked.

    I can understand. I too have now googled up on the matter. It appears this is indeed a bug in ColdFusion 9.

     

    Not surprising. Seach for MySQL in the ColdFusion Bugbase, and you will find that there have been connection issues between ColdFusion 9 and versions of MySQL newer than 5.0.x right from the beginning.

     

    It is therefore not a coincidence that unchecking 'Maintain Connections' seems to work. It confirms there is a connection issue.

     

    That setting has to do with connection pooling. When you check it, ColdFusion reuses a connection from a pool of connection objects for numerous queries to the database server. This reuse of course means sparing CPU resources, hence efficiency. Unchecking the setting compels ColdFusion to create a new connection for each query. You wouldn't want that, if your application is data-intensive.

     

    I have gathered from the reactions on the web that the driver that ships with ColdFusion 8 appears to work well with the setting "Maintain Connections". That driver is mysql-connector-java-commercial-5.0.5-bin.jar. The newer driver that ships with ColdFusion 9 is mysql-connector-java-commercial-5.1.11-bin.jar. This brings me to my suggestion for a test. It goes without saying, you should do the test on a test server, not in production!

     

    Stop ColdFusion 9. Copy mysql-connector-java-commercial-5.0.5-bin.jar to the lib directory (if you don't already have it there). Remove all other drivers of type mysql-connector-java-commercial-x.x.x-bin.jar from the lib directory, and store them in a back-up location out of the lib directory.

     

    Restart ColdFusion 9. Choose MySQL 4/5 and check the setting to Maintain Connections. Any more communication failure?

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 12, 2011 4:22 AM   in reply to BKBK

    The Maintain Connections setting means that after a db connection is created

    for a given database, that connection will be used for the current query and

    then kept open in a connection pool so that they can be reused for later

    queries. The reason is that opening a connection is an expensive, time

    consuming operation, and its more efficient to only have to authenticate

    once.  When this setting is disabled, for every page request accessing a

    given database, a new database connection will be created, the db

    authentication will occur, the query/queries on the request will happen,

    then the db connection will be closed.

     

    When you maintain connections you have a pool of db connections that exist

    for an extended period, being frequently reused with additional requests.

    If the connections are idle for a period greater than the Inactive Timeout

    setting in the datasource definition, then those connections are closed and

    the pool size is reduced.  Also, if a request checks out a connection from

    the pool, attempts to use it for a query for some request, then if that db

    connection produces a db error then that is another situation where the db

    connection will be closed and removed from the pool.

     

    It has been known to happen that when you are pooling datasource connections

    like this that its possible that the TCP connection to the database has been

    interrupted for some reason, and when the connection is checked out for use

    on a page request you will get some type of "communication" error.  The

    actual error message will vary depending on the database.

     

    If unchecking Maintain Connections resolves this MySQL Communication Link

    Failure issue for you, then you are better off re-checking it AND adding a

    validation query.  ColdFusion 8.0 introduced a field in the dsn definition

    for Validation Query.  It works this way: When a database connection is

    first created AND every subsequent time that connection is checked back out

    from the pool, the validation query will run BEFORE any queries for the page

    request.  If the validation query fails, your page request will never see

    the error because ColdFusion will throw away that db connection and get

    another connection from the db connection pool.  It will then run the

    validation query for that connection too.  If that one errors, ColdFusion

    will continue closing the bad connections and checking out other connections

    until there are no connections left in the connection pool.  If it actually

    got that far (meaning every connection in the pool turned out to be bad)

    then ColdFusion will then create a NEW db connection and use that one, and

    it will run the validation query on that too.  All of this happens before

    your request runs to guarantee that your request gets a good db connection

    from the start.

     

    A good validation query is something is that is highly efficient so that db

    isn't really taxed by having to run it.  For MySQL you could use: Select 1

    That's it.  Enter that into the validation query field for the datasource

    and keep Maintain Connections checked to improve efficiency with connection

    pooling.

     

    Make sense?

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 12, 2011 6:07 AM   in reply to Steven Erat

    Validation query crossed my mind, but I dimissed the idea as irrelevant. in this case, ColdFusion can connect to MySQL. So connection isn't the problem. It is apparent that connection pooling is the factor that is somehow disrupting the communication between ColdFusion and MySQL. It seems as if pooled connections fail to be established between ColdFusion 9 and versions of MySQL driver newer than 5.0.x.

     

    If that is indeed so, then the communication will fail with or without the validation query. That is, all connections from the pool will be eliminated, one after the other, as the communication will keep failing. You will then be back where you started. With perhaps yet one extra disadvantage, though. The validation query runs under the hood. ColdFusion may not alert you of any exceptions, leaving you blissfully unaware of the failed connections.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 12, 2011 8:17 AM   in reply to BKBK

    A database connection in the connection pool maintains an TCP connection to

    the database.  If the connection is idle for a little while and something

    happens to break that previously established TCP connection, then the next

    query to try to use that db conn after checking it out will encounter some

    type of communication error (I think I recall that Oracle would report a

    "Net 8 Protocol Error" or similar)   This is where the validation query is

    helpful because it tests for bad or "stale" connections before letting the

    request use it.

     

    There are a range of things that can interrupt or break the TCP connection

    from under a DB connection that has been idle.  A problematic router, a db

    that was restarted, and other more subtle reasons.  Some even over

    localhost.  I think there's a good chance validation query will prevent the

    application from encountering this type of communication link error.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 12, 2011 9:59 AM   in reply to Steven Erat

    StevenErat wrote:

     

    A database connection in the connection pool maintains an TCP connection to

    the database.  If the connection is idle for a little while and something

    happens to break that previously established TCP connection, then the next

    query to try to use that db conn after checking it out will encounter some

    type of communication error (I think I recall that Oracle would report a

    "Net 8 Protocol Error" or similar)   This is where the validation query is

    helpful because it tests for bad or "stale" connections before letting the

    request use it.

     

    There are a range of things that can interrupt or break the TCP connection

    from under a DB connection that has been idle.  A problematic router, a db

    that was restarted, and other more subtle reasons.  Some even over

    localhost.  I think there's a good chance validation query will prevent the

    application from encountering this type of communication link error.

    You certainly have much more experience in the matter than I do. I hasten to add that I am just trying to figure this out on paper, as it were.

     

    The validation query is like me calling out to the waiter to make an order.  Calling him is a test to see whether I can get his attention. What if a communication problem prevents him from hearing me? Then he wont be able to transmit my order to the kitchen, let alone bring me the grub.

     

    Same goes for the validation query. What if the same persistent communication failure prevents the validation query from being transmitted to the database for execution? Then ColdFusion will know the connection is bad. My point is, that will be that and we will be back to where we started.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 12, 2011 10:32 AM   in reply to BKBK
    Same goes for the validation query. What if the same persistent communication failure prevents the validation query from being transmitted to the database for execution? Then ColdFusion will know the connection is bad. My point is, that will be that and we will be back to where we started.

     

    The frequency of the error is reported to be low.

     

    I get this error occuring infrequently, maybe 5 - 10 times a day

    So only on some occasions are the database connections problematic.  And this might be attributed to a few db connections going "stale" as I like to refer to it.  ColdFusion thinks that a given db connection is good, but in fact the proverbial rug has been pulled out from under it if the TCP connection somehow got dropped.  A validation query will be useful to prevent the request from getting a stale connection.  You would only be "back where we started" if every connection had this Communication Link problem since ColdFusion would be endlessly running validation queries, throwing away bad ones, and creating new ones.  Indeed if that were the case, it would be indicative of a much larger problem I think.

     

    Also, to discuss the value of packet sniffing, with a tool like Wireshark you could monitor the TCP connection state of every connection between CF and the database (well, only if they are on different hosts since packet sniffing only monitors things that go out on the network).  This would only be a useful endeavor on a host receiving low traffic since packet sniffing is highly verbose and on a production system you'd end up with way too much traffic to sort through.  At this point, you can probably disregard that suggestion since it was more of an academic one and not really practical.

     

    Enabling MySQL logging could be useful.  The log will be large, but you could filter it pretty easily for analysis to search for keywords like "error" or such.  You might get more information about how MySQL sees the problem when that Communication Link error occurs.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 13, 2011 12:18 AM   in reply to PeteComcar

    PeteComcar,

    You get no more communication failure. You have Maintain Connections going, too! What more could you want? Hats off to Steven!

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2011 12:26 PM   in reply to BKBK

    I've read through this and made changes based on these posts, but still have a few questions relating to a DSN 'failure'

     

     

    http://forums.adobe.com/thread/836689

     

    Thanks so muc if you can help explain/resolve!

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2011 12:37 PM   in reply to Beards247

    Beards247 wrote:


    but still have a few questions relating to a DSN 'failure'

     

    I'm sorry the Psychic Hot Line forum is the next one over.

     

    I'm affraid we can not read your mind here.  You will need to actually ASK any questions you would like help with explaining.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2011 1:21 PM   in reply to ilssac

    Thanks for getting back isaac. From the other post -

     

    The full error Error Executing Database Query.  [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name  'LittleBobbyTables'. (table name withheld)

     

    It fails intermittently - there is 1 table and 1 view that occassionally get this error. At first I suspected a network DNS issue, but it doesn't seem to be  the case since test CF Data Source Names to server name,  Server alias,  and IP address DSN all work.

     

    We are able to stop the error by changing the Data Source Name from server to the alias name or vice versa.  So the process:

     

    ColdFusion error - cannot find tables that do exist. Go into admin:

     

    • Change CF DSN from actual server name to DNS alias, submit or vice versa and submit

     

    Trying to figure out why this resolves the problem? b/c the connection pool is refreshed? Bigger Q - how do we troubleshoot the actual failure?

     

    FWIW, I've increased the connection pool, decreased the time out and decreased the check time.

     

    Thanks in advance,

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 11, 2012 1:41 PM   in reply to Steven Erat

    I'm running into a similar error using ColdFusion9 Standard.  I've just switched over to using mySQL 5.0.8 on a remote server.  Everything seemed to be running fine at first.  Because this app imports data from  API's for a slew of different accounts, I've created a queue to manage the imports using a simple scheduled task and some TEMP data tables.  At first the queue runs fine and then about two hours into the imports I get this error in the logs over and over again for what seems like each new import/record:

     

    Error Executing Database Query.Communications link failure  Last packet sent to the server was 0 ms ago. The specific sequence of files included or processed is: ******

     

    I've tried both the mySQL 4/5 driver and the JDBC/Other mySQL connector in the CFAdmin area.   I also had the Validation Query filled in with "Select 1" as was descibed earlier in this post.  I haven't tried this senerio with both Connectors, so I may just be using the wrong one.

     

    I also have the Maintain Connections  checked.  It's still seems pretty sparadic as the only place I see this error now is from the Queue.  The "front-end" interface was showing this problem until I made the recommended changes.  It may still be doing it on the front end and I just haven't spent enough time banging on the application to see it.

     

    The Timeout is set to 20 (min) in the CFadmin datasource section and I've read that this value should set to be less than the mySQL timeout?  Any help would be wonderful as I'm spending way too much time on this issue.

     

    Thanks in advance

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 11, 2012 1:54 PM   in reply to Matt@Bridgeleaf

    @Matt,

     

     

    Matt@Bridgeleaf wrote:

    I haven't tried this senerio with both Connectors, so I may just be using the wrong one.

    First, make sure your app is using the driver you think its using.  Try to configure them the same each time you change them, so both should have the Validation Query when you're testing it.  Or remove/rename one datasource to guarantee the app is not using one so you can test the other confidently.

     

     

    Matt@Bridgeleaf wrote:

    I also have the Maintain Connections  checked.

     

    For both of your datasources (with the 2 different drivers), try unchecking Maintain Connections and retest.  You should not have to restart CF, but if you can it's always a good practice to ensure no wackiness occurs.  By having it disabled, connection pooling is not used.  I expect you would NOT see the error when this is unchecked.  If no error occurs, then you can always use this as a fall back workaround, although there is a performance hit for not using connection pooling.

     

    If the error goes away when that is unchecked, then that demonstrates the problem is in connection pooling and the server was grabbing "bad" cached connections.  The validation query is intended to resolve that so that by guaranteeing you a "good" connection each time.

     

    If the error goes away when Maintain Connections is NOT checked, but the error reappears when it IS checked AND you are using Validation Query, then the problem may not be the same as was discussed earlier in the thread.  There is a chance that the error is the same, but the root cause is different.

     

    You might want to enable MySQL logging too to see if it yields any further info.

     

    I haven't been troubleshooting this area in quite a while so I may be missing something. 

     

    Let us know how it goes.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 12, 2012 6:00 AM   in reply to Steven Erat

    Had to do exactly what you recommended with multiple datasources and test test test.  It took a little while, but after some trial and error, I was able to determine that the mySQL 4/5 connector with the Maintain Connections not Checked seemed to have worked or at least so far.  

     

    Thanks to Steven for taking time out of his busy day to help point me in the right direction.  And thanks to all the other contributing CFer's out there as well!

     

    As always, it's much appreciated!

     

    Matt

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2012 12:31 PM   in reply to Matt@Bridgeleaf

    After getting this email, I realized I never posted our final solution - using a direct IP address. Everything else caused failures of one form or another which did not seem to relate to anything we could find.

     

    Using IP addy is not ideal, but it works for our environment.

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 4, 2012 12:21 PM   in reply to Beards247

    I am migrating from CF 9 to CF 10 and am running into this same error.  The difference being that I don't see it occasionally (5-10 times a day as stated above), but rather, I see it only on certain pages?  99% of the site runs perfect, but I have a couple pages that will hit this error every single call.  The queries that are failing are nothing special: (select something, something from table where id = number) and are used in other places on the site with no problem whatsoever. 

     

    Google searches are turning up very little with the exception of this thread.

     

    I'm at wits end.  Any ideas?

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 4, 2012 12:25 PM   in reply to sdsinc_pmascari

    Have you tried changing the CF datasource to an IP in the CF administrator? That is what has been working for us.

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 4, 2012 12:40 PM   in reply to Beards247

    Yes, all my DSNs are pointed to IPs.  I've run through every suggestion in this thread to no avail.  Again, most of the site works perfect.  Can't for the life of me figure out what is wrong with these select few pages.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 9:10 AM   in reply to Steven Erat

    Steven, can you further clarify what happens when maintain db connections is unchecked? 

     

    When this setting is disabled, for every page request accessing a

    given database, a new database connection will be created, the db

    authentication will occur, the query/queries on the request will happen,

    then the db connection will be closed.

     

    If you have a query at the top of the page using a DSN with maintain db connections unchecked, does the db connection close immediately after the query runs?  Or does it close after the entire page is processed?  What about a cfc?  If I split out the cfc processes into two or more functions, will the db connection close when the cffunction is done or does it stay open for until all the calls are made for whatever page is calling these functions?

     

    I appreciate any clarifications.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 9:54 AM   in reply to mowebdev2

    mowebdev2 wrote:

     

    I appreciate any clarifications.

    Then start your own thread!

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points