Import isssue

Hi,
 
I’m getting the following error on Step 6….
CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘created_at’ at row 1

  1. Profile photo of lvmedialvmedia 230.00 $tone January 27, 2021
    Also note the created_at field doesn't have any 0000-00-00 00:00:00 values. All have a date format like 2021-01-27 13:29:40

25 answers

Profile photo of Mall Staff 162290.00 $tone January 28, 2021
Public

Hi there,

I’m getting the following error on Step 6….
CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘created_at’ at row 1

Also note the created_at field doesn’t have any 0000-00-00 00:00:00 values. All have a date format like 2021-01-27 13:29:40

Please check the log file of our module at pub/ub-tool/protected/runtime/ub_data_migration.log
and provide me more detail about the context you got that issue.

What is the related data table which raises that issue?
 
I will help to check further and get back to you then.
 
Regards,
Mall.

#1
Profile photo of lvmedia 230.00 $tone January 28, 2021
Public

The error log comes up with the same errror….
 
2021/01/28 10:03:52 [error] [ub_data_migration] CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘created_at’ at row 1

#2
Profile photo of Mall Staff 162290.00 $tone January 28, 2021
Public

Hi there,

What tables are used to transfer the data for Step 6?

There are many tables related to data migration in step #6. If you can not provide more details about the error message, let’s provide me information about your instance:
+ Admin credentials and URL to the backend of your M2 site
+ SSH credentials and let me know the path to the M2 folder
I will help to check further and get back to you then.
Regards,
Mall.

#5
Profile photo of lvmedia 230.00 $tone January 28, 2021
Public

Also, I’ve imported all the products (step 5) and transferred the images across (which show in the admin), and made the shop is pointing to the create migrated menu etc, cleared cache and reindexed. But the products don’t seem to show on the front-end website. Will I need to complete steps 6 and 7 before they show up?
 
Thanks 

#8
Profile photo of Mall Staff 162290.00 $tone January 29, 2021
Public

Hi there,

Did you access info work ok?

I checked in your instance and I couldn’t access the backend of your site: http://prntscr.com/xsanm2

It seems that you have not deployed the static files on your site successfully yet. Please consider re-deploying static files again at your end. And then, I will help to check further on other issues you mentioned.

I’ve attached the Putty private key (SSH) — sent this to your email address.

Please send over to our info (at) ubertheme.com again, I did not see any email associated with your profile though. 

Also, I’ve imported all the products (step 5) and transferred the images across (which show in the admin), and made the shop is pointing to the create migrated menu etc, cleared cache and reindexed.

Please make sure that your products are ready in stock and enabled. And then, make sure that you did reindex the data successfully. You can run the following command to check the states of indexers:
php -f bin/magento indexer:status
 
Regards,
Mall.
 
 

#9
Profile photo of ubdev Staff 79160.00 $tone January 31, 2021
Public

Hi there, 

Also sent info to email address [email protected] (including private key)

We’ve not received the credentials you provided yet via [email protected]. Please help to check again. 
Meanwhile, please provide SSH credentials and let us know the path to your M2 folder as well. 
Regards,
Ubertheme team

#13
Profile photo of Mall Staff 162290.00 $tone February 1, 2021
Public

Hi there,

But the products don’t seem to show on the front-end website. 

I checked your instance and saw the root cause of that issue was because you hadn’t reindexed the data successfully yet:
http://prntscr.com/xy7q99
http://prntscr.com/xy7uy0

Please consider reindexing the data in your M2 once again. You can try these steps:
+ You reset all Magento’s indexers by running the CLI command:

php -f bin/magento indexer:reset

and then, you reindex the data using the CLI command:

php -d memory_limit=-1 -f bin/magento indexer:reindex

And then let me how it goes.

Regards,
Mall.
 
 
 
 

#17
Profile photo of lvmedia 230.00 $tone February 1, 2021
Public

Hi,
I’ve sent you all the information via email and WeTransfer (Just in case).
The extension is working great so far… very impressed. However i just have the following issues:

1. Following error when trying to import the customer data (step 6): CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘created_at’ at row 1
2. I’ve transfer all the product images but a lot of them are not showing in the admin.

3. I can’t seem to be able to see any of the products on the front end. 
4. When i click on a product to edit in the admin i get an error

I’ve the usually things like reindex, clear cache etc

Thanks for your help.

#18
Profile photo of Mall Staff 162290.00 $tone February 2, 2021
Public

Hi there,

1. Following error when trying to import the customer data (step 6): CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘created_at’ at row 1

I checked your instance and saw that you got that issue in step #6: http://prntscr.com/xzx3xi
http://prntscr.com/xzx4ig

That related to the bad data case in your M1.customer_entity.created_at field. To handle that case, you can try these steps:

+ Open the PHP file at: pub/ub-tool/protected/controllers/Step6Controller.php
and find the code lines:

//save/update
if (!$customer2->save()) {

and replace it with:

if ($customer2->updated_at === '0000-00-00 00:00:00' || empty($customer2->updated_at)) {
$customer2->updated_at = date("Y-m-d H:i:s");
}
if ($customer2->created_at === '0000-00-00 00:00:00' || empty($customer2->created_at)) {
$customer2->created_at = date("Y-m-d H:i:s");
}
//save/update
if (!$customer2->save()) {

+ And then, you continue with data migration in step #6:

php -f bin/ubdatamigration run --step=6

2. I’ve transfer all the product images but a lot of them are not showing in the admin.

Please make sure that you did fully copy media files following the steps noted in the Readme.html (packed with your download package). 

And make sure that you set the right permission for folders/files of medial files after moving to M2.

Next, you try running the following CLI commands:

php -dmemory_litmit=-1 -f bin/magento catalog:images:resize
php -f bin/magento cache:clean

and then, you check the issue you mentioned again.

3. I can’t seem to be able to see any of the products on the front end. 
4. When i click on a product to edit in the admin i get an error

I helped to enable the developer mode in your instance:

[[email protected] public_html]$ php -f bin/magento deploy:mode:set developer;
Enabled developer mode.

In the product grid views in the backend, I saw almost all your products had missing value of the ‘salable quantity’: http://prntscr.com/xzxk19 which related to the products not showing in the frontend issue you mentioned.

And when viewing a product on your site, I got 500 error: http://prntscr.com/xzxm03
and then, I did enable the full developer mode in your instance, and I saw the root cause of that issue:

Fatal error: Uncaught TypeError: Argument 2 passed to Magento\Framework\View\Element\UiComponentFactory::argumentsResolver() must be of the type array, null given, called in /home/cbmpaymonthlysit/public_html/vendor/magento/framework/View/Element/UiComponentFactory.php on line 222 and defined in /home/cbmpaymonthlysit/public_html/vendor/magento/framework/View/Element/UiComponentFactory.php:174 Stack trace: #0 /home/cbmpaymonthlysit/public_html/vendor/magento/framework/View/Element/UiComponentFactory.php(222): Magento\Framework\View\Element\UiComponentFactory->argumentsResolver(‘preorder’, NULL) #1 /home/cbmpaymonthlysit/public_html/vendor/magento/module-

that issue related to the bad setting of the product attribute: preorder
Please check that attribute in your M2 DB: http://prntscr.com/xzxqdk
To solve that, issue you can run the following SQL query in your M2 DB to change the setting of that attribute:

Update mg9w_eav_attribute Set frontend_input = 'text' Where attribute_code = 'preorder';

And then, you check the issue on viewing a product in the backend again.

Once done, you check the issue with the ‘saleable quantity’ I mentioned above and then you reindex the data again and check your products in the frontend again.
 
Regards,
Mall.
 
 
 
 
 

#19
Profile photo of lvmedia 230.00 $tone February 2, 2021
Public

Thanks much appreciated they all worked perfectly.
I’ve bumped into a small issue with Step 7 so nearly done. I get the following error when i try to import the sales data…
Mage2SalesOrderStatus: Status is too long (maximum is 32 characters).
Mage2SalesOrderStatusState: Status is too long (maximum is 32 characters).

#20
Profile photo of Mall Staff 162290.00 $tone February 3, 2021
Public

Hi there,

Mage2SalesOrderStatus: Status is too long (maximum is 32 characters).

That was because your M1 DB had at least one data record which had a bad data value of the “sales_order_status.status” field.

You can run the SQL below in your M1 DB to look for those bad data records at your end:

SELECT * FROM `sales_order_status` WHERE LENGTH(`status`) > 32;

and then, you have to update the string value of the “sales_order_status.status” field to reduce the length of the string value (max length allowed in M2 = 32 characters) in the data records that you find.

Mage2SalesOrderStatusState: Status is too long (maximum is 32 characters).

That is a similar case with the bad data in your M1 DB, but it happened in other table “sales_order_status_state.status“. 

You can run the following SQL query in your M1 DB to look for those data records:

SELECT * FROM `sales_order_status_state` WHERE LENGTH(`status`) > 32;

and then, you update the string value of the “sales_order_status_state.status” field to reduce the length of the string value (max length allowed in M2 = 32 characters) on the found data records.
 
After that, you can continue with the data migration in step #7

Regards,
Mall.

#21
Profile photo of lvmedia 230.00 $tone February 16, 2021
Public

Hi, Thanks for all your help so far. Everything works great, however, I’ve just bumped in to one small issue that I’ve only just noticed. I migrated the data for two stores (Multi-Site). I’ve checked the Magento 2 database and it looks like all the product data for store 1 and store 2 has been successfully transferred from my existing Magento 1.9 website over to Magento 2 website. The one thing I’ve noticed is all the products prices are the same for both stores (which shouldn’t be the case). So when i go in to one of the products on store 1: http://contactleft2.paymonthlysite.co.uk/diy-tactical-buckles-fabrics/fidlock.html the price is correct, but on store 2 the price should be half the price… I’ve changed the scope in the product to store 2 (cl distribution) and the price is still the same as store 2. It seems when I change the scope to store 2 for any of the products it doesn’t change to any of the store 2 data. Any ideas? many thanks

#22
Profile photo of ubdev Staff 79160.00 $tone February 17, 2021
Public

Hi there, 
Please help to check your product settings again. If the issue still persists, please provide us more details about the issue and the illustration screenshots (if available). 
Also, please confirm if your site credentials (provided via email on Feb 2, 2021) remains available. 
Regards,
Ubertheme team

#23
Profile photo of lvmedia 230.00 $tone February 17, 2021
Public

Hi, Thanks for your prompt reply. 
The site credentials are the same.
I’ve sent everything to your email address including screenshots.
 
Thanks
Iain
 

#24
Profile photo of Mall Staff 162290.00 $tone February 18, 2021
Public

Hi Iain,

I’ve followed the following guide to change the catalog price scope to ‘website’: https://meetanshi.com/blog/configure-magento-2-price-scope/
However, when I change the scope in products to store 2 (CL Distribution) I can’t change the price to a different one to store 1 (Contact Left).

Please note that the setting of ‘Catalog Price Scope’ (at https://prnt.sc/zxapn3) has a relationship with the setting of ‘Base Currency’ at https://prnt.sc/zxaj9u

And I saw that the scope of the Price field was changed from ‘Global’ to ‘Website’ by your custom setting in the editing product page: http://prntscr.com/zxcr9w

And the issue you mentioned related to the Magento2′ functions only. You can learn more about the Prices settings by Scopes via this Magento official post.

I tested that with a newly added product SKU = test123 and I got the same issue: http://prntscr.com/zxe8ec

That said, it doesn’t relate to our migration tool. Please check again. 
 
Regards,
Mall.
 

#25

Please login or Register to Submit Answer

Written By

Comments