How fix broken tables after db import in case there is an broken tables attributes using ddev

ddev-fix-broken-schema-2-databases.md

See the hint that you may need with ddev mysql root login

To export MySQL table schemas from one database and import them into another to fix a broken import, you can use the following approach:

Solution with drop tables

[!ATTENTION!] See the solution that avoids losing data

[!!!ATTENTION!!!] See the comprehensive ALTER statement

1. Export the schema:

First, use mysqldump to export only the table structures (without data) from the source database. With DDEV, you can do this as follows

Need clarification how to execute MySQLdump in ddev as root that should have access to the entire database with schema tables

• About ddev database management
• About changing root password and allow to connect to mysql from different ip

ddev exec mysqldump -u root -p --no-data --skip-comments --skip-set-charset --add-drop-table --routines --events --triggers your_database_name > schema_export.sql

Replace your_database_name with the actual name of your database.

2. Edit the schema file (optional):

If needed, you can edit the schema_export.sql file to remove or modify any statements that might cause issues during import (for example you may need to use updated command without key--add-drop-table ).

3. Import the schema:

To import this schema into the target database, use:

ddev mysql < schema_export.sql

This will create or modify the tables in your current DDEV database to match the exported schema.

4. Fix specific table attributes:

If you need to fix specific attributes on tables that were lost during the previous import, you can generate ALTER TABLE statements. Here's a query to help identify differences:

SELECT 
    CONCAT(
        'ALTER TABLE `', t.TABLE_NAME, '` ',
        GROUP_CONCAT(
            CASE
                WHEN c.COLUMN_NAME IS NULL THEN CONCAT('ADD COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                    IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                )
                WHEN sc.COLUMN_NAME IS NULL THEN CONCAT('DROP COLUMN `', c.COLUMN_NAME, '`')
                WHEN sc.COLUMN_TYPE != c.COLUMN_TYPE OR sc.IS_NULLABLE != c.IS_NULLABLE OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT OR sc.EXTRA != c.EXTRA 
                THEN CONCAT('MODIFY COLUMN `', c.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                    IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                )
            END
            SEPARATOR ', '
        ),
        ';'
    ) AS alter_statement
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN 
    information_schema.COLUMNS sc ON t.TABLE_NAME = sc.TABLE_NAME AND c.COLUMN_NAME = sc.COLUMN_NAME AND sc.TABLE_SCHEMA = 'source_database'
WHERE 
    t.TABLE_SCHEMA = 'target_database'
    AND (c.COLUMN_NAME IS NULL OR sc.COLUMN_NAME IS NULL OR sc.COLUMN_TYPE != c.COLUMN_TYPE OR sc.IS_NULLABLE != c.IS_NULLABLE OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT OR sc.EXTRA != c.EXTRA)
GROUP BY 
    t.TABLE_NAME;

Replace 'source_database' with the name of your source database (the one with the correct schema) and 'target_database' with the name of your target database (the one you're fixing).

This query will generate ALTER TABLE statements for any differences between the two databases.

5. Execute the ALTER statements:

Save the output of the above query to a file (let's call it alter_statements.sql), then execute it:

ddev mysql < alter_statements.sql

This process should help you export the correct schema from one database and apply it to another, fixing any attributes that were lost during a previous import. Remember to always backup your database before making significant changes.

Adjust script to avoid losing data in DB tables

It will results that tables would be droped and you loose data after applying the schema to your broken database

ddev exec mysqldump -u root -p --no-data --skip-comments --skip-set-charset --add-drop-table --routines --events --triggers your_database_name > schema_export.sql

Let me clarify and provide a safer approach:

The --add-drop-table option in the mysqldump command does include DROP TABLE statements before each CREATE TABLE statement. This could potentially lead to data loss if applied directly to a database with existing data. Let's modify our approach to ensure we don't lose any data:

1. Export the schema without the --add-drop-table option:

ddev exec mysqldump -u root -p --no-data --skip-comments --skip-set-charset --routines --events --triggers your_database_name > schema_export.sql

This command will export the schema without DROP TABLE statements.

2. Review and edit the schema_export.sql file:

• Remove any potentially destructive statements.
• Change CREATE TABLE statements to CREATE TABLE IF NOT EXISTS to avoid errors for existing tables.

3. For existing tables that need modification, we'll generate ALTER TABLE statements instead of replacing the entire table. Here's a query to help identify differences and generate safe ALTER TABLE statements:

SELECT 
    CONCAT(
        'ALTER TABLE `', t.TABLE_NAME, '` ',
        GROUP_CONCAT(
            CASE
                WHEN c.COLUMN_NAME IS NULL THEN CONCAT('ADD COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                    IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                )
                WHEN sc.COLUMN_TYPE != c.COLUMN_TYPE OR sc.IS_NULLABLE != c.IS_NULLABLE OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT OR sc.EXTRA != c.EXTRA 
                THEN CONCAT('MODIFY COLUMN `', c.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                    IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                )
            END
            SEPARATOR ', '
        ),
        ';'
    ) AS alter_statement
FROM 
    information_schema.TABLES t
JOIN 
    information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN 
    information_schema.COLUMNS sc ON t.TABLE_NAME = sc.TABLE_NAME AND c.COLUMN_NAME = sc.COLUMN_NAME AND sc.TABLE_SCHEMA = 'source_database'
WHERE 
    t.TABLE_SCHEMA = 'target_database'
    AND (sc.COLUMN_TYPE != c.COLUMN_TYPE OR sc.IS_NULLABLE != c.IS_NULLABLE OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT OR sc.EXTRA != c.EXTRA)
GROUP BY 
    t.TABLE_NAME
HAVING 
    alter_statement IS NOT NULL;

Replace 'source_database' with the name of your source database (the one with the correct schema) and 'target_database' with the name of your target database (the one you're fixing).

4. Save the output of this query to a file named alter_statements.sql.

5. Now, you can safely apply the changes:

ddev mysql < schema_export.sql
ddev mysql < alter_statements.sql

This approach will:

• Create any missing tables (without dropping existing ones)
• Add any missing columns to existing tables
• Modify existing columns to match the source schema

This method ensures that you don't lose any existing data while still updating the schema to match the source database. Always remember to backup your database before making any schema changes, just in case.

Enhance ALTER statement

I'll extend the query to include collation changes for both tables and columns. This updated query will generate ALTER statements to modify table and column collations to match the source database. Here's the enhanced version:

SELECT 
    CONCAT(
        'ALTER TABLE `', t.TABLE_NAME, '` ',
        -- Change table collation if different
        IF(t.TABLE_COLLATION != st.TABLE_COLLATION,
           CONCAT('COLLATE = ', st.TABLE_COLLATION, ', '),
           ''),
        -- Column modifications
        GROUP_CONCAT(
            CASE
                WHEN c.COLUMN_NAME IS NULL THEN 
                    CONCAT('ADD COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, 
                    IF(sc.COLLATION_NAME IS NOT NULL, CONCAT(' COLLATE ', sc.COLLATION_NAME), ''),
                    ' ', IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, 
                       CONCAT(' DEFAULT ', 
                              CASE 
                                  WHEN sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP' THEN sc.COLUMN_DEFAULT
                                  WHEN sc.DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext') THEN CONCAT('''', REPLACE(sc.COLUMN_DEFAULT, '''', '\\'''), '''')
                                  ELSE sc.COLUMN_DEFAULT
                              END),
                       ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                    )
                WHEN sc.COLUMN_NAME IS NULL THEN 
                    CONCAT('DROP COLUMN `', c.COLUMN_NAME, '`')
                WHEN sc.COLUMN_TYPE != c.COLUMN_TYPE 
                  OR sc.IS_NULLABLE != c.IS_NULLABLE 
                  OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT 
                  OR sc.EXTRA != c.EXTRA
                  OR sc.COLLATION_NAME != c.COLLATION_NAME THEN 
                    CONCAT('MODIFY COLUMN `', c.COLUMN_NAME, '` ', sc.COLUMN_TYPE, 
                    IF(sc.COLLATION_NAME IS NOT NULL, CONCAT(' COLLATE ', sc.COLLATION_NAME), ''),
                    ' ', IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, 
                       CONCAT(' DEFAULT ', 
                              CASE 
                                  WHEN sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP' THEN sc.COLUMN_DEFAULT
                                  WHEN sc.DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext') THEN CONCAT('''', REPLACE(sc.COLUMN_DEFAULT, '''', '\\'''), '''')
                                  ELSE sc.COLUMN_DEFAULT
                              END),
                       ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                    )
            END
            SEPARATOR ', '
        ),
        ';'
    ) AS alter_statement
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN 
    information_schema.TABLES st ON t.TABLE_NAME = st.TABLE_NAME AND st.TABLE_SCHEMA = 'source_database'
LEFT JOIN 
    information_schema.COLUMNS sc ON t.TABLE_NAME = sc.TABLE_NAME AND c.COLUMN_NAME = sc.COLUMN_NAME AND sc.TABLE_SCHEMA = 'source_database'
WHERE 
    t.TABLE_SCHEMA = 'target_database'
    AND (
        c.COLUMN_NAME IS NULL 
        OR sc.COLUMN_NAME IS NULL 
        OR sc.COLUMN_TYPE != c.COLUMN_TYPE 
        OR sc.IS_NULLABLE != c.IS_NULLABLE 
        OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT 
        OR sc.EXTRA != c.EXTRA
        OR sc.COLLATION_NAME != c.COLLATION_NAME
        OR t.TABLE_COLLATION != st.TABLE_COLLATION
    )
GROUP BY 
    t.TABLE_NAME
HAVING 
    alter_statement IS NOT NULL;

This enhanced query does the following:

1. Compares table collations and includes an ALTER TABLE statement to change the table collation if it differs from the source database.
2. For each column, it now includes the collation in both ADD COLUMN and MODIFY COLUMN statements when applicable.
3. It handles the COLUMN_DEFAULT value more carefully, especially for string data types, to avoid issues with quotes.
4. It checks for differences in column collations and includes them in the MODIFY COLUMN statements when necessary. To use this query:
5. Replace 'source_database' with the name of your original database (the one with the correct schema and collations).
6. Replace 'target_database' with the name of your target database (the one you're fixing).
7. Run this query in your MySQL client or through DDEV.
8. Save the output to a file, for example, alter_statements.sql.
9. Review the generated ALTER statements to ensure they're correct and safe to apply.
10. Apply the changes:

ddev mysql < alter_statements.sql

This approach will update your target database to match the source database's schema, including table and column collations, without losing existing data. As always, make sure to backup your database before applying any schema changes.

What else may need to be added to the Alter statement considering the most detailed mysql statement to create a table with all possible attributes?

To consider a more comprehensive approach. Let's enhance our ALTER statement generation to include additional important attributes that can be part of a detailed CREATE TABLE statement. Here's an expanded version of the query that covers more aspects:

SELECT 
    CONCAT(
        'ALTER TABLE `', t.TABLE_NAME, '` ',
        -- Change table collation if different
        IF(t.TABLE_COLLATION != st.TABLE_COLLATION,
           CONCAT('COLLATE = ', st.TABLE_COLLATION, ', '),
           ''),
        -- Change table comment if different
        IF(t.TABLE_COMMENT != st.TABLE_COMMENT,
           CONCAT('COMMENT = ', QUOTE(st.TABLE_COMMENT), ', '),
           ''),
        -- Change table engine if different
        IF(t.ENGINE != st.ENGINE,
           CONCAT('ENGINE = ', st.ENGINE, ', '),
           ''),
        -- Column modifications
        GROUP_CONCAT(
            CASE
                WHEN c.COLUMN_NAME IS NULL THEN 
                    CONCAT('ADD COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, 
                    IF(sc.COLLATION_NAME IS NOT NULL, CONCAT(' COLLATE ', sc.COLLATION_NAME), ''),
                    ' ', IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, 
                       CONCAT(' DEFAULT ', 
                              CASE 
                                  WHEN sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP' THEN sc.COLUMN_DEFAULT
                                  WHEN sc.DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext') THEN CONCAT('''', REPLACE(sc.COLUMN_DEFAULT, '''', '\\'''), '''')
                                  ELSE sc.COLUMN_DEFAULT
                              END),
                       ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), ''),
                    IF(sc.COLUMN_COMMENT != '', CONCAT(' COMMENT ', QUOTE(sc.COLUMN_COMMENT)), '')
                    )
                WHEN sc.COLUMN_NAME IS NULL THEN 
                    CONCAT('DROP COLUMN `', c.COLUMN_NAME, '`')
                WHEN sc.COLUMN_TYPE != c.COLUMN_TYPE 
                  OR sc.IS_NULLABLE != c.IS_NULLABLE 
                  OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT 
                  OR sc.EXTRA != c.EXTRA
                  OR sc.COLLATION_NAME != c.COLLATION_NAME
                  OR sc.COLUMN_COMMENT != c.COLUMN_COMMENT THEN 
                    CONCAT('MODIFY COLUMN `', c.COLUMN_NAME, '` ', sc.COLUMN_TYPE, 
                    IF(sc.COLLATION_NAME IS NOT NULL, CONCAT(' COLLATE ', sc.COLLATION_NAME), ''),
                    ' ', IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, 
                       CONCAT(' DEFAULT ', 
                              CASE 
                                  WHEN sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP' THEN sc.COLUMN_DEFAULT
                                  WHEN sc.DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext') THEN CONCAT('''', REPLACE(sc.COLUMN_DEFAULT, '''', '\\'''), '''')
                                  ELSE sc.COLUMN_DEFAULT
                              END),
                       ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), ''),
                    IF(sc.COLUMN_COMMENT != '', CONCAT(' COMMENT ', QUOTE(sc.COLUMN_COMMENT)), '')
                    )
            END
            SEPARATOR ', '
        ),
        -- Add primary key if it's different or missing
        IF(tp.PRIMARY_KEY != sp.PRIMARY_KEY,
           CONCAT(', DROP PRIMARY KEY, ADD PRIMARY KEY (', GROUP_CONCAT(DISTINCT CONCAT('`', sp.COLUMN_NAME, '`')), ')'),
           ''),
        -- Add or modify indexes
        GROUP_CONCAT(
            DISTINCT
            CASE
                WHEN si.INDEX_NAME IS NOT NULL AND ti.INDEX_NAME IS NULL THEN
                    CONCAT(', ADD ', IF(si.NON_UNIQUE = 0, 'UNIQUE ', ''), 'INDEX `', si.INDEX_NAME, '` (`', si.COLUMN_NAME, '`)')
                WHEN si.INDEX_NAME IS NOT NULL AND ti.INDEX_NAME IS NOT NULL AND (si.NON_UNIQUE != ti.NON_UNIQUE OR si.COLUMN_NAME != ti.COLUMN_NAME) THEN
                    CONCAT(', DROP INDEX `', ti.INDEX_NAME, '`, ADD ', IF(si.NON_UNIQUE = 0, 'UNIQUE ', ''), 'INDEX `', si.INDEX_NAME, '` (`', si.COLUMN_NAME, '`)')
                WHEN si.INDEX_NAME IS NULL AND ti.INDEX_NAME IS NOT NULL THEN
                    CONCAT(', DROP INDEX `', ti.INDEX_NAME, '`')
            END
        ),
        -- Add or modify foreign keys
        GROUP_CONCAT(
            DISTINCT
            CASE
                WHEN sfk.CONSTRAINT_NAME IS NOT NULL AND tfk.CONSTRAINT_NAME IS NULL THEN
                    CONCAT(', ADD CONSTRAINT `', sfk.CONSTRAINT_NAME, '` FOREIGN KEY (`', sfk.COLUMN_NAME, '`) REFERENCES `', sfk.REFERENCED_TABLE_NAME, '` (`', sfk.REFERENCED_COLUMN_NAME, '`)')
                WHEN sfk.CONSTRAINT_NAME IS NOT NULL AND tfk.CONSTRAINT_NAME IS NOT NULL AND (sfk.REFERENCED_TABLE_NAME != tfk.REFERENCED_TABLE_NAME OR sfk.REFERENCED_COLUMN_NAME != tfk.REFERENCED_COLUMN_NAME) THEN
                    CONCAT(', DROP FOREIGN KEY `', tfk.CONSTRAINT_NAME, '`, ADD CONSTRAINT `', sfk.CONSTRAINT_NAME, '` FOREIGN KEY (`', sfk.COLUMN_NAME, '`) REFERENCES `', sfk.REFERENCED_TABLE_NAME, '` (`', sfk.REFERENCED_COLUMN_NAME, '`)')
                WHEN sfk.CONSTRAINT_NAME IS NULL AND tfk.CONSTRAINT_NAME IS NOT NULL THEN
                    CONCAT(', DROP FOREIGN KEY `', tfk.CONSTRAINT_NAME, '`')
            END
        ),
        ';'
    ) AS alter_statement
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN 
    information_schema.TABLES st ON t.TABLE_NAME = st.TABLE_NAME AND st.TABLE_SCHEMA = 'source_database'
LEFT JOIN 
    information_schema.COLUMNS sc ON t.TABLE_NAME = sc.TABLE_NAME AND c.COLUMN_NAME = sc.COLUMN_NAME AND sc.TABLE_SCHEMA = 'source_database'
LEFT JOIN 
    (SELECT TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS PRIMARY_KEY 
     FROM information_schema.STATISTICS 
     WHERE TABLE_SCHEMA = 'target_database' AND INDEX_NAME = 'PRIMARY' 
     GROUP BY TABLE_NAME) tp ON t.TABLE_NAME = tp.TABLE_NAME
LEFT JOIN 
    (SELECT TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS PRIMARY_KEY 
     FROM information_schema.STATISTICS 
     WHERE TABLE_SCHEMA = 'source_database' AND INDEX_NAME = 'PRIMARY' 
     GROUP BY TABLE_NAME) sp ON t.TABLE_NAME = sp.TABLE_NAME
LEFT JOIN 
    information_schema.STATISTICS si ON st.TABLE_NAME = si.TABLE_NAME AND si.TABLE_SCHEMA = 'source_database' AND si.INDEX_NAME != 'PRIMARY'
LEFT JOIN 
    information_schema.STATISTICS ti ON t.TABLE_NAME = ti.TABLE_NAME AND ti.TABLE_SCHEMA = 'target_database' AND ti.INDEX_NAME != 'PRIMARY'
LEFT JOIN 
    information_schema.KEY_COLUMN_USAGE sfk ON st.TABLE_NAME = sfk.TABLE_NAME AND sfk.TABLE_SCHEMA = 'source_database' AND sfk.REFERENCED_TABLE_NAME IS NOT NULL
LEFT JOIN 
    information_schema.KEY_COLUMN_USAGE tfk ON t.TABLE_NAME = tfk.TABLE_NAME AND tfk.TABLE_SCHEMA = 'target_database' AND tfk.REFERENCED_TABLE_NAME IS NOT NULL
WHERE 
    t.TABLE_SCHEMA = 'target_database'
    AND (
        c.COLUMN_NAME IS NULL 
        OR sc.COLUMN_NAME IS NULL 
        OR sc.COLUMN_TYPE != c.COLUMN_TYPE 
        OR sc.IS_NULLABLE != c.IS_NULLABLE 
        OR sc.COLUMN_DEFAULT != c.COLUMN_DEFAULT 
        OR sc.EXTRA != c.EXTRA
        OR sc.COLLATION_NAME != c.COLLATION_NAME
        OR sc.COLUMN_COMMENT != c.COLUMN_COMMENT
        OR t.TABLE_COLLATION != st.TABLE_COLLATION
        OR t.TABLE_COMMENT != st.TABLE_COMMENT
        OR t.ENGINE != st.ENGINE
        OR tp.PRIMARY_KEY != sp.PRIMARY_KEY
        OR si.INDEX_NAME IS NOT NULL
        OR ti.INDEX_NAME IS NOT NULL
        OR sfk.CONSTRAINT_NAME IS NOT NULL
        OR tfk.CONSTRAINT_NAME IS NOT NULL
    )
GROUP BY 
    t.TABLE_NAME
HAVING 
    alter_statement IS NOT NULL;

This enhanced query now includes:

1. Table-level attributes:

• Collation
• Comment
• Engine

2. Column-level attributes:

• Data type
• Collation
• Nullability
• Default value

3. Extra attributes (e.g., AUTO_INCREMENT)

• Column comments

4. Primary Keys:

• Drops and re-adds the primary key if it's different

5. Indexes:

• Adds missing indexes
• Modifies existing indexes if they're different
• Drops indexes that don't exist in the source database

6. Foreign Keys:

• Adds missing foreign keys
• Modifies existing foreign keys if they're different
• Drops foreign keys that don't exist in the source database

To use this query:

1. Replace 'source_database' with the name of your original database (the one with the correct schema).
2. Replace 'target_database' with the name of your target database (the one you're fixing).
3. Run this query in your MySQL client or through DDEV.
4. Save the output to a file, for example, comprehensive_alter_statements.sql.
5. Review the generated ALTER statements carefully to ensure they're correct and safe to apply.
6. Apply the changes:

ddev mysql < comprehensive_alter_statements.sql

This comprehensive approach should cover most aspects of table structure that you might need to synchronize between databases. However, always review the generated statements and test them in a safe environment before applying them to your production database. Some complex constraints or database-specific features might still require manual intervention.

The stated above approach may results with empty ALTER TABLE statements.
Here is the proper prompt:

Change the db_test_cts to your broken database and db to your source database with correct schema.

SET SESSION group_concat_max_len = 1000000;

SELECT 
    CONCAT(
        'ALTER TABLE `', t.TABLE_NAME, '` ',
        GROUP_CONCAT(
            DISTINCT CASE
                WHEN c.COLUMN_NAME IS NULL THEN CONCAT('ADD COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                    IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                )
                ELSE CONCAT('MODIFY COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                    IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                    IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                    IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
                )
            END
            SEPARATOR ', '
        ),
        ';'
    ) AS alter_statement
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = 'db_test_cts'
LEFT JOIN 
    information_schema.COLUMNS sc ON t.TABLE_NAME = sc.TABLE_NAME AND sc.TABLE_SCHEMA = 'db' AND c.COLUMN_NAME = sc.COLUMN_NAME
WHERE 
    t.TABLE_SCHEMA = 'db_test_cts'
GROUP BY 
    t.TABLE_NAME;

If you still encounter issues with very large tables or if you prefer to have each column modification as a separate statement, you can further modify the query to output one row per column change:

SELECT 
    CONCAT(
        'ALTER TABLE `', t.TABLE_NAME, '` ',
        CASE
            WHEN c.COLUMN_NAME IS NULL THEN CONCAT('ADD COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
            )
            ELSE CONCAT('MODIFY COLUMN `', sc.COLUMN_NAME, '` ', sc.COLUMN_TYPE, ' ', 
                IF(sc.IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), 
                IF(sc.COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', IF(sc.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', sc.COLUMN_DEFAULT, CONCAT('''', sc.COLUMN_DEFAULT, ''''))), ''),
                IF(sc.EXTRA != '', CONCAT(' ', sc.EXTRA), '')
            )
        END,
        ';'
    ) AS alter_statement
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = 'db_test_cts'
LEFT JOIN 
    information_schema.COLUMNS sc ON t.TABLE_NAME = sc.TABLE_NAME AND sc.TABLE_SCHEMA = 'db' AND c.COLUMN_NAME = sc.COLUMN_NAME
WHERE 
    t.TABLE_SCHEMA = 'db_test_cts';

Change the db_test_cts to your broken database and db to your source database with correct schema.

This version will output each column modification as a separate ALTER TABLE statement, which should avoid any issues with statement length.

The fixing process

while IFS= read -r statement; do
    echo "Executing: $statement"
    echo "$statement" | ddev mysql -u db -pdb db_test_cts
done < sql/alter_statement.sql

Here is the ddev command that create ALTER statements fo all tables to be applied on broken database

#!/bin/bash

## Description: Generate ALTER statements for all tables in the database
## Usage: shell-alter-gen
## Example: ddev shell-alter-gen

echo "Running shell-alter-gen"
pwd

DB_SOURCE="db"
DB_TARGET="db_test_cts"
OUTPUT_FILE="sql/alter_statements-3.sql"

# Initialize the output file
echo "-- Generated ALTER statements for database: $DB_TARGET based on $DB_SOURCE" > $OUTPUT_FILE

# Get all table names from the source database
tables=$(ddev mysql --silent --skip-column-names -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$DB_SOURCE';")

# Check if the command to get table names was successful
if [ $? -ne 0 ]; then
    echo "Failed to get table names from database: $DB_SOURCE"
    exit 2
fi

# Iterate over each table and generate ALTER statements
for table in $tables; do
    echo "-- Generating ALTER statements for table: $table"
    echo "-- Generating ALTER statements for table: $table" >> $OUTPUT_FILE

    # Columns Information
    ddev mysql --silent --skip-column-names 2>/dev/null <<EOF >> $OUTPUT_FILE
    SELECT CONCAT(
        'ALTER TABLE ', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' ', COLUMN_TYPE,
        IF(IS_NULLABLE = 'NO', ' NOT NULL', ''),
        IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)), ''),
        IF(EXTRA != '', CONCAT(' ', EXTRA), ''),
        IF(COLUMN_COMMENT != '', CONCAT(' COMMENT ', QUOTE(REPLACE(COLUMN_COMMENT, "'", '"'))), ''),
        ';'
    ) AS alter_statement
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '$DB_SOURCE' AND TABLE_NAME = '$table'
EOF

    # Check if the command to get columns information was successful
    if [ $? -ne 0 ]; then
        echo "Failed to get columns information for table: $table"
        continue
    fi

    # Indexes Information
    ddev mysql --silent --skip-column-names 2>/dev/null <<EOF >> $OUTPUT_FILE
    SELECT CONCAT('ALTER TABLE ', TABLE_NAME, 
                  IF(INDEX_NAME = 'PRIMARY', ' ADD PRIMARY KEY ', 
                     IF(NON_UNIQUE = 0, ' ADD UNIQUE INDEX ', ' ADD INDEX ')), 
                  IF(INDEX_NAME != 'PRIMARY', INDEX_NAME, ''), 
                  ' (', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ', '), ');') AS alter_statement
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = '$DB_SOURCE' AND TABLE_NAME = '$table'
    GROUP BY INDEX_NAME, NON_UNIQUE;
EOF

    # Check if the command to get indexes information was successful
    if [ $? -ne 0 ]; then
        echo "Failed to get indexes information for table: $table"
        continue
    fi

    # Constraints Information (for foreign keys)
    ddev mysql --silent --skip-column-names 2>/dev/null <<EOF >> $OUTPUT_FILE
    SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD CONSTRAINT ', CONSTRAINT_NAME, 
                  ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME, ');') AS alter_statement
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = '$DB_SOURCE' AND TABLE_NAME = '$table' AND REFERENCED_TABLE_NAME IS NOT NULL;
EOF

    # Check if the command to get constraints information was successful
    if [ $? -ne 0 ]; then
        echo "Failed to get constraints information for table: $table"
        continue
    fi

done

echo "ALTER statements have been written to $OUTPUT_FILE"

Read more about How to create custom ddev commands

Another way is to use mysqldiff program which you need to install with pip2 install . in the mysql-utilities root folder of that package.
by default, it is not compatible with mysql8 cause it used the algorithm for authentication caching_sha2_password which is not covered in the Python package,

pip2 was removed from Ubuntu 20.04, but here is a guide to get it anyway:
https://stackoverflow.com/questions/61981156/unable-to-locate-package-python-pip-ubuntu-20-04

so I tried to extend the package with the additional plugin and it was able to establish a connection and perform some queries on mysql8 db as root.

1. Implement a Plugin for authentication

mysql-utilities-1.7.0-ai compatible with mysql8 Published on github (read commit message with link this workaround)

2. Adjust generated mysqldiff script in ~/.local/bin/mysqldiff

If you don't want to modify the library code, you can set the character set explicitly when connecting. Modify the mysqldiff script to include the character set in the connection options:
Add 'charset': 'utf8' to the conn_opts dictionary:

conn_opts = {
    'quiet': True,
    'version': "5.1.30",
    'charset': 'utf8',
}

3. Fix ERROR: Query failed. 1146 (42S02): Table 'mysql.proc' doesn't exist

The error "Table 'mysql.proc' doesn't exist" typically occurs when using an older version of MySQL Utilities with a newer version of MySQL Server. The mysql.proc table was removed in MySQL 8.0 and replaced with data dictionary tables.
To create the mysql.proc view in a MySQL 8.0 database running in a DDEV container, you can follow these steps:
ddev MySQL -uroot -proot

CREATE OR REPLACE
  ALGORITHM = UNDEFINED
  DEFINER = 'root'@'%'
  SQL SECURITY DEFINER
VIEW mysql.proc AS
SELECT
  ROUTINE_NAME AS name,
  ROUTINE_TYPE AS type,
  ROUTINE_SCHEMA AS db,
  ROUTINE_SCHEMA AS definer,
  CREATED AS created,
  LAST_ALTERED AS modified,
  LAST_ALTERED AS timestamp,
  ROUTINE_COMMENT AS comment,
  CHARACTER_SET_CLIENT AS character_set_client,
  COLLATION_CONNECTION AS collation_connection,
  DATABASE_COLLATION AS database_collation
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA NOT IN ('sys', 'performance_schema',
                             'information_schema', 'mysql');

4. Solving the ERROR: Query failed. 1146 (42S02): Table 'mysql.event' doesn't exist

   CREATE OR REPLACE
     ALGORITHM = UNDEFINED
     DEFINER = 'root'@'%'
     SQL SECURITY DEFINER
   VIEW mysql.event AS
   SELECT
     EVENT_CATALOG AS db,
     EVENT_SCHEMA AS name,
     DEFINER,
     TIME_ZONE,
     EVENT_BODY,
     EVENT_DEFINITION AS body,
     EVENT_TYPE AS type,
     EXECUTE_AT,
     INTERVAL_VALUE,
     INTERVAL_FIELD,
     SQL_MODE,
     STARTS,
     ENDS,
     STATUS,
     ON_COMPLETION,
     CREATED,
     LAST_ALTERED AS modified,
     LAST_EXECUTED,
     EVENT_COMMENT AS comment,
     ORIGINATOR,
     CHARACTER_SET_CLIENT,
     COLLATION_CONNECTION,
     DATABASE_COLLATION
   FROM INFORMATION_SCHEMA.EVENTS;

5. First working results of mysqldiff

mysqldiff --server1=root:[email protected]:32781 --server2=root:[email protected]:32781 db:db_test_cts > .diff_output.sql

# WARNING: Using a password on the command line interface can be insecure.
# server1 on 127.0.0.1: ... connected.
# server2 on 127.0.0.1: ... connected.
# WARNING: Objects in server1.db_test_cts but not in server1.db:
#        TABLE: whitelabel_node__comment
#        TABLE: watchdog
#        TABLE: whitelabel_node_revision__field_tags
#        TABLE: whitelabel_node__field_tags
#        TABLE: whitelabel_node_revision__comment
#        TABLE: flyway_schema_history
Compare failed. One or more differences found.

mysqldiff finally gives a result but expectations was different

Using --force key mysqldiff --server1=root:[email protected]:32781 --server2=root:[email protected]:32781 db:db_test_cts --force > .diff_output.sql

--- `db`.`whitelabel_block_content_revision__body`
+++ `db_test_cts`.`whitelabel_block_content_revision__body`
@@ -1,15 +1,12 @@
 CREATE TABLE `whitelabel_block_content_revision__body` (
-  `bundle` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
-  `deleted` tinyint NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
-  `entity_id` int unsigned NOT NULL COMMENT 'The entity id this data is attached to',
-  `revision_id` int unsigned NOT NULL COMMENT 'The entity revision id this data is attached to',
-  `langcode` varchar(32) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT 'The language code for this data item.',
-  `delta` int unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
+  `bundle` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
+  `deleted` tinyint NOT NULL,
+  `entity_id` int unsigned NOT NULL,
+  `revision_id` int unsigned NOT NULL,
+  `langcode` varchar(32) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
+  `delta` int unsigned NOT NULL,
   `body_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `body_summary` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
   `body_format` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
-  PRIMARY KEY (`entity_id`,`revision_id`,`deleted`,`delta`,`langcode`),
-  KEY `bundle` (`bundle`),
-  KEY `revision_id` (`revision_id`),
-  KEY `body_format` (`body_format`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Revision archive storage for block_content field body.'
+  PRIMARY KEY (`entity_id`,`revision_id`,`deleted`,`delta`,`langcode`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# Comparing `db`.`whitelabel_cache_bootstrap` to `db_test_cts`.`whitelabel_cache_bootstrap`   [FAIL]
# Object definitions differ. (--changes-for=server1)
#

I thought it will generate ALTER statements (See the working solution tested on broken database)