[Solved] Can't find datasource / fix "error in script file"

[RoryOF]

This is a bit embarrassing but here goes...

Sometime ago I went through the pain of creating a 'split' database on my 'work' computer. Everything worked brilliantly until last week when I tried to create a report. Base crashed and even though I tried to do the 'recovery' thing, I now have a weird problem. When I try and access the table of one of the databases I created earlier, I get this error...

The connection to the database source "2013 GTT Learners.file" could not be established

error in script file line: 65 java.io.EOFException in statement [SET TABLE PUBLIC."Students_in_courses" INDEX '9997 9645 9502 0 0 0 109']

The really embarrassing thing is that I can't locate the datasource on my computer. I teach at school and they have a weird system for backing up teacher work. To cut a long story short, I can't find the folder where the datasource might be (and have no idea where to look). The data is being saved somewhere - only I have no idea where this is and am feeling really stupid.

When the problem first occurred, I did a system restore and this fixed it - but the problem has come back and I'm looking for a more permanent fix. To further complicate matters, a different database (on the same machine) works fine.

I've tried 'unzipping' the file in an attempt to be able to access / edit the script file but this did not work (it let me see the xhtml for the tables / queries I'd written, but the actual data is no-where to be found). I've made a brief screencast showing the issue. If you have time / inclination you can see it below.

http://youtu.be/tK-7oePM-kE

Thanks in advance for any suggestions

Dzeni

[DACM]

Welcome Dzeni,

...I can't locate the datasource on my computer.

The Data source URL displayed at the bottom of your Base window indicates that the database is located at the default location:

users/public/databases/mydb

On the Windows 7 platform, this is most likely:

C:\Users\Public\databases\mydb

You should be keenly aware of this database folder location in order to perform/setup regular backups of the folder.

...a 'split' database...I get this error...

error in script file line: 65 java.io.EOFException in statement [SET TABLE PUBLIC."Students_in_courses" INDEX '9997 9645 9502 0 0 0 109'

It appears that you have an acute corruption of your mydb.script or mydb.data file. I'm not sure how a Base Report could cause this in a 'split database' configuration, but could you give us an idea of the size of your database? It appears that you have 109 or fewer records in the "Students_in_courses" table. The other numerals (INDEX '9997 9645 9502...) are "markers" likely indicating locations in your mydb.data file, but I have no idea how to troubleshoot those INDEX entries (as reflected in your mydb.script file...so a backup for comparison would be nice).

Here's some automatic recovery steps (that may or may not work in your case):

