If you encounter this error in Magento 2.4.3 with mySQL 8:

Design Config Grid index has been rebuilt successfully in 00:00:00
Customer Grid index has been rebuilt successfully in 00:00:00
Category Products index process error during indexation process:
SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `innodb_system` cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `catalog_category_product_index_store1_tmp` LIKE `catalog_category_product_index_store1`
Product Categories index process error during indexation process:
SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `innodb_system` cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `catalog_category_product_index_store1_tmp` LIKE `catalog_category_product_index_store1`
Catalog Rule Product index has been rebuilt successfully in 00:00:00
Product EAV index has been rebuilt successfully in 00:00:00
Stock index has been rebuilt successfully in 00:00:00
Product Price index has been rebuilt successfully in 00:00:00
Catalog Product Rule index has been rebuilt successfully in 00:00:00
Catalog Search index has been rebuilt successfully in 00:00:01

Here is the fix:

Filename: framework-adapter-pdo.patch

Patch to fix PDO Adapter when MySQL 8.0 used for Temporary tables using LIKE SQL query. According to MySQL doc pages: https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

You cannot use CREATE TEMPORY TABLE ... LIKE to create an empty table based on the definition of a table that resides in the mysql tablespace, InnoDB system tablespace (innodb_system), or a general tablespace. The tablespace definition for such a table includes a TABLESPACE attribute that defines the tablespace where the table resides, and the aforementioned tablespaces do not support temporary tables.

-- To apply patch --

Add the cweagans/composer-patches plugin to the composer.json file.

composer require cweagans/composer-patches

Edit the composer.json file and add the following section to specify:

 "extra": {
 "magento-force": "override",
 "patches": {
 "magento/framework": {
 "Magento2-Framework-PDO-Adapter-MySQL8: Patch to fix PDO Adapter when MySQL 8.0 used for Temporary tables using LIKE SQL query": "https://raw.githubusercontent.com/magemojo/m2-patches/main/framework-adapter-pdo.patch"
 } 
 }

Text is added in the "extra": { section, with following content:

Explanations:

Module: "magento/module-name" ← That’s the Magento 2 Core module we are patching
Title: We link this with internal subject name.
URL to patch: That’s link from above and our demo repository.

Apply the patch. Use the -v option only if you want to see debugging information.

composer -v install

Update the composer.lock file. The lock file tracks which patches have been applied to each Composer package in an object.

composer update --lock

When this is done you may need to execute:

php bin/magento setup:upgrade --keep-generated
php bin/magento setup:di:compile

Source: https://github.com/magemojo/m2-patches/wiki#magento2-framework-pdo-adapter-mysql8