Database: Attachment migration

PartTimeNerd

Hi folks,

I’m currently working on a migration from ZCP 7.1.10 to Kopano.
On the live ZCP server, the attachments are currently stored in mysql database. During the migration, I’d like to change the storage method of attachments to filesystem.
But when I run the convert script with delete option, the database size remains. I have to truncate the lob table manually. Is it safe to do so or will I face some issues with this method?

My second question is: Can I migrate attachments from file to database back again, if I’m not happy with file storage?

Appreciate any help…
PTN

Sinux

You may have tried???

https://documentation.kopano.io/kopanocore_administrator_manual/configure_kc_components.html#storing-attachments-outside-the-database

Sinux

PartTimeNerd

Yes I did, and unfortunately, this lead me exact to, where I am now.
Even with the delete option, the database size didn’t reduce by the size of the attachment folder.
And I wonder, if there is a script to migrate from db to file, if there might be someone, tried the other way… So any help / ideas appreciated… :)

bhuisman

Hi,

I would not recommend any manual database change (such as truncating a table). Depending on which MySQL version you’re running (and its configuration) you might need to create a backup, drop the database and remove the files and then restore the backup.

While you’re at it, you might want to have a look at https://kb.kopano.io/display/WIKI/Basic+MySQL+tuning and see if your current MySQL settings match what is currently recommended for the best performance.

-Bob

PartTimeNerd

Hey Bob,

just to make sure, I get it right:

run the db convert script with delete option
backup the database via mysqldump
delete the db-files in /var/lib/mysql/dbname
restore the backup

Is that your suggestion?

BR
PTN

bhuisman

@PartTimeNerd

Before step 3 (delete the files) first drop the database. After that, it makes sense to check what is in the directory, newer versions of MySQL clean up their own files.

It also makes sense to test your backup by restoring it in a different database before you drop the database.

PartTimeNerd

Ok… thanks. I’ll try that way and give you feedback.
Hopefully, I get this done until next weekend…

PartTimeNerd

Ok, it worked.

Running the db convert script with delete option
Export database
Dump database
Import database

I wonder, if there is no way, cleaning up the database with mysql tools.
Thank you, Bob.

BR
PTN

bhuisman

@PartTimeNerd Glad to hear! There are no MySQL tools but there are some that can help you in the Percona toolkit.

umgfoin

@PartTimeNerd

@PartTimeNerd said in Database: Attachment migration:

I wonder, if there is no way, cleaning up the database with mysql tools.

I suggest OPTIMZE TABLE table_name; - this will internally recreate the table, thus freeing pre-allocated table-space.

mysql -hlocalhost -ukopano_usr -pkopano_pwd

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13289742786
Server version: 10.1.23-MariaDB MariaDB Server


MariaDB [(none)]> use kopano_db

Database changed
MariaDB [kopano_db]> optimize table lob;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| kopano_db.lob | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| kopano_db.lob | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.01 sec)

MariaDB [kopano_db]> quit

++umgfoin

bhuisman

This does not always work, depending on your server version/configuration.

Depends on the settings ‘#innodb_file_per_table’ as well.