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

Active active replication in PostgreSQL

Lets deep into active active high availability solution on PostgreSQL 

Detailed Session on the Live Recording Session 

https://www.youtube.com/playlist?list=PLlYYbZpzszyV4rpM4yiyJ4G1im10R83kD

PostgreSQL Table Partition on AWS RDS

If we grow bigger in the business , seamlessly our customer and transaction data also increases . In the meantime performance needs to consider as well 

So in this case of bigger tables indexes will not help us to achieve good performance on peak times . Alternatively we have partitioning to split the tables data into multiple pieces on all the relational database environments 

Like wise we are going to do range partition on sample table in PostgreSQL Database , In PostgreSQL three types of partition methods are available , 

Below are the important concern in the PostgreSQL Partition ,

• Possible to attach regular table into partitioned one 
• Not Possible to transform regular table to partitioned one   

So based on the above formula , we have tried to transform regular table into partitioned one for your reference 

Any one can use this example and perform partitioning in AWS PostgreSQL RDS easily 

Click GitHub Link for Code : AWS-PostgreSQL-RDS-Table-Partition

Step 1 : Create base datablogspaycheck table and insert some sample records 

DROP TABLE IF EXISTS datablogspaycheck CASCADE;

DROP SEQUENCE IF EXISTS public.paycheck_id_seq;

CREATE SEQUENCE public.paycheck_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

create table datablogspaycheck

(

    payment_id int NOT NULL DEFAULT nextval('public.paycheck_id_seq'::regclass), 

    created timestamptz NOT NULL,

    updated  timestamptz NOT NULL DEFAULT now(),

    amount float,

    status varchar DEFAULT 'new'

);

CREATE INDEX idx_paycheck ON datablogspaycheck (created);

INSERT INTO datablogspaycheck (created) VALUES (

generate_series(timestamp '2023-01-01'

               , now()

               , interval  '5 minutes') ); 

Step 2 : Rename base table with new name

ALTER TABLE datablogspaycheck RENAME TO datablogspaycheck_basetable;

Step 3 : Create Partitioned table 

create table datablogspaycheck

(

    payment_id int NOT NULL DEFAULT nextval('public.paycheck_id_seq'::regclass), 

    created timestamptz NOT NULL,

    updated  timestamptz NOT NULL DEFAULT now(),

    amount float,

    status varchar DEFAULT 'new'

)PARTITION BY RANGE (created);

Step 4 : Create Separate Partition for each create date 

CREATE TABLE datablogspaycheck_202303 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-01-01') TO ('2023-03-01');

   

CREATE TABLE datablogspaycheck_20230304 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

    

CREATE TABLE datablogspaycheck_202304 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

    

CREATE TABLE datablogspaycheck_202311 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-05-01') TO ('2023-11-01');

   

CREATE TABLE datablogspaycheck_2024 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-11-01') TO ('2024-01-01');

Step 5 : Migrate the all records

insert into datablogspaycheck (payment_id,created,updated,amount,status) select payment_id,created,updated,amount,status from datablogspaycheck_basetable;

Step 6 : Validate each partition 

select * from datablogspaycheck_202303 order by 2 desc

select * from datablogspaycheck_20230304 order by 2 desc

select * from datablogspaycheck_202311 order by 2 desc

Its done , Easily migrated normal table data into partitioned table 

Thanks for Reading !!!

Deep dive into Babelfish Compass

        Wow !!! If suppose on the migration projects we need to more stuffs and things to convert when coming to procedures , functions and other database objects 

But AWS is providing good things to migrate with easy steps , Ha Ha ... Don't overthink still you need to do 40% code migration works 

In this part Babelfish Compass is giving various options to support migration the codes from SQL Server to PostgreSQL with bebelfish feature enabled PaaS servers 

Below are easy steps on the Script Conversations

Prerequisites 

• Install a 64-bit Java Runtime Environment (JRE) version 8 or higher

1.Download Compass Tool in Below 

https://github.com/babelfish-for-postgresql/babelfish_compass/releases/tag/v.2023-03-a

Needs to download .zip file to work with Babelfish Compatibility 

2.Unzip and Place the files in separate folder 

3.Be Ready with you SQL Database Generated Scripts file and Copy it in Same Folder 

Database name has been highlighted in below , 

4.Next , we can start running report with Babelfish Compass 

C:\Users\Admin\Downloads\BabelfishCompass_v.2023-03-a\BabelfishCompass>BabelfishCompass.bat reportfinal datablogsdbprod.sql

5.Finally Reports are generated in Documents Directory 

6.We can review the reports in any format , for me its easy with in HTML browser 

Just double click the HTML document , So like below we will get supported and unsupported features details in depth . 

We can directly go and debug the code . Also bebelfish compass is having plenty of methods to rewrite the code , we will check it in next blog 

Happy Coding !!!

Oracle RDS Audit log enable

Oracle Audit Log : 

Oracle Audit Log refers to the feature in Oracle Database that records and stores information about various database activities and events. It provides a mechanism to track and monitor user activities, system events, and changes made to the database.

1. User Logins: Recording user login attempts and authentication information.
2. Database Activities: Logging SQL statements executed by users, including select, insert, update, and delete operations.
3. Privilege Usage: Monitoring the usage of privileges, such as granting or revoking permissions.
4. Schema Changes: Tracking modifications to database objects, such as creating or altering tables, views, or indexes.
5. System Events: Recording system-level events, such as startup and shutdown of the database.
6. Security Violations: Detecting unauthorized access attempts or suspicious activities.
7. Administrative Operations: Logging administrative tasks performed by database administrators, such as user management or database configuration changes.

The Oracle Audit Log provides an essential tool for security, compliance, and troubleshooting purposes.

Types of Auditing in Amazon RDS for Oracle : 

1. Standard Auditing 
2. Unified Auditing 
3. Fine-grained Auditing

We are going to see , how do we enable Standard auditing in Oracle RDS 

How to enable Audit Log in Oracle RDS?

Make sure you have enabled custom parameter group for Oracle RDS 

• Modify below values for Audit_Trail Parameter 

            Audit_Trail - DB, EXTENDED

• Next ,Just needs to modify below DDL or DML statements to capture the logs from the server 

            AUDIT DELETE ANY TABLE;

            AUDIT DELETE TABLE BY USER_01 BY ACCESS;

            AUDIT DELETE TABLE BY USER_02 BY ACCESS;

            AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE ON DEFAULT;

            AUDIT READ ON DIRECTORY datapump_dir;

Its all done , we have enabled required logs to capture for security purpose 

How to we monitor Audit Logs ? 

We can just run the below command get the captured audit logs in Oracle RDS ,

SELECT * FROM DBA_AUDIT_TRAIL order by 1 desc 

Its just for normal scenario , explained the process . Still we can separate Audit Table space and many further things are available in Oracle . Let see on another blogs 

Happy Auditing !!!

How to Automate MongoDB Database Backups in Linux

We have setup of One Primary with Multiple Secondary

Even if we configured highly available setup and backups , native backup are so special to take it and keep it somewhere in the cloud 

using the below script we can easily schedule backup in Linux environments  

https://github.com/selvackp/MongoNativeBackup-/blob/main/mongo_dump.sh

export PATH=/bin:/usr/bin:/usr/local/bin
#Decalre Today Date
TODAY=`date +"%d%b%Y"`

#Declare Variables Required to pass for mongo dump command
DB_BACKUP_PATH='/mnt/mongobackup'
MONGO_HOST='localhost'
MONGO_PORT='27017'
MONGO_USER='xxxxxxxxxxx'
MONGO_PASSWD='xxxxxxxxxxxxx'
DATABASE_NAMES='ALL'

#Remove Old Backup Files 
find ${DB_BACKUP_PATH} -name "*.zip" -type f -mtime +3 -delete

find ${DB_BACKUP_PATH} -type d -mtime +3 -exec rm -rf {} \;

#Create Directory for Backup
mkdir -p ${DB_BACKUP_PATH}/${TODAY}
cd ${DB_BACKUP_PATH}/${TODAY}/

if [ ${DATABASE_NAMES} = "ALL" ]; then
	echo "You have choose to backup all database"
	mongodump --uri="mongodb://${MONGO_USER}:${MONGO_PASSWD}@${MONGO_HOST}:${MONGO_PORT}"
else
	echo "Running backup for selected databases"
	for DB_NAME in ${DATABASE_NAMES}
	do
	mongodump --uri="mongodb://${MONGO_USER}:${MONGO_PASSWD}@${MONGO_HOST}:${MONGO_PORT}/${DB_NAME}"
	done
fi

#Compress The Backup
cd ${DB_BACKUP_PATH}/${TODAY}

zip -r ${DB_BACKUP_PATH}_${TODAY}.zip ${DB_BACKUP_PATH}/${TODAY}

cd ${DB_BACKUP_PATH}/${TODAY}

#Copy the Compressed file into Azure Container using Shared Access Token
azcopy cp ${DB_BACKUP_PATH}_${TODAY}.zip "https://xxxxxxxxxxx.blob.core.windows.net/xxxxxxxxxxxx?sp=w&st=xxxxxTxxxxxxxZ&se=xxxxxxZ&spr=https&sv=2021-06-08&sr=c&sig=csdfcdsxxxxxxxxxxxxxxx" --recursive=true

#Send Mail with Backup Logs 
if [ $? -ne 0 ]
then
        echo "Mongo Native backup Failed in $(hostname) $(date). Please contact administrator." | mail -r mail@datablogs.com -s "Mongo Native backup Failed $(hostname)" dbsupport@datablogs.com < /mongodata/cronscripts/mongo_backup_log.log

        else

        echo "Mongo Native backup completed in $(hostname)." | mail -r mail@datablogs.com -s "Mongo Native backup completed in $(hostname)" dbsupport@datablogs.com < /mongodata/cronscripts/mongo_backup_log.log
fi