Issues with setting up LinkAce using SQLite

[dhananjayraut]

Describe the bug
I was following this guide: https://www.linkace.org/docs/v1/setup/setup-with-sqlite/
when I ran following command

docker exec -it linkace_app_1 php artisan migrate

I got this error:

In Connection.php line 703:

  SQLSTATE[HY000] [14] unable to open database file (SQL: PRAGMA foreign_keys = ON;)


In Exception.php line 18:

  SQLSTATE[HY000] [14] unable to open database file


In PDOConnection.php line 40:

  SQLSTATE[HY000] [14] unable to open database file

To Reproduce

1. Complete steps 1-4 of this guide: https://www.linkace.org/docs/v1/setup/setup-with-docker/simple/
2. Then follow this guide: https://www.linkace.org/docs/v1/setup/setup-with-sqlite/ till the migrate command
3. command ends up with an error.

looks like docker has created a directory with name database.sqlite

Expected behavior
steps in official guide should work and the command should complete successfully.

LinkAce setup (please complete the following information):

• Version: latest
• Installed via: docker
• OS: Ubuntu 21.04 on raspberry pi
• Docker version: 20.10.10
• docker-compose version: 1.29.2

[Kovah]

Hi! Thanks for reporting this.
I have updated the installation guide. Would you mind trying it again?

https://www.linkace.org/docs/v1/setup/setup-with-sqlite/

[dhananjayraut]

after following the updated guide and creating a admin user using CLI, I opened the website which started it's initial setup. It again asked me to fill a form to create new user. and when I filled the details it failed with 500 server error. I looked into logs but nothing looked like an error.
maybe I am doing something wrong.

[Kovah]

Could you change SETUP_COMPLETED=false to SETUP_COMPLETED=true and try again?

Thank you for your patience.

[dhananjayraut]

@Kovah after changing the SETUP_COMPLETED opening the website shows the login screen. After entering the credentials I was able to open the dashboard. but when tried to add a link it gave me the 500 server error screen.
I checked logs in the storage folder in between long stack traces there was this line:

[previous exception] [object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 8 attempt to write a readonly database at /app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117)

[Kovah]

Could you change the permissions for the database file to 0777 to test this?

chmod 0777 database.sqlite

[dhananjayraut]

@Kovah changing the permissions did not fix it. still I am getting 500 server error when I add a link. relevant line in logs:

[2021-12-03 09:51:07] production.ERROR: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database (SQL: insert into "links" ("is_private", "title", "description", "url", "user_id", "icon", "thumbnail", "check_disabled", "status", "updated_at", "created_at") values (0, tor.pi.homenet, 0, https://tor.pi.homenet/, 1, link, ?, 1, 3, 2021-12-03 09:51:07, 2021-12-03 09:51:07)) {"userId":1,"exception":"[object] (Illuminate\\Database\\QueryException(code: HY000): SQLSTATE[HY000]: General error: 8 attempt to write a readonly database (SQL: insert into \"links\" (\"is_private\", \"title\", \"description\", \"url\", \"user_id\", \"icon\", \"thumbnail\", \"check_disabled\", \"status\", \"updated_at\", \"created_at\") values (0, mywebsite.com, 0, https://mywebsite.com/, 1, link, ?, 1, 3, 2021-12-03 09:51:07, 2021-12-03 09:51:07)) at /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php:703)

[Kovah]

I'm out of ideas for now. I do not use SQLite at all so I could just guess what the problem is.

[Drallas]

Same issue for me, 500 - Server Error - An internal server error occured. If you are the administrator, consult the application logs for details.

when adding links / list / tags

192.168.1.52 - - [09/Jan/2022:13:15:26 +0100] "POST /links HTTP/1.1" 500 1872 "http://192.168.1.223:8383/dashboard" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:95.0) Gecko/20100101 Firefox/95.0" "-"

[Drallas]

Work arround from #332 fixed it for me.

[shouya]

It looks like the environment variable SETUP_COMPLETED has been deprecated. Setting it to either true or false both leads me to the setup page. I tried inserting the value directly to the sqlite database (insert into settings (user_id, key, value) values (1, 'setup_completed', 'true')) but that doesn't work either.

How to mark the setup as completed with the current version? Is there any chance to get the outdated doc updated?

[martadinata666]

If you just ugrade, i assume from older version that still use SETUP_COMPLETE env. following upgrade doc should be good, i dont use sqlite so i dont really sure if it will migrate db correctly.

php artisan migrate
php artisan cache:clear

[Kovah]

If you already ran the update steps @martadinata666 mentioned, you should have the setting in the database. If it does not exist, add it with insert into settings (user_id, key, value) values (null, 'system_setup_completed', 1)

[shouya]

Thanks, running `insert into settings (user_id, key, value) values (null, 'system_setup_completed', 1)` fixes the problem for me.

…

On June 17, 2022 12:51:57 PM UTC, Kevin Woblick ***@***.***> wrote: If you already ran the update steps @martadinata666 mentioned, you should have the setting in the database. If it does not exist, add it with `insert into settings (user_id, key, value) values (null, 'system_setup_completed', 1)` -- Reply to this email directly or view it on GitHub: #327 (comment) You are receiving this because you commented. Message ID: ***@***.***>

[chrissawyerfan4]

Per https://www.linkace.org/docs/v1/setup/setup-with-sqlite/

I currently discourage using SQLite for LinkAce because of ongoing issues with the database system.

And #327 (comment)

I'm out of ideas for now. I do not use SQLite at all so I could just guess what the problem is.

If you copy the literal error into a search engine, the top stackoverflow result says to also set permissions (like chown) on the directory rather than only on the database file. Doing that makes everything work out of the box for me. It has been running stable on sqlite for some time now.

Personally, I set it up to use the storage/ directory rather than creating a new, dedicate database/ directory for just one file, because that needs to be writable for the application already and this keeps all variable/state files in one place.

The documentation could mention that the file and directory need permission setting, like chown www-data:www-data ./storage/ ./storage/database.sqlite. I would also recommend not encouraging people to use mode 0777 without a warning banner in place that this effectively disables the permission model of their system for the specified file. I think updating the documentation should allow closing this bug report.

[Jogai]

@chrissawyerfan4 Can you share the compose file you ended up with?

[chrissawyerfan4]

@Jogai Sorry, I somehow did not get (or accidentally marked as read) this notification.

I'm not sure if I used any compose file, I just ran chown www-data:www-data storage/ storage/database.sqlite (which needs to be writable by the web user for things like view caching anyway) and configured the .env file so the application uses DB_DATABASE=../storage/database.sqlite. Looking at composer.json now, I don't see where one would specify file owners, or do you mean to add a script entry? In that case, something like this would probably work (I'm not sure about which working directory this runs in, though, so you may need to add ../ or so):

mkdir -p storage && touch storage/database.sqlite && chown www-data:www-data storage/ storage/database.sqlite

[martadinata666]

the www-data is the one that run web-server, on debian system usually www-data, on other system may use different name.

[Kovah]

Please not that - for whatever reason - SQLite needs write access to the directory containing the database file. Please double check if the /storage directory is writable for www-data too.

[chrissawyerfan4]

@martadinata666 Yes, sorry I should have marked that more clearly as just an example user. I'm not actually sure how the storage/ directory is currently set up automatically with the right permissions/owner, as that has the same problem as the database file and directory (if that's not also storage/): the correct owner or group may differ, even on the same distribution simply based on user preference.