Tag Archives: mysql

[Solved] Caused by: java.sql.SQLException: Access denied for user ‘root‘@‘hadoop102‘ (using password: YES)

Initialize hive metabase   When schematool – initschema – dbtype MySQL – verb is used, the following error occurs:

org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : Access denied for user ‘root’@’hadoop102’ (using password: YES)     SQL Error code: 1045


Cause analysis:

“Access denied” means that hive is refused to connect to MySQL database, and there is a problem in account permission or password configuration to connect to MySQL database.

When configuring the Metastore to MySQL, configure the hive-site.xml. It is found that the password connecting to the MySQL database in the figure below is forgotten to be modified when pasting the configuration;

After correction, initialize hive metadata and execute the following command:

 schematool -initSchema -dbType mysql -verbose

Initialization complete.

This error report is due to my carelessness, which may not be universal. It is only used to remind you to check these two configurations!

MySql Install Error: Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘

@[TOC] (error 2002 (HY000): can’t connect to local MySQL server through socket ‘/ TMP/MySQL. Socket’)

System overview

CentOS 7 + MySql-community-release-el6-5.noarch。

Install MySQL process

First, download the RPM installation package as follows:
WGet – P/usr/software/ http://repo.mysql.com/mysql-community-release-el-5.noarch.rpm
Then, run the following command to install the yum source:
RPM – IVH mysql-community-release-el-5. Noarch. RPM
after the yum source is installed, you can view it as follows:
ll/etc/yum. Repos. D/
if the yum source is installed correctly, It should be displayed:
total dosage 48
– rw-r – r–. 1 root 1664 November 23 2020 CentOS base. Repo
– rw-r – r–. 1 root 1309 November 23 2020 CentOS cr. repo
– rw-r – r–. 1 root 649 November 23 2020 CentOS debuginfo. Repo
– rw-r – r–. 1 root 314 November 23 2020 CentOS FastTrack. Repo
– rw-r – r–. 1 root 630 November 23 2020 Cen TOS media. Repo
– rw-r – r–. 1 root 1331 November 23 2020 CentOS sources. Repo
– rw-r – r–. 1 root 8515 November 23 2020 CentOS vault. Repo
– rw-r – r–. 1 root 616 November 23 2020 centos-x86_ Next, run the following command to install MySQL:
Yum – y install MySQL
at the end of the process, Run the following command to start MySQL service and report an error:
Service mysqld start
at this time, the error content is as shown in the question. After checking the above installation process with the following command, the packages to be installed are:
RPM – QA | grep MySQL
the packages to be seen at this time are as follows:
mysql-community-release-el6-5. Noarch
mysql-community-common-5.6.51-2. El6.x86_ 64
mysql-community-client-5.6.51-2.el6.x86_ 64
mysql-community-libs-5.6.51-2.el6.x86_ 64
you can see that there is no MySQL Server package in the above package. So next, install MySQL server with the following command:
Yum – y install MySQL server
after the above command is executed successfully, start MySQL service:
Service mysqld start
and then run:
MySQL – uroot to enter MySQL server.

Failed to retrieve platformtransactionmanager for @ transactional test: [defaulttest]

Foreword: This is JUnit test class. I hope that the data can be rolled back after the test, and the previous data will not be affected. But I have no problem in other tutorials. I report an error, and then I check a lot of data, but it is fruitless. Finally, I solve it by myself

Error message: java.lang.illegalstateexception: failed to retrieve platformtransactionmanager for @ transactional test

java.lang.IllegalStateException: Failed to retrieve PlatformTransactionManager for @Transactional test: [DefaultTestContext@6356695f testClass = SystemServiceTest, testInstance = com.****.****.oc.coresystem.service.SystemServiceTest@716f94c1, testMethod = update@SystemServiceTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@4f18837a testClass = SystemServiceTest, locations = '{}', classes = '{class com.****.****.Application, class com.****.****.Application}', contextInitializerClasses = '[]', activeProfiles = '{dev}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@4d3167f4, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@87f383f, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@5891e32e, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@42607a4f], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> false]]
	at org.springframework.util.Assert.state(Assert.java:94) ~[spring-core-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.springframework.test.context.transaction.TransactionalTestExecutionListener.beforeTestMethod(TransactionalTestExecutionListener.java:185) ~[spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.springframework.test.context.TestContextManager.beforeTestMethod(TestContextManager.java:291) ~[spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) [junit-4.12.jar:4.12]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) [junit-4.12.jar:4.12]
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) [junit-4.12.jar:4.12]
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) [junit-4.12.jar:4.12]
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) [junit-4.12.jar:4.12]
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) [junit-4.12.jar:4.12]
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363) [junit-4.12.jar:4.12]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190) [spring-test-5.1.18.RELEASE.jar:5.1.18.RELEASE]
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137) [junit-4.12.jar:4.12]
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69) [junit-rt.jar:na]
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33) [junit-rt.jar:na]
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220) [junit-rt.jar:na]
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53) [junit-rt.jar:na]

