Tag Archives: mysql

MySQLAccess denied for user ‘root’@’localhost’ [How to Solve]

The error information is as follows:

[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Cause of the problem.

There are empty users in the database

Solution:

1 Disable mysql service: # service mysql stop

2 Enter the command: # mysqld_safe –user=mysql –skip-grant-tables –skip-networking &

3 Login to the database: # mysql -u root mysql

4 mysql> use mysql;

5 mysql> select user,host,password from user;

6 Delete the empty user from the above query: mysql> delete from user where user=”;

7 Quit the database: mysql> quit

8 Start mysql service: # service mysql start

9 Log back in: # service mysql start # mysql -u root -p

Error in connecting to MySQL via Navicat on MAC

After installing MySQL on the Mac, connect with Navicat, and a connection failure error will pop up in the connection test:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

Find the following solutions online:

1.mysql -u root -p 2.use mysql; 3.alter user 'root'@'localhost' identified with mysql_native_password by '11111111'; 4.flush privileges;

explain:

1. Root is the user name, localhost is the IP address 127.0.0.1, especially the local machine, mysql_native_Password is the old password verification mechanism, followed by the password for MySQL.

2. Flush privileges: refresh the system permission related tables of MySQL.

As shown in the figure:

Back to Navicat, the personal test is successful!

Postscript: some netizens said that the new version would report this error. The MySQL I installed is the latest version 8.0.13. I don’t know if this is the reason.

[Solved] MYSQL Error: BIGINT UNSIGNED value is out of range

Causes:

When two fields are trying to be subtracted, if one or both fields are of type unsigned, an error is reported if the value to be subtracted is less than 0 (BIGINT UNSIGNED value is out of range)

.
Handling:

Example: select a – b from table

Change:select if(a >= b, a – b, – (b – a)) from table

[Solved] MySQL query reports an error: Legal mix of settlements

1. Specific scenarios

The two tables are:

CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户编号（自增字段）',
  `userName` varchar(32) NOT NULL DEFAULT '' COMMENT '用户昵称',
  # ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `tb_sms_message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机号',
  # ...
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8;

Error Message:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

2.Find SQL

SELECT * FROM `tb_sms_message`
  where tel not IN (select userName from tb_user);

3. Cause analysis

The collates of the two associated fields are inconsistent and cannot be directly associated

Here, we can convert the proofreading set of the field to consistent:

CONVERT(tel USING utf8) COLLATE utf8_general_ci

Modified SQL:

SELECT * FROM `tb_sms_message`
  where CONVERT(tel USING utf8) COLLATE utf8_general_ci not IN (select userName from tb_user);

MySQL external import data error [How to Solve]

Problem: export the database from the server to SQL and import the local database. Error code: 2006 – MySQL server is offline

Solution:

(1) This problem is due to the default max of MySQL_allowed_Because the packet is too small, you can modify it larger

max_allowed_Packet = 100m
in addition, you can add two parameters to expand the corresponding time
interactive_timeout=28800000
wait_timeout=28800000

(2) View the current configuration
show variables like ‘% max’_allowed_packet%’;

(3) Enter MySQL server and modify the configuration
Run
set global Max on the MySQL command line_allowed_Packet = 2 * 1024 * 1024 * 10 (the parameter can be set larger)
then close the MySQL server link and enter
show VARIABLES like ‘%max_allowed_packet%’;
View Max_allowed_Is the packet edited successfully

(4) Import the data again to solve the problem

[Solved] Centos Install MYSQL Error: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

When installing MySQL in CentOS, an error is reported when executing the command mysqld — initialize/lib/LD Linux. So. 2: bad elf interpreter: no such file or directory

This is because a 32-bit program is installed in a 64 bit system

Install glibc.i686:

yum install glibc.i686

After executing the glibc.i686 installation command, we continue to execute mysqld — initialize and find that the system still reports an error:

error while loading shared libraries: libz.so.1:

cannot open shared object file: No such file or directory

This is because the zlib.i686 component is missing in the system. Let’s continue to install zlib.i686:

yum install zlib.i686 --setopt=protected_multilib=false <!--The setpot parameter handles multiple library coexistence conflicts-->

At this point, if you continue to execute mysqld — initialize, the package still lacks i686 components. Later, the blogger continued to install various missing components, and found that this is not the way to do it. It is estimated that there are hundreds of such components missing. I don’t know when the installation will end. Later, I continued to search for methods on the Internet, Finally, we found a way to install all the components needed by 32-bit programs

yum install xulrunner.i686  <!--ia32-libs.i686 //is ubuntu series, and after 13.10 ubuntu also seems to have no this-->

[Solved] MYSQL Error: this authentication plugin is not supported

this authentication plugin is not supported

The application keeps reporting an error when connecting to mysql docker: this authentication plugin is not supported.
I found that the new version of mysql (8.0 or above) updated the plugin used by the root user to caching_sha2_password.
Login to mysql and enter the following command to see.

The solutions are.
(1) Downgrade and use an older version of mysql.
(2) Change root’s plugin to mysql_native_password.
Here change it to

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’;

This line of code has two meanings, first: change root’s password to ‘root’, discarding the old password. Second: use mysql_native_password to encode the new password.

Then start the application again, it still reports the same error. Looking at mysql.user again, I found that there is another root user with host “%”.

Change this user as well.
ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘root ‘;
See again.

The change was successful, and when I started the application, I got the following error
This user requires mysql native password authentication
Add ?allowNativePasswords=true to the url of the mysql connection, this time it works fine.
———————

[Solved] MySQL Restart Error: Another MySQL daemon already running with the same UNIX socket

Another MySQL daemon already running with the same UNIX socket

Solutions

service mysqld stop
mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak
service mysqld start

Or delete

rm /var/lib/mysql/mysql.sock

[Solved] Error report of inconsistent password format between low version and high version of MySQL

When importing low version database data into high version database, the following problems are caused by different password formats:

[root@localhost~]#mysql-uroot-p
Enterpassword:
ERROR2049(HY000):Connectionusingold(pre-4.1.1)authenticationprotocolrefused(clientoption'secure_auth'enabled)

View help

[root@localhostmysql]#mysql--help|grep'secure-auth'
--secure-authRefuseclientconnectingtoserverifitusesold
(Defaultstoon;use--skip-secure-authtodisable.)

Still failed

[root@localhost~]#mysql-uroot-p--secure-auth=off
Enterpassword:
ERROR1275(HY000):Serverisrunningin--secure-authmode,but'root'@'localhost'hasapasswordintheoldformat;pleasechangethepasswordtothenewformat。

The final solution

[root@localhostmysql]#vim/etc/my.cnf
[mysqld]
secure_auth=off

Then Restart Database
[root@localhost~]#mysql-uroot-p--secure-auth=off
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis5
Serverversion:5.6.28-76.1-logPerconaServer(GPL),Release76.1,Revision5759e76

Copyright(c)2009-2015PerconaLLCand/oritsaffiliates
Copyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

root@localhost11:31:[(none)]>

Update password format

root@localhost11:31:[(none)]>updatemysql.usersetpassword=password('xxxxxx')whereuser='root'andhost='localhost';
root@localhost11:36:[(none)]>flushprivileges;

Delete secure after modification_ Auth = off parameter, restart the database

[Solved] MySQL Connect Error: Can’t connect to MySQL server on ‘ ‘(61)

There is an account TT that can’t connect to MySQL server on ‘< remote-ip>’ ( 61)

1. Check whether the user has remote login permission

mysql> SELECT User, Host FROM mysql.user;
+-----------+-----------+
| User      | Host      |
+-----------+-----------+
| tt        | %         |
| mysql.sys | localhost |
| root      | localhost |
| Laily     | ％        |
| ttt       | ％        |
+-----------+-----------+
5 rows in set (0.00 sec)

Obviously, TT is allowed to log in from other servers

2. Check whether MySQL server has monitored port 3306

[root@centos-linux ~]# netstat -tulpen
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       User       Inode      PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      0          16801      1507/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      0          17222      1970/master
tcp6       0      0 :::3306                 :::*                    LISTEN      27         46396      22054/mysqld
tcp6       0      0 :::22                   :::*                    LISTEN      0          16803      1507/sshd
tcp6       0      0 ::1:25                  :::*                    LISTEN      0          17223      1970/master
udp        0      0 0.0.0.0:68              0.0.0.0:*                           0          49600      22999/dhclient
udp        0      0 0.0.0.0:43504           0.0.0.0:*                           0          48850      22999/dhclient
udp6       0      0 :::47875                :::*                                0          48851      22999/dhclient

I have no problem here. If I don’t monitor port 3306 or only monitor localhost (0.0.0.0 means monitor all), add the following line in My. CNF

bind-address = 0.0.0.0

3. If the server is centos7, add MySQL service to the firewall

[root@centos-linux ~]# sudo firewall-cmd --zone=public --permanent --add-service=mysql
success
[root@centos-linux ~]# sudo systemctl restart firewalld

After I restart the firewall, I can access it normally

DebugAH

How to Solve Your Programmer Error