Indexing product prices fails

Question

We moved out Magento 2 to a new server. All works fine but indexing of the product prices. All other indexers work fine. An when we run bin/magento indexer:reindex we get the following error:

SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'ku0h8_mag_stagin'@'localhost' (using password: YES), query was: DELETE price_index FROM catalog_product_index_price_temp AS price_index INNER JOIN catalog_product_entity AS product_entity ON product_entity.entity_id = price_index.entity_id LEFT JOIN inventory_stock_1 AS inventory_stock ON inventory_stock.sku = product_entity.sku WHERE (inventory_stock.is_salable = 0 OR inventory_stock.is_salable IS NULL) AND (price_index.website_id = 1) AND (price_index.entity_id IN ('1', '2', '3', '4', '5', '8', '9', '29', '30', '31', '34', '35', '36', '37', '38', '39', '40', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '95', '96', '97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153'))

1. localhost is not the host we have set in env.php
2. The table catalog_product_index_price_temp does not exists (it's catalog_product_index_price_tmp)

I have no idea why Magento uses catalog_product_index_price_temp.

Thanks for any help or hint where to start searching.

Answer 1

First You need to export inventory_stock_1 view from your database then inventory_stock_1.sql file open in any text editor then 'DEFINER='ku0h8_mag_stagin'@'localhost' and remove from your SQL file now save this SQL file and remove inventory_stock_1 view from your database then now import this saved inventory_stock_1.sql now your issue is fixed.

Answer 2

The above answer by Chirag is correct and works for me as of the time of this posting. If you're using a database management application (e.g. HeidiSQL or Sequel Pro), try the following:

1. Find inventory_stock_1 in the list of tables
2. Copy the Create Syntax of this view. In Sequel Pro, this is displayed on the Table Info tab.
3. Paste this syntax as a new query. In Sequel Pro, this is the Query tab.
4. Modify the DEFINER value to be the username of the DB itself. In my case for my local environment it ended up looking like this: DEFINER='magento2'@'%'.
5. Change the name of the view to something other than "inventory_stock_1".
6. Run the query. Delete the old "inventory_stock_1" view and rename the one you just created to "inventory_stock_1".
7. Clear the cache in your local environment, and then reindex. It should work.