Self-hosted installation via Docker on Synology - New user experience report - Hackish init sql fix

Admin Talk Installation
1

Hey seatable-community,

first: thanks a lot for this great product! It works great for my specific use case and I am really happy with the provided functionality, scripting capabilities and hosting options.

I wanted to share that I have installed the latest version (4.0.0) from the docker image on my Synology NAS, and ran in one issue that I somehow circumvented - but probably not in the best way I should :slight_smile:

My story: I did following steps based on Redirecting...

  1. Downloaded the image and the yml file :green_circle:
root@synology:/volume1/docker# docker pull seatable/seatable-developer:latest
root@synology:/volume1/docker# mkdir /volume1/docker/seatable
root@synology:/volume1/docker# cd /volume1/docker/seatable
wget -O "docker-compose.yml" "https://manual.seatable.io/docker/Developer-Edition/docker-compose.yml"
  1. Adjusted docker-compose.yml to my environment (adjusted domain, using just a single HTTP non-standard port, no HTTPS, as the Synology NAS acts as reverse proxy and does the HTTPS handling) :green_circle:
  2. Started it up via docker-compose up and waited to see the idle message :green_circle:
root@synology:/volume1/docker/seatable# docker-compose up
Creating seatable-memcached ... done
Creating seatable-redis     ... done
Creating seatable-mysql     ... done
Creating seatable           ... done
...
seatable     | *** Running /etc/my_init.d/01_init.sh...
seatable     | *** Booting runit daemon...
seatable     | *** Runit started as PID 16
seatable     | *** Running /templates/enterpoint.sh...
seatable     | 2023-07-08 16:04:24 Conf exists 
seatable     | 2023-07-08 16:04:25 Nginx ready 
seatable     | 2023-07-08 16:04:25 This is an idle script (infinite loop) to keep container running.
  1. Restarted it in detached mode and started the service :green_circle:
root@synology:/volume1/docker/seatable# docker-compose up -d
root@synology:/volume1/docker/seatable# docker exec -d seatable /shared/seatable/scripts/seatable.sh start
  1. Created a super user - and here the issue started :yellow_circle:
root@synology:/volume1/docker/seatable# docker exec -it seatable /shared/seatable/scripts/seatable.sh superuser  
E-mail address: some.valid@email.address
Password: 
Password (again): 
Traceback (most recent call last):
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.ProgrammingError: (1146, "Table 'dtable_db.profile_profile' doesn't exist")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/opt/seatable/seatable-server-4.0.0/dtable-web/seahub/base/management/commands/createsuperuser.py", line 150, in handle
    User.objects.create_superuser(email, password)
  File "/opt/seatable/seatable-server-4.0.0/dtable-web/seahub/base/accounts.py", line 156, in create_superuser
    u = self.create_user(email, password, is_staff=True, is_active=True)
  File "/opt/seatable/seatable-server-4.0.0/dtable-web/seahub/base/accounts.py", line 87, in create_user
    Profile.objects.add_or_update(username=virtual_id, contact_email=email, need_show_video=True)
  File "/opt/seatable/seatable-server-4.0.0/dtable-web/seahub/profile/models.py", line 29, in add_or_update
    profile = self.get(user=username)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/models/query.py", line 431, in get
    num = len(clone)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/models/query.py", line 262, in __len__
    self._fetch_all()
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/models/query.py", line 1324, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/models/query.py", line 51, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/opt/seatable/seatable-server-latest/dtable-web/thirdpart/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table 'dtable_db.profile_profile' doesn't exist")

During debugging I came across https://forum2.seatable.io/t/seatable-docker-superuser-creation-script-exits-with-errors/97/8 and logged into the database to see that only 21 tables were created in my environment

root@synology:/volume1/docker/seatable# docker exec -it seatable mysql -h db -P 3306 -u root -p dtable_db
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.5-10.5.21-MariaDB-1:10.5.21+maria~ubu2004 mariadb.org binary distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+----------------------------+
| Tables_in_dtable_db        |
+----------------------------+
| activities                 |
| admin_log_adminlog         |
| api2_token                 |
| api2_tokenv2               |
| auth_group                 |
| auth_group_permissions     |
| auth_permission            |
| auth_user                  |
| auth_user_groups           |
| auth_user_user_permissions |
| avatar_avatar              |
| avatar_groupavatar         |
| base_commandslastcheck     |
| base_userlastlogin         |
| bound_third_party_accounts |
| captcha_captchastore       |
| constance_config           |
| email_sending_log          |
| user_activity_statistics   |
| webhook_jobs               |
| webhooks                   |
+----------------------------+
21 rows in set (0.00 sec)

I did not see any error message during init though I was assuming, everything is OK

  1. I’ve tried to re-run the init command but ran into an mysql error :red_circle:
root@synology:/volume1/docker/seatable# docker exec -it seatable /shared/seatable/scripts/seatable.sh init-sql
mysql server is ready
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 1: Can't create database 'ccnet_db'; database exists
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 1: Can't create database 'seafile_db'; database exists
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 1: Can't create database 'dtable_db'; database exists
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1050 (42S01) at line 15: Table 'activities' already exists
Init sql success
  1. I adjusted the HTTPS endpoints in dtable_web_settings.py, but still same issue when re-running the init script
  2. In my despair I adjusted the /opt/seatable/seatable-server-latest/sql/mysql/dtable.sql and added an IF NOT EXISTS to the CREATE TABLE statements and IGNOREto the INSERT INTO statements:
...
CREATE TABLE IF NOT EXISTS `activities` (
...
INSERT IGNORE INTO `auth_permission` VALUES (1,'Can add content ...
  1. That did the trick and I could run the init command and have now 124 tables :green_circle:
root@977fcab7572c:/shared/seatable/scripts# /shared/seatable/scripts/seatable.sh init-sql
mysql server is ready
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 1: Can't create database 'ccnet_db'; database exists
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 1: Can't create database 'seafile_db'; database exists
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 1: Can't create database 'dtable_db'; database exists
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

Init sql success
...
| user_starred_dtables                      |
| webhook_jobs                              |
| webhooks                                  |
| workspaces                                |
+-------------------------------------------+
124 rows in set (0.00 sec)

I am sharing that because this was my new user journey and the init sql issue fix feels not right, yet I wanted to let others how I got it up an running - at least in a hack-ish way.

The question I have is: how should that be solved correctly? I am happy to create a github issue or give more infos etc if needed.

Best regards,
Matthias

Sidenote: for me this is especially exciting to see this product doing so well as I contributed over 10 years ago a mini translation fix to your sister project seafile =)

2

Hi @matt.

First of all, it’s an absolute amazing description you posted. It’s something you don’t see very often. :slight_smile:

As just another user, my thought is you are using the developer edition of SeaTable. When I have a look at the seatable docker page I can see, that version is already outdated (over one month old) in comparison to the enterprise version (https://hub.docker.com/u/seatable). Another thing could be the dev version is just broken. :expressionless:

So maybe you should try to switch over to the enterprise version even though you might deal with the three user limitation.

Regards
AkDk7

3

Great finding re the version, thanks for sharing! Maybe I can do a test with both editions sometime from scratch and compare.

4

Dear matt,
I can only confirm what akdk7 says: we test every enterprise edition thoroughly, while we only touch the developer edition every few months. I am not aware of any problems with the enterprise edition when creating databases.

5

Just to get a closure on that: as suggested, I tried with the enterprise edition and it worked flawlessly, interesting! Thanks!

7

Thanks for reporting your observation.