LeonardSchwenk commented 2 years ago

Hello, if got a firebird database file lets call it mydatabase.fdb. The database contains around 1 GB of data and has quite a lot of tables.

On my Windows machine(in the cloud) I can access the database and the tables. I downloaded firebird 3.0 server and installed it.

C:\Program Files\Firebird\Firebird_3_0>isql 
SQL> CONNECT mydatabase.FDB
CON> user 'sysdba' password 'mySecretPW';
SQL> Select first 10 * from MyTable;

And i get the output of the table.
Works

Now I tried to connect to my docker container and connect to mydatabase.fdb.

My docker-compose.yml

version: '3'

services:
  db:
    platform: linux/amd64
    image: jacobalberty/firebird
    environment:
      ISC_PASSWORD: firebird
      FIREBIRD_DATABASE: mydatabase.FDB

    ports:
        - 3050:3050

RUN: docker-compose up

I switch to dbeaver with jaybird 4.0 connect to the DB all goes correct.

But I cannot see my tables or select from them.

When I cli into the docker container I can see the file under /firebird/data.

When I try the following from within the container I get the following error:

RUN:
"${PREFIX}"/bin/isql
SQL> CONNECT mydatabase.FDB

CON> user 'sysdba' password 'firebird';
Database: mydatabase.FDB, User: SYSDBA
SQL>  Select first 10 * from MyTable;

ERROR:
Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -204
-Table unknown
-PERSONAL
-At line 1, column 30

I've also tried the same with the firebird tag 3.0 But then i get the following error:


Statement failed, SQLSTATE = HY000
unsupported on-disk structure for file /firebird/data/MAND2.FDB; found 13.0, support 12.2

So it seems the latest dock-file works but I cannt see my tables or select from them.

Info: Locally im running macOS silicon but I dont think it depends on that as I run docker with platform: linux/amd64 also tried x86.

Appreciate any help.

mrotteveel commented 2 years ago

Did you restore the database, or did you just copy the file? The file format on Linux is different than on Windows, so you need a transportable backup (transportable is the default) and restore that for a database to work. You cannot just copy a database file between platforms.

LeonardSchwenk commented 2 years ago

Oh wow. Copied the file. Can i change the already copied file to a transportable file? How to I create a transportable file from windows ? Appreciate the help.

mrotteveel commented 2 years ago

No, the database file itself is not transportable. You need to create a backup with gbak on Windows (using gbak from the Windows Firebird install), and restore it with gbak (using gbak of the Docker image, or using a remote restore through the service manager).

The second error ("unsupported on-disk structure for file /firebird/data/MAND2.FDB; found 13.0, support 12.2") is caused by attempting to open a Firebird 4.0 database with Firebird 3.0.4 (or a higher 3.0.x).

LeonardSchwenk commented 2 years ago

I managed to backup my database with gbak. Is it possible to restore the database backup on startup of the docker container or from docker compose ? As its not that pleasant to cli into the container extract the file and then work with it.

LeonardSchwenk commented 2 years ago

I am not able to restore the backup from within the docker container.

Backup command: gbak -b -v mydatabase.FDB mydatabase_backup.fbk -transportable -user SYSDBA -pass secretPw Restore command: "${PREFIX}"/bin/gbak -c -user SYSDBA -password secretPw mydatabase_backup.fbk mydatabase_restore.fdb ERROR:

gbak: ERROR:expected backup description record
gbak:Exiting before completion due to errors

jacobalberty commented 2 years ago

