MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

Had a some conversation with friend he is facing this issue for long back and unable to solve it . But i had an thought why we cant replicate in our test environment and fix this quickly . After a long time had a nice troubleshooting hours 

Lets deep dive into the troubleshooting , before enter into the troubleshooting requested few sample output for the same 

Outputs for the issues : 

First things requested to login without grant tables and asked to repair the tables  

root@ip-100-23-45-122:sudo mysqld_safe --skip-grant-tables --skip-networking --skip-plugin-load &

When trying to repair it mysql database most of the tables got corrupted , So corruption happened on the master tables itself ,

root@ip-100-23-45-122:/var/lib/mysql/mysql# mysqlcheck -u root -p --repair --databases mysql

Enter password: 

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.engine_cost

Error    : Table 'mysql.engine_cost' doesn't exist

status   : Operation failed

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.gtid_executed

Error    : Table 'mysql.gtid_executed' doesn't exist

status   : Operation failed

mysql.help_category

Error    : Table 'mysql.help_category' doesn't exist

status   : Operation failed

mysql.help_keyword

Error    : Table 'mysql.help_keyword' doesn't exist

status   : Operation failed

mysql.help_relation

Error    : Table 'mysql.help_relation' doesn't exist

status   : Operation failed

mysql.help_topic

Error    : Table 'mysql.help_topic' doesn't exist

status   : Operation failed

mysql.innodb_index_stats

Error    : Table 'mysql.innodb_index_stats' doesn't exist

status   : Operation failed

mysql.innodb_table_stats

Error    : Table 'mysql.innodb_table_stats' doesn't exist

status   : Operation failed

mysql.ndb_binlog_index                             OK

mysql.plugin

So further we need to try something to resolve it . I have replicated same issues with my local environment and followed below steps in the local 

Troubleshooting Step 1 : 

Installed separate mysql instance with different port and copied all user database directories to new server then tried to start mysql its started also 

But when trying to access the same user table got an same error 

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

 So issue not with master table corruption , something different 

Troubleshooting Step 2 : 

I have gone through so many links and references tried below things as well ,

• Stop the MySQL then Remove ib_logfile0 , ib_logfile1 . Again start the MySQL Server - Its not worked 
• Move corrupted ibdata1 file in the newly installed MySQL server and then restart MySQL Server - Its not worked 
• More links requested to restart multiple times its very funny thing but tried it will or not - Its also not worked 

Finally complete MySQL troubleshooting reference  resolved this issue with replacing tablespace method 

Troubleshooting Step 3 :

As per reference link mentioned below Restoring Orphan File-Per-Table ibd Files then its resolved and able to retrive the records from the table 

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

Again installed one more MySQL Server and Created dummy database as datablogs and created only structure from the sample 

Note : Only if you have structure of the table its possible to recover 

mysql> create database datablogs;

Query OK, 1 row affected (0.01 sec)

mysql> use datablogs;

Database changed

