Category Archives: MySQL

[Solved] Error response from daemon: driver failed programming external connectivity on endpoint mysql

Error response from daemon: driver failed programming external connectivity on endpoint mysql (0b3324391f54cd0137ff7a9da6ed145656b187dfb984d507ca62252a54d47245): (iptables failed: iptables –wait -t nat -A DOCKER -p tcp -d 0/0 –dport 3306 -j DNAT –to- destination 172.17.0.2:3306 ! -i docker0: iptables: No chain/target/match by that name.

 

Error reproduction: After starting mysql, close the firewall, and then restart mysql (docker restart mysql), the above error occurs.

Reason: After we start Docker, we operate the firewall firewalld, and the above error will occur

Solution: restart docker (systemctl restart docker), restart docker service and regenerate custom chain DOCKER

Why can it be solved:

The custom chain DOCKER defined when the docker service starts, when the centos7 firewall is cleared,

The bottom layer of firewall is to use iptables for data filtering, which is built on top of iptables, which may conflict with Docker.

firewalld Rules that will be removed from iptables  when  starting or restarting DOCKER , thus affecting the normal working of Docker.

When you are using Systemd, it  firewalld will start before Docker, but if you operate after Docker has started  firewalld , you will need to restart the Docker process.

[Solved] FATAL ERROR: please install the following Perl modules before executing /application/mysql/scripts/mysql_install_db: Data::Dumper

I got an error when initializing mysql:

FATAL ERROR: please install the following Perl modules before executing /application/mysql/scripts/mysql_install_db:
Data::Dumper

 

reason:

Missing Data::Dumper in perl module

 

solution:

Install the autoconf library (the Data:Dumper module will be installed when this package is installed)

yum -y install autoconf

[Solved] MySQL Error: “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”

Project scene:

Recently, after deploying the project, an error occurred when running:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘grades.order_id’ 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

Problem Description

Using the GROUP BY statement violates sql_mode=only_full_group_by. Because the default mode after mysql version 5.7 is ONLY_FULL_GROUP_BY.


Cause Analysis:

Check the official documentation and find that starting from MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL did not detect functional dependencies, and ONLY_FULL_GROUP_BY was not enabled by default.) This may cause some sql statements to fail.


solution:

Execute the command vim /etc/mysql/conf.d/mysql.cnfto modify the configuration file

If there is sql_mode configuration in my.cnf, remove ONLY_FULL_GROUP_BY.

If not, put the following content in the corresponding place

[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
BASHcopyfull screen

After saving, execute the command to service mysql restartrestart mysql.

[Solved] EOS7.6 Error: Init DB failed [Specified key was too long; max key length is 767 bytes…

Init DB failed![Specified key was too long; max key length is 767 bytes[CREATE INDEX IDX_WFWI_PARTICI ON WFWORKITEM( PARTICIPANT )]
Specified key was too long; max key length is 767 bytes[CREATE INDEX WTRI_EXPAT ON WFTASKRESOURCEINFO(EXCLUDEUNIQUEID)]
Specified key was too long; max key length is 767 bytes[CREATE INDEX WTRI_COMP ON WFTASKRESOURCEINFO(QUEUENAME,STATUS,SERVERID)]
]


– select 'yes' for retry and' no 'for exit

 

If the system variable innodb_large_prefix is enabled (enabled by default, it is off by default on my computer installation of MySQL 5.6.41, and on by default on MySQL 5.7), the index key prefix is limited to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix is limited to 767 bytes for any table in row format.

innodb_large_prefix will be removed and deprecated in future releases. The innodb_large_prefix was introduced in MySQL 5.5 to disable large prefix indexes for compatibility with earlier versions of InnoDB that did not support large index key prefixes.

For InnoDB tables using REDUNDANT or COMPACT row formats, the index key prefix length is limited to 767 bytes. For example, you might reach this limit using a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and a maximum of 3 bytes per character.

Attempts to use an index key prefix length that exceeds the limit will return an error. To avoid such errors in replication configurations, avoid enabling enableinnodb_large_prefix on the master server (if it cannot be enabled on the slave server).

The restrictions that apply to index key prefixes also apply to full column index keys.

Note: The above is 767 bytes, not characters, specifically the number of characters, which is related to the character set. gbk is double-byte, utf-8 is triple-byte

Solutions.

1: Enable the system variable innodb_large_prefix
Note: It is not enough to have this system variable enabled. The following conditions must be met.

2: System variable innodb_large_prefix is ON

3: System variable innodb_file_format is Barracuda

4: ROW_FORMAT is DYNAMIC or COMPRESSED

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%innodb_file_format%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+
3 rows in set (0.00 sec)

mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_format_max=BARRACUDA;
Query OK, 0 rows affected (0.01 sec)

After completing the above operations, EOS platform 7.6 is successfully installed.

[Solved] tidb-cdc Create Task Error: Unknown or incorrect time zone

1. Error reporting details

fail to open MySQL connection: [CDC:ErrMySQLConnectionError]Error 1298: Unknown or incorrect time zone: 'Asia/Shanghai'

2. Troubleshooting

# Login tidb and check the time zone
show variables like '%time_zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Asia/Shanghai |
| time_zone        | SYSTEM        |
+------------------+---------------+
# login mysql and check the time zone
show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

It can be found that the time zone of the upstream tidb is North America/USA, while the time zone of the downstream MySQL is CST

3. Solution

Method 1: load the time zone

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

Method 2: write sink to upstream and downstream time zones

cdc cli changefeed create --sink-uri="mysql://user:password@mysql_ip:mysql_port/?time-zone=CST" --pd=http://pd_ip:pd_port

[Solved] error {dataSource-1} init error java.sql.SQLException: com.mysql.cj.jdbc.Driver

error {dataSource-1} init error java.sql.SQLException: com.mysql.cj.jdbc.Driver

July 08, 2019 2:39:14 PM com.alibaba.druid.pool.DruidDataSource error
WARNING:error {dataSource-1} init error java.sql.SQLException: com.mysql.cj.jdbc.Driver
	at com.alibaba.druid.util.JdbcUtils.createDriver(JdbcUtils.java:520)
	at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:583)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:915)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:911)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:98)
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:598)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:655)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:686)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:698)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:776)
	at edu.haizi.dao.UserDao.login(UserDao.java:17)
	at edu.haizi.test.UserDaoTest.testLogin(UserDaoTest.java:15)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at com.alibaba.druid.util.JdbcUtils.createDriver(JdbcUtils.java:518)
	... 36 more

