Tag Archives: mysql

Elasticsearch imports MySQL data Error when executing the bin/logstash – f command

 

Error report 1

Problem Description:

  Unable to connect to database. Tried 1 times {:error_message=>”Java::JavaSql::SQLException: Access denied for user ‘root’@’localhost’ (using password: YES)”}

Solution:

Unable to connect to database. It is possible that the configuration file, database name or password of the imported database are incorrect (this is the wrong password)

Check the jdbc.conf file configured in the logstash decompression directory

Error report 2

Problem Description:

Logstash could not be started because there is already another instance using the configured data directory.   If you wish to run multiple instances, you must change the “path.data” setting.

Logstash cannot be started because another instance already uses the configured data directory. If you want to run multiple instances, you must change the “path. Data” setting.

Solution:

You need to specify your path.data and add it after starting the Import command   — path.data=/root/

  [ root@redis logstash-7.4.2]# bin/logstash -f /usr/local/logstash/logstash-7.4.2/jdbc.conf –path.data=/root/

How to Solve Nacos startup error

Error 1:

Unable to start web server; nested exception is org.springframework.boot.web.server.WebServerExcepti

Solution:
Open the startup file, and different operating systems open different files. Open startup.cmd on windows, open startup.sh for others, find set MODE, and change it to standalone mode.

Error 2:

Error creating bean with name ‘memoryMonitor’ defined in URL [jar: File:/D:/Nacos/nacos203/target/Nacos server. Jar!/boot-inf/lib/nacos-config-2.0.3. Jar
Solution:

Database connection If there is a problem, the correction method is as follows:
open the application.properties file in the nacos/conf/ folder , find the database connection address, pay attention to whether there is &serverTimezone=UTC after it, and add it if not.
db.url.0=jdbc:mysql: …/lamp_nacos?characterEncoding=utf8&connectTimeout=1000&socketTimeout=3000&autoReconnect=true &serverTimezone=UTC

Mapper.xml Error: Error setting non null for parameter #3 with JdbcType null.

Record the problems encountered in learning SSM framework 3
Exception Reporting
Message Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property=‘id’, mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId=‘null’, jdbcTypeName=‘null’, expression=‘null’}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
Description The server encountered an unexpected condition that prevented it from completing the request.
The problem occurs mainly in the mapper.xml file and can be due to several reasons.
1. two #’s are written together and will be identified incorrectly (my error)

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set>
            <include refid="News_update"/>
        </set>
        where id=##{id}
    </update>

2. JavaEE comments appear inside/**/

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set> /*  */
            <include refid="News_update"/>
        </set>
        where id=#{id}
    </update>

3. #{id} covered with quotation marks

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set>
            <include refid="News_update"/>
        </set>
        where id='#{id}'
    </update>

All the above detailed errors may lead to errors in the execution of SQL statements, so we should be as careful as possible in the future development process.

Solve the problem of error loading MySQL DB module. Encountered during Django project

Solve the problem of error loading MySQL DB module encountered during Django project

Python version 3.9 was used when creating the Django project. The MySQL database was successfully introduced in the process of doing the project, but later the python version was reduced to 3.7, so this error was reported when re running the system

Problem causes and solutions:
the package used by Django to connect to MySQL in Python 3 is pymysql, so the first step is to install pymysql:

pip install pymysql

Installation does not mean that it is OK. It also needs to be installed in the project__ init__. Add the following code to the. Py file:

import pymysql
pymysql.install_as_MySQLdb()

MySQL Workbench: Error Code: 1175 [How to Solve]

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.   To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Error code: 1175. You are using secure update mode, and you try to update a table that uses key columns without where. To disable safe mode, in preferences – & gt; Switch this option in the SQL editor and reconnect.

Solution: it can be solved with one statement.

SET SQL_SAFE_UPDATES = 0;

After executing the statement, execute the update operation again.

How to Solve Azkaban startup error

Environment.
One virtual machine (web and exector both on one machine)
MySQL 8.x (most of the later problems are due to him)
Hive-3.1.2
Azkaban-exec-server-3.84.4
Azkaban-web-server-3.84.4
1. SLF4J problems:
ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Class path contains multiple SLF4J bindings.

2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Class path contains multiple SLF4J bindings.
2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Found binding in [jar:file:/opt/module/azkaban/azkaban-exec/lib/slf4j-log4j12-1.7.21.jar!/org/slf4j/impl/StaticLoggerBinder.class]
2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Found binding in [jar:file:/data/soft/apache-hive-3.1.2-bin/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
2021/09/30 10:02:31.823 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

Cause: conflict with log4j package in hive
solution: delete Azkaban exec/lib/slf4j-log4j12-1.7.21.jar or add. Bak suffix
command:

cd /azkaban-exec/lib
mv slf4j-log4j12-1.7.21.jar slf4j-log4j12-1.7.21.jar.bak

2. DB connection problems(MySQL 8)
ERROR [MySQLDataSource] [Azkaban] Failed to find write-enabled DB connection.

2021/09/30 10:23:33.973 +0800 ERROR [MySQLDataSource] [Azkaban] Failed to find write-enabled DB connection. Wait 15 seconds and retry. No.Attempt = 1
java.sql.SQLException: Cannot create PoolableConnectionFactory (java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long)
        at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2294)

Reason: there is a problem with the MySQL driver package. The MySQL driver package provided by Azkaban cannot connect to MySQL 8. Just change to mysql-connector-java-5.1.47, and then a usessl error will be reported. This can be changed to MySQL. Database = Azkaban?Usessl = false in azkaban.properties. (both web and exector will report this error. Just modify it in turn)

3.
ERROR [PluginCheckerAndActionsLoader] [Azkaban] plugin path plugins/triggers doesn’t exist!
ERROR [ExecutorManager] [Azkaban] No active executors found

2021/09/30 10:47:52.799 +0800 ERROR [PluginCheckerAndActionsLoader] [Azkaban] plugin path plugins/triggers doesn't exist!
2021/09/30 10:47:52.838 +0800 INFO [AzkabanWebServer] [Azkaban] Setting timezone to Asia/Shanghai
2021/09/30 10:47:52.838 +0800 INFO [AzkabanWebServer] [Azkaban] Registering MBeans...
2021-09-30T10:47:52,869 INFO [main] azkaban.server.MBeanRegistrationManager - Bean azkaban.jmx.JmxJettyServer registered.
2021-09-30T10:47:52,871 INFO [main] azkaban.server.MBeanRegistrationManager - Bean azkaban.jmx.JmxTriggerManager registered.
2021-09-30T10:47:52,890 INFO [main] azkaban.server.MBeanRegistrationManager - Bean azkaban.jmx.JmxExecutorManager registered.
2021-09-30T10:47:52,915 INFO [main] azkaban.server.MBeanRegistrationManager - Bean org.apache.log4j.jmx.HierarchyDynamicMBean registered.
2021/09/30 10:47:52.915 +0800 INFO [AzkabanWebServer] [Azkaban] ************* loginLoggerObjName is null, make sure there is a logger with name azkaban.webapp.servlet.LoginAbstractAzkabanServlet
2021/09/30 10:47:52.916 +0800 INFO [ExecutorManager] [Azkaban] Initializing executors from database.
2021/09/30 10:47:52.941 +0800 ERROR [ExecutorManager] [Azkaban] No active executors found
2021/09/30 10:47:52.942 +0800 ERROR [StdOutErrRedirect] [Azkaban] Exception in thread "main"
2021/09/30 10:47:52.942 +0800 ERROR [StdOutErrRedirect] [Azkaban] azkaban.executor.ExecutorManagerException: No active executors found
2021/09/30 10:47:52.942 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ActiveExecutors.setupExecutors(ActiveExecutors.java:52)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ExecutorManager.setupExecutors(ExecutorManager.java:192)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ExecutorManager.initialize(ExecutorManager.java:127)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ExecutorManager.start(ExecutorManager.java:141)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.webapp.AzkabanWebServer.launch(AzkabanWebServer.java:234)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.webapp.AzkabanWebServer.main(AzkabanWebServer.java:227)

ERROR [PluginCheckerAndActionsLoader] [Azkaban] plugin path plugins/triggers doesn’t exist!
(you can refer to this.) https://blog.csdn.net/liumu243/article/details/81288884 ）
this error can be ignored or run. The main problem here is that the exector is not activated

curl -G "localhsot:12321/executor?action=activate" && echo
//Appears to prove successful activation
{status:success}

Administrative Region:
https://192.168.xx.xx:8443
http://192.168.xx.xx:8081

Error creating bean with name ‘dataSourceScriptDatabaseInitializer‘ defined in class path resource [

Error creating bean with name ‘dataSourceScriptDatabaseInitializer’ defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Unsatisfied dependency expressed through method ‘dataSourceScriptDatabaseInitializer’ parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dataSource’ defined in class path resource

Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Unsatisfied dependency expressed through method 'dataSourceScriptDatabaseInitializer' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource

The reason for my error is that I made an error when using @springboottest automatic injection when I first learned spring data JPA

@SpringBootTest

//springbootTest The server is not turned on by default,

public class MainTest {
    @Autowired
UserRepository userRepository;

@Test
    void t1(){
    System.out.println(userRepository);
}
}

I searched the Internet for several hours and debugged it. I found that the datasource was always empty, because it was tested and run in the test method at that time, and the errors given were always the same as above. However, I clearly configured the database in application.yml, and later found it during overall debugging, Failed to load driver class com.mysql.cj.jdbc.driver in either of hikariconfig class loader or thread context classloader

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

Of course, it may also be due to other errors. If you also encounter this error, you can see whether you have added MySQL dependencies to your dependencies

[Solved] mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this opera

Problem: back up the database on centos7 of the virtual machine, and an error occurs when executing mysqldump: error: ‘access denied; You need (at least one of) the process privilege (s) for this operation ‘when trying to dump tablespaces

[root@localhost backup]# sh ./mysql_backup.sh
Start exporting the database...
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
The export was successful and the file name is : /data/backup/mysql/2021-10-06_003536.sql.gz

Solution: log in to MySQL with the root account in CentOS

[root@localhost backup]# mysql -uroot -p

Input password

Then execute the command

mysql> GRANT PROCESS ON *.* TO 'demo'@'localhost';

This demo should be changed to your own login database account

Then refresh the database

mysql> flush privileges;

All execution processes:

[root@localhost backup]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 8.0.24 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> GRANT PROCESS ON *.* TO 'demo'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[root@localhost backup]# sh ./mysql_backup.sh
Start exporting the database... 
The export was successful and the file name is : /data/backup/mysql/2021-10-06_003815.sql.gz
[root@localhost backup]#

This method is accessed locally by the user

Another method is to change localhost to% by using the global access command

mysql> GRANT PROCESS ON *.* TO 'demo'@'%';

Similarly, change the demo to your own MySQL login account, and then execute the above command to refresh the database

I used the first method.

SQL editor and reconnect [an exception when MySQL (workbench) updates data] [error code: 1175]

Abnormal conditions:

  Error Code: 1175.
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect.

When using the secure update mode, the update statement must use a keyword column attribute to limit the scope of the update. If you do not use keyword columns to limit the scope or update all records, you cannot use safe update mode.

The solution is to execute the following statement to set it to non secure update mode.

#Non-secure mode
SET SQL_SAFE_UPDATES=0;
#secure mode
SET SQL_SAFE_UPDATES=1;

[Solved] Error: ER_HOST_NOT_PRIVILEGED: Host ‘x.x.x.x‘ is not allowed to connect to this MySQL server

Problem Description:

Use nodejs to connect to the MySQL database of ECs and execute the JS file. The error is as follows:

Error: ER_HOST_NOT_PRIVILEGED: Host 'x.x.x.x' is not allowed to connect to this MySQL server
{
  code: 'ER_HOST_NOT_PRIVILEGED',
  errno: 1130,
  sqlMessage: "Host 'x.x.x.x' is not allowed to connect to this MySQL server",
  sqlState: undefined,
  fatal: true
}

Solution:

This is caused by MySQL configuration that does not support remote connection. You need to connect to the server for the following configuration (log in to the root account):

mysql -u root -p
use mysql;
select host from user where user='root';
update user set host = '%' where user ='root';
// If Host = '%', it means that all IPs have connection privileges, which should be set according to the IPs of the production environment
flush privileges;

CLP: error: getaddrinfo enotfound http://x.x.x.x/

Problem Description:

Use nodejs to connect to the MySQL database of ECs and execute the JS file. The error is as follows:

Error: getaddrinfo ENOTFOUND http://x.x.x.x/
{
  errno: -3008,
  code: 'ENOTFOUND',
  syscall: 'getaddrinfo',
  hostname: 'http://x.x.x.x/',
  fatal: true
}

resolvent:

In the createconnection method, the host can write the domain name or server IP address