Magento 2 : How to fix tablespace for table exists | base table or view already exists

Fixing General error: 1813 Tablespace for table xx Please DISCARD the tablespace before IMPORT and SQLSTATE[42S01]: Base table or view already exists

Recently I encountered this error while upgrading an extension on Magento 2.2.2
The error was

SQLSTATE[HY000]: General error: 1813 Tablespace for table '`jack_db1`.`jill_table`' exists. Please DISCARD the tablespace before IMPORT., query was: CREATE TABLE IF NOT EXISTS `jill_table`

When I tried to fix the above error, I received:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table '`jack_db1`.`jill_table`' already exists, query was: CREATE TABLE IF NOT EXISTS `jill_table`

So here is how to fix it:

Important Note: Replace jack_db1 with your database Name and jill_table with your table Name

PART 1 – Fixing MySQL backend

First log in to server and check the mysql data folder (mine was /var/lib/mysql/jack_db1)
say your database name is jack_db1 and your table is jill_table
so issue

ls /var/lib/mysql/jack_db1

You will find .frm and .idb files
Note that these files are present as a couple (each frm file has a corresponding idb file)
But the table that is causing you issues will have one of them missing.
.frm was missing in my case, so copy any other frm file and name it as jill_table.frm (i copied wishlist.frm using following command )
cd /var/lib/mysql/jack_db1;cp wishlist.frm jill_table.frm
use the following command to fix ownership

chown -R mysql:mysql /var/lib/mysql/jack_db1/*

Part 2: Fixing Magento Backend

Login to phpMyAdmin if available or use MySQL CLI to perform the following actions.
In the table setup_module of your magento installation
Delete the row which is related to your corrupted extension

DELETE FROM `setup_module` WHERE module="Custom_Module"

you can also do the same from phpMyAdmin

Now delete the table which was causing the issue, (you would not be able to view the table in phpmMyAdmin)
Just run the following query in SQL

DROP TABLE IF EXISTS `jill_table`;

Part 3: Upgrade the Magento using php cli

run the following commands:

rm -rf var/cache/*
rm -rf generated/*
php bin/magento module:enable Custom_Module --clear-static-content
php bin/magento setup:upgrade
#  Hopefully this time you wont see any errors, proceed as usual if upgrade command worked.
#  run
php bin/magento setup:di:compile
#  and 
php bin/magento setup:static-content:deploy -f

Good Luck with Magento 🙂