[BUG] Startup Error - Githubissues

Cold-Burn commented 4 months ago

Hello all,

I'm trying to open my database on the latest released stable version HO-8.0.645.2-portable-win-JRE. HO when loading delivers the following startup error:

java.sql.SQLException: IO error: RowInputBinary 15925268 at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source) at core.db.ConnectionManager.executePreparedQuery(ConnectionManager.kt:73) at core.db.ConnectionManager.executePreparedQuery(ConnectionManager.kt:63) at core.db.AbstractTable.executePreparedSelect(AbstractTable.java:452) at core.db.AbstractTable.load(AbstractTable.java:189) at core.db.MatchLineupPlayerTable.getMatchLineupPlayers(MatchLineupPlayerTable.java:167) at core.db.DBManager.getMatchLineupPlayers(DBManager.java:674) at core.model.match.MatchLineupTeam.loadLineup(MatchLineupTeam.java:778) at core.db.DBManager.loadMatchLineupTeam(DBManager.java:2294) at core.db.DBManager.loadLineup(DBManager.java:2311) at core.db.DBManager.loadNextMatchLineup(DBManager.java:2306) at core.model.HOModel.(HOModel.java:121) at core.model.HOVerwaltung.loadModel(HOVerwaltung.java:193) at core.model.HOVerwaltung.loadLatestHoModel(HOVerwaltung.java:117) at core.HO.main(HO.java:197) at core.HOLauncher.main(HOLauncher.java:81) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at com.exe4j.runtime.LauncherEngine.launch(LauncherEngine.java:84) at com.exe4j.runtime.WinLauncher.main(WinLauncher.java:94) at com.install4j.runtime.launcher.WinLauncher.main(WinLauncher.java:25) Caused by: org.hsqldb.HsqlException: IO error: RowInputBinary 15925268 at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.rowio.RowInputBinary.readInt(Unknown Source) at org.hsqldb.index.NodeAVLDisk.(Unknown Source) at org.hsqldb.RowAVLDisk.(Unknown Source) at org.hsqldb.persist.RowStoreAVLDisk.get(Unknown Source) at org.hsqldb.persist.DataFileCache.getFromFile(Unknown Source) at org.hsqldb.persist.DataFileCache.get(Unknown Source) at org.hsqldb.persist.RowStoreAVLDisk.get(Unknown Source) at org.hsqldb.index.NodeAVLDisk.findNode(Unknown Source) at org.hsqldb.index.NodeAVLDisk.getRight(Unknown Source) at org.hsqldb.index.IndexAVL.findNode(Unknown Source) at org.hsqldb.index.IndexAVL.findFirstRow(Unknown Source) at org.hsqldb.RangeVariable$RangeIteratorMain.getFirstRow(Unknown Source) at org.hsqldb.RangeVariable$RangeIteratorMain.initialiseIterator(Unknown Source) at org.hsqldb.RangeVariable$RangeIteratorMain.next(Unknown Source) at org.hsqldb.QuerySpecification.buildResult(Unknown Source) at org.hsqldb.QuerySpecification.getResult(Unknown Source) at org.hsqldb.StatementQuery.getResult(Unknown Source) at org.hsqldb.StatementDMQL.execute(Unknown Source) at org.hsqldb.Session.executeCompiledStatement(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) ... 24 more Caused by: java.io.EOFException at org.hsqldb.lib.HsqlByteArrayInputStream.readInt(Unknown Source) ... 44 more

When I click OK on the error message it quits HO process.

Note: the database comes from version HO-7.2.510.2-portable-win-JRE.

Please let me know if you need further details.

Thank you.

wsbrenk commented 4 months ago

@Cold-Burn I'm afraid your database is corrupt. Try to restore a backup which are stored in zip-files in the database folders.

Cold-Burn commented 4 months ago

@Cold-Burn I'm afraid your database is corrupt. Try to restore a backup which are stored in zip-files in the database folders.

So if I open in old version I don't have a corrupted database and when I open in the new version I have a corrupted database? I also tried the zip files with same result.

Is there a toolfix for these cases?

wsbrenk commented 4 months ago

@Cold-Burn could you please upload one zip backup to this thread. then i could try to debug.

Cold-Burn commented 4 months ago

Hi Wsbrenk.

Due to size I have uploaded the file to: https://mega.nz/file/Ln5XXRwT#HnHHl2OLxFiu2Na3Lm20hnvINIKwk4E-p14c_bs6Fro

Password to open 7z file: g3Xj6/2D%p

Thank you.

wsbrenk commented 3 months ago

Got it.

wsbrenk commented 3 months ago

Looks like the DB is corrupt at first debugging. In fact, HO7 starts because it handles the specified exception. However, no lineups are loaded and displayed in HO7 either.

wsbrenk commented 3 months ago

