Last active
July 17, 2020 06:48
-
-
Save podarcis/cdce3371b8149e86924e9bb5f91e0961 to your computer and use it in GitHub Desktop.
A shell script that can assist in converting a Pimcore 4 database with utf8 encoding to utf8mb4. See https://github.com/pimcore/pimcore/issues/6698
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env bash | |
| # | |
| # Tool for converting (Pimcore) utf8 database to utf8mb4 | |
| # Author: dominik@weblizards.de | |
| # | |
| # Versions: | |
| # 1 - Inital version | |
| # 2 - Don't alter (EcommerceFramework) table default with charset=utf8 and collation=utf8_bin | |
| # | |
| # Credits to write up: https://mathiasbynens.be/notes/mysql-utf8mb4 | |
| # | |
| # USE AT YOUR OWN RISK! ALWAYS BACKUP YOUR DATA FIRST! | |
| MYSQL=mysql | |
| if [[ $# -lt 4 ]]; then | |
| echo "Usage: MYSQL_PWD=db-password ${0##*/} <host> <port> <user> <db>" | |
| echo " set MYSQL_ALTER=y if you want to execute the ALTER statements (BACKUP first!)." | |
| exit | |
| fi | |
| HOST=$1 | |
| PORT=$2 | |
| USER=$3 | |
| DB=$4 | |
| function mysql_query() { | |
| local arg_pw | |
| if [ -z ${MYSQL_PWD+x} ]; then | |
| arg_pw="-p" | |
| fi | |
| $MYSQL -h $HOST -P $PORT -u $USER $arg_pw $DB -e "$@" -B -N | |
| } | |
| function mysql_exec() { | |
| local arg_pw | |
| if [ -z ${MYSQL_PWD+x} ]; then | |
| arg_pw="-p" | |
| fi | |
| echo "> $@" | |
| # execute ALTER statements if env var MYSQL_ALTER=y | |
| if [[ ${MYSQL_ALTER} == y ]]; then | |
| $MYSQL -h $HOST -P $PORT -u $USER $arg_pw $DB -e "$@" | |
| fi | |
| } | |
| # what about the new collations? https://stackoverflow.com/a/766996/8440510 | |
| function get_utf8mb4_collation() { | |
| local collation_alter | |
| case "$1" in | |
| "utf8_general_ci") | |
| collation_alter=utf8mb4_general_ci | |
| ;; | |
| "utf8_unicode_ci") | |
| collation_alter=utf8mb4_unicode_ci | |
| ;; | |
| "utf8_bin") | |
| collation_alter=utf8mb4_bin | |
| ;; | |
| *) | |
| #collation_alter="utf8mb4_???" | |
| echo "Unknown collation '$1', aborting." | |
| exit 1 | |
| ;; | |
| esac | |
| echo $collation_alter | |
| } | |
| # Set SGR (Select Graphic Rendition) parameters | |
| function _sgr() { | |
| # check for color support | |
| tput colors &>/dev/null || return | |
| local csi='\e[' term='m' arg sgr= opt m | |
| for arg in $*; do | |
| opt=${arg%%=*} | |
| case $opt in | |
| "bold" | "bright") m=1 ;; | |
| "underline") m=4 ;; | |
| "blink") m=5 ;; | |
| "reverse" | "inverse") m=7 ;; | |
| "fg" | "c" | "bg") | |
| case ${arg#*=} in | |
| "red") m=1 ;; | |
| "green") m=2 ;; | |
| "yellow") m=3 ;; | |
| "blue") m=4 ;; | |
| "magenta") m=5 ;; | |
| "cyan") m=6 ;; | |
| "white") m=7 ;; | |
| *) m=0 ;; # black (and unknown) | |
| esac | |
| [[ $opt == "bg" ]] && m="4${m}" || m="3${m}" | |
| ;; | |
| *) m=0 ;; # reset/normal (and unknown) | |
| esac | |
| sgr+="${m};" | |
| done | |
| [[ ! ${sgr} ]] && sgr="0" || sgr=${sgr%;} # remove superfluous trailing ";" | |
| echo -n -e "${csi}${sgr}${term}" | |
| } | |
| #################### | |
| data=$(mysql_query "SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$DB';") | |
| charset=$(echo $data | awk '{print $1}') | |
| collation=$(echo $data | awk '{print $2}') | |
| if [[ $charset == "utf8" || $collation == "utf8_*" ]]; then | |
| echo "$(_sgr fg=red)Schema default mismatch (charset=$charset collation=$collation)$(_sgr), execute:" | |
| collation_alter=$(get_utf8mb4_collation $collation) | |
| mysql_exec "ALTER DATABASE \`$DB\` CHARACTER SET = utf8mb4 COLLATE = $collation_alter;" | |
| fi | |
| data=$(mysql_query "SELECT T.table_name, CCSA.character_set_name, T.TABLE_COLLATION | |
| FROM information_schema.TABLES T, | |
| information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA | |
| WHERE CCSA.collation_name = T.table_collation AND T.table_schema = '$DB';") | |
| while read -r line | |
| do | |
| table=$(echo $line | awk '{print $1}') | |
| charset=$(echo $line | awk '{print $2}') | |
| collation=$(echo $line | awk '{print $3}') | |
| if [[ $charset == "utf8" || $collation == "utf8_*" ]]; then | |
| # Pimcore special case: https://github.com/pimcore/pimcore/blob/v6.6.0/bundles/EcommerceFrameworkBundle/Tools/Installer.php#L53 | |
| if [[ $collation == "utf8_bin" ]]; then | |
| echo "$(_sgr fg=yellow)!!!$(_sgr) $(_sgr fg=green)SKIPPING table $table mismatch (charset=$charset collation=$collation)$(_sgr)..." | |
| else | |
| echo "$(_sgr fg=blue)Table $table mismatch (charset=$charset collation=$collation)$(_sgr), execute:" | |
| collation_alter=$(get_utf8mb4_collation $collation) | |
| mysql_exec "ALTER TABLE \`$table\` DEFAULT CHARSET=utf8mb4;" | |
| fi | |
| fi | |
| # BEGIN columns | |
| columns_alter= | |
| data=$(mysql_query "SELECT COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME | |
| FROM information_schema.COLUMNS | |
| WHERE TABLE_SCHEMA = '$DB' and TABLE_NAME = '$table';") | |
| while read -r line; do | |
| column=$(echo $line | awk '{print $1}') | |
| type=$(echo $line | awk '{print $2}') | |
| column_charset=$(echo $line | awk '{print $3}') | |
| column_collation=$(echo $line | awk '{print $4}') | |
| if [[ $column_charset == "utf8" || $column_collation == "utf8_*" ]]; then | |
| # Pimcore special case: https://github.com/pimcore/pimcore/blob/v6.6.0/bundles/InstallBundle/Resources/install.sql#L367 | |
| if [[ $column_collation == "utf8_bin" ]]; then | |
| echo "$(_sgr fg=yellow)!!!$(_sgr) $(_sgr fg=green)SKIPPING table $table column $column $type mismatch (charset=$column_charset collation=$column_collation)$(_sgr)..." | |
| continue | |
| fi | |
| echo "$(_sgr fg=green)Table $table column $column $type mismatch (charset=$column_charset collation=$column_collation)$(_sgr):" | |
| collation_alter=$(get_utf8mb4_collation $column_collation) | |
| # we need to issue the columns modifications all together or we'll get "Column 'column' cannot be part of FULLTEXT index" | |
| if [[ -n $columns_alter ]]; then | |
| columns_alter="${columns_alter}, " | |
| fi | |
| columns_alter="${columns_alter}MODIFY \`$column\` $type CHARACTER SET utf8mb4 COLLATE $collation_alter" | |
| fi | |
| done <<< "$data" | |
| if [[ -n $columns_alter ]]; then | |
| mysql_exec "ALTER TABLE \`$table\` $columns_alter;" | |
| fi | |
| # END columns | |
| # Repair and optimize | |
| mysql_exec "REPAIR TABLE \`$table\`;" | |
| mysql_exec "OPTIMIZE TABLE \`$table\`;" | |
| done <<< "$data" | |
| echo "$(_sgr bg=green)DONE$(_sgr)" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment