Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.4.4
-
MariaDB 11.4.4-MariaDB-ubu2204
cPanel Version 124.0.21
Ubuntu v22.04.0 STANDARD kvm
Description
The web application works without error with MySQL8,
The problem is only with MariaDB 11.4 (cPanel)
The database is using utf8mb4_general_ci as default and still having the error
"InformaciĆ³n
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_general_ci, COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation'='"
(collation_error_1.jp)
Attachments
- error2.png
- 237 kB
- error3.png
- 63 kB
- maybe-similiar-issue.jpg
- 161 kB
Issue Links
- relates to
-
MDEV-36037 Illegal mix of collations after upgrade from 11.4.4 to 11.4.5
-
- Closed
-
- links to
Activity
-- --------------------------------------------------------
|
-- Host: 127.0.0.1
|
-- VersiĆ³n del servidor: 11.4.4-MariaDB-ubu2204 - mariadb.org binary distribution
|
-- SO del servidor: debian-linux-gnu
|
-- HeidiSQL VersiĆ³n: 12.8.0.6908
|
-- --------------------------------------------------------
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
/*!40101 SET NAMES utf8 */; |
/*!50503 SET NAMES utf8mb4 */; |
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
/*!40103 SET TIME_ZONE='+00:00' */; |
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
|
-- Volcando estructura para funciĆ³n site_web_db.status_fx_get_filter_customer
|
DELIMITER //
|
CREATE FUNCTION `status_fx_get_filter_customer`(`vcode` VARCHAR(50) |
) RETURNS varchar(50) CHARSET utf8mb4 COLLATE utf8mb4_general_ci |
NO SQL |
BEGIN
|
DECLARE v_filter_customer VARCHAR(50); |
|
SELECT filter_customer |
INTO v_filter_customer |
FROM status |
WHERE code=vcode; |
|
RETURN v_filter_customer; |
|
END// |
DELIMITER ;
|
|
/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */; |
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; |
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */; |
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */; |
-- --------------------------------------------------------
|
-- Host: 127.0.0.1
|
-- VersiĆ³n del servidor: 11.4.4-MariaDB-ubu2204 - mariadb.org binary distribution
|
-- SO del servidor: debian-linux-gnu
|
-- HeidiSQL VersiĆ³n: 12.8.0.6908
|
-- --------------------------------------------------------
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
/*!40101 SET NAMES utf8 */; |
/*!50503 SET NAMES utf8mb4 */; |
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
/*!40103 SET TIME_ZONE='+00:00' */; |
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
|
-- Volcando estructura para tabla site_web_db.ticket
|
CREATE TABLE IF NOT EXISTS `ticket` ( |
`id` bigint(20) NOT NULL DEFAULT 0, |
`subject` varchar(250) NOT NULL DEFAULT '', |
`status` varchar(50) DEFAULT NULL, |
`created_at` datetime NOT NULL, |
`updated_at` datetime NOT NULL, |
`audit_user_id` int(11) NOT NULL, |
`priority_id` int(11) NOT NULL, |
`topic_id` int(11) NOT NULL, |
`description` mediumtext NOT NULL, |
`ticket_type_id` int(11) DEFAULT NULL, |
`prefix` varchar(50) DEFAULT NULL, |
`storage_directory` varchar(50) DEFAULT NULL, |
`group_id` int(11) DEFAULT NULL, |
`starred` tinyint(4) DEFAULT 0, |
`read_by_customer` tinyint(4) DEFAULT 1, |
`read_by_agent` tinyint(4) DEFAULT 0, |
`customer_user_id` int(11) DEFAULT NULL, |
`customer_agent_id` int(11) DEFAULT NULL, |
`type` varchar(50) DEFAULT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `uk_ticket_prefix` (`prefix`) USING BTREE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
|
-- Volcando datos para la tabla site_web_db.ticket: ~0 rows (aproximadamente)
|
|
/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */; |
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; |
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */; |
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */; |
-- --------------------------------------------------------
|
-- Host: 127.0.0.1
|
-- VersiĆ³n del servidor: 11.4.4-MariaDB-ubu2204 - mariadb.org binary distribution
|
-- SO del servidor: debian-linux-gnu
|
-- HeidiSQL VersiĆ³n: 12.8.0.6908
|
-- --------------------------------------------------------
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
/*!40101 SET NAMES utf8 */; |
/*!50503 SET NAMES utf8mb4 */; |
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
/*!40103 SET TIME_ZONE='+00:00' */; |
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
|
-- Volcando estructura para tabla site_web_db.status
|
CREATE TABLE IF NOT EXISTS `status` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`code` varchar(50) NOT NULL, |
`name` varchar(100) NOT NULL, |
`status` varchar(50) NOT NULL DEFAULT 'active', |
`created_at` datetime NOT NULL, |
`updated_at` datetime NOT NULL, |
`audit_user_id` int(11) NOT NULL, |
`is_final` tinyint(4) NOT NULL, |
`label_background_color` varchar(50) NOT NULL DEFAULT '', |
`label_text_color` varchar(50) NOT NULL DEFAULT '', |
`is_start` tinyint(4) DEFAULT 0, |
`filter_customer` varchar(50) DEFAULT NULL, |
`name_customer` varchar(50) DEFAULT NULL, |
`property` varchar(50) DEFAULT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `uk_status_code` (`code`), |
UNIQUE KEY `uk_status_name` (`name`) |
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
|
-- Volcando datos para la tabla site_web_db.status: ~11 rows (aproximadamente)
|
INSERT INTO `status` (`id`, `code`, `name`, `status`, `created_at`, `updated_at`, `audit_user_id`, `is_final`, `label_background_color`, `label_text_color`, `is_start`, `filter_customer`, `name_customer`, `property`) VALUES |
(1, 'created_by_customer', 'Creado por propietario', 'active', '2024-11-05 09:50:13', '2024-12-16 13:25:35', 56, 0, '#28772d', '#f3f2f2', 1, 'open', 'Abierto', 'customer'), |
(2, 'assigned_by_agent', 'Asignado a agente', 'active', '2024-11-05 10:13:42', '2024-12-16 13:25:44', 56, 0, '#1d5d4d', '#000000', 0, 'open', 'Abierto', 'agent'), |
(3, 'escalated_by_system', 'Escalado', 'active', '2024-11-05 10:15:15', '2024-12-16 13:26:57', 56, 0, '#f41a1a', '#e8d4d4', 0, 'open', 'Abierto', 'system'), |
(4, 'closed_by_agent', 'Cerrado por agente', 'active', '2024-11-05 10:35:28', '2024-12-16 13:25:48', 56, 1, '#1a52d5', '#d7e5e1', 0, 'closed', 'Cerrado', 'agent'), |
(5, 'reopened_by_customer', 'Reabierto', 'active', '2024-11-05 10:35:49', '2024-12-16 13:26:54', 56, 0, '#66c77a', '#fffafa', 0, 'open', 'Abierto', 'customer'), |
(6, 'on_hold_by_agent', 'En espera', 'active', '2024-11-05 10:40:31', '2024-12-16 13:25:51', 56, 0, '#e6783d', '#d2cbcb', 0, 'open', 'Abierto', 'agent'), |
(7, 'in_progress_by_agent', 'En proceso', 'active', '2024-11-05 12:55:09', '2024-12-16 13:25:55', 56, 0, '#cd794c', '#d7dfd9', 0, 'open', 'Abierto', 'agent'), |
(8, 'closed_by_customer', 'Cerrado por cliente', 'active', '2024-12-13 18:14:08', '2024-12-16 13:26:50', 56, 1, '#1a52d5', '#d7e5e1', 0, 'closed', 'Cerrado', 'customer'), |
(10, 'close_automatic_by_system', 'Cerrado automƔtico', 'active', '2024-12-13 18:15:01', '2024-12-16 13:26:03', 56, 1, '#1a52d5', '#d7e5e1', 0, 'closed', 'Cerrado', 'system'), |
(11, 'reply_message_by_customer', 'Respuesta de propietario', 'active', '2024-12-16 09:00:49', '2024-12-16 13:38:15', 56, 0, '#fffffff', '#fffffff', 0, 'open', 'Abierto', 'customer'), |
(12, 'reply_message_by_agent', 'Respuesta de agente', 'active', '2024-12-16 13:40:30', '2024-12-16 13:40:30', 56, 0, '#fffffff', '#fffffff', 0, 'open', 'Abierto', 'agent'); |
|
/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */; |
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; |
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */; |
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */; |
The issue I'm reporting here, is not a Matomo issue. it is a an issue with a custom app.
The Matomo screenshot previously attached is just for reference, showing that there are other people around there having the same issue with MariaDB
Your status_fx_get_filter_customer() returns varchar(50) COLLATE utf8mb4_general_ci.
Your default connection charset is utf8mb4 and default collation for utf8mb4 is utf8mb4_uca1400_ai_ci. So your 'aa' string implicitly has COLLATE utf8mb4_uca1400_ai_ci. Thus the error.
You can change the function to return COLLATE utf8mb4_uca1400_ai_ci. Or remove the COLLATE clause completely and let it use the default collation (which is utf8mb4_uca1400_ai_ci). Or use COLLATE clause during comparison. Or set collation_connection. Or change character_set_collations
Updated with a few links - text is easier to follow than images.
Can you identify the query an table structure related to the "Illegal mix of collations" error?
If its not easily identifiable the plugin https://mariadb.com/kb/en/sql-error-log-plugin/ that is already installed but not enabled will log the error on retriggering the same event.
Please include SHOW CREATE TABLE {tablename} for those table and the SQL query executed.
So the cause is that the expression is comparing two field in a SQL query that have different collations. As collations define how characters are compared there is no obvious selection as to which collation to use.
The Matomo solution (3), https://matomo.org/faq/troubleshooting/how-do-i-resolve-the-error-illegal-mix-of-collations-for-operation/, convert the collations to a uniform utf8mb4_uca1400_ai_ci would be a reasonable solution to your application?