Ошибка кодировка базы latin1 отличается от кодировки соединения utf8

Добрый день!
На сайте при тестировании конфигурации, были проблемы с кодировкой, ругался на latin1.
Я исправил ошибку стандартными средствами, как было указано в рекомендации и вроде еще выполнил пару команд (точно не помню, было давно):

Получил запросы на все таблицы
SEL ECT CONCAT(‘ALT ER   TABLE `’, t.`TABLE_SCHEMA`, ‘`.`’, t.`TABLE_NAME`, ‘` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;’) as sqlcode
FR OM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = ‘`DB_NAME`’
ORDER BY 1
и выполнил для каждой таблицы:
ALT ER   TABLE `DB_NAME`.`TABLE_NAME` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

При проверке сайта стало все ок:
Кодировка соединения (check_mysql_connection_charset): Ok
character_set_connection=utf8, collation_connection=utf8_unicode_ci, character_set_results=utf8
Кодировка базы данных (check_mysql_db_charset): Ok
CHARSET=utf8, COLLATION=utf8_unicode_ci

НО! Выползла куча багов при работе со строковыми функциями. Похоже, где то я не до правил) Я заглянул в базу и увидел в кодировках полный бардак:
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database latin1
character_set_results utf8mb4
character_set_server latin1
character_set_system utf8
collation_connection utf8mb4_unicode_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

Подскажите, как лучше всего исправить и привести базу и сайт к  единой кодировке (UTF8utf8_unicode_ci наверно????) и самое главное что бы базу не загубить)

В процессе настройки сервера для сайта, пришлось столкнуться с некоторыми проблемами. В частности с проблемой кодировки базы данных PostgreSQL. Дело в том, что при установке PostgreSQL, шаблоны баз данных создавались с кодировкой LATIN1, а сайт работает на Django, с использованием кодировки UTF8. В результате, при попытке вставки данных выпадала следующая ошибка:

ERROR: encoding UTF8 does not match locale en_US Detail: The chosen LC_CTYPE setting requires encoding LATIN1.


Поискав информацию, удалось найти несколько решений, среди которых было решение, которое позволяет пересоздать шаблон базы данных с кодировкой UTF8. Но пройдёмся внимательно по симптомам задачи.



Проверка кодировки



Для проверки кодировки, применяемой на сервере и в базе данных, необходимо выполнить следующие команды.


Заходим в режим работы с PoctgreSQL:

sudo -u postgres psql


psql — это утилита для работы с базами данных, а postgres — это супер пользователь PostgreSQL.


И выполняем следующие команды:

postgres=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 LATIN1
(1 row)

postgres=# l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | LATIN1   | en_US   | en_US | 
 template0 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)



В выводе будет показана кодировка LATIN1, как для сервера, так и для баз данных.




  


                       
  





Исправление кодировки для template1



А теперь исправим кодировку template1, который используется для создания баз данных.

postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
postgres=# DROP DATABASE Template1;
postgres=# CREATE DATABASE template1 WITH owner=postgres ENCODING = 'UTF-8' lc_collate = 'en_US.utf8' lc_ctype = 'en_US.utf8' template template0;
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';


В данном случае сначала мы указываем, что template1 не является шаблоном для баз данных. Удаляем данный шаблон. Потом создаём новый шаблон с кодировкой UTF8 и устанавливаем данную базу данных в качестве шаблона для новых баз данных. Дальше новые базы данных будут создаваться с кодировкой UTF8.


И проверим, в какой кодировке у нас находиться шаблон template1, сам сервер, кстати, будет по-прежнему в кодировке LATIN1

postgres=# l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | LATIN1   | en_US      | en_US      | 
 template0 | postgres | LATIN1   | en_US      | en_US      | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(3 rows)

Mysql поддерживает много кодировок и это нередко является головной болью для программистов. Самая частая проблема — кракозяблы вместо русского текста. Это происходит из за того, что текст либо лежит на сервере, либо отдается клиенту в неверной кодировке. Последнее(а иногда и первое) решается проще всего. Устанавливаем кодировку соединения (в utf8 в примере) сразу после установления соединения

Хуже, когда скрипт отдает в базу, данные в верной кодировке, а в ответ получаем кракозяблы или вопросики. Или когда часть таблиц в верной кодировке, часть нет.. В таких случаях придется разбираться детально.

Этот запрос обязательно проверять в самом скрипте, а не в phpmyadmin, где могут быть установлены другие параметры

1. character_set_client — кодировка, в которой данные будут поступать от клиента
2. character_set_connection — по умолчанию для всего, что в рамках соединения не имеет кодировки
3. character_set_database — кодировка по умолчанию для баз
4. character_set_filesystem — кодировка для работы с файловой системой (LOAD DATA INFILE, SELECT … INTO OUTFILE, и т.д.)
5. character_set_results — кодировка, в которой будет выбран результат
6. character_set_server — кодировка, в которой работает сервер
7. character_set_system — идентификаторы MySQL, всегда UTF8
8. character_sets_dir — папка с кодировками

По умолчанию после установки mysql сервер, который устанавливается ленивым хостеромадмином имеет кодировку latin1. Соответственно указанные выше глобальные переменные будут в latin1. Базы соответственно по умолчанию и таблицы так же. И именно на это стоит обратить в самом начале обратить внимание, чтобы проблемы не всплывали позднее.

В идеальном варианте, нам следуетпривести все отмеченные цветом кодировки к единому значению. Тогда мы просто будем избавлены от мелких ошибок с кодировкой. Фактически, если мы работаем с хостингом, то на (3) и (6) мы повлиять не сможем. Но и это не страшно если настроены остальные три параметра. Mysql умет перекодировать на лету если правильно настроена кодировка соединения.

Ну и наконец, основной вопрос, что делать если одна из mysql таблиц(или несколько) в неверной кодировке и на сайте видны кракозяблывопросики?

1. Выяснить кодировку таблицы.

В этой таблице поле sName в кодировке latin1, если у нас соединение в другой кодировке, то мы увидим кракозябры.

2. Поэтому дальше проверим кодировку соединения, sql запросом SHOW VARIABLES LIKE ‘character_set_client’. Замечу, что php функция mysqli_client_encoding(), нам не подойдет, так как она отображает кодировку только на момент соединения.

3. Если кодировка соединения не совпала с кодировкой одного из полей таблицы, то 2 очевидных варианта.
Если у нас все таблицы в одной кодировке, то проще поменять кодировку соединения .
А как исправить неверную кодировку поля таблицы?
Для этого выполним 2 запроса

Нельзя обойтись только вторым запросом. Важно выполнить оба. Первый преобразовывает данные в двоичные, второй запрос, преобразовывает данные в строковые сменив кодировку.. Т.е. по сути мы не измениили двоичные данные, мы изменили правило формирования символов.  Если бы мы попробовали обойтись только вторым запросом, то получили бы ошибочный набор.

Развернут apache и MySQL на Ubuntu 14.04LTS.

Вывод SHOW VARIABLES LIKE'character%'

Variable_name — Value

character_set_client — utf8

character_set_connection — utf8

character_set_database — utf8

character_set_filesystem — binary

character_set_results — utf8

character_set_server — utf8

character_set_system — utf8

character_sets_dir — /usr/share/mysql/charsets/

Вывод SHOW VARIABLES LIKE 'collation%';

Variable_name — Value

collation_connection — utf8_general_ci

collation_database — utf8_general_ci

collation_server — utf8_general_ci

php подключение к базе и вывод кодировки:

<?php
header('Content-Type: text/html; charset=utf-8');
require_once ('../config.php');
// Create connection
$conn = new mysqli(DB_URL, DB_USER, DB_PASS, DB_NAME);
printf("Изначальная кодировка: %sn", $conn->character_set_name());
mysqli_query($conn, "SET NAMES 'UTF-8'");
printf("А теперь: %sn", $conn->character_set_name());
$conn->set_charset("utf8");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Вывод браузера

Изначальная кодировка: latin1 А теперь: latin1

Думаю, это главная причина проблемы, связанной с незаписывающимися кириллическими данными в БД из формы. Числа пишутся, кириллица — нет.

09.11.2021

 
if (defined('BX_UTF') && BX_UTF === true)
{
	if ($character_set_connection != 'utf8mb3')
		$strError = GetMessage("SC_CONNECTION_CHARSET_WRONG", array('#VAL#' => 'utf8', '#VAL1#' => $character_set_connection));
	elseif ($collation_connection != 'utf8mb3_unicode_ci')
		$strError = GetMessage("SC_CONNECTION_COLLATION_WRONG_UTF", array('#VAL#' => $collation_connection));
}