4

I'm having difficulties in exporting all products with their categories from Magento v2.1.9 installation for importing them to Magento v2.3.0.

Because I don't need all other tables I don't want to use the same database and actually I'm not sure even if I use the old database for the new installation I wouldn't miss some tables that are introduced in v2.3.0.

What I have tried so far is exporting tables matching the pattern catalog* and try to import them to the new database but doing so it complains about some foreign keys that are related to product attributes.

So how can I cleanly export all products, their categories and attributes and all data related to this entity?

More details

The error is:

ERROR 1005 (HY000) at line 154: Can't create table `****`.`catalogrule_customer_group` (errno: 150 "Foreign key constraint is incorrectly formed")

that by gathering more information from show engine innodb status;:

2019-03-13 11:25:45 0x7f6a74069700 Error in foreign key constraint of table `****`.`catalogrule_customer_group`:
    FOREIGN KEY (`customer_group_id`) REFERENCES `customer_group` (`customer_group_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Rules To Customer Groups Relations'
....

I found that catalogrule_customer_group's customer_group_id column data type differs from customer_group's customer_group_id column. The former is unsigned int and the latter is unsigned smallint.

So up to now I'm ensured the differences between both versions isn't tied to tables to be less or more but also tables structures. There are two problems here:

  1. Resolving the given error wouldn't guarantee no further errors while importing.

  2. I shouldn't import catalogrule_customer_group because I don't have customer* tables of old database in place.

So the question remains as is. How could someone export products and all their related data to be used for a future import (into v2.3.* to be more specific)?

  • is there any reason you do not want to use the built in import / export tools M2 comes with? (docs.magento.com/m2/ce/user_guide/system/data-export.html) – bjornredemption Mar 9 at 10:28
  • @bjornredemption The problem with it is that it doesn't care about attribute sets and I get Invalid value for Attribute Set column error while importing. – revo Mar 9 at 10:40
  • Direct DB imports can be tricky but try Disable Foreign Key checks during export and import. I believe there are however answers for your attribute set error and this might be an easier approach. – bjornredemption Mar 9 at 15:10
  • @bjornredemption Unfortunately I didn't find anything. I already tried disabling foreign key checks too with no luck. – revo Mar 9 at 17:03
1

I think the problem with just catalog* is that the Magento catalog uses EAV and needs other tables like eav_attribute_*.

The other possible issue with DB dumping just catalog tables from 2.1 and loading into 2.3 is that any 2.3 catalog DB upgrade will not be applied. If you wanted to go the DB route I think you need to upgrade a copy of the M2.1 DB to M2.3 and then cleate a script to dump the appropriate tables with drop-table and disable-keys that call be loaded of a clean M2.3 install.

Import/Export approach: As @bjornredemption called out you could go the Magento import/export route. But as you've noted Attribute Sets and any values for dropdown and multi-select product attribute must exist in the target Magento install. The import/export approach would involve trying to run the import, address any import errors, and continue until all errors have been resolved. Note: don't forget the image aspect of product import. Based on your question I'd choose this route.

Update

Based on the question update it appears M2.3 has changed the structure of some tables that have foreign keys to tables you are trying to import. This is why I suggested you should first upgrade a copy of the M2.1 database before dumping tables to import into M2.3.

  • Backup the current M2.3 database
  • Replace with a copy of the M2.1 database
  • Run CLI magento setup:upgrade
  • Dump the desired table from the M2.1 DB that has been upgraded to M2.3 structure
  • Replace the upgraded database with the M2.3 initial backup
  • Now you should be able to load the upgraded M2.1 DB dump into M2.3

Note: you could also just use the parrel database an updated env.php instead of replacing DB copies.

  • Beside catalog* tables I exported eav_* tables too and got the same error about the foreign keys and I know if I go for resolving errors this will take a significant amount of time and still at the end I'm not sure if I haven't missed anything. – revo Mar 13 at 10:46
  • Working through the Magento native import/export to resolve errors with your import should provide knowledge on the Magento import will be valuable for future import tasks. For DB dumps are you sure your sql file has SET FOREIGN_KEY_CHECKS=0; set near the top? I don't understand how you get foreign key errors if the check is disabled. – Ian at Xantek Mar 13 at 11:16
  • Please see the update. – revo Mar 13 at 11:47
  • answer updated based on the updated error message supplied in the question – Ian at Xantek Mar 13 at 14:48
  • @revo any luck trying to upgrade the 2.1 databases before dumping tables? – Ian at Xantek Mar 15 at 12:13
0

You can export it from magento admin panel, then import on new magento with magmi extension, it's free and fast importer and can import categories, products, attributes, images and so more...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.