MySql 8+版本 load data出错的问题

修改config启用load data local

<span style="color: #ff0000;">[</span><span style="color: #ff0000;">client</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
loose</span><span style="color: #808080;">-</span>local<span style="color: #808080;">-</span>infile <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>

错误：MySqlBulkLoader 报 promissions deny

为啥：5.6之前，这代码没问题，5.6之后的版本，默认local = false

咋办：代码加一段就可以了

<span style="color: #0000ff;">var</span> bulk = <span style="color: #0000ff;">new</span><span style="color: #000000;"> MySqlBulkLoader(conn)
                    {
                        CharacterSet </span>= <span style="color: #800000;">"</span><span style="color: #800000;">utf8mb4</span><span style="color: #800000;">"</span><span style="color: #000000;">,
                        <span style="text-decoration: underline;"><em><strong>Local </strong></em></span></span><span style="text-decoration: underline;"><em><strong>= <span style="color: #0000ff; text-decoration: underline;">true</span></strong></em></span><span style="color: #000000;"><span style="text-decoration: underline;"><em><strong>, #加这个，参考官方文档，文末有链接</strong></em></span>
                        FieldTerminator </span>= <span style="color: #800000;">"</span><span style="color: #800000;">,</span><span style="color: #800000;">"</span><span style="color: #000000;">,
                        FieldQuotationCharacter </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">"</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
                        EscapeCharacter </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">"</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
                        LineTerminator </span>= <span style="color: #800000;">"</span><span style="color: #800000;">\r\n</span><span style="color: #800000;">"</span><span style="color: #000000;">,
                        FileName </span>=<span style="color: #000000;"> csvFileItemPath,
                        NumberOfLinesToSkip </span>= <span style="color: #800080;">1</span><span style="color: #000000;">,
                        TableName </span>=<span style="color: #000000;"> cacheTableName
                    }</span>

错误：MySqlBulkLoader 报 The used command is not allowed with this MySQL version；设置了local_infile = 1也没啥用

为啥：猜测mysqlconnection，默认关闭这个功能的。至于为啥全局变量启用load data还是无效，我没有深究，有知道的小伙伴能告诉我最好了 [手动狗头]

咋办：修改链接字符串，加上allowLoadLocalInfile

Data Source=***.***.***.*;Initial Catalog=******;user id=*****;pwd=*******;<span style="text-decoration: underline;"><em><strong>allowLoadLocalInfile=<span style="color: #0000ff; text-decoration: underline;">true</span>;</strong></em></span>

错误：万一手贱，以为是包的问题，把mysql.data.ef6包给升级了，发现咋跑都报错

为啥：mysql.data.ef6迁了，最新版到包mysql.data.entityframework。如果还是有问题（具体啥错我就不贴了），把下面的这个config招呼上

  <span style="color: #0000ff;"><</span><span style="color: #800000;">entityFramework </span><span style="color: #ff0000;">codeConfigurationType</span><span style="color: #0000ff;">="MySql.Data.EntityFramework.MySqlEFConfiguration, MySql.Data.EntityFramework"</span><span style="color: #0000ff;">></span>
    <span style="color: #0000ff;"><</span><span style="color: #800000;">defaultConnectionFactory </span><span style="color: #ff0000;">type</span><span style="color: #0000ff;">="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"</span><span style="color: #0000ff;">></span>
      <span style="color: #0000ff;"><</span><span style="color: #800000;">parameters</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">parameter </span><span style="color: #ff0000;">value</span><span style="color: #0000ff;">="mssqllocaldb"</span> <span style="color: #0000ff;">/></span>
      <span style="color: #0000ff;"></</span><span style="color: #800000;">parameters</span><span style="color: #0000ff;">></span>
    <span style="color: #0000ff;"></</span><span style="color: #800000;">defaultConnectionFactory</span><span style="color: #0000ff;">></span>
    <span style="color: #0000ff;"><</span><span style="color: #800000;">providers</span><span style="color: #0000ff;">></span>
      <span style="color: #0000ff;"><</span><span style="color: #800000;">provider </span><span style="color: #ff0000;">invariantName</span><span style="color: #0000ff;">="System.Data.SqlClient"</span><span style="color: #ff0000;"> type</span><span style="color: #0000ff;">="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"</span> <span style="color: #0000ff;">/></span>
      <span style="color: #0000ff;"><</span><span style="color: #800000;">provider </span><span style="color: #ff0000;">invariantName</span><span style="color: #0000ff;">="MySql.Data.MySqlClient"</span><span style="color: #ff0000;"> type</span><span style="color: #0000ff;">="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework, Version=8.0.18.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"</span> <span style="color: #0000ff;">/></span>
    <span style="color: #0000ff;"></</span><span style="color: #800000;">providers</span><span style="color: #0000ff;">></span>
  <span style="color: #0000ff;"></</span><span style="color: #800000;">entityFramework</span><span style="color: #0000ff;">></span>

参考：

Mysql8以上版本默认禁用load data命令官宣

https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html

LoadData命令相关

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

命令报错：ERROR 1148: The used command is not allowed with this MySQL version

https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version

MySqlBulkLoader官方实例

https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html

MySql 8+版本 load data出错的问题

标签：cti   net   mss   dbconnect   mis   com   file   filename   this