datablogs: Point In Time Restore
Showing posts with label Point In Time Restore. Show all posts
Showing posts with label Point In Time Restore. Show all posts

Monday, June 23, 2025

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 


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 !!!

Friday, February 17, 2023

Thursday, August 11, 2022

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