Category Archives: MYSQL

Centos MYSQL Install Error: The GPG keys listed for the “MySQL 8.0 Community Server” repository are already installed but they are not correct for this package.

Scenario:

When installing mysql8, I didn’t encounter this problem before. This time, I suddenly encountered a new problem (maybe mysql5.7?): The GPG keys listed for the “MySQL 8.0 Community Server” repository are already installed but they are not correct for this package.

Solution:

1. Try this first:

sudo rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY*

Then yum install mysql-community-server

2. If you can’t, you can use this:

sudo yum -y install * --nogpgcheck

GPG keys are the keys generated when the installation package is released. During installation, you need to check the keys to ensure security.

[Solved] ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

After changing the environment recently, the docker reported an error when installing mysql8. The solution is as follows

Find my.cnf to modify

# Add the data
[client]
# Default character set
#default-character-set=utf8
socket= /var/run/mysqld/mysqld.sock 

[mysql]
# Default character set
#default-character-set=utf8
socket= /var/run/mysqld/mysqld.sock #### Here the original configuration is written above [client]
#socket= /storage/db/mysql/mysql.sock
[mysqld]
#
server-id=01
port=3306
## Turn on binary logging
log-bin=mysql-slave-bin
## relay_log configuration relay log
relay_log=edu-mysql-relay-bin
# Ignore case
lower_case_table_names=1

pid-file= /var/run/mysqld/mysqld.pid

socket= /var/run/mysqld/mysqld.sock
#socket= /storage/db/mysql/mysql.sock
# Database data storage directory
datadir= /var/lib/mysql

secure-file-priv= NULL

# Disabling symbolic-links is recommended to prevent assorted security risks
skip-symbolic-links=0
# Maximum number of links
max_connections=200
# Maximum number of failures
max_connect_errors=10
# Default time zone
default-time_zone='+8:00'

character-set-client-handshake=FALSE

character_set_server=utf8mb4

# default-character-set=utf8

collation-server=utf8mb4_unicode_ci

init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'

# Default authentication with 'mysql_native_password' plugin
default_authentication_plugin= mysql_native_password

# Custom config should go here
!includedir /etc/mysql/conf.d/

MySQL Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Enter the following codes in MYSQL:

set sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

Note:

if you use copy and paste, please delete the two single quotation marks and type them again, otherwise an error will be reported
if you enter them manually, there is no problem at all

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

You can’t specify target table for update in from clause in MySQL. This error means that you can’t select some values of the same table in the same SQL statement before updating the table. (not the same table)

Then execute to update the content of the first message of each user to Hello world

Because in the same SQL statement, select the minimum ID value of each user message in the message table first, and then update the message table, error 1093 (HY000): you can’t specify target table ‘message’ for update in from claim will appear.

Solution: select the result through an intermediate table again to avoid this error (including delete)

Modify after where condition

Note that only MySQL has this problem, and neither MSSQL nor Oracle has this problem.

[Solved] for user ‘root’ using method ‘mysql_native_password’ failed with message: Reading from the stream has failed

Add to the connection string:SslMode=None;

Example:

java.sql.SQLException: Error; uncategorized SQLException; SQL state [null]; error code [0];

Error Messages:
org.springframework.jdbc.UncategorizedSQLException: Error attempting to get column ‘user_photo’ from result set. Cause: java.sql.SQLException: Error; uncategorized SQLException; SQL state [null]; error code [0]; Error; nested exception is java.sql.SQLException: Error

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)

Solution:
Add two annotations to the corresponding entity class

//@Data Generate getter,setter ,toString and other functions
//@NoArgsConstructor Generate constructor without parameters
//@AllArgsConstructor //generate full-parameter constructor

