Database Error After Installation

Support
1

After installation and login I receive the following error:

A Database Error Occurred
Error Number: 1525

Incorrect DATE value: '0000-00-00'

SELECT SQL_CALC_FOUND_ROWS ip_quotes.*, ip_users.user_name, ip_users.user_company, ip_users.user_address_1, ip_users.user_address_2, ip_users.user_city, ip_users.user_state, ip_users.user_zip, ip_users.user_country, ip_users.user_phone, ip_users.user_fax, ip_users.user_mobile, ip_users.user_email, ip_users.user_web, ip_users.user_vat_id, ip_users.user_tax_code, ip_users.user_subscribernumber, ip_users.user_iban, ip_users.user_gln, ip_users.user_rcc, 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
2

Are you able to change source code? If so I could provide a hackish-workaround.

3

Yes. I can change the source code.

4

You can change the following file - application/modules/invoices/models/Mdl_invoices.php

public function default_select()
{
    $this->db->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.*", false);
}

In the function look for the line (line 69 on my version)
(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,

and change the ‘0000-00-00’ to read something line ‘1970-01-01’

(CASE (SELECT COUNT(*) FROM ip_invoices_recurring WHERE ip_invoices_recurring.invoice_id = ip_invoices.invoice_id and ip_invoices_recurring.recur_next_date <> '1970-01-01') WHEN 0 THEN 0 ELSE 1 END) AS invoice_is_recurring,

Explanation

The error is due to the mysql version or mysql settings which does not allow ‘0000-00-00’ as a valid date.

The fix for this is to change the source code.

The correct workaround for this to to disable strict mode on mysql. Since I am not aware of how familiar you are with the mysql administration, we can use the above back.

Why ‘1970-01-01’

Dates on Unix systems are stored as the number of seconds since 1970-01-01 (called the Epoch). This scheme is adopted by mysql as well.

The field cannot be NULL, It requires a valid value.

That date will be 0 second since the Epoch (which is equivalent to an “empty” date.

3 Likes
5

It worked like a charm. Thank you!

6

I just use the second method, ie. add a line to my.cnf and it worked too.

[MySQL]
…
…
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
…

7

This fixed the issue for me aswell, thanks a lot! I couldn’t do what was stated here A Database Error Occurred - Error Number: 1525 - #6 since the database is running in a shared space I couldn’t use the global modifier and without it wouldn’t work.

8

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.