1. Locate any backup you may have of your database ('mydb') folder (we may need that to recover line 65 from a good mydb.script file)
2. Make a new backup or two of the 'mydb' database folder before proceeding...
3. Windows 7: Show File Extensions
4. Verify the existence of 'mydb.backup' in the 'mydb' folder
5. Open 'mydb.properties' in a basic text editor (Notepad)
6. Locate the line 'modified=no' and change to 'modified=yes'
7. Save the file and close the text editor
8. Delete mydb.data
9. Open your Base file: 2013 GTT Learners.file.odb in Base
10. Click on the Tables icon (this will cause the HSQLDB engine to rebuild 'mydb.data' from 'mydb.backup')
11. Report any errors
12. And if you get errors, then please post the contents of 'mydb.script' (assuming you're using CACHED tables, because if your using any MEMORY tables then the data from those MEMORY tables will be included in the mydb.script file...which should be deleted from any public post)

I also notice from your error that your schema is revealed: PUBLIC. Your video shows a Data source URL (at the bottom of your Base window) with 'default_schema=true' set, so I'm not sure how or why your system reveals the PUBLIC schema. So I'm wondering if you should attempt to "repair" your 'mydb.script' file (with backups available for immediate restoration of mydb.script as needed) by eliminating 'PUBLIC.' from your line 65 as reported above in the error...?

I've tried 'unzipping' the file

I suppose you mean you've unzipped your Base file: 2013 GTT Learners.file.odb. This file contains only Queries, Forms, Reports and Macros in a 'split database' configuration. Your data is located in separate mydb.* files within the .../mydb folder (mentioned above).

[nzdzeni]

Thanks so much for your help.

I managed to find my 'public' file with the help of our IT technician. Our school system is weird and whilst C drive exists, it's not labelled as such - which was part of the problem.

My initial database file was very small (around 1kb) which might mean it was corrupted. Luckily, an uncorrupted copy had been backed up on the school's server and I now have a copy of this.

I made a second backup in a separate folder (in case) and followed your steps.

Now the error says:

---

Code: Select all   Expand viewCollapse view

The connection to the data source "2013 GTT Learners.file" could not be established.

error in script file line: 39 file input/output errorerrororg.hsqldb.HsqlException:java.io.IOExpception: There is not enough space on the disk opening file - file /users/public/databases/mydb/mydb.data

---

Interestingly, the 'new' data file has 0 kb (the 'backup' is 160kb).

So I think my data is still in-tact but I can't connect to it just yet...

I'm not sure whether I'm using CACHED or MEMORY tables (step 12). How would I tell? I'm assuming sharing MEMORY tables would be a breach of security??? I think it's CACHED because the only information is the table headings / links between tables - there is no obviously private data although you can probably get quite a good idea of the database's architecture.

Line 39 is the one that talks about restarting the numbering on my "students in courses" table. This table records which kid is in which class. At the start of the year there was a fair bit of movement whilst students figured out their course schedules. This means that I ended up deleting some kids from the database (had it set up so that when a kid was deleted, it cascaded). Offhand, I'm not sure how many kids I should have on the table but the 127 id seems quite high (before we thought it might be 109 based on the error message). Of course, there could well be an id of 127 or more but only 109 entries as some kids have been deleted.

Problem is, I have very little idea of what I'm doing Here's the contents of the script file (which I'm afraid to edit!) ...

---

Code: Select all   Expand viewCollapse view

SET DATABASE UNIQUE NAME HSQLDB3C5B0FE781
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE SQL NAMES FALSE
SET DATABASE SQL REFERENCES FALSE
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL TYPES FALSE
SET DATABASE SQL TDC DELETE TRUE
SET DATABASE SQL TDC UPDATE TRUE
SET DATABASE SQL TRANSLATE TTI TYPES TRUE
SET DATABASE SQL CONCAT NULLS TRUE
SET DATABASE SQL NULLS FIRST TRUE
SET DATABASE SQL UNIQUE NULLS TRUE
SET DATABASE SQL CONVERT TRUNCATE TRUE
SET DATABASE SQL AVG SCALE 0
SET DATABASE SQL DOUBLE NAN TRUE
SET DATABASE SQL LONGVAR IS LOB FALSE
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
SET DATABASE TEXT TABLE DEFAULTS ''
SET DATABASE DEFAULT TABLE TYPE CACHED
SET FILES WRITE DELAY 500 MILLIS
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 8
SET FILES LOB SCALE 32
SET FILES DEFRAG 0
SET FILES NIO TRUE
SET FILES NIO SIZE 256
SET FILES LOG TRUE
SET FILES LOG SIZE 50
CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
ALTER USER SA SET LOCAL TRUE
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE CACHED TABLE PUBLIC."Students_in_Courses"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Student_ID" VARCHAR(10),"Course_ID" VARCHAR(10))
ALTER TABLE PUBLIC."Students_in_Courses" ALTER COLUMN "ID" RESTART WITH 127
CREATE CACHED TABLE PUBLIC."Results"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Student_ID" VARCHAR(10),"Standard_ID" VARCHAR(10),"Grade_ID" VARCHAR(3),"Comment" VARCHAR(200))
ALTER TABLE PUBLIC."Results" ALTER COLUMN "ID" RESTART WITH 54
CREATE CACHED TABLE PUBLIC."Standards"("Standard_ID" VARCHAR(10) PRIMARY KEY,"Topic" VARCHAR(20),"Description" VARCHAR(150),"Version" VARCHAR(2),"Level" VARCHAR(1),"Credits" INTEGER,"Course_ID" VARCHAR(10),"External" BIT(1))
CREATE CACHED TABLE PUBLIC."Parent_Contact"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Student_ID" VARCHAR(10),"Date" DATE,"Contact_Person" VARCHAR(30),"Description" VARCHAR(200))
ALTER TABLE PUBLIC."Parent_Contact" ALTER COLUMN "ID" RESTART WITH 143
CREATE CACHED TABLE PUBLIC."Missing_Time"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Student_ID" VARCHAR(10),"Course_ID" VARCHAR(10),"Date" DATE,"Time" INTEGER,"Reason_ID" VARCHAR(30),"Description" VARCHAR(200))
ALTER TABLE PUBLIC."Missing_Time" ALTER COLUMN "ID" RESTART WITH 423
CREATE CACHED TABLE PUBLIC."Standards_in_Courses"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Standard_ID" VARCHAR(10),"Course_ID" VARCHAR(10),FOREIGN KEY("Standard_ID") REFERENCES PUBLIC."Standards"("Standard_ID") ON DELETE CASCADE)
ALTER TABLE PUBLIC."Standards_in_Courses" ALTER COLUMN "ID" RESTART WITH 22
CREATE CACHED TABLE PUBLIC."Grades"("Grade_ID" VARCHAR(6) PRIMARY KEY,"Description" VARCHAR(25))
CREATE CACHED TABLE PUBLIC."Courses"("Course_ID" VARCHAR(50) NOT NULL PRIMARY KEY)
CREATE CACHED TABLE PUBLIC."Excuse_List"("Reason_ID" VARCHAR(15) PRIMARY KEY,"Description" VARCHAR(50),"Category" VARCHAR(10))
CREATE CACHED TABLE PUBLIC."Students"("Student_ID" VARCHAR(6) PRIMARY KEY,"Last_Name" VARCHAR(20),"First_Name" VARCHAR(20))
ALTER TABLE PUBLIC."Students_in_Courses" ADD FOREIGN KEY("Course_ID") REFERENCES PUBLIC."Courses"("Course_ID")
ALTER TABLE PUBLIC."Students_in_Courses" ADD FOREIGN KEY("Student_ID") REFERENCES PUBLIC."Students"("Student_ID") ON DELETE CASCADE
ALTER TABLE PUBLIC."Results" ADD FOREIGN KEY("Grade_ID") REFERENCES PUBLIC."Grades"("Grade_ID")
ALTER TABLE PUBLIC."Results" ADD FOREIGN KEY("Student_ID") REFERENCES PUBLIC."Students"("Student_ID") ON DELETE CASCADE
ALTER TABLE PUBLIC."Results" ADD FOREIGN KEY("Standard_ID") REFERENCES PUBLIC."Standards"("Standard_ID") ON DELETE CASCADE
ALTER TABLE PUBLIC."Parent_Contact" ADD FOREIGN KEY("Student_ID") REFERENCES PUBLIC."Students"("Student_ID") ON DELETE CASCADE
ALTER TABLE PUBLIC."Missing_Time" ADD FOREIGN KEY("Reason_ID") REFERENCES PUBLIC."Excuse_List"("Reason_ID")
ALTER TABLE PUBLIC."Missing_Time" ADD FOREIGN KEY("Student_ID") REFERENCES PUBLIC."Students"("Student_ID") ON DELETE CASCADE
ALTER TABLE PUBLIC."Standards_in_Courses" ADD FOREIGN KEY("Course_ID") REFERENCES PUBLIC."Courses"("Course_ID")
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
SET TABLE PUBLIC."Students_in_Courses" INDEX '9997 9645 9502 0 0 0 109'
SET TABLE PUBLIC."Results" INDEX '20355 17435 16940 20491 0 0 0 0 53'
SET TABLE PUBLIC."Standards" INDEX '5346 0 21'
SET TABLE PUBLIC."Parent_Contact" INDEX '16055 11299 0 0 130'
SET TABLE PUBLIC."Missing_Time" INDEX '18300 10732 12224 0 0 0 400'
SET TABLE PUBLIC."Standards_in_Courses" INDEX '10467 10407 10557 0 0 0 21'
SET TABLE PUBLIC."Grades" INDEX '5775 0 7'
SET TABLE PUBLIC."Courses" INDEX '5814 0 6'
SET TABLE PUBLIC."Excuse_List" INDEX '5906 0 12'
SET TABLE PUBLIC."Students" INDEX '6196 0 97'
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)


---

Thanks in advance for your suggestions!

[nzdzeni]

Never mind. I plucked up my courage, made multiple backups of both the .script and .data file and then played. Have no idea what did the trick but magically it's now working. "Playing" involved deleting the .script file (which did not do anything expect get rid of the error) and then replacing the deleted .script file with a backed up version. Also ended up putting two versions of my .data file into the folder, on called mydb.data and the second mydb.backup (both 160kb which had all the data).

I've learned a lot about backing up (and locating my datasource) so probably know just enough to be slightly dangerous.

If the 'fix' does not 'stick' - chances are, I'll be back.

Thanks so much for the clear explanations.

[DACM]

Well color me purple.

I assumed you were using the built-in HSQLDB 1.8.0.10, when in fact you had upgraded to HSQLDB 2.x. The steps I posted are not applicable to HSQLDB 2.x.

Luckily, an uncorrupted copy had been backed up on the school's server and I now have a copy of this.

Whew!

I'm not sure whether I'm using CACHED or MEMORY tables (step 12). How would I tell?

Notice the CREATE statement for each of your tables in the .script file you posted. Each table says CREATE CACHED...
Also notice that line 23 of your .script file reads: SET DATABASE DEFAULT TABLE TYPE CACHED

So I think my data is still in-tact but I can't connect to it just yet...

So if you simply replace the database folder with the backup and Run '2013 GTT Learners.file.odb' in Base, you get an error when clicking the Tables icon?

Edit: Just noticed the cross-post. Congratulations, you've recovered from disaster through [inadvertent] backup of your database folder. All readers please heed the advice to maintain historical versions of your database folder, which can be done automatically with free software or most free cloud-folder services. ...

[nzdzeni]

Thanks for the tip on how to see if a .script file is CACHED or MEMORY.

I don't get an error anymore and everything is working well - although every time I do anything my heart rate goes up for fear of crashing the thing. I tried a number of different things but did not keep a note of what I did - so can't explain how it got fixed. I should have tried replacing the entire folder (whoops) but that would have been too simple. I ended up putting in a 'good' copy of the mydb.data file and a 'good' copy of the mydb.backup file and with both files in the folder, it worked - have no idea why.

I will be regularly backing up the entire folder - so that my precious data is kept safe.

[RoryOF]

Do not backup anything, be it database or important file of any type, over the last backup, in case of corruption/system failure; Best to have rotating backups (Mon backup, Tues backup etc).

[DACM]

Do not backup anything, be it database or important file of any type, over the last backup...Best to have rotating backups (Mon backup, Tues backup etc).

Yes, that's good advice for manual backup purposes.

But the latest trend in automatic backup software and cloud-folders is file 'versioning' or file 'history' which automatically keeps multiple versions for retrieval in case of disaster (including the case RoryOF mentioned; inadvertent manual backup over the existing backup). That's why I mention the automatic 'versioning/history' feature in each case.

[nzdzeni]

A good point. I backed it up by copying the 'good' folder and then giving it a new name. I'm not too stressed because the data is held on C drive which means it automatically gets backed up to my school's servers so if everything turns to custard, my tech guy will be able to retrieve.