How to fix ERROR 1253: COLLATION not valid for CHARACTER SET in MySQL

SHOW VARIABLES LIKE '%char%';
SHOW VARIABLES LIKE '%collation%';

SHOW CREATE TABLE your_table_name;

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

-- Using CONVERT to change both character set and collation
SELECT CONVERT(your_column USING utf8mb4) COLLATE utf8mb4_unicode_ci FROM your_table;

-- Or cast to the correct character set
SELECT CAST(your_column AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci FROM your_table;

DELIMITER //

CREATE PROCEDURE your_procedure(
  IN param1 VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
)
BEGINS
  DECLARE local_var VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  
  -- Procedure body here
END//

DELIMITER ;

-- Replace incompatible collations with compatible ones
-- Find: COLLATE utf8_general_ci (with utf8mb4)
-- Replace: COLLATE utf8mb4_unicode_ci

-- Or normalize to utf8mb4_unicode_ci across the entire dump
-- Find: CHARACTER SET utf8 COLLATE utf8_unicode_ci
-- Replace: CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

-- Change the entire table
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Or change individual columns
ALTER TABLE your_table_name
MODIFY COLUMN your_column VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Linux/Mac
sudo systemctl restart mysql

# Or using mysqld directly
mysqld_safe