Tag Archives: mysql

MySQL Connect Error: Authentication plugin ‘caching_sha2_password’ cannot be loaded

When many users connect to MySQL database with Navicat premium 12, authentication plugin ‘caching will appear_ sha2_ Error in password ‘cannot be loaded

The reason for this is that the encryption rule in versions before mysql8 is mysql_ native_ After mysql8, the encryption rule is caching_ sha2_ Password, there are two ways to solve the problem, one is to upgrade the Navicat driver, the other is to restore the MySQL user login password encryption rules to MySQL_ native_ password.

Here is the second way. The solution is as follows

1. Run the command prompt with administrator permission, log in MySQL (remember to add environment variables)

mysql -u root -p

Password: # login to MySQL

2. Modify the account password encryption rules and update the user password

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER; # Modify encryption rules

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_ native_ password BY ‘password’; # Update the user’s password

3. Refresh permissions and reset password

FLUSH PRIVILEGES; # Refresh permissions

Screenshots of the above two steps

Separate password reset command: alter user ‘root’ @’localhost ‘identified by’ 111111 ‘

Now open Navicat premium 12 again to connect to the MySQL problem database, and you will find that the connection is successful

 

MySQL error 1205: lock wait timeout exceeded solution

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

Error 1205 (HY000): lock wait timeout exceeded; try restarting transaction

Error reason: an SQL execution is finished, but it is not committed. If the following SQL wants to execute, it is locked, and the timeout ends

Solution:

1. View the process information of the database

show full processlist;

2. View the transaction table

SELECT * FROM information_schema.INNODB_TRX

Note: abnormal transaction found (usually records whose start time is a long time from now)

/*Note the TRX in the result_ mysql_ thread_ The value of the ID part*/

Find the corresponding ID, and then kill the value of ID

When MySQL uses select statement to export table data, error 1290 is reported

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

When using select… Into output to export data, error 1290 (HY000) is reported. The statement is as follows:

select * into outfile 'd:\abc.txt'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\n'
from emp;

Error 1290 (HY000): the MySQL server is running with the — secure file priv option so it cannot execute this statement

analysis: prompt message – Secure File priv, it can be seen that the file export is restricted. So modify the restriction information in the configuration

Note:

secure_ file_ Prive = null restricts mysqld from importing and exporting

secure_ file_ Priv = “C/programdata/MySQL/MySQL server 8.0/uploads” restricts the import and export of mysqld to C/programdata/MySQL/MySQL server 8.0/uploads

secure_ file_ Priv = ”does not restrict the import and export of mysqld

solution:

I don’t want to restrict it, so the solution is to add secure to the MySQL configuration file_ file_ priv=’ ‘

1. Open the my.ini file of windows, my version mysql8.0, and the configuration file path is C:// programdata/MySQL/MySQL server 8.0/my.ini

2. Add secure under [mysqld] node of my.ini file_ file_ priv=”

3. Restart mysql8.0 service

Execute the export statement again

Error C in creating Chinese table name in MySQL ode:1005 Can ‘t create table .. errno:22 )

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

When using Mysql to create a Chinese table name, an error · error C is reported ode:1005 Can ‘t create table .. errno:22 ·。 This situation is mainly caused by the odd number of Chinese characters in the table name, which can be changed to even number of Chinese characters

Completely solve MySQL error: 1030, ‘got error 28 from storage engine’

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

Copyright notice: This article is the original article of the blogger, which follows the CC 4.0 by-sa copyright agreement. Please attach the link of the original source and this notice for reprint
link to this article: https://blog.csdn.net/harry5508/article/details/84345684

To be frank with me, the online articles are all the same, and none of them can be solved. It’s just a matter of clearing up the memory, but there is no Xiao Bai who can’t be taught to clean up… =

the problem is that the server system is full, and the temporary file directory specified by MySQL is full, This is what I mean.

the following solution is that the/dev/vda1 system is full. In fact, I have no idea where/dev/vda1 is or what it is. Later, I learned that it is a virtio block device< to popularize science: a line beginning with ‘C’ indicates that the device is a character device, and a line beginning with ‘B’ indicates that it is a block device

/dev/VDA and/dev/VDB are virtio block type devices, while/dev/SDA is SD, or SCSI type device

open 4. If the log is too large, it can be cleared

run the command:

cat/dev/null > File. Log

for pro testing, catalina.out in the MySQL folder can be deleted. Execute:

echo “> Catalina. Out

generally, this log file is relatively large.
5. If the software package is too large, you can uninstall it if you don’t need it

to perform the uninstall

RPM – E software name

or delete

RM – RF folder name

6. You can delete the large file almost. You can use DF – H again and reserve half of the disk space to perfectly solve the MySQL 1030 problem original link: https://blog.csdn.net/harry5508/article/details/84345684

Error 1046 (3d000): no database selected) error in importing MySQL datagram from Ubuntu 18.04

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

The error message is as follows:

ERROR 1046 (3D000): No database selected

There are two reasons for the error

1. Before importing the database, there is no such database in the database

Solution: create a new database with the name of the database you want to import, select the database, and then import it

The input command and running results are as follows:

　　

mysql> use mydb
mysql> source /root/mydb.sql

2. The location of the SQL to be imported is not correct 2

Solution: check the path of your SQL file to modify it

　　

MySQL error operation should contain * column solution

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

SQL statements are used to deal with some content. When executing a statement, MySQL reports an error: operate should contain 1 column

Literally, there needs to be one data column