I like the idea of an auto restore on first launch (or maybe even be able to load a new backup in and have it restore @LeonardSchwenk do you have any thoughts on how that might work/behave?

LeonardSchwenk commented 2 years ago

An enviroment variable would be great like FIREBIRD_BACKUP. Needed with FIREBIRD_BACKUP_USER AND FIREBIRD_BACKUP_PASSWORD. (not sure how to use option or just take standard backup and restore options)

FIREBIRD_DATABASE would be skipped.

In the docker-entrypoint.sh gbok would be used to restore the backup database. (should be replaced) Then use the restored database as it would be a set FIREBIRD_DATABASE.

Im still not able to restore the database from within the docker container.

I tried to connect to the database on windows with firebird 4.0 and isql:

Got the following error:

Statement failed, SQLSTATE = HY000
unsupported on-disk structure for file C:\USERS\ME\DOWNLOADS\MyDatabase.FDB;** found 12.0, support 13.0**

Which is pretty irritating as I got a diffent error from the docker container with the tag 3.0:

unsupported on-disk structure for file /firebird/data/MyDatabase.FDB; **found 13.0, support 12.2** What ODS do I have now 13 ? 12 ?

LeonardSchwenk commented 2 years ago

Sorry I think the ODS version error is misleading.

This is my error when I backup the database on windows and try to restore it in the docker container.

When in docker container: cd firebird/data

"${PREFIX}"/bin/gbak -r mydb_backup.fbk mydb_restore.fdb -user SYSDBA -password firebird or "${PREFIX}"/bin/gbak -c mydb_backup.fbk mydb_restore.fdb -user SYSDBA -password firebird ... produces the same error

gbak: ERROR:expected backup description record
gbak:Exiting before completion due to errors

The file seems not valid even if it is. (works on windows)

jacobalberty commented 2 years ago

That error message indicates you are passing in an invalid backup file, either you've accidentally copied the wrong file instead of the backup file or it was corrupted in transmission (possibly transmitted in ASCII instead of BINARY mode if using FTP).

I would start diagnosis with taking a checksum of the file on the windows machine where it originated and then comparing it against the linux machine where you are restoring the backup see if they match. if they do I would copy the backup file back from the linux machine to the windows machine and attempt to restore the copied backup on the windows machine (in a new filename don't overwrite your working database file) and see if that works. If checksums match and restore works on windows then it looks like you may have run into some form of bug in firebird itself.

If you are moving from 3.0 on windows to 4.0 in docker you might also want to go over https://ib-aid.com/download/docs/fb4migrationguide.html and check for incompatibilities such as usage of reserved words or PSQL cursor fixes. But I don't believe any of those issues would cause this error, but it's worth checking just to be sure.

I would also give restoration a try on both v3.0.9 and v4.0.1 tags. If it works in v3.0.9 but not v4.0.1 maybe try performing another backup within the v3.0.9 docker container and restore that in a v4.0.1 docker container, though the transportable backup format should make that a non issue.

LeonardSchwenk commented 2 years ago

I was able to unpack the database on my macOS machine with no problem. Which makes it even more weird as I'm running firebird 3.0 and used the same backup database file. That makes me assume the backup file is not corrupted.

Im not able to select from the macOS restored db within the docker container

I tried

cd firebird/data
"${PREFIX}"/bin/gbak -c  -user SYSDBA -password firebird myDB_backup.fbk myDB_restore.FDB

with v3.0.9 and v4.0.1 always get the same:

gbak: ERROR:expected backup description record
gbak:Exiting before completion due to errors

But thats ok for now I'm fine with running firebird on macOS. I will get another firebird db in the next month I think. I will then try again with the docker container. We can close this issue for now and I will reopen it when I have a fresh DB backup. Is that fine for now ? If you need any help implenting or discussing the firebird backup feature for the docker container, let me know. Thanks

jacobalberty commented 2 years ago

I'm adding a function to auto restore any .fbk files found in /firebird/restore. It will look for a .env file as well containing RESTORE_USER and RESTORE_PASSWORD as the user and pass to restore to. if there is no .env file it will use ISC_USER and ISC_PASSWORD from /firebird/etc/SYSDBA.password to restore instead.

So if you have /firebird/restore/database.fbk it will look for /firebird/restore/database.env for the credentials for restore, if that file doesn't exist it will use the credentials in /firebird/etc/SYSDBA.password instead. and restore to /firebird/restore/database.fdb. I believe this should provide a decent balance of flexibility and automation

VIS-Repo commented 2 years ago

@jacobalberty thank you. I have just come across this project and it looks exactly what I'm after. However, whilst I've been using Firebird on Windows for years I am new to Docker and Linux so a bit more usage info in the ReadMe would be very helpful. For example could you provide a sample 'docker container run ....' command or a docker compose command showing how some of the variables are used please? I am trying to build a container using Firebird v3.0.9 SuperServer with a user and password. I have a master.fbk file I'd like included in the command for auto unpacking into your default folder using the new feature you describe above.

Any help would be much appreciated

jacobalberty / firebird-docker

Cannot access or select from tables from docker firebird database #87

But I cannot see my tables or select from them.