Unable to import product models on Magento Cloud

[chefbuitenhuis]

Environment and configuration

1. Magento Cloud
2. Magento 2.3.4 with Akeneo Connector 100.4.0
3. Akeneo EE 3.0.41 Rose

Steps to reproduce

1. Start product model import on a Magento Cloud environment
2. Wait for it to fail at step: Add columns to product model table

Expected result

1. Product models are imported correctly.

Actual result

1. Product models fail to import:

product_model [08:51:45] Start import [08:51:45] Import ID : 5e81b321164bd [08:51:45] Create temporary table [08:51:45] completed [08:51:45] Fill temporary table [08:53:02] 1722 line(s) found [08:53:02] Remove columns from product model table [08:53:30] completed [08:53:30] Add columns to product model table [08:53:56] SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

[akosglue]

do you have a solution for this ?

[LSERRE]

Same issue with Magento 2.3.4 with version 101.0.0

[Dnd-Gimix]

Hello @LSERRE ,

Thank you for your feedback on Akeneo Connector for Magento 2. Could you please provide some insights on your Akeneo and Magento 2 setup please :

• Number of channels mapped to Magento
• Number of locales imported to Magento
• Number of attributes in your Family JEWELS_INIDIVIDUAL with the number of localizable / scopable / localizable and scopable attributes
• Also if possible, a "DESCRIBE TABLE" of the temporary "tmp_akeneo_connector_product_model" table that should be in your database after the error

Thank you. Regards,

[LSERRE]

Number of channels mapped to Magento: 1 Number of locales imported to Magento: 6 Number of attributes in your Family JEWELS_INIDIVIDUAL with the number of localizable / scopable / localizable and scopable attributes: 145 but there are not all filled. I can't find a view where I can see attributes by families with scopbable and localized. Also if possible, a "DESCRIBE TABLE" of the temporary "tmp_akeneo_connector_product_model" table that should be in your database after the error Here is the state of the table 'tmp_akeneo_connector_entities_product_model' and not 'tmp_akeneo_connector_product_model' just before it has been deleted when error occurs:

