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
Posted (edited)
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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

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