Yii2: PHP Notice – yii\base\ErrorException: Undefined index: constraint_name in MySQL 8.0.21

[PitBult]

PHP Notice – yii\base\ErrorException

Undefined index: constraint_name

1. in /vendor/yiisoft/yii2/db/mysql/Schema.php (line 394)
   $constraints[$row['constraint_name']]['referenced_table_name'] = $row['referenced_table_name'];

SELECT
    kcu.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
    (
        kcu.constraint_catalog = rc.constraint_catalog OR
        (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
    ) AND
    kcu.constraint_schema = rc.constraint_schema AND
    kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = 'log' AND kcu.table_name = 'log'

MySQL 8.0.21 fields name:
CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME

MySQL < 8.0.21 fields name:
constraint_name, column_name, referenced_table_name, referenced_column_name

How to fix?

Add to common/config/main-local.php:
'attributes' => [PDO::ATTR_CASE => PDO::CASE_LOWER],
Example:

 'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=db_name',
            'username' => 'username',
            'password' => '******',
            'attributes' => [PDO::ATTR_CASE => PDO::CASE_LOWER],
        ],

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.36
| PHP version | 7.4.7
| MySQL | 8.0.21
| Operating system | Ubuntu 18

[samdark]

So the query itself works but fields are returned with uppercase names?

[alex-code]

A global lowercase config option is a bit risky as users may already have case sensitive column names.

Passing the $row through this function array_change_key_case before accessing keys should work.

[PitBult]

So the query itself works but fields are returned with uppercase names?

Absolutely right!
The query works but fields are returned with uppercase names...

[DavidOliver]

I'm running Craft CMS (which uses Yii) on Ubuntu 20.04 and get a hard error/exception for this after updating to MySQL 8.0.21.

As per @alex-code 's comment, I'm using this in Schema.php to get round it in development for now:

foreach ($rows as $row) {
    $row = array_change_key_case($row, CASE_LOWER);

[omsi668]

Hello,

This fix :
'attributes' => [PDO::ATTR_CASE => PDO::CASE_LOWER],
creates a lot of regressions on my application (which is probably not following all the best practices but still)

However the array_change_key_case by @DavidOliver seems to fix it without any regressions.
Hoping that'll help

[samdark]

@alex-code that sounds like a good solution for schema issue. Would you like to prepare a pull request?

[darkdef]

IMHO, need change sql query to:

SELECT
    kcu.CONSTRAINT_NAME as constraint_name,
    kcu.COLUMN_NAME as column_name,
    kcu.REFERENCED_TABLE_NAME as referenced_table_name,
    kcu.REFERENCED_COLUMN_NAME as referenced_column_name
    ...

similarly function loadTableConstraints (in Schema.php) etc

[samdark]

What @darkdef posted is preferred if it works well in both version 8 and early versions.

[alex-code]

If no one else wants to I can try and get a PR done tomorrow.

[samdark]

@darkdef is preparing something.

[golam-sorwar]

I'm running Craft CMS (which uses Yii) on Ubuntu 20.04 and get a hard error/exception for this after updating to MySQL 8.0.21.

As per @alex-code 's comment, I'm using this in Schema.php to get round it in development for now:

foreach ($rows as $row) {
    $row = array_change_key_case($row, CASE_LOWER);

@DavidOliver Thanks 😊 , It’s working for me .

[DavidOliver]

@golam-sorwar There's now a released Craft update that fixes this, so you shouldn't need any patching.

[turalus]

I'm having the same issue. Adding'attributes' => [PDO::ATTR_CASE => PDO::CASE_LOWER], to the config messes up everything. what is the fix for this issue?

[turalus]

I don't know how this issue is related to other extensions but https://github.com/yii2tech/ar-variation this one stopped working