Tag Archives: mysql

MySQL reports fatal error encoded during command execution. How to solve the error

Add to the connection string

Allow User Variables=True

Because the SQL statement contains custom parameters, such as @ rownum
allow user variables = true allows users to customize parameters.

MySql Error1067：Invalid default value for ‘c_Gender‘

The following error occurred when importing the MySQL script ebuy.txt file:

Error1067、Error1146

On the command line, enter show variables like ‘% character%’; View character set

As shown in the figure above, my question is character_ set_ Database and character_ set_ server

At the command line, enter set character_ set_ database=utf8mb4;

set character_ set_ server=utf8mb4;

Then delete the original Ebuy database and run the script again.

When the springcloud obtains the cloud link database information, an error is reported: errorcode 1045, state 28000

Today, when doing the micro service exercise, put the relevant information of the linked database on the code cloud warehouse, and configure application.yml through config to pull the data; An error occurs when querying the user’s Micro service connection database

Post error message first

jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8, errorCode 1045, state 28000

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1657) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1723) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2838) ~[druid-1.2.7.jar:1.2.7]

2021-11-03 18:43:32.882 ERROR 14288 --- [eate-2036393189] com.alibaba.druid.pool.DruidDataSource   : create connection SQLException, url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8, errorCode 1045, state 28000

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1657) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1723) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2838) ~[druid-1.2.7.jar:1.2.7]

2021-11-03 18:43:32.883  INFO 14288 --- [eate-2036393189] c.a.druid.pool.DruidAbstractDataSource   : {dataSource-1} failContinuous is true
2021-11-03 18:43:33.397 ERROR 14288 --- [eate-2036393189] com.alibaba.druid.pool.DruidDataSource   : create connection SQLException, url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8, errorCode 1045, state 28000

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1657) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1723) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2838) ~[druid-1.2.7.jar:1.2.7]

2021-11-03 18:43:33.908 ERROR 14288 --- [eate-2036393189] com.alibaba.druid.pool.DruidDataSource   : create connection SQLException, url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8, errorCode 1045, state 28000

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1657) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1723) ~[druid-1.2.7.jar:1.2.7]
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2838) ~[druid-1.2.7.jar:1.2.7]

2021-11-03 18:43:34.420 ERROR 14288 --- [eate-2036393189] com.alibaba.druid.pool.DruidDataSource   : create connection SQLException, url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8, errorCode 1045, state 28000

Config:

server:
  port: 9100
spring:
  application:
    name: config-server
  cloud:
    config:
      server:
        git:
          uri: https://gitee.com/shangdigao/shop1103
          username: [email protected]
          password: GYH000909
eureka:
  client:
    service-url:
      defaultZone: http://localhost:8761/eureka

Member (user micro service)

eureka:
  client:
    service-url:
      defaultZone: http://localhost:8761/eureka
spring:
  application:
    name: member-server
  cloud:
    config:
      discovery:
        enabled: true
        service-id: config-server
      label: master
      name: member-server

Code cloud:

According to the error report, it is found that there is a problem with the user name and password when connecting to the database. After many attempts, it is found that the password pulled by config from the code cloud is incorrect

It is possible that the 0 at the beginning of the password was not obtained

Change the database login password to 123456

Run successfully

[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:

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

Docker starts MySQL container and reports an error driver failed programming external connectivity on endpoint mysq

Look at the error first:

The mistake is like the above. At first, I thought my command was wrong or something else, but later I found it was not

But it’s still troubleshooting one by one.
first, the command to create a MySQL container:
docker create – P 3306:3306 — name of the container – e mysql_ ROOT_ Password = database password MySQL version:

After creation, enter the command: docker PS – a
to view all containers

Then you can see the MySQL you just named

Start command:
docker start container ID

Then an error will be reported. If an error is reported, please see the following:

Problem solving:

The first is to restart the docker. It is useless for me to restart anyway. The restart command:
systemctl restart docker
it is also possible that you will be OK after restarting

The second is to view all containers through the docker PS – a command. If you have created MySQL before, when you create a MySQL container again, you need to change an external port, not 3306. Ensure that the port and name are different from the MySQL container created before

You can also use the previous MySQL container and start it directly through the docker start container ID

“2003” error reported by sqlyog for Linux remote connection solution

Just now xsell tried to connect to the database, but it couldn’t do anything. The error is reported: 2003, as shown in the following figure:

In xshell ① After closing the Linux Firewall ② Start MySQL service ③ After logging in with root, you need to authorize root, as shown in the figure below.

Then it succeeded!

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.

[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!

[Solved] Sequelize DatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP

Error report after project deployment

SequelizeDatabaseError: ER_ WRONG_ FIELD_ WITH_ GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘mysql.category.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_ full_ group_ by

To put it simply, the output result is called target list, which is the field followed by select. There is another place, group by column, which is the field followed by group by. Because only_FULL_GROUP_By is turned on set, so if a field does not appear in the target list and group by fields at the same time, or the value of the aggregate function, the SQL query is considered illegal by MySQL and an error will be reported.

Error source code:

// Find All
const categoryAll = async (ctx) => {
  const resData = await Category.findAll({
    attributes: ['name', 'id', [sequelize.fn('COUNT', sequelize.col('name')), 'count']],
    // group: 'name', // Key
    where: {
      articleId: { [Op.not]: null }
    },
    order: [[sequelize.fn('COUNT', sequelize.col('name')), 'desc']]
  })
  ctx.body = successResult(resData)
}

Solution:

Global SQL modification is mentioned in several reference articles_ Mode, but I failed to modify it with this method. Even if it succeeds, it is temporary and will reappear after MySQL is restarted
so I chose another method to modify the MySQL configuration file and add SQL manually_Mode is mandatory. Only_FULL_GROUP_By is not required property
1. Confirm which configuration file MySQL loads. You can use the following methods to confirm

mysql –verbose –help | grep my.cnf

Directory where my configuration is located:

2. Modify the configuration and add the following content at the end of the configuration file

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note: there is no keyword [mysqld] in my configuration file. If there is no keyword, an error will be reported
then restart MySQL.

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

ProgrammerAH

Programmer Guide, Tips and Tutorial