Everything is restored, all data recovered!
In case someone will ever have similiar issues: I used
- version 4.3.10 (so not latest) with
- 3 users and just
- 5 bases, no big data,
- standard plugins from market.seatable.io installed.
But the bases had automations, e-mail automations (smtp as „integrations from third parties“ set up), general apps and some webforms.
What happened
Literally without any changes, SeaTable was this week just not accessible anymore (so nobody was logged in = assumingly no data cached, SeaTable being the only software run on this virtual Linux/Ubuntu server). I copied all directories as mentioned at „Backup and Recovery“ from SeaTable without dumping the MariaDB databases before.
Error
I deleted the whole server and set up a new virtual server image. Then, I re-installed SeaTable and tried to copy all directories back. System did not run, no login page accessible.
Error reason
Every user has a single and random ID #, even the same user data at a new installation results in a different ID #. Bases and data were there, but could not be connected to any user (old user ID did not exist in the new installation, new user ID not in old bases or metadata). So they were not shown in system admin dashboard.
Solution
Some manual work!
- Installing the system (the same old version!) again
- Login, create all former users manually again
- Replace these users’ former user ID # with the new one from 2. in seafile_db and dtable_db
- Delete and re-install plugins by changing repos ID.
- Re-create „integrations from third parties“, here the smtp for e-mail automations.
About 1.: As mentioned in the Admin handbook for Installation, make sure to install your former version.
About 2.: Login as admin, create users as before in system admin, copy their user id #, i.e.from user name by clicking on each user.
About 3.: If you can read/extract the former user ID # from ccnet_db, e.g. in table UserRole, perfect. If not, I used the free version of „Stellar Repair“, see at “Repair corrupt MariaDB Database” (although the database was actually not corrupted and could be dumped). The software is easy to use and depicts all data from each table of your databases in a nice structure, although export is not possible in the free version. By selecting database ccnet_db, you can find the old user ID #.
Then, I dumped the two „content databases“ dtable_db and seafile_db from /opt/mariadb, i.e. the subdirectories dtable_db and seafile_db which comprised mainly of .frm and .ibd files. This is easy as described in „Backup & Recovery“ with
docker exec -it mariadb mysqldump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt seafile_db > ./seafile_db.sql
docker exec -it mariadb mysqldump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt dtable_db > ./dtable_db.sql
The result are two .sql files, which can be processed like simple text files e.g. in Notepad++. There, you replace your old user ID # with the new ones in both seafile_db.sql and dtable_db.sql.
Afterwards, docker down, restore seafile_db and dtable_db in their directories at your re-installed version. Leave ccnet_db as it is – your former users are already back. Copy the old directories storage-data, seafile-data and seahub-data back as described in “Backup and Recovery” to replace the new, generic ones. Your bases are back!
==> In ccnet_db, the users are recorded again (with new ID #). Through dtable_db and seafile_db, you connected your users’ new ID # with the old bases (entries like who created them, who can see/edit them), the same for old apps and automations!
Docker up to see whether the system works and bases are back.
About 4.: Plugins were depicted in system admin but did not work (error when trying to open, update or use). For this, delete the PLUGINS_REPO_ID as described in “Backup & Recovery” → “Special cases and advanced topics”. Again docker down and up again, then simply re-install any plugin from marketplace (or elsewhere).
About 5.: What I did not properly restore were the „integrations from third parties“, in my case just e-mail details to send e-mail automations. I did this manually in each base, luckily the e-mail automations are not corrupted or deleted because of a missing sender.
Thank you very much for your support and night shifts, SeaTable team! And for creating this robust system!
Exactly, that’s how I could save the (raw) data.
