[Bugzilla user: frank@altpeter.de]
[Bugzilla date: 2024-06-26T15:42:31+00:00]

Hi,

I did read the support.html page, I did search for my problem on the discourse forum and on Google, but I wasn't able to find any information about it. I also read the FAQ, especially the 'Troubleshooting' section.

**What steps will reproduce the problem?**
Install Orthanc 1.12.3 from FreeBSD ports and start it.

**What is the expected output? What do you see instead?**
I expect that the orthanc server is initializing the sqlite database and start - or do something other than just exiting with the following error:

[Called orthanc --trace and pasting the relevant parts from it]

I0626 17:35:37.790530             MAIN SQLiteDatabaseWrapper.cpp:1389] Creating the database
T0626 17:35:37.790571             MAIN Connection.cpp:159] (sqlite) SQLite::Connection::Execute -- Orthanc - A Lightweight, RESTful DICOM Store

[...]

-- Set the version of the database schema
-- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration
INSERT INTO GlobalProperties VALUES (1, "6");

E0626 17:35:37.796042             MAIN Connection.cpp:168] SQLite execute error: no such column: "6" - should this be a string literal in single-quotes? (1)
E0626 17:35:37.800207             MAIN main.cpp:2106] Uncaught exception, stopping now: [SQLite: Cannot execute a command] (code 1004)
W0626 17:35:37.800297             MAIN main.cpp:2137] Orthanc has stopped

After this first run, the sqlite database is incomplete, which results in an "Incompatible version of the database: Incompatible version of the Orthanc database: Unknown" error in subsequent invocations until removing the index file again. Then the above error reoccurs while trying to populate the database.

**What version of the product are you using? On what operating system?**

root@murphy:~ 0# freebsd-version                  
14.1-RELEASE-p1
root@murphy:~ 0# /usr/local/sbin/Orthanc --version
/usr/local/sbin/Orthanc 1.12.3
root@murphy:~ 1# sqlite3 -version
3.46.0 2024-05-23 13:25:27 96c92aba00c8375bc32fafcdf12429c58bd8aabfcadab6683e35bbb9cdebalt1 (64-bit)
root@murphy:.../etc/orthanc 0# file OrthancStorage/index 
OrthancStorage/index: SQLite 3.x database, last written using SQLite version 3046000, writer version 2, read version 2, file counter 2, database pages 30, cookie 0x19, schema 4, UTF-8, version-valid-for 2



**Please provide sample, possibly anonymized, DICOM files**
Not applicible, because I never got to get the server started yet.

[Bugzilla user: s.jodogne@gmail.com]
[Bugzilla date: 2024-06-27T05:53:08+00:00]

Hello,

This probably indicates a permission issue on your file system. Have you checked that the folder containing the SQLite database of Orthanc belongs to the same user as the one that runs Orthanc?

Unfortunately, the upstream Orthanc project is not responsible for the FreeBSD packaging. Please get in touch with the downstream maintainers:
https://www.freshports.org/science/orthanc

Regards,
Sébastien-

[Bugzilla user: frank@altpeter.de]
[Bugzilla date: 2024-06-27T09:47:08+00:00]

I'm sorry but I have to disagree.

Using a clean and fresh installation, using no configuration file (e.g. using the compiled defaults), using --trace and using root as running uid for testing, and getting a readable and writable index file with almost all settings that are processed by the init process, but stopping at the

INSERT INTO GlobalProperties VALUES (1, "6");

with an execute error, doesn't sound like a permission problem to me. Additionally, the error message in question doesn't read "permission denied" or "file not found" or similar, but 

SQLite execute error: no such column: "6" - should this be a string literal in single-quotes?

Besides that, before I get bumped between upstream and FreeBSD downstream up and forth, I would like to make sure that it's definitely not an upstream problem I'm facing here.

But, as far as I can see, the problem with an incorrect INSERT statement is present in the code:

root@murphy:...orthanc/work 0# grep -r 'INSERT INTO GlobalProperties' Orthanc-1.12.3 
Orthanc-1.12.3/OrthancServer/Sources/Database/InstallTrackAttachmentsSize.sql:INSERT INTO GlobalProperties VALUES (6, 1);  -- GlobalProperty_GetTotalSizeIsFast
Orthanc-1.12.3/OrthancServer/Sources/Database/PrepareDatabase.sql:INSERT INTO GlobalProperties VALUES (1, "6");

As you can see, the file InstallTrackAttachmentsSize.sql wants to

INSERT INTO GlobalProperties VALUES (6, 1); 

while the file PrepareDatabase.sql wants to

INSERT INTO GlobalProperties VALUES (1, "6");

and latter is exactly what the first invocation of Orthanc is complaining.

I can confirm this theory by manually doing it after the initial run:

root@murphy:.../etc/orthanc 255# sqlite3 OrthancStorage/index 
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite> INSERT INTO GlobalProperties VALUES (1, "6");
Parse error: no such column: "6" - should this be a string literal in single-quotes?
  INSERT INTO GlobalProperties VALUES (1, "6");
                            error here ---^
sqlite> INSERT INTO GlobalProperties VALUES (1, 6);
sqlite> 

After this, Orthanc starts as expected.


So, if you're still sure to see a FreeBSD related permission problem here, let me know. I'm quite curious.

[Bugzilla user: s.jodogne@gmail.com]
[Bugzilla date: 2024-06-27T10:02:21+00:00]

If can demonstrate that your problem is not specific to the FreeBSD package, I'll evidently look into it.

In either case, there is no issue on Debian and Ubuntu, for which I'm the package maintainer. Furthermore, I cannot reproduce your issue by building from scratch on my Ubuntu 22.04 box.

So please provide a way to reproduce your issue, without resorting on the FreeBSD package.

[Bugzilla user: bacon4000@gmail.com]
[Bugzilla date: 2025-10-23T14:21:56+00:00]

Sébastien: What version of sqlite are you using on Debian/Ubuntu?  It appears that Debian Bullseye currently uses 3.34.1:

    https://packages.debian.org/bullseye/sqlite3

I approximately followed Frank's workaround, but used single quotes instead of a bare 6:

    INSERT INTO GlobalProperties VALUES (1, '6');

This also fixed the index file and allowed Orthanc to start successfully.  A bare 6 worked as well, though single quotes are probably correct, assuming we trust the error message.

According to https://sicp.pascal-lab.net/2024/labs/lab11/2_2.html, double quotes are no longer allowed for string literals starting with sqlite 3.41.  

FreeBSD ports upgraded to 3.41.2 in April 2023, and is on 3.50.2 now.

So I'm guessing this will break on Debian/Ubuntu as well, once they upgrade sqlite to 3.41 or later.

I'm a FreeBSD ports committer, so I can add a workaround patch to the port framework for now, but it looks like this should be fixed upstream.

Cheers,

    Jason

[Bugzilla user: bacon4000@gmail.com]
[Bugzilla date: 2025-10-25T19:47:28+00:00]

The FreeBSD port has been updated to 1.12.9 along with a patch for this issue and a couple other minor improvements.  It now works out-of-the-box again.

[Bugzilla user: s.jodogne@gmail.com]
[Bugzilla date: 2025-11-04T15:33:58+00:00]

Thanks for the investigation! The following changeset should solve this issue:
https://orthanc.uclouvain.be/hg/orthanc/rev/d7b003958d89