Category 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

[Solved] MySQL Error: com.mysql.cj.jdbc.util.TimeUtil.loadTimeZoneMappings(TimeUtil.java:163)

Question:

Mysql database version 5.6, jdbc driver package mysql-connector-java-5.1.49-bin version, start tomcat, and an error is reported when connecting to the database. Details:

java.lang.NullPointerException
    java.util.Properties$LineReader.readLine(Properties.java:434)
    java.util.Properties.load0(Properties.java:353)
    java.util.Properties.load(Properties.java:341)
    com.mysql.cj.jdbc.util.TimeUtil.loadTimeZoneMappings(TimeUtil.java:163)
    com.mysql.cj.jdbc.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:109)
    com.mysql.cj.mysqla.MysqlaSession.configureTimezone(MysqlaSession.java:308)
    com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:2474)
    com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1817)
    com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1673)
    com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:656)
    com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:349)
    com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:221)

After query, it is said that the exception is thrown because the format of the MySQL server time zone (inherited from the system time zone) is different from that expected by the MySQL connector.

Solution:

Add the JDBC URL to the servertimezone, for example:

jdbc.url=jdbc:mysql://localhost:3306/xuejia?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8

servertimezone = UTC , where UTC is the unified standard world time; China is in the East eighth district. If you need to specify the time zone as China, you can modify the configuration to servertimezone = GMT% 2B8 .

About time zone:

UTC, coordinated universal time. UTC is a time measurement system based on atomic time and second length, which is as close as possible to GMT in time. To ensure that UTC does not differ from GMT by more than 0.9 seconds, positive or negative leap seconds will be added to UTC if necessary. UTC is now used as world standard time
GMT: Greenwich mean time, that is, universal time. Noon in GMT refers to the time when the sun crosses the Greenwich meridian (primary meridian). However, due to the uneven and irregular rotation of the earth, GMT is not accurate, and it is no longer used as the world standard time
UTC is basically the same as GMT, with an error of no more than 0.9 seconds

[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!

How to Solve Client does not support authentication protocol requested by server; Consider upgrading MySQL

Today, the server deployed node.js + MYSQL, and the calling interface reported an error er_ NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

After checking the information, I changed two places

1. Server security group policy is configured and 3306 port is added

2. I modify the MySQL encryption rules to normal mode, and the default is strict encryption mode

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的数据库密码';

The operation steps are as follows: then restart node.js to test

It’s done~

 

[Solved] MYSQL8 Error: ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)

1：stop serves: net stop mysql
2：remove serves: mysqld -remove mysql
3：E:\zyf\mysql8\bin>mysqld -initialize-insecure -user=mysql
4：E:\zyf\mysql8\bin>mysqld -install mysql
5：E:\zyf\mysql8\bin>net start mysql
6：E:\zyf\mysql8\bin>mysql
Error: ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO)
7：E:\zyf\mysql8\bin>mysql -uroot -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10  Then you can log in normally!
If you encounter Navicat can’t connect to mysql service: Error reported.

You can start by logging into mysql in a DOS command window at
Then run: mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘root123’;
Query OK, 0 rows affected (0.07 sec)
Then you can log in normally.

Oracle executes SQL script file with command

When there are too many SQL commands (the SQL file is too large), the execution of PLSQL is slow and easy to timeout. At this time, you can directly execute the SQL script file with sqlplus command, as follows:

1. Sqlplus login

> sqlplus username/ password@dbname

2. Execute SQL file

If the SQL file is in the current directory, you can directly execute:

>@ demo.sql

If the SQL file is not in the current directory, you can use the absolute path:

>@ D:/demo.sql

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.

How to Backup MySQL database regularly

Requirement: regularly back up MySQL specific database

Pre:

MySQL socket path;

# netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     37194    /tmp/mysql.sock

Backup command, named after dbname + date:

mysqldump --socket=/tmp/mysql.sock -u root -ppassword dbname > dbname_back_`date +%F`.sql

Mount to the public disk, there is no need for SCP;

Prepare the SH file, back up DB to a specific path, package it, and delete the backup for more than 30 days:

#!/bin/bash
backdir=/mnt/backup_db
d=`date +%F`

mysqldump --socket=/tmp/mysql.sock -u root -ppassword dbname > $backdir/dbname_back_$d.sql

cd $backdir
gzip *_$d.sql
find ./ -name "*.gz" -mtime +30 |xargs rm

Set sh file as executable:

chmod +x /mnt/backup_db/mysql_backup.sh

Determine the backup frequency and execute it at 7:59 a.m. every Friday;

# crontab -e
59 07 * * 5 /bin/bash /mnt/backup_db/mysql_backup.sh >/tmp/mysql_backup.log 2>/tmp/mysql_backup.log