Posts by jscha

jscha · Nov 23rd 2016

To this day I have not encountered any problems.

And thanks to Nuxwin for the latest release !!

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

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

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.

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!

Posts by jscha

Let's encrypt error with database collation

Let's encrypt error with database collation

Let's encrypt error with database collation

Let's encrypt error with database collation

Let's encrypt error with database collation

Let's encrypt error with database collation