How to fix CR_CANT_READ_CHARSET (2019): Can't initialize character set in MySQL

# Check if charsets directory exists
ls -la /usr/share/mysql/charsets/

# Common charset file locations:
# /usr/share/mysql/charsets/
# /usr/share/mysql-8.0/charsets/
# /usr/local/mysql/share/charsets/
# /opt/mysql/share/charsets/

# List charset files
ls /usr/share/mysql/charsets/*.xml

# Should see files like:
# utf8.xml
# utf8mb4.xml
# latin1.xml
# Index.xml
# and others

# Check file permissions
ls -la /usr/share/mysql/charsets/utf8mb4.xml
# Should be readable (typically 644 or 755)

# Ubuntu/Debian
sudo apt-get update
sudo apt-get install --reinstall mysql-client
# or for MySQL 8.0
sudo apt-get install --reinstall mysql-client-8.0
# or for community version
sudo apt-get install --reinstall mysql-community-client

# Verify charsets directory after installation
ls -la /usr/share/mysql/charsets/

# Red Hat/CentOS/Rocky Linux
sudo yum reinstall mysql-community-client
# or
sudo dnf reinstall mysql-community-client

# Verify installation
rpm -ql mysql-community-client | grep charsets

# macOS with Homebrew
brew reinstall mysql-client
# or
brew reinstall mysql

# Verify
ls -la /usr/local/opt/mysql/share/charsets/

# Find where MySQL client expects charset files
mysql --help | grep "character-sets-dir"

# If files exist but in different location, set environment variable
export MYSQL_CHARSET_DIR=/usr/share/mysql/charsets

# Make permanent in bash profile
echo 'export MYSQL_CHARSET_DIR=/usr/share/mysql/charsets' >> ~/.bashrc
source ~/.bashrc

# For system-wide setting
echo 'export MYSQL_CHARSET_DIR=/usr/share/mysql/charsets' | sudo tee -a /etc/environment

// PHP - Set charset directory in PDO options
$options = [
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
];
$dsn = "mysql:host=localhost;dbname=mydb;charset=utf8mb4";
$pdo = new PDO($dsn, $user, $pass, $options);

// Or in php.ini
mysql.default_charset = utf8mb4

# Python - Ensure proper character set in connection
import mysql.connector

config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'password',
    'database': 'mydb',
    'charset': 'utf8mb4',
    'use_unicode': True
}

connection = mysql.connector.connect(**config)

# Option 1: Install full MySQL client package in Dockerfile
FROM php:8.2-fpm

RUN apt-get update && apt-get install -y \
    default-mysql-client \
    && rm -rf /var/lib/apt/lists/*

# Verify charsets are installed
RUN ls -la /usr/share/mysql/charsets/

# Option 2: Copy charset files from MySQL image
FROM php:8.2-fpm

# Copy charsets from official MySQL image
COPY --from=mysql:8.0 /usr/share/mysql/charsets /usr/share/mysql/charsets

# Verify
RUN ls -la /usr/share/mysql/charsets/

# Docker Compose - volume mount charset files
version: '3.8'
services:
  app:
    image: php:8.2-fpm
    volumes:
      - /usr/share/mysql/charsets:/usr/share/mysql/charsets:ro
    environment:
      - MYSQL_CHARSET_DIR=/usr/share/mysql/charsets

# Install charset files in running container
docker exec -it container_name bash
apt-get update && apt-get install -y mysql-client
ls /usr/share/mysql/charsets/
exit

# Commit changes to image
docker commit container_name my-app:with-mysql-charsets

# Check directory permissions
ls -ld /usr/share/mysql/
ls -ld /usr/share/mysql/charsets/

# Directory should be readable and executable
sudo chmod 755 /usr/share/mysql/charsets/

# Check file permissions
ls -la /usr/share/mysql/charsets/

# Files should be readable
sudo chmod 644 /usr/share/mysql/charsets/*.xml

# Check ownership
sudo chown -R root:root /usr/share/mysql/charsets/

# Check SELinux contexts (Red Hat/CentOS)
ls -Z /usr/share/mysql/charsets/

# Restore SELinux contexts if needed
sudo restorecon -Rv /usr/share/mysql/charsets/

# Check if current user can read files
cat /usr/share/mysql/charsets/utf8mb4.xml > /dev/null
echo $?  # Should return 0 if readable

# Test from application user context
sudo -u www-data cat /usr/share/mysql/charsets/utf8mb4.xml > /dev/null
sudo -u nginx cat /usr/share/mysql/charsets/utf8mb4.xml > /dev/null

// PHP - Don't specify charset, let server decide
$dsn = "mysql:host=localhost;dbname=mydb";
$pdo = new PDO($dsn, $user, $pass);

// Or explicitly use latin1 (more widely available)
$dsn = "mysql:host=localhost;dbname=mydb;charset=latin1";
$pdo = new PDO($dsn, $user, $pass);

// Then set charset after connection
$pdo->exec("SET NAMES utf8mb4");

# Python - Let connection use server default
import mysql.connector

config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'password',
    'database': 'mydb'
    # Don't specify charset initially
}

connection = mysql.connector.connect(**config)
# Set charset after connection
connection.cmd_query("SET NAMES utf8mb4")

// Node.js - Use server default charset
const mysql = require('mysql2/promise');

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
  // charset: 'utf8mb4'  // Comment out to use server default
});

// Set charset after connection
await connection.query("SET NAMES utf8mb4");

# Check MySQL client version
mysql --version

# Check installed client library version
ldconfig -p | grep mysql

# Check which libmysqlclient is being used
ldd $(which mysql) | grep mysql

# For PHP, check MySQL client library version
php -i | grep "Client API version"

# For Python
python -c "import mysql.connector; print(mysql.connector.__version__)"

# Update to latest client library
# Ubuntu/Debian
sudo apt-get update
sudo apt-get upgrade mysql-client libmysqlclient-dev

# Red Hat/CentOS
sudo yum update mysql-community-client mysql-community-libs

# Verify charset files after update
ls -la /usr/share/mysql/charsets/

# Rebuild PHP MySQL extension
sudo apt-get install --reinstall php-mysql
# or
sudo pecl install mysql

# Restart PHP-FPM
sudo systemctl restart php8.2-fpm

# Verify
php -m | grep mysql