When this problem occurs in MySQL database:
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.
MySQL has entered the safe mode, which is the safe updates mode
Solution:
SHOW VARIABLES like 'sql_safe_updates'; # Query the status of the current safe-updates mode
SET sql_safe_updates=0; # Turn off safe-updates mode
Error message:
2022-02-08 14:17:32.022 INFO 1816 --- [nio-8080-exec-3] com. alibaba. druid. pool. DruidDataSource : {dataSource-1} inited
2022-02-08 14:17:32.110 ERROR 1816 --- [eate-1764881818] com. alibaba. druid. pool. DruidDataSource : create connection SQLException, url: jdbc: mysql://127.0.0.1:3306/itqs?allowMultiQueries=true , errorCode 1045, state 28000
As shown below:
Reason: application The MySQL database user name or password in the properties file is incorrect.
Solution: change to the correct user name or password.
1. Error information
Feb 02, 2022 10:44:44 AM com.alibaba.druid.pool.DruidDataSource error
WARNING: init datasource error, url: jdbc:mysql://localhost:3306/xuesheng?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
java.sql.SQLException: Access denied for user ''@'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:836)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1570)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1636)
at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:874)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1246)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1242)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
at com.qzxiaofeng.jdbc.DruidDemo.main(DruidDemo.java:21)
Feb 02, 2022 10:44:44 AM com.alibaba.druid.pool.DruidDataSource error
WARNING: {dataSource-1} init error
java.sql.SQLException: Access denied for user ''@'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:836)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1570)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1636)
at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:874)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1246)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1242)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
at com.qzxiaofeng.jdbc.DruidDemo.main(DruidDemo.java:21)
Feb 02, 2022 10:44:44 AM com.alibaba.druid.pool.DruidDataSource info
WARNING: {dataSource-1} inited
Exception in thread "main" java.sql.SQLException: Access denied for user ''@'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:836)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1570)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1636)
at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:874)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1246)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1242)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
at com.qzxiaofeng.jdbc.DruidDemo.main(DruidDemo.java:21)
II My configuration is as follows
1 Test code
public class DruidDemo {
public static void main(String[] args) throws Exception {
//3.Load the configuration file
Properties prop=new Properties();
prop.load(new FileInputStream("src/druid.properties"));
//4. Get the connection pool object
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. Get the corresponding database connection
Connection connection = dataSource.getConnection();
System.out.println(connection);
// System.out.println(System.getProperty("user.dir"));
}
}
2. Configuration of guide package in maven
<!-- druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
3.druid.properties configuration
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xuesheng?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
user=root
password=123456
# initialize the number of connections
initialSize=5
#Maximum number of connections
maxActive=10
#Maximum waiting time
maxWait=3000
3. Solution
Change the user to username in druid.properties
InnoDB restrictions
1. a table cannot contain more than 1000 columns.
2. The maximum internal key length is 3500 bytes, but MySQL itself limits this to 1024 bytes.
3. except for VARCHAR, BLOB and TEXT columns, the maximum row length is slightly less than half of the database page. That is, the maximum row length is about 8000 bytes. longBLOB and longTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB. innoDB stores the first 768 bytes of VARCHAR, BLOB or TEXT columns in rows, and the rest is stored in scattered pages.
4. The default database page size in InnoDB is 16KB.
Solution 1: replace the engine
ALTER TABLE tableName ENGINE = MyISAM;
Springboot reports error creating bean with name ‘datasource’ defined in class path resource, factory method ‘datasource’ three exception; nested exception is org.springframework.boot.autoconfigure.jdbc.Datasourceproperties $datasourcebeancreationexception: failed to determine a suitabledriver class solution
Problem background solution experience Lyric: cut me apart from two spirits
Problem background:
An error is reported when connecting to MySQL:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.zaxxer.hikari.HikariDataSource]: Factory method 'dataSource' threw exception; nested exception is org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException: Failed to determine a suitable driver class
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:658)
org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:732)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:414)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:302)
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.zaxxer.hikari.HikariDataSource]: Factory method 'dataSource' threw exception; nested exception is org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException: Failed to determine a suitable driver class
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185)
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:653)
... 87 more
Caused by: org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException: Failed to determine a suitable driver class
Solution:
1. It can be seen from the error report that the configuration of datasource database cannot be found. After inspection, it was forgotten in application The YML configuration file configures the database information
spring:
datasource:
#url: jdbc:mysql://[ip]:[port]/[name]?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
url: jdbc:mysql://localhost:3306/mysqlTest?serverTimezone=UTC&characterEncoding=UTF-8
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
sql:
init:
encoding: utf-8This is the problem I encountered in mongodb project. The original code is as follows:
running result:
the modified code is nested with quotation marks:
running result:
mysql database view data, as shown in the following figure:
successfully insert data. I wish everyone a smooth solution, I wish you success!!
1. System configuration information:
System MacOS bigsur 11.6
MySQL 8.0.26
2. Start MySQL service:
sudo /usr/local/mysql/support-files/mysql. server start
The error message is as follows:
Starting MySQL
.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/jinjideleishen.local.pid).Solution commands
mac default user is _mysql, under linux the default user of mysql is mysql
Use chown to change the owner of the specified file to the specified user or group
-R : Process all files in the specified directory and its subdirectories
sudo chown -R _mysql:_mysql /usr/local/mysql/Restart successful:
sudo /usr/local/mysql/support-files/mysql.server restart
ERROR! MySQL server PID file could not be found!
Starting MySQL
.................................................... SUCCESS!3. Stop MySQL service
sudo /usr/local/mysql/support-files/mysql.server stop4. Restart MySQL service
sudo /usr/local/mysql/support-files/mysql.server restartEnvironment
CDH 5
MySQL 5.7.30
check the manual that corresponds to your MySQL server version for the right synta to use near '@ default character
set utf8 at line 1
reason
For the problem of script execution mode, – P does not write a password after it. Execute it directly, and then enter the password. The result returns successful
/usr/share/cmf/schema/scm_prepare_database.sh mysql -h Database_ip -u username -p --scm-host cdhip scm scm scm
Question
Today, when writing MySQL code in pycharm, I always encounter errors when using Union. Although the syntax itself is correct, the red horizontal line is annoying
It’s just that you have to change union to union all to eliminate the error report.
Press setting -> editor-> Inspections, then find no data sources configured under the SQL on the right, and cancel all related to the red exclamation mark, but it is still fruitless.
However, inspired by the article, the reason for the problem is found in the location of the configuration database
Solution:
The configuration database is in file -> Settings -> Languages & Frameworks -> SQL conversations
the reason for the problem is that I configured the database before. At that time, Clickhouse was configured, which is different from MySQL in the usage of union,
so just change Clickhouse to MySQL.
before change
modified
This will return to normal~
ERROR 1067 (42000): Invalid default value for ‘last_updated_on’
Solution:
Remove the values: NO_ZERO_IN_DATE,NO_ZERO_DATE in sql_mode.
show variables like 'sql_mode';Outcome:
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
Modify sql_mode：
set session
sql_mode=’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Execute successfully!
1. Jar package is not imported
2 Multiple versions conflict
3 Version mismatch between idea and Tomcat
java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver
at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1444)
at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1252)
Error reporting check
1. Check whether you have introduced MySQL-connector-Java.Jar package
This is used to link the database
2. Check the position of the bag
be sure to check
3. See if there are more spaces in the incoming string
Resolve the MySQL: datetime (0) null default null error
Remove the database from mysql5 7 is exported as SQL in mysql5 When importing on 5, there will be an error of datetime (0) null default null. The main reason for the error is mysql5 7 and mysql5 Datetime and timestamp of 5 are incompatible, mysql5 7. The exported format is datetime (0) and mysql5 5, the syntax is not recognized
Solution:
1) Change datetime(0) to datetime, or timestamp(0) to timestamp in the export statement
2) Keep database version consistent
be careful
When datetime is set to CURRENT_TIMESTAMP by default, the length of CURRENT_TIMESTAMP cannot be specified as 0 as well.
datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0)
Correct grammar
datetime NULL DEFAULT CURRENT_TIMESTAMP