Tag Archives: # Mysql

[Solved] Error 1044: Access denied for user ‘syz-remote‘@‘%‘ to database ‘webapp‘

Error 1044: Access denied for user ‘syz-remote’@‘%’ to database ‘webapp’

Cause: I was testing the code of golang connecting to the database. I finally reinstalled MySQL and set up remote login, but this problem still occurred.

My SYZ-remote account is specially created for remote login. Therefore, unlike the root user, it does not have the due permission. We need to set its permissions in MySQL .

View MySQL account permissions through this code

mysql> SELECT host,user,password_expired,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+------------------+------------+------------+
| host      | user             | password_expired | Grant_priv | Super_priv |
+-----------+------------------+------------------+------------+------------+
| localhost | root             | N                | Y          | Y          |
| localhost | mysql.session    | N                | N          | Y          |
| localhost | mysql.sys        | N                | N          | N          |
| localhost | debian-sys-maint | N                | Y          | Y          |
| %         | syz-remote       | N                | N          | N          |
+-----------+------------------+------------------+------------+------------+
5 rows in set (0.00 sec)

You can see that the permission of Grant_priv and Super_priv in syz-remote is N

We gave him authority

update mysql.user set grant_priv = 'Y', Super_priv='Y' where user = 'syz-remote';

Refresh it again

flush privilegs;

[Solved] ERROR 1030 (HY000): Got error 28 from storage engine

Problem background

When logging into the database to view the table structure in Linux environment, an error is reported:

desc base_mirror;
ERROR 1030 (HY000): Got error 28 from storage engine

Most of the conclusions of Google search are the problem of insufficient disk space

View the temporary space directory of MySQL

mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name                           | Value                      |
+-----------------------------------------+----------------------------+
| basedir                                 | /usr/                      |
| binlog_direct_non_transactional_updates | OFF                        |
| character_sets_dir                      | /usr/share/mysql/charsets/ |
| datadir                                 | /var/lib/mysql/            |
| ignore_db_dirs                          |                            |
| innodb_data_home_dir                    |                            |
| innodb_log_group_home_dir               | ./                         |
| innodb_max_dirty_pages_pct              | 75                         |
| innodb_max_dirty_pages_pct_lwm          | 0                          |
| innodb_tmpdir                           |                            |
| innodb_undo_directory                   | .                          |
| lc_messages_dir                         | /usr/share/mysql/          |
| plugin_dir                              | /usr/lib64/mysql/plugin/   |
| slave_load_tmpdir                       | /tmp                       |
| tmpdir                                  | /tmp                       |
+-----------------------------------------+----------------------------+
15 rows in set (0.00 sec)

View this machine as follows:

#df -TH
Filesystem     Type      Size  Used Avail Use% Mounted on
devtmpfs       devtmpfs   17G  9.6G  7.2G  58% /dev
tmpfs          tmpfs      17G  4.1k   17G   1% /dev/shm
tmpfs          tmpfs      17G  1.5M   17G   1% /run
tmpfs          tmpfs      17G     0   17G   0% /sys/fs/cgroup
/dev/vda2      ext4       63G   63G   0G  100% /
/dev/vda1      ext4      1.1G  145M  809M  16% /boot
tmpfs          tmpfs     3.4G     0  3.4G   0% /run/user/0
overlay        overlay    63G   45G   15G  75% /home/docker/overlay/5a22b3cdbe381649e30044980fd09341ceb4460c98fd36055c4727c3c31f9f9b/merged
shm            tmpfs      68M     0   68M   0% /home/docker/containers/f61e00164f9f7d2077952af0a025670d7b88a7fb42c37c4e73d7e4c68ef3115e/shm

The overall space of the machine is still available, but the space of the root directory is insufficient, and the MySQL temporary space directory is under the root directory. You need to delete it to make space. After operation:

mysql> desc base_mirror;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| mirror_name        | varchar(100) | NO   |     | NULL    |                |
| origin_mirror_name | varchar(100) | NO   |     | NULL    |                |
| mirror_tag         | varchar(100) | NO   |     | NULL    |                |
| cloud_version      | varchar(100) | NO   |     | NULL    |                |
| archi              | varchar(100) | NO   |     | NULL    |                |
| mirror_path        | varchar(200) | NO   |     | NULL    |                |
| os_version         | varchar(100) | NO   |     | NULL    |                |
| namespace          | varchar(100) | NO   |     | NULL    |                |
| description        | longtext     | NO   |     | NULL    |                |
| upgrade_log        | longtext     | YES  |     | NULL    |                |
| create_time        | datetime(6)  | NO   |     | NULL    |                |
| gitlab_path        | varchar(200) | NO   |     | NULL    |                |
| branch_name        | varchar(200) | NO   |     | NULL    |                |
| docker_file_path   | varchar(200) | NO   |     | NULL    |                |
| Dockerfile_info    | longtext     | YES  |     | NULL    |                |
| build_log          | longtext     | YES  |     | NULL    |                |
| commit_id          | varchar(100) | NO   |     | NULL    |                |
| user               | varchar(100) | YES  |     | NULL    |                |
| is_newest          | varchar(100) | NO   |     | NULL    |                |
| is_delete          | varchar(20)  | NO   |     | NULL    |                |
| is_free            | varchar(20)  | NO   |     | NULL    |                |
| can_upgrade        | int(11)      | NO   |     | NULL    |                |
| owner              | varchar(100) | NO   |     | NULL    |                |
| imageid            | varchar(64)  | NO   |     | NULL    |                |
| mirror_sha         | varchar(255) | NO   |     | NULL    |                |
| mirror_label       | varchar(25)  | NO   |     | NULL    |                |
| is_escrow          | int(11)      | NO   |     | NULL    |                |
| openapi_taskid     | int(11)      | YES  |     | NULL    |                |
| is_recommend       | int(11)      | NO   |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
30 rows in set (0.00 sec)

Problem-solving.

MySql 8.x java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

Questions

Error in remote connection to MySQL 8.0 using mybatis plus to reverse generate entity class codegenerator

Exception in thread "main" java.lang.RuntimeException: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
	at com.baomidou.mybatisplus.generator.config.DataSourceConfig.getConn(DataSourceConfig.java:170)
	at com.baomidou.mybatisplus.generator.config.builder.ConfigBuilder.<init>(ConfigBuilder.java:110)
	at com.baomidou.mybatisplus.generator.AutoGenerator.execute(AutoGenerator.java:96)
	at com.dev.docker.config.CodeGenerator.main(CodeGenerator.java:125)
Caused by: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
	at com.baomidou.mybatisplus.generator.config.DataSourceConfig.getConn(DataSourceConfig.java:168)
	... 3 more

reason

Official website: https://mysqlconnector.net/connection-options/

         If the user account uses sha256_ Password authentication, transmission must protect the password; TLS is the preferred mechanism, but if it is not available, RSA public key encryption will be used. To specify the RSA public key of the server, use the serverrsapublickeyfile connection string setting, or set allowpublickeyretrieval = true to allow clients to automatically request the public key from the server. Note that allowpublickeyretrieval = true may allow malicious agents to perform mitm attacks to obtain plaintext passwords, so it is false by default and must be explicitly enabled.

         Because allowpublickeyretrieval and allowpublickey retrieval are false by default, it is necessary to add “allowpublickeyretrieval = true” on the JDBC connection

Solutions

Add “allowpublickeyretrieval = true” to JDBC connection

jdbc:mysql://xxxx/xxx?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true