The core reason for this problem in my project is that I have three different data sources in this large project, and JUnit does not know which data source transaction you want to roll back! It requires you to specify a platform transaction manager

Configuration of one of the primary data sources:

The test class then specifies the transactionmanager

Re run the test method, the problem is solved!

 
 

[Solved] MYSQLD: Can‘t create directory ‘/usr/local/mysql/data/’(Errcode:2 -No such file or directory)

Detailed error information of MySQL version 5.7.31 startup

MySQL version   Mysql-5.7.31 initialization error information is shown in the figure below

The configuration file is correct   There’s no problem with the permissions of my.cnf configuration file. How can the default initialization still go to the/usr/local/MySQL directory

A complete solution   Specify basedir and dataDir again during initialization

mysqld –initialize –console –basedir=/usr/local/develop/mysql-5.7.31 –datadir=/usr/local/develop/mysql-5.7.31/data

So far, the problem has been solved   Successfully initialized

I’m sending you a question   But there will be problems when starting, as shown in the figure

This is a very special problem
A complete solution

./mysqld –user=root –basedir=/usr/local/develop/mysql-5.7.31 –datadir=/usr/local/develop/mysql-5.7.31/data

Be careful not to add start after the start command   Otherwise, it will prompt another error
As shown in the picture

After the start command is executed successfully, check whether MySQL exists

ps -ef |grep mysql

end

[Solved] Daily further: database error: unknown column ‘model’ in ‘field list‘

  If you encounter this problem today, record that you have created this’ model ‘field in the database and used querywrapper to initialize the query,   And then I reported this mistake,     Then I go to the database to query with SQL statements executed by the system;   It turned out that it was still a mistake   1054 – Unknown column ‘model’ in ‘field list’   ,
After that, use “*”   To query can directly find the data;   The field “model” also exists,   I don’t know what this is,   But locate the problem, start to modify the field. Then there is no problem
Other tables I created have the ‘model’ field, but this problem does not occur
But this is still a question,   After reading the explanation of other blogs, I found that I didn’t have the answer I wanted

Hope to have big guys see trouble to teach younger brother, also hope in the future I can give myself a direct answer~

Wrong report

2021-06-10 09:52:09.327 [http-nio-8083-exec-2] ERROR o.jeecg.common.exception.JeecgBootExceptionHandler:57 - 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
### The error may exist in org/jeecg/modules/scw/scwEquipment/mapper/EquipmentMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  id,create_by,create_time,update_by,update_time,ip_address,housen_no,house_name,date_time,temp_data,max_num,min_num,aver_num,outhumi_data,inhumi_data,outtemp_data,intemp_data,status,model  FROM scw_equipment     WHERE (housen_no = ?) ORDER BY date_time DESC
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
### The error may exist in org/jeecg/modules/scw/scwEquipment/mapper/EquipmentMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,create_by,create_time,update_by,update_time,ip_address,housen_no,house_name,date_time,temp_data,max_num,min_num,aver_num,outhumi_data,inhumi_data,outtemp_data,intemp_data,status,model  FROM scw_equipment     WHERE (housen_no = ?) ORDER BY date_time DESC
### Cause: java.sql.SQLSyntaxErrorException: 
    Unknown column 'model' in 'field list'; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
	............(useless information in the middle is omitted)
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
    ............(useless information in the middle is omitted)

Then I go to the database and query with the SQL statement executed by the system

It turned out to be a mistake   1054 – Unknown column ‘model’ in ‘field list’

After that, use “*”   To query, you can directly find the data. This field “model” also exists,

Other tables I created have the ‘model’ field, but they can be found directly

[Solved] java.sql.SQLException: Unknown system variable cache query size

Recently, when I was learning springboot + MySQL + mybatis and doing a project with CSDN boss, I encountered an error when connecting to MySQL:

java.sql.SQLException: Unknown system variable 'query_cache_size'

Error reason:
the version of MySQL connector Java is too low, and the database driver version does not match the local MySQL version. Query cache has been out of date since MySQL 5.7.20, but it has been removed since MySQL 8.0
solution:
first, modify the driver name in the application. YML or application. Properties file

