Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
11.8.2
-
None
-
Ubuntu 24.04, n.a.
Description
Today we did an upgrade from 11.4 to 11.8.2. Later the day we found that some applications did not work any more as before.
The reason is that UNIX_TIMESTAMP() now returns NULL instead of 0 (zero):
This is old behaviour is OK and works correctly:
mariadb from 11.4.3-MariaDB, client 15.2 for linux-systemd (x86_64) using readline 5.1
SQL> show global variables like '%time_zone';
|
+------------------+--------+
|
| Variable_name | Value |
|
+------------------+--------+
|
| system_time_zone | CEST |
|
| time_zone | SYSTEM |
|
+------------------+--------+
|
|
|
root@mariadb-114 [(none)] SQL> select UNIX_TIMESTAMP('1970-01-01 01:00:00');
|
+---------------------------------------+
|
| UNIX_TIMESTAMP('1970-01-01 01:00:00') |
|
+---------------------------------------+
|
| 0 |
|
+---------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
root@mariadb-114 [(none)] SQL> select UNIX_TIMESTAMP('1970-01-01 00:00:00');
|
+---------------------------------------+
|
| UNIX_TIMESTAMP('1970-01-01 00:00:00') |
|
+---------------------------------------+
|
| NULL |
|
+---------------------------------------+
|
1 row in set (0.000 sec)
|
The new version behaves differently:
Server version: 11.8.2-MariaDB-log MariaDB Server
root@mariadb-118 [(none)] SQL> show global variables like '%time_zone';
|
+------------------+--------+
|
| Variable_name | Value |
|
+------------------+--------+
|
| system_time_zone | CEST |
|
| time_zone | SYSTEM |
|
+------------------+--------+
|
2 rows in set (0.001 sec)
|
|
|
root@mariadb-118 [(none)] SQL> select UNIX_TIMESTAMP('1970-01-01 01:00:00');
|
+---------------------------------------+
|
| UNIX_TIMESTAMP('1970-01-01 01:00:00') |
|
+---------------------------------------+
|
| NULL |
|
+---------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
root@mariadb-118 [(none)] SQL> select UNIX_TIMESTAMP('1970-01-01 00:00:00');
|
+---------------------------------------+
|
| UNIX_TIMESTAMP('1970-01-01 00:00:00') |
|
+---------------------------------------+
|
| NULL |
|
+---------------------------------------+
|
1 row in set (0.000 sec)
|
This new behaviour will break the following SQL query and the whole part of the application:
$sql = sprintf("INSERT INTO timesheet (id, begin_ts, end_ts, project_id, employee_id, work_performed) VALUES (%d, UNIX_TIMESTAMP('%s'), UNIX_TIMESTAMP('%s'), %d, %d ,'%s')" |
, $aTimesheetEntry['id'], $aTimesheetEntry['begin_ts'], $aTimesheetEntry['end_ts'], $aTimesheetEntry['project_id'], $aTimesheetEntry['employee_id'], $mysqli->real_escape_string($aTimesheetEntry['work_performed'])); |
with the following error:
[Mon Jun 16 20:01:01.809358 2025] [php:error] [pid 888375:tid 888375] [client 85.2.192.10:52300]
|
PHP Fatal error: Uncaught mysqli_sql_exception: Column 'end_ts' cannot be null in /opt/admin/lib/timesheet.inc:88
|
Stack trace:
|
#0 /opt/admin/lib/timesheet.inc(88): mysqli->query()
|
Attachments
Issue Links
- is caused by
-
MDEV-15751 CURRENT_TIMESTAMP should return a TIMESTAMP (WITH TIME ZONE?)
-
- Closed
-