Error : loading local data is disabled in Asp.Net and MySql
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.
-
s0
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.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.