Let's encrypt error with database collation

**jscha** · Nov 6th 2016

Hi all,

I am using i-MSCP 1.3.7 with Let's Encrypt 1.2. After installing and activating it, I switch to a customer panel -> domains -> Let's Encrypt and get:

Code

An unexpected error occurred
An exception has been thrown in file /var/www/imscp/gui/library/Functions/Shared.php at line 2345:
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Although I very well understand the error massage, I do not know wether it is a good idea to just log into PMA and change the collation. I have received this error also in the previous version.

Right now, I cannot use the plugin and need to run Certbot manually. I would like to change that and would appreciate your help.

Thanks in Advance!

**Nuxwin** · Nov 6th 2016

Hello,

Could you please make a dump of your current letsencrypt table in the imscp database and show us the structure?

Thank you.

Also please:

Distro, codename?
MySQL version?
i-MSCP version ?

**Nuxwin** · Nov 6th 2016

@jscha

Also please, say us for which field (and in which table) the collation is set to utf8_general_ci. Normally, we use only the utf8_unicode_ci collation, excepted for some fields of the reseller_props table which were set to the utf8_general_ci collation by mistake...

**jscha** · Nov 6th 2016

hi,

thanks for the fast answer!

here is further info:
we currently (still) use imscp 1.37 on debian 7.11 (wheezy) with mysql 5.5.52.0 installed.

the database dump for let's encrypt showed (info is not complete, but should suffice I hope).

Code

