Category Archives: MYSQL

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

mysql> select user,plugin from mysql.user;
+——————+———————–+
| user | plugin |
+——————+———————–+
| root | caching_sha2_password |
| mysql.infoschema | mysql_native_password |
| mysql.session | mysql_native_password |
| mysql.sys | mysql_native_password |
| root | caching_sha2_password |
+——————+———————–+

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 “%”.

mysql> select host,user,plugin from mysql.user;
+———–+——————+———————–+
| host | user | plugin |
+———–+——————+———————–+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session | mysql_native_password |
| localhost | mysql.sys | mysql_native_password |
| localhost | root | mysql_native_password |
+———–+——————+———————–+
5 rows in set (0.00 sec)

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

mysql> select host,user,plugin from mysql.user;
+———–+——————+———————–+
| host | user | plugin |
+———–+——————+———————–+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session | mysql_native_password |
| localhost | mysql.sys | mysql_native_password |
| localhost | root | mysql_native_password |
+———–+——————+———————–+

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] Navicat Connect Oracle :Cannot load OCI DLL, 126

Error in 32-bit system: cannot load OCI DLL, 126

Solution: Navicat menu – tools – > Options – > OCI select oci.dll in bin under Oracle installation directory

Error in Windows Server 2008: cannot load OCI DLL, 126

Oracle 11g R2 is installed on Windows Server 2008 server. When using Navicat to connect Oracle, the following error will be prompted:

Cannot load OCI DLL, 126: Instant Client package is required for Baic and TNS connection ，
For more information: http://wiki.navicat.com/wiki/index.php/Instant_client_required

According to the above link page, Navicat only supports 32-bit instant client. Therefore, although we have installed 64 bit Oracle, we need to download a 32-bit client as Navicat only supports 32-bit http://www.oracle.com/technetwork/topics/winsoft-085727.html 。

Here is the complete solution:

Step 1: download the file from the above address: instantclient-basic-nt-12.1.0.2.0.zip,

Step 2: unzip the installation package to: D]/APP/administrator/product/instantclient_ 2_ 2_ x32

Step 3: open Navicat, select tools → options → other → OCI, and then set OCI library as D: APP/administrator/product/instantclient_ 12_ 2_ X32/oci.dll, set SQL * plus as: D]/APP/administrator/product/11.2.0/dbhome_ 1/BIN/sqlplus.exe.Sure

Step 4: the test is successful

ORA-01940: cannot drop a user that is currently connected [How to Solve]

 

This error is often encountered when deleting a database user

ORA-01940: cannot drop a user that is currently connected

The reason is that there is a program connecting to the user that we need to delete, so we must disconnect the user before deleting it.

1. Find which connections are running under the account.

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’USERNAME’；

Note: Capitalization is required here

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’SSTEST’;

SID SERIAL#
———- ———-
137 41813
335 25993
464 58747
500 9153
531 50402
629 9264
631 11237
664 36180

8 rows selected.

2. Deleting user processes.

SQL>ALTER SYSTEM KILL SESSION ‘137,41813’;

System altered.

SQL>ALTER SYSTEM KILL SESSION ‘33525993;

System altered.

3, re-view the user connection and confirm that no connection is in use

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’SSCRM’;

4、delete users

drop user sstest cascade ；

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

Phenomenon

MySQL appears when deleting a table

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Why

It may be that in mysql, the foreign key association is set between the deleted table and another table, which makes it impossible to update or delete data

Solutions

By setting foreign_ KEY_ Checks variable to avoid this

Disable foreign key constraints

SET FOREIGN_KEY_CHECKS = 0;

Then you can delete the table

Start the foreign key constraint after deletion

SET FOREIGN_KEY_CHECKS = 1;

View current foreign_ KEY_ The value of checks can be determined by the following command

SELECT  @@FOREIGN_KEY_CHECKS;

Attention

This setting will only affect the current session, not the global

If you want to set global variables, you can write like this

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

[Solved] MYSQL ERROR 2003 (HY000): can’t connect to MySQL server (10060)

How to Solve MYSQL ERROR 2003 (HY000): Can’t connect to MySQL server on “host” (10038)

Error when connecting to remote MySQL.

ERROR 2003 (HY000): Can’t connect to MySQL server ‘ip’ (10038)

View error type (Linux systems)

perror 113

1

The results are as follows:
OS error code 113: no route to host

The reason for this error is that the server-side firewall does not open the port used by MySQL program. The default is 3306, and the port can be opened

/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

1

Save the settings and restart the firewall

service iptables save
service iptables restart

Linux Set up open 3306 port solution

Firewall

Enable: service iptables start
Shutdown: service iptables stop

Turn off the firewall for a long time

On: chkconfig iptables on
Turn off: chkconfig iptables off

/sbin/iptables -I INPUT -p tcp –dport 3306 -j ACCEPT #Open port 3306

/sbin/iptables -A INPUT -p tcp –dport 80 -j ACCEPT #Enable port 80

/sbin/iptables -A INPUT -p tcp –dport 22 -j ACCEPT #Open port 22

My local machine is Ubuntu, and I can open it by just typing the above command

/etc/rc.d/init.d/iptables save #Save the configuration

/etc/rc.d/init.d/iptables restart #restart the service

View open ports
/etc/init.d/iptables status
The result is as follows
Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
2 ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 tcp dpt:22

3 ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 tcp dpt:80

[Solved] Rename failed for user ‘dbo’ -MS SQL ERROR 15150

SQL SERVER 2016 error when modifying “User Mapping”.

TITLE: Microsoft SQL Server Management Studio

——————————
Rename failed for User ‘dbo’. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.37971.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+User&LinkId=20476

——————————

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot alter the user ‘dbo’. (Microsoft SQL Server, Error: 15150)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=15150&LinkId=20476

——————————

BUTTONS:
OK

——————————

Solution:

Log in to SQL SERVER STUDIO with SA and execute the following command.

use testdatabaseName
EXEC sp_changedbowner ‘sa’,’true’

[Solved] MYSQL Remote connection 2003 Error: can’t connect to MYSQL server on ”(10060)

Date: November 17, 2017

Problem: error in remote connection to MySQL database: 2003 can’t connect to MySQL server on ” (10060)

Reason: the alicloud console does not open port 3306 in the instance security group

How to discover: discovered when using Navicat to connect to remote database

Repair: open the alicloud console and add port 3306 to the instance security group

In which files have been modified: the alicloud console has been modified

I lead to: No

Bug resolution time: 2 hours

Lesson: because I was just beginning to learn Java, I didn’t think that it was a problem on the Alibaba cloud server console when this problem occurred. I checked a lot of information on the Internet, There are three similar solutions: 1. Port 3306 is not opened on the remote server; 2. Your IP is not authorized to log in to the remote database (or your database account refuses to log in remotely); 3. The network is blocked

The solution to the first problem is:

Opening port 3306 in iptables

#/sbin/iptables-IINPUT-ptcp–dport3306-jACCEPT

#/Etc/rc.d/init.d/iptablessave:

#Service iptables restart takes effect

The second solution is:

Set remote user access rights:

//Any remote host can access the database

mysql> GRANTALLPRIVILEGESON*.*TO’root’@’%’WITHGRANTOPTION;

//You need to enter a command for the modification to take effect

mysql> FLUSHPRIVILEGES;

//Exit

mysql> EXIT

The third is not explained

(another way is to turn off the server firewall:

#

This method is not recommended for serviceiptables stop, which may cause unknown security problems.)

Obviously, it didn’t solve the problem in the end. A lot of information on the Internet is repetitive. In the end, there is no way. I found that it was the problem on the Alibaba cloud console with the help of my elder martial brother. It shows that I’m still thinking in the wrong direction

Open ECS

Open an instance of the menu on the left

Open the management on the far right of the instance

Open the security group of this instance in the menu on the left

Open the configuration rule on the right

Add security group rule in upper right corner

The protocol type is customized by default, the port range is 3306/3306, the authorization object is 0.0.0.0/0, and others are OK by default

Confirm and restart the server

Connect OK!

[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