Mysql 5.7 Causes Error When Ps Try To Insert Date As 0000-00-00 00:00:00

[omine]

I'm testing current PRestaShop version, 1.6.1.3, under MySQL 5.7

 

I found a bug on order confirmation page:

 

"500 server error"

 

After enable the debug mode, i could see the problem:

Incorrect datetime value: '0000-00-00 00:00:00' for column 'invoice_date' at row 1

INSERT INTO `ps_orders` (`id_address_delivery`, `id_address_invoice`, `id_cart`, `id_currency`, `id_shop_group`, `id_shop`, `id_lang`, `id_customer`, `id_carrier`, `current_state`, `secure_key`, `payment`, `module`, `recyclable`, `gift`, `gift_message`, `mobile_theme`, `total_discounts`, `total_discounts_tax_incl`, `total_discounts_tax_excl`, `total_paid`, `total_paid_tax_incl`, `total_paid_tax_excl`, `total_paid_real`, `total_products`, `total_products_wt`, `total_shipping`, `total_shipping_tax_incl`, `total_shipping_tax_excl`, `carrier_tax_rate`, `total_wrapping`, `total_wrapping_tax_incl`, `total_wrapping_tax_excl`, `round_mode`, `round_type`, `shipping_number`, `conversion_rate`, `invoice_number`, `delivery_number`, `invoice_date`, `delivery_date`, `valid`, `reference`, `date_add`, `date_upd`) VALUES ('5', '5', '6', '1', '1', '1', '1', '2', '3', '0', 'af2c4771ba022eb69f1898c79858707a', 'Bank wire', 'bankwire', '0', '0', NULL, '0', '0', '0', '0', '5541.52', '5541.52', '5277.63', '0', '5270.63', '5534.17', '7.35', '7.35', '7', '5', '0', '0', '0', '2', '2', NULL, '1', '0', '0', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0', 'IRUIKTIKL', '2015-12-23 20:52:51', '2015-12-23 20:52:51')

 

The test is running from fresh PrestaShop installation.

 

No overrides, no third-party modules. All default settings.

 

I found something regarding this issue with MySQL5.7

 

https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade

 

Seems the 5.7 did some changes that may cause many bugs like this.

 

How to solve or prevents?

 

 

 

[omine]

 

Found solution

 

MySQL 5.7 default settings, sql-mode is

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

On MySQL settings file, add the line or edit if exists:

sql-mode="NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO"

Linux:

> vi /etc/my.cnf

> service mysqld restart

 

 

 

Edited December 25, 2015 by omine (see edit history)

[Sylvain CM]

Hi, thank you for your feedback, I've informed our developers!

[Julien Bourdeau]

Hi omine,

 

PrestaShop uses 0-dates a lot which makes it incompatible with MySQL 5.7.

 

Changing your server configuration is the best way, however if you can't modify it, you can apply this fix: https://github.com/PrestaShop/PrestaShop/pull/4507/files

 

We found a few other bugs, we'll group all fixes under the "MySQL 5.7" label: https://github.com/PrestaShop/PrestaShop/pulls?q=label%3A%22Mysql+5.7%22

 

If you find another problem with this mysql version, please report it on forge.prestashop.com (or update this thread).

 

Thank you!

[filerem1]

Hi everybody, and sorry for re-open this post, but i have same problem in my shop. I have testing this solution,, but nothing it's appened...

other solution and results is the same.

 

I am in a dead end, and i don't know what's the prblem is.

 

My mysql-server version is 5.5.54 , my server an ubuntu 14.04 and my prestashop is 1.6.0.11.

 

I have tested a fresh install and it's work fine!

I dont' understand everything anymore...

 

Thanks if you have a solution for this.

have a nice day.

 

Philippe. ( and sorry for my english)

Edited April 28, 2017 by filerem1 (see edit history)

[kirubanidhi]

Check your MySql Version by using below comment,

Mysql > Select Version

Your version is above 5.7.21-0ubuntu0.16.04.1

Then check sql mode

Mysql > SELECT [spam-filter]sql_mode

After then execute the below code in your phpmyadmin or Mysql

SET GLOBAL sql_mode = ''

 

 

 

Edited April 24, 2018 by kirubanidhi (see edit history)

[alx1]

Hello everibody,

I am facing with an issue with standard tool importing products in Prestashop.

PrestaShop: 1.4.7.0

System: Linux #1 SMP Tue Dec 4 03:50:32 EST 2018 x86_64

Version: LiteSpeed

PHP: 5.6.40

MySQL: 10.2.19-MariaDB-cll-lve

The error which I got when importing is:

Incorrect datetime value: '' for column 'date_upd' at row 1

In the log file i found:

[15-Jan-2019 18:58:25 Europe/Bucharest] PHP Warning:  Illegal string offset 'label' in /home/................AdminImport.php on line 359

I have no acces to change the configuration file of mysql.

 

I will apreciate if somebody face with the same issue and solve it to share wiyh me here the sollution.

From my point of view there should be a problem of the date format string.

Thanks in advance.

 

[CORIYATSIA]

On 4/28/2017 at 8:57 AM, filerem1 said:

Hi everybody, and sorry for re-open this post, but i have same problem in my shop. I have testing this solution,, but nothing it's appened...

other solution and results is the same.

 

I am in a dead end, and i don't know what's the prblem is.

 

My mysql-server version is 5.5.54 , my server an ubuntu 14.04 and my prestashop is 1.6.0.11.

Audacity Find My iPhone Origin

I have tested a fresh install and it's work fine!

I dont' understand everything anymore...

 

Thanks if you have a solution for this.

have a nice day.

 

Philippe. ( and sorry for my english)

I found a bug on order confirmation page:

Edited March 17, 2019 by CORIYATSIA (see edit history)

[Janett]

Use MySQL 5.6 or upgrade your PrestaShop version

[Eolia]

or update your /classes/db/DbPDO.php file 

	/**
	 * @see DbCore::connect()
	 */
	public function connect()
	{
		try {
			$this->link = $this->_getPDO($this->server, $this->user, $this->password, $this->database, 5);
		} catch (PDOException $e) {
			die(sprintf(Tools::displayError('Link to database cannot be established: %s'), utf8_encode($e->getMessage())));
		}

		// UTF-8 support
		if ($this->link->exec('SET NAMES \'utf8\'') === false)
			die(Tools::displayError('PrestaShop Fatal error: no utf-8 support. Please check your server configuration.'));
		// MYSQL 5.7 compat
            	if ($this->link->exec('set session sql_mode=\'\'') === false)
                	die(Tools::displayError('PrestaShop Fatal error'));
		return $this->link;
	}

 

[wokan09]

On 12/23/2015 at 1:44 PM, omine said:

 

Found solution

 

MySQL 5.7 default settings, sql-mode is

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

On MySQL settings file, add the line or edit if exists:

sql-mode="NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO"

Linux:

> vi /etc/my.cnf

> service mysqld restart

 

 

 

After 11 hours looking for the problem, without eat, and crazy you save my day, thanks a lot!!!

[limitcracker]

On 7/23/2020 at 8:43 PM, wokan09 said:

After 11 hours looking for the problem, without eat, and crazy you save my day, thanks a lot!!!

Hi, thanks a lot!

For MAMP Pro users you can see the above fix in MAMP Pro:

taken from https://stackoverflow.com/questions/51986993/how-to-disable-strict-mode-in-mysql-using-mamp-on-a-mac

[pottslewis82]

Check your MySql Version by using below comment,

Mysql > Select Version

Your version is above 5.7.21-0ubuntu0.16.04.1

Then check sql mode 

Mysql > SELECT [spam-filter]sql_mode

After then execute the below code in your phpmyadmin or Mysql

Edited January 11, 2024 by pottslewis82 (see edit history)

[Forest Lymon]

You're encountering this issue because MySQL 5.7 introduced stricter default settings that do not allow invalid or zero dates like 0000-00-00 00:00:00 in strict SQL mode.

✅ Why This Error Happens

In MySQL 5.7 and later, STRICT_TRANS_TABLES mode is enabled by default. This means:

MySQL will reject invalid or zero date values like 0000-00-00 00:00:00 when inserting or updating data.

This helps prevent silently storing incorrect data.

So, if your application or script tries to insert a date value of 0000-00-00 00:00:00, it will trigger an error like:

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00'

🛠️ How to Fix It

You have a few options depending on your needs:

1. Use a Valid Default Date Instead

Instead of 0000-00-00 00:00:00, use:

• NULL if the date is optional.
• A real default date like 1970-01-01 00:00:00.

2. Disable Strict Mode (Not Recommended for Production)

You can relax MySQL’s strict mode, but this can lead to poor data quality.

Temporary (for current session):

SET sql_mode = '';

Permanent (edit config file):
In my.cnf or my.ini, comment out or remove STRICT_TRANS_TABLES:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

Then restart MySQL.

⚠️ Disabling strict mode is not recommended for production environments unless you're certain it's safe. For more practical guides like this on databases, development tips, and troubleshooting, check out ZainabSays — your go-to resource for clear and concise tech advice!

3. Check Your ORM or App Configuration

If you're using frameworks like Laravel or WordPress, check how dates are handled. Set default values appropriately or adjust your model/database rules.