Recent update broke sqlite volume mapping ?

[kub3let]

How Shlink is set up

• Shlink Version: Docker shlinkio/shlink:stable
• Database engine used: SQLite

Summary

After upgrading shlink it now fails to start with a sqlite volume mapping.

I have watchtower updating automatically every 7 days, so the image from last week should still work.

I tried to recreate the container with a fresh database but I get the same error.

logs:

Initializing database if needed...

 [ERROR] Error generating database.. Run this command with -vvv to see specific 
         error info.                                                            

Initializing database if needed...

 [ERROR] Error generating database.. Run this command with -vvv to see specific 
         error info.                                                            

Initializing database if needed...

 [ERROR] Error generating database.. Run this command with -vvv to see specific 
         error info.   
...

How to reproduce

# start container once to extract a default sqlite database for persistent volume mapping

docker run -d \
--name shlink \
-v /etc/localtime:/etc/localtime:ro \
-e DEFAULT_DOMAIN=example.com \
-e IS_HTTPS_ENABLED=true \
-e GEOLITE_LICENSE_KEY=xxxxxxxx \
--restart unless-stopped \
shlinkio/shlink:stable

# extract db
mkdir -p /srv/shlink/data/
docker cp shlink:/etc/shlink/data/database.sqlite /srv/shlink/data/database.sqlite

docker stop shlink && docker rm shlink

# now start with db volume mapping, same as before but with:
# -v /srv/shlink/data/database.sqlite:/etc/shlink/data/database.sqlite \

docker run -d \
--name shlink \
-v /etc/localtime:/etc/localtime:ro \
-v /srv/shlink/data/database.sqlite:/etc/shlink/data/database.sqlite \
-e DEFAULT_DOMAIN=example.com \
-e IS_HTTPS_ENABLED=true \
-e GEOLITE_LICENSE_KEY=xxxxxxxx \
--restart unless-stopped \
shlinkio/shlink:stable

[acelaya]

The usage of sqlite for anything other that quickly testing Shlink, is discouraged.

Please don't use SQLite in production, or try to mount it via docker volumes, as it has known issues and cannot be done reliably.

See these links for more context:

• https://shlink.io/documentation/troubleshooting/#how-to-keep-data-on-docker-image-between-container-recreations
• SQLite persistent storage shlink-docker-image#40 (comment)
• Enable persistent database volumes #1522 (comment)

[kub3let]

The setup worked perfectly fine for over 2 years, If you don't want users to use sqlite why not remove it in the first place.

Switching to mysql I get the exact same issue, so seems to be more of a generic problem.

USE mysql;
CREATE DATABASE shlink;
CREATE USER 'shlink'@'%' IDENTIFIED BY 'xxxxxxxxxxxxx';
GRANT SHOW DATABASES ON *.* TO 'shlink'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON shlink.* TO 'shlink'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

docker run -d \
--name shlink \
-v /etc/localtime:/etc/localtime:ro \
-e DEFAULT_DOMAIN=example.com \
-e IS_HTTPS_ENABLED=true \
-e GEOLITE_LICENSE_KEY=xxxxxxxx \
-e DB_DRIVER=mysql \
-e DB_USER=shlink \
-e DB_PASSWORD=xxxxxxxxxxxxx \
-e DB_HOST=192.168.3.110 \
--restart unless-stopped \
shlinkio/shlink:stable

Initializing database if needed...

 [ERROR] Error generating database.. Run this command with -vvv to see specific 
         error info.                                                            

Initializing database if needed...

 [ERROR] Error generating database.. Run this command with -vvv to see specific 
         error info.                                                            

Initializing database if needed...

[acelaya]

Have you checked if it does actually work?

Shlink retries those commands until the database server is available, and it looks it worked on the third try.

I will take a look though, as the output can be a bit alarming for no reason.

If you don't want users to use sqlite why not remove it in the first place.

It has many use cases, like giving Shlink a quick try, or different automations. Just not production.

[kub3let]