identifier varchar(255) YES UNI NULL family text YES NULL code text YES NULL family_variant text YES NULL parent text YES NULL categories text YES NULL box text YES NULL insert text YES NULL is_lot text YES NULL section text YES NULL activity text YES NULL function text YES NULL stackable text YES NULL collection text YES NULL engravable text YES NULL makt_maktx-en_GB text YES NULL makt_maktx-fr_FR text YES NULL mara_extwg text YES NULL mara_labor text YES NULL mara_matkl text YES NULL mara_mtart text YES NULL mara_prdha text YES NULL mara_zmgra text YES NULL mara_zmgrd text YES NULL mara_zmgre text YES NULL mara_zmgrf text YES NULL marc_herkl text YES NULL marc_stawn text YES NULL mvke_bonus text YES NULL mvke_mvgr2 text YES NULL mvke_mvgr3 text YES NULL mvke_mvgr5 text YES NULL mvke_prat2 text YES NULL esthetique text YES NULL subactivity text YES NULL a_partir_de text YES NULL mara_zzmodel text YES NULL segmentation text YES NULL sub_function text YES NULL transformable text YES NULL sub_collection text YES NULL ebible_metadata-en_GB text YES NULL ebible_metadata-fr_FR text YES NULL ebible_metadata-ja_JP text YES NULL ebible_metadata-ko_KR text YES NULL ebible_metadata-zh_CN text YES NULL ebible_metadata-zh_TW text YES NULL ebible_info_ch110-en_GB text YES NULL ebible_info_ch110-fr_FR text YES NULL ebible_info_ch110-ja_JP text YES NULL ebible_info_ch110-ko_KR text YES NULL ebible_info_ch110-zh_CN text YES NULL ebible_info_ch110-zh_TW text YES NULL product_line_group text YES NULL exceptional_product-en_GB text YES NULL exceptional_product-fr_FR text YES NULL exceptional_product-ja_JP text YES NULL exceptional_product-ko_KR text YES NULL exceptional_product-zh_CN text YES NULL exceptional_product-zh_TW text YES NULL web_publication_status-en_GB text YES NULL web_publication_status-fr_FR text YES NULL web_publication_status-ja_JP text YES NULL web_publication_status-ko_KR text YES NULL web_publication_status-zh_CN text YES NULL web_publication_status-zh_TW text YES NULL ebible_info_caracteristics-en_GB text YES NULL ebible_info_caracteristics-fr_FR text YES NULL ebible_info_caracteristics-ja_JP text YES NULL ebible_info_caracteristics-ko_KR text YES NULL ebible_info_caracteristics-zh_CN text YES NULL ebible_info_caracteristics-zh_TW text YES NULL metrique_reference_niveau_modele text YES NULL created text YES NULL updated text YES NULL AESTHETIC-products text YES NULL _entity_id int(11) YES UNI NULL _is_new smallint(6) YES 0 axis varchar(255) YES mvke_mvgr4 text YES NULL web_mentions-fr_FR text YES NULL product_title-en_GB-web text YES NULL product_title-fr_FR-web text YES NULL product_title-ja_JP-web text YES NULL product_title-ko_KR-web text YES NULL product_title-zh_CN-web text YES NULL product_title-zh_TW-web text YES NULL criteria_gender text YES NULL criteria_stones-web text YES NULL scene7_template text YES NULL description_produit_longue_1-en_GB-web text YES NULL description_produit_longue_1-fr_FR-web text YES NULL description_produit_longue_1-ja_JP-web text YES NULL description_produit_longue_1-ko_KR-web text YES NULL description_produit_longue_1-zh_CN-web text YES NULL description_produit_longue_1-zh_TW-web text YES NULL SUBSTITUTION-product_models text YES NULL X_SELL-product_models text YES NULL mara_zmgrh text YES NULL price_zpvm-EUR text YES NULL web_material-en_GB text YES NULL ebible_universe text YES NULL web_jewelry_center_stone-en_GB-web text YES NULL web_jewelry_stone_origin-en_GB-web text YES NULL web_price-AUD text YES NULL web_price-CNY text YES NULL web_price-EUR text YES NULL web_price-JPY text YES NULL web_price-KRW text YES NULL web_price-RUB text YES NULL web_price-TWD text YES NULL web_price-USD text YES NULL price_zpvm-AUD text YES NULL price_zpvm-CNY text YES NULL price_zpvm-JPY text YES NULL price_zpvm-KRW text YES NULL price_zpvm-RUB text YES NULL price_zpvm-TWD text YES NULL price_zpvm-USD text YES NULL web_material-fr_FR text YES NULL ebible_product_family text YES NULL web_jewelry_stone_origin-fr_FR-web text YES NULL web_jewelry_stone_quality-fr_FR-web text YES NULL description_produit_longue_2-en_GB-web text YES NULL description_produit_longue_2-fr_FR-web text YES NULL description_produit_longue_3-en_GB-web text YES NULL description_produit_longue_3-fr_FR-web text YES NULL web_material-ja_JP text YES NULL web_material-ko_KR text YES NULL web_material-zh_CN text YES NULL web_material-zh_TW text YES NULL web_mentions-ja_JP text YES NULL taille_pierre-web text YES NULL criteria_jewelry_carat text YES NULL web_jewelry_center_stone-fr_FR-web text YES NULL web_jewelry_stone_origin-ja_JP-web text YES NULL web_jewelry_stone_origin-ko_KR-web text YES NULL web_jewelry_stone_origin-zh_CN-web text YES NULL web_jewelry_stone_origin-zh_TW-web text YES NULL web_jewelry_center_stone-ja_JP-web text YES NULL web_jewelry_center_stone-ko_KR-web text YES NULL web_jewelry_center_stone-zh_CN-web text YES NULL web_jewelry_center_stone-zh_TW-web text YES NULL web_jewelry_stone_quality-en_GB-web text YES NULL web_jewelry_stone_quality-ja_JP-web text YES NULL web_jewelry_stone_quality-ko_KR-web text YES NULL web_jewelry_stone_quality-zh_CN-web text YES NULL web_jewelry_stone_quality-zh_TW-web text YES NULL ebible_jewelry_fixation-en_GB text YES NULL mara_zmgri text YES NULL export_web text YES NULL web_mentions-ko_KR text YES NULL web_mentions-zh_CN text YES NULL web_jewelry_paving-en_GB-web text YES NULL web_jewelry_paving-fr_FR-web text YES NULL web_jewelry_paving-ja_JP-web text YES NULL web_jewelry_paving-ko_KR-web text YES NULL web_jewelry_paving-zh_CN-web text YES NULL web_jewelry_paving-zh_TW-web text YES NULL web_jewelry_more_details-en_GB-web text YES NULL web_jewelry_more_details-fr_FR-web text YES NULL web_jewelry_more_details-ja_JP-web text YES NULL web_jewelry_more_details-ko_KR-web text YES NULL web_jewelry_more_details-zh_CN-web text YES NULL web_jewelry_more_details-zh_TW-web text YES NULL ebible_jewelry_size_resizing-en_GB text YES NULL ebible_jewelry_other_information-en_GB text YES NULL mara_behvo text YES NULL description_produit_longue_3-ja_JP-web text YES NULL description_produit_longue_3-ko_KR-web text YES NULL description_produit_longue_3-zh_CN-web text YES NULL description_produit_longue_3-zh_TW-web text YES NULL mvke_mvgr1 text YES NULL ebible_info_diamonds-en_GB text YES NULL new_product text YES NULL description_produit_longue_2-ja_JP-web text YES NULL description_produit_longue_2-ko_KR-web text YES NULL description_produit_longue_2-zh_CN-web text YES NULL description_produit_longue_2-zh_TW-web text YES NULL univers text YES NULL aesthetic_exception text YES NULL ch100 text YES NULL INDISSOCIABLE-product_models text YES NULL exclusivity text YES NULL size_grid text YES NULL c250 text YES NULL web_mentions-en_GB text YES NULL ebible_info_diamonds-fr_FR text YES NULL ebible_jewelry_fixation-fr_FR text YES NULL ebible_jewelry_size_resizing-fr_FR text YES NULL ebible_jewelry_other_information-fr_FR text YES NULL

