3

I'm in the process of upgrading the CF8 instances in our environment to CF10, but I'm running into a weird issue with the DB.

Datasources connect to a MySQL 5.0.8 DB using InnoDB. Our current setup with CF8 works fine, but when we try and point a CF10 instance, it seems to start leaving connections open on writes which locks the table and that starts to block other connections. Even as connections start to close it causes a backup of writes to happen, eventually causing the other CF hosts/apps to break. I have to kill all connections from the CF10 host to stabilize the rest.

The only (obvious) thing I have done is updating the JDBC connector from the default one to the 5.0.8 version, but that hasn't helped.

Any ideas why CF10 would behave differently than CF8?

Cesar
  • 173
  • 6
  • 1
    Just having connections open shouldn't be able to impact your other servers, as having a connection open doesn't lock tables when there's no activity in that connection - when you say it's locking tables, what are you seeing that indicates that? What do the connections from the CF10 server look like in `show processlist;`? – Shane Madden Dec 16 '13 at 19:22
  • They show idle in show processlist, but the application server is recording lockwait timeouts on basic update queries that occur sub second on other servers. – Cesar Dec 16 '13 at 19:27
  • Looks like someone else has had the same issue: http://forums.adobe.com/message/5734181 The version of MySQL matches, would it make sense to try to update that? They also reported success turning off connection pooling ("Maintain connections"), see if that helps for you? – Shane Madden Dec 17 '13 at 07:12
  • We tried turning off the persistent connections, but it ended up crashing CF because garbage collection didn't seem to happen. Edit: I just read the username... That's one of my guys... heh. You can see how long we've been trying to figure this out. – Cesar Dec 17 '13 at 22:35
  • Heh! Fair enough. How about if you spin up a MySQL server on a newer version with a copy of the data, and see if the behavior is different at all on 5.1 or 5.5? And with the problems reclaiming connections, I wonder if requests are dying mid-transaction or something.. anything in the coldfusion exception log? – Shane Madden Dec 17 '13 at 23:01
  • I pointed the datasources to a test environment and used JMeter to load test it, but I was never able to reproduce the issue. – Cesar Dec 18 '13 at 05:36
  • Interesting. Can we get crazier and do a whole duplicate environment to try to reproduce the problem? And nothing in the exception log? – Shane Madden Dec 18 '13 at 07:05
  • Unfortunately, it is not feasible to do a full environment duplication for this. Nothing valuable in the exception log. A bunch of auth errors that were corrected (bad password initially configured). – Cesar Dec 18 '13 at 14:46
  • Hmm.. kinda hard to fiddle with different variables, in that case. I wonder if it'll somehow work right with only CF10 servers? Or maybe with an upgrade of the production MySQL? – Shane Madden Dec 18 '13 at 16:44
  • We were wondering if the old version of MySQL was causing the issue, but according to their docs, 5.0 is still supported with CF10. Doing the db upgrade is a bit of an undertaking though, so we may end up in a chicken/egg situation. – Cesar Dec 18 '13 at 16:47

1 Answers1

0

So after everything it turns out there was a change to the MySQL connector. For the version of MySQL that we are running we had to set "useLocalSessionState=false" in the jdbc connection settings. Once we did this, it all worked out perfectly.

Cesar
  • 173
  • 6