Tag Archives: mysql

[Solved] MYSQL Error: The user specified as a definer (‘root’@’%’) does not exist

Recently, I imported a library and found that the function reported an error. At first glance, it was a view error. At first glance, Navicat reported an error directly. The user specified as a definer (‘root’@’%’) does not exist。

Solution:

Because the person who created the view

Solution 1

If you only have the current user and you do not have the password of the root user, it is recommended that you delete the current view, copy the statement and re-establish it.

Solution 2

If you have power user privileges, enter commands

grant all privileges on *.* to root@"%" identified by ".";
flush privileges;

Replace root with the user you want to authorize.

Add one thing

For the security of the view, please supplement.

definer

When it is defined as a determiner, the user specified by the determiner must exist in the database and have the corresponding operation permission before it can be executed successfully. It has nothing to do with whether the current user has permission.

invoker

When it is defined as invoker, it can be executed successfully as long as the executor has execution permission.

MySQL Error: [Err] 1071 – Specified key was too long; max key length is 767 bytes

[Err] 1071 – Specified key was too long; max key length is 767 bytes

This will appear on mysq5 7 or below because InnoDB is not enabled_large_prefix

MySQL 5.6：

MySQL 5.7：

Simple method: if the requirement is not very high, you can also directly set the table field varchar (255) to varchar (64)

The other is to modify the configuration (the service will become invalid after restarting)

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set

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

MySQL Error: The server quit without updating PID file [How to Solve]

According to Google, there are many possible reasons for the problem. The best way to find out the specific reasons is to check the error log first:

1. The/usr/local/MySQL/data/mysql.pid file may not have write permission
solution: give permission, execute “chown – R MySQL: MySQL/var/data” “Chmod – R 755/usr/local/MySQL/data”, and then restart mysqld!

2. There may already be a MySQL process in the process
solution: use the command “PS – ef|grep mysqld” to check whether there is a mysqld process. If so, use “kill – 9 process number” to kill, and then restart mysqld!

3. It may be the second time MySQL is installed on the machine. There is residual data that affects the startup of the service
solution: go to the MySQL data directory/data. If mysql-bin.index exists, delete it as soon as possible. It is the culprit. I use the third method to solve it!

4. The/etc/my.cnf configuration file will be used when MySQL does not specify a configuration file at startup. Please open this file to see if a data directory (dataDir) is specified under the [mysqld] section
solution: please set this line under [mysqld]: dataDir =/usr/local/MySQL/data

5. Skip federated field problem
solution: check the/etc/my.cnf file for uncommented skip federated fields. If so, comment them out immediately.

6. The error log directory does not exist
solution: use the “chown” and “Chmod” commands to give MySQL owners and permissions

7. SELinux causes trouble. If it is CentOS system, SELinux will be enabled by default
solution: close it, open/etc/SELinux/config, change SELinux = enabling to SELinux = disabled, save the disk, exit and try restarting the machine.

8. In /etc/my.cnf  Add the parameter user = root in [mysqld]

Mysql database initialization password error: error: ‘access denied for user’ root ‘@’ localhost ‘(using password: Yes)’

Error reporting reason:

Password input error. If you are prompted with using password: No, it means that you need to enter a password  

Solution:

When enter password: appears, do not enter the password. Press Ctrl + C directly and you will be asked to enter a new password.