Unknown column 'c.generation_expression' in 'field list' when connecting to MySQL database

[joshdean-ts]

trafficstars

I'm attempting to connect to a MySQL database hosted in RDS, but am running into issues actually running queries against it. I know the connection is working because I can enumerate the tables, but can't retrieve columns/individuals/etc. Issue happens with both Mysql.data and MySqlConnector, both .NET 5 and Core 3.1:

Minimal example:

open System
open System.Data
open FSharp.Data.Sql

[<Literal>]
let connString = "Server=<myserverurl>;Database=TS_Data;User=username;Password=password"
[<Literal>]
let dbVendor = Common.DatabaseProviderTypes.MYSQL
[<Literal>]
let resPath = __SOURCE_DIRECTORY__ + @"\libraries" 
[<Literal>]
let indivAmount = 1000
[<Literal>]
let useOptTypes = true

type sql = SqlDataProvider<dbVendor,connString,ResolutionPath = resPath,IndividualsAmount = indivAmount,UseOptionTypes = useOptTypes,Owner = "TS_Data">
let tsData = sql.GetDataContext().TsData

let x =
    query {
        for employee in tsData.Employees do
        select (employee.Email)
    }

Results in the following error: The type 'TS_Data.EMPLOYEESEntity' does not define the field, constructor or member 'Email'

If I change the select() statement to select all fields then I don't receive any intellisense errors, but during build I receive the following error: C:\Users\joshd\source\repos\DBTestConnector\DBTestConnector\Program.fs(24,25): error FS3021: Unexpected exception from provided type 'FSharp.Data.Sql.SqlDataProvider,DatabaseVendor="3",ConnectionString="Server=url;Database=TS_Data;User=username;Password=password",ResolutionPath="C:\\Users\\joshd\\source\\repos\\DBTestConnector\\DBTestConnector\\libraries",UseOptionTypes="True",Owner="TS_Data"+dataContext+TS_DataSchema+TS_Data.EMPLOYEES' member 'GetMethods': The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Unknown column 'c.generation_expression' in 'field list'

Package Versions: FSharp.Core: 5.0.0.0 MySql.Data 8.0.23 MySqlConnector 1.3.1 SQLProvider 1.2.1

[bdkoepke]

Try using SQLProvider version 1.1.91. I think a regression was introduced in build 1.1.92 that breaks the MySQL support on dotnet core.

I have the same issue as you.

[bdkoepke]

I found the line where this is happening.

SELECT DISTINCTROW c.COLUMN_NAME,c.DATA_TYPE, c.character_maximum_length, c.numeric_precision, c.is_nullable ,CASE WHEN ku.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType, c.COLUMN_TYPE, EXTRA, COLUMN_DEFAULT, length(c.generation_expression) > 0 FROM INFORMATION_SCHEMA.COLUMNS c left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku on (c.TABLE_CATALOG = ku.TABLE_CATALOG OR ku.TABLE_CATALOG IS NULL) AND c.TABLE_SCHEMA = ku.TABLE_SCHEMA AND c.TABLE_NAME = ku.TABLE_NAME AND c.COLUMN_NAME = ku.COLUMN_NAME and ku.CONSTRAINT_NAME='PRIMARY' WHERE c.TABLE_SCHEMA = @schema AND c.TABLE_NAME = @table

It's line 544 in Providers.MySql.fs.

What version of MySQL are you running? I'm on 5.6 and I think 5.6 doesn't support generation expressions. I think this is a more limited issue to MySql versions <5.7.

[bdkoepke]

Confirmed, migrating from 5.6 to 5.7 fixes the issue. We need a way to determine whether generation_expression exists. I couldn't determine whether the schemaCache could be used in the 'match' statement. Seems like it might add items dynamically, if information schema is always cached then we could match on that efficiently.