Error when linking tickets: "Cannot execute SQL" due to wrong datetime format in dt_created
Posted: Sat Jun 14, 2025 3:30 am
I encountered a persistent error when trying to link two tickets using the "Link Ticket" feature in HESK. Instead of successfully linking the tickets, the system displayed an error page with the message:
Cannot execute SQL
Cause of the Problem
After some debugging, I found the issue in the SQL INSERT statement in admin_ticket.php, at line 736:
In my installation, the hesk_date() function returns dates in the Brazilian format (d/m/Y H:i:s), for example: 13/06/2025 23:56:59. However, MySQL’s DATETIME field requires the format Y-m-d H:i:s (2025-06-13 23:56:59).
This mismatch caused MySQL to reject the INSERT operation and triggered the error.
Note: In setups where hesk_date() (or the locale) returns dates in American format (Y-m-d H:i:s), this error may not happen, which is why it can go unnoticed in some environments.
Solution
To resolve this, I modified line 736 to use MySQL’s built-in NOW() function for the dt_created column, which always produces a compatible format:
After making this change, the tickets were successfully linked and the error no longer occurred.
Summary
Cannot execute SQL
Cause of the Problem
After some debugging, I found the issue in the SQL INSERT statement in admin_ticket.php, at line 736:
Code: Select all
$q = "INSERT INTO ".hesk_dbEscape($hesk_settings['db_pfix'])."linked_tickets (ticket_id1, ticket_id2, dt_created) VALUES ('".hesk_dbEscape($ticket['id'])."', '".hesk_dbEscape($get_ticket_data['id'])."','".hesk_date()."')"; This mismatch caused MySQL to reject the INSERT operation and triggered the error.
Note: In setups where hesk_date() (or the locale) returns dates in American format (Y-m-d H:i:s), this error may not happen, which is why it can go unnoticed in some environments.
Solution
To resolve this, I modified line 736 to use MySQL’s built-in NOW() function for the dt_created column, which always produces a compatible format:
Code: Select all
$q = "INSERT INTO ".hesk_dbEscape($hesk_settings['db_pfix'])."linked_tickets (ticket_id1, ticket_id2, dt_created) VALUES ('".hesk_dbEscape($ticket['id'])."', '".hesk_dbEscape($get_ticket_data['id'])."', NOW())";Summary
- File: admin_ticket.php
- Line: 736
- Problem: Incorrect datetime format for dt_created when linking tickets (Brazilian format: d/m/Y H:i:s)
- Note: This issue may not occur in environments using the American date format (Y-m-d H:i:s)
- Solution: Use NOW() in the SQL statement instead of a PHP-formatted date