Database 4 byte UTF-8 support

By webdevtim on 26 Jun 2017 at 23:09 UTC

I have looked through the discussions on this topic and applied all the suggestions I could find, but I still get

Database 4 byte UTF-8 support Not enabled

4 byte UTF-8 for mysql is not activated, but it is supported on your system. It is recommended that you enable this to allow 4-byte UTF-8 input such as emojis, Asian symbols and mathematical symbols to be stored correctly. See the documentation on adding 4 byte UTF-8 support for more information.

I have run a query on the data base: SHOW CREATE DATABASE name_of_database;

and get the results back: CREATE DATABASE name_of_database /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */

And I have added lines to the settings.php file to enable 4 byte UTF-8 support as follows:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'name_of_database',
      'username' => 'name_of_user',
      'password' => 'u6w0tF0eW9',
      'host' => 'db',
      'port' => '3306',
      'driver' => 'mysql',
      'prefix' => 'prefix_',
      'charset' => 'utf8mb4',
      'collation' => 'utf8mb4_general_ci',
    ),
  ),
);

And I still get the response:

Database 4 byte UTF-8 support Not enabled

4 byte UTF-8 for mysql is not activated, but it is supported on your system. It is recommended that you enable this to allow 4-byte UTF-8 input such as emojis, Asian symbols and mathematical symbols to be stored correctly. See the documentation on adding 4 byte UTF-8 support for more information.

And here is what every table in the database shows:

InnoDB utf8mb4_unicode_ci …

What am I missing here??

Comments

my.ini

sprite commented 26 June 2017 at 23:24

Did you modify the my.ini file?


# UTF 8 Settings
init-connect=
collation_server=utf8mb4_general_ci
character_set_server=
skip-character-set-client-handshake
character_sets-dir="{path}"

Did you modify the php.ini file (see link)?

https://secure.php.net/manual/en/ini.core.php#ini.default-charset

spritefully yours
Technical assistance provided to the Drupal community on my own time ...
Thank yous appreciated ...

assume my.cnf was updated no my.ini

webdevtim commented 26 June 2017 at 23:31

I asked my host to update my.cnf with the following:

In order to allow for large indexes, MySQL must be set up with the following my.cnf settings:

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

I did not ask them to update my.ini

I will ask them to update my.ini with

init-connect=
collation_server=utf8mb4_general_ci
character_set_server=
skip-character-set-client-handshake
character_sets-dir="{path}"

{path} is a tag that get replaced with the actual path, or should the actual path be substituted for {path}

Host Said Cannot Update my.ini

webdevtim commented 27 June 2017 at 15:40

Host said:

I'm sorry but we can't change settings in global my.cnf file. All the tables and database is set correctly but still you may need to change character set and collation for mysql client connection. This can be done by issuing following query:
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

This should be done as soon Drupal connects to database. Please ask your Drupal support about where and how this should be added.

The site is running on shared hosting environment and some settings can't be changed. In case you still need those server side setting we can offer you our dedicated VPMC hosting. This a virtual machine with dedicated resources and support for server settings and configuration. Hosting plan price starts at $200/mo.

I am stuck with shared hosting for now because I don't have the $200+/mo. to set up a virtual private memory cloud dedicated server. So I need to know where I add that MySQL query string so that it runs at connection time.

I just can't seem to be able to wrap my head around virtual, private and cloud at the same time, seems like private and cloud are mutually exclusive terms, especially considering the weak encryption standards in the U.S.

syntax ...

sprite commented 27 June 2017 at 18:40

You should be able to add those directives to either the .htaccess or the settings.php file associated with your Drupal installation.

Right now I can't think of all the exact syntax, but there should be plenty of documentation around for appropriate syntax in each context.

Also, export your SQL database with phpmyadmin, then load it into a lightweight text editor and perform global search and replace on all the table type and table collation settings in the database itself and then re-import the database back onto the server with phpmyadmin.

-------

By the way, a proper, well managed and configured VPS, with SSD hard drive, starts at about $150.00 / mo, with a physical dedicated server starting at about $500 / mo, in my experience. I use a Centos VPS with WHM and cPanel, but with root access via "nomachine"(Linux remote GUI/CLI desktop), and SSH.

The only quality server system less expensive is - linode.com - but by the time you add in all the linode "add-ons" from their base ala carte price, with a full service VPS, the price is almost the same.

https://www.linode.com/pricing

spritefully yours
Technical assistance provided to the Drupal community on my own time ...
Thank yous appreciated ...

Which Collation to Use

webdevtim commented 29 June 2017 at 21:07

Someone on one thread said to add this to settings.php:


$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'tapactio',
  'username' => 'tapactio',
  'password' => 'password',
  'host' => 'db',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_general_ci',
);

run hte following queries:
ALTER DATABASE myDatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "myDatabase"
AND TABLE_TYPE="BASE TABLE"

Either the suggestion for the settings.php file is wrong, or the SQL query is wrong.

I would think that the settings.php suggestions is correct and the SQL queries should be
run hte following queries:
ALTER DATABASE myDatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci';

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci';') AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "myDatabase"
AND TABLE_TYPE="BASE TABLE"

or the settings.php entry should look like:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'tapactio',
  'username' => 'tapactio',
  'password' => 'password',
  'host' => 'db',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_unicode_ci',
);

Which should it be???

read the following ...

sprite commented 30 June 2017 at 23:30

read the following article ...

https://www.drupal.org/node/2754539

spritefully yours
Technical assistance provided to the Drupal community on my own time ...
Thank yous appreciated ...

That article is where the problem lies

webdevtim commented 30 June 2017 at 23:45

This article is wrong. The article says to include database directives in the settings.php file as follows:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'databasename',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_general_ci',
);

But then goes on to suggest that the tables all be updated according to the following ALTER and SELECT queries.

ALTER DATABASE databasename CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "databasename"
AND TABLE_TYPE="BASE TABLE"

We cannot set the database to use utf8mb4_unicode_ci collation and then instruct Drupal to access that database via utf8mb4_general_ci collation. This is wrong.

The question is which is preferable for Drupal utf8mb4_unicode_ci or utf8mb4_general_ci?

I have read that utf8mb4_unicode_ci is preferable because it is newer and more precisely defined, but that utf8mb4_general_c is faster for the reason that it isn't so precisely defined.

I am going to try utf8mb4_unicode_ci and if it works end of story, if it doesn't I will drop back to utf8mb4_general_ci.

I got it set up correctly

webdevtim commented 1 July 2017 at 03:15

the correct settings.php entry for database setting is:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'databasename',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_unicodel_ci',
);

Drupal reports that:

Database 4 byte UTF-8 support Enabled
4 byte UTF-8 for mysql is enabled.

That article was wrong. you either choose utf8mb4_general_ci for everything or choose utf8mb4_unicode_ci for everything, you don't mix.

typo?

mmjvb commented 1 July 2017 at 08:56

the correct settings.php entry for database setting is:

$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'databasename',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicodel_ci',
);

use instead: 'collation' => 'utf8mb4_unicode_ci',

Typo Made on Forum not in Settings.php

webdevtim commented 1 July 2017 at 14:11

This is what I settled on for my database configuration; Drupal liked this:

$databases = array (
 'default' => 
  array (
   'default' => 
    array (
     'database' => 'dbname',
     'username' => 'dbuser',
     'password' => 'xxxxxxxxxxxxx',
     'host' => 'db',
     'port' => '3306',
     'driver' => 'mysql',
     'charset' => 'utf8mb4',
     'collation' => 'utf8mb4_unicode_ci',
     'prefix' =>
       array(
        'default'   => 'main_',
        'users'     => 'shared_',
        'sessions'  => 'shared_',
        'role'      => 'shared_',
        'authmap'   => 'shared_',
       ),
    ),
  ),
);

I actually got this from reading all the comments in default.settings.php. Actually I used unique names for prefixes because that adds just one more difficulty on the road to hacking a database, because everyone knows the default Drupal table names, but they don't know the weird prefix I might add in front of those names.