1.25.0 MySQL import collation changes lead to illegal mix of collations #8130
Description
Preliminary checklist
- I am using the latest stable version of DDEV (see upgrade guide)
- I have searched existing issues
Expected Behavior
Database imports should not change the collation of tables. Alternatively, they should use the server's configured default.
Collation affects the behavior of the database and it should be the same in DDEV as it is in production and other environments.
Actual Behavior
Tables with different collations can not be joined, so applications expecting a particular collation can crash.
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' in /var/www/html/index.php:31 Stack trace: #0 /var/www/html/index.php(31): PDO->query() #1 {main} thrown in /var/www/html/index.php on line 31
Steps To Reproduce
Here's an example, where a database dump including the default MySQL collation utf8mb4_0900_ai_ci does not work with tables created after because
ddev import-dbconverts the table toutf8mb4_unicode_ci.- DDEV sets the server's default collation to
utf8mb4_unicode_520_ci.
Here's a minimal DDEV project along with an example database and example script that queries the two tables.
#!/usr/bin/env bash
set -euo pipefail
set -x
DIR="default-collations.d"
if [[ -d "${DIR}" ]]; then
echo >&2 "${DIR} already exists."
exit 1;
fi
mkdir "${DIR}"
cd "${DIR}"
ddev config --project-type=php --database=mysql:8.0
cat > foo_table.sql <<EOF
-- Create Foo table with one string field
CREATE TABLE Foo (
foo VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Insert sample rows with random values
INSERT INTO Foo (foo) VALUES
('apple'),
('banana');
EOF
cat > index.php <<EOF
<?php
// Connect to database using DDEV defaults
try {
\$pdo = new PDO(
'mysql:host=db;dbname=db',
'db',
'db',
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
} catch (PDOException \$e) {
die("Connection failed: " . \$e->getMessage());
}
// Create temporary table
\$pdo->exec("
CREATE TEMPORARY TABLE api_data (
foo VARCHAR(255),
bar VARCHAR(255)
)
");
// Insert two rows
\$pdo->exec("
INSERT INTO api_data (foo, bar) VALUES
('apple', 'some random value'),
('passionfruit', 'another random value')
");
// Join api_data with Foo table and count results
\$stmt = \$pdo->query("
SELECT COUNT(*) as count
FROM api_data
JOIN Foo ON api_data.foo = Foo.foo
");
\$result = \$stmt->fetch(PDO::FETCH_ASSOC);
echo "Number of results: " . \$result['count'] . "\n";
EOF
ddev start
ddev import-db --file=foo_table.sql
ddev launch
Anything else?
No response