New products not showing in category frontend Magento 2.3.7-p3

Before you dismiss this kindly read the details. I have come across several issues but none provide a solution to my question.

My products do not show in the frontend of the store.

They are visible when I visit them directly in the format: http://baseURL/catalog/product/view/id/ but they do not show in the categories frontend. The product image shows along with the add to cart button.
Database View: The view (inventory_stock_1) is in the database.
Product setup: is enabled, in stock, default stock is over 18 in the product grid, Product Salable Quantity shows that default stock is 18.
Source/Stock: items have been assigned to inventory source. Stock is assigned to source.
I am managing inventory with the inventory configurations
I have even gone into the display settings for the categories. Display mode is static block and products. Anchor is YES. Available Product Listing Sort By: USE All
CLI Commands: Magento commands have been run but nothing. indexer, cache.

Below is the result of bin/magento indexer:reindex

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 has been rebuilt successfully in 00:00:00
Product Categories index has been rebuilt successfully in 00:00:00
Catalog Rule Product index has been rebuilt successfully in 00:00:00
SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `yx_gtt` cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `gtt_catalog_product_index_eav_temp` LIKE `gtt_catalog_product_index_eav_tmp`
Stock index has been rebuilt successfully in 00:00:00
Inventory index has been rebuilt successfully in 00:00:00
Catalog Product Rule index has been rebuilt successfully in 00:00:00
SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `yx_gtt` cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `gtt_catalog_product_index_price_temp` LIKE `gtt_catalog_product_index_price_tmp`
Catalog Search index has been rebuilt successfully in 00:00:03

Even with all the above, I get an error: We can’t find products matching the selection.. when I visit the frontend. The categories show as links in the menu but the products under them are not displayed

Any help will be appreciated