Eye/updates/db-patch-mysql-utf8.sh

#!/bin/bash

db_name=$1

echo -n "Enter password:"
read db_pass

C_TABLES=$(mysql -u root -p ${db_name} --password=${db_pass} -B -N -e "SHOW TABLES")

echo "${C_TABLES}" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; SET foreign_key_checks = 1; "}' >migration_utf8
echo "ALTER DATABASE ${db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;">>migration_utf8
echo "${C_TABLES}" | while read table; do
mysql -u root -p ${db_name} --password=${db_pass} -e "show create table ${table}" | sed 's/\\n/\n/g' | egrep -i "[[:space:]]MEDIUMTEXT[[:space:]]" | awk '{ print $1 }' | while read c_field; do
    echo "ALTER TABLE $table MODIFY $c_field TEXT NULL" >>migration_utf8
    done
done

exit


mysql -u root -p ${db_name} --password=${db_pass} < migration_utf8
rm -f migration_utf8

exit