Tag Archives: database

[Solved] Druid connection pooling Error: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

Scene restatement

In the morning, I checked the actual operation log of the previous day of the project and found that an exception was generated in the Druid connection pool and MySQL in the springboot project. The reconnection did not affect the system temporarily
the following is the specific error message:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 178,478,007 milliseconds ago.  The last packet sent successfully to the server was 4 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:172)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1247)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2363)
	at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2481)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2360)
	at com.alibaba.druid.wall.WallFilter.statement_executeQuery(WallFilter.java:498)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2360)
	at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2481)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2360)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeQuery(StatementProxyImpl.java:211)
	at com.alibaba.druid.pool.DruidAbstractDataSource.testConnectionInternal(DruidAbstractDataSource.java:1270)
	at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:966)
	at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4544)
	at com.alibaba.druid.filter.logging.LogFilter.dataSource_getConnection(LogFilter.java:827)
	at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4540)
	at com.alibaba.druid.filter.FilterAdapter.dataSource_getConnection(FilterAdapter.java:2723)
	at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4540)
	at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection(StatFilter.java:661)
	at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4540)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:931)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:923)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:100)
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
	at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
	at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
	at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:338)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at sun.reflect.GeneratedMethodAccessor100.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	at com.sun.proxy.$Proxy83.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy91.getTerminalTotalList(Unknown Source)
	at com.tsing.demo.service.DevicesService.getTerminalTotalList(DevicesService.java:118)
	at com.tsing.demo.controller.DevicesTimeMessageController.getDeviceTotalData(DevicesTimeMessageController.java:166)
	at sun.reflect.GeneratedMethodAccessor129.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1061)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:961)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:888)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1597)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

Analyze the cause of the error

Combined with their own understanding and the analysis of others on the Internet, the positioning is as follows:

    1. since the project uses MySQL database, the “wait_timeout” of MySQL is set to 8 hours without modifying the configuration file. In other words, when a connection is idle for more than 8 hours, it will be automatically closed and recycled by mysql. We use the Druid connection pool to manage the database connection. When MySQL closes a timeout connection, the client just requests the connection from the connection pool. Because the connection pool does not know that the connection is closed by mysql, it will reassign the invalid connection, so this exception will be reported

Solution

1 from the perspective of reason, the simplest solution is to directly modify the MySQL configuration file my.ini and directly set the “wait_timeout” to a larger value, which can be set to about 24 days. However, this does not eliminate such abnormalities from the root, but delays the number and time of abnormal “explosion”
2 modify the Druid connection pool to reduce the connection cycle in the connection pool and recycle invalid connections as much as possible
make the maximum connection lifetime less than the “wait_timeout” time of MySQL.

# Configure the minimum time, in milliseconds, for a connection to survive in the pool
spring.datasource.minEvictableIdleTimeMillis=30000
spring.datasource.maxEvictableIdleTimeMillis=180000

How to Solve MySQL version 5.7+ Group by group error

MySQL-this is incompatible with sql_mode=only_full_group_By error resolution

1. Principle level
this error occurs in MySQL version 5.7 and above:

   The default sql configuration for mysql 5.7 is: sql_mode="ONLY_FULL_GROUP_BY", which strictly enforces the "SQL92 standard".

   When upgrading from 5.6 to 5.7, most of them choose to adjust sql_mode to make it consistent with 5.6 in order to be as compatible as possible with the program.

2. SQL level

    In sql execution, the cause appears.

    Simply put: the output is called target list, which is the field followed by select, and a place group by column, which is

    group by followed by the field. Because the ONLY_FULL_GROUP_BY setting is turned on, so if a field is not in the target list 

    and group by fields, or the value of the aggregation function, then this sql query is considered illegal by mysql and will report an error.

Translated with www.DeepL.com/Translator (free version)

1. View the statement of SQL_mode is as follows

select @@GLOBAL.sql_mode;

Second, the solution – (recommended solution two)
① solution one: sql statement temporarily modify sql_mode
set @@GLOBAL.sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Question.

          After restarting the mysql database service, ONLY_FULL_GROUP_BY will still appear.

② Solution 2: perfect solution.

To modify the MySQL configuration file, add SQL manually_ Mode is mandatory. Only is not required_ FULL_ GROUP_ By attribute,
VI/etc/my. CNF
Add or modify configuration file:
sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISIN_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

Restart the MySQL service and solve it successfully.

	service mysqld restart

Dbeaver Import SQL File Error: Error executing process Process failed (exit code = 1). See error log. Process failed (exit code = 1). See error log.

****Dbeaver7. Error in importing SQL file error executing process failed (exit code = 1). See error log. Process failed (exit code = 1). See error log

It may be because the import file contains the datetime type and the default table is 0. In the mysql configuration file (my.ini), check whether it contains sql_mode=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, if not, add it at the end of the document Can.

Redis Error: (error) NOAUTH Authentication required. [How to Solve]

This error is encountered in the redis interface

127.0.0.1:6379> select 0
(error) NOAUTH Authentication required.

This error is caused by lack of authorization. You can enter a password to solve it at this time.

127.0.0.1:6379> auth "your Password"

Or specify a password when accessing the ‘redis’ client

redis-cli -h 127.0.0.1 -p 6379 -a "your Password"

Ise14.7 uses its own XST synthesis Error: error: ngdbuild: 604

“ERROR:NgdBuild:604 – logical block ‘TEMPERAURE_CTRL/AD7893 ’ with type ‘ad7893’ could not be resolved. A pin name misspelling can cause this, a missing edif or ngc file, case mismatch between the block name and the edif or ngc file name, or the misspelling of a type name. Symbol ’ is not supported in target ‘virtex5’.”

Solution:
1. Connect all the ports of the top-level module
2. Connect all the inputs and outputs of the sub-module, and assign values to the outputs

[Solved] no all patern found! & Error on Generate Activation Code…

No all pattern found! And error on generate activation code

Error 1: no all pattern found!

Solution: Win + R, enter regedit

Computer found\HKEY_CURRENT_User\software\premiersoft,
delete Navicat in premiersoft and start again

Error 2: error on generate activation code

Solution: if the network is not disconnected during the operation, restart after the network is disconnected

[Solved] MySQL: Syntax error or access violation: 1055 Expression #1 of SELECT list is not …

Reason for error.
MySQL 5.7.5 and above implements detection of functional dependencies. If only_full_group_by SQL mode is enabled (which it is by default), then MySQL rejects queries referenced by select lists, conditions, or sequential lists that will refer to unnamed non-aggregated columns in the group, rather than depend on them functionally. (Prior to 5.7.5, MySQL did not detect functional dependencies, and only_full_group_by was not enabled by default. For a description of the pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
You can view the contents of sql_mode by executing the following command.

 

　　mysql> SHOW SESSION VARIABLES;

mysql> SHOW GLOBAL VARIABLES;

mysql> select @@sql_mode;

It can be seen that the value of sql_mode for both session and global is

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

only_full_group_by Description. 
only_full_group_by : use this is to use the same group rules as oracle, select the columns to be in the group, or itself is an aggregated column (SUM, AVG, MAX, MIN) only, in fact, this configuration is currently a personal feeling and distinct similar, so remove the good

Solution:
Execute the following two commands.

mysql> set global sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

mysql> set session sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

These two commands, remove the sql_mode of ONLY_FULL_GROUP_BY
However, many users say to directly modify the mysql configuration file by adding sql_mode= to my.ini

[mysqld]
sql_mode=

Huang has also tried it himself. It’s really OK!

Clickhouse error: XXXX.XXXX_local20211009 (8fdb18e9-bb4c-42d8-8fdb-18e9bb4c02d8): auto…

Code: 49, e.displayText() = DB::Exception: Part 20211009_67706_67706_0 is covered by 20211009_67118_67714_12 but should be merged into 20211009_67706_67715_1. This shouldn’t happen often., Stack trace (when copying this message, always include the lines below):
Error Messages:
XXXX.XXXX_local20211009 (8fdb18e9-bb4c-42d8-8fdb-18e9bb4c02d8): auto DB::StorageReplicatedMergeTree::processQueueEntry(ReplicatedMergeTreeQueue::SelectedEntryPtr)::(anonymous class)::operator()(DB::StorageReplicatedMergeTree::LogEntryPtr &) const: Code: 49, e.displayText() = DB::Exception: Part 20211009_67706_67706_0 is covered by 20211009_67118_67714_12 but should be merged into 20211009_67706_67715_1. This shouldn’t happen often., Stack trace (when copying this message, always include the lines below):
Solution 1.
1. Try to delete the local table and the distributed table XXXX.XXXX_local20211009

How to Solve Error: No suitable driver found for

No suitable driver found for jdbc:mysql:localhost:mysql when using JDBC to connect to MySQL database
MySQL version: 8.0.26
Change the driver to:
“jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai”

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai","root","dcc12345");
            System.out.println("数据库连接成功");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

The writing method of the new version of MySQL driver is different from that of the previous version. Jar package 8.0. * everything is common

An error of 500 is reported when an item is assigned to a role

Original: void addrolesbyuserid (string [] roleids, string userid);

After modification: void addrolesbyuserid (@ param (“roleids”) string [] roleids, @ param (“userid”) string userid);

Error reason: roleides is not found, and the roleides parameter is not detected by SQL statement

Solution: add the @ param annotation to specify the parameters of the incoming SQL statement

How to Solve Ogg start error message ogg-00014

preface

recently, when configuring Ogg two-way replication, due to improper parameter settings, an error was reported at startup. The processing methods are summarized as follows

1. Startup error

[oracle@target ogg]$ ggsci
Explanation: According to the error message, the parameter is not set properly

2. Treatment method

[oracle@target ogg]$ more ./GLOBALS
CHEMA ogg
checkpointtable ogg.rep_demo_ckpt

Switch to OGG root directory, here CHEMA ogg error, should be GGSCHEMA ogg, modify can

[oracle@target ogg]$ ggsci
Login again, everything is OK