Jump to content
omine

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

Recommended Posts

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
 
 
Seems the 5.7 did some changes that may cause many bugs like this.
 
How to solve or prevents?
 
 
 

Share this post


Link to post
Share on other sites

 

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 by omine (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

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!

  • Like 2

Share this post


Link to post
Share on other sites

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 by filerem1 (see edit history)

Share this post


Link to post
Share on other sites

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 by kirubanidhi (see edit history)

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites
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 by CORIYATSIA (see edit history)

Share this post


Link to post
Share on other sites

Use MySQL 5.6 or upgrade your PrestaShop version

Share this post


Link to post
Share on other sites

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;
	}

 

Share this post


Link to post
Share on other sites
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!!!

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More