Category Archives: MySQL

[Solved] There is a problem with mysql8.0 without password in Navicat connection

There is a problem with mysql8.0 without password in Navicat connection

**Problem: * * connect to mysql8.0 with Navicat, but the test connection fails
* * reason: * * when installing mysql8.0, there is no password set, and there is no random password under data in the MySQL installation directory

Solutions:
1. Stop the MySQL service
open the CMD as an administrator and close it or close the windows service page

C:\Users\xxx>net stop mysql`

2. As an administrator, open the CMD page and enter the following command to start MySQL without password

C:\Users\xxx>mysqld --console --skip-grant-tables --shared-memory

Take a look at the following screen:

3. Open a new CMD page and enter the following statement:

mysql> flush privileges; -- Modify Permissions
Query OK, 0 rows affected (0.16 sec)
 
mysql> ALTER USER "root"@"localhost" IDENTIFIED BY "123456"; -- Modify Password
Query OK, 0 rows affected (0.08 sec)

Then the login password is 123456

4. After setting, start the MySQL service normally, and use the user name and password to verify whether the setting is successful

[Solved] ORA-00020: maximum number of processes (150) exceeded

[oracle@db ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:16:06 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-00020: maximum number of processes (150) exceeded
Method 1: Change the processes parameter
To solve the ORA-00020 error, you can increase the parameter value of processes, but you need to start the database normally and log in successfully before you can modify it.
1. Firstly, log in the database successfully by adding the parameter “-prelim”.

1
2
3
4
5		 [oracle@db ~]$ sqlplus -prelim/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:22:07 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

At this point, the database can be closed and opened normally, following the following command to solve the problem.

1
2
3
4
5
6
7		 SQL> shutdown immediate;
SQL> startup;
SQL> show parameter processes;
SQL> alter system set processes=1000 scope=spfile;
SQL> startup force;
SQL> show parameter processes;
SQL> exit;

2.Start-up example

1
2
3
4
5
6
7
8
9		 SQL> startup
ORACLE instance started.
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0SQL> exit
Disconnected from ORACLE

It means that it is already open.
3. Modify the parameters

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42		 [oracle@db ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:22:35 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter process

NAME                                 TYPE        VALUE
———————————— ———– ——————————
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
processor_group_name                 string
SQL> select status from v$instance;

STATUS
————
STARTED

SQL> alter system set processes=1000 scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

4. Restart the instance

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29		 [oracle@db ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:24:59 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 6313463808 bytes
Fixed Size                  2264976 bytes
Variable Size            1476395120 bytes
Database Buffers         4815060992 bytes
Redo Buffers               19742720 bytes
Database mounted.
Database opened.
SQL> show parameter processes;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
aq_tm_processes                      integer     1
db_writer_processes                  integer     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     1000

.
Method 2: Kill all oracle processes

1
2		 $ ps -ef |grep $ORACLE_SID|grep -v grep|awk ‘{print $2}’ | xargs kill -9
$ ipcs -m | grep oracle | awk ‘{print $2}’ | xargs ipcrm shm

Problems in connecting mysql8.0 in IDEA [How to Solve]

MySQL 5.7 has always been used before, because some changes have taken place after updating to MySQL 8.0, and some information needs to be modified in the configuration of MySQL driver connection.

In MySQL version 5.7, getting the database connection is realized by the following code

connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名称","数据库账号","数据库密码");

In MySQL 8.0, you need to add a time zone. The implementation code is as follows

connection = DriverManager.getConnection(” jdbc:mysql :// localhost:3306/ Database name true & amp; characterEncoding=utf-8&& amp; serverTimezone=GMT%2B8& Usessl = false “,” database account “,” database password “);

After completing the above modification, you can perfectly solve the connection problem of mysql8.0. The connection is successful, as shown in the figure below

[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.

Using Mybatis to connect to the database error: Loading class `com.mysql.jdbc.Driver‘. This is deprecated. The new driver···

Mybatis connects to a Mysql database with the following warning.
Loading class `com.mysql.jdbc.Driver’. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver’. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Solution.
In the Mybatis connection database configuration file, modify the connection driver to.

<dataSource type="POOLED">
    <!--change com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver -->
    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
</dataSource>

[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

postgresql-Database query problem-ERROR: column “t.domainid“ must appear in the GROUP BY clause or be used in an a

ERROR: column “t.domainid” must appear in the GROUP BY clause or be used in an aggregate function

The data table is as follows.

 select * from td_link_1h_d_20210427 limit 10;
 
    time    | linkid | domainid | ipversion |    upbps    |    dnbps    | updisbps | dndisbps  |  upmaxbps   |  dnmaxbps   |  upminbps   |  dnminbps   
------------+--------+----------+-----------+-------------+-------------+----------+-----------+-------------+-------------+-------------+-------------
 1619452800 |   4401 |    10001 |         4 |   147856388 |     5923764 |        0 |         0 |   166378265 |    12533696 |   123212832 |     2247349
 1619452800 |   5002 |    10001 |         6 |           0 |           0 |        0 |         0 |           4 |           4 |           0 |           0
 1619452800 |   5503 |    10001 |         6 |           0 |         149 |        0 |         0 |           2 |         194 |           0 |         102
 1619452800 |   8724 |    10001 |         6 |         238 |           0 |        0 |         0 |         609 |           2 |          23 |           0
 1619452800 |   1201 |    10001 |         4 |   255570975 |  4111481619 |  3035634 |  19703184 |   292787968 |  4751690795 |   209619623 |  3334699801
 1619452800 |   4700 |    10001 |         4 | 19626007220 |  8697825644 |        0 |         0 | 23504765993 |  9930189669 | 16586678202 |  7205462505
 1619452800 |  13249 |    10001 |         6 |     3066257 |      262560 |        0 |        12 |     5817708 |      339458 |      954397 |      217159
 1619452800 |   9156 |    10001 |         4 | 19773923700 |  2984265593 |        0 |         0 | 22335976464 |  3388045463 | 17124116138 |  2543622015
 1619452800 |  11741 |    10001 |         4 |  5628620627 | 20052950343 | 51741713 | 318854796 |  6958633624 | 23485369390 |  4357751762 | 15861228522
 1619452800 |   2700 |    10001 |         4 |   657502770 |  2246692965 |        0 |         0 |   845823645 |  2394743374 |   506699006 |  1880772827
(10 rows)

The following error occurred when querying the database by grouping statistics:

select linkid as "ID",domainid as "Domain",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "Time",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time order by linkid,time;

ERROR:  column "t.domainid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select linkid as "ID",domainid as "Domain",to_char(time::abs...
                                   ^

After checking a lot of data, it is found that the error means that the domainid field must appear in group by or be used for aggregation function

so I modify the SQL statement, add the domainid field to group by, and then query again

 select linkid as "ID",domainid as "Doamin",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "TIME",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time,domainid order by linkid,time,domainid;

MYSQL Enter password:ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost:3306‘ 

[mysqld]
# Set port 3306
port=3306
# Set the mysql installation directory
basedir=E:\\software\\\mysql\\mysql-8.0.11-winx64 # Remember to use a double slash here \\\, single slash I will get an error here, but look at other people's tutorials, some are single slash. Try it yourself
# Set the mysql database data storage directory
datadir=E:\software\\\mysql\\\mysql-8.0.11-winx64\\\Data # Same as above here
# Maximum number of connections allowed
max_connections=200
# The number of failed connections allowed. This is to prevent someone from that host from trying to attack the database system
max_connect_errors=10
# The default character set used on the server is UTF8
character-set-server=utf8
# Default storage engine to be used when creating new tables
default-storage-engine=INNODB
# Default authentication with "mysql_native_password" plugin
default_authentication_plugin=mysql_native_password
[mysql]
# Set the default character set for mysql client
default-character-set=utf8
client]
# Set the default port used by mysql clients when connecting to the server
port=3306
default-character-set=utf8

Remote connection to MySQL database error: is not allowed to connect to this MYSQL server solution

Leave a reply

1. Change the table method.

It may be that your account does not allow remote login, only localhost. At this time, as long as you are on the localhost computer, after logging in to MySQL, change the “host” item in the “user” table in the “mysql” database, and change the name from “localhost” to “%”

mysql - u root - p

mysql > use mysql;

mysql > update  user  set host =  ' % '  where  user  =  ' root ' ;

mysql > select host, user  from  user ;

 

 

Note: Personally feel that it is not suitable!

 

[Error content]: SQL Error (2013): Lost connection to MySQL server at’waiting for initial communication packet’, system error: 0

[Error generation process]: Appears when connecting to MySQL.

[Solution]: Open my.ini, find the [mysqld] item, add a sentence after it: skip-name-resolve, save, and restart the mysql service~

 

The above is the full text introduction to solve MySQL Error (2013): Lost connection to MySQL server at waiting for initial communication packet, I hope it will be helpful for you to learn and use the database.

 

2. Authorization law.

 

1) For example, if you want myuser to use mypassword to connect to the mysql server from any host.

GRANT  ALL  PRIVILEGES  ON  * . *  TO  ' myuser ' @ ' % ' IDENTIFIED BY  ' mypassword '  WITH  GRANT  OPTION ;

FLUSH    PRIVILEGES ;

 

2) If you want to allow the user myuser to connect to the mysql server from the host whose ip is 192.168.1.6, and use mypassword as the password

GRANT  ALL  PRIVILEGES  ON  * . *  TO  ' myuser ' @ ' 192.168.1.3 ' IDENTIFIED BY  ' mypassword '  WITH  GRANT  OPTION ;

FLUSH    PRIVILEGES ;

 

3) If you want to allow the user myuser to connect to the dk database of the mysql server from the host whose ip is 192.168.1.6, and use mypassword as the password

GRANT  ALL  PRIVILEGES  ON dk. *  TO  ' myuser ' @ ' 192.168.1.3 ' IDENTIFIED BY  ' mypassword '  WITH  GRANT  OPTION ;

FLUSH    PRIVILEGES ;

 

 

The first method I used 1), and finally execute a statement mysql>FLUSH RIVILEGES to make the modification effective.

There is another method, but I haven’t tried it personally, I can find it on csdn.net, you can take a look.

Run on the machine where mysql is installed:

1. d: / mysql / bin /> mysql    - h localhost    - u root   // This should be able to enter the MySQL server

2. mysql > GRANT    ALL    PRIVILEGES    ON    * . *    TO    ' root ' @ ' % '    WITH    GRANT    OPTION   // Give any host access to data

3. mysql > FLUSH    PRIVILEGES   //The modification takes effect

4. mysql > EXIT   // Exit the MySQL server

 

In this way, you can log in as root on any other host!

[Solved] MYSQL 5.7 gruop by eRROR: this is incompatible with sql_mode=only_full_group_by

Leave a reply

From the development of a project to the test and online operation, the team’s immature management of the project will affect the development efficiency of the project. Since I just took over the project and set up the PHP environment in Centos alone, I considered using the higher version, choosing Mysql5.7, the local development environment or the Windows inheritance environment, the PHP environment and Centos are unified 7.0, but the Mysql version is 5.3 , The local development went well, and some problems appeared under Centos, which was caused by the inconsistency between the development environment and the test|online environment.

mysql command groop by error this is incompatible with sql_mode=only_full_group_by

Take a look at the meaning of ONLY_FULL_GROUP_BY: For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal, because the column is not in the GROUP BY clause, that is to say, the column found It must appear after the group by, otherwise an error will be reported, or this field appears in the aggregate function.

Next, let’s just talk about the process of solving the problem.

First enter the Mysql client to execute the command

select @@GLOBAL.sql_mode;
+------------------------------------------------- -------------------------------------------------- -----------------------------------+ 
| @@GLOBAL.sql_mode | 
+------ -------------------------------------------------- -------------------------------------------------- ----------------------------+ 
|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 
+------ -------------------------------------------------- -------------------------------------------------- ----------------------------+

See the first paragraph of the above repentant command line —> ONLY_FULL_GROUP_BY;

There are about three solutions given on the Internet. One is to add aggregate functions to the query condition column in the statement of the program code. If you do this, it will be very troublesome, and you can use group by in the development process. You have to work hard on the sentence, and this method is fine.

It is recommended to turn off this configuration item directly.

So how to turn off this item, can it be turned off directly by executing the command on the client side, set @@global.sql_mode = ……., the execution is successful, and then execute the command to check it out. One item is gone, and then we restart the mysql service again, query again, still… haha. .

So, let’s play a trick next, let’s start from the Mysql configuration file.

Find my.cnf, edit it~Put the following commands in the right place, restart the mysql service, OK~the error is correct~

Note: There is not extra set in front.

[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