Database error when using MariaDB

[frainz-de]

Hi,

I'm trying to install v0.5.2 on Uberspace using the following guide: https://lab.uberspace.de/guide_etesync-server.html . However, the migrate step fails with the following error:

MySQLdb._exceptions.OperationalError: (1170, "BLOB/TEXT column 'uid' used in key specification without a key length")

I have attached the full stack trace and my etebase-server.ini. stacktrace.txt etebase-server.ini.txt

[tasn]

First of all, as you saw, the guide is outdated. It's about EteSync 1.0 rather than 2.0. The setup process should be very similar though, just a few things are renamed (like etebase-server.ini which you already noticed). Anyhow, it seems to be that MariaDB unlike Postgres/Sqlite requires you to set a max length to all fields used as indices.

I don't currently have time to figure this out and make the adjustments needed for MariaDB, but if you are able, you can try doing it locally. Just change the uid fields to have a max_length of let's say 50 (if they are binary fields), run ./manage.py makemigrations followed by ./manage.py migrate, and it should then work. If you get it fixed we can look into adding the fixes to upstream too.

[lucollab]

Hey there,

i am also running etesync on uberspace, but etesync 2.0.

Here are the steps i made (but without mysql, i am just running 2-3 Users):

$ uberspace web domain add sub.yourdomain.de

$ pip3.6 install uwsgi --user

$ nano ~/etc/services.d/uwsgi.ini

[program:uwsgi]
command=uwsgi --master --emperor %(ENV_HOME)s/uwsgi/apps-enabled
autostart=true
autorestart=true
stderr_logfile = ~/uwsgi/err.log
stdout_logfile = ~/uwsgi/out.log
stopsignal=INT

$ mkdir -p ~/uwsgi/apps-enabled
$ touch ~/uwsgi/err.log
$ touch ~/uwsgi/out.log

$ supervisorctl reread
$ supervisorctl update
$ supervisorctl status

$ git clone https://github.com/etesync/server ~/etebase

$ cd ~/etebase
$ pip3.6 install -r requirements.txt --user
$ pip3.6 install mysqlclient --user

$ mkdir /var/www/virtual/$USER/html/static/
$ ln -s /var/www/virtual/$USER/html/static/
$ python3.6 manage.py collectstatic

$ my_print_defaults client

$ nano ~/etebase/etebase_server_settings.py

DEBUG = False

ALLOWED_HOSTS = ['sub.yourdomain.de']

SECURE_HSTS_SECONDS = 518400
SECURE_HSTS_INCLUDE_SUBDOMAINS = True
SECURE_CONTENT_TYPE_NOSNIFF = True
SECURE_BROWSER_XSS_FILTER = True
SECURE_SSL_REDIRECT = True
SESSION_COOKIE_SECURE = True
CSRF_COOKIE_SECURE = True
X_FRAME_OPTIONS = 'DENY'
SECURE_HSTS_PRELOAD = True

$ python3.6 manage.py migrate

$ python3.6 manage.py check --deploy

$ uberspace web backend set / --http --port 8000

$ uberspace web backend set --apache /static

$ nano ~/uwsgi/apps-enabled/etesync_server.ini

[uwsgi]
chdir = $(HOME)/etebase
base = %(chdir)/etebase_server

http = :8000
master = true
wsgi-file = %(base)/wsgi.py
touch-reload = %(wsgi-file)
static-map = /static=%(base)/static

app = wsgi

plugin = python

$ supervisorctl restart uwsgi

$ supervisorctl status

$ curl -I https://sub.yourdomain.de

$ cd etebase

$ python3.6 manage.py createsuperuser

https://sub.yourdomain.de/admin

[tasn]

@lucollab: I edited your comment to have everything included as code so it renders nicely. :)

[lucollab]

@tasn Ah thanks!! :-D dont know why i am always struggeling with such functions :-D

[lucollab]

btw.: I am a more a less silent tester of etesync. At the time, i successfully selfhosted it on a root server at hetzner, on uberspace and with the new docker file on my raspberry pi combined with portainer. It all works. I am also running the etesync-dav client on my mac and integrated etesync in my native calender, reminder and contacts app. Just one thing: I would love to have multiple Accounts in Etesync Notes on Android. Thats possible?

[tasn]

Glad to hear. :) It's possible, though not yet implemented. Could you maybe open a ticket here: https://github.com/etesync/etesync-notes/issues ? Let's not hijack this thread.

[Altahrim]

Hello,

I got the same problem on MySQL 8.

ALTER TABLE `django_etebase_collectiontype` 
  ADD CONSTRAINT `django_etebase_collectiontype_uid_ba9e127e_uniq` UNIQUE (`uid`);

ERROR 1170 (42000): BLOB/TEXT column 'uid' used in key specification without a key length

As @tasn suspected, it can be fixed with something like this :

ALTER TABLE django_etebase_collectiontype
  MODIFY uid VARBINARY(255) NOT NULL;

Then is it possible to apply patches >= 32.

[tasn]

Is this the only key that's problematic? If so, I can easily fix it.

[Altahrim]

Yes,I think so. I was able to launch service and create an account just after.

[tasn]

Fixed. Don't forget to run ./manage.py migrate to update the db.

[Altahrim]

Sorry, same error… On a fresh install, I got the following:

> ./manage.py migrate
[...]

  Applying django_etebase.0031_auto_20201013_1336... OK
  Applying django_etebase.0032_auto_20201013_1409...Traceback (most recent call last):
  File "/opt/etebase/.venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)

[...]

  File "/opt/etebase/.venv/lib/python3.7/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'uid' used in key specification without a key length")

To fix, I have to ALTER MySQL schema:

mysql etebase -e 'ALTER TABLE django_etebase_collectiontype MODIFY uid VARBINARY(255) NOT NULL;'

Then I can finish the migrations:

> ./manage.py migrate
System check identified some issues:

WARNINGS:
django_etebase.CollectionType.uid: (fields.W162) MySQL does not support a database index on longblob columns.
    HINT: An index won't be created. Silence this warning if you don't care about it.
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, django_etebase, myauth, sessions, token_auth
Running migrations:
  Applying django_etebase.0032_auto_20201013_1409... OK
  Applying django_etebase.0033_collection_uid... OK
  Applying django_etebase.0034_auto_20201214_1124... OK
  Applying django_etebase.0035_auto_20201214_1126... OK
  Applying django_etebase.0036_auto_20201214_1128... OK
  Applying django_etebase.0037_auto_20210127_1237... OK
  Applying myauth.0002_auto_20200515_0801... OK
  Applying myauth.0003_auto_20201119_0810... OK
  Applying sessions.0001_initial... OK
  Applying token_auth.0001_initial... OK

I think patch 32 must be edited to fix the bug, newpatch 37 doesn't seem to be necessary.

[tasn]

Fixed. :)

[Altahrim]

Arf, sorry, I still have the same error.

I am wondering why Django creates a LONGBLOB, I think it should be a VARBINARY. Any idea how to do that?

[tasn]

Can you check the SQL? Maybe it should be maxlength and I should be setting something else. I just noticed: MySQL does not support a database index on longblob columns. So that's a problem anyway...

Does mysql support it for varbinary? Could you try to lookup how to get django to use varbinary? I don't have time at the moment.

[Altahrim]

Yes, MySQL supports index (unique or not) on VARBINARY. If I understood correctly MySQL can only index first bytes of a (LONG)BLOB so it cannot creates a UNIQUE index.

It seems Django always translate BinaryField into LONGBLOB for MySQL. It may be possible to use TextField (aka. VARCHAR for MySQL) with binary encoding (see: binary fields in MySQL) but I didn't find how to set collation for a single field…

[tasn]

Grr.. I don't know what to say. We could potentially have a custom field, though it sounds like a lot of effort for a db we don't use ourselves. Anyone willing to take a jab at it?

[Altahrim]

For anyone hitting this bug, here is a temporary workaround:

• Run all possible patchs as documented:

  ./manage.py migrate
  # Fail on patch 32

• Open MySQL with something like this (adapt according to your needs):

  mysql -u $username -p $etebaseDbName

• Execute the following request:

  ALTER TABLE django_etebase_collectiontype MODIFY uid VARBINARY(255) NOT NULL;

• Complete your patchs:

  ./manage.py migrate

• Continue install as documented

---

@tasn Thank you for your time :)

[tasn]

Hm... I wonder. Maybe there's a way to modify the migration to run custom SQL for mariadb/mysql?

[plsnotracking]

https://stackoverflow.com/a/49317165 - @tasn @Altahrim @victor-rds we can do this with Django?

Or would it be safer to run something one off (https://docs.djangoproject.com/en/dev/ref/migration-operations/#runsql) at the time of init and then leave it be.

[tasn]

This is not part of the migration, the migrations will still fail. We need to find a way to do it as part of the migration (I think you can run raw sql commands, so a patch shouldn't be too hard to build).