Let's start with what we know:
- The content management system that we purchased is written in ColdFusion. CMS runs on Windows, Linux, and Solaris and it can use Oracle, SQL Server, or mySQL for its database.
- ColdFusion was a Macromedia product until they were purchased by Adobe. While there is a free developer/trail edition you can download, ColdFusion needs to be licensed for use, and that only covers installation support. You need to buy a support plan for any further assistance.
- ColdFusion has its own built-in web server that is unsupported for production deployments.
- ColdFusion is now a J2EE server application, and needs to run inside a full J2EE server (it's deployed as either an EAR or a WAR.
- SunOne is a full J2EE server, but is only supported as a web server and not an application server (which makes no sense). To deploy ColdFusion with SunOne, it needs to be run inside Adobe's JRun.
- The latest and greatest version of JRun uses Java 1.4.2_09, which is not compliant with the Energy Policy Act of 2005.
- ColdFusion connects to the database via JDBC. ColdFusion and JRun use Data Direct's JDBC Drivers to provide that connectivity.
- The latest and greatest version of ColdFusion, version 7.02, ships with version 3.3 of the Data Direct Drivers, which do not support Oracle 10gR2 (the latest and greatest version of Oracle). We had to update to Data Direct 3.5 to get ColdFusion working with Oracle 10gR2.
So far, not worth its own blog post, is it? Update the drivers and move on.
My last three weeks of problems have centered around
this Adobe technote about updating the JDBC drivers.
To install the new DataDirect database drivers, follow the instructions in the appropriate section below.
[...]
JRun 4
1. Stop JRun 4.
2. Backup your existing macromedia_drivers.jar file.
3. Unzip macromedia_drivers.zip into the same directory, overwriting the previous macromedia_drivers.jar.
A new file, DDJDBCAuth03.dll, which is required for Windows Authentication, is also included in the zip file replacing the older DDJDBCAuth.DLL.
Note: java.library.path must be modified (either in the jvm.config file or through the JMC) to include {application.home}/lib so that DDJDBCAuth03.DLL loads correctly.
4. Restart JRun 4.
JRun uses the JDBC drivers in macromedia_drivers.jar in /jrun4/lib. For example, with JRun 4 on Windows installed on drive C:, this would be C:\JRun4\lib\macromedia_drivers.jar.
This looks pretty straight forward, and only took me five minutes to complete. I toiled with the same performance problem I was having for another week or so until the CMS vendor provided a piece of test code that showed we were still running Data Direct 3.3.
::scratches head::
Remember up above where I said that ColdFusion is deployed as either an EAR or a WAR file? It turns out that the driver is actually in two places:
cmsadmin@zetes2$ find . -name macromedia_drivers.jar -print
./lib/macromedia_drivers.jar
./servers/cfusion/cfusion-ear/cfusion-war/WEB-INF/cfusion/lib/macromedia_drivers.jar
To really update the drivers, and not just hope the Java class loader picks the right one, I had to:
- Start JRun and ColdFusion, which explodes the archive and deploys the application.
- Find the compiled driver file inside the archive and replace it with the updated version.
- Shutdown and restart JRun and CF.
Lo and behold, suddenly my test code shows my data sources are running Data Direct 3.5; good start.
The actual problem that started this was installing the CMS demo site. Since this product is repository-independent (like every other chicken-shit vendors), the demo site is created by a bunch of CFQUERY tags inside the page you install from: You browse to page, point it to the database, push a button, and it makes with the CREATE TABLE...INSERT INTO TABLE...BEEP BEEP...BLINK BLINK...for a couple minutes until viola!
Only when I tried, it blew up. I reset and tried again, and it blew up. I turned on debugging, reset, tried again, and it blew up. The first day I had this problem, my install abended eleven times. Over the course of several weeks, no less than forty times: setup, go, explode.
The problem? After inserting about 1800 records, the database server would run to a point and then stop to wait for the web server to say "ready for more? let's go". While the database server waited for an ack from the web server, the web server sat there waiting for the database server to tell it "I finished inserting those records, what now?". Which, if you care, looks something like this:
He's watching football, she's doing Sudoku; neither one is really listening to the other.
The CMS vendor has no idea what the problem is, and in fairness they probably shouldn't. The problem is three layers of middleware down in the JDBC drivers that Macromedia (who is now owned by Adobe) licensed from Data Direct to bundle with JRun and ColdFusion.
Does your head hurt yet?
To further troubleshoot this, I had our DBAs do SQL traces on their side (which is where that screen shot came from) while I did JDBC traces on the web server side. Here's where the second problem in that technote bites me in the ass:
JDBCSpy built into the driver
Spy passes calls issued by a running application to an underlying DataDirect Connect for JDBC driver and logs detailed information about those calls, information you can use for troubleshooting problems. Configuration requires addingSpyAttributes to the JDBC URL through the "Connection String" field. [My emphasis] Unfortunately, due to bug 60098, the "Connection String" field doesn't work for Oracle, SQL Server, Sybase or Informix built-in drivers.
Yes, how unfortunate for me and everyone else in the world. What the fuck database
isn't on that list? Access?
Now I need to fist the enchanted goat in order to get any useful debugging information. Here's where it gets better:
For these drivers, users need to create an "Other" type data source and fill in all the data source parameters manually.
For ODBC Socket and DB2 (7.0.1):
Add the following parameters to the data source Connection String field: [...]
Seems helpful enough, until you realize that the parameters they've laid out for you aren't actually all the parameters that you need to fill in. I had to play trial-and-error for 25 minutes to guess what the correct parameters were. It gets even better because here is their example of an Oracle connect string:
For Oracle, SQL Server, Sybase and Informix:
JDBCSpy URL for "other" type data sources:
jdbc:macromedia:oracle://Server1:1521;serviceName=ORCL;SpyAttributes=(log=(file)C:\\temp\\spy.log;logTName=yes;timestamp=yes)
Wait for it...
The fucking syntax is wrong!
Barry found
Data Direct's Manual for their JDBC drivers on March 8th, so I dug through that to find that the parameter is
NOT serviceName=[] it's SID=[].
How hard would it be to add a screen shot so you know NOT to fill in the "Driver Name" with "Oracle"? If you do, it's no longer type "OTHER" and JDBCSpy won't log your transactions.
The log files it generated were as worthless as they were voluminous, except to show it stopped at exactly the same insert statement every single time:
spy(jrpp-3)(2007/03/21 17:48:10.004)>> Statement[1435].execute(String sql)
spy(jrpp-3)(2007/03/21 17:48:10.004)>> sql = INSERT INTO ControlInstance
(CONTROLID,CONTROLTYPE,CREATIONDATE,INHERITED,OWNERID,PAGEID,PARENTCONTROLID,PARENTCONTROLTYPE)
VALUES
(1541,26,'2004-06-15 10:43:34',0,1000002,857,1185,1)
spy(Finalizer)(2007/03/21 17:49:54.043)>> Connection[3].setAutoCommit(boolean autoCommit)
spy(Finalizer)(2007/03/21 17:49:54.043)>> autoCommit = true
spy(Finalizer)(2007/03/21 17:49:54.043)>> java.sql.SQLException: [Macromedia][Oracle JDBC Driver]Object has been closed. ErrorCode=0 SQLState=HY000
java.sql.SQLException: [Macromedia][Oracle JDBC Driver]Object has been closed.
at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source)
at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source)
at macromedia.jdbc.base.BaseConnection.setAutoCommit(Unknown Source)
at macromedia.jdbcspy.SpyConnection.setAutoCommit(Unknown Source)
at coldfusion.server.j2ee.sql.JRunConnection.setAutoCommit(JRunConnection.java:394)
at coldfusion.server.j2ee.sql.JRunConnection.clean(JRunConnection.java:223)
at coldfusion.server.j2ee.sql.JRunConnection.close(JRunConnection.java:433)
at coldfusion.server.j2ee.sql.pool.JDBCPool.expire(JDBCPool.java:695)
at coldfusion.server.j2ee.pool.ObjectPool.closeAllResources(ObjectPool.java:292)
at coldfusion.server.j2ee.sql.pool.JDBCPool.closeAllResources(JDBCPool.java:884)
at coldfusion.server.j2ee.pool.ObjectPool.finalize(ObjectPool.java:312)
at java.lang.ref.Finalizer.invokeFinalizeMethod(Native Method)
at java.lang.ref.Finalizer.runFinalizer(Finalizer.java:83)
at java.lang.ref.Finalizer.access$100(Finalizer.java:14)
at java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:160)
Somewhere, somehow, some resource on the database server is being exhausted by a fairly small number of inserts and we don't know what resource or why because both servers eventually stare off into space waiting for the other to make the next move.
In the end, I gave up and had the vendor walk me through cloning the demo site from our production system and hand-jamming it into test.
Oh, did I forget to mention that this ran flawlessly on our production system? That's right, I didn't because I found another bug in CMS. We installed the demo site into /opt/SUNWwbsvr/docs but when the installer ran (and blew up) it had created /opt/sunwwbsvr/docs. Solaris is case sensitive, and a developer had jammed in an lcase() function that non-narcissistic applications like Apache forgive. Sun has to make their name CAPS in all their products.
What a fucking waste of time. Adobe's documentation staff need to bathe in napalm.