@Cold-Burn if you will have a look at your log files of a HO7 session, you should find such error messages too - isn't it?

wsbrenk commented 3 months ago

@tychobrailleur ConnectionManager.executePreparedQuery does not handle this sql exception thrown by preparedStatement.executeQuery(). I'm not sure if it is a good idea to implement a handler (catch) - what do you think?

Cold-Burn commented 3 months ago

@Cold-Burn if you will have a look at your log files of a HO7 session, you should find such error messages too - isn't it?

I do find this row "java.sql.SQLException: IO error: RowInputBinary 15925268" at log file, but I'm not sure if it is exactly this that you want me to look for. Log file has 15mb.

wsbrenk commented 3 months ago

@Cold-Burn Yes, this is exactly what I meant. So the HO7 database is already corrupted (match table).

I think, the only chance is to create a new database. The only data which will be lost should be the trainings of the past, i remember. You have to restore this trainings manually. All other data should be reloaded from hattrick or could be restored by recalculating the subskills.

Cold-Burn commented 3 months ago

Hi,

I created a new database on version 8.1.655.2, after I've imported all hrf files. For some reason somethings aren't loaded: Previous games Older series Stadium attendances

Some other things might still be missing as I'm still testing this version.

Any suggestion to have this data available?

Thank you.

wsbrenk commented 3 months ago

Importing old hrf files might not be the best way to get back the old data (hrfs for instance doesn't store any match information).

The Download-Option allows to reload old series. As I said - the only data I remember which will not be reloaded are the training settings. They are important for the correct subskill calculation. So you have to "remember" those settings and restore them manually.

masterpatje commented 3 months ago

When you download a hrf file you can download previous seasons in the right part.

After you downloaded the previous seasons you can go the tab with the match history and there is an option there to download previous matches.

Good luck!

Cold-Burn commented 3 months ago

For now I'll keep both versions and test further the version 8.1.655.2. Nevertheless it's a pity that some information/historic is lost in the updates between versions 7 to 8, which is strange considering that my database was coming all the way from HO! ver. 1.

wsbrenk commented 3 months ago

@Cold-Burn It's a pity loosing data - i confirm this. But the reason for your situation is not the update. The update migrated your corrupt database to a new corrupt database. The new version of course is no longer tolerant accessing the corrupted table as the old version was.

We have two options:

Make the HO8 tolerant as the HO7 version was - you can not access any match data further on.
Remain intolerant, which would force you to restore a non corrupted backup of your database as soon as your database gets corrupted again (which may happen if your HO process is killed)

I would prefer option 2. What does @tychobrailleur mean?

tychobrailleur commented 3 months ago

I would prefer option 2. What does @tychobrailleur mean?

I am not entirely sure I understand the difference “tolerant” vs. “intolerant” here?

tychobrailleur commented 3 months ago

@tychobrailleur ConnectionManager.executePreparedQuery does not handle this sql exception thrown by preparedStatement.executeQuery(). I'm not sure if it is a good idea to implement a handler (catch) - what do you think?

Generally speaking we would need to handle SQL exceptions better, but I am not sure what a good handling of those exceptions would be, especially in the situation where the hsql DB is corrupt. It would possibly allow us to display a nice-looking message “database corrupt,” but that's about it?

We never really got into the root causes for these db corruption issues, and they seem to be mostly cause by database updates across versions. In any case it is more than likely caused by the DB not being properly closed, either because HO crashed, or was killed because hanging.

We should think of additional strategies to avoid those corruptions:

Ensure that the database changes are minimum, and backward-compatible (no rename, no column or table dropping, only additions)
Maintain an alternative storage format (e.g. non-binary file, other db), with the obvious caveat that this would be space-greedy (unless we only store a snapshot of the current state)
Make the installation process smarter, and create backups prior to installing
Make more backups and at specific points when using HO
Other?

A long time ago I had started to look into creating a tool to recover corrupt DBs, but this proved pretty much impossible to maintain due to the changes of version, and the tables that are in the corrupted blocks are lost anyway, so that data is gone. So probably not the right approach.

wsbrenk commented 2 months ago

I am not entirely sure I understand the difference “tolerant” vs. “intolerant” here?

@tychobrailleur in HO 7 the java sql exception was "handled" by ignoring the exception. The user never recognized this error if he doesn't look into the match panels. The new implementation does not catch the sql exception which leads to the error message shown in this ticket.

For me the advantage of the current solution is, that the user immediatly recognizes if his database gets corrupted. And he should restore a backup of a correct database backup before all backups are overridden by damaged database instances.

Later workflow could eventually be supported by a sql error handling. maybe not only showing the stacktrace, but asking the user to restore one of his backups.

ho-dev / HattrickOrganizer

[BUG] Startup Error #2118