convert zabbix server tables to utf8mb4_bin after upgrade from 6 to 7 lts

gistfile1.txt

#!/bin/bash
set -e

echo "Starting Zabbix database collation fix..."

# Load DB credentials from the Zabbix web config
ZBX_CONF="/etc/zabbix/web/zabbix.conf.php"
DB_NAME=$(grep "\$DB\['DATABASE'\]" "$ZBX_CONF" | cut -d "'" -f 4)
DB_USER=$(grep "\$DB\['USER'\]" "$ZBX_CONF" | cut -d "'" -f 4)
DB_PASSWORD=$(grep "\$DB\['PASSWORD'\]" "$ZBX_CONF" | cut -d "'" -f 4)

echo "Database: $DB_NAME"
echo "User    : $DB_USER"

# Find tables with collations that aren't supported by Zabbix 7
echo "Looking for tables with outdated or unsupported collations..."
mysql -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" -e "
  SELECT TABLE_NAME 
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = '$DB_NAME' 
    AND TABLE_COLLATION NOT IN ('utf8_bin','utf8mb3_bin','utf8mb4_bin');
" | tail -n +2 >/tmp/zbx_unsupported_tables.txt

# If such tables are found, convert them
if [[ -s /tmp/zbx_unsupported_tables.txt ]]; then
  echo "The following tables will be converted to utf8mb4_bin:"
  cat /tmp/zbx_unsupported_tables.txt
  while IFS= read -r table; do
    echo "Converting: $table"
    mysql -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" -e \
      "ALTER TABLE \`$table\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"
  done </tmp/zbx_unsupported_tables.txt
  echo "All unsupported collations have been fixed."
else
  echo "All tables already use a supported collation."
fi

# Clean up
rm -f /tmp/zbx_unsupported_tables.txt