Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[FirebirdSQL] Error retrieving foreign keys #1844

Closed
sgauge opened this issue Jul 4, 2017 · 10 comments

Comments

Projects
None yet
3 participants
@sgauge
Copy link

commented Jul 4, 2017

Hello,

I've got this error message whenever I try to look at foreign keys. I have no issue with primary keys or indexes.

image

SQL Error: Statement state CURSOR_OPEN only allows next states [CLOSING, PREPARED, ERROR], received EXECUTING
java.sql.SQLNonTransientException: Statement state CURSOR_OPEN only allows next states [CLOSING, PREPARED, ERROR], received EXECUTING

Thx !

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 9, 2017

Please post error stacktrace (you can get it in Error Log view or in log file: https://github.com/serge-rider/dbeaver/wiki/Log-files)

@sgauge

This comment has been minimized.

Copy link
Author

commented Jul 9, 2017

Here we go :
dbeaver-debug.zip

Looks like there's another exception occuring :

2017-07-09 21:27:39.529 - org.jkiss.dbeaver.DBException: SQL Error [335544565] [22018]: **Cannot transliterate character between character sets** [SQLState:22018, ISC error code:335544565]
org.jkiss.dbeaver.DBException: SQL Error [335544565] [22018]: Cannot transliterate character between character sets [SQLState:22018, ISC error code:335544565]
	at org.jkiss.dbeaver.model.impl.jdbc.cache.JDBCStructCache.loadChildren(JDBCStructCache.java:176)
	at org.jkiss.dbeaver.model.impl.jdbc.cache.JDBCStructCache.getChildren(JDBCStructCache.java:220)
	at org.jkiss.dbeaver.ext.generic.model.GenericTable.getAttributes(GenericTable.java:149)
	at org.jkiss.dbeaver.ext.generic.model.GenericTable.getConstraints(GenericTable.java:173)
	at org.jkiss.dbeaver.ext.generic.model.ForeignKeysCache.fetchObject(ForeignKeysCache.java:126)
	at org.jkiss.dbeaver.ext.generic.model.ForeignKeysCache.fetchObject(ForeignKeysCache.java:1)
	at org.jkiss.dbeaver.model.impl.jdbc.cache.JDBCCompositeCache.loadObjects(JDBCCompositeCache.java:312)
	at org.jkiss.dbeaver.model.impl.jdbc.cache.JDBCCompositeCache.getObjects(JDBCCompositeCache.java:112)
	at org.jkiss.dbeaver.ext.generic.model.GenericTable.getAssociations(GenericTable.java:193)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.jkiss.dbeaver.model.navigator.DBNDatabaseNode.extractPropertyValue(DBNDatabaseNode.java:793)
	at org.jkiss.dbeaver.model.navigator.DBNDatabaseNode.loadTreeItems(DBNDatabaseNode.java:479)

@sgauge sgauge closed this Jul 9, 2017

@sgauge sgauge reopened this Jul 9, 2017

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 9, 2017

What firebird driver do you use? I can't reproduce this with 3.0.1 or any 2.x driver (you can see/change driver version in driver editor dialog->Download/Update).

@sgauge

This comment has been minimized.

Copy link
Author

commented Jul 10, 2017

3.0.1

image

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 10, 2017

Is there anything special about your database?

What if you will read table columns and referenced table columns first (before foreign keys)?

@sgauge

This comment has been minimized.

Copy link
Author

commented Jul 11, 2017

Hi serge, sorry for the late answer, I wanted to make some additional testing.

So yes, there's something with my database (and especially metadata), I had to back it up and restore it using the a "fix metadata" option. This solved the exceptions with DBeaver.

However, please note that this troublesome database does not generate any issue with other tools like FlameRobin or IBWorkbench.

As far as I'm concerned I will try to fix my production databases, but I think it would add to the tool robustness if it could cope with such errors. It's up to you if you want to keep this issue open. If you want to continue investigations, I will provide a link for you to download the faulty database.

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 11, 2017

I think this is a JDBC-specific issue. It happens during reading of table columns for primary key constraint.
I can't fix this (unless there is some workaround, e.g. changing sequence of metadata object loading).

@sgauge

This comment has been minimized.

Copy link
Author

commented Jul 11, 2017

Fair enough, I close the issue ;-)

@sgauge sgauge closed this Jul 11, 2017

@mrotteveel

This comment has been minimized.

Copy link

commented Apr 29, 2018

The error itself indicates that there was an inconsistent state within Jaybird itself: an attempt was made to execute a statement on a statement handle while it still had a serverside cursor open. The implementation shouldn't allow that as execution on a higher level in the implementation should trigger the close before proceeding with executing .

If you (still) have a reproducible case, I'd very much like to know (either on http://tracker.firebirdsql.org/browse/JDBC or https://github.com/FirebirdSQL/jaybird/issues).

I have created http://tracker.firebirdsql.org/browse/JDBC-531 to see if I can find a way to reproduce this.

@mrotteveel

This comment has been minimized.

Copy link

commented Sep 12, 2018

I managed to track down the problem. In cached result sets like used for metadata queries, the normal signalling doesn't work as these result sets are detached from the original statement. If an exception occurred while populating this cached result set, the cursor of the statement wasn't closed. As metadata statements are cached and reused, this then resulted in the exception when it was executed again.

Even if this problem had not existed, this would still have lead to an exception though given the corrupted character set of the metadata (which caused the error "Cannot transliterate character between character sets").

The statement state error will be fixed in Jaybird 3.0.6 and 4.0. The metadata error can only be fixed by fixing the underlying database.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.