datablogs

Friday, October 17, 2025

How to upgrade MySQL from 8.0 to 8.4 ?

 |   |  No comments  |  , , ,

Dear all my DBA Friends its time to upgrade to 8.4 . Yes We are getting notification from all the cloud partners as well on the customer side requests move to latest version 

Here is detailed step for upgradtion on Ubuntu 22 . Before upgrading make sure you have noticed all the incompatible changes between 8.0 to 8.4 ,

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-6.html

Best Practices for Updating MySQL from 8.0 to 8.4 | SQLFlash

Lets move on the steps ,

Environment : 

  • Ubuntu 22
  • MySQL 8.0.43
Step 1 : 

Please take complete backup of your existing database server 

mysqldump -u root -p --routines --triggers --events --max_allowed_packet=1024M am80db > am80db_before_8.4_Upgrade.sql

Also take mysql database as well

Step 2 :

Download and add the MySQL APT repo package 

wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb

root@ip-11-1-23-23:~# wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb

--2025-10-17 13:45:36--  https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb

Resolving dev.mysql.com (dev.mysql.com)... 104.120.82.77, 2600:140f:1e00:486::2e31, 2600:140f:1e00:4b2::2e31

Connecting to dev.mysql.com (dev.mysql.com)|104.120.82.77|:443... connected.

HTTP request sent, awaiting response... 302 Moved Temporarily

Location: https://repo.mysql.com//mysql-apt-config_0.8.33-1_all.deb [following]

--2025-10-17 13:45:37--  https://repo.mysql.com//mysql-apt-config_0.8.33-1_all.deb

Resolving repo.mysql.com (repo.mysql.com)... 23.10.47.157, 2600:140f:1e00:a86::1d68, 2600:140f:1e00:a8f::1d68

Connecting to repo.mysql.com (repo.mysql.com)|23.10.47.157|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 18072 (18K) [application/x-debian-package]

Saving to: ‘mysql-apt-config_0.8.33-1_all.deb.1’


mysql-apt-config_0.8.33-1_all.deb.1     100%[============================================================================>]  17.65K  --.-KB/s    in 0.001s


2025-10-17 13:45:37 (25.2 MB/s) - ‘mysql-apt-config_0.8.33-1_all.deb.1’ saved [18072/18072]


(Reading database ... 99983 files and directories currently installed.)

Preparing to unpack mysql-apt-config_0.8.33-1_all.deb ...

Unpacking mysql-apt-config (0.8.33-1) over (0.8.33-1) ...

Setting up mysql-apt-config (0.8.33-1) ...

root@ip-11-1-23-23:~# sudo dpkg-reconfigure mysql-apt-config

File '/usr/share/keyrings/mysql-apt-config.gpg' exists. Overwrite? (y/N) y

root@ip-11-1-23-23:~# sudo apt update

Hit:1 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy InRelease

Hit:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease

Hit:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-backports InRelease

Hit:4 http://repo.mysql.com/apt/ubuntu jammy InRelease

Hit:5 http://security.ubuntu.com/ubuntu jammy-security InRelease

Get:6 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts Sources [965 B]

Get:7 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 Packages [14.5 kB]

Fetched 15.5 kB in 4s (3798 B/s)

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

10 packages can be upgraded. Run 'apt list --upgradable' to see them.

While configure it will ask to choose mysql-8.4 LTS and Click OK . Make sure you have selected 8.4 not 8.0 

then update the package list in the ubuntu 

apt update

root@ip-11-1-23-23:~# sudo apt update

Hit:1 http://repo.mysql.com/apt/ubuntu jammy InRelease

Hit:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy InRelease

Hit:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease

Hit:4 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-backports InRelease

Hit:5 http://security.ubuntu.com/ubuntu jammy-security InRelease

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

10 packages can be upgraded. Run 'apt list --upgradable' to see them.

Step 3 :

Lets start installing the package , make sure selecting conf options properly 

sudo apt install mysql-server -y

root@ip-11-1-23-23:~# sudo apt install mysql-server -y
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libcgi-fast-perl libcgi-pm-perl libclone-perl libencode-locale-perl libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-parser-perl
  libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libprotobuf-lite23
  libtimedate-perl liburi-perl
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-client-plugins mysql-community-server
  mysql-community-server-core
The following packages will be REMOVED:
  mysql-client-8.0 mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0
The following NEW packages will be installed:
  mysql-client mysql-community-client mysql-community-client-core mysql-community-client-plugins mysql-community-server mysql-community-server-core
The following packages will be upgraded:
  mysql-common mysql-server
2 upgraded, 6 newly installed, 4 to remove and 8 not upgraded.
Need to get 36.5 MB of archives.
After this operation, 47.3 MB of additional disk space will be used.
Get:1 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-server amd64 8.4.6-1ubuntu22.04 [59.2 kB]
Get:2 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-common amd64 8.4.6-1ubuntu22.04 [60.6 kB]
Get:3 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client-plugins amd64 8.4.6-1ubuntu22.04 [1524 kB]
Get:4 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client-core amd64 8.4.6-1ubuntu22.04 [1847 kB]
Get:5 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client amd64 8.4.6-1ubuntu22.04 [2174 kB]
Get:6 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-client amd64 8.4.6-1ubuntu22.04 [59.3 kB]
Get:7 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-server-core amd64 8.4.6-1ubuntu22.04 [30.7 MB]
Get:8 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-server amd64 8.4.6-1ubuntu22.04 [69.3 kB]
Fetched 36.5 MB in 5s (7141 kB/s)
Preconfiguring packages ...
(Reading database ... 99983 files and directories currently installed.)
Preparing to unpack .../mysql-server_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-server (8.4.6-1ubuntu22.04) over (8.0.43-0ubuntu0.22.04.2) ...
(Reading database ... 99986 files and directories currently installed.)
Removing mysql-server-8.0 (8.0.43-0ubuntu0.22.04.2) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Removing mysql-client-8.0 (8.0.43-0ubuntu0.22.04.2) ...
Removing mysql-client-core-8.0 (8.0.43-0ubuntu0.22.04.2) ...
Removing mysql-server-core-8.0 (8.0.43-0ubuntu0.22.04.2) ...
(Reading database ... 99784 files and directories currently installed.)
Preparing to unpack .../0-mysql-common_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-common (8.4.6-1ubuntu22.04) over (5.8+1.0.8) ...
Selecting previously unselected package mysql-community-client-plugins.
Preparing to unpack .../1-mysql-community-client-plugins_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-community-client-plugins (8.4.6-1ubuntu22.04) ...
Selecting previously unselected package mysql-community-client-core.
Preparing to unpack .../2-mysql-community-client-core_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-community-client-core (8.4.6-1ubuntu22.04) ...
Selecting previously unselected package mysql-community-client.
Preparing to unpack .../3-mysql-community-client_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-community-client (8.4.6-1ubuntu22.04) ...
Selecting previously unselected package mysql-client.
Preparing to unpack .../4-mysql-client_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-client (8.4.6-1ubuntu22.04) ...
Selecting previously unselected package mysql-community-server-core.
Preparing to unpack .../5-mysql-community-server-core_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-community-server-core (8.4.6-1ubuntu22.04) ...
Selecting previously unselected package mysql-community-server.
Preparing to unpack .../6-mysql-community-server_8.4.6-1ubuntu22.04_amd64.deb ...
Unpacking mysql-community-server (8.4.6-1ubuntu22.04) ...
Setting up mysql-common (8.4.6-1ubuntu22.04) ...
Installing new version of config file /etc/mysql/conf.d/mysql.cnf ...
Installing new version of config file /etc/mysql/my.cnf.fallback ...
Setting up mysql-community-server-core (8.4.6-1ubuntu22.04) ...
Setting up mysql-community-client-plugins (8.4.6-1ubuntu22.04) ...
Setting up mysql-community-client-core (8.4.6-1ubuntu22.04) ...
Setting up mysql-community-client (8.4.6-1ubuntu22.04) ...
Setting up mysql-client (8.4.6-1ubuntu22.04) ...
Setting up mysql-community-server (8.4.6-1ubuntu22.04) ...
Installing new version of config file /etc/apparmor.d/usr.sbin.mysqld ...
Installing new version of config file /etc/mysql/mysql.cnf ...

Configuration file '/etc/mysql/mysql.conf.d/mysqld.cnf'
 ==> Modified (by you or by a script) since installation.
 ==> Package distributor has shipped an updated version.
   What would you like to do about it ?  Your options are:
    Y or I  : install the package maintainer's version
    N or O  : keep your currently-installed version
      D     : show the differences between the versions
      Z     : start a shell to examine the situation
 The default action is to keep your current version.
*** mysqld.cnf (Y/I/N/O/D/Z) [default=N] ? Y
Installing new version of config file /etc/mysql/mysql.conf.d/mysqld.cnf ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Setting up mysql-server (8.4.6-1ubuntu22.04) ...
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.11) ...
Scanning processes...
Scanning candidates...
Scanning linux images...

Running kernel seems to be up-to-date.

Restarting services...
Service restarts being deferred:
 systemctl restart networkd-dispatcher.service
 systemctl restart unattended-upgrades.service
 systemctl restart user@0.service

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.

Step 4 :

Lets do the final restart and check the status of MySQL

sudo systemctl restart mysql
sudo systemctl status mysql

root@ip-11-1-23-23:~# sudo systemctl restart mysql

root@ip-11-1-23-23:~# sudo systemctl status mysql

● mysql.service - MySQL Community Server

     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

     Active: active (running) since Fri 2025-10-17 13:48:46 UTC; 7s ago

       Docs: man:mysqld(8)

             http://dev.mysql.com/doc/refman/en/using-systemd.html

    Process: 95024 ExecStartPre=/usr/share/mysql-8.4/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

   Main PID: 95063 (mysqld)

     Status: "Server is operational"

      Tasks: 36 (limit: 9387)

     Memory: 444.4M

        CPU: 1.905s

     CGroup: /system.slice/mysql.service

             └─95063 /usr/sbin/mysqld


Oct 17 13:48:42 ip-10-1-94-82 systemd[1]: Starting MySQL Community Server...

Oct 17 13:48:46 ip-10-1-94-82 systemd[1]: Started MySQL Community Server.

Step 5 :

Dont try mysql_upgrade is no longer exist on MySQL 8.0.16 then lets complete mysql_secure_installation and update proper password for the root user 

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'ewewewewewe';

Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

Step 6 :

Lets verify the MySQL Upgrade has been success or not on the log file , Highlighted logs clarifing upgrade successfully completed 

sudo tail -n 50 /var/log/mysql/error.log

2025-10-17T13:47:41.670888Z 0 [System] [MY-015015] [Server] MySQL Server - start.

2025-10-17T13:47:42.054060Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.6) starting as process 94072

2025-10-17T13:47:42.129906Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2025-10-17T13:47:49.394972Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2025-10-17T13:47:49.429761Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80023' to '80300'.

2025-10-17T13:47:53.024004Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80023' to '80300' completed.

2025-10-17T13:47:57.289846Z 4 [System] [MY-013381] [Server] Server upgrade from '80043' to '80406' started.

2025-10-17T13:48:07.600863Z 4 [System] [MY-013381] [Server] Server upgrade from '80043' to '80406' completed.

2025-10-17T13:48:08.000645Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2025-10-17T13:48:08.000744Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2025-10-17T13:48:08.081364Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2025-10-17T13:48:08.082336Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.6'  socket: '/var/run/mysqld/mysqld.sock' port: 3306  MySQL Community Server - GPL.

2025-10-17T13:48:39.677032Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.4.6).

2025-10-17T13:48:41.686113Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 10  user: 'Ruban'.

2025-10-17T13:48:41.689689Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 11  user: 'Ruban'.

2025-10-17T13:48:42.651459Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.6)  MySQL Community Server - GPL.

2025-10-17T13:48:42.651514Z 0 [System] [MY-015016] [Server] MySQL Server - end.

2025-10-17T13:48:43.120978Z 0 [System] [MY-015015] [Server] MySQL Server - start.

2025-10-17T13:48:43.492756Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.6) starting as process 95063

2025-10-17T13:48:43.529553Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2025-10-17T13:48:45.123068Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2025-10-17T13:48:46.095152Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2025-10-17T13:48:46.095299Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2025-10-17T13:48:46.181691Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2025-10-17T13:48:46.182507Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.6'  socket: '/var/run/mysqld/mysqld.sock' port: 3306  MySQL Community Server - GPL.

Step 7 :

Final Validation from my end ,



Reference : 
Lets chat if you need further assistance on this , Please comment 

Tuesday, September 16, 2025

Oracle Standard Edition Log Shipping in Windows

 |   |  No comments  |  , , ,

In today’s always-on world, database downtime can cost businesses a fortune. Setting up a reliable Disaster Recovery (DR) solution ensures your organization can quickly recover from unexpected failures.

This guide walks you through a practical, step-by-step process of implementing Oracle 11g Standard Edition DR, from enabling archive logs to recovering the database at the DR site.

Why This Matters

  • Business Continuity: Minimize downtime during outages.

  • Data Protection: Ensure no transaction is lost.

  • Compliance: Meet RTO/RPO requirements.

Prerequisites

Before starting, ensure:

  • Oracle 11g Standard Edition is installed on both DC (Primary) and DR (Standby) servers.

  • Network connectivity & firewall rules allow archive log and backup file transfer between DC and DR.

  • Adequate storage is provisioned for backups and archive logs.

High-Level DR Workflow

[DC: Primary Database]
   |
   |--- Archive Logs + RMAN Backups ---> [DR: Standby Database]
                                              |
                                              --> Restore + Recover --> Open DB

Step-by-Step Implementation

Step 1: Install Oracle 11g

Install Oracle 11g Standard Edition on both DC and DR servers.

Step 2: Enable Archive Logging (DC)

Archive logs capture every committed change — critical for recovery.

SQL>SELECT LOG_MODE FROM V$DATABASE;

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;

Step 3: Force Logging & Supplemental Logs

Ensure redo logs are always generated and enough data is captured for recovery.

SQL>ALTER DATABASE FORCE LOGGING;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 4: Configure Recovery Parameters

Increase recovery area size if required:

SQL>SHOW PARAMETER db_recovery;
SQL>ALTER SYSTEM SET db_recovery_file_dest_size=10g;

Step 5: Configure RMAN

Enable control file autobackup and retention policy:

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Step 6: Take Full Backup (DC)

RMAN>BACKUP DATABASE;

This will back up all datafiles and perform an autobackup of control file & SPFILE.

Step 7: Prepare DR Instance

  • Shutdown instance

  • Remove old datafiles (oradata)

  • Start in NOMOUNT mode

STARTUP NOMOUNT;

Step 8: Move Backup Files to DR

Transfer flash_recovery_area folder to the DR server.

Step 9: Restore Control File & Database

RMAN>RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN>ALTER DATABASE MOUNT;
RMAN>RESTORE DATABASE;

Step 10: Recover Database

Update the catalog in RMAN before proceeding Recover Database 

RMAN> CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
searching for all files that match the pattern C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG no files found to be unknown to the database

Use RMAN or SQL*Plus:
SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Enter AUTO to apply logs automatically.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1046243 generated at 09/16/2025 08:25:59 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC
ORA-00280: change 1046243 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1048238 generated at 09/16/2025 08:35:03 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_21_NDL89XF1_.ARC
ORA-00280: change 1048238 for thread 1 is in sequence #21
ORA-00278: log file 'C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC' no longer needed for this recovery

Finally open with resetlogs:

SQL>ALTER DATABASE OPEN RESETLOGS;

Step 11: Validate

Run validation queries:

SQL>SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;
SQL>SELECT file#, checkpoint_change# FROM v$datafile_header ORDER BY file#;
SQL>SELECT checkpoint_change# FROM v$database;

Ensure no archive log gaps remain.

RMAN Handy Scripts

Here are some ready-to-use RMAN scripts for ongoing maintenance:

Catalog & Recover

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
  RESTORE ARCHIVELOG ALL;
  RECOVER DATABASE;
  DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
  RELEASE CHANNEL c1;
}

Point-in-Time Recovery

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  SET UNTIL SEQUENCE 18 THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
  RELEASE CHANNEL c1;
}

Key Takeaways

  • Automate archive log shipping from DC to DR to ensure near real-time recovery capability.

  • Regularly run RMAN crosschecks to keep backup metadata in sync.

  • Validate DR site periodically by performing trial recovery exercises.

With this approach, you can be confident that your Oracle 11g environment is disaster-ready and downtime can be minimized.

Monday, June 23, 2025

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

 |   |  No comments  |  , , , ,

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

Monday, April 8, 2024

Tuesday, November 14, 2023

PostgreSQL Table Partition on AWS RDS

 |   |  2 comments  |  , , , , ,

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



Sunday, June 25, 2023

Deep dive into Babelfish Compass

 |   |  1 comment  |  , , , , ,

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