July 08, 2019 2:39:14 PM com.alibaba.druid.pool.DruidDataSource info
MESSAGES:{dataSource-1} inited

org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: com.mysql.cj.jdbc.Driver

	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:598)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:655)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:686)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:698)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:776)
	at edu.haizi.dao.UserDao.login(UserDao.java:17)
	at edu.haizi.test.UserDaoTest.testLogin(UserDaoTest.java:15)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.sql.SQLException: com.mysql.cj.jdbc.Driver
	at com.alibaba.druid.util.JdbcUtils.createDriver(JdbcUtils.java:520)
	at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:583)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:915)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:911)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:98)
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
	... 29 more
Caused by: java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at com.alibaba.druid.util.JdbcUtils.createDriver(JdbcUtils.java:518)
	... 36 more


Process finished with exit code -1

Solution: the version of MySQL under windows is higher than idea, which makes the version incompatible

Modify Maven dependency package

Original version 5.1.9

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

Modify to a later version

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

[Solved] pymysql.err.ProgrammingError: (1064, ‘You have an error in your SQL syntax;

[Solved] pymysql.err.ProgrammingError: (1064, ‘You have an error in your SQL syntax;

Purpose

Use pymysql to insert data into the MySQL database.

Abnormal information

code

sql_test = '''INSERT INTO "es_site" ("id", "site_name", "site_role", "url", "password", "username") VALUES (1, 'test', 'test', 'https://test.com', 'passwd', 'estest');'''
cur.execute(sql_test)
conn.commit()
cur.close()
conn.close()

Solution

The field name in sql cannot be enclosed in quotation marks. Just remove the quotation marks of the field and table name

[Solved] Error Code: 2013. Lost connection to MySQL server during query

Error Code: 2013. Lost connection to MySQL server during query

Today, when using the official MySQL graphical tool mysqlworkbench to query the view, this error is reported:
error code: 2013 Lost connection to MySQL server during query

If the disk is not full, you can use the following method:

Modify the timeout in the red box. The default is 10 seconds. If the table data is too large and the query time is too long, the above error will be returned.

[Solved] Error response from daemon: driver failed programming external connectivity on endpoint mysql

Error response from daemon: driver failed programming external connectivity on endpoint mysql

docker command:
docker start container_name/id
Container Start Error:

Error response from daemon: driver failed programming external connectivity on endpoint mysql (cf1ba9f9e0613e14f42332d187a51429f8213aaf91d775f2ec3600614c78e6e1): (iptables failed: iptables --wait -t nat -A DOCKER -p tcp -d 0/0 --dport 3306 -j DNAT --to-destination 172.17.0.2:3306 ! -i docker0: iptables: No chain/target/match by that name.
(exit status 1))
Error: failed to start containers: mysql

 

Solution: restart docker:systemctl restart docker

https://blog.csdn.net/qq_45652428/article/details/124870923

[Solved] mysql-community-server Install Error: error: Failed dependenice…

mysql-community-server Install Error: error: Failed dependenice…

1. Problems encountered

There is no problem installing MySQL community common, MySQL community LIBS and MySQL community client, but there is an error when installing MySQL community server. There is a lack of libaio dependency

2. Solve problems

Execute the command: yum - y install libaio
execute the command: rpm -ivh mysql-community-server-5.7.38-1.el7.x86_64.rpm

How to Solve Error: Rsa Public Key not Find

Error: Rsa Public Key not Find

Navicat for MySQL is a graphical tool for MySQL.

Resolve RSA public key not find

I found that the reason for this problem is that I didn’t put keyword under the installation path of graphical tools
solution: copy keygen to the installation directory of graphical tools.

No all pattern found! File Already Patched?

This prompt appears because you have previously registered with keygen on your computer and cannot re register
solution:
1 Use Win + R to input regedit instruction to edit the registry

2. Find HKEY in the registry_CURRENT_User\software\premiersoft, and delete the directory
3. Reinstall the graphical tool.

Registration steps

premise: do not open it after successful installation
1. Copy keygen to the installation directory of graphical tools
2. Select MySQL , click patch , and a registered patch will be generated in the installation directory
3. Click generate in the corresponding column of keyword to generate a serial number. At this time, first disable the network or disconnect the network , open the graphical tool, select register , fill in the serial number, click activate, and then select to activate manually
4. At this time, a string of request codes will be generated. Paste them into keyword , and click generate in the corresponding column of activation code to generate the activation code
5. Paste the activation code into the graphical tool, and the registration is successful.