Pomelo.EntityFrameworkCore.MySql
Pomelo.EntityFrameworkCore.MySql copied to clipboard
scaffold MariaDB with MDEV-32500
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.