SchemaSpy - Error message while generating the report

Question

I am using SchemaSpy (5.0.0) to generate a report of our Informix database. While running it I get the following error during the last step of 'Gathering schema details':

java.sql.SQLException: The cursor has been previously released and is unavailable.
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3187)
    at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3467)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2378)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2303)
    at com.informix.jdbc.IfxSqli.executeFetch(IfxSqli.java:2037)
    at com.informix.jdbc.IfxSqli.getaRow(IfxSqli.java:4077)
    at com.informix.jdbc.IfxResultSet.next(IfxResultSet.java:494)
    at com.informix.jdbc.IfxDatabaseMetaData.getImportedKeys(IfxDatabaseMetaData.java:5524)
    at net.sourceforge.schemaspy.model.Table.connectForeignKeys(Table.java:106)
    at net.sourceforge.schemaspy.model.Database.connectTables(Database.java:984)
    at net.sourceforge.schemaspy.model.Database.<init>(Database.java:83)
    at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:211)
    at net.sourceforge.schemaspy.Main.main(Main.java:42)
Caused by: java.sql.SQLException
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3472)

I am using the Informix JDBC driver in version 4.1.0 (also tried 4.10.8.1). Strangely this driver also needs the BSON library.

Any idea what's the problem?

score 3 · Answer 1 · answered Mar 03 '17 at 19:39

3

Agree 100% with Jonathan. This should be reported to IBM Technical Support. IBM releases about 3 or Fixpacks a year, and I see no reason for Informix 12.10 user to be penalized. It would be great to open a service request. I love schemaSpy!

Eric

answered Mar 03 '17 at 19:39

Begooden IT

41
2

Is there a fix available for the above issue ? Or is there informix ticket to track this ? We have a same issue with the new informix driver 4.10.JC9 We run the initial queries through Liquibase. No issues with the older version of informix driver 3.70.JC5 Any help is appreciable – Vikram Alva Aug 17 '17 at 06:31
@VikramAlva yes, you can download the 4.50.1 driver which finally fixes this issue. See my answer posted in here on the details. – Brian Hughes Apr 03 '19 at 18:25

score 2 · Answer 2 · answered Feb 28 '17 at 13:04

2

For future reference I could solve the problem by replacing the above driver 4.X with an older version. Seems that driver is buggy or SchemaSpy is not compatible with the newer 4.X drivers. The driver which worked was the latest 3.X driver (3.70.JC8 to be exact).

answered Feb 28 '17 at 13:04

Lonzak

9,334
5
57
88

2

Good discovery. This issue should really be reported to IBM/Informix Tech Support if at all possible. Back-door channels are not as good as actual customer reports. – Jonathan Leffler Mar 01 '17 at 00:50

score 0 · Answer 3 · answered Aug 17 '17 at 10:31

Looking at the stack the server is failing to do a select and returns an error to the client when it tries to retrieve the list of primary keys:

java.sql.SQLException: The cursor has been previously released and is unavailable.
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3187)
    at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3467)    <--- means error ;)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2378)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2303)
    at com.informix.jdbc.IfxSqli.executeFetch(IfxSqli.java:2037)
    at com.informix.jdbc.IfxSqli.getaRow(IfxSqli.java:4077)
    at com.informix.jdbc.IfxResultSet.next(IfxResultSet.java:494)
    at com.informix.jdbc.IfxDatabaseMetaData.getImportedKeys(IfxDatabaseMetaData.java:5524)

I did a quick test with 4.10.JC8 in one of my servers against the stores_demo database and it completed without a glich:

d:\JJTMP>java -jar \jjtmp\schemaSpy_5.0.0.jar -t informix -db stores7 -s informix -u informix -p password -o \jjtmp -host 420ito:9088 -server ids1210 -dp \infx\jdbc410jc8x\lib\ifxjdbc.jar
Using database properties:
  [\jjtmp\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/informix.properties
Gathering schema details....................................................................(4sec)
Writing/graphing summary..............(7sec)
Writing/diagramming details.................................................................(6sec)
Wrote relationship details of 65 tables/views to directory '\jjtmp' in 19 seconds.
View the results by opening \jjtmp\index.html

d:\JJTMP>

If you get a SQLI trace you should be able to see why that particular SQL is failing. Add the SQLIDEBUG property to the connection string in the schemaSpy command line to generate one. Something like this:

d:\JJTMP>java -jar \jjtmp\schemaSpy_5.0.0.jar -t informix -db stores7 -s informix -u informix -p password -o \jjtmp -host 420ito:9088 -server ids1210 -dp \infx\jdbc410jc8x1\lib\ifxjdbc.jar -connprops SQLIDEBUG\=trace
Using database properties:
  [\jjtmp\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/informix.properties
Gathering schema details....................................................................(4sec)
Writing/graphing summary..............(7sec)
Writing/diagramming details.................................................................(6sec)
Wrote relationship details of 65 tables/views to directory '\jjtmp' in 18 seconds.
View the results by opening \jjtmp\index.html

d:\JJTMP>dir trace*
 Volume in drive D is Data750
 Volume Serial Number is F0B7-2E46

 Directory of d:\JJTMP

17/08/2017  11:10         2,008,706 trace1502964639886.0
               1 File(s)      2,008,706 bytes
               0 Dir(s)  284,868,997,120 bytes free

Then you can decode the trace file using the sqliprint/sqliprt CSDK tool (it should be on the engine bin directory)

d:\JJTMP>sqliprt -notimestamp trace1502964639886.0 > trace.txt

d:\JJTMP>

But as everyone said, it may be a good idea to open a ticked with tech support. It could be specific to your database/schema (wonder if it fails against other databases) but even so getImportedKeys() shouldn't fail with that error no whatever the application does.

score 0 · Accepted Answer · answered Apr 03 '19 at 18:24

I just answered this similar question here https://stackoverflow.com/a/55458418/8334122. The copy of that answer is below.

I had to get some help tracking down this as it's a timing issue + bug in the driver.

The problem here is with some versions of the Informix JDBC driver. Quick fix is to upgrade to the latest 4.50.1 driver. It's out on maven.

<!-- https://mvnrepository.com/artifact/com.ibm.informix/jdbc -->
<dependency>
    <groupId>com.ibm.informix</groupId>
    <artifactId>jdbc</artifactId>
    <version>4.50.1</version>
</dependency>

In detail. The 4.10.X drivers introduced a statement cleaner thread that runs every 15 seconds and looks for resources that need to be closed as they have no more references and could be garbage collected. The problem, then compounds as a couple JDBC metdata calls incorrectly failed to close resources causing this cleaner thread to close resources in the middle of the work sometimes. Hence some people see it often, some never. You can work around it with 4.10.JC12 where we added a connection flag to disable the cleaner thread (IFMXCONNECTION_CLEANER_THREADS=0). Or the best option is to upgrade to the newest 4.50.1 driver which has this issue properly fixed

SchemaSpy - Error message while generating the report

4 Answers4