1. Janos SUTO Avatar Janos SUTO
  2. Piler
  3. piler
  4. Issues

Provide instructions on how to migrate database from utf8 to utf8mb4

Issue #1088 closed
int created an issue

on the upgrade documentation page http://www.mailpiler.org/wiki/current:upgrade
in chapter “from 1.3.0 to 1.3.5“ you describe step by step how to keep the utf8 charset but not how migrate from utf8 to utf8mb4.

But since new piler installations use the utf8mb4 this would be the preferred path to take for an upgrade also. But for this procedure you just refer to an Bitbucket issue from 2016. For me it is not clear from this issue which steps need to be taken.

Please provide step by step instructions on how to migrate the piler database from utf8 to utf8mb4.

Comments (2)

  1. int reporter

    I figured out how to upgrade mailpiler from version 1.3.0 to 1.3.8 and wrote this little howto.
    All the commands are for Centos 7 - for other os like debian you need to adjust.

    the general sources I used for information:
    *http://www.mailpiler.org/wiki/current:upgrade
    *http://www.mailpiler.org/wiki/current:installation
    *http://www.mailpiler.org/wiki/current:post-install
    *https://community.spiceworks.com/how_to/138923-how-to-install-mailpiler-on-centos-7
    *http://wiki.ozanh.com/doku.php?id=linux:debian:mailpiler:web-configuration

    Make a backup before you upgrade! Especially the database is crutial for mailpiler. Be sure you can roll back to a working installation.

    Disable all piler cronjobs to avoid modification of the database and mail store during the upgrade process

    su - piler
crontab -e

    stop all Piler daemons to make sure no new mail is added during the upgrade process

    /etc/init.d/rc.searchd stop
/etc/init.d/rc.piler stop

    update the OS

    yum update

    make sure all needed packages are installed and up to date

    yum install epel-release gcc make wget libzip libzip-devel sysstat
yum install tre tre-devel python python-devel MySQL-python openssl openssl-devel

    Mailpiler 1.3.0 used PHP 5.x, mailpiler 1.3.8 now uses PHP 7.x - change from PHP 5.4 to PHP 7.4 :

    remove PHP 5.4

    yum list installed | grep php   
yum remove php*

    install PHP 7.4 - it is not part of the Centos Repo, you need to add REMI Repo - https://www.tecmint.com/install-php-7-in-centos-7/#

    yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm
yum-config-manager --enable remi-php74

    install needed PHP 7.4 Modules

    yum install php php-common php-cli php-ldap php-gd php-pecl-memcache php-pdo php-mysql php-curl php-zip php-fpm php-mbstring

    make sure all helper program packages are installed and up to date

    yum install tcp_wrappers tcp_wrappers-devel catdoc poppler poppler-utils unrtf tnef libpst

    ‌

    Mailpiler 1.3.0 used the utf8 character set in the database, mailpiler 1.3.8 now uses utf8mb4 by default.
    if you want to keep the utf8 character set see instructions at http://www.mailpiler.org/wiki/current:upgrade "from 1.3.0 to 1.3.5" on how to do that.

    I made the decision to change the character set from utf8 to utf8mb4
    sources I consulted for the necessary steps:
    *http://www.mailpiler.org/wiki/current:upgrade "from 1.3.0 to 1.3.5"
    *https://bitbucket.org/jsuto/piler/issues/709/mysql_stmt_execute-error-incorrect-string
    *https://bitbucket.org/jsuto/piler/issues/895/reference-convert-piler-database-to
    *https://bitbucket.org/jsuto/piler/issues/899/interface-just-shows-search-mails-from

    each of these sources contains only a part of the necessary database changes.
    I combined all of them and added additional changes to the database columns that I found by comparing the databases from 1.3.0 and 1.3.7

    change Character Set of the database

     mysql -u piler -p
 ALTER DATABASE piler CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

    apply all changes to the database columns that were modified from 1.3.0 to 1.3.7

     USE piler;
 ALTER TABLE piler.archiving_rule CHANGE COLUMN `attachment_type` `attachment_type` varchar(64) COLLATE 'utf8_general_ci' default NULL;
 ALTER TABLE piler.attachment CHANGE COLUMN `name` `name` TINYBLOB default NULL;
 ALTER TABLE `customer_settings` CHANGE `domain` `domain` varchar(128) COLLATE 'utf8_general_ci' NULL AFTER `id`;
 ALTER TABLE `domain` CHANGE `domain` `domain` char(64) COLLATE 'utf8_general_ci' NOT NULL FIRST, CHANGE `mapped` `mapped` char(64) COLLATE 'utf8_general_ci' NOT NULL AFTER `domain`;
 ALTER TABLE `email` CHANGE `email` `email` char(128) COLLATE 'utf8_general_ci' NOT NULL AFTER `uid`;
 ALTER TABLE `group` CHANGE `groupname` `groupname` char(128) COLLATE 'utf8_general_ci' NOT NULL AFTER `id`;
 ALTER TABLE `online` CHANGE `username` `username` varchar(128) COLLATE 'utf8_general_ci' NOT NULL FIRST;
 ALTER TABLE `online` CHANGE `ipaddr` varchar(64) COLLATE 'utf8_general_ci' NOT NULL FIRST;
 ALTER TABLE `retention_rule` CHANGE `domain` `domain` varchar(100) COLLATE 'utf8_general_ci' NULL AFTER `id`, CHANGE `from` `from` varchar(100) COLLATE 'utf8_general_ci' NULL AFTER `domain`, CHANGE `to` `to` varchar(100) COLLATE 'utf8_general_ci' NULL AFTER `from`, CHANGE `attachment_name` `attachment_name` varchar(100) COLLATE 'utf8_general_ci' NULL AFTER `size`, CHANGE `attachment_type` `attachment_type` varchar(64) COLLATE 'utf8_general_ci' NULL AFTER `attachment_name`;
 ALTER TABLE piler.sph_index CHANGE COLUMN `from` `from` TINYBLOB default NULL;
 ALTER TABLE piler.sph_index CHANGE COLUMN `to` `to` TEXT COLLATE 'utf8_general_ci' default NULL;
 ALTER TABLE piler.sph_index CHANGE COLUMN `todomain` `todomain` TEXT COLLATE 'utf8_general_ci' default NULL;
 ALTER TABLE piler.sph_index CHANGE COLUMN `subject` `subject` BLOB default NULL;
 ALTER TABLE piler.sph_index CHANGE COLUMN `body` `body` MEDIUMBLOB default NULL;
 ALTER TABLE piler.sph_index CHANGE COLUMN `attachment_types` `attachment_types` TEXT COLLATE 'utf8_general_ci' default NULL;

    change Character Set of the tables

     ALTER TABLE `archiving_rule` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `attachment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `audit` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `autosearch` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `counter` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `counter_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `customer_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `domain` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `domain_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `email` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `email_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `folder` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `folder_extra` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `folder_message` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `folder_rule` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `folder_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `google` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `google_imap` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `group` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `group_email` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `group_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `import` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `ldap` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `legal_hold` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `metadata` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `note` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `online` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `option` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `rcpt` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `remote` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `retention_rule` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `search` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `sph_counter` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `sph_index` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `tag` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `timestamp` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `user_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `private` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    ‌

    actual Mailpiler upgrade

     wget https://bitbucket.org/jsuto/piler/downloads/piler-1.3.8.tar.gz
 tar zxvf piler-1.3.8.tar.gz
 cd piler-1.3.8
 ./configure --localstatedir=/var --with-database=mysql
 make
 su -c 'make install'
 ldconfig

    Do not run 'make postinstall' ! this might overwrite your Config Files and Encryption Key in /usr/local/etc/piler !

    'make install' created new config files named ".dist" in /usr/local/etc/piler
    compare these new config files with your old config files and merge the changes by hand.

    ‌

    make sure the www/tmp directory can be accessed by the web server

    chown apache:apache /var/piler/www/tmp

    start Piler daemons again

     /etc/init.d/rc.piler start
 /etc/init.d/rc.searchd start

    Mailpiler 1.3.8 has two new cron jobs, add them to the piler crontab

     su - piler
 crontab -e

    add lines

     */5 * * * * /usr/bin/find /var/piler/error -type f|wc -l > /var/piler/stat/error
 3 * * * * /usr/local/libexec/piler/watch_sphinx_main_index.sh

    and enable all piler cronjobs again

    The upgrade is done - test that everything is working correctly again...

    ‌

    The upgrade instructions at http://www.mailpiler.org/wiki/current:upgrade were not quite living up to the expectation that the first sentence

    This page gives a detailed, step by step instructions how to do the upgrade with less pain.

    gave me.

    I hope my write up can help to improve the documentation.

  3. Log in to comment
Assignee
–
Type
bug
Priority
minor
Status
closed
Votes
0
Watchers
1
Jira: the preferred issue tracker for Bitbucket. Join the team!