MySQL database quote tables updated_at invalid default value

[stephan-cream]

Preconditions

Magento version up to 2.1.6 with MySQL version on or above 5.7

Steps to reproduce:

Run the following query. ALTER TABLE quote AUTO_INCREMENT = 1 -or- ALTER TABLE quote_item AUTO_INCREMENT = 1 -or- ALTER TABLE quote_address AUTO_INCREMENT = 1 -or- ALTER TABLE quote_address_item AUTO_INCREMENT = 1 -or- ALTER TABLE quote_payment AUTO_INCREMENT = 1 -or- ALTER TABLE quote_shipping_rate AUTO_INCREMENT = 1

Expected Result:

The tables have their auto increment id reset to 1. No errors.

Actual Result:

Error Code: 1067 Invalid default value for 'updated_at' (The increment id is NOT reset to 1).

While looking to reset my auto increments on each table i noticed the following error when trying to do the quote tables (e.g: quote, quote_address, quote_item).

ALTER TABLE quote AUTO_INCREMENT = 1

Error Code: 1067 Invalid default value for 'updated_at'

Currently we are running Magento 2.1.6, our MySQL version is 14.14 Distrib 5.7.18.

From some browsing around i noticed that there is a strict mode as of 5.7 and later. This error is caused because 0000-00-00 00:00:00 is not valid according to strict mode. As such altering the table is impossible unless the strict mode is disabled / bypassed.

Seeing as how most tables (e.g: sales_order, sales_shipment, sales_invoice) have CURRENT_TIMESTAMP as the default value for updated at my suggestion would be to also do this for the quote tables and other possible tables which have this invalid default value.

Affected tables:

• quote
• quote_item
• quote_address
• quote_address_item
• quote_payment
• quote_shipping_rate

[veloraven]

@stephan-cream thank you for your feedback. Please, format this issue according to the Issue reporting guidelines: with steps to reproduce, actual result and expected result. Please, also identify which version of Magento you are running.

[stephan-cream]

This feels like i'm just repeating myself here since i posted all data including how to repdroduce it and versions used for DB and Magento but here we go:

Preconditions

Magento version up to 2.1.6 with MySQL version on or above 5.7

Steps to reproduce:

Run the following query. ALTER TABLE quote AUTO_INCREMENT = 1 -or- ALTER TABLE quote_item AUTO_INCREMENT = 1 -or- ALTER TABLE quote_address AUTO_INCREMENT = 1 -or- ALTER TABLE quote_address_item AUTO_INCREMENT = 1 -or- ALTER TABLE quote_payment AUTO_INCREMENT = 1 -or- ALTER TABLE quote_shipping_rate AUTO_INCREMENT = 1

Expected Result:

The tables have their auto increment id reset to 1. No errors.

Actual Result:

Error Code: 1067 Invalid default value for 'updated_at' (The increment id is NOT reset to 1).

Documentation from MySQL on this subject:

• https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_allow_invalid_dates

Suggested fix: Change the default value for the above tables to CURRENT_TIMESTAMP as such is the case for almost all other tables such as sales_order.

[daniel-ifrim]

@veloraven Also, devs should take a look at Magento\Quote\Model\ResourceModel\Quote::loadByCustomerId which is called in API REST. In my case, on frontend it loads a quote and in API REST scope (called on cart page) it loads another quote because update_at is 0000-00-00 00:00:00. I'm in a case when the product was deleted but the product_id is still in a quote_item of this older quote that gets loaded in estimate-shipping-methods instead of the new quote because updated_at is 0000-00-00 00:00:00. In any case updated_at should not be 0000-00-00 00:00:00 because of loadByCustomerId function. As a result a 400 BAD request is triggered on call to /rest/default/V1/carts/mine/estimate-shipping-methods.

This issue seems to be related with https://github.com/magento/magento2/issues/7308

[goretail]

Open SQL file and add code: SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00";

So import again

[magento-engcom-team]

@stephan-cream, thank you for your report. We were not able to reproduce this issue by following the steps you provided. If you'd like to update it, please reopen the issue.

[alextricity]

I had this issue when I truncated some tables to try to wipe out some data... I ended up having to scap the entire site. I can confirm this is an issue with the latest version of Magento. I hate to see this closed as I have seen quite a few other desperate pleas in regard to this issue. I truncated most product and category tables too. Did you truncate any tables to get to this point?