MYSQL Startup Error: [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to

When using PHP to query MySQL database, the query speed is very slow. So I repeated the MySQL service on the server. As a result, the MySQL link was not available. It took several hours to solve it. Here is a summary of the solution

1. Error reporting

When I input the following startup command to start, I found that it could not be started

service mysqld start

MariaDB/MySQL cannot be started. The following error is reported in the log file:

[ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables

[ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with –with-aria-tmp-tables
Error Messages:

2. Reason

PLESK Upgrade Error，mysql Connect the database Error:


ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
...
Trying to start service mysql... failed
...
server.example.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
server.example.com systemd[1]: Failed to start MariaDB 10.2.13 database server.
server.example.com systemd[1]: Unit mariadb.service entered failed state.
server.example.com systemd[1]: mariadb.service failed.

3. Solution

SSH connect to the server and enter the data directory, you can usually set the save path in my.cnf, and you can find centos7 9 + MariaDB path is /etc/MySQL/MariaDB conf.d/server.cnf)：

Remove Aria_log_Control file:

cd /var/lib/mysql/
# Please replace aria_log_control, in case to report an error when you delete it.
mv aria_log_control aria_log_control.old
# restart
service mysqld restart

If you can start, you can ignore the following commands and update Plesk:

plesk installer --select-release-current --reinstall-patch --upgrade-installed-components

[Solved] MySQL Fail to Start Error: Error number 28 means ‘No space left on device’

Error Messages:
InnoDB: Write to file ./ib_logfile1failed at offset 1333788672, 1048576 bytes should have been written, only 786432 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means ‘No space left on device’
[ERROR] InnoDB: Cannot set log file ./ib_logfile1 to size 2048 MB
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin ‘InnoDB’ init function returned error.
[ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize builtin plugins.
[ERROR] Aborting

Cause:
Insufficient hard disk space

Solution:
Clear the hard disk space and restart MySQL service.

[Solved] An error occurred while updating the entries. See the inner exception for details.

Error message:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'StatusId'.

There is an error updating the database. The field StatusId is invalid

Solution:

After checking, it is found that the data model in the program is StatusId, and the actual database field has been changed to statusvalues

You just need to determine which field to use, make synchronous modifications and use the same field.

NodeJS Connect MYSQL Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

I’m mysql8 For versions above 0, start to report errors when connecting to the MySQL database in the server with nodejs

This means that the server starts up, but there is an error in the password protocol in the database. The result I found on the Internet tells me that it is mysql8.0 supports a new cryptographic protocol, but nodejs does not support it at present.

The following is my solution after avoiding the pit, which may not be applicable to everyone

1. Log in to MySQL server

Open the command line as an administrator and enter

mysql -u administrator -p

If you are prompted that MySQL is not an executable file, you can CD on the command line to the bin directory in mysql, or add Mysql to the environment variable

Note: the above root is the user name you want to modify. It may not be administrator. Mine is administrator

2. Enter the password

Then enter the password and enter mysql

3. Select database

Therefore, first we need to select the database and enter it on the command line

use mysql

Note: This is mysql, not any other name, unless you modify the name of the table in the database where the user name and password are stored, but the probability is very small, and you can’t move there.

If the wrong database is selected, the following errors will be prompted in the subsequent password blank operation

4. Leave password blank

If the original password is not blank and the password is modified directly, an error will be reported

Password blank operation, enter

update user set authentication_string='' where user='administrator';

The above administrator is still the user name you need to modify the password protocol

5. Refresh

This step is important

flush privileges;

6. Modify password and agreement

I’ve been stuck in this step for a long time

When I modify the password agreement, the error in the figure appears

After checking a lot of information, I understand

Open the database, MySQL database and user table, and you can see

The host before the user name I want to modify is’% ‘instead of’ localhost ‘, so I need to change the localhost in the command to%

alter user 'administrator'@'%' identified with mysql_native_password by '123456';

The above administrator is the user name you need to change your password, and 123456 is your password

This completes all steps

MySQL forgot password modification, etc. many online tutorials don’t seem to apply to MySQL 8 0. Write this part when you have time.

DebugAH

How to Solve Your Programmer Error