Error:
21:55:14 update users set realname=‘Lisi2’ where username=‘ls’ 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. 0.000
sec
Solution:
Execution of this statement will result in: SET SQL_SAFE_UPDATES=0;
Today, when I was learning mybatis, I always reported an error when executing the query operation! The error is as follows:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
It probably means that there is a problem with the database connection. After checking the database name, user name and password, there are no errors. Some blogs say that they need to change the MySQL configuration file. I really think it’s too troublesome to change
Solution:
A blog commented that if MySQL is version 8.0 or above, usessl in the configuration should be set to false
and then it will be
useSSL=true
# Error Message:
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
### The error may exist in com/dao/BookMapper.xml
### The error may involve com.dao.BookMapper.queryAllBook
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
java.sql.SQLException: Access denied for user 'Charles'@'localhost' (using password: YES)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
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:835)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Cause analysis:
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC
username = root
password = admin
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<!-- Database connection -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<context:property-placeholder location="classpath:database.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${driver}"/>
<property name="jdbcUrl" value="${url}"/>
<property name="user" value="${username}"/>
<property name="password" value="${password}"/>
<property name="maxPoolSize" value="30"/>
<property name="minPoolSize" value="10"/>
<property name="autoCommitOnClose" value="false"/>
<property name="checkoutTimeout" value="10000"/>
<property name="acquireRetryAttempts" value="2"/>
</bean>
There is also a prompt when the cursor is placed on the value
online solutions have been tried and have not taken effect
Solution:
Finally, I accidentally tried to explicitly assign the attribute of the database connection pool, and the connection was successful
1. Exception description
org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException: Write conflict, txnStartTS=426985930345676879,
After switching the tidb database, the above error reports occur from time to time;
2. Problem analysis
It is found that there is a write conflict by searching the online data according to the error content. The optimistic lock is used by default under version 3.0 of tidb database. This problem will occur in the case of multiple concurrent modifications;
3. Solution
(1) Add & amp; database connection; sessionVariables=tidb_ txn_ Change mode = pessimistic to pessimistic lock
(2) Break big things apart
4. Subsequent optimization selection
1、 Mybatisplus only configures mapper in YML, and the path scanning is not enough
mapper-locations: classpath:com/jack/shale_porosity/mapper/xml/*.xml
In this way, only the basemapper provided by mybatis can be used. The mapper file is not loaded in the target file.
2、 Configure the mapper file scanning path again in the pom.xml file
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
In this way, you can successfully use custom SQL and SQL in basemapper.
Profile:
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
spring.datasource.dbcp2.connection-init-sqls=SET NAMES utf8mb4
pom
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
<scope>runtime</scope>
</dependency>
Existing problem:
an error is reported during MySQL 6.0 installation: start service failed, as shown in the figure
Solution:
you need to delete the relevant registry and reinstall it
1) uninstall MySQL that failed to install, uninstall MySQL in control panel → programs and functions, and delete relevant folders and files under the installation path
2) delete the MySQL related directory under the programdata directory on disk C. if it cannot be found, you need to “show hidden folder” operation
3) delete the relevant registry
(1) start → run → CMD → regedit to enter the registry list
(2) delete the following three places in sequence:
HKEY_ LOCAL_ MySQL directory under machine/system/controlset001/services/eventlog/applications; HKEY_ LOCAL_ MySQL directory under machine/system/controlset002/services/eventlog/applications; HKEY_ LOCAL_ MySQL directory under machine/system/currentcontrolset/services/eventlog/applications.
4) Restart the computer and reinstall mysql6.0.
Reason: mysql5 defaults the wait_timeout of its connection to 8 hours
modify the configuration file. The steps are as follows:
【1】 Log in to MySQL and CMD
MySQL – H host address – U user name – P user password (for example, MySQL – hlocalhost – uroot – p123456)
【2】 View wait_ Timeout, CMD command:
show global variables like ‘wait_ timeout’;
[3] if wait_ Timeout is too small to be modified. CMD command:
set global wait_ timeout=604800;
set global interactive_ timeout=604800;
after modification, you can test.
First, when executing the load data command, a message appears, loading local data is disabled; this must be enabled on both the client and server sides
Set needs to be set on the server side local_infile=1
And set MySQL — local infile = 1 – uroot – P when connecting to the client
The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
The mysql.ini file already exists in the installed MySQL directory. You need to create a new my.ini file and add
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
secure-file-priv=