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

matt · July 9, 2023, 7:48pm

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

My story: I did following steps based on Developer Edition - SeaTable Admin Manual

Downloaded the image and the yml file

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"

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)
Started it up via docker-compose up and waited to see the idle message

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.

Restarted it in detached mode and started the service

root@synology:/volume1/docker/seatable# docker-compose up -d
root@synology:/volume1/docker/seatable# docker exec -d seatable /shared/seatable/scripts/seatable.sh start

Created a super user - and here the issue started

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 SeaTable docker: superuser creation script exits with errors - #8 by rdb 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

I’ve tried to re-run the init command but ran into an mysql error

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

I adjusted the HTTPS endpoints in dtable_web_settings.py, but still same issue when re-running the init script
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 ...

That did the trick and I could run the init command and have now 124 tables

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 =)

AkDk7 · July 9, 2023, 8:04pm

Hi @matt.

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

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 (Docker). Another thing could be the dev version is just broken.

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

Regards
AkDk7

matt · July 10, 2023, 11:01am

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

cdb · July 10, 2023, 7:56pm

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.

matt · July 14, 2023, 7:02pm

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

system · July 16, 2023, 7:02pm

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.

cdb · July 18, 2023, 7:47am

Thanks for reporting your observation.