sql_require_primary_key for mysql clusters

[fballiano]

Hi everybody, I'm trying to use openmage 20.0.7 on a digitalocean stack composed by:

• a frontend server for apache/php
• a managed mysql8 DB cluster (this is a specific feature provided by digitalocean: https://www.digitalocean.com/products/managed-databases-mysql/)

Because the DB is in a cluster configuration it has sql_require_primary_key set to true (not changable), which makes sense in case of replication.

The problem is that a lot of (maybe all of) the magento/openmage indexes create temporary tables without primary keys, triggering errors all over the place (saving products in the backend, reindexing from command line etc).

I'm not that expert in this specific part of the systems so I'm not sure about what I'm writing but... why do you think magento used temporary tables without primary keys? would it be a good thing for use to modify the core to add primary keys to all those tmp tables in order to support this mysql8 cluster thing?

Thanks for any suggestion!

[fballiano]

an example of one of the errors:

SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting., query was: CREATE TEMPORARY TABLE cmcatalogrule_product_price_tmp(grouped_idvarchar(80) NULL COMMENT 'Grouped ID' ,product_idint UNSIGNED NULL COMMENT 'Product ID' ,customer_group_idsmallint UNSIGNED NULL COMMENT 'Customer Group ID' ,from_datedate NULL COMMENT 'From Date' ,to_datedate NULL COMMENT 'To Date' ,action_amountdecimal(12,4) NULL COMMENT 'Action Amount' ,action_operatorvarchar(10) NULL COMMENT 'Action Operator' ,action_stopsmallint NULL COMMENT 'Action Stop' ,sort_orderint UNSIGNED NULL COMMENT 'Sort Order' ,pricedecimal(12,4) NULL COMMENT 'Product Price' ,rule_product_idint UNSIGNED NULL COMMENT 'Rule Product ID' ,from_timeint UNSIGNED NULL default '0' COMMENT 'From Time' ,to_timeint UNSIGNED NULL default '0' COMMENT 'To Time' , INDEXIDX_CMCATALOGRULE_PRODUCT_PRICE_TMP_GROUPED_ID (grouped_id) ) COMMENT='CatalogRule Price Temporary Table' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci