Tag Archives: mysql

The problem of MySQL database connection failure errno: 1049 error

Today, I use phpstudy to open the MySQL database, and then connect to the database failed: the report is 1049, which means that the database does not exist.

The error is as follows
. In this CMD window, you can go all the way up to the time when you just connected the data

to see if your database name is right, but my database name is wrong
config file, which contains the

default.json file. There is a

database, fill in your own correct database name in the database, and then connect to the database after saving it

this way means success, and I hope it will help you with your mistakes!!!

Cannot resolve classpath entry: e://jar/mysql-connector-java-5.1.17-bin mybatis error in reverse engineering

error: failed: Cannot resolve classpath entry: E:\jar\mysql-connector-java-5.1.17-bin -> [Help 1]
“
[INFO] Scanning for projects…
[INFO]
[INFO] ————————–< pay-center:demo >—————————
[INFO] Building demo 0.0.1-SNAPSHOT
[INFO] ——————————–[ jar ]———————————
[INFO]
[INFO] — mybatis-generator-maven-plugin:1.3.2:generate (default-cli) @ demo —
[INFO] ————————————————————————
[INFO] BUILD FAILURE
[INFO] ————————————————————————
[INFO] Total time: 1.043 s
[INFO] Finished at: 2021-07-01T10:31:26+08:00
[INFO] ————————————————————————
[ERROR] Failed to execute goal org.mybatis.generator:mybatis-generator-maven-plugin:1.3.2:generate (default-cli) on project demo: Execution default-cli of goal org.mybatis.generator:mybatis-generator-maven-plugin:1.3.2:generate failed: Cannot resolve classpath entry: E:\jar\mysql-connector-java-5.1.17-bin -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/PluginExecutionException
My generatorConfig.xml file looks like this, showing only the parts.



My generatorConfig.xml file looks like this, showing only the parts.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration>


    <!-- Database driver: select the database driver package on your local hard disk-->
    <classPathEntry  location="E:\jar\mysql-connector-java-5.1.17-bin"/>

    <context id="DB2Tables"  targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <!-- Whether to remove automatically generated comments true: yes : false:no -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>



This database driver cannot be found problem occurs, go directly to the database driver to delete. The following figure shows the comparison of the two figures
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration>

    

    <context id="DB2Tables"  targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <!-- Whether to remove automatically generated comments true: yes : false:no -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>

[Solved] error: Failed dependencies libcrypto.so.10()(64bit) is needed, libssl.so.10()(64bit) is needed

Premise: I install percona-server-shared-compat-8.0.23-14.1.el8.x86 in percona server for MySQL 8.0 bundle on CentOS 8_ 64. RPM package, even if the latest version of OpenSSL (version: 1:1.1.1g-15.el8) is installed_ 3) It doesn’t help

error:Failed Dependencies
		libcrypto.so.10()(64bit) is needed by percona-server-shared-compat-8.0.23-14.1.el8.x86_64.rpm
		libcrypto.so.10(libcrypto.so.10)(64bit) is needed by percona-server-shared-compat-8.0.23-14.1.el8.x86_64.rpm
		libssl.so.10()(64bit) is needed by percona-server-shared-compat-8.0.23-14.1.el8.x86_64.rpm
		libssl.so.10(libssl.so.10)(64bit) is needed by percona-server-shared-compat-8.0.23-14.1.el8.x86_64.rpm

Process : Found the library when googling
https://pkgs.org/download/libcrypto.so.10()(64bit)
I found compat-openssl10 and an explanation to the effect that compat-openssl10 contains only libraries and provides compatibility with earlier versions and software that does not support compilation with OpenSSL-1.1.

The OpenSSL toolkit provides support for secure communications between machines. This version of OpenSSL package contains only the libraries and is provided for compatibility with previous releases and software that does not support compilation with OpenSSL-1.1.

From stackoverflow, we can find that openssl provides libcrypto.so.10 without the module name, making the system think that the dependency is missing

In your case, openssl seems to provide only libcrypto.so.10 without any module name, making geramer-server believe that dependency is missing as it requires libcrypto.so.10 from module libcrypto.so.10.

https://stackoverflow.com/questions/20518183/dependency-resolution-fails-on-installed-library

Solution:
yum install compat-openssl10
or
yum install http://mirror.centos.org/centos/8/AppStream/x86_64/os/Packages/compat-openssl10-1.0.2o-3.el8.x86_64.rpm

Result:

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communicat

Solution: when connecting to the database, change usessl = true to usessl = false. Of course, if usessl = true is not written, it is true by default.

Note: however, I don’t know why I did it. What’s more, the tutorial I read only uses true, and why I report an error. If a boss sees it, he hopes to answer it.

MySQL character set error resolution: err=Error 3988: Conversion from collation utf8mb4_unicode_ci into utf8_general_ci impo

The content of a field contains expressions. When inserting mysql, you will be prompted with an error:

err=Error 3988: Conversion from collation utf8mb4_unicode_ci into utf8_general_ci impossible for parameter

solve:

1. Modify MySQL character set

I am using docker

  1. Modify mysql.cnfmaps to docker where /etc/mysql/mysql.cnf:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
      1. start command parameter addition:
 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

After restarting the container, enter Mysql to view:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
| collation_connection     | utf8mb4_unicode_ci             |
| collation_database       | utf8mb4_unicode_ci             |
| collation_server         | utf8mb4_unicode_ci             |
+--------------------------+--------------------------------+
11 rows in set (0.17 sec)

2. Modify the character set of table

        1. modify the character set of a specific table:
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
          1. modify the character set of a specific field:
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci

An error occurred when MySQL backup with mysqldump instruction in MacOS system

Error 1064 (42000) from mysqldump command

Mysqldump – uroot – P library name & gt/ Backup path/backup file name. SQL;

Always report error:

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 -uroot -p bank>/ Users/F/bank_ back.sql’ at line 1

resolvent:

Input mysqldump command directly in the terminal, do not input mysqldump command after logging into the database.

Special thanks to the original author guanggulala.

You have an error in your SQL syntax； check the manual that corresponds to your MySQL server version

    if your MySQL is copied, please replace the Chinese related spaces, Chinese commas and Chinese ‘with English
    this is my error: [2021-06-29 09:30:55.391] [MySQL] [000071] [MySQL] you have an error in your SQL syntax; Check the manual that responses to your MySQL server version for the right syntax to use near 'insert to Dept (ID, name, PID) values ('1000','head office ',') 'at line 1

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

Python: Django exception when executing migrate operation: django.db.utils.OperationalError: (1051, “Unknown table’xxx’”)

Problem scenario:    When executing Python manage.py migrate, an error is reported (1051, “unknown table ‘xxx’),   At this time, there is no ‘xxx’ table in the database

Solution:
1. Comment out the tables in models.py that you failed to update

2. Execute after comments are dropped

python manage.py makemigrations

python manage.py migrate --fake

3. After the above execution is successful, cancel the table annotation in models.py

4. Cancel the comment and execute

python manage.py makemigrations

python manage.py migrate

[Solved] Description: Failed to configure a DataSource: ‘url‘ attribute is not specified and no embedded

Description:
Failed to configure a DataSource: ‘url’ attribute is not specified and no embedded datasource could be configured.
Reason: Failed to determine a suitable driver class
Action:
Consider the following:
If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).
My database configuration file is application.properties. after reading it seems to be unreadable, I recreate the application.yml file again, just copy the configuration file to this side yml. restart it and it’s fine.

server:
  port: 8080
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/springcloud?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    username: root
    password: 123456

I don’t know why.

[Solved] MySQL5.6.44 [Err] 1067 – Invalid default value for create_date settlement programme

Scenario
[Err] 1067 – Invalid default value for ‘create_date’, for the create table species statement as follows.
`create_date` timestamp(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT ‘creation time’ SOLVED
MySQL5.6.44 and MySQL5.7.27 timestamp set default rule changed, not “0000 00-00 00:00:00″
Solution:
Check sql_mode:
mysql> show session variables like ‘%sql_mode%’;
+—————+——————————————————————————————————————————————-+
| Variable_name | Value                                                                                                                                     |
+—————+——————————————————————————————————————————————-+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+—————+——————————————————————————————————————————————-+
1 row in set (0.01 sec)
change sql_mode, remove NO_ZERO_IN_DATE,NO_ZERO_DATE:
mysql> set sql_mode=”ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”;

Solution to javax.naming.noinitialcontextexception error

When using DBCP to configure data sources, a test class was written. In the test class, DBCP was called to get the database connection error: javax.naming.NoInitialContextException: Need to specify class name in environment or or in,

Check whether the dependent jar package is imported:
commons-dbcp2-2.8.0. Jar
commons-pool2-2.10.0. Jar
mysql8.0 jar package
mysql5.0 jar package

Test code:

package test;

import org.junit.Test;
import tools.DataSourceUtil;

import java.sql.Connection;

public class test {
    @Test
    public void test(){
        Connection connection = DataSourceUtil.getConnection();
        System.out.println(connection);
    }
}

Database source code:

package tools;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class DataSourceUtil {

    // Get the connection
    public static Connection getConnection() {
        // ctrl + alt + t --- select 6
        // instantiate the object of the initial context
        Connection connection = null;
        try {
            InitialContext initialContext = new InitialContext();
            // Get the data in the configuration and get the object object
            Object lookup = initialContext.lookup("java:comp/env/jdbc/easybuy");
            // unboxing strong turn to get the connection pool
            DataSource ds = (DataSource) lookup;
            // get the connection pool to give me a connection
            connection = ds.getConnection();
        } catch (NamingException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // Return to link
        return connection;
    }

    // close connection
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Bug map:

reason:

When DBCP is used to connect to the database, the main function is always used to test. The initialcontext is only available in the context of the web application server. While the configuration file is in the web directory, it is obvious that if initialcontext wants to access the configuration file, it must run on the web server to connect to the database and get the connection object

It can’t be tested directly with the main function, it can only be displayed in Tomcat or servlet or JSP

Running results:

#Successfully get the connection object!