Category Archives: MYSQL

[Solved] Linux starts MySQL service Error: Error code = exited, status = 127

phenomenon

The error reported when starting MySQL service is as follows:

[root@linmo mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Wed 2021-10-12 09:13:18 UTC; 4s ago
  Process: 1015 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=127)
  Process: 1991 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Oct 12 09:13:17 linmo systemd[1]: mysqld.service: control process exited, code=exited status=127
Oct 12 09:13:17 linmo systemd[1]: Failed to start MySQL Server.
Oct 12 09:13:17 linmo systemd[1]: Unit mysqld.service entered failed state.
Oct 12 09:13:17 linmo systemd[1]: mysqld.service failed.
Oct 12 09:13:18 linmo systemd[1]: mysqld.service holdoff time over, scheduling restart.
Oct 12 09:13:18 linmo systemd[1]: start request repeated too quickly for mysqld.service
Oct 12 09:13:18 linmo systemd[1]: Failed to start MySQL Server.
Oct 12 09:13:18 linmo systemd[1]: Unit mysqld.service entered failed state.
Oct 12 09:13:18 linmo systemd[1]: mysqld.service failed.
[root@linmo mysql]#

reason

The system lacks dependent packages: libaio, numactl;

Solution

Install missing dependent packages: libaio, numactl

# install libaio
yum install libaio
# install numactl
#wget wget http://mirror.centos.org/centos-7/7/os/x86_64/Packages/numactl-libs-2.0.9-5.el7_1.x86_64.rpm
# installl numactl
rpm -ivh numactl-libs-2.0.9-5.el7_1.x86_64.rpm
# install perl
yum isntall perl

End

MySQL Use innobackupex to backup and recovery error [How to Solve] 

$innobackupex --user=admin --password="xxxxxx" --socket=/u01/mysql/run/mysql.sock /data/backup/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
211206 10:40:54 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
211206 10:40:54 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/u01/mysql/run/mysql.sock' as 'admin' (using password: YES).
211206 10:40:54 version_check Connected to MySQL server
211206 10:40:54 version_check Executing a version check against the server...
211206 10:40:54 version_check Done.
211206 10:40:54 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: /u01/mysql/run/mysql.sock
Using server version 5.7.25-log
Warning: option 'datadir' points to nonexistent directory '/var/lib/mysql'
Warning: option 'datadir' has different values:
'/var/lib/mysql' in defaults file
'/u01/mysql/data/' in SHOW VARIABLES
innobackupex version 2.4.15 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 544842a)
xtrabackup: uses posix_fadvise().
innobackupex: Can't change dir to '/var/lib/mysql' (Errcode: 2 - No such file or directory)
xtrabackup: cannot my_setwd /var/lib/mysql

 

Solution:
MySQL instance for custom installation, modify mv /etc/my.cnf /etc/my.cnf.bk

[Solved] Springboot Project Connect MYSQL Error: Establishing SSL connection without server’s identity verification is not recommended.

Error message

Establishing SSL connection without server's identity verification is not recommended.

According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set.

For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'.

You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

 

Solution:

#Modify database connection Add useSSL=true
jdbc:mysql://localhost:3306/aa?useUnicode=true&characterEncoding=utf-8&useSSL=true

Where usessl = true means that when the JDBC version is inconsistent with the MySQL version, when using JDBC to connect to your database, your JDBC version is incompatible with the MySQL version. The MySQL version is higher. Add “usessl =” true “after the connection statement to connect to the database.

How to Sovle mysqldump backup Error on the MySQL command line

Today, when I was backing up with mysqldump, an error occurred. The following is my command for backing up the database:

mysql> mysqldump -hlocalhost -uroot -p myempoyees  student > "D:/backup/file.sql";

The following error reports occur:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL 
server version for the right syntax to use near 'mysqldump -hlocalhost -uroot -p myempoyees  student > 
"D:/backup/file.sql"' at line 1

But the command should be right

The result of online query: mysqldump is not a MySQL command, but a command line user program, which must be called by shell command line.

Execute the statement in the shell command window without entering the MySQL command

C:\Program Files\MySQL\MySQL Workbench 8.0>mysqldump -hlocalhost -uroot -p myempoyees  student >"D:/backup/file.sql"
Enter password: ******
C:\Program Files\MySQL\MySQL Workbench 8.0>

Enter the password and the backup is successful!

[Solved] Error 1054 (42s22) unknown column ‘password’ in ‘field list’ when updating the password of mysql5.7 or above

Error 1054 (42s22) unknown column ‘password’ in ‘field list’ is reported after execution

The reason for the error is: There is no password field in the MySQL database in version 5.7, and the password field is changed to authentication_string

>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> select User from user;  #This is the query user command
+-----------+
| User      |
+-----------+
| *******  |
| mysql.sys |
| root      |
+-----------+
3 rows in set (0.00 sec)

mysql> update user set password=password("*******") where user="*******";  #Change password error
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******';  #Change password error
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;  #Effective immediately
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

n>mysql -u ******* -p #Login with this user was successful.
Enter password: ********
…………………………
mysql>

sql Error: Every derived table must have its own alias [How to Solve]

Mysql database is used

 1 UPDATE tb_cr_circulate_gtsc 
 2 set path_status='A06'
 3 where
 4 order_id='Ph800601211123155507'
 5 and
 6 receive_role='21075116-001'
 7 and
 8 receive_role_name='024722cd-b197-462f-bdc2-f0423d88e580&Z&11caea25-4996-4682-aa3c-ea698e5140cc&21075116-001&四川省川东农药化工有限公司'
 9 and 
10 ( 
11 (SELECT statusSum from   (select COUNT(path_status)  as statusSum FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' ) )
12 <  
13 (SELECT statusSum from  (select COUNT(path_status)  as statusSum  FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )
14 )

Error message: each derived table must have its own alias

Solution: add an alias to the newly generated table

1 ( 
2 (SELECT statusSum from   (select COUNT(path_status)  as statusSum FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' ) as a)
3 <  
4 (SELECT statusSum from  (select COUNT(path_status)  as statusSum  FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') as a )
5 )

[Solved] sql Error: You can’t specify target table ‘tb_cr_circulate_gtsc’ for update in FROM clause

Mysql database is used

The SQL statement is as follows

 1 UPDATE tb_cr_circulate_gtsc 
 2 set path_status='A06'
 3 where
 4 order_id='Ph800601211123155507'
 5 and
 6 receive_role='21075116-001'
 7 and
 8 receive_role_name='024722cd-b197-462f-bdc2-f0423d88e580&Z&11caea25-4996-4682-aa3c-ea698e5140cc&21075116-001&四川省川东农药化工有限公司'
 9 and 
10 ((select COUNT(path_status)   FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' )
< (select COUNT(path_status) FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )

According to the online query: you cannot select some values of the same table in the same SQL statement, and then update the table.

Solution: select multiple times through an intermediate table

1 ( 
2 (SELECT statusSum from   (select COUNT(path_status)  as statusSum FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' ) )
3 <  
4 (SELECT statusSum from  (select COUNT(path_status)  as statusSum  FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )
5 )

Mac: Use brew to Install MYSQL Error

question：　　tar: Error opening archive: Failed to open ‘/Users/……

answer:
As shown in the picture, if you get an error when installing the six dependency, then install the six dependency package separately, brew install six.

—————
question:
.Logging to ‘/usr/local/var/mysql/leodeMacBook-Pro.local.err’.
ERROR! The server quit without updating PID file (/usr/local/var/mysql/leodeMacBook-Pro.local.pid).

[Solved] Install Error: mysqlclient-1.4.6-cp38-cp38-win32.whl is not a supported wheel on this platform.

Workaround, rename

First, check the matching name of PIP

View in pycharm

Open terminal in the lower sidebar and enter

pip debug --verbose

After modifying to consistent   Finally, install   Enter the installation package directory, CMD and enter pip install name

Installation succeeded

[Solved] The server time zone value ‘Öйú±ê׼ʱ¼ä’ is unrecognized or represents more than one time zone

1. Error Messages:

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_202]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_202]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_202]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_202]

2. Solution:

ADD

&serverTimezone=GMT%2B8