Docker and SQLite

[deon.korb]

Hi,

I am running the KCollins Docker image 8.1.12.

I have created a SQLite db at /media/usb/historian.db and am trying to connect to it.

It just remains faulted. Is there something funny that needs to happen so the Docker container can break out and see the db file?

Regards,

[PGriffith]

Yes. Docker containers are run in their own filesystem abstraction; they don’t (for deliberate security reasons) see the rest of the contents of your hard drive.

You can use bind mounts or volumes to ‘share’ resources from the host into the container; volumes are the generally preferred way.

[deon.korb]

Thank you very much for the nudge in the right direction

[deon.korb]

I think I still have a problem with permissions:
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: sqlth_drv)

My knowledge of Linux is limited to what I have blindly copied and pasted off the internet.

Do I have to allow the Docker service writes to the db file?

[kcollins1]

Can you share your docker run (or Compose YAML) statement that you’re using? This will help us see the configuration of your container, which should include declarations for your bind-mount, for example. If you’re running this on Linux directly, file permissions may be involved. If you also run ls -la /media/usb, share that output as well (which will tell us the file permissions on that mounted drive).

kcollins/ignition runs as UID/GID 999 by default. This means that it will have equivalent permissions to bind-mounted resources (from your host, such as your USB drive). As of kcollins/ignition:8.1.12, you now have the option to run the container as root (which is typically not a best-practice) by supplying the IGNITION_UID=0 environment variable.

[deon.korb]

Run command:

docker run -p 8088:8088 \
-p 1883:1883 \
--name RPiIgnitionMaker \
--restart=always \
--privileged \
-e TZ=Africa/Johannesburg \
-e GATEWAY_ADMIN_USERNAME=Administrator \
-e GATEWAY_ADMIN_PASSWORD=<password> \
-v my-ignition-data:/var/lib/ignition/data \
-v /media/usb:/media/usb \
-e IGNITION_EDITION=maker \
-e IGNITION_LICENSE_KEY=<licence> \
-e IGNITION_ACTIVATION_TOKEN=<token> \
-e GATEWAY_MODULES_ENABLED=opc-ua,perspective,sfc,tag-historian \
-d kcollins/ignition:latest

Mounted the /media/usb identical on both sides, was just easiest.

No idea what the ls -la results mean

[YF129701]

@deon.korb

No idea what the ls -la results mean

That's a common Linux command list or ls that will print out whatever is in the current directory. The -a flag will display hidden files (starting with .) and the -l flag lists the contents in a longer format.

This command is close to what dir does in the command line in Windows.

[kcollins1]

Okay, it looks like /media/usb on your Pi has been mounted by the pi user. Inspecting a local Raspberry Pi I have here shows that pi user had UID/GID of 1000 by default. I'd recommend just running the Ignition Gateway with a matching UID/GID. You can try the following:

1. Stop and remove your existing gateway container (which has your volume declaration, so gateway state should be safe) with:

   docker stop RPiIgnitionMaker && docker rm RPiIgnitionMaker
   

2. Launch a new container with IGNITION_UID=1000 and IGNITION_GID=1000 environment variables.

   docker run -p 8088:8088 \
       -p 1883:1883 \
       --name RPiIgnitionMaker \
       --restart=always \
       --privileged \
       -e TZ=Africa/Johannesburg \
       -e GATEWAY_ADMIN_USERNAME=Administrator \
       -e GATEWAY_ADMIN_PASSWORD=<password> \
       -v my-ignition-data:/var/lib/ignition/data \
       -v /media/usb:/media/usb \
       -e IGNITION_EDITION=maker \
       -e IGNITION_LICENSE_KEY=<licence> \
       -e IGNITION_ACTIVATION_TOKEN=<token> \
       -e GATEWAY_MODULES_ENABLED=opc-ua,perspective,sfc,tag-historian \
       -e IGNITION_UID=1000 \
       -e IGNITION_GID=1000 \
       -d kcollins/ignition:8.1.12
   

   Note: I changed the image tag to 8.1.12 to be explicit as a best-practice. It is typical to see latest tag in documentation so folks don't have to update it all the time.

As of kcollins/ignition:8.1.9, the container actually starts as root user (UID=0) and then utilizes gosu to step-down to a standard user (default of UID/GID=999) for launching the Ignition Gateway. This approach allows the entrypoint to correct/update permissions as root to a configurable UID/GID at runtime. Previously, this was only possible by specifying these same variables as build arguments and building a custom derived image with a [new] static UID/GID baked in.

Let me know if you have any other questions!

[deon.korb]

Hi Kevin,

Thanks for this. The container didn’t start with UID=1000, but did with 0 which is sufficient for the trial.

Every is “working” now. The performance of the handful of tags to the SQLite DB is not great and I get a lot of log errors with file locked etc.

I’m probably going to install MariaDB and check that out for comparison.

Thanks,
Deon

[pturmel]

@deon.korb

The performance of the handful of tags to the SQLite DB is not great and I get a lot of log errors with file locked etc.

This is no surprise. SQLite just isn't made for the Tag History use-case. Recent discussion on SQLite:

SQLite DB failed to load after polling rate

This. Use a real database. A SQLite DB, fundamentally, is a generic configuration file manipulated with SQL. Its codebase is optimized for compact single file storage and a minimalist library footprint. It is not and never will be suitable for Tag History in any ordinary sense. If you want maximum utility in a free database, I recommend PostgreSQL. I think MariaDB is also a good choice.

[kcollins1]

Agreed that a traditional DB is going to perform better. FWIW, I’ve got a couple of other follow-up thoughts to offer here too… I haven’t done a comparison between performance of the different schemas, but defining a SQLite DB Connection and then using the associated Tag History provider to store tag history uses a different schema for the SQLite file than if you use the Internal History option to explicitly create a Tag History provider. If you wanted to try that out (and still have it write to your USB drive), you could:

1. Create a folder on your USB drive:

   mkdir /media/usb/tag-historian
   

2. Add a separate bind-mount to the specific path that Ignition places internal history tag provider backing SQLite DBs.

   docker run ...
       -v /media/usb/tag-historian:/var/lib/ignition/data/local/tag-historian
   

3. Go to Config → Tags → History and create a new tag history provider of type “Internal Historian”.

I’ll let someone else chime in on whether one may perform differently than the other–I didn’t want to not mention this option just because I hadn’t fleshed out performance benchmarks. It might not net you anything.

I also wonder whether you’re having trouble with performance of the attached drive, too. There is such a wide quality/performance variance of USB flash media that it is pretty easy to get one with absolutely atrocious write performance. Same goes with MicroSD cards if you’re running on a Pi. You didn’t mention the specific errors either–it could be the internal config.idb that is causing trouble on a slow drive. If you’ve got a really fast USB drive connected with good R/W performance (over USB3 interface on a Rasp Pi 3 or better), it might be worth trying to just run the container with a bind-mounted data volume against your USB drive (i.e. docker run ... -v /media/usb/ignition-data:/data ...).

Hope this helps, just trying to think of some other things you could try to get things working.

PS: I didn’t have any troubles with the UID/GID=1000 (albeit not yet on an actual 32-bit RaspPi on armhf)… Container logs and architecture (output of uname -a) would help.