A Database Error Occurred - Error Number: 1525

[hyprem]

HI there

I have updated my MySQL Server to a newer Version. Since then I get the following error on login:
A Database Error Occurred
Error Number: 1525

Incorrect DATE value: '0000-00-00'

SELECT SQL_CALC_FOUND_ROWS ip_quotes.*, ip_users.*, ip_clients.*, ip_invoice_sumex.*, ip_invoice_amounts.invoice_amount_id, IFnull(ip_invoice_amounts.invoice_item_subtotal, '0.00') AS invoice_item_subtotal, IFnull(ip_invoice_amounts.invoice_item_tax_total, '0.00') AS invoice_item_tax_total, IFnull(ip_invoice_amounts.invoice_tax_total, '0.00') AS invoice_tax_total, IFnull(ip_invoice_amounts.invoice_total, '0.00') AS invoice_total, IFnull(ip_invoice_amounts.invoice_paid, '0.00') AS invoice_paid, IFnull(ip_invoice_amounts.invoice_balance, '0.00') AS invoice_balance, ip_invoice_amounts.invoice_sign AS invoice_sign, (CASE WHEN ip_invoices.invoice_status_id NOT IN (1, 4) AND DATEDIFF(NOW(), invoice_date_due) > 0 THEN 1 ELSE 0 END) is_overdue, DATEDIFF(NOW(), invoice_date_due) AS days_overdue, (CASE (SELECT COUNT(*) FROM ip_invoices_recurring WHERE ip_invoices_recurring.invoice_id = ip_invoices.invoice_id and ip_invoices_recurring.recur_next_date <> '0000-00-00') WHEN 0 THEN 0 ELSE 1 END) AS invoice_is_recurring, ip_invoices.* FROM `ip_invoices` JOIN `ip_clients` ON `ip_clients`.`client_id` = `ip_invoices`.`client_id` JOIN `ip_users` ON `ip_users`.`user_id` = `ip_invoices`.`user_id` LEFT JOIN `ip_invoice_amounts` ON `ip_invoice_amounts`.`invoice_id` = `ip_invoices`.`invoice_id` LEFT JOIN `ip_invoice_sumex` ON `sumex_invoice` = `ip_invoices`.`invoice_id` LEFT JOIN `ip_quotes` ON `ip_quotes`.`invoice_id` = `ip_invoices`.`invoice_id` ORDER BY `ip_invoices`.`invoice_id` DESC LIMIT 10

Filename: core/MY_Model.php

Line Number: 157

What I did till now is I edited all data in the database form 0000-00-00 to a current date in the past.
However I stil get this error.

Could anyone help me please

[M4rt1n]

Are you using MariaDB?
If yes, have you updated to higher then 10.3?
If again yes, have you updated from <=v12 to >=13?

More infos about your setup would be good. If you update and there are changed (specially breaking changes) you always should run the needed update script.
Please notice that InvoicePlane does not provide then Updatescript but MariaDB should.

[hyprem]

Dear Martin_Anonym

Thanks for your reply! Yeah stupid me didnt post the Version.
So I have a Webserver running cPanel/WHM and currently installed is MySQL 8.0
Since cPanel did all the work by itself I am sure the upgrade scripts went trough without any Errors.
My php Version running on 7.2 and teh extensions as described from the requirements are installed.

[M4rt1n]

Just found the problem.
Please read here:

https://community2.invoiceplane.com/t/topic/9121/2

MySQL should be compatible there should not be any problems with in in particular. But the MySQL Server should be configurated correct.

The direct solution is this:

Error in MySQL when setting default value for DATE or DATETIME

BTW the command to run in MySQL:
SET GLOBAL sql_mode = '';

[hyprem]

Thanks Martin_Anonym

I can confirm this fixed the issue even on MySQL 8.