I’ve gotten the following error when I attempt to migrate the sales data. Do you have any suggestion on how to track down the record that is causing mischief?
CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘converted_at’ at row 1
-
steelwave ⋅250.00 $tone February 6, 2017
I manage"d it: it's a ' issue it was in my attribute name. I juste removed it. I have unfortunattelly another issue: I tried to reinstalll everithing but I shave the migrtation menu x 3 liek this bellow:1 - Databases SettingsFinished Edit Settings 2 - Migrate Websites, StoresFinished Re-run Reset Edit Settings 3 - Migrate AttributesFinished Re-run Reset Edit Settings 4 - Migrate CategoriesFinished Re-run Reset Edit Settings 5 - Migrate ProductsFinished Re-run Reset Edit Settings 6 - Migrate CustomersFinished Re-run Reset Edit Settings 7 - Migrate SalesFinished Re-run Reset Edit Settings 8 - Migrate OthersPending Settings 9 - Databases SettingsPending Settings 10 - Migrate Websites, StoresPending Settings 11 - Migrate AttributesPending Settings 12 - Migrate CategoriesPending Settings 13 - Migrate ProductsPending Settings 14 - Migrate CustomersPending Settings 15 - Migrate SalesPending Settings 16 - Migrate OthersPending Settings 17 - Databases SettingsPending Settings 18 - Migrate Websites, StoresPending Settings 19 - Migrate AttributesPending Settings 20 - Migrate CategoriesPending Settings 21 - Migrate ProductsPending Settings 22 - Migrate CustomersPending Settings 23 - Migrate SalesPending Settings 24 - Migrate OthersPendingMay you help?
steelwave ⋅250.00 $tone
February 6, 2017
13 answers
Hi kasigi,
Let’s enable debug mode by edit the php file in the pub/ub-tool/index.php
#ini_set(‘display_errors’, 1);
=> ini_set(‘display_errors’, 1);
#defined(‘YII_DEBUG’) or define(‘YII_DEBUG’, true);
=> defined(‘YII_DEBUG’) or define(‘YII_DEBUG’, true);
And re-run the data migration in step #7.
And provide me the content of the log file of our tool at pub/ub-tool/protected/runtime/ub_data_migration.log
I will check further and help you out.
Regards,
Mall.
I just extracted what I suspect is the relevant section:
2017/02/06 00:03:56 [info] [ub_data_migration] Step #6 was finished.
2017/02/06 00:03:56 [info] [ub_data_migration] ——————--
2017/02/06 00:03:57 [info] [ub_data_migration] Start running step #7
2017/02/06 00:03:57 [info] [ub_data_migration] Migrated data in step #7 ok with 46 Sales Rules.
2017/02/06 00:03:57 [info] [ub_data_migration] Continue running step #7
2017/02/06 00:03:57 [info] [ub_data_migration] (Continued) migrated data in step #7 ok with 30 Sales Order Statuses.
2017/02/06 00:03:57 [info] [ub_data_migration] Continue running step #7
2017/02/06 00:03:57 [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 ‘converted_at’ at row 1
2017/02/06 00:04:29 [info] [ub_data_migration] Continue running step #7
2017/02/06 00:04:29 [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 ‘converted_at’ at row 1
Hi kasigi,
statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘converted_at’ at row 1
That issue come from you have some bad data with new rules of Magento 2 in the table sales_flat_quote.
To solve that let’s try run bellow sql directly in your Magento1 database:
Update sales_flat_quote Set converted_at = NULL Where converted_at = '0000-00-00 00:00:00';
And then, re-run the data migration in the step #7 of our tool.
And tell me know how it goes.
Regards,
Mall.
Interesting…. I just tried to run that SQL on the Magento1 DB and it is spitting back error 1292 again. I just looked at the underlying table definition and the column type is a timestamp which precludes a value of null in the first place.
Is the script expecting the table field to be a different data type?
Hi there,
Interesting…. I just tried to run that SQL on the Magento1 DB and it is spitting back error 1292 again.
Did you run the sql as my suggesting successfully?
If yes, that maybe there a another issue with other field in your database. Let’s provide me the full content of the log file of our tool.
I will check further and help you out.
Regards,
Mall.
Hi Mall --
I tried to run the SQL you suggested and running that SQL itself caused error 1292 and was not able to complete.
Hi Mall,
That might be a bit challenging for two reasons:
- The server is well behind the walls and there’s no inbound SSH access from the outside internet
- It’s a complete copy of our customer database and I’m not able to grant access even if it was reachable
Is there an alternative that I can look into?
Hi kasigi,
So, let’s enable debug mode as my suggesting in first reply.
And then, re-run data migration in the step #7
And provide me the full content of the log file of our tool at:
pub/ub-tool/protected/runtime/ub_data_migration.log
I will check further and tell you a workaround to solve that.
Regards,
Mall.
Hi Mall --
I found the answer to the Error 1292 issue. The MySQL server is fairly recent and as such it has a SQL mode that does not permit null or zero entry dates (hence the SQL you supplied is considered invalid and errors out). The solution is to run:
SET GLOBAL sql_mode = ‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
SET SESSION sql_mode = ‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
As an FYI, I *DID* enable debugging and the relevant section of the log was posted (I’d read all of it and the only entries above the lines I posted were indications of previous steps completing).
Unfortunately there is a NEW error which I will start a different thread for.
-Tor
Hi Mall --
I found the answer to the Error 1292 issue. The MySQL server is fairly recent and as such it has a SQL mode that does not permit null or zero entry dates (hence the SQL you supplied is considered invalid and errors out). The solution is to run:
SET GLOBAL sql_mode = ‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
SET SESSION sql_mode = ‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
As an FYI, I *DID* enable debugging and the relevant section of the log was posted (I’d read all of it and the only entries above the lines I posted were indications of previous steps completing).
Unfortunately there is a NEW error which I will start a different thread for.
-Tor
