scaffold MariaDB with MDEV-32500

[eworm-de]

Steps to reproduce

Let's create a test case:

CREATE DATABASE a;
USE a;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`),
  CONSTRAINT `constraint` FOREIGN KEY (`parent`) REFERENCES `test` (`id`)
) ENGINE=InnoDB;

CREATE DATABASE b;
USE b;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`),
  CONSTRAINT `constraint` FOREIGN KEY (`parent`) REFERENCES `test` (`id`)
) ENGINE=InnoDB;

CREATE USER 'test'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON a.* TO 'test'@'%';
GRANT SELECT, DELETE ON b.* TO 'test'@'%';

Scaffolding this with user test fails.

The issue

Scaffolding a.test fails with something like...

System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
   at MySqlConnector.Core.Row.GetString(Int32 ordinal) in /_/src/MySqlConnector/Core/Row.cs:line 355
   at MySqlConnector.MySqlDataReader.GetString(Int32 ordinal) in /_/src/MySqlConnector/MySqlDataReader.cs:line 293
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.GetConstraints(DbConnection connection, IReadOnlyList`1 tables)
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.GetTables(DbConnection connection, Func`3 filter, String defaultCharSet, String defaultCollation)
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.GetDatabase(DbConnection connection, DatabaseModelFactoryOptions options)
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Unable to cast object of type 'System.DBNull' to type 'System.String'.

... where it succeeds for b.test.

Further technical details

We have traced that to a change in MariaDB, referenced with MDEV-32500 Information schema leaks table names and structure to unauthorized users.

Depending on user's actual permissions the results in information_schema now change. The primary change (and the cause for our trouble) is column REFERENCED_TABLE_NAME in table REFERENTIAL_CONSTRAINTS being nullable now. This query is similar to what you use...

SELECT 
    `TABLE_SCHEMA`,
    `TABLE_NAME`,
    `CONSTRAINT_NAME`,
    (SELECT 
            `DELETE_RULE`
        FROM
            `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
        WHERE
            `REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_NAME` = `KEY_COLUMN_USAGE`.`CONSTRAINT_NAME`
                AND `REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_SCHEMA` = `KEY_COLUMN_USAGE`.`CONSTRAINT_SCHEMA`) AS `DELETE_RULE`
FROM
    `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE
    `TABLE_SCHEMA` IN ('a' , 'b')
        AND `TABLE_NAME` = 'test'
        AND `CONSTRAINT_NAME` <> 'PRIMARY'
        AND `REFERENCED_TABLE_NAME` IS NOT NULL;

... and it results in...

+--------------+------------+-----------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | DELETE_RULE |
+--------------+------------+-----------------+-------------+
| a            | test       | constraint      | NULL        |
| b            | test       | constraint      | RESTRICT    |
+--------------+------------+-----------------+-------------+
2 rows in set (0,018 sec)

... where the NULL is what confuses your code.

[eworm-de]

BTW, the change was committed before mariadb-10.4.32, and was merged into all supported releases branches:

% git tag --contains=547dfc0e01bb5acbe070728706afb8a116b100e2          
mariadb-10.10.7
mariadb-10.11.6
mariadb-10.11.7
mariadb-10.11.8
mariadb-10.4.32
mariadb-10.4.33
mariadb-10.4.34
mariadb-10.5.23
mariadb-10.5.24
mariadb-10.5.25
mariadb-10.6.16
mariadb-10.6.17
mariadb-10.6.18
mariadb-11.0.4
mariadb-11.0.5
mariadb-11.0.6
mariadb-11.1.3
mariadb-11.1.4
mariadb-11.1.5
mariadb-11.2.2
mariadb-11.2.3
mariadb-11.2.4
mariadb-11.3.1
mariadb-11.3.2
mariadb-11.4.1
mariadb-11.4.2
mariadb-11.5.1