[serhii-shnurenko]

tmp_akeneo_connector_product_model is myisam table(at least in our setup), while the error mentioned in the report relates to the InnoDB storage engine.

Our setup failing while attempting to modify akeneo_connector_product_model which is InnoDB.

I attached to running process via strace -tt -s 300 -p <pid> (attribute name in the output redacted)

22:44:20.161016 sendto(8, "W\0\0\0\3ALTER TABLE `akeneo_connector_product_model` ADD COLUMN `attribute1234_NL` text ", 91, MSG_DONTWAIT, NULL, 0) = 91
22:44:20.161132 poll([{fd=8, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000) = 1 ([{fd=8, revents=POLLIN}])
22:44:20.174141 recvfrom(8, "\312\0\0\1\377^\4#42000Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs", 32768, MSG_DONTWAIT, NULL, NULL) = 206

This can be resolved by setting the innodb_strict_mode to 0 (you can set it via code during imports since it session-based with set session or globally with set global). But I don't recommend this solution. The design of the table is not scalable.

There are several approaches that can be used to resolve this issue. One of them is converting the table to MyISAM storage engine. (not verified, but can't see why not, since there is no concurrent work on this table. Also not too good solution) Another is doing sharding (probably per locale sharding). Start using normal forms for this importer may be too complicated to implement.

[hostep]

In case people haven't seen it yet, there was recently a change published in version 104.3.4 that might help against this problem: https://github.com/akeneo/magento2-connector-community/compare/v104.3.3...v104.3.4

In our case, using ROW_FORMAT=DYNAMIC in combination with InnoDB didn't solve the problem, but switching back to MyISAM storage engine did.

That last one is especially needed when using MySQL 8, because the default storage engine got changed from MyISAM to InnoDB between MySQL 5.7 and 8.0. So we now need to explicitly set the storage engine to MyISAM on the table with MySQL 8.

---

I do agree with Serhii that this isn't the best solution, because MySQL is deprecating MyISAM so there is a chance that this storage engine will disappear one day in the future. A better solution is probably to not create one big table with all the attributes, but to split them up per locale for example, so that the number of columns in a table isn't too big.

[jogoossens]

You should try to get rid of MyISAM somehow for many reasons 😄

MyISAM does not even work at all on group replication or galera clusters, for that reason it's automatically disabled on such clusters. Sticking to a more or less outdated storage engine sounds like a bad idea :)

Some extra references: https://lefred.be/content/still-using-myisam-it-is-time-to-switch-to-innodb/ https://bugs.mysql.com/bug.php?id=78553