KingbaseESV008R006 数据库 在MySQL兼容模式下 同步表结构报错

[yuanwsh1]

问题描述及重现代码：
fsql.CodeFirst.SyncStructure(tables)
安装的Nuget包
FreeSql.Provider.KingbaseES 3.5.105.0
运行环境: .net framework 4.8
数据库版本: KingbaseESV008R006
SyncStructure内执行的SQL：
select d.description
from sys_class a
inner join sys_namespace b on b.oid = a.relnamespace
left join sys_description d on d.objoid = a.oid and objsubid = 0
where upper(b.nspname) not in ('SYS_CATALOG', 'INFORMATION_SCHEMA', 'TOPOLOGY', 'SYSAUDIT', 'SYSLOGICAL', 'SYS_TEMP_1', 'SYS_TOAST', 'SYS_TOAST_TEMP_1', 'XLOG_RECORD_READ') and a.relkind in ('r') and b.nspname = 'PUBLIC' and a.relname = 'TStationVersion'
and upper(b.nspname || '.' || a.relname) not in ('PUBLIC.GEOGRAPHY_COLUMNS','PUBLIC.GEOMETRY_COLUMNS','PUBLIC.RASTER_COLUMNS','PUBLIC.RASTER_OVERVIEWS')
错误信息: function upper(boolean) is not unique
初步猜测 mysql兼容模式下， upper(b.nspname || '.' || a.relname) 中的||会被视为OR而不是连接串

[2881099]

默认是用 pgsql 模式，如果是 mysql 模式，可以用 FreeSql.Provider.Custom.dll

https://freesql.net/guide/freesql-provider-custom.html

[NoobLCH]

改用Provider.CustomMySql连接后进行同步表结构还是会有其它问题:
报错: System.Exception: 42601: syntax error at or near "Engine" ---> Kdbndp.KingbaseException: 42601: syntax error at or near "Engine"

freeSql = new FreeSql.FreeSqlBuilder()
    .UseMonitorCommand(cmd => G.AddEvent(cmd.CommandText))
    .UseConnectionFactory(FreeSql.DataType.CustomMySql, () => new KdbndpConnection(ConnectString))
    .Build();

freeSql.SetDbProviderFactory(KdbndpFactory.Instance);

freeSql.CodeFirst.SyncStructure(GetTables());

SyncStructure出错SQL
CREATE TABLE IF NOT EXISTS atwf.TStationVersion (
Id BIGINT(20) AUTO_INCREMENT,
SoftDelete BIT(1) NOT NULL,
StationNumber VARCHAR(255) NOT NULL,
StationIP VARCHAR(255),
VersionValue BIGINT(20) NOT NULL,
VersionType VARCHAR(255) NOT NULL,
UpdateTime DATETIME(3) NOT NULL,
PRIMARY KEY (Id),
UNIQUE INDEX uk_stationVersion_index(StationNumber, VersionType)
) Engine=InnoDB

[yuanwsh1]

@2881099 如上 使用custom建表还是报错

[2881099]

Aop.CommandBefore replace一下

[NoobLCH]

System.Exception: 3F000: schema or package "atwf" does not exist ---> Kdbndp.KingbaseException: 3F000: schema or package "atwf" does not exist
这里得把atwf.去了才能建起来

SQL语句:
CREATE TABLE IF NOT EXISTS atwf.TStationVersion (
Id BIGINT(20) AUTO_INCREMENT,
SoftDelete BIT(1) NOT NULL,
StationNumber VARCHAR(255) NOT NULL,
StationIP VARCHAR(255),
VersionValue BIGINT(20) NOT NULL,
VersionType VARCHAR(255) NOT NULL,
UpdateTime DATETIME(3) NOT NULL,
PRIMARY KEY (Id),
UNIQUE INDEX uk_stationVersion_index(StationNumber, VersionType)
)

看上去直接用Provider.CustomMysql生成的SQL语句在Kingbase的mysql兼容模式下问题可能会有多处

[2881099]

他可能对 ddl 不是兼容，只支持 crud

[2881099]

v3.5.106

[NoobLCH]

v3.5.106测试对上述mysql兼容模式问题均没有改善；使用.UseConnectionString(FreeSql.DataType.KingbaseES, ConnectString) 仍旧报function upper(boolean) is not unique；使用.UseConnectionFactory(FreeSql.DataType.CustomMySql, () => new KdbndpConnection(ConnectString))仍存在 syntax error at or near "Engine" / schema or package "atwf" does not exist

另外似乎3.5.106里Kingbase即使使用UseConnectionString去构建也需要SetDbProviderFactory了？之前是使用UseConnectionFactory构建的需要去SetDbProviderFactory

目前本地通过Aop.CommandBefore和Aop.ConfigEntityProperty规避了出现的问题，可以用了

public static IFreeSql KingBaseInstance(IFreeSql freeSql, string ConnectString)
{
    if (freeSql == null)
    {
        freeSql = new FreeSql.FreeSqlBuilder()
            .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText))
            .UseConnectionString(FreeSql.DataType.KingbaseES, ConnectString)
            .Build();

        freeSql.SetDbProviderFactory(KdbndpFactory.Instance);

        string mode = freeSql.Ado.ExecuteScalar("show database_mode;").ToString()?.Trim().ToLower();

        if (mode == "mysql")
        {
            freeSql.Dispose();
            freeSql = null;

            freeSql = new FreeSql.FreeSqlBuilder()
            .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText))
            .UseConnectionFactory(FreeSql.DataType.CustomMySql, () => new KdbndpConnection(ConnectString))
            .Build();
            freeSql.SetDbProviderFactory(KdbndpFactory.Instance);

            freeSql.Aop.CommandBefore += Ksql_MySqlMode_Aop_CommandBefore;
            freeSql.Aop.ConfigEntityProperty += Ksql_MySqlMode_Aop_ConfigEntityProperty;
        }
        else
            freeSql.Aop.ConfigEntityProperty += Psql_Aop_ConfigEntityProperty;
    }

    return freeSql;
}

static void Psql_Aop_ConfigEntityProperty(object sender, ConfigEntityPropertyEventArgs e)
{
    if (e.Property.PropertyType.IsEnum)
        e.ModifyResult.MapType = typeof(int);
}
static void Ksql_MySqlMode_Aop_ConfigEntityProperty(object sender, ConfigEntityPropertyEventArgs e)
{
    if (e.Property.PropertyType.IsEnum)
        e.ModifyResult.MapType = typeof(int);
    if (e.Property.PropertyType == typeof(bool))
        e.ModifyResult.MapType = typeof(int);
    if (e.Property.PropertyType == typeof(DateTime))
        e.ModifyResult.DbType = "timestamptz";
}
static void Ksql_MySqlMode_Aop_CommandBefore(object sender, CommandBeforeEventArgs e)
{
    if (e.Command.CommandText.Contains("Engine=InnoDB"))
        e.Command.CommandText = e.Command.CommandText.Replace("Engine=InnoDB", "");
    if (e.Command.CommandText.Contains("`atwf`"))
        e.Command.CommandText = e.Command.CommandText.Replace("`atwf`.", "");
}

[2881099]

之前了解的信息比较碎片化，看了上面代码后突然想到，如果用 FreeSql.Provider.MySqlConnector 连接会怎样？

[NoobLCH]

使用MySqlConnector似乎连不上金仓MySQL模式的数据库（使用CustomMySQL就可以），会Connect Timeout expired
MySqlException: The Command Timeout expired before the operation completed.