Jump to content

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


omine

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?
 
 
 
Link to comment
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
Link to comment
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
Link to comment
Share on other sites

  • 1 year later...

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)
Link to comment
Share on other sites

  • 11 months later...
  • 8 months later...

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.

 

Link to comment
Share on other sites

  • 1 month later...
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)
Link to comment
Share on other sites

  • 3 weeks later...
  • 7 months later...

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

 

Link to comment
Share on other sites

  • 7 months later...
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
Link to comment
Share on other sites

  • 3 weeks later...
  • 3 years later...
  • 1 year later...

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.

  • Like 1
Link to comment
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...