Scribbles for my bad memory

Working with multiple MySQL environments is a common scenario for developers. Whether you're connecting to development, staging, or production databases, you often need different connection parameters for each environment. While MySQL's configuration files help manage these connections, switching between them can be cumbersome. Let me introduce a simple but powerful bash function that will streamline this process.

The Problem

If you work with MySQL regularly, you likely have multiple connection profiles defined in your ~/.my.cnf file using the [group] syntax. To use a specific configuration group, you need to use the --defaults-group-suffix option:

mysql --defaults-group-suffix=dev
mysql --defaults-group-suffix=prod

This works, but it's verbose and typo-prone.

The Solution: A Smarter Bash Function

Here's a bash function that makes working with MySQL much more convenient:

m() {
    if [ $# -eq 0 ]; then
        # If no arguments provided, run mysql directly
        mysql
    elif [[ "$1" == -* ]]; then
        # If first argument starts with -, pass all arguments directly to mysql
        mysql "$@"
    else
        # Otherwise, treat first argument as suffix
        local suffix=$1
        shift
        mysql --defaults-group-suffix=$suffix "$@"
    fi
}

How It Works

This function provides three different behaviors depending on how you call it:

1. No arguments: Simply runs the MySQL client with default settings.

   m
   

2. First argument starts with a dash: Passes all arguments directly to MySQL, just like you're using the mysql command.

   m -e "SHOW DATABASES"
   

3. First argument doesn't start with a dash: Uses the first argument as the defaults-group-suffix and passes the remaining arguments to MySQL.

   m dev -e "SELECT * FROM users"
   

Practical Examples

Example 1: Connect to Different Environments

m dev    # Connect to development database
m prod   # Connect to production database
m stage  # Connect to staging database

Example 2: Run Commands Directly

m dev -e "SELECT COUNT(*) FROM orders"
m prod -N -e "SELECT id FROM customers LIMIT 5"  # -N removes column names

Example 3: Use MySQL Options Directly

m -N -e "SELECT VERSION()"  # Quick query without environment prefix
m -t -e "SHOW TABLES"  # Table format output

Setup Instructions

1. Add the function to your .bashrc or .zshrc file.
2. Set up your MySQL configuration groups in ~/.my.cnf:

[client]
# Default settings

[clientdev]
user=devuser
password=devpass
host=dev-db.example.com

[clientprod]
user=produser
password=prodpass
host=prod-db.example.com

[mysql]
database=YourDataBase

3. Source your shell configuration file or restart your terminal.

Benefits

• Brevity: No need to type connection parameters (-h, -u, -p) repeatedly, as they're stored in your config file.
• Flexibility: Pass any MySQL options like -N (skip column names) or -e (execute) alongside environment selection.
• Consistency: Maintain the standard MySQL command syntax for direct option usage.

This simple function has saved me countless keystrokes and made database work significantly smoother. It's especially handy for running quick queries with options like -e (execute) and -N (no headers) without having to type connection details every time. Give it a try in your workflow—small improvements like this add up to a much more efficient development experience!

Do you have any favorite shell functions that make your development life easier? Share them in the comments!

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() {
    if [ $# -eq 0 ]; then
        # If no arguments provided, run mysql directly
        mysql
    elif [[ "$1" == -* ]]; then
        # If first argument starts with -, pass all arguments directly to mysql
        mysql "$@"
    else
        # Otherwise, treat first argument as suffix
        local suffix=$1
        shift
        mysql --defaults-group-suffix=$suffix "$@"
    fi
}

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.

However, I found a much simpler solution on 23-Mar-2025, which is

DELETE FROM PromotionSkus 
WHERE Id NOT IN (
    SELECT Id FROM (
        SELECT MIN(Id) AS Id 
        FROM PromotionSkus 
        GROUP BY SkuId
    ) A
);

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.