Teguh Triharto Learning Center: 2025

Wednesday, December 24, 2025

.::: create Tools Migration from SQL Server MSSQL to MariaDB using bash shell script :::.

correlation with correlation with https://teguhth.blogspot.com/2024/01/install-configure-odbcinstini-odbc-for.html

1. sample data mssql & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

MSSQL
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query.html

2. data server A & B
server A sql server
ip : 10.10.10.7
db : teguhth
schema : dbo
port 1433
user : admin
password : admin11!!

server B Mariadb
ip : 10.10.10.90
db : teguhthsql
port 3306
user : admin
password : admin

mysql -h 10.10.10.90 -uadmin -p --skip-ssl

buat script shell untuk migrasi dari server A mssql to server B

.::: create Tools Migration from MariaDB to MongoDB using bash shell script :::.

correlation with https://teguhth.blogspot.com/2025/12/create-tools-migration-from-mongodb-to.html

1. data migration server A Maria to server B mongo
server A Mariadb
ip : 10.10.10.90
db : teguhth
port 3306
user : admin
password : admin

server B mongodb
ip : 10.10.10.9
db : mariadbsample
port 27017
user : admin
password : admin

buat script shell untuk migrasi dari server A maria to server B mongo

.::: create Tools Migration from MongoDB to MariaDB using bash shell script :::.

1. sample data edb & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

MongoDB
https://teguhth.blogspot.com/2020/12/study-kasus-praktis-belajar-query.html

2. data server A & B
server A mongodb
ip : 10.10.10.9
db : teguhth
port 27017
user : admin
password : admin

server B Mariadb
ip : 10.10.10.90
db : teguhthedb
port 3306
user : admin
password : admin

buat script shell untuk migrasi dari server A postgres to server B

1. sample data edb & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

PostgreSQL
https://teguhth.blogspot.com/2019/09/study-kasus-praktis-belajar-query.html

2. data server A & B
server A Postgres
ip : 10.10.10.9
db : teguhth
port 5432
user : admin
password : admin

server B Mariadb
ip : 10.10.10.90
db : teguhthedb
port 3306
user : admin
password : admin

.::: DBLink MariaDB Manipulation with Shell Scipt to insert table from Server A to Server B :::.

A. sample source server A server B sample

1. record source & destination

=== source ===
ip : 10.10.10.90
porr : 3306
database : dbatools
table : dbsizeos

=== destination ==
ip : 10.10.10.9
porr : 3306
database : dbatools
table : list_dbsizeos

correlation https://teguhth.blogspot.com/2025/12/script-check-size-database-base-on-size.html

A. Using basic table info

1. create table

CREATE TABLE IF NOT EXISTS dbsizeos (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
capture_time DATETIME DEFAULT CURRENT_TIMESTAMP,
db_name VARCHAR(100) NOT NULL,
size_bytes BIGINT NOT NULL,
size_mb DECIMAL(12,2) NOT NULL
);

2.script

correlation https://teguhth.blogspot.com/2023/08/create-script-backup-database-full.html

1. enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

2. create t-sql sp_backup_full_basic

IF DB_ID('dbatools') IS NULL
BEGIN
CREATE DATABASE [dbatools];
END
GO

USE [dbatools];
GO

CREATE PROCEDURE [dbo].[sp_backup_full_basic]

-- =============================================
-- Author: Teguh Triharto
-- Create date: 22 March 2000
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"
-- =============================================

.::: Script check size database base on size folder in OS Linux example database size MariaDB :::.

ini shell script sederhana, rapi, dan standar DBA untuk list folder di /var/lib/mysql + hitung size-nya.

1. Versi Paling Simpel (langsung pakai du) semual folder n non folder
[root@teguhth sizex]# cat cek_foldernisi.sh
#!/bin/bash

BASE_DIR="/var/lib/mysql"

echo "Database Directory Size Report"
echo "=============================="
printf "%-25s %10s\n" "FOLDER" "SIZE"
echo "------------------------------"

du -sh ${BASE_DIR}/* 2>/dev/null | sort -h | while read size folder; do
printf "%-25s %10s\n" "$(basename "$folder")" "$size"
done

echo "------------------------------"
echo "TOTAL:"
du -sh "$BASE_DIR" | awk '{print $1}'

[root@teguhth sizex]#

.::: Simulation API HEAD Using Phyton & MariaDB MySQL with HTTP Status Code 200,404 :::.

1. Install phyton

sudo dnf install python3 python3-pip -y
pip3 install flask mysql-connector-python

2. create database sample & insert data

CREATE DATABASE hris;
USE hris;

CREATE TABLE buy_period (
id INT AUTO_INCREMENT PRIMARY KEY,
period_year INT NOT NULL,
period_month INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL
);
INSERT INTO buy_period (period_year, period_month, start_date, end_date)
VALUES (2025, 1, '2025-01-01', '2025-01-31');

INSERT INTO buy_period (period_year, period_month, start_date, end_date)
VALUES (2025, 5, '2025-05-01', '2025-05-31');

.::: Create alert using API Bot Telegram to group & subtopic :::.

correlation to https://teguhth.blogspot.com/2025/12/create-alert-using-api-bot-telegram-to.html

1. add bot group to new group with message privilegde

2. run chat sample to username bot

3. get message with id group (record group id chat)

https://api.telegram.org/bot8537695777:AAF6qO3Hm3l695Mw0CpvuIJtQppexF2jAW8/getUpdates

or

curl -s https://api.telegram.org/bot8537695777:AAF6qO3Hm3l695Mw0CpvuIJtQppexF2jAW8/getUpdates

Berikut panduan cara membuat alert/notification ke Telegram dari server atau aplikasi Anda. Saya jelaskan cara paling umum dan paling mudah: mengirim pesan ke Telegram via Bot API.

1. Buat Bot Telegram (via BotFather) example bot 'teguhth' usernamebot 'teguhth_alert_bot'

Buka Telegram.
Cari BotFather.

Ketik:

/start
/newbot

.::: Simulation create view with additional ID Unique row_number(),hash /md5 & Primary Key in Oracle :::.

1. Buat Tabel dengan tanggal_lahir
CREATE TABLE karyawan (
emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nama VARCHAR2(100),
jabatan VARCHAR2(100),
lokasi VARCHAR2(100),
tanggal_lahir DATE
);

2. Insert Data Sample (dengan tanggal lahir)

Dari gambar error tersebut terlihat jelas penyebabnya:

procedure spbuy(integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer) does not exist

Artinya: FUNCTION atau Stored Procedure spbuy (atau sptaxsample) membutuhkan 15 parameter, tapi saat dipanggil hanya diberikan 14 parameter.

. Tujuan Anda

Anda ingin mensimulasikan kasus tersebut pada database sample oracle dengan database contoh bernama hris.

Saya buatkan contoh struktur database, fungsi, dan pemanggilan yang benar & salah, sehingga error yang sama bisa direplikasi.

. SIMULASI DI oracle: Database hris

1. Buat Database Sample

.::: Troubleshoot PostgreSQL procedure spbuy(integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer) does not exist :::.

1. Buat Tabel dengan tanggal_lahir
CREATE TABLE karyawan (
emp_id SERIAL PRIMARY KEY,
nama VARCHAR(100),
jabatan VARCHAR(100),
lokasi VARCHAR(100),
tanggal_lahir DATE
);

2. Insert Data Sample (dengan tanggal lahir)

1. Buat Tabel dengan tanggal_lahir
CREATE TABLE karyawan (
emp_id INT IDENTITY(1,1) PRIMARY KEY,
nama VARCHAR(100),
jabatan VARCHAR(100),
lokasi VARCHAR(100),
tanggal_lahir DATE
);

2. Insert Data Sample (dengan tanggal lahir)

Saya isi tanggal lahir contoh yang wajar:

INSERT INTO karyawan (nama, jabatan, lokasi, tanggal_lahir) VALUES
('Andi', 'Staff IT', 'Jakarta', '1990-05-12'),
('Sinta', 'HRD', 'Bandung', '1992-11-03'),
('Budi', 'Finance', 'Surabaya', '1988-07-21'),
('Ratna', 'Manager', 'Jakarta', '1985-03-10');

.::: Simulation create view with additional ID Unique row_number(),hash /md5 & Primary Key in MariaDB :::.

1. Buat Tabel dengan tanggal_lahir
CREATE TABLE karyawan (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
nama VARCHAR(100),
jabatan VARCHAR(100),
lokasi VARCHAR(100),
tanggal_lahir DATE
);

Dari gambar error tersebut terlihat jelas penyebabnya:

Incorrect number of arguments for FUNCTION ... expected 15, got 14

Artinya: FUNCTION atau Stored Procedure spbuy (atau sptaxsample) membutuhkan 15 parameter, tapi saat dipanggil hanya diberikan 14 parameter.

. Tujuan Anda

Anda ingin mensimulasikan kasus tersebut pada database sample MariaDB dengan database contoh bernama hris.

Saya buatkan contoh struktur database, fungsi, dan pemanggilan yang benar & salah, sehingga error yang sama bisa direplikasi.

. SIMULASI DI MARIADB: Database hris

1. Buat Database Sample
CREATE DATABASE IF NOT EXISTS hris;
USE hris;

.::: T-SQL Script to Drop All Tables: Foreign-Key Tables First, Then Other Tables, While Excluding One Table (e.g., barang) :::.

Berikut beberapa pilihan query SQL Server untuk menampilkan daftar tabel yang memiliki Foreign Key dan diurutkan.

1. Urut berdasarkan nama tabel
SELECT DISTINCT
t.name AS TableName
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables t ON fk.parent_object_id = t.object_id
ORDER BY
t.name;

A. Source (backup data )

1. check table barang

2. Backup data as table using bcp

bcp teguhth.dbo.barang out "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql

bcp barang out "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql -d teguhth

.::: Drop All tables in database using Truncate & drop :::.

correlation with https://teguhth.blogspot.com/2025/10/script-drop-all-database-non-system-in.html

1. Drop table using script

2. Drop table using script and duration

3. script drop_table_indb.sh

[root@teguhth tbldrop]# pwd
/data/drop/tbldrop
[root@teguhth tbldrop]# cat drop_table_indb.sh
#!/bin/bash
# ===========================================================
# Script Name : drop_all_tbl_confirm.sh
# Tujuan : Menghapus semua tables dari 1 Database MariaDB dengan konfirmasi
# OS Diuji : CentOS 9
# Author : Teguh Triharto
# ===========================================================

# === Konfigurasi koneksi ===
USER="admin"
PASS="admin"
HOST="localhost"
DBX="secretdb"
PORT="3306"

.::: Simulation API PATCH Curl Using Phyton & MariaDB MySQL with HTTP Status Code 500, 400, 200, 404 :::.

continue from https://teguhth.blogspot.com/2025/11/simulation-api-post-using-phyton.html

1. script get sample

[root@teguhth api]# cat patch_api_buy.py
from flask import Flask, request, jsonify
import mysql.connector

app = Flask(__name__)

def get_db():
return mysql.connector.connect(
host="localhost",
user="admin",
password="admin",
database="hris"
)

continue from https://teguhth.blogspot.com/2025/11/simulation-api-post-using-phyton.html

1. script get sample

[root@teguhth api]# cat /data/api/put_api_buy.py
from flask import Flask, request, jsonify
import mysql.connector

app = Flask(__name__)

def get_db():
return mysql.connector.connect(
host="localhost",
user="admin",
password="admin",
database="hris"
)

.::: Simulation API GET Curl Using Phyton & MariaDB MySQL with HTTP Status Code 500, 400, 200, 404 :::.

continue from https://teguhth.blogspot.com/2025/11/simulation-api-post-using-phyton.html

1. script get sample

[root@teguhth api]# cat /data/api/get_api_buy.py
from flask import Flask, request, jsonify
import mysql.connector

app = Flask(__name__)

def get_db():
return mysql.connector.connect(
host="localhost",
user="admin",
password="admin",
database="hris"
)

@app.route('/get-buy-period', methods=['GET'])
def get_buy_period():
# Ambil data dari query string
year = request.args.get("year")
month = request.args.get("month")

.::: Testing Script Lucee using Driver MongoDB Database (NoSQL) :::.

1. run mongo.cfm

2. run mongo_find as console

3. run mongo_table as table

1. run mssql

http://10.10.10.90:8888/belimssql.cfm

2. run edb

http://10.10.10.90:8888/beliedb.cfm

3. run oracle

http://10.10.10.90:8888/belioracle.cfm

.::: Install Lucee 6 In Almalinux 9 Centos 9 :::.

1. Install httpd & enable

dnf install httpd -y

systemctl enable httpd
systemctl start httpd

2. download lucee wget https://cdn.lucee.org/lucee-6.2.3.35-linux-x64-installer.run

3. install lucee

/opt/lucee-6.2.3.35-linux-x64-installer.run

[root@teguhth nitip2]# /opt/lucee-6.2.3.35-linux-x64-installer.run
----------------------------------------------------------------------------
Welcome to the Lucee Installer.

Tomcat Version: 11.0.13
Bundled Java: 21.0.9+10-LTS

----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.

.::: Testing Script Lucee using Driver MariaDB & MySQL to access MariaDB Database :::.

1. create database & access

CREATE DATABASE testdb;
CREATE USER 'luceeuser'@'%' IDENTIFIED BY 'Password123!';
GRANT ALL PRIVILEGES ON testdb.* TO 'luceeuser'@'%';
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
pesan VARCHAR(255)
);

INSERT INTO test_table (pesan) VALUES ('Halo dari Lucee di CentOS 9!');

SELECT * FROM test_table ORDER BY id DESC LIMIT 5;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in Oracle Database :::.

1. create table

CREATE TABLE datefilter (
datex DATE NOT NULL
);

CREATE TABLE datefilter (
datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

INSERT INTO datefilter (datex)
WITH cte AS (
SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
FROM dual
CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '1' SECOND
FROM cte;

INSERT INTO datefilter (datex)
WITH cte AS (
SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
FROM dual
CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '15' DAY + INTERVAL '1' SECOND
FROM cte;

1. create table

CREATE TABLE datefilter (
datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

WITH RECURSIVE cte AS (
-- Mulai dari bulan ini
SELECT date_trunc('month', now())::date AS dt,
1 AS lvl

UNION ALL

-- Mundur 36 bulan
SELECT (dt - INTERVAL '1 month')::date,
lvl + 1
FROM cte
WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT dt + INTERVAL '1 millisecond' -- tanggal 1
FROM cte
UNION ALL
SELECT dt + INTERVAL '14 days' + INTERVAL '1 millisecond' -- tanggal 15
FROM cte;

1. create table

CREATE TABLE datefilter (
datex DATETIME NOT NULL
);

2. create date sample 3 years
INSERT INTO datefilter (datex)
WITH RECURSIVE cte AS (
-- Mulai dari bulan ini (tanggal 1)
SELECT
DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AS dt,
1 AS lvl

UNION ALL

-- Mundur 36 bulan
SELECT
DATE_SUB(dt, INTERVAL 1 MONTH),
lvl + 1
FROM cte
WHERE lvl < 36
)
SELECT dt + INTERVAL 1000 MICROSECOND -- tanggal 1
FROM cte

UNION ALL

SELECT (dt + INTERVAL 14 DAY) + INTERVAL 1000 MICROSECOND -- tanggal 15
FROM cte;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in SQL Server MSSQL :::.

1. create table

CREATE TABLE datefilter (
datex DATETIME NOT NULL
);

2. create date sample 3 years

;WITH cte AS (
-- Mulai dari bulan ini
SELECT
CAST(DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS DATETIME) AS dt,
1 AS lvl
UNION ALL
-- Mundur 36 bulan (3 tahun)
SELECT
DATEADD(MONTH, -1, dt),
lvl + 1
FROM cte
WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT DATEADD(MILLISECOND, 1, dt) -- Tanggal 1
FROM cte
UNION ALL
SELECT DATEADD(MILLISECOND, 1, DATEADD(DAY, 14, dt)) -- Tanggal 15
FROM cte
OPTION (MAXRECURSION 0);

.::: Script Monitoring GTID Mirroring using Shell Script Colour :::.

1. Monitoring GTID no colour

.::: Install MariaDB 10.6, Maxscale, Keepalived in Suselinux SLES 15 :::.

1. enable repo

teguhth01suse:~ # cat /etc/zypp/repos.d/mariadb.repo
[mariadb]
enabled=1
autorefresh=1
baseurl=https://yum.mariadb.org/10.6/sles/15/x86_64
gpgcheck=1
teguhth01suse:~ #

2. Prepare packages
rpm -qa | grep -i libmaria

zypper remove libmariadb3-3.1.22-150600.16.3.x86_64

rpm -qa | grep -i libmaria

rpm --import https://downloads.mariadb.com/MariaDB/MariaDB-Server-GPG-KEY
rpm --import https://downloads.mariadb.com/MariaDB/RPM-GPG-KEY-MariaDB

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/socat-1.8.0.0-150600.20.6.1.x86_64.rpm
zypper install socat-1.8.0.0-150600.20.6.1.x86_64.rpm

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm
zypper install libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm

Blog List

Wednesday, December 24, 2025

Tuesday, December 23, 2025

Monday, December 22, 2025

Friday, December 19, 2025

Thursday, December 18, 2025

Wednesday, December 17, 2025

Friday, December 12, 2025

Thursday, December 11, 2025

Wednesday, December 10, 2025

Monday, December 8, 2025

Friday, December 5, 2025

Tuesday, December 2, 2025

Friday, November 28, 2025

Thursday, November 27, 2025

Wednesday, November 26, 2025

Tuesday, November 25, 2025

Monday, November 24, 2025

Friday, November 14, 2025

Tuesday, November 11, 2025

Tuesday, November 4, 2025

Tuesday, October 28, 2025

Popular Posts