Ask Your Question
1

Porting database to 5.3

asked 2017-06-16 18:24:05 +0100

dsc3507 gravatar image

updated 2017-06-19 06:18:24 +0100

I am using Ubuntu 14.04 and a 4.2.8.2 embedded hsqldb database. I want to port over to LO 5.3. I have tried on both Linux 16.04 and win7 with 5.3 loaded and I get the same error on both:

-SQL Status: S1000 Error code: -40 General error: org.hsqldb.lib.FileSystemRuntimeException: java.io.IOException:

Java is up to date and loaded and checked in options.

when selecting the table. Is there a fix or way to do this?

Here is the script file in question from the last comment -

SET DATABASE COLLATION "Latin1_General"

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

CREATE CACHED TABLE "chs68"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FirstName" VARCHAR(255),"LastName" VARCHAR(255),"SpouseName" VARCHAR(255),"Address" VARCHAR(255),"City" VARCHAR(255),"State" VARCHAR(255),"PostalCode" VARCHAR(255),"HomePhone" VARCHAR(255),"WorkPhone" VARCHAR(255),"CellPhone" VARCHAR(255),"EmailAddress" VARCHAR(255),"NumberAttending" VARCHAR(255),"GuestsID" VARCHAR(255),"Address2" VARCHAR(255),"NewLastname" VARCHAR(255),"SendMail" VARCHAR(255),"PlanAttend2008" VARCHAR(255),"MyElementary" VARCHAR(255),"MyJunior" VARCHAR(255),"class photo" VARCHAR(100),"PersonalComment" VARCHAR(255),"Deceased" BOOLEAN,"Contact2008" BOOLEAN,"Contact2018" BOOLEAN,"EmailOK" BOOLEAN,"Comments" VARCHAR(255),"Committee" BOOLEAN,"NeverFound" BOOLEAN,"EmailNotConfirmed" BOOLEAN,"PlanAttend2018" BOOLEAN)

SET TABLE "chs68" INDEX'59664 544'

ALTER TABLE "chs68" ALTER COLUMN "ID" RESTART WITH 544

CREATE USER SA PASSWORD ""

GRANT DBA TO SA

SET WRITE_DELAY 60

and here is the script once the new DB was working under split wizzard -

SET DATABASE UNIQUE NAME HSQLDB5CBE196A76

SET DATABASE GC 0

SET DATABASE DEFAULT RESULT MEMORY ROWS 0

SET DATABASE EVENT LOG LEVEL 0

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 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 UNIQUE NULLS TRUE

SET DATABASE SQL CONVERT TRUNCATE TRUE

SET DATABASE SQL AVG SCALE 0

SET DATABASE SQL DOUBLE NAN TRUE

SET FILES WRITE DELAY 500 MILLIS

SET FILES BACKUP INCREMENT TRUE

SET FILES CACHE SIZE 10000

SET FILES CACHE ROWS 50000

SET FILES SCALE 32

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

ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1

SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC

GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC

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 DBA TO SA

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2017-06-16 19:01:44 +0100

Ratslinger gravatar image

You state Java is checked in options. Does this mean you can see the version in the window below Use Java runtime environment? If this is not seen:

image description

you do not have the correct Java installed. The Java bit size must match LO. 32-bit LO uses 32-bit JRE and 64-bit LO needs 64-bit JRE.

edit flag offensive delete link more

Comments

Yes, I have 1.8.0_131 in that window and checked. Also LO info: Version: 5.3.3.2 (x64) Build ID: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448 CPU Threads: 2; OS Version: Windows 6.1; UI Render: default; Layout Engine: new; Locale: en-US (en_US); Calc: group I guess my question is should this work? Should one be able to bring a DB from an older version of LO to a newer and no have problems, at least not total failure problems?

dsc3507 gravatar imagedsc3507 ( 2017-06-17 05:12:13 +0100 )edit

Today I still have & use Base files created in Ubuntu 14.04 with LO 4.2.8 on a Mint 18 system using LO 4.3.x (and other distros) without problem. I can't be sure, but I seem to recall a problem with Base around v4.1.x or 4.2.x but cannot find anything right now. Are you using any macros or connected to an event on DB open? Can't tell much without your .odb.

Ratslinger gravatar imageRatslinger ( 2017-06-17 05:18:18 +0100 )edit

I might add some additional info - the messages I get are:

The connection to the data source "chs68-50th" could not be established. AND org.hsqldb.lib.FileSystemRuntimeException: java.io.IOException:

I am moving this version 4 LO database to a new directory on a different computer. I have updated the database entry in options. I have also created a new simple test database in the new code and it seems to work fine. So I suspect that discounts any java issue.

dsc3507 gravatar imagedsc3507 ( 2017-06-17 05:30:14 +0100 )edit

Please note: currently using on Mint 18 with LO v5.3.2.2 and not v4.3!

Ratslinger gravatar imageRatslinger ( 2017-06-17 05:32:03 +0100 )edit

I am not doing anything unusual. No macros, no events. I think I will create a very simple test DB in the old system and see if it to will not read in the new version and report back.

dsc3507 gravatar imagedsc3507 ( 2017-06-17 05:32:42 +0100 )edit

Yes, that makes a difference. When you first open this .odb, in the lower left corner does it state Embedded database? If so, by any chance, in your LO v4.2.8.2 did you have any additional paths set?

Ratslinger gravatar imageRatslinger ( 2017-06-17 05:38:16 +0100 )edit

OK, I created a very simple 5 field 1 record database in the 4.1 LO base. Moved it over and it plays fine. I am not sure that proves anything other than you can port. Obviously it does not like something in my larger 540 record database. One thing I do is link images but I did move over the image directory in the same path under the database. It would be great if there was some kind of test for a database for any possible errors. This might be very hard to find. Any tips on what to do?

dsc3507 gravatar imagedsc3507 ( 2017-06-17 05:44:34 +0100 )edit

Yes, in answer to your question it does read embedded database. Am I out of luck because of that? What determines an embedded database when it is created? I am not storing any photos in the database at least I don't think so . I started out doing that an quickly realized it was not a good idea and deleted that field and went to linked photos. Maybe I should copy the database and try deleting the photo links?

dsc3507 gravatar imagedsc3507 ( 2017-06-17 06:14:43 +0100 )edit

Linked images would not create a problem. Embedded would be a big problem. The next thing I would try is to split (you did say this was embedded) out the database. On this post (click here, in my answer, I have created a procedure to take a broken DB and possibly create a working split DB from it. It is not difficult. Just follow step-by-step and use a COPY of your odb

Ratslinger gravatar imageRatslinger ( 2017-06-17 06:19:42 +0100 )edit

FYI - An embedded DB means that the data is zipped into the .odb file when closed and unzipped when the .odb is opened. This is a potential for trouble as this process has been known to corrupt data. A split DB will greatly reduce this type of problem as the data is resident in its own sub-directory. This is similar to other DB's such as MySQL and PostgreSQL where data is stored separately. Should the above split work, I suggest you retain that set-up.

Ratslinger gravatar imageRatslinger ( 2017-06-17 06:26:18 +0100 )edit
0

answered 2017-06-19 19:26:53 +0100

dsc3507 gravatar image

The answer to this question is in all the comments and the kind help from "ratslinger" getting me through all the machinations of getting an embedded DB split and running. I hope this helps others in the same situation.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-06-16 18:24:05 +0100

Seen: 93 times

Last updated: Jun 19