Yii2 ActiveRecord / Schema does not work with MySQL 8.0.21 any more

[meyergru]

What steps will reproduce the problem?

Using any ActiveRecord with a model that has dependencies under MySQL 8.0.21. This was a recent secirity update to Ubuntu 20.04.

What is the expected result?

It should work.

What do you get instead?

I receive an exception like this:

2020-07-31 09:24:46 [134.101.220.23][-][suetptivhkpdscnp5ope59s210][error][yii\base\ ErrorException:8] yii\base\ErrorException: Undefined index: constraint_name in /home /name2ip/dyndns/_protected/vendor/yiisoft/yii2/db/mysql/Schema.php:394
Stack trace:
#0 /home/name2ip/dyndns/_protected/vendor/yiisoft/yii2/db/mysql/Schema.php(394): yii \base\ErrorHandler->handleError()
#1 /home/name2ip/dyndns/_protected/vendor/yiisoft/yii2/db/mysql/Schema.php(126): yii \db\mysql\Schema->findConstraints()
#2 /home/name2ip/dyndns/_protected/vendor/yiisoft/yii2/db/Schema.php(757): yii\db\my sql\Schema->loadTableSchema()
#3 /home/name2ip/dyndns/_protected/vendor/yiisoft/yii2/db/Schema.php(193): yii\db\Sc hema->getTableMetadata()
#4 /home/name2ip/dyndns/_protected/vendor/yiisoft/yii2/db/ActiveRecord.php(435): yii \db\Schema->getTableSchema()

Additional info

Q	A
Yii version	2.0.36
PHP version	7.4.3
Operating system	Ubuntu 20.04

Analysing the problem, I found that in Schema.pm, function findConstraints(), the array index is used like this after fetching the rows from INFORMATION_SCHEMA:

$row['constraint_name']

Actually, this has always worked up to MySQL 8.0.20. Now, the returned column name is 'CONSTRAINT_NAME', so adressing the hash by the lower-case name throws an exception. After I changed all those references to upper case, it works again. However, this should be fixed in a case-independent way in Yii2. Also, there are other places that are suspicious, like loadTableConstraints().

[s1lver]

look #18171

[meyergru]

Thanks for the pointer, that is a good temporary fix. To fix that independently, one could just do:

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`
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 = :tableName AND kcu.table_name = :tableName1

Or have [PDO::ATTR_CASE => PDO::CASE_LOWER] as the default.

[s1lver]

Mysql Ver 8.0.21
Yii 2.0.36

Solution in Yii 2.0.36 don`t work for me. Only adding code solves the problem:

$row = array_change_key_case($row, CASE_LOWER);

[meyergru]

I use 2.0.36 and the [PDO::ATTR_CASE => PDO::CASE_LOWER] works.

The fix with the explicit column names was commited on Jul 15th and 2.0.36 was released Jul 7th. The fix will be applied only in 2.0.37. Did you try to apply it manually? I did and it works for me.

[paskuale75]

When will the next version be released ? Any news ? Thanks

[samdark]

I'm back from short vacation. Going to work on new release this week.

[haroldiedema]

I use 2.0.36 and the [PDO::ATTR_CASE => PDO::CASE_LOWER] works.

The fix with the explicit column names was commited on Jul 15th and 2.0.36 was released Jul 7th. The fix will be applied only in 2.0.37. Did you try to apply it manually? I did and it works for me.

Sorry @meyergru , but configuring a global PDO setting to transform all columns (not just this) to lowercase causes a lot of regression / bugs in existing code. Column names can have upper- and lower-case characters, and can be used as such in PHP (since array keys are case-sensitive as well).

Configuring PDO to return all attributes as lower-case values by setting [PDO::ATTR_CASE => PDO::CASE_LOWER] should not be considered a fix!