Reference: convert piler database to utf8mb4

[eXtremeSHOK]

Run the following sql to convert your database to utf8mb4, note you will need to make sure your sphinx and piler configs are set to use utf8mb4.

This will take a long time...

ALTER DATABASE piler CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

USE piler;

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 `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;

[eXtremeSHOK]

added fixes for Specified key was too long; max key length is 767 byte

[Thoth]

Hello,

when i try to convert my database, i'm getting three errors:

• The first is referencing line 10. This one was i able to fix by myself, the line has to be

ALTER TABLE `online` CHANGE `ipaddr` `ipaddr` varchar(64) COLLATE 'utf8_general_ci' NOT NULL FIRST;

• the next two errors are

ERROR 1071 (42000) at line 13: Specified key was too long; max key length is 3072 bytes

and

ERROR 1071 (42000) at line 27: Specified key was too long; max key length is 3072 bytes

How can i fix these? I have piler 1.3.4, which was updated from 1.3.1 which was updated from 1.1.1.

Best regards Thoth

[Janos SUTO]

I think this issue can be closed.