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