mysql> CREATE TABLE tbl_followup (

       id int(11) NOT NULL AUTO_INCREMENT,

       table_header text,

       action varchar(100) DEFAULT NULL,

       action_button_text varchar(100) DEFAULT NULL,

       parent_template text,

       created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

       modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

       PRIMARY KEY (id)

     ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE datablogs.tbl_followup DISCARD TABLESPACE;

Query OK, 0 rows affected (0.01 sec)

Copied only tbl_followup.ibd file from the corrupted server to new server then imported the tablespace 

mysql> ALTER TABLE datablogs.tbl_followup IMPORT TABLESPACE;

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select * from datablogs.tbl_followup;

Empty set (0.01 sec)

So overall while reading it very simple but its very hard troubleshooting did in lifetime !!!

Anyway instead of doing multiple solution follow my steps to resolve the issue sortly !!!

Restore and Point in time Restore with Percona Backup of MongoDB on another instance

Its easy to recover MongoDB Backup using Percona Backup for MongoDB

https://medium.com/@datablogs/restore-and-point-in-time-restore-with-percona-backup-of-mongodb-on-another-instance-4766f4468443

SQL Server Point in Time Recovery

 Oooooouch !!! I have deleted critical data in the ledger table !!!

When we ran delete or drop Script on any database without double check , Its Really big problem for the end users !!! Also you wont sleep 😁😁😁

If you have GODDBA , you are saved millions dollars of your data also your valuable customers . 

Oh Great 😀😀😀 How we can achieve that ? 

In SQL Server Multiple Database Backup Methods are available to tackle that , 

If SQL Server database is in full recovery model we can take transaction log backups on every hour/Minute/Seconds into local or remote drives . 

So, Yes am taking Log Backup 😕😕😕 How to we restore particular time ? 

Using with transaction logs we can bring back the data as much as you want depends on your log backup strategy . 

To achieve that here is the automated point in time restore script for your valuable environment ,

Scripts are available in https://github.com/selvackp/SQLServerPointInTimeRestore.git

So finally you have saved your millions dollars worth of data in 10 Minutes !!!

USE master
GO

DECLARE 
        @DatabaseOLDName			sysname  = 'test',										
        @DatabaseNewName			sysname  = 'test1',										
        @PrimaryDataFileName		        sysname  = 'test',									
	@SecDataFileName                        sysname  =  NULL,
        @DatabaseLogFileName		        sysname  = 'test_log',								
	@PrimaryDataFileCreatePath              sysname  = 'D:\MISC\Bkp\testdata.mdf',			
        @SecDataFileCreatePath		        sysname  = NULL,									
        @SecDataFileCreatePath1		        sysname  = NULL,									
        @DatabaseLogFileCreatePath              sysname  = 'D:\MISC\Bkp\test_log.ldf',		
        @PITRDateTime				datetime = '2022-08-11T20:44:11';

DECLARE @command				nvarchar(MAX),
        @OldPhysicalPathName		        nvarchar(MAX),
        @FullBackupDateTime			datetime,
        @DiffBackupDateTime			datetime,
        @LogBackupDateTime			datetime,
        @message				nvarchar(MAX);

SET @command = N'RESTORE DATABASE @DatabaseNewName FROM DISK = @OldPhysicalPathName WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime,
     MOVE N''' + @PrimaryDataFileName + N''' TO N''' + @PrimaryDataFileCreatePath + N''','
           + COALESCE('
     MOVE N''' + @SecDataFileName + ''' TO N''' + @SecDataFileCreatePath + ''',', '')
           + N'
     MOVE N''' + @DatabaseLogFileName + N''' TO N''' + @DatabaseLogFileCreatePath + N''';';

SELECT     TOP (1) @OldPhysicalPathName = bmf.physical_device_name,@FullBackupDateTime = bs.backup_start_date
FROM       msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON  bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName AND  bs.type= 'D' AND  bs.backup_start_date < @PITRDateTime
ORDER BY   bs.backup_start_date DESC;

SET @message = N'Starting restore of full backup file '+ @OldPhysicalPathName + N', taken ' + CONVERT(nvarchar(30), @FullBackupDateTime, 120);

RAISERROR(@message, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;


SET @command = N'RESTORE DATABASE @DatabaseNewName FROM DISK = @OldPhysicalPathName WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime;';

SELECT     TOP (1) @OldPhysicalPathName = bmf.physical_device_name,@DiffBackupDateTime = bs.backup_start_date
FROM       msdb.dbo.backupset   AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON  bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName AND  bs.type  = 'I' AND  bs.backup_start_date >= @FullBackupDateTime AND  bs.backup_start_date< @PITRDateTime
ORDER BY   bs.backup_start_date DESC;

IF @@ROWCOUNT > 0
BEGIN;
    SET @message = N'Starting restore of differential backup file ' + @OldPhysicalPathName + N', taken ' + CONVERT(nvarchar(30), @DiffBackupDateTime, 120);

    RAISERROR(@message, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;
END;

SET @command = N'RESTORE LOG @DatabaseNewName
FROM DISK = @OldPhysicalPathName
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime;';

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY TYPE_WARNING FOR
SELECT     bmf.physical_device_name,
           bs.backup_start_date
FROM       msdb.dbo.backupset         AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
   ON      bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName
AND        bs.type                 = 'L'
AND        bs.backup_start_date    >= COALESCE(@DiffBackupDateTime, @FullBackupDateTime)
ORDER BY   bs.backup_start_date ASC;

OPEN c;

FETCH NEXT FROM c
INTO @OldPhysicalPathName,
     @LogBackupDateTime;

WHILE @@FETCH_STATUS = 0
BEGIN;
    SET @message = N'Starting restore of log backup file '
                   + @OldPhysicalPathName + N', taken '
                   + CONVERT(nvarchar(30), @LogBackupDateTime, 120);
    RAISERROR(@message, 0, 1) WITH NOWAIT;
    EXEC sys.sp_executesql @command,
                           N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                           @DatabaseNewName,
                           @OldPhysicalPathName,
                           @PITRDateTime;

    IF @LogBackupDateTime >= @PITRDateTime
        BREAK;

    FETCH NEXT FROM c
    INTO @OldPhysicalPathName,
         @LogBackupDateTime;
END;

CLOSE c;
DEALLOCATE c;

SET @command = N'RESTORE DATABASE @DatabaseNewName
WITH RECOVERY;';

RAISERROR('Starting recovery', 0, 1) WITH NOWAIT;
EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;
GO