Problem: 1.16 Release Notes do mention requiring MySQL8

[misilot]

Expected behaviour
Mention that MySQL 8.x is required in 1.16 now

Current behaviour
Does not mention it at https://wiki.archivematica.org/Archivematica_1.16.0_and_Storage_Service_0.22.0_release_notes

Steps to reproduce
Upgrade from 1.15 to 1.16 with the deploy-pub playbook, and it will error out.

Your environment (version of Archivematica, operating system, other relevant details)
Ubuntu 22.04
Archivematica 1.15 upgrading to 1.16

---

For Artefactual use:

Before you close this issue, you must check off the following:

• All pull requests related to this issue are properly linked
• All pull requests related to this issue have been merged
• A testing plan for this issue has been implemented and passed (testing plan information should be included in the issue body or comments)
• Documentation regarding this issue has been written and merged (if applicable)
• Details about this issue have been added to the release notes (if applicable)

[replaceafill]

@misilot We dropped MySQL 5.x support in Archivematica 1.15.0:

• Issue milestoned in the 1.15.0 release
• Release notes for 1.15.0

We also added a section to the docs about upgrading MySQL 5.x to 8.x.

Does that help?

[misilot]

Thanks, guess I missed that when upgrading to 1.15 :(

Working up converting the collation types. I put a PR in for the deploy-pub to install MySQL 8 (Or upgrade)

artefactual/deploy-pub#150

Though I think this needs to have the major-minor version when it is 8.0 (at least on ubuntu 22.04) since there isn't a package called percona-server-8.0*

[misilot]

@replaceafill so I am trying to use that script and it errors out on one of the views

Processing database: MCP
Fixing database charset and collation
Fixed database charset and collation
Fixing tables charset and collation
Fixed tables charset and collation
Fixing column collation for varchar columns

ERROR 1347 (HY000) at line 20: 'MCP.FileExtensions' is not BASE TABLE

[replaceafill]

@mamedin Could you take a look at this when you have a chance?

[mamedin]

It seems database has some views or temp tables. Please, can you try the following script:

#!/usr/bin/env bash

set -o errexit   # abort on nonzero exitstatus
set -o nounset   # abort on unbound variable
set -o pipefail  # do not hide errors within pipes

# Array of database names
DATABASES=(
  MCP
  SS
)

# Collation and CHARSET
CHARSET="utf8mb4"
COLLATION="utf8mb4_0900_ai_ci"

# MySQL authentication (optional, default no auth)
MYSQL_USE_AUTH=False
MYSQL_USER=root
MYSQL_PASSWORD="THE_PASSWORD"

# Function to execute a query
execute_query() {
    local query="$1"
    local db_name="$2"
    local user_arg=""

    if [ "$MYSQL_USE_AUTH" = "True" ]; then
        user_arg="-u$MYSQL_USER"
        export MYSQL_PWD="$MYSQL_PASSWORD"
    fi

    mysql -N -B $user_arg -e "$query" "$db_name"
}

# Function to fix database charset and collation
fix_database_charset() {
    local query="ALTER DATABASE ${DB_NAME} CHARACTER SET $CHARSET COLLATE $COLLATION;"
    echo "Fixing database charset and collation"
    execute_query "$query" "$DB_NAME"
    echo "Fixed database charset and collation"
}

# Function to fix tables charset and collation
fix_tables_charset() {
    local query="SELECT CONCAT('ALTER TABLE \`', table_name, '\` CHARACTER SET $CHARSET COLLATE $COLLATION;') \
    FROM information_schema.TABLES AS T, information_schema.\`COLLATION_CHARACTER_SET_APPLICABILITY\` AS C \
    WHERE C.collation_name = T.table_collation \
    AND T.table_schema = '$DB_NAME' \
    AND (C.CHARACTER_SET_NAME != '$CHARSET' OR C.COLLATION_NAME != '$COLLATION') \
    AND T.table_type = 'BASE TABLE';"  # Exclude views and temporary tables

    local alter_table_queries=$(execute_query "$query" "$DB_NAME")
    alter_table_queries_no_foreign_key_checks=$(echo -e "SET FOREIGN_KEY_CHECKS=0;\n$alter_table_queries\nSET FOREIGN_KEY_CHECKS=1;")
    echo "Fixing tables charset and collation"
    execute_query "$alter_table_queries_no_foreign_key_checks" "$DB_NAME"
    echo "Fixed tables charset and collation"
}

# Function to fix column collation for varchar columns
fix_varchar_columns_collation() {
    local query="SELECT CONCAT('ALTER TABLE \`', table_name, '\` MODIFY \`', column_name, '\` ', DATA_TYPE, \
    '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET $CHARSET COLLATE $COLLATION', \
    (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';') \
    FROM information_schema.COLUMNS \
    WHERE TABLE_SCHEMA = '$DB_NAME' AND DATA_TYPE = 'varchar' \
    AND (CHARACTER_SET_NAME != '$CHARSET' OR COLLATION_NAME != '$COLLATION') \
    AND table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = '$DB_NAME' AND table_type = 'BASE TABLE');"  # Exclude views and temporary tables

    local alter_table_queries=$(execute_query "$query" "$DB_NAME")
    alter_table_queries_no_foreign_key_checks=$(echo -e "SET FOREIGN_KEY_CHECKS=0;\n$alter_table_queries\nSET FOREIGN_KEY_CHECKS=1;")
    echo "Fixing column collation for varchar columns"
    execute_query "$alter_table_queries_no_foreign_key_checks" "$DB_NAME"
    echo "Fixed column collation for varchar columns"
}

# Function to fix column collation for non-varchar columns
fix_non_varchar_columns_collation() {
    local query="SELECT CONCAT('ALTER TABLE \`', table_name, '\` MODIFY \`', column_name, '\` ', DATA_TYPE, ' \
    CHARACTER SET $CHARSET COLLATE $COLLATION', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';') \
    FROM information_schema.COLUMNS \
    WHERE TABLE_SCHEMA = '$DB_NAME' \
    AND DATA_TYPE != 'varchar' \
    AND (CHARACTER_SET_NAME != '$CHARSET' OR COLLATION_NAME != '$COLLATION') \
    AND table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = '$DB_NAME' AND table_type = 'BASE TABLE');"  # Exclude views and temporary tables

    local alter_table_queries=$(execute_query "$query" "$DB_NAME")
    alter_table_queries_no_foreign_key_checks=$(echo -e "SET FOREIGN_KEY_CHECKS=0;\n$alter_table_queries\nSET FOREIGN_KEY_CHECKS=1;")
    echo "Fixing column collation for non-varchar columns"
    execute_query "$alter_table_queries_no_foreign_key_checks" "$DB_NAME"
    echo "Fixed column collation for non-varchar columns"
}

# Loop through each database in the array
for DB_NAME in "${DATABASES[@]}"; do
    echo "Processing database: $DB_NAME"
    fix_database_charset
    fix_tables_charset
    fix_varchar_columns_collation
    fix_non_varchar_columns_collation
    echo "Migration completed for $DB_NAME"
done

# Unset the MYSQL_PWD environment variable after executing the queries
unset MYSQL_PWD

Summary of Changes:

1. Excluding Views and Temporary Tables in fix_tables_charset:

• Added AND T.table_type = 'BASE TABLE' to exclude views and only target base tables.

2. Excluding Views and Temporary Tables in fix_varchar_columns_collation:

• Changed subquery to AND table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = '$DB_NAME' AND table_type = 'BASE TABLE') to exclude views and only target base tables.

3. Excluding Views and Temporary Tables in fix_non_varchar_columns_collation:

• Changed subquery to AND table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = '$DB_NAME' AND table_type = 'BASE TABLE') to exclude views and only target base tables.

[misilot]

Thanks @mamedin currently reimporting the database and will test this.

[misilot]

Thanks @mamedin that worked! It is failing at the migrations now, with tables already existing but I will put in another ticket for that since it seems unrelated to this.

[mamedin]

Perfect, I just added a note to that new issue. Thanks!