Solution: change the query result of the query SQL to a separate field column

MySQL import file prompt — secure file priv option problem

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

MySQL import CSV format file data execution prompt error (error 1290)

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

【1】 Analyze the reasons

In fact, the reason is very simple, because the import and export directory permissions are limited when MySQL is installed. It can only be imported in the specified directory

You can use the following command to see what the current value of secure file priv is

SHOW VARIABLES LIKE "secure_file_priv";

Results: the results were as follows

As you can see, the value of local value is null. What does null mean?Information:

(1) Null, which means forbidden

(2) If the value value has a folder directory, it means that only files in the directory are allowed (PS: Test subdirectory is not allowed)

(3) If it is empty, the directory is not restricted

【2】 Solutions

The cause of the problem has been found, and the solution depends on the business needs

(1) Scheme 1:

Put the import file into the path corresponding to the current value of secure file priv

(2) Scheme 2:

Change the value of secure file priv to the path where the file is to be imported

(3) Scheme 3: modify the configuration

Remove the Import Directory restrictions. You can modify the MySQL configuration file (my.ini under windows and my.cnf under Linux). Under [mysqld], check whether there is any:

secure_ file_ priv =

If there is no such line, add it manually. If it exists, as shown in the following table:

secure_ file_ priv = /home

Such a line indicates that it is restricted to the/home folder. For example:

secure_ file_ priv =

Such a line of content means that there is no restriction on the directory, and the equal sign must exist, otherwise MySQL cannot be started

After modifying the configuration file, restart Mysql to take effect

After restart:

Close: Service mysqld stop

Start: Service mysqld start

Re query results:

After verification, the import file is normal

Good Good Study, Day Day Up.

Sequence selection cycle summary

How to Solve MYSQL error “no directory, logging in with home = -“

Prerequisites: Using the system’s environment
3.13.0-24-generic

Version of mysql: 5.6.33

Error description.
The first error reported with mysqld_safe start is as follows.

root@zabbix-forFunction:~# mysqld_safe
170425 01:56:52 mysqld_safe Can’t log to error log and syslog at the same time. Remove all –log-error configuration options for –syslog to take effect.
170425 01:56:52 mysqld_safe Logging to ‘/var/log/mysql/error.log’.
170425 01:56:52 mysqld_safe A mysqld process already exists
root@zabbix-forFunction:~# ps -aux | grep mysql
mysql 1472 0.1 15.5 1752260 625824 ?Ssl Apr24 1:16 /usr/sbin/mysqld
root 10160 0.0 0.0 15952 920 pts/3 S+ 01:59 0:00 grep –color=auto mysql

At this time, use /etc/init.d/mysql stop to stop the mysql service and find that the mysql process is still present, you need to use the following command to stop the process.

root@zabbix-forFunction:~# sudo service mysql stop
mysql stop/waiting
root@zabbix-forFunction:~# ps -aux | grep mysql
root 13531 0.0 0.0 15948 920 pts/3 S+ 02:15 0:00 grep –color=auto mysql

Use the usermod command to relocate the directory where mysql is located.

root@zabbix-forFunction:~# sudo usermod -d /var/lib/mysql/ mysql
Then restart the mysql service again at

root@zabbix-forFunction:~# sudo service mysql start
mysql start/running, process 13597

How to Solve MYSQL Error: Can’t create test file xxx lower-test

In Linux, the prompt MySQL prompts [warning] cant create test file XXX lower test. Here are some friends who have encountered this kind of problem. Let’s have a look at the solution

I think you must have come in from the search engine! You must want to change the default installation of MySQL data directory
you have modified the value of dataDir in my.cnf

The first is to look at the database log

mysqld started

[Warning] Can’t create test file xxx.lower-test
[Warning] Can’t create test file xxx.lower-test
/usr/libexec/mysqld: Can’t change dir to ‘/xxx’ (Errcode: 13)
[ERROR] Aborting

You’ve chown and Chmod several times about the owner and permission of the new data directory or its parent path

You’ve tried to service mysql start, or/etc/init.d/mysql start, or mysql start countless times_ install_ db！

Congratulations on reading this article. I found a solution after being trapped by the system for several hours

There are two reasons for this. Any one of them will cause you to be told the warning by the system. If you are not a professional Linux system security engineer, or if you are just a php programmer, and do not have in-depth research on system security, you will not be easy to find the answer

First, SELinux. I remember when I was a student, RedHat was installed on the character interface There is such an option, usually we listen to the advice of the predecessors, change the default value to not install it. But if the machine you are about to operate is running SELinux, it can really prevent your MySQL from running in the new target location_ install_ DB operation, and the title of the warning. A simple solution is to use the command to shut down SELinux temporarily so that your operation can continue
setenforce 0

But it’s best to use a permanent method so that you don’t want the goods after restart

Modify SELinux = disabled in/etc/SELinux/config file, and then restart or wait for the next restart

Second, AppArmor, which is the same as SELinux, also limits the directory permissions that MySQL can use
in the file/etc/apparmor.d/usr.sbin.mysqld, there are two lines that specify the data file path permissions that MySQL can use

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

As you can see,/var/lib/MySQL/is the default path of the data files installed in MySQL before, and the permission of the directory where mysqld can be used is controlled by the app
I want to move the data files to/data/MySQL, so that mysqld can use the directory/data/MySQL, just follow the above two and add the following two

/data/mysql/ r,
/data/mysql/** rwk,

Restart the application,/etc/inid.d/application restart