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
-
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
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.
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
What tables are used to transfer the data for Step 6?
What tables are used to transfer the data for Step 6?
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.
Did you access info work ok?
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
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.
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
I sent all the info on a private on here
Do you have another email account I can send them to maybe they’re being blocked by your domain
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.
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.
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:
[cbmpaymonthlysit@lvmedia2 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.
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).
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.
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
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
Hi, Thanks for your prompt reply.
The site credentials are the same.
I’ve sent everything to your email address including screenshots.
Thanks
Iain
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.