Copy link to clipboard
Copied
'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?
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
...Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
I can't remember now, but I think it was just a MySQL 3/4 driver from the drop down at the top.
I remember my old local windows machine and new Ubuntu machine both have a driver that I had to download and set up as "other", but the old live machine used the MySQL driver from the drop down, so I just did the same thing for the new live one, but noticed it had changed to MySQL 4/5.
I'll try setting it up as an "other"... I should have thought of that myself. I'll have to wait till we're out of peak times now, I'll try that later tonight and update the ticket later.
Copy link to clipboard
Copied
Ok I added an "other" database type, and it's doing the same thing, works most of the time but occasional "communications link failure" messages coming in.
I used the latest Connector/J 5.1.14, on MySQL 5.0.77.
I think my next move is to try to update MySQL to 5.1, and if that still fails then 5.5.8.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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 am back using MySQL 4/5, as it made no difference switching to the one I custom installed.
I'm not going to mark this is answered for another 24 hours though, as I only get 3 - 10 errors a day so I wont to be sure it wasn't just a lucky day.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Yes the driver was previously MySQL 4/5, and is back to that now, it was only temporarily set to use the one I'd installed as a solution for this bug, but it didn't fix anything so it's gone.
However, since I've unchecked 'Maintain Connections' I've not had ANY communications link failure.
So what exactly does 'Maintain Connections' do?
Also the packet sniffing comment was originally a response to Steven, I think that's where the confusion came from.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
That's probably the best answer to any forum question I've ever posted and/or seen.
I'll set the dsn to check 'Maintain Connections' and I've added "SELECT 1".
I'll give it 24 hours and see if that continues to keep the errors away.
BKBK, I'll give your solution a try next if this doesn't work out.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
@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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
Have you tried changing the CF datasource to an IP in the CF administrator? That is what has been working for us.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.