Tag Archives: mysql

How to Solve MySQL Secure file priv error

Problem description

When I want to export data in mysql, I find an error. The error contents are as follows

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Principle interpretation

secure file priv is a system variable in MySQL that controls the operation of importing and exporting data, such as load data or select... Into output or load_FILE()

Use show global variables like 'secure_File% ' view the set value of this variable, which may have the following three values

  1. no value. That is, this variable has no effect

2﹥ point to a path. That is, import and export can only use this path

three ⃣ ️ NULL。 Import and export operations are prohibited

The default value of this system variable is different for different platforms

terms of settlement

Environment: MySQL 8.0.26 (installed with homebrew ), macbook pro 2020 Intel Edition

one ⃣ Create corresponding files, ~ /.My.CNF , and use your customary text editor. I use nano , which contains the following contents

[mysqld]
secure_file_priv = ''

two ⃣ Restart the MySQL service and log in

brew services stop mysql
brew services start mysql

mysql -uroot -p           

3﹥ check whether the modification is successful in MySQL,

show global variables like 'secure_file%';

# If the modification is successful it should display the following, showing a null value
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

Done!

Link:
https://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

An error is reported when sqoop imports data into MySQL database: error tool. Exporttool: error during export: export job failed!

Problem Description:

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143

21/10/10 08:51:52 INFO mapreduce.Job:  map 100% reduce 0%
21/10/10 08:51:53 INFO mapreduce.Job: Job job_1633826412371_0001 failed with state FAILED due to: Task failed task_1633826412371_0001_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0

21/10/10 08:51:54 INFO mapreduce.Job: Counters: 9
    Job Counters 
        Failed map tasks=4
        Launched map tasks=4
        Other local map tasks=3
        Data-local map tasks=1
        Total time spent by all maps in occupied slots (ms)=52317
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=52317
        Total vcore-milliseconds taken by all map tasks=52317
        Total megabyte-milliseconds taken by all map tasks=53572608
21/10/10 08:51:54 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
21/10/10 08:51:54 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 110.2385 seconds (0 bytes/sec)
21/10/10 08:51:54 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead
21/10/10 08:51:54 INFO mapreduce.ExportJobBase: Exported 0 records.
21/10/10 08:51:54 ERROR tool.ExportTool: Error during export: Export job failed!

Solution:

① First, check whether the table structure in hive is consistent with MySQL. (desc table name)

 

 

  If the table structure is consistent, it may be caused by inconsistent character sets of MySQL tables

② Sqoop import data to MySQL database command:

bin/sqoop export \
> --connect “jdbc:mysql://master:3306/mysql?useUnicode=true&characterEncoding=utf-8” \
> --username root \
> --password 000000 \
> --table QX_diyu_results \
> --num-mappers 1 \
> --export-dir /user/hive/warehouse/diyu_resaults \
> --input-fields-terminated-by ","

It can be seen from the command that the code I use is utf8, so the character set of the MySQL table should be modified to utf8

 

 

  After the modification, run the command again. The following results show that the data import is successful:

 

[Solved] Hive export MYSQL Error: Container [pid=3962,containerID=container_1632883011739_0002_01_000002] is running 270113280B beyond the ‘VIRTUAL’ memory limit.

Problem description

Container [pid=3962,containerID=container_1632883011739_0002_01_000002] is running 270113280B beyond the ‘VIRTUAL’ memory limit.

Current usage: 91.9 MB of 1 GB physical memory used; 2.4 GB of 2.1 GB virtual memory used. Killing container.

Cause of problem

When yarn starts running, it checks the virtual memory and throws an exception.

Solution:

Modify Hadoop_Home/etc/Hadoop/yen-site.xml file

Add the following to save and exit:

1 <property>
2 <name>yarn.nodemanager.vmem-pmem-ratio</name>
3 <value>3.0</value>
4 </property>

Problem solved!

Treatment of MySQL database keyword as column name by mybatisplus — sqlsyntax errorexception: you have an error in your SQL syntax;

explain:

When designing the database, if you use the MySQL keyword as the column name (for example, order is used for sorting), an error will be reported: java.sql.sqlsyntax errorexception: you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER,CREATE_ TIME,MODIFY_ TIME FROM xxx’ at line 1

terms of settlement:

Modify the column name, do not use the keyword as the column name; (recommended)

When querying, use quotation marks (single quotation marks, double quotation marks and back quotation marks) to enclose the column name of its keyword,

If you use mybatis to write SQL, there is no problem. You can use quotation marks to solve it;

If you use mybatis plus, when querying all columns, you need to list all columns, because the corresponding column names containing keywords need to be processed.

How to Solve Navicat Remote Connection to MySQL is Stuck

Developing an application system to connect to the MySQL database of the company’s test server is very slow, but connecting to the local MySQL database is very fast. At first, it was thought that it may be caused by network connection problems. After Ping and routing, it was found that the network communication is normal, and the local connection on the MySQL machine is very fast, Therefore, the network problem has been basically eliminated, so I want to see if it is a MySQL configuration problem. After querying MySQL related documents and network search, it seems that a configuration can solve such a problem, that is, add the following configuration parameters to the MySQL configuration file:

[mysqld]
skip-name-resolve

Under Linux, the configuration file is/etc/my.cnf. Under windows, the configuration file is my.ini file under MySQL installation directory. Note that the configuration is added under [mysqld]. After changing the configuration and saving it, restart MySQL and conduct remote connection test. Everything is restored as before.

MySQL service failed to read description, error code: 2

Scenario: when 360 is used to remove garbage, a MySQL configuration file is deleted (PS: it should be the registry), resulting in a prompt: “MySQL service failed to read the description, error code: 2”

terms of settlement:

Detailed steps:

CMD administrator starts
switch to the database bin directory
enter the command SC delete MySQL
restart the computer
repeat actions 1 and 2
enter mysqld.exe – install
net start MySQL
precautions:

Restart the computer after the third step
in the sixth step, enter mysqld.exe – install instead of mysql.exe – install

 

Original link: https://blog.csdn.net/weixin_ 42437102/article/details/106380319

 

[Solved] MYSQL Error: Cannot delete or update a parent row: a foreign key constraint fails

1 problem
when deleting data or tables associated with foreign keys, MySQL reports an error:

Cannot delete or update a parent row: a foreign key constraint fails

2 solution

SET foreign_key_checks = 0; // First set the foreign key constraint check off

drop table mytable; // Delete the data, table or view

SET foreign_key_checks = 1; // Turn on foreign key constraint checking to maintain the structural integrity of the table
First turn off foreign key constraints, perform the delete operation, and then turn on foreign key constraints