Closed jangrewe closed 4 months ago
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
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?
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.
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'")
The PR would be this one: https://github.com/TheSpaghettiDetective/obico-server/pull/955
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!