Sorry I cut the log, since it just repeats over and over with restart=unless-stopped, tries to autorecover.

I tried multiple version now and 3.5.4 works just fine, 3.6.0 stops working for me.

I switched now to mysql with 3.5.4

[acelaya]

Can you open a new issue reporting this? It should work with MySQL just fine.

Run it with SHELL_VERBOSITY=3, and it will print further information on what's failing.

https://shlink.io/documentation/troubleshooting/#troubleshoot-errors-when-booting-up-the-docker-image

[acelaya]

Yesterday I debugged my own instance, where I also use MySQL and --restart unless-stopped, and I do see those errors a couple of times until the container starts to work.

I probably didn't notice because the downtime is not too high. Just a couple of seconds.

I tried to run it with SHELL_VERBOSITY=3, and in my case the reason for that error was that the database was not reachable by Shlink, because it was starting up (race condition). Once up, it connects properly and everything works.

I suspect this problem has always been there, but Shlink 3.6.0 has changed how the initial commands are run, and now prints some errors that were explicitly muted before. This should be possible to verify by running older versions with SHELL_VERBOSITY=3 (Edit: I can confirm this).

This also probably relates with the fact that Shlink should actively wait for the database to be available (with a reasonable timeout), before starting to "fail", but that is tricky. I created an issue a couple of years ago around this, but I never got to do it.

So the course of action here is:

• The SQLite error when using volumes won't be addressed. There's an active intention for people to not use this approach for production. SQLite is intended to be used only for quick disposable containers.
• Improve documentation/installer/etc to clearly state the point above.
• Verify if the error was present in older Shlink versions, but just "muted", making it pass unnoticed (Edit: Confirmed).
• If the above is true, think on next steps. A quick solution would be continue to mute those error messages.
• Better document the need for the database to be available before starting Shlink. Using --depends-on is not enough.
• @kub3let when you have managed to run with SHELL_VERBOSITY=3, feel free to open a separate issue if you see a different error with MySQL and Shlink >=3.6.0

[Blackclaws]

Just another comment because this also broke our URL shortener out of nowhere.

I do not see a valid reason why shlink would not work fine with a local sqlite database even if it isn't "recommended". The database works totally fine if not mounted as a single fine but instead by mounting the whole data folder. That is, it used to work fine, until the new version of the container starts running the init script everytime. The issue you get is the following:

 ERR    RUN  '/usr/local/bin/php' 'bin/doctrine' 'orm:schema-tool:create' '--no-interaction'
  ERR    ERR  
  ERR    ERR   ! [CAUTION] This operation should not be executed in a production environment! 
  ERR    ERR  
  ERR    ERR   Creating database schema...
  ERR    ERR  
  ERR    ERR  
  ERR    ERR  In ToolsException.php line 19:
  ERR    ERR                                                                                 
  ERR    ERR    [Doctrine\ORM\Tools\ToolsException]                                          
  ERR    ERR    Schema-Tool failed with Error 'An exception occurred while executing a quer  
  ERR    ERR    y: SQLSTATE[HY000]: General error: 1 table tags already exists' while execu  
  ERR    ERR    ting DDL: CREATE TABLE tags (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  
  ERR    ERR     name VARCHAR(255) NOT NULL)                                                 
  ERR    ERR                                                                                 
  ERR    ERR  
  ERR    ERR  Exception trace:
  ERR    ERR    at /etc/shlink/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/ToolsException.php:19
  ERR    ERR   Doctrine\ORM\Tools\ToolsException::schemaToolFailure() at /etc/shlink/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:107
  ERR    ERR   Doctrine\ORM\Tools\SchemaTool->createSchema() at /etc/shlink/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/CreateCommand.php:65
  ERR    ERR   Doctrine\ORM\Tools\Console\Command\SchemaTool\CreateCommand->executeSchemaCommand() at /etc/shlink/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:46
  ERR    ERR   Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /etc/shlink/vendor/symfony/console/Command/Command.php:326
  ERR    ERR   Symfony\Component\Console\Command\Command->run() at /etc/shlink/vendor/symfony/console/Application.php:1063
  ERR    ERR   Symfony\Component\Console\Application->doRunCommand() at /etc/shlink/vendor/symfony/console/Application.php:320
  ERR    ERR   Symfony\Component\Console\Application->doRun() at /etc/shlink/vendor/symfony/console/Application.php:174
  ERR    ERR   Symfony\Component\Console\Application->run() at /etc/shlink/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/ConsoleRunner.php:51
  ERR    ERR   Doctrine\ORM\Tools\Console\ConsoleRunner::run() at /etc/shlink/bin/doctrine:12
  ERR    ERR  
  ERR    ERR  In ExceptionConverter.php line 56:
  ERR    ERR                                                                                 
  ERR    ERR    [Doctrine\DBAL\Exception\TableExistsException (1)]                           
  ERR    ERR    An exception occurred while executing a query: SQLSTATE[HY000]: General err  
  ERR    ERR    or: 1 table tags already exists                               

So it basically tries to create tables even though they already exist. This is bad. Even if you don't want to support sqlite for more than quick testing this essentially means that you can't "restart" test databases even if it tries to actively create tables every time.

I'd like to add that breakage like this is bad for minor version releases. Even if it isn't the "intended" deployment scenario the fact that it works out of the box like this when using the docker container makes it kind of the default deployment scenario, in which case upgrades shouldn't just break it.

For now we've rolled back to 3.5.4.

There also doesn't seem to be a standard migration path from sqlite to any other database, not that large of a problem in our case but annoying nevertheless.

[acelaya]

@Blackclaws can you try if v3.6.0 is also affected by this?

I suspect this regression might be caused by #1413, which was introduced in v3.6.1

[Blackclaws]

@Blackclaws can you try if v3.6.0 is also affected by this?

I suspect this regression might be caused by #1413, which was introduced in v3.6.1

Just tried it and you are correct. 3.6.0 works fine while 3.6.1 is broken.

[Blackclaws]

My guess is that this might be the culprit:

shlink/module/CLI/src/Command/Db/CreateDatabaseCommand.php

Lines 81 to 86 in 6558c37

	private function ensureDatabaseExistsAndGetTables(): array
	{
	if ($this->regularConn->getDriver()->getDatabasePlatform() instanceof SqlitePlatform) {
	return [];
	}

Wherein as far as I understand it for sqlite no tables are returned as existing.

[Blackclaws]

An easy way to test if its fixed:

docker run --name shlinktest -d shlinkio/shlink:stable
sleep 10
docker stop shlinktest
docker start -a shlinktest

This should work fine. It currently doesn't.

[acelaya]

I have just released Shlink 3.6.3, which fixes the regression introduced in v3.6.1 while trying to fix another bug.

The docker images will be available as soon as this is done https://github.com/shlinkio/shlink/actions/runs/5269669996

That said, a couple of comments for you @Blackclaws

First of all, I would encourage you to be less aggressive and more respectful when addressing an open source maintainer who's work you are taking advantage of FOR FREE.

I'd like to add that breakage like this is bad for minor version releases.

As said, this was an unintentional regression, so please, be more empathetic.

There also doesn't seem to be a standard migration path from sqlite to any other database

There is: https://shlink.io/documentation/advanced/import-short-urls/#shlink-instance

The link above is the only result when searching for "database migration" in the docs.

I do not see a valid reason why shlink would not work fine with a local sqlite database

The issues I shared some comments above explain what kind of problems you can face when doing this, and when those problems arise and you loose production data, you will blame me.

I'm protecting your future you (and me) from yourself.

this also broke our URL shortener out of nowhere

You are using a combination that is not officially supported for production.

The fact that something is not supported doesn't mean it does not work, it means it is not actively tested, and if it stops working, there's no warranty it will be fixed.

I probably did a poor job documenting and stating that SQLite is not officially supported for production environments. Even less combined with docker volumes.

From now on you know you are using an unsupported scenario. Proceed at your own risk.