ERROR: Loading local data is disabled – this must be enabled on both the client and server sides

by

If LOCAL capability is disabled, on either the server or client side, a client that attempts to issue a LOAD DATA LOCAL statement receives the following error message:

ERROR 3950 (42000): Loading local data is disabled; this must be
enabled on both the client and server side

I met the same issue when I want to load the text file pet.txt into the pet table following a tutorial of Mysql:https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

After searching online, I fixed it by these steps:

  1. set the global variables by using this command:
mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)
  1. quit current server: 
mysql> quit
Bye
  1. connect to the server with local-infile system variable :
mysql --local-infile=1 -u root -p1

This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side.
To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime.

  1. use your Database and load the file into the table:
mysql> use menagerie
Database changed
mysql> load data local infile '/path/pet.txt' into table pet;
Query OK, 8 rows affected, 7 warnings (0.00 sec)

Does it work?

References:

https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html
https://dev.mysql.com/doc/refman/8.0/en/source-configuration-options.html#option_cmake_enabled_local_infile
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

More Related Contents:

  1. MySQL Cross Server Select Query
  2. How can I correct MySQL Load Error
  3. INSERT INTO SELECT statement with more columns which is not in another table
  4. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  5. Why does MYSQL higher LIMIT offset slow the query down?
  6. Normalization in MYSQL
  7. MySQL load NULL values from CSV data
  8. Is there a REAL performance difference between INT and VARCHAR primary keys?
  9. JOIN queries vs multiple queries
  10. Calculate difference between two datetimes in MySQL
  11. What is the maximum length of data I can put in a BLOB column in MySQL?
  12. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  13. Getting around MySQL “Can’t reopen table” error
  14. Can’t Create Entity Data Model – using MySql and EF6
  15. SQLException: No suitable driver found [duplicate]
  16. In MySQL, can I copy one row to insert into the same table?
  17. MySQL: Cloning a MySQL database on the same MySql instance
  18. MySQL sharding approaches?
  19. MySQL performance: multiple tables vs. index on single table and partitions
  20. Can MySQL convert a stored UTC time to local timezone?
  21. Returning a value even if no result
  22. MySQL Workbench: Error in query (1064): Syntax error near ‘VISIBLE’ at line 1
  23. I can not find my.cnf on my windows computer [duplicate]
  24. Does Order of Fields of Multi-Column Index in MySQL Matter
  25. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  26. Postgres LEFT JOIN with WHERE condition
  27. MySQL – Using COUNT(*) in the WHERE clause
  28. MySQL Integer vs DateTime index
  29. Mysql stored procedure don’t take table name as parameter
  30. Composite Primary Key performance drawback in MySQL

Leave a Comment