MySQL database import and export data error solution example explanation

Exporting Data

Report an error

SHOW VARIABLES LIKE "secure_file_priv";
View the default export directory

mysql> SELECT * FROM student INTO OUTFILE "G:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Workaround

SELECT * FROM student INTO OUTFILE "G:/ProgramData/MySQL/MySQL Server 8.0/Uploads/student.txt";
Query OK, 2 rows affected (0.02 sec)

Data display

Export Results

Importing Data

Report an error

mysql> load data local infile 'G:/ProgramData/MySQL/MySQL Server 8.0/Uploads/student.txt'
 -> into table student(a,b,c);
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

Workaround

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> SET GLOBAL local_infile = true;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

Report an error

mysql> load data local infile 'G:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student.txt'
 -> into table student(id,name,score);
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

Workaround

C:\Users>mysql -uroot -p --local-infile
Log in using this method

Report an error

mysql> load data local infile 'G:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student.txt'
 -> into table student(id,name,score);
ERROR 2 (HY000): File 'G:ProgramDataMySQLMySQL Server 8.0Uploadsstudent.txt' not found (OS errno 2 - No such file or directory)

Workaround

mysql> load data local infile 'G://ProgramData/MySQL/MySQL Server 8.0/Uploads/student.txt'
 -> into table student(id,name,score);
Query OK, 8 rows affected, 2 warnings (0.01 sec)
Records: 10 Deleted: 0 Skipped: 2 Warnings: 2

Results

mysql> select *from student;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | zs | 100.0 |
| 2 | zlh | 100.0 |
| 3 | cyx | 99.1 |
| 4 | xjj | 90.0 |
| 5 | aa | 100.0 |
| 6 | alk | 20.1 |
| 7 | zml | 11.1 |
| 8 | djh | 98.0 |
| 9 | cc | 100.0 |
| 10 | pp | 20.0 |
+------+------+-------+
10 rows in set (0.00 sec)

This is the end of this article about the examples of solving errors when importing and exporting data from MySQL database. For more information about solving errors when importing and exporting data from MySQL database, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

4 solutions to mysql import csv errors
Solve the problem of error 10038 when connecting to MySQL remotely in Navicat
Solution to Navicat Premier remote connection to MySQL error 10038
Analysis on how to solve the problem of Navicat Premium connecting to MySQL 8.0 and reporting error "1251"
How to skip errors in mysql master-slave replication
Problems and solutions of error 08001 when linking to MySQL in IDEA and no table display after successful connection
Mysql table creation foreign key error solution

<<: Docker installation and configuration command code examples

>>: W3C Tutorial (13): W3C WSDL Activities