--
-- Table structure for table `letsencrypt`
--
CREATE TABLE IF NOT EXISTS `letsencrypt` (
`letsencrypt_id` int(10) unsigned NOT NULL,
`admin_id` int(10) DEFAULT NULL,
`domain_id` int(10) DEFAULT NULL,
`domain_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`domain_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`letsencrypt_inc_www_cname` tinyint(4) NOT NULL DEFAULT '1',
`letsencrypt_status` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ok',
`letsencrypt_previous_status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`letsencrypt_status_date` int(10) unsigned NOT NULL DEFAULT '0',
`letsencrypt_expires` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Display More

Unfortunately I must admit, that although the iscp table's 'global' collation is utf8_unicode_ci, all subtables but letsencrypt are set to utf8_general_ci. This may be due to the fact, that we have been carrying this db for quite some time...

Thanks again.

**Nuxwin** · Nov 7th 2016

Quote from jscha

Unfortunately I must admit, that although the iscp table's 'global' collation is utf8_unicode_ci, all subtables but letsencrypt are set to utf8_general_ci.

Please, clarify a bit. I don't see any problem regarding the letsencrypt table. Collation seem to be set to utf8_general_ci as expected. Which subtable is set with utf8_general_ci collation exactly?

**jscha** · Nov 7th 2016

Thanks again for the fast answer.

To clarify:

- the ispcp global collation is utf8_unicode_ci
- letsencrypt table's collation is utf8_unicode_ci
- all other tables (admin, autoreplies_log, config, custom_menus, domain..., web_software_options) are utf8_general_ci

I hope the structure is clear now. And unfortunately, I do think that this collides with what you stated in your initial response about the collations

edit:
I looked into the tables and it seems that in all subtables there are varchar fields that use the utf8_general_ci collation and some that use utf8_unicode_ci.
When creating a structure only database dump, the fields using utf8_unicode_ci are stated explicitly as utf8_unicode_ci. The tables collation is just marked as utf8.

As an example you find the dump for the domain table.

Code

--
-- Table structure for table `domain`
--
CREATE TABLE IF NOT EXISTS `domain` (
`domain_id` int(10) unsigned NOT NULL,
`domain_name` varchar(200) DEFAULT NULL,
`domain_admin_id` int(10) unsigned NOT NULL DEFAULT '0',
`domain_created` int(10) unsigned NOT NULL DEFAULT '0',
`domain_expires` int(10) unsigned NOT NULL DEFAULT '0',
`domain_last_modified` int(10) unsigned NOT NULL DEFAULT '0',
`domain_mailacc_limit` int(11) DEFAULT NULL,
`domain_ftpacc_limit` int(11) DEFAULT NULL,
`domain_traffic_limit` bigint(20) DEFAULT NULL,
`domain_sqld_limit` int(11) DEFAULT NULL,
`domain_sqlu_limit` int(11) DEFAULT NULL,
`domain_status` varchar(255) DEFAULT NULL,
`domain_alias_limit` int(11) DEFAULT NULL,
`domain_subd_limit` int(11) DEFAULT NULL,
`domain_ip_id` int(10) unsigned DEFAULT NULL,
`domain_disk_limit` bigint(20) unsigned DEFAULT NULL,
`domain_disk_usage` bigint(20) unsigned DEFAULT NULL,
`domain_disk_file` bigint(20) unsigned DEFAULT NULL,
`domain_disk_mail` bigint(20) unsigned DEFAULT NULL,
`domain_disk_sql` bigint(20) unsigned DEFAULT NULL,
`domain_php` varchar(15) DEFAULT NULL,
`domain_cgi` varchar(15) DEFAULT NULL,
`allowbackup` varchar(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'dmn|sql|mail',
`domain_dns` varchar(15) NOT NULL DEFAULT 'no',
`domain_software_allowed` varchar(15) NOT NULL DEFAULT 'no',
`phpini_perm_system` varchar(15) NOT NULL DEFAULT 'no',
`phpini_perm_register_globals` varchar(15) NOT NULL DEFAULT 'no',
`phpini_perm_allow_url_fopen` varchar(15) NOT NULL DEFAULT 'no',
`phpini_perm_disable_functions` varchar(15) NOT NULL DEFAULT 'no',
`phpini_perm_mail_function` varchar(20) NOT NULL DEFAULT 'yes',
`phpini_perm_display_errors` varchar(15) NOT NULL DEFAULT 'no',
`domain_external_mail` varchar(15) NOT NULL DEFAULT 'no',
`external_mail` varchar(15) NOT NULL DEFAULT 'off',
`web_folder_protection` varchar(5) NOT NULL DEFAULT 'yes',
`mail_quota` bigint(20) unsigned NOT NULL,
`document_root` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '/htdocs',
`url_forward` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no',
`type_forward` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`host_forward` varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'Off'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Display More

**Nuxwin** · Nov 7th 2016

Quote from jscha

the ispcp

Ok... You migrated from ispcp.

For now, please set all tables to utf8_unicode_ci collation manually through PMA, including columns if needed. I'll add a fix in next release. For the columns, do that only for those on which the wrong utf8_general_ci collation is set.

The bug is not in the LetsEncrypt plugin, nor in i-MSCP core. This is more a migration issue.

**jscha** · Nov 7th 2016

Thanks again for your help and input.
And yes as previously stated, we have been carrying the database for quite some time :D. Since the migration was a long time ago, I had forgotten.

I will change the collations manually and report back in a few days.

Best regards!

**jscha** · Nov 7th 2016

So I looked again into the database and found that there were way to many fields with the utf8_general_ci collation to do it manually. So I opted out of the idea to do it manually and decided to try an automated way and watch out for any errors.

I did some reading and found a lot of opinions that apart from special characters (like the german ß / ss ) changing the collation from utf8_general_ci to utf8_unicode_ci should not be troublesome, since the field contents should stay the same (this is different than changing the collation from latin_xxx to utf8 often brings a lot of problems). So I went for it and tried it.

For now, I have not found any errors the site seems to be working properly AND I can use the LetsEncryptPlugin without any problems! I will continue to test the sites and i-mscp for any erros and will report back.

For others facing the same problem:

I found some nice posts on stackoverflow and finally this site (http://georgepavlides.info/con…abase-to-utf8_general_ci/)

1. I created a copy of the database (database_change)
and did the following in pma' sql query window

SQL

SELECT CONCAT('ALTER TABLE `', tbl.`TABLE_SCHEMA`, '`.`', tbl.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;') FROM `information_schema`.`TABLES` tbl WHERE tbl.`TABLE_SCHEMA` = 'database_change'

Note: the last string 'database_change' is the database name

This then will produce a sql command list to change the collation for every table/field in database

Code

ALTER TABLE `database`.`admin` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`autoreplies_log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`config` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`custom_menus` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`domain` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`domain_aliasses` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`domain_dns` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`domain_traffic` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`email_tpls` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`error_pages` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`ftp_group` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`ftp_users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`hosting_plans` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`htaccess` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`htaccess_groups` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`htaccess_users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`httpd_vlogger` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`letsencrypt` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`login` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`mail_users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`php_ini` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`plugin` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`quotalimits` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`quotatallies` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`reseller_props` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`server_ips` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`server_traffic` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`sql_database` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`sql_user` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`ssl_certs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`subdomain` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`subdomain_alias` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`tickets` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`user_gui_props` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`web_software` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`web_software_depot` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`web_software_inst` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `database`.`web_software_options` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Display More

I copied this and entered it into another sql query window. The query affected 11 tables.

The the whole database was in utf8_unicode_ci collation. I then renamed the original database to 'database_backup' and renamed 'database_change' to 'database'

**jscha** · Nov 23rd 2016

To this day I have not encountered any problems.

And thanks to Nuxwin for the latest release !!

Share

Daily visitors