Category Archives: MYSQL

[How to Solve ]Error 1130 (HY000) of MySQL authorized connection

ERROR 1130 (HY000): Host ‘Lenovo-PC’ is not allowed to connect to this MySQL server

MySQL Authorization Allowed Connection ERROR 1130 (HY000)

C:\Users\Lenovo>mysql-h10.255.9.79-uroot-p034039
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
ERROR1130(HY000):Host'Lenovo-PC'isnotallowedtoconnecttothisMySQLserver

C:\Users\Lenovo>mysql-hlocalhost-uroot-p034039
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis54
Serverversion:5.6.21MySQLCommunityServer(GPL)

Copyright(c)2000,2014,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

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

mysql>

You can see from the above information that when the h parameter is the local IP address, MySQL does not allow the client to connect. Why

Look at the following SQL query:

mysql>usemysql
Databasechanged
mysql>selectuser,hostfromuser;
+------+-----------+
|user|host|
+------+-----------+
|root|127.0.0.1|
|root|::1|
||localhost|
|root|localhost|
+------+-----------+
4rowsinset(0.00sec)

the root user is only allowed to connect on 127.0.0.1 and localhost, but is not allowed to connect using the local IP in LAN

This explains why connections are not allowed

The following is to give root the corresponding login permissions

mysql>GRANTALLPRIVILEGESON*.*TO'root'@'10.255.9.79'IDENTIFIEDBY'034039'WITHGRANTOPTION;
QueryOK,0rowsaffected(0.23sec)

mysql>selectuser,hostfromuser;
+------+-------------+
|user|host|
+------+-------------+
|root|10.255.9.79|
|root|127.0.0.1|
|root|::1|
||localhost|
|root|localhost|
+------+-------------+
5rowsinset(0.00sec)

mysql>

Through this authorization statement

mysql>GRANTALLPRIVILEGESON*.*TO'root'@'10.255.9.79'IDENTIFIEDBY'034039'WITHGRANTOPTION;

it is allowed to use the root user and password 034039 to connect all MySQL databases at the address of 10.255.9.79, and pay the select, insert, update and delete permissions

Finally, refresh the permissions:

mysql>flushprivileges;
QueryOK,0rowsaffected(0.68sec)

OK, this completes the authorization

C:\Users\Lenovo>mysql-h10.255.9.79-uroot-p034039
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis55
Serverversion:5.6.21MySQLCommunityServer(GPL)

Copyright(c)2000,2014,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

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

mysql>

[Solved] Mysql ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

 

Mysql5.6 is closed by default and 5.7 is open by default

show variables like '%innodb_large_prefix%';

set global innodb_large_prefix=on;

show variables like '%innodb_file_format%';
#output:
innodb_file_format    Antelope
innodb_file_format_check    ON
innodb_file_format_max    Antelope

 set global innodb_file_format=Barracuda;

MYSQL ERROR 1005 (HY000): Can’t create table [How to Solve]

A small problem I encountered today when creating a MYSQL database.

create table booktype
(
btid int(5) unsigned zerofill auto_increment not null primary key,
btname varchar(100) not null unique,
btnote text
);

create table books
(
bid int(5) unsigned zerofill auto_increment not null primary key,
bname char(30) not null,
isbn char(50) not null,
author char(30) not null,
press text,
summary text,
bcount int not null default 0,
btid int,
foreign key(btid) references booktype(btid)
);

error:

ERROR 1005 (HY000): Can’t create table ‘.\bookdata\books.frm’ (errno: 150)

The main problem is that

foreign key(btid) references booktype(btid) in books table btid is int and booktype table btid set associated field type does not match, books table btid corrected to: btid int(5) unsigned zerofill, it will not report an error, creating tables and modifying tables often forget this in one small step.

[Solved] MYSQL Deadlock found when trying to get lock; try restarting transaction

Deadlock found when trying to get lock; try restarting transaction

1213 – Deadlock found when trying to get lock; Try restarting transaction
one thing to remember is that the row lock and unlock of InnoDB are for primary key index. If the table is locked according to the index when querying, but not through the primary key when updating,
the process waiting to unlock the query will report 1213 error, and the program may return a null value
instance:
Table
solidgoods (table name)
solidgoodsid index
productid
businessid

start thread a
execution:
set autocommit = 0
select businessid from soldgoods where soldgoodsID = ‘ac63837c76222e4a5419e2529d775ae4’ for UPDATE;<
query result

start thread B
execute:
set autocommit = 0
select businessid from soldgoods where soldgoodsID = ‘ac63837c76222e4a5419e2529d775ae4’ for UPDATE;<
the query is waiting to be unlocked

at this time, it is executed in thread a:
update solidgoods set productid = 2 where business id =’0a527df4763c3dc71cbafebec5a8d787 ‘
the value of the lock table is not updated according to the primary key

thread B will appear:
[err] 1213 – deadlock found when trying to get lock; Try restarting transaction

if the statement executed in the last thread a is changed:
update soldgoods set productid = 2 where soldgoods id =’ac63837c76222e4a5419e2529d775ae4 ‘
modify the value according to the index
and then
commit
commit the transaction. Thread B can get the query value smoothly

MYSQL error: Column’id’ in field list is ambiguous solution

[Err] 1052 – Column ‘modify_time’ in where clause is ambiguous

Error statement:

SELECT AVG(T.se)%60
FROM
( SELECT TIMESTAMPDIFF(SECOND,first_transfer_time,modify_time) se
FROM xes_appeals
INNER JOIN xes_appeal_templates
WHERE xes_appeals.app_type = xes_appeal_templates.app_type
AND xes_appeals.app_type = 313
AND xes_appeals.first_transfer_time >= ‘2018-11-23 00:00:00’
AND xes_appeals.first_transfer_time <= ‘2018-11-23 23:59:59’
AND modify_time >= ‘2018-11-23 00:00:00’
AND modify_time <= ‘2018-11-23 23:59:59′
AND xes_appeals.`status` =4
AND xes_appeals.operater_id <> 0

) T

 

The column’ID’ is repeated in the field list. In fact, the two tables have the same field, but the table name is not added before the name of the table field when used, resulting in unknown reference

 

after adjustment:

SELECT AVG(T.se)%60
FROM
( SELECT TIMESTAMPDIFF(SECOND,first_transfer_time,xes_appeals.modify_time) se
FROM xes_appeals
INNER JOIN xes_appeal_templates
WHERE xes_appeals.app_type = xes_appeal_templates.app_type
AND xes_appeals.app_type = 313
AND xes_appeals.first_transfer_time >= ‘2018-11-23 00:00:00’
AND xes_appeals.first_transfer_time <= ‘2018-11-23 23:59:59’
AND xes_appeals.modify_time >= ‘2018-11-23 00:00:00’
AND modify_time <= ‘2018-11-23 23:59:59’
AND xes_appeals.`status` =4
AND xes_appeals.operater_id <> 0

) T

 

How to Solve MySQL Startup error: mysql-bin.index not found (Errcode: 13)

After installing and initializing MySQL database under Linux, use mysqld_ Safe starts the MySQL database, as shown below, the startup fails

[ root@SVNServer bin]# ./mysqld_ safe –user=mysql&

Or

[ root@SVNServer bin]#/etc/init.d/mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/AY14020816093477605eZ.pid).

Restart MySQL will throw the above red font error

You can only see the specific reasons for the failure of starting mysql. You need to check the. Err file in the database directory and the. Err file. The contents are as follows:

140726 00:18:10 mysqld_ safe mysqld from pid file /data/mysql/AY14020816093477605eZ.pid ended

140726 00:31:19 mysqld_ safe Starting mysqld daemon with databases from /data/mysql

/usr/local/mysql/bin/mysqld: File ‘./mysql-bin.index’ not found (Errcode: 13)

140726 0:31:19 [ERROR] Aborting

140726 0:31:19 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

This error report is marked in red, errcode13, which is generally a permission problem. Whether MySQL users have permission to write all the files in the database directory, check the permissions, and modify the permissions of users and user groups in the MySQL directory

chown -R mysql:mysql /usr/local/mysql

Check the permissions in the/usr/local/MySQL directory, as shown in the following figure

I found the problem. It’s really a permission problem. The following troubleshooting is simple. Assign the owner of the data directory to the MySQL user

in RedHat system, : chown mysql.mysql – R/usr/local/MySQL can be used

in CentOS system, : chown can be used mysql:mysql -R /usr/local/mysql

OK, now start MySQL again, as shown in the figure below, there is no error

[ root@SVNServer bin]#/etc/init.d/mysqld start

If the error is still reported, it may be the permission setting problem of/usr/local/MySQL directory. The permission setting is as follows:

chmod 755 /usr/local/mysql

After setting, the permissions are as follows: ll/usr/local/MySQL

Drwxr-xr-x 9 MySQL MySQL 12288 July 26 09:50 MySQL

Try, can connect, as shown in the figure, OK

 

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 timestamp set default value error Invalid default value reason and solution

MySQL timestamp setting default value error Invalid default value reason and solution

The data exported from the mysql5.5 database is placed in mysql5.7.10 and an error is reported!

DROP TABLE IF EXISTS `passwd_reset`;
CREATE TABLE `passwd_reset` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staffId` int(11) DEFAULT NULL,
  `toEmail` varchar(50) DEFAULT NULL,
  `token` varchar(100) DEFAULT NULL,
  `validTime` int(11) DEFAULT NULL,
  `createTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

error:

[SQL] DROP TABLE IF EXISTS passwd_reset; Affected rows: 0 Time: 0.000s [Err] 1067-Invalid default value for’createTime’

reason:

The value range of timestamp type: 1970-01-01 00:00:00 to 2037-12-31 23:59:59,

Adjust the initial value to 1970-01-02 00:00:00 and it’s fine. For the time being, I don’t know how to get it into the 5.5 database.

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

[Solved] MYSQL export mysqldump version mismatch upgrade or downgrade your local MySQL client programs

 

 

I use MySQL Community Edition and I solved this problem today.

goto https://dev.mysql.com/downloads/mysql/

download the ZIP archive of your desired version

Open the ZIP archive and go to “bin” folder

extract MYSQLDUMP.EXE where you want

Close the Zip Archive and open MySQl Workbench

In MySql Workbench goto to Edit > Preferences > Administration

Select the file that you just extracted in “Path to mysqldumptool”

Press OK and it’s done!