2.3 to 2.3.1 Upgrade showing sales_order_grid already exists

Question

So far the upgrade went pretty smooth but now I have a few outdated modules and it's asking me to run the setup:upgrade on CLI but I keep getting an SQLSTATE general error:

1813 Tablespace for table 'db_mine.sales_order_grid' exists. Please DISCARD the tablespace before IMPORTsales_order_grid' exists. Please DISCARD the tablespace before IMPORT

---

Update

My sales_order_grid table is no longer in my DB.

I try to create the table again like so:

    CREATE TABLE `sales_order_grid` (
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity Id',
  `status` varchar(32) DEFAULT NULL COMMENT 'Status',
  `store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
  `store_name` varchar(255) DEFAULT NULL COMMENT 'Store Name',
  `customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer Id',
  `base_grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Base Grand Total',
  `base_total_paid` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Paid',
  `grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Grand Total',
  `total_paid` decimal(12,4) DEFAULT NULL COMMENT 'Total Paid',
  `increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
  `base_currency_code` varchar(3) DEFAULT NULL COMMENT 'Base Currency Code',
  `order_currency_code` varchar(255) DEFAULT NULL COMMENT 'Order Currency Code',
  `shipping_name` varchar(255) DEFAULT NULL COMMENT 'Shipping Name',
  `billing_name` varchar(255) DEFAULT NULL COMMENT 'Billing Name',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Updated At',
  `billing_address` varchar(255) DEFAULT NULL COMMENT 'Billing Address',
  `shipping_address` varchar(255) DEFAULT NULL COMMENT 'Shipping Address',
  `shipping_information` varchar(255) DEFAULT NULL COMMENT 'Shipping Method Name',
  `customer_email` varchar(255) DEFAULT NULL COMMENT 'Customer Email',
  `customer_group` varchar(255) DEFAULT NULL COMMENT 'Customer Group',
  `subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal',
  `shipping_and_handling` decimal(12,4) DEFAULT NULL COMMENT 'Shipping and handling amount',
  `customer_name` varchar(255) DEFAULT NULL COMMENT 'Customer Name',
  `payment_method` varchar(255) DEFAULT NULL COMMENT 'Payment Method',
  `total_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Total Refunded',
  `signifyd_guarantee_status` varchar(32) DEFAULT NULL COMMENT 'Signifyd Guarantee Disposition Status',
  `refunded_to_store_credit` decimal(12,4) DEFAULT NULL COMMENT 'Refund to Store Credit',
  PRIMARY KEY (`entity_id`),
  UNIQUE KEY `SALES_ORDER_GRID_INCREMENT_ID_STORE_ID` (`increment_id`,`store_id`),
  KEY `SALES_ORDER_GRID_STATUS` (`status`),
  KEY `SALES_ORDER_GRID_STORE_ID` (`store_id`),
  KEY `SALES_ORDER_GRID_BASE_GRAND_TOTAL` (`base_grand_total`),
  KEY `SALES_ORDER_GRID_BASE_TOTAL_PAID` (`base_total_paid`),
  KEY `SALES_ORDER_GRID_GRAND_TOTAL` (`grand_total`),
  KEY `SALES_ORDER_GRID_TOTAL_PAID` (`total_paid`),
  KEY `SALES_ORDER_GRID_SHIPPING_NAME` (`shipping_name`),
  KEY `SALES_ORDER_GRID_BILLING_NAME` (`billing_name`),
  KEY `SALES_ORDER_GRID_CREATED_AT` (`created_at`),
  KEY `SALES_ORDER_GRID_CUSTOMER_ID` (`customer_id`),
  KEY `SALES_ORDER_GRID_UPDATED_AT` (`updated_at`),
  FULLTEXT KEY `FTI_65B9E9925EC58F0C7C2E2F6379C233E7` (`increment_id`,`billing_name`,`shipping_name`,`shipping_address`,`billing_address`,`customer_name`,`customer_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order Grid'

I get the error message - Last Error Message Tablespace for table db.sales_order_grid' exists. Please DISCARD the tablespace before IMPORT.

Then I run an ALTER Table: ALTER TABLE sales_order_grid DISCARD TABLESPACE;

I get the error message: Table 'db.sales_order_grid' doesn't exist

Answer 1

I think it's an known issue when database get upgraded it's and issue of 'tablespace full' the database server's tablespace is defined by the innodb_data_file_path setting and by default is rather small. Even made larger,It can also occur when we try to run large query whcih's log,cache are stored into tablespace. so in order to resolve this issue you need to search for "tablename.ibd" file in mysql and remove it then it will work.

For more Detail you can take reference of this link. Hope this will help you.

Answer 2

I solved this issue just running:

n98-magerun2 maintenance:enable && 
n98-magerun2 db:dump --strip=@stripped db && 
n98-magerun2 db:import --drop --optimize db.sql && 
n98-magerun2 maintenance:disable && 
n98-magerun2 setup:static-content:deploy -f & 
n98-magerun2 setup:di:compile & 
n98-magerun2 c:flush & 
n98-magerun2 i:reindex & 

Answer 3

I read a lot about the steps mentioned above but that did not correct my problem.

There is a multi-step process that will correct the issue, that has been used on other cases of this happening. It basically works like this:

1. Restore the database
2. Dump the sales_order_grid table to /srv/public_html/sales_order_grid.sql
3. Run magento setup:upgrade, di:compile, and static-content:deploy (sales order grid gets dropped here)
4. Dump the full database to /srv/public_html/full_db.sql
5. Drop and recreate the database (this also required shutting down mysql, removing the part of the table that got left behind, and restarting it)
6. Import full_db.sql and sales_order_grid.sql
7. Run index to check everything is working

Answer 4

Deleting the file your-mysql-data-dirs/your-magento-store-schema-dirs/sales_shipment_item.ibd seems to solve the issue in my case. (Magento 2.3.2, mySQL 8.0.18 (Homebrew))