Category and product row_id is always null when the entity is created. They can diverge too.
Insert query shoud be
INSERT INTO `catalog_product_entity` (`entity_id`, `attribute_set_id`, `type_id`, `sku`, `updated_at`, `row_id`)
SELECT `tmp_akeneo_connector_entities_product`.`_entity_id` AS `entity_id`,
`tmp_akeneo_connector_entities_product`.`_attribute_set_id` AS `attribute_set_id`,
`tmp_akeneo_connector_entities_product`.`_type_id` AS `type_id`,
`tmp_akeneo_connector_entities_product`.`identifier` AS `sku`,
NOW() AS `updated_at`,
IFNULL(p.row_id, _entity_id) AS `row_id`
FROM `tmp_akeneo_connector_entities_product`
LEFT JOIN `catalog_product_entity` AS `p`
ON _entity_id = p.entity_id
LEFT JOIN `staging_update` AS `s`
ON p.created_in = s.id
WHERE (s.is_rollback = 1 OR s.id IS NULL)
ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`),
`attribute_set_id` = VALUES(`attribute_set_id`),
`type_id` = VALUES(`type_id`),
`sku` = VALUES(`sku`),
`updated_at` = VALUES(`updated_at`),
`row_id` = VALUES(`row_id`);
instead of
INSERT INTO `catalog_product_entity` (`entity_id`, `attribute_set_id`, `type_id`, `sku`, `updated_at`, `row_id`)
SELECT `tmp_akeneo_connector_entities_product`.`_entity_id` AS `entity_id`,
`tmp_akeneo_connector_entities_product`.`_attribute_set_id` AS `attribute_set_id`,
`tmp_akeneo_connector_entities_product`.`_type_id` AS `type_id`,
`tmp_akeneo_connector_entities_product`.`identifier` AS `sku`,
NOW() AS `updated_at`,
`p`.`row_id`
FROM `tmp_akeneo_connector_entities_product`
LEFT JOIN `catalog_product_entity` AS `p`
ON _entity_id = p.entity_id
LEFT JOIN `staging_update` AS `s`
ON p.created_in = s.id
WHERE (s.is_rollback = 1 OR s.id IS NULL)
ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`),
`attribute_set_id` = VALUES(`attribute_set_id`),
`type_id` = VALUES(`type_id`),
`sku` = VALUES(`sku`),
`updated_at` = VALUES(`updated_at`),
`row_id` = VALUES(`row_id`);
Category and product row_id is always null when the entity is created. They can diverge too.
Insert query shoud be
instead of