[Feature] Use MySQL/MariaDB/Postgres instead of SQLite

[jangrewe]

I have a solid dislike for SQLite, mostly because i prefer to just have my DB backup solution do its job, instead of me having to add another file to my file-based backup solution. But i of course get why starting of with SQLite makes sense for "simple" deployments.

As you seem to be using dj-database-url, it should theoretically be possible to just use one of the RDBMS mentioned in the title, but before switching over and migrating or starting with a new blank database, i was wondering if there are any known issues with any of them, or any other reason why sticking to SQLite is mandatory.

I don't think you guys are running the hosted service on SQLite, either ;-)

Thanks for any feedback!

[kennethjiang]

You can set the DATABASE_URL in .env. We are using postgresql in prod so it at least takes postgres. https://github.com/TheSpaghettiDetective/obico-server/blob/release/docker-compose.yml#L28

Feel free to test other db flaors and lmk how it goes. I'll close the issue for now

[jangrewe]

Okay, here's what i did...

In the obico-web container, i dumped the database:

nerdctl exec -it obico-web bash
./manage.py dumpdata > /data/backup.json

Then i created a MySQL (actually MariaDB) user and database and updated the .env file:

DATABASE_URL=mysql://obico:<redacted>@172.17.0.1:3306/obico

and restarted the obico-migrate container.

In theory, this would the create the DB schema in the MySQL DB and all i would have to do then would be import the data again, via:

nerdctl exec -it obico-web bash
./manage.py loaddata /data/backup.json

... and of course afterwards restart all other containers to switch to the new DATABASE_URL.

But unfortunately the image i'm using (thanks @gabe565) does not include the MySQLdb module:

May 20 17:24:31 hades nerdctl[2555010]: django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module.
May 20 17:24:31 hades nerdctl[2555010]: Did you install mysqlclient?

Could you guys maybe add mysqlclient to the requirements.txt, so that Gabe's images would be rebuilt with it?

[kennethjiang]

Please open a PR for it. Adding the dependency is easy but testing and making sure it works entails a lot more work. This is something I hope you can do as part of the PR.

[jangrewe]

I managed to build an image with mysqlclient, but it looks like MariaDB doesn't quite like the migrations:

May 20 18:54:45 hades systemd[1]: Started obico-migrate.service - Obico Migrate.
May 20 18:54:47 hades nerdctl[2745665]: System check identified some issues:
May 20 18:54:47 hades nerdctl[2745665]: WARNINGS:
May 20 18:54:47 hades nerdctl[2745665]: ?: (mysql.W002) MariaDB Strict Mode is not set for database connection 'default'
May 20 18:54:47 hades nerdctl[2745665]:         HINT: MariaDB's Strict Mode fixes many data integrity problems in MariaDB, such as data truncation upon insertion, by escalating warnings into errors. It is strongly recommended you activate it. See: https://docs.djangoproject.com/en/4.0/ref/databases/#mysql-sql-mode
May 20 18:54:47 hades nerdctl[2745665]: ?: (staticfiles.W004) The directory '/app/../frontend/builds' in the STATICFILES_DIRS setting does not exist.
May 20 18:54:47 hades nerdctl[2745665]: ?: (staticfiles.W004) The directory '/app/../frontend/static' in the STATICFILES_DIRS setting does not exist.
May 20 18:54:47 hades nerdctl[2745665]: account.EmailAddress: (models.W036) MariaDB does not support unique constraints with conditions.
May 20 18:54:47 hades nerdctl[2745665]:         HINT: A constraint won't be created. Silence this warning if you don't care about it.
May 20 18:54:47 hades nerdctl[2745665]: account.EmailAddress: (models.W043) MariaDB does not support indexes on expressions.
May 20 18:54:47 hades nerdctl[2745665]:         HINT: An index won't be created. Silence this warning if you don't care about it.
May 20 18:54:47 hades nerdctl[2745665]: app.GCodeFolder: (models.W036) MariaDB does not support unique constraints with conditions.
May 20 18:54:47 hades nerdctl[2745665]:         HINT: A constraint won't be created. Silence this warning if you don't care about it.
May 20 18:54:47 hades nerdctl[2745665]: app.OctoPrintTunnel.app: (fields.W162) MariaDB does not support a database index on longtext columns.
May 20 18:54:47 hades nerdctl[2745665]:         HINT: An index won't be created. Silence this warning if you don't care about it.
May 20 18:54:47 hades nerdctl[2745665]: app.OctoPrintTunnel.subdomain_code: (fields.W162) MariaDB does not support a database index on longtext columns.
May 20 18:54:47 hades nerdctl[2745665]:         HINT: An index won't be created. Silence this warning if you don't care about it.
May 20 18:54:47 hades nerdctl[2745665]: app.Printer.auth_token: (mysql.W003) MariaDB may not allow unique CharFields to have a max_length > 255.
May 20 18:54:47 hades nerdctl[2745665]:         HINT: See: https://docs.djangoproject.com/en/4.0/ref/databases/#mysql-character-fields
May 20 18:54:47 hades nerdctl[2745665]: app.SharedResource.share_token: (mysql.W003) MariaDB may not allow unique CharFields to have a max_length > 255.
May 20 18:54:47 hades nerdctl[2745665]:         HINT: See: https://docs.djangoproject.com/en/4.0/ref/databases/#mysql-character-fields
May 20 18:54:48 hades nerdctl[2745665]: Operations to perform:
May 20 18:54:48 hades nerdctl[2745665]:   Apply all migrations: account, admin, app, auth, channels_presence, contenttypes, oauth2_provider, sessions, sites, socialaccount
May 20 18:54:48 hades nerdctl[2745665]: Running migrations:
May 20 18:54:48 hades nerdctl[2745665]:   Applying app.0064_notificationsetting...Traceback (most recent call last):
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
May 20 18:54:48 hades nerdctl[2745665]:     return self.cursor.execute(sql, params)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/mysql/base.py", line 75, in execute
May 20 18:54:48 hades nerdctl[2745665]:     return self.cursor.execute(query, args)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 209, in execute
May 20 18:54:48 hades nerdctl[2745665]:     res = self._query(query)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 315, in _query
May 20 18:54:48 hades nerdctl[2745665]:     db.query(q)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 226, in query
May 20 18:54:48 hades nerdctl[2745665]:     _mysql.connection.query(self, query)
May 20 18:54:48 hades nerdctl[2745665]: MySQLdb._exceptions.OperationalError: (1060, "Duplicate column name 'notification_enabled'")
May 20 18:54:48 hades nerdctl[2745665]: The above exception was the direct cause of the following exception:
May 20 18:54:48 hades nerdctl[2745665]: Traceback (most recent call last):
May 20 18:54:48 hades nerdctl[2745665]:   File "/app/./manage.py", line 15, in <module>
May 20 18:54:48 hades nerdctl[2745665]:     execute_from_command_line(sys.argv)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
May 20 18:54:48 hades nerdctl[2745665]:     utility.execute()
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 440, in execute
May 20 18:54:48 hades nerdctl[2745665]:     self.fetch_command(subcommand).run_from_argv(self.argv)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 414, in run_from_argv
May 20 18:54:48 hades nerdctl[2745665]:     self.execute(*args, **cmd_options)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 460, in execute
May 20 18:54:48 hades nerdctl[2745665]:     output = self.handle(*args, **options)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 98, in wrapped
May 20 18:54:48 hades nerdctl[2745665]:     res = handle_func(*args, **kwargs)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 290, in handle
May 20 18:54:48 hades nerdctl[2745665]:     post_migrate_state = executor.migrate(
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 131, in migrate
May 20 18:54:48 hades nerdctl[2745665]:     state = self._migrate_all_forwards(
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 163, in _migrate_all_forwards
May 20 18:54:48 hades nerdctl[2745665]:     state = self.apply_migration(
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 248, in apply_migration
May 20 18:54:48 hades nerdctl[2745665]:     state = migration.apply(state, schema_editor)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/migrations/migration.py", line 131, in apply
May 20 18:54:48 hades nerdctl[2745665]:     operation.database_forwards(
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/migrations/operations/fields.py", line 108, in database_forwards
May 20 18:54:48 hades nerdctl[2745665]:     schema_editor.add_field(
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/mysql/schema.py", line 104, in add_field
May 20 18:54:48 hades nerdctl[2745665]:     super().add_field(model, field)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 641, in add_field
May 20 18:54:48 hades nerdctl[2745665]:     self.execute(sql, params)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 192, in execute
May 20 18:54:48 hades nerdctl[2745665]:     cursor.execute(sql, params)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
May 20 18:54:48 hades nerdctl[2745665]:     return self._execute_with_wrappers(
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
May 20 18:54:48 hades nerdctl[2745665]:     return executor(sql, params, many, context)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
May 20 18:54:48 hades nerdctl[2745665]:     with self.db.wrap_database_errors:
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
May 20 18:54:48 hades nerdctl[2745665]:     raise dj_exc_value.with_traceback(traceback) from exc_value
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
May 20 18:54:48 hades nerdctl[2745665]:     return self.cursor.execute(sql, params)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/django/db/backends/mysql/base.py", line 75, in execute
May 20 18:54:48 hades nerdctl[2745665]:     return self.cursor.execute(query, args)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 209, in execute
May 20 18:54:48 hades nerdctl[2745665]:     res = self._query(query)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 315, in _query
May 20 18:54:48 hades nerdctl[2745665]:     db.query(q)
May 20 18:54:48 hades nerdctl[2745665]:   File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 226, in query
May 20 18:54:48 hades nerdctl[2745665]:     _mysql.connection.query(self, query)
May 20 18:54:48 hades nerdctl[2745665]: django.db.utils.OperationalError: (1060, "Duplicate column name 'notification_enabled'")
May 20 18:54:48 hades systemd[1]: obico-migrate.service: Main process exited, code=exited, status=1/FAILURE
May 20 18:54:48 hades systemd[1]: obico-migrate.service: Failed with result 'exit-code'.

So the main problem is:

MySQLdb._exceptions.OperationalError: (1060, "Duplicate column name 'notification_enabled'")

from https://github.com/TheSpaghettiDetective/obico-server/blob/release/backend/app/migrations/0064_notificationsetting.py

The PR would be this one: #955