Database: Attachment migration



  • 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





  • 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… :)


  • Kopano

    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



  • 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


  • Kopano

    @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.



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



  • 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


  • Kopano

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



  • @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


  • Kopano

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

    Depends on the settings ‘#innodb_file_per_table’ as well.


Log in to reply
 

Looks like your connection to Kopano Community Forum was lost, please wait while we try to reconnect.