driver-class-name: com.mysql.cj.jdbc.Driver  #Linked Drivers for MySQL 8.0 onwards
com.mysql.jdbc.Driver #Older linked drivers

Then modify the driver version in the POM. XML file

<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.25</version>
			<scope>runtime</scope>
		</dependency>

The previous version here is 5. X. just change it to the actual MySQL version number. Mine is 8.0.25
remember to reload Maven’s dependencies after changing. Otherwise, it will still report an error:

Cannot load driver class:com.mysql.cj.jdbc.Driver

Click this icon to reload the dependency

perfect solution!

Solve the problem of MySQL 1251 client does not support

    problem display:
    solution
    Enter MySQL from the command line
    start MySQL service: net start MySQL
    login MySQL: MySQL - U root - P password
    View user information: select host, user, plugin, authentication_ string from mysql.user;
    modify user password:

    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
    

    Connection successful

Mysql Error:The user specified as a definer (‘mysql.infoschema‘@‘localhost‘) does not exist

Copyright note: This article is kept and original by the blogger. Please indicate the source for reprint
original address: https://blog.csdn.net/qq_ 38688267/article/details/117745441

Contents of articles

Scenario Description: find a solution on the network and use ‘mysqldump — all databases’ to back up the’ MySQL ‘library

Finally, the solution is summarized

Scene introduction

   after installing mysql8, use mysqldump command to backup data from mysql5.7, and then synchronize to mysql8. The specific orders are as follows:

mysqldump -uroot -p --all-databases > /root/dump.sql

   then import the data into mysql8 through source command. After the import, when querying the table list, mysql8 reports an error: MySQL E rror:The user specified as a definer (‘mysql.infoschema’@’localhost’) does not exist

  

Looking for solutions on the Internet

   a slightly more reliable solution: https://blog.wmsay.com/article/44 . But it still doesn’t solve my problem…

  

Do it yourself

   after careful thinking, I think that the problem may be in dump. SQL , because I directly specified all databases libraries, which may be MySQL and information_ Schema and other libraries are backed up, and then the data of these libraries in MySQL 8 is covered, resulting in an error.

   in order to confirm my idea, I tried the mysqldump -- all databases command locally and looked at the data in it:

Using mysqldump -- all databases will back up the MySQL library

   will not back up information_ schema、performance_ Schema library
  

Final solution

   the reason is the data coverage of MySQL database data mysql5.7, which is bound to have problems. Simply re install MySQL 8. Then dump the data again and specify the library to dump

mysqldump -uroot -p --databases mydb1 mydb2 > /root/dump.sql

Summary

   when using mysqldump , do not use the -- all databases parameter lazily, but use the -- databases db1 DB2 db3 method to specify. Using mysqldump -- all databases will back up the MySQL library, which is prone to problems!

ModuleNotFoundError: No module named ‘MySQLdb‘

Install MySQL client to solve!

Install the following dependent packages in order

================================

yum install mysql-devel

yum install gcc

yum install python3-devel

pip3 install mysqlclient

================================
the two methods found before
install MySQL connector Python
or pymysql.install_ as_ Mysqldb ()
doesn’t work for me. If I use it, there will be other errors

[Solved] com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure error

This error was encountered during the test of integrating mybatis with springboot

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

The last packet successfully received from the server was 317 milliseconds ago.  The last packet sent successfully to the server was 314 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_181]
	。。。。
	followed by a long list of

The solution is to add usessl = false to the connection string
or change usessl = true to use = false
for example

 url: jdbc:mysql://localhost:3306/sqmb?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC


end!!

SqlSession was not registered for synchronization because synchronization is not active

Main problem stack: sqlsession [org. Apache. Ibatis. Session. Defaults DefaultSqlSession@7e35f105 ]Was not registered for synchronization because synchronization is not active
scenario: spring boot + mybatis
reason: some mybatis do not output detailed stack information, such as the use of @ param parameter and the mapping of corresponding SQL variables, If you don’t manually try catch, you won’t see the problem or error.
solution: just try catch yourself and solve it according to the stack information

An error was reported in the process of importing the table from mysql5.7: [err] 1067 – invalid default value for ‘***‘

It is said on the Internet that my.ini or my.cnf files should be changed so that they can be used. I didn’t use them. I used the following method

My problem is that when creating database tables visually, I choose timestamp as the data type and report an error

solution: add current to the default value_ The value of timestamp can be from 10:00:00 on January 1, 1970 to 23:59:59 on December 31, 2037