Scribbles for my bad memory

IF statement won't work directly in standard SQL scripts outside of a stored procedure or function in MySQL. Thus, we will have to use a different approach using CREATE PROCEDURE to encapsulate the logic. Here’s an example that you can reference to create a stored procedure to safely make the changes:

DELIMITER //

CREATE PROCEDURE AdjustFilesTable()
BEGIN
    -- Change FileExt from char(4) to char(5) if it exists
    IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'Files' AND COLUMN_NAME = 'FileExt') > 0 THEN
        SET @sql = 'ALTER TABLE Files CHANGE COLUMN FileExt FileExt CHAR(5) NOT NULL';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    -- Rename CreateAt to CreatedAt if it exists
    IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'Files' AND COLUMN_NAME = 'CreateAt') > 0 THEN
        SET @sql = 'ALTER TABLE Files CHANGE COLUMN CreateAt CreatedAt BIGINT DEFAULT NULL';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    -- Rename UpdateAt to UpdatedAt if it exists
    IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'Files' AND COLUMN_NAME = 'UpdateAt') > 0 THEN
        SET @sql = 'ALTER TABLE Files CHANGE COLUMN UpdateAt UpdatedAt BIGINT DEFAULT NULL';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END //

DELIMITER ;

-- Call the procedure to adjust the table
CALL AdjustFilesTable();

-- Optionally, drop the procedure afterwards
DROP PROCEDURE IF EXISTS AdjustFilesTable;

Explanation:

1. Stored Procedure Creation: We create a stored procedure named AdjustFilesTable that contains the logic to adjust the table.
2. Conditional Logic: Inside the procedure, we use IF statements to check for the existence of each column before attempting to alter it.
3. Dynamic SQL Execution: We prepare, execute, and deallocate the statements as needed.
4. Calling the Procedure: After creating the procedure, we call it to perform the adjustments.
5. Cleanup: Optionally, you can drop the procedure afterward to prevent clutter.

Note:

• Make sure you have the necessary permissions to create procedures in your MySQL environment.

Muscle Memory Makeover

Docker just streamlined Docker Compose by integrating it as a plugin. Great news, but it means us old hats need to retrain our fingers. Here's a quick fix for your .bashrc to keep things smooth:

alias docker-compose='docker compose'

MySQL in a Flash

As a programmer and Linux admin, I juggle multiple MySQL servers with different group suffixes. Typing --defaults-group-suffix every time was a drag. This handy bash function saves the day:

m() {
  mysql --defaults-group-suffix=$1
}

Now, connecting to a database is as easy as:

m specific-suffix

This keeps your workflow concise and saves you precious keystrokes. Put them into you .bashrc or .zshrc now and let our life easier!

You want to add a unique index to a table, and unfortunately, there are already many duplicate rows in it. Manually find and delete these rows is time-wasting and error-prone. So why won't we just write one SQL statement and quickly resolve it?

First try, I wrote the following statement, and it won't work:

DELETE FROM PromotionSkus A 
WHERE 
	A.SkuId IN (SELECT SkuId FROM PromotionSkus B GROUP BY B.SkuId HAVING COUNT(B.SkuId) > 1) 
	AND 
	A.Id NOT IN (SELECT MIN(Id) FROM PromotionSkus C GROUP BY C.SkuId HAVING COUNT(C.SkuId) > 1);

AND this one below works!

DELETE FROM PromotionSkus A
WHERE 
	A.Id NOT IN (SELECT Id FROM (SELECT MIN(Id) AS Id, COUNT(SkuId) AS Total FROM PromotionSkus GROUP BY SkuId HAVING Total > 1) AS B)
	AND 
	A.SkuId IN (SELECT SkuId FROM (SELECT SkuId FROM PromotionSkus GROUP BY SkuId HAVING COUNT(SkuId) > 1) AS C);

The reason is well explained in this brilliant article.

Another mysql tip: using mysqldump export a table with one line one row.

mysqldump --databases YourDataBaseName --tables YourTableName --skip-extended-insert

Why do we need that? It is much easier to compare !

1. put a .my.cnf file into your home directory, for example

$ cat ~/.my.cnf 
[client]
user=yourDbUserName
password=yourDbPassword
host=yourDbHost

2. write a bash script to update the database

#!/bin/bash

set -ex

variable=`TZ="Africa/Abidjan" date '+%Y-%m-%d %H:%M:%S'`
sql="Your SQL statement with ${variable}"

logDir=/data/Logs/`date '+%Y'`/`date '+%m'`
mkdir -p "${logDir}"
echo "${sql}" >> "${logDir}/`date '+%d'`.sql"
mysql -v -v -e "${sql}" >> "${logDir}/`date '+%d'`.result.txt"

3. put a line by run crontab -e

the end.

1. on a server with huge disk space, run crontab -e and put the following code in it (you may need to change the directory names to fit your situation)

30 3 * * * mysqldump --databases DB1 DB2 DB3 DB4 | gzip > /data/backups/mysql/db_`date '+\%Y\%m\%d-\%H\%M\%S'`.sql.gz
30 4 * * * find /data/backups/mysql/db_* -mtime -1 -exec scp {} anotherserver:/data/backups/mysql \;
30 5 * * * find /data/backups/mysql/db_* -mtime +30 -exec rm {} \;

2. on another server, run crontab -e and put the following line into it:

30 5 * * * find /data/backups/mysql/db_* -mtime +7 -exec rm {} \;

summary & explanation:

1. at 3:30 backup MySQL data in a local directory and keep latest 30 days backup in that directory
2. at 4:30 transfer the latest backup to another server in case the first backup may be destroyed by an accident.
3. at 5:30 remove the oldest backup on the local backup server (the code shows that the latest 30 days backups are kept on the server).
4. at 5:30 remove the oldest backup on the remote server (the code shows that only the latest 7 days backups are kept on that server).