Could not find type mapping for column with data type 'datetime /* mariadb-5.3 */'. Skipping column.

[SimpleSandman]

The issue

When scaffolding a view with a CAST to DATETIME, I'm getting a mapping error.

Steps to reproduce

CREATE DATABASE test
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

-- 
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE test;

--
-- Create table `item_data`
--
CREATE TABLE item_data
(
    id INT(11) NOT NULL
  , item_category BIGINT(20) NOT NULL
  , group_id BIGINT(20) NOT NULL
  , effect_type_1 BIGINT(20) NOT NULL
  , effect_target_1 BIGINT(20) NOT NULL
  , effect_value_1 BIGINT(20) NOT NULL
  , effect_type_2 BIGINT(20) NOT NULL
  , effect_target_2 BIGINT(20) NOT NULL
  , effect_value_2 BIGINT(20) NOT NULL
  , add_value_1 BIGINT(20) NOT NULL
  , add_value_2 BIGINT(20) NOT NULL
  , add_value_3 BIGINT(20) NOT NULL
  , limit_num BIGINT(20) NOT NULL
  , sort BIGINT(20) NOT NULL
  , rare BIGINT(20) NOT NULL
  , enable_request BIGINT(20) NOT NULL
  , request_reward BIGINT(20) NOT NULL
  , item_place_id BIGINT(20) NOT NULL
  , start_date TEXT NOT NULL
  , end_date TEXT NOT NULL
  , PRIMARY KEY (id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 212,
CHARACTER SET utf8,
COLLATE utf8_unicode_ci;

-- 
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE test;

--
-- Create view `basic_item_data_info`
--
CREATE
DEFINER = 'root'@'localhost'
VIEW basic_item_data_info
AS
SELECT `item`.`id` AS `id`
     , `item`.`item_category` AS `item_category`
     , `item`.`group_id` AS `group_id`
     , `item`.`item_place_id` AS `item_place_id`
     , CAST(`item`.`start_date` AS DATETIME) AS `start_date` -- line in question
     , CAST(`item`.`end_date` AS DATETIME) AS `end_date` -- line in question
FROM `item_data` `item`;

USE test;

INSERT INTO item_data (id, item_category, group_id, effect_type_1, effect_target_1, effect_value_1, effect_type_2, effect_target_2, effect_value_2, add_value_1, add_value_2, add_value_3, limit_num, sort, rare, enable_request, request_reward, item_place_id, start_date, end_date) 
VALUES (1, 11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 9999, 2, 0, 1, 5, 1, '2017/1/1 0:00:00', '2050/1/1 0:00:00');

Package Manager Console Command

Scaffold-DbContext 'User Id=root;Password=sa;Host=localhost;Database=test;Character Set=utf8' Pomelo.EntityFrameworkCore.MySql -OutputDir Models -ContextDir Context

Error message

Using ServerVersion '10.5.9-mariadb'.
Could not find type mapping for column 'basic_item_data_info.start_date' with data type 'datetime /* mariadb-5.3 */'. Skipping column.
Could not find type mapping for column 'basic_item_data_info.end_date' with data type 'datetime /* mariadb-5.3 */'. Skipping column.

Further technical details

MariaDB version: 10.5.9
Operating system: Windows 10 Pro 2004 (19041.928)
Pomelo.EntityFrameworkCore.MySql version: 5.0.0
Microsoft.AspNetCore.App version: .NET 5

Console App Solution

Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio Version 16
VisualStudioVersion = 16.0.31229.75
MinimumVisualStudioVersion = 10.0.40219.1
Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "TestPomeloScaffold", "TestPomeloScaffold\TestPomeloScaffold.csproj", "{18BB78DC-DE72-4CC6-8020-1DFCA6BD20A5}"
EndProject
Global
	GlobalSection(SolutionConfigurationPlatforms) = preSolution
		Debug|Any CPU = Debug|Any CPU
		Release|Any CPU = Release|Any CPU
	EndGlobalSection
	GlobalSection(ProjectConfigurationPlatforms) = postSolution
		{18BB78DC-DE72-4CC6-8020-1DFCA6BD20A5}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
		{18BB78DC-DE72-4CC6-8020-1DFCA6BD20A5}.Debug|Any CPU.Build.0 = Debug|Any CPU
		{18BB78DC-DE72-4CC6-8020-1DFCA6BD20A5}.Release|Any CPU.ActiveCfg = Release|Any CPU
		{18BB78DC-DE72-4CC6-8020-1DFCA6BD20A5}.Release|Any CPU.Build.0 = Release|Any CPU
	EndGlobalSection
	GlobalSection(SolutionProperties) = preSolution
		HideSolutionNode = FALSE
	EndGlobalSection
	GlobalSection(ExtensibilityGlobals) = postSolution
		SolutionGuid = {01C4C5CB-A32A-4DAA-A024-690C749B4FD2}
	EndGlobalSection
EndGlobal

Console App Project

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.6">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="MySqlConnector" Version="1.3.8" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="5.0.0" />
  </ItemGroup>

</Project>

[lauxjpn]

@SimpleSandman Thanks for the high quality report! Those help fixing issues quite a lot.

---

Unrelated to the actual issue here, we recommend explicitly using utf8mb4 (and utf8mb4_general_ci) over utf8. utf8 is currently still an alias for utf8mb3, which is a proprietary UTF-8 implementation by MySQL/MariaDB, that is not standard conform and is going to be removed in a future release. Before that happens, utf8 will become an alias for utf8mb4.

See 10.9.3 The utf8 Character Set (Alias for utf8mb3) for more information regarding MySQL, and Unicode for the situation regarding MariaDB.

[SimpleSandman]

Thank you for the compliment! I'm super happy this report allowed you to take care of this issue.

Also, I'm pretty new to the ways of MySQL/MariaDB, but I knew I needed UTF-8 to handle Japanese characters. So I truly appreciate you mentioning that I needed to be using utf8mb4. I will make those changes into my database immediately!

[lauxjpn]

The fix will be part of 5.0.1. Until then, just use the latest 5.0.x compatible nightly build.