Can't execute SQL: SELECT COUNT(*) AS `cnt`, `status`, IF (`owner` = 2, 1, IF (`owner` = 0, 0, IF (`assignedby` = 2, 3, 2) ) ) AS `assigned_to`, IF (`due_date` < NOW(), 2, IF (`due_date` BETWEEN NOW() AND (NOW() + INTERVAL 7 DAY), 1, 0) ) AS `due` FROM `hesk_tickets` WHERE 1 AND 1 AND `name` LIKE '%test%' COLLATE 'utf8_unicode_ci' GROUP BY `assigned_to`, `due`, `status`
MySQL said:
COLLATION 'utf8mb3_unicode_ci' is not valid for CHARACTER SET 'latin1'
I have combed through the settings on the DB and can't find a reference to the latin1 anywhere. We currently have the following:
Debian 12
Apache2= 2.4.61-1~deb12u1
mariadb-server-core =1:10.11.6-0+deb12u1
hesk=3.4.6
The MariaDB server, database, tables and columns have no reference to latin1. Any thoughts on how to resolve this?
This usually happens when the database charset is set to a different one than what Hesk sets during the installation (maybe due to a database change/move or underlying SQL server change? I Don't know).
If you try to export the hesk_tickets table structure in SQL (using phpMyAdmin for example, or mysqldup), what does the SQL structure code say?
Klemen, creator of HESK and PHPJunkyardWas this helpful? You can buy me a drink here
As a work around, I ended up removing the COLLATE clauses from the SQL queries that the find_ticket.php was sending and that stopped the error from occurring.
Looking at the hesk_tickets table in the mysql dump file It looks as if the tables were configured with utf8. Here is what I see:
-- MariaDB dump 10.19 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: hesksandbox
-- ------------------------------------------------------
-- Server version 10.11.6-MariaDB-0+deb12u1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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 */;
--
-- Table structure for table `hesk_tickets`
--
DROP TABLE IF EXISTS `hesk_tickets`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `hesk_tickets` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`trackid` varchar(13) NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
`email` varchar(1000) NOT NULL DEFAULT '',
`category` smallint(5) unsigned NOT NULL DEFAULT 1,
`priority` enum('0','1','2','3') NOT NULL DEFAULT '3',
`subject` varchar(255) NOT NULL DEFAULT '',
`message` mediumtext NOT NULL,
`message_html` mediumtext DEFAULT NULL,
`dt` timestamp /* mariadb-5.3 */ NOT NULL DEFAULT '2000-01-01 08:00:00',
`lastchange` timestamp /* mariadb-5.3 */ NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`firstreply` timestamp /* mariadb-5.3 */ NULL DEFAULT NULL,
`closedat` timestamp /* mariadb-5.3 */ NULL DEFAULT NULL,
`articles` varchar(255) DEFAULT NULL,
`ip` varchar(45) NOT NULL DEFAULT '',
`language` varchar(50) DEFAULT NULL,
`status` tinyint(3) unsigned NOT NULL DEFAULT 0,
`openedby` mediumint(8) DEFAULT 0,
`firstreplyby` smallint(5) unsigned DEFAULT NULL,
`closedby` mediumint(8) DEFAULT NULL,
`replies` smallint(5) unsigned NOT NULL DEFAULT 0,
`staffreplies` smallint(5) unsigned NOT NULL DEFAULT 0,
`owner` smallint(5) unsigned NOT NULL DEFAULT 0,
`assignedby` mediumint(9) DEFAULT NULL,
`time_worked` time /* mariadb-5.3 */ NOT NULL DEFAULT '00:00:00',
`lastreplier` enum('0','1') NOT NULL DEFAULT '0',
`replierid` smallint(5) unsigned DEFAULT NULL,
`archive` enum('0','1') NOT NULL DEFAULT '0',
`locked` enum('0','1') NOT NULL DEFAULT '0',
`attachments` mediumtext NOT NULL,
`merged` mediumtext NOT NULL,
`history` mediumtext NOT NULL,
`custom1` mediumtext NOT NULL,
`custom2` mediumtext NOT NULL,
`custom3` mediumtext NOT NULL,
`custom4` mediumtext NOT NULL,
`custom5` mediumtext NOT NULL,
`custom6` mediumtext NOT NULL,
`custom7` mediumtext NOT NULL,
`custom8` mediumtext NOT NULL,
`custom9` mediumtext NOT NULL,
`custom10` mediumtext NOT NULL,
`custom11` mediumtext NOT NULL,
`custom12` mediumtext NOT NULL,
`custom13` mediumtext NOT NULL,
`custom14` mediumtext NOT NULL,
`custom15` mediumtext NOT NULL,
`custom16` mediumtext NOT NULL,
`custom17` mediumtext NOT NULL,
`custom18` mediumtext NOT NULL,
`custom19` mediumtext NOT NULL,
`custom20` mediumtext NOT NULL,
`custom21` mediumtext NOT NULL,
`custom22` mediumtext NOT NULL,
`custom23` mediumtext NOT NULL,
`custom24` mediumtext NOT NULL,
`custom25` mediumtext NOT NULL,
`custom26` mediumtext NOT NULL,
`custom27` mediumtext NOT NULL,
`custom28` mediumtext NOT NULL,
`custom29` mediumtext NOT NULL,
`custom30` mediumtext NOT NULL,
`custom31` mediumtext NOT NULL,
`custom32` mediumtext NOT NULL,
`custom33` mediumtext NOT NULL,
`custom34` mediumtext NOT NULL,
`custom35` mediumtext NOT NULL,
`custom36` mediumtext NOT NULL,
`custom37` mediumtext NOT NULL,
`custom38` mediumtext NOT NULL,
`custom39` mediumtext NOT NULL,
`custom40` mediumtext NOT NULL,
`custom41` mediumtext NOT NULL,
`custom42` mediumtext NOT NULL,
`custom43` mediumtext NOT NULL,
`custom44` mediumtext NOT NULL,
`custom45` mediumtext NOT NULL,
`custom46` mediumtext NOT NULL,
`custom47` mediumtext NOT NULL,
`custom48` mediumtext NOT NULL,
`custom49` mediumtext NOT NULL,
`custom50` mediumtext NOT NULL,
`due_date` timestamp NULL DEFAULT NULL,
`overdue_email_sent` tinyint(1) DEFAULT 0,
`satisfaction_email_sent` tinyint(1) DEFAULT 0,
`satisfaction_email_dt` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `trackid` (`trackid`),
KEY `archive` (`archive`),
KEY `categories` (`category`),
KEY `statuses` (`status`),
KEY `owner` (`owner`),
KEY `openedby` (`openedby`,`firstreplyby`,`closedby`),
KEY `dt` (`dt`),
KEY `assignedby` (`assignedby`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
This all looks good to me. I am afraid I'm out of ideas why you were getting the SQL error. Perhaps I could get clues if I tested the server "hands-on," but with the data I have, everything looks as it should.
Klemen, creator of HESK and PHPJunkyardWas this helpful? You can buy me a drink here
