.::: Create Store Procedure Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB MarinaDB MySQL :::.

 

correlation https://teguhth.blogspot.com/2026/04/sync-manual-table-add-column-alter_10.html

1. CREATE TABLE (yang belum ada di aisyah)

DELIMITER $$

CREATE PROCEDURE sp_compare_table(
    IN p_source_db VARCHAR(100),
    IN p_target_db VARCHAR(100)
)
BEGIN
    SET SESSION group_concat_max_len = 1000000;

    SET @sql = CONCAT(
    'SELECT 
        CONCAT(
            ''CREATE TABLE ', p_target_db, '.'' , t.table_name, '' ( \n'',
            GROUP_CONCAT(
                CONCAT(
                    ''  '', c.column_name, '' '',
                    c.column_type,
                    IF(c.is_nullable = ''YES'', '' NULL'', '' NOT NULL'')
                )
                ORDER BY c.ordinal_position
                SEPARATOR '',\n''
            ),
            ''\n);''
        ) AS create_table_script
    FROM information_schema.tables t
    JOIN information_schema.columns c 
        ON t.table_schema = c.table_schema
        AND t.table_name = c.table_name
    WHERE t.table_schema = ''', p_source_db, '''
    AND t.table_name NOT IN (
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = ''', p_target_db, '''
    )
    GROUP BY t.table_name'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;
 

.::: Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB, MySQL, MarinaDB :::.

 

correlation https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. CREATE TABLE (yang belum ada di aisyah)

SELECT 
CONCAT(
    'CREATE TABLE aisyah.', t.table_name, ' ( \n',
    GROUP_CONCAT(
        CONCAT(
            '  ', c.column_name, ' ',
            c.column_type,
            IF(c.is_nullable = 'YES', ' NULL', ' NOT NULL')
        )
        ORDER BY c.ordinal_position
        SEPARATOR ',\n'
    ),
    '\n);'
) AS create_table_script
FROM information_schema.tables t
JOIN information_schema.columns c 
    ON t.table_schema = c.table_schema
    AND t.table_name = c.table_name
WHERE t.table_schema = 'teguhth'
AND t.table_name NOT IN (
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'aisyah'
)
GROUP BY t.table_name;
 

.::: Create Store Procedure Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in SQL Server :::.

 

correlation https://teguhth.blogspot.com/2026/04/sync-manual-table-add-column-alter.html


1. CREATE TABLE (yang belum ada di aisyah)

CREATE PROCEDURE sp_compare_table
    @source_db SYSNAME,
    @target_db SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
 

.::: Sample lab Using OPENROWSET in Microsoft SQL Server 2025 :::.

 

A. prerequirement

1. prerequirement 
Access Database Engine 2016 (x64)

https://www.microsoft.com/en-us/download/details.aspx?id=54920

2. enable access service 
 

3. AKTIFKAN OPENROWSET

Di Microsoft SQL Server:

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

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
 

.::: Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in SQL Server :::.

 

correlation https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare.html

1. CREATE TABLE (yang belum ada di aisyah)

SELECT 
'CREATE TABLE aisyah.dbo.[' + t1.name + '] (' + CHAR(10) +
STRING_AGG(
    '    [' + c1.name + '] ' + 
    ty1.name +
    CASE 
        WHEN ty1.name IN ('varchar','char','varbinary','binary') 
            THEN '(' + 
                CASE WHEN c1.max_length = -1 THEN 'MAX' 
                     ELSE CAST(c1.max_length AS VARCHAR(10)) END + ')'
 

.::: Shell script to simulate pararel query thread or Injector CPU in MariaDB :::.

  

1. run run_query_parallel.sh , cpu lebih rendah 

sh run_query_parallel.sh

[root@teguhth-all pararel]# sh run_query_parallel.sh
Jumlah eksekusi (TOTAL): 10000
Jumlah paralel (THREAD): 10
Menjalankan 10000 query dengan 10 paralel...
Query yang dijalankan: 'SELECT *, SLEEP(0.001) as delay FROM pembelian;'
Progress: 10000/10000 (100%)
Selesai semua eksekusi.
[root@teguhth-all pararel]# 

::: Simalation Locking, Blocking & Deadlock in SQl Server MSSQL, LCK_M_S(locking), LCK_M_X(Blocking Deadlock) :::.

 

Mantap ini contoh tabel kamu sudah pas untuk bikin simulasi.
Saya buatkan 3 skenario nyata di SQL Server: locking ? blocking ? deadlock (step-by-step, tinggal kamu jalankan di 2 query window / session).

1. SIMULASI LOCKING

Session 1 (Window 1) (session 64 sa)

SELECT @@SPID as session_id;
BEGIN TRAN;
UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 10 
WHERE KODE_PEMBELIAN = 'BEL-E001';
 

Session 2 (session 60 sa)
 

.::: Create User Access MariaDB MySQL using login Active Directory Windows via PAM :::.

 

1. correlation from https://teguhth.blogspot.com/2019/08/join-domain-active-directory-on-centos.html

2. enable pam plugin 

INSTALL SONAME 'pam';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';

MariaDB [(none)]> INSTALL SONAME 'pam';
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]>
MariaDB [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| pam         | ACTIVE        |
+-------------+---------------+
1 row in set (0.002 sec)

MariaDB [(none)]>
 

.::: T-SQL Clean backup retention di SQL Server MSSQL :::.

  

correlation with http://teguhth.blogspot.com/2025/12/create-script-backup-all-database-full.html

1. create sp_backup .bat script for backup

### 10.10.10.200

sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_full_basic]"
sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_diff_basic]"
sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_log_basic]"

2. create sp_cleanbackup.bat for sp_clean

sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_cleanup_direct]"
 

.::: Sample Access Database MariaDB via Backend Golang & Frontend using Lucee & PHP :::.

 

1. Sample for table pembelian

mkdir -p /opt/go-pembelian
cd /opt/go-pembelian

mkdir backend
cd backend

2. run mod & driver 

cd backend
go mod init pembelian-api
go get github.com/go-sql-driver/mysql

[root@teguhth-all go-pembelian]# cd backend
[root@teguhth-all backend]# go mod init pembelian-api
go: creating new go.mod: module pembelian-api
[root@teguhth-all backend]# ls
go.mod
[root@teguhth-all backend]# go get github.com/go-sql-driver/mysql
go: downloading github.com/go-sql-driver/mysql v1.9.3
go: downloading filippo.io/edwards25519 v1.1.0
go: added filippo.io/edwards25519 v1.1.0
go: added github.com/go-sql-driver/mysql v1.9.3

[root@teguhth-all backend]# pwd
/opt/go-pembelian/backend
[root@teguhth-all backend]# ls
go.mod  go.sum
[root@teguhth-all backend]#
 

.::: Move log_bin_basename (mysqlbinlogs) MariaDB / MySQL using symlink relink symbolic link :::.

 

A. capture before 

1.check mariadb version

2. check log_bin_basename 

SHOW VARIABLES WHERE Variable_name IN ('datadir','basedir','log_bin_basename');

 

3. check before move datadir 
 
B. move log_bin_basename 
 

.::: Move Datadir MariaDB / MySQL using symlink relink symbolic link :::.

 

A. capture before 

1. check mariadb version

2. check datadir 

SHOW VARIABLES WHERE Variable_name IN ('datadir','basedir');
SHOW VARIABLES LIKE 'log_bin_basename';
 

 

.::: Install MariaDB 10.11.x Using repo local cdrom ISO on Centos 9, Almalinux 9, Redhat 9 :::.

 

1. enable repolocal 

https://teguhth.blogspot.com/2026/02/enable-repo-local-using-cdrom-on-centos.html

2. download

mariadb archive 
https://archive.mariadb.org/mariadb-10.11/yum/almalinux9-amd64/rpms/

maxscale archive
https://dlm.mariadb.com/browse/mariadbmaxscale/24.02.7/yum/rhel/9/x86_64/