Error : loading local data is disabled in Asp.Net and MySql

Dung Do Tien May 09 2022 155

Hello guys, I have created a console application in Asp.net Framework 4.7.2. This application will help me detect and collect data from other databases. This app will scan every 30 mins and get the top 1000 records to insert into a table.

I'm using MySqlBulkCopy to help insert data faster. like this:

 public async Task<bool> BulkInserTokenSpam(List<BookEntities> tokens)
{
    try
    {
        if (tokens == null || !tokens.Any()) return false;

        //1. Create datatable
        DataTable dataTable = new DataTable();
        dataTable.Columns.Add(new DataColumn("bookname", typeof(string)));
        dataTable.Columns.Add(new DataColumn("author", typeof(string)));
        dataTable.Columns.Add(new DataColumn("engname", typeof(string)));
        dataTable.Columns.Add(new DataColumn("soldcount", typeof(int)));
        dataTable.Columns.Add(new DataColumn("reason", typeof(int)));
        dataTable.Columns.Add(new DataColumn("status", typeof(int)));
        dataTable.Columns.Add(new DataColumn("createddate", typeof(decimal)));

        foreach (var token in tokens)
        {
            DataRow dr = dataTable.NewRow();
            dr["bookname"] = token.BookName;
            dr["Author"] = token.Author;
            dr["engname"] = token.EnsName;
            dr["soldcount"] = token.SoldCount;
            dr["createddate"] = Utils.DateTimeToUnix(DateTime.Now);
            dataTable.Rows.Add(dr);
        }

        // 2. Transfer to DB
        string connectionString = MySqlConnectorHelper.GetConnectionString(ConnectionEntity.DBPosition.BulkCopy);
        var connection = new MySqlConnector.MySqlConnection(connectionString);
        connection.Open();
        var bulkCopy = new MySqlBulkCopy(connection);
        bulkCopy.DestinationTableName = "tbl_books";
        var result = await bulkCopy.WriteToServerAsync(dataTable);
        if (result.Warnings.Count != 0)
        {
            return false;
        }

    }
    catch (Exception ex)
    {
        throw ex;
    }
    return true;
}

But when running this method my application threw Loading local data is disabled; this must be enabled on both the client and server sides.

 Loading local data is disabled; this must be enabled on both the client and server sides
   at MySqlConnector.Core.ServerSession.<ReceiveReplyAsyncAwaited>d__93.MoveNext() in /_/src/MySqlConnector/Core/ServerSession.cs:line 959
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__2.MoveNext() in /_/src/MySqlConnector/Core/ResultSet.cs:line 43
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 129
   at MySqlConnector.MySqlDataReader.<CreateAsync>d__106.MoveNext() in /_/src/MySqlConnector/MySqlDataReader.cs:line 480
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext() in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext() in /_/src/MySqlConnector/MySqlCommand.cs:line 296
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlBulkLoader.<LoadAsync>d__73.MoveNext() in /_/src/MySqlConnector/MySqlBulkLoader.cs:line 213
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at MySqlConnector.MySqlBulkCopy.<WriteToServerAsync>d__27.MoveNext() in /_/src/MySqlConnector/MySqlBulkCopy.cs:line 329
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlBulkCopy.<WriteToServerAsync>d__22.MoveNext() in /_/src/MySqlConnector/MySqlBulkCopy.cs:line 137
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() 

Below is my connection string:

 Server=127.0.0.1;Port=3306;Database=spam_token_mamager_4;User=root;Password=123456

I'm using VS 2020 and Windows 11.

Thank you for any idea.

Have 1 answer(s) found.
  • s

    sivakumar vadamalai May 09 2022

    I got the same error in the MySQL database. You can follow 2 steps below to solve your issue:

    Step 1: Add more ;AllowLoadLocalInfile=True at the end of your connection string, like this:

     Server=127.0.0.1;Port=3306;Database=BookManager;User=root;Password=123456;AllowLoadLocalInfile=True

    Step 2: Run the command below in your MySql database 

     set global local_infile=true;
    show global variables like 'local_infile';

    It's is okay if it returns local_infile : ON

     Now you can test again and see if your issue is solved.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close