Tag Archives: mysql

org.hibernate.exception.SQLGrammarException: could not extract ResultSet, Resolve MySQL 5.7.5 or above GROUP_BY is not supported

Title: org.hibernate.exception.sqlgrammarexception: could not extract resultset, solve group above MySQL 5.7.5_ By is not supported

The reason for the problem:
MySQL 5.7.5 and above functions rely on the detection function. If only is enabled_ FULL_ GROUP_ By SQL mode (by default), MySQL will reject queries that select lists, having conditions, or order by lists, refer to non collection columns that are neither named nor functionally dependent on them in the group by clause( Before 5.7.5, MySQL did not detect any functional dependency, and only was not enabled by default_ FULL_ GROUP_ BY。 For a description of the behavior before 5.7.5, please refer to the MySQL 5.6 reference manual.)

Solution 1:

Open navcat,

Query with SQL:

select @@global.sql_ mode

The values are as follows:

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

Remove only_ FULL_ GROUP_ By, reset the value.

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’;

Solution 2:

Successful steps:

Item open

sudo vim /etc/mysql/conf.d/mysql.cnf

Scroll to the bottom of the file and copy and paste

[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

Go to the bottom of the file

Save and exit input mode

sudo service mysql restart

Restart mysql
complete

Solve the problem of jdk8 after win7 is installed. Has value ‘1.8’, but ‘1.7’ is required

I win7 x64 Ultimate Edition, installed JDK7 and jdk8 at the same time, after uninstalling jdk8, the CMD command line input: Java – version, originally thought to display java version 1.7, the result of error: has value ‘1.7’, but ‘1.8’ is required

 
I look at Java_ Home, the environment variable, is found to be OK, pointing to C:// Java/jdk1.7.0

resolvent:

After JDK7 is installed, jdk8 is installed, because when JDK1.8 is installed, java.exe, javaw.exe and javaws.exe are automatically copied to the directory C: (Windows) system32, because the priority of this directory in the windows environment variable is higher than Java_ The priority of the environment variable set by home.

After understanding this, you will know the cause of the error. Although JDK1.8 has been uninstalled, the three executable files of java.exe, javaw.exe and javaws.exe in the directory of C: (Windows) system32 are still JDK1.8, so the Java version of jdk1.7 is_ Copy the three corresponding executable files under home/bin to the directory of C: Windows/system32. At this time, enter Java – version on the command line of CMD, and everything is normal.

 
My situation is just the opposite of his, so I replace several files of jdk8 and overlay them in the directory of C: (Windows) system32.

According to the above modification, enter Java – version in CMD to display version 1.8

PhpMyAdmin localhost only displays the codes of configuration

One day, when using phpMyAdmin, you can’t jump out of the login page. Instead, it’s the code in configuration (see below). Due to the Mac operating system, the methods given by the Internet for a long time are not available. Later, it seems that some of the previous settings have been overridden.

<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * Main loader script
 *
 * @package PhpMyAdmin
 */

/**
 * Gets some core libraries and displays a top message if required
 */
require_once 'libraries/common.inc.php';

/**
 * display Git revision if requested
 */
require_once 'libraries/display_git_revision.lib.php';
require_once 'libraries/Template.class.php';

/**
 * pass variables to child pages
 */
$drops = array(
    'lang',
    'server',
    'collation_connection',
    'db',
    'table'
);
foreach ($drops as $each_drop) {
    if (array_key_exists($each_drop, $_GET)) {
        unset($_GET[$each_drop]);
    }
}
unset($drops, $each_drop);

The LoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoamodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulliblibliblibliblibliblibliblibliblibliblibliblibexe/ apaapache2/libliblibliblibliblibgaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggag
sudo apachectl restart 256303;`

Get connection timeout retry: 1 MySQL errorcode 0, state 08s01 docker container accessing MySQL container is very slow and sometimes interrupted

Today, I encountered a problem: starting the Tomcat container and then accessing the MySQL container, the query speed is very slow, and sometimes it is the same as disconnecting
solution: when starting the Tomcat container, do not add the host name and MAC address, it is likely that there is a conflict

PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[08004] [1040] Too many connections

preface

The OJ, which was built before, suddenly made an HTTP 500 error this evening. When I opened the log, I found that the MySQL database collapsed.

Text

Then open the MySQL configuration file. The path of the configuration file is as follows

/etc/mysql/mysql.conf.d/mysqld.cnf

Max the number of links in it_ Change the connections to 1000, and then restart mysql.

NettyAvroRpcClient RPC connection error

2014-12-19 01:05:42,141 (lifecycleSupervisor-1-1) [WARN – org.apache.flume.sink.AbstractRpcSink.start(AbstractRpcSink. java:294 )] Unable to create Rpc client using hostname: xxx.xxx.xxx.xxx, port: 41100
org.apache.flume.FlumeException: NettyAvroRpcClient { host: 121.41.49.51, port: 41100 }: RPC connection error

This problem occurs when flume uses Avro to accept data.

First, let’s see if the port of the connected server is monitored

If you want to send data to port 4383 of 192.168.1.1, you need a server listening to this window, otherwise RPC connection failure will occur

Docker mysql8 modify password

Docker mysql8 modify password

Set skip password login

docker exec -it mysql /bin/sh
# Note: The configuration file for editing is docker.cnf
vi /etc/mysql/conf.d/docker.cnf
exit

Restart MySQL container

docker restart mysql

Restart MySQL container

The old version of the command is invalid:
update user set password = password (‘a123456 ‘), where user =’root'</ s>
correct:

UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';
-- Change the password for use without expiration
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
flush privileges;

Error handling

ERROR 2059 (HY000): Authentication plugin ‘caching_ sha2_ password’ cannot be loaded: /usr/lib64/mysql/plugin/caching_ sha2_ password.so: cannot open shared object file: No such file or directory

Maybe the password is not set and will never expire. Maybe the database client version under Linux is too low. I tried to connect to mysql8 of docker in server B on server a, but the connection was successful by using DataGrid in windows.

Creating users and authorizations

create user  'nacos'@'%' identified by '123';
grant all on mid_nacos.* to 'nacos'@'localhost' ;
flush privileges;

Here, all the permissions of the table are granted, and all the items that can be authorized are explained and referenced https://www.cnblogs.com/yinzhengjie/p/10263692.html

Solve the error of operation create user failed for ‘user’ @ ‘%

It may be that the user already exists, you can delete and recreate it to
check whether the user exists: select user from user
delete: drop user 'user' @ '%

Solving com.mysql.cj.exceptions.unabletoconnectexception: public key retrieval is not allowed

Add & amp; after the connection information; allowPublicKeyRetrieval=true

MYSQL Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: Yes)

Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: Yes)

This prompt is a password error. If you forget your password, you can use this method to reset your password
* * 1. * * the first step to reset your password is to skip the password authentication process of MySQL
modify the MySQL configuration file
and find it in the directory of C:// program files/MySQL/MySQL server 5.7 my.cnf/my .ini/my- default.ini One of the three is modified
in [mysqld] skip-grant-tables

**2 * * restart MySQL service

	net start mysql

If this command reports an error and cannot start MySQL service, run it as an administrator cmd.exe ( cmd.exe In the directory of C: Windows: system32

**3. * * implementation again

	mysql -u root -p

You don’t need to enter a password. Press enter to enter mysql

**4 * * Change Password

	update user set Password=PASSWORD('888888') where User='root'; 
	FLUSH PRIVILEGES;

Use 888888 to login next time

5. If 4 reports error 1820 (HY000): you must set password before executing this statement, execute the code

 	SET PASSWORD = PASSWORD('123456');
	or SET PASSWORD = '123456';

Then prompt query OK, 0 rows affected (0.00 sec)
and you can log in with 123456 password

Error (1820) if the password is not changed in time after MySQL installation

The password of MySQL was not changed in time when logging in for the first time

When downloading MySQL for the first time, because the password was not changed in time, it was prompted to change the password first.

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

When changing the password according to the requirements, the following error occurs when executing SQL command:

mysql> alter user 'root'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

The reason is that the password does not conform to the password rules, which is too simple.

Therefore, it is necessary to modify the password policy in time

set global validate_password_policy=0
set global validate_password_length=1;

validate_ password_ polic =
0 or low verify length only
1 or medium verify length, number, case and special characters
2 or strong verify length, number, case, special character and dictionary file

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

MySQL data backup scheme (compatible with local and remote)

For reprint, please indicate the source: http://blog.csdn.NET/itas109  

QQ technology exchange group: 129518033

 

This program will be updated continuously, and the software will be open source in the future…

 

At present, the solution:

1、 Execute CMD command, mysqldump and FTP by C + +

1. First of all, learn the syntax of mysqldump, which is not detailed here. For reference http://www.cnblogs.com/qq78292959/p/3637135.html

Export data (including remote) to local command

mysqldump -uroot -p123456 -h 192.168.1.101 databasetest > C:/data.sql

Pay attention to [seat according to the number]:

User name root

User name root

User name root

User name root

User name root

User name root

User name root

User name root Password 123456

Password 123456

User name root

User name root

User name root

User name root Password 123456

Password 123456 Local/remote IP 192.168.1.101 Database test
database test 2. C + + execute CMD command The above command is executed by anonymous pipeline, and the code will be open source later

2. C + + execute CMD command

[Solved] MySQL Error: Client does not support authentication protocol requested by server

#Client does not support authentication protocol requested by server; consider upgrading MySQL client

Background: unknown, only my personal situation for reference. The question is not sure if it is related to Noje.js , that is, ES6 is directly related, and whether it is inevitable or not is uncertain Node.js When developing the server, the parameters such as the domain name (localhost), port (3306), user name (root), password, etc. are set correctly, but still an error is reported.

Premise: the computer has installed (1) MySQL (current version 8.0). It is recommended to install (2) MySQL database visualization software, such as Navicat.

#Solution steps:
[solution]
connect to the database (best through third-party software like Navicat or console), and input the following contents:

mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘New root password’; 

(here “;” please note)

It is recommended to close the running server (if it exists, whether it is successfully started or not), check the new database password, and then restart
refer to stack overflow https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

#Note:
I don’t quite understand the cause of this problem and how it was solved. So I suggest that you follow the example.
Theoretically, this command only resets the password of root. If the password before and after reset is the same, it should not report an error at the beginning

Please try this solution first, if it is invalid, then consider upgrading MySQL version.