MySQL database import and export data error solution example explanation

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

Recommend

Binary Type Operations in MySQL

This article mainly introduces the binary type op...

vue-cropper component realizes image cutting and uploading

This article shares the specific code of the vue-...

User experience of portal website redesign

<br />From the launch of NetEase's new h...

Details on using JS array methods some, every and find

Table of contents 1. some 2. every 3. find 1. som...

MySQL 8.0 New Features - Introduction to Check Constraints

Table of contents Preface Check Constraints Creat...

SELinux Getting Started

Back in the Kernel 2.6 era, a new security system...

jQuery plugin to achieve carousel effect

A jQuery plugin every day - jQuery plugin to impl...

vue-cli configuration uses Vuex's full process record

Table of contents Preface Installation and Usage ...

MySQL 5.7.18 installation and configuration tutorial under Windows

This article shares the installation and configur...

Steps to package and deploy the Vue project to the Apache server

In the development environment, the vue project i...

CSS3 to achieve timeline effects

Recently, when I turned on my computer, I saw tha...

How to deploy FastDFS in Docker

Install fastdfs on Docker Mount directory -v /e/f...