since two weeks do my HSQLDB spontaneous stand (last week 2 days with 2-3 dayli). This CPU-Workload ist 100%. All DB-Connections to all db-files are killed. Clients have this error message: java.sql.SQLTransientConnectionException: connection exception: connection failure: java.io.EOFException.
I do not find my error. This DB-Files are ok. This filesizes are ca 60 MB. None exception in this hsqldb-terminal. SQL-Statements are ok. This software is since months at work.
I will morging start HSQLDB with event logging parameter: hsqldb.applog=1
Have Your a idea what it is?
Many thanks!
Larissa
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
See the org.hsqldb.jdbc.Util class source for the problems that throw this exception. These are generally client side or network problems.
Using applog=1 is always a good idea as it will show most serious errors on the database server side. But I think it won't show the reason for this problem. Check the process CPU and memory usage both for client and server processes. It may even be a memory issue on client or on the server.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
thanks for your quickly response! (as always!) I see this class org.hsqldb.jdbc.Util. Your has right. It must be network problem. This error is probably aftermach database is out of control. I wait und see together this applog.
Best regards, Larissa
Here is this stack trace for the exception:
Tue May 22 09:59:50 CEST 2012:
java.sql.SQLTransientConnectionException: connection exception: connection failure: java.io.EOFException
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
….
at lari…..
at lari…..
at lari…..
….
at tomcat…..
at tomcat…..
at tomcat…..
at tomcat…..
….
Caused by: org.hsqldb.HsqlException: connection exception: connection failure: java.io.EOFException
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ClientConnection.execute(Unknown Source)
… 40 more
Which version are you using? You should also use the same jar on client and server.
Find out which prepared statement is causing this (is it the same one or different ones) and report the statement together with the CREATE TABLE for the tables that it refers to.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
a logging this database do not show error source or this like. I would enabled a logging for sql per:
SET DATABASE EVENT LOG SQL LEVEL 2
This statement has none effect. Is this logging not available. My statements (selects and updates) are not write in this sql.log-file. It is correct? If yes, then will activate my programm-logging.
Many thanks for your support!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The EVENT LOG will probably not show this problem.
1. Did this problem start after you changed the HSQLDB version, or any other configuration?
2. Please use a try { … } catch (SQLException ..) block around the PreparedStatement execute call and find out which statement is doing this. Report the SQL for the statement and the CREATE TABLE for the tables used in the statement.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
1. This is a web application. This HSQLDB and Webserver have the same lib mit HSQLDB-Drivers. This configuration from HSQLDB is always the same.
2. In this web application have all methods for hsqldb-access (select or insert etc.) try and catch-blocks. The exceptions will write always in this logfile from my web application. This program do not throws a exception. However this processing from a sql-statement do make 100% cpu workload. This to occur not always (in the same statement). That is my problem.
This Exception up here (java.sql.SQLTransientConnectionException: ..java.io.EOFException) throws a client after this HSQLDB-process with 100% workload are killed from my (kill -9 <processId>). Others do'n have access to HSQLDB. This has occurred to me, in the meantime.
You cannot probably help me in my problem. I self must find a solution. Many thanks for your support!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This Problem start AFTER my changed the database to version 2.2.8. This version change is already older (start new version for 4 weeks, this problem start for 2 weeks). I do not think, that it is due. I do not know which sql statement is it. Ordinarily function all statements. Maybe it lies in the WHERE clause. I generate it at some places dynamically. Maybe gives with a customer at all the data constellation which causes the problem.
This statement 'SET DATABASE EVENT LOG SQL LEVEL 2' has not effect. This file <db_alias>.sql.log is created but empty. Why? I do not know it.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello Fred! Thanks for you information! After a process has 100% workload, I cannot connection to database. Wait, wait, wait… Today do this problem not occur. :-)
I find today a other solution: process tracing at Linux-Level
1. htop for determination this Main-PID and Child-PID from HSQLDB-process with 100% workflow
2. Here are find SQL-Statements and HSQLDB-files access:
strace -f -p <Child-PID> -o /tmp/trace_hsqldb_corrupt.txt
oder
strace -f -p <Main-PID> -o /tmp/trace_hsqldb_corrupt.txt
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am. :-) Today I have found the error souce. This is my java database procedure. The procedure is now ok. This was a java problem, not SQL-Statement. Because this was database procedure, it looked in such a way, as if it was a SQL error.
Thanks for you help!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I checked SET DATABASE EVENT LOG SQL LEVEL 2 and it was not stored in the .script file (because it is only used for debugging). I have now changed the code to store this setting in the .script file. Check the future version.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello Fred, hello altogether,
i have a big problem:
since two weeks do my HSQLDB spontaneous stand (last week 2 days with 2-3 dayli). This CPU-Workload ist 100%. All DB-Connections to all db-files are killed. Clients have this error message: java.sql.SQLTransientConnectionException: connection exception: connection failure: java.io.EOFException.
I do not find my error. This DB-Files are ok. This filesizes are ca 60 MB. None exception in this hsqldb-terminal. SQL-Statements are ok. This software is since months at work.
I will morging start HSQLDB with event logging parameter: hsqldb.applog=1
Have Your a idea what it is?
Many thanks!
Larissa
See the org.hsqldb.jdbc.Util class source for the problems that throw this exception. These are generally client side or network problems.
Using applog=1 is always a good idea as it will show most serious errors on the database server side. But I think it won't show the reason for this problem. Check the process CPU and memory usage both for client and server processes. It may even be a memory issue on client or on the server.
Also print the full stack trace for the exception and report it.
Hello Fred,
thanks for your quickly response! (as always!) I see this class org.hsqldb.jdbc.Util. Your has right. It must be network problem. This error is probably aftermach database is out of control. I wait und see together this applog.
Best regards, Larissa
Here is this stack trace for the exception:
Tue May 22 09:59:50 CEST 2012:
java.sql.SQLTransientConnectionException: connection exception: connection failure: java.io.EOFException
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
….
at lari…..
at lari…..
at lari…..
….
at tomcat…..
at tomcat…..
at tomcat…..
at tomcat…..
….
Caused by: org.hsqldb.HsqlException: connection exception: connection failure: java.io.EOFException
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ClientConnection.execute(Unknown Source)
… 40 more
This is Error-Message:
java.sql.SQLTransientConnectionException: connection exception: connection failure: java.net.SocketException: Broken pipe
Which version are you using? You should also use the same jar on client and server.
Find out which prepared statement is causing this (is it the same one or different ones) and report the statement together with the CREATE TABLE for the tables that it refers to.
Ok. If i find out this prepared statement, do i it.
My Clients and server has this self version: 2.2.8
Hello Fred,
a logging this database do not show error source or this like. I would enabled a logging for sql per:
SET DATABASE EVENT LOG SQL LEVEL 2
This statement has none effect. Is this logging not available. My statements (selects and updates) are not write in this sql.log-file. It is correct? If yes, then will activate my programm-logging.
Many thanks for your support!
The EVENT LOG will probably not show this problem.
1. Did this problem start after you changed the HSQLDB version, or any other configuration?
2. Please use a try { … } catch (SQLException ..) block around the PreparedStatement execute call and find out which statement is doing this. Report the SQL for the statement and the CREATE TABLE for the tables used in the statement.
My comments to:
1. This is a web application. This HSQLDB and Webserver have the same lib mit HSQLDB-Drivers. This configuration from HSQLDB is always the same.
2. In this web application have all methods for hsqldb-access (select or insert etc.) try and catch-blocks. The exceptions will write always in this logfile from my web application. This program do not throws a exception. However this processing from a sql-statement do make 100% cpu workload. This to occur not always (in the same statement). That is my problem.
This Exception up here (java.sql.SQLTransientConnectionException: ..java.io.EOFException) throws a client after this HSQLDB-process with 100% workload are killed from my (kill -9 <processId>). Others do'n have access to HSQLDB. This has occurred to me, in the meantime.
You cannot probably help me in my problem. I self must find a solution. Many thanks for your support!
OK, now I understand better.
Did this problem start BEFORE or AFTER you changed the database to 2.2.8?
The sql.log on the server will show which SQL statement is executed when this happens. You can report the lines at the end of this file.
This Problem start AFTER my changed the database to version 2.2.8. This version change is already older (start new version for 4 weeks, this problem start for 2 weeks). I do not think, that it is due. I do not know which sql statement is it. Ordinarily function all statements. Maybe it lies in the WHERE clause. I generate it at some places dynamically. Maybe gives with a customer at all the data constellation which causes the problem.
This statement 'SET DATABASE EVENT LOG SQL LEVEL 2' has not effect. This file <db_alias>.sql.log is created but empty. Why? I do not know it.
Hello Fred! I can sql logging in this my application, therefore is this statement 'SET DATABASE EVENT LOG SQL LEVEL 2' not really important.
I will look into the problem with EVENT LOG SQL later.
You can always check the currently active sessions and the statement they are executing. Try this when you get the large CPU load.
Connect to server with database manager and execute this statement.
select * from information_schema.system_sessions
Hello Fred! Thanks for you information! After a process has 100% workload, I cannot connection to database. Wait, wait, wait… Today do this problem not occur. :-)
I find today a other solution: process tracing at Linux-Level
1. htop for determination this Main-PID and Child-PID from HSQLDB-process with 100% workflow
2. Here are find SQL-Statements and HSQLDB-files access:
strace -f -p <Child-PID> -o /tmp/trace_hsqldb_corrupt.txt
oder
strace -f -p <Main-PID> -o /tmp/trace_hsqldb_corrupt.txt
Hello Fred!
I am. :-) Today I have found the error souce. This is my java database procedure. The procedure is now ok. This was a java problem, not SQL-Statement. Because this was database procedure, it looked in such a way, as if it was a SQL error.
Thanks for you help!
Hi Larissa
Glad you found it!
I checked SET DATABASE EVENT LOG SQL LEVEL 2 and it was not stored in the .script file (because it is only used for debugging). I have now changed the code to store this setting in the .script file. Check the future version.
Thank you very much!