Category Archives: MYSQL

Mysql Error: 1140 – In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column ‘a.store’; this is incompatible with sql_mode=only_full_group_by


According to the information,
group by has been optimized after MySQL 5.7. The version after the default startup improvement enables ONLY_FULL_GROUP_BY mode.

That is, ONLY_FULL_GROUP_BY is a sql_mode provided by the MySQL database, and this sql_mode is used to ensure the validity of the SQL statement “grouping for the most value”. This mode adopts the processing method with databases such as Oracle and DB2. That is, columns with ambiguous semantics are not allowed in the select target list.

Solution:
As long as there are aggregation functions sum(), count(), max(), avg() and other functions, group by needs to be used

[Solved] must appear in the GROUP BY clause or be used in an aggregate function

Today, when I use Postgres database to write sql, I found an error after execution: column “XXXXXX” must appear in the GROUP BY clause or be used in an aggregate function. After putting the query fields into groups according to the prompts, the results of the query are not what I need. Yes, I checked the solution on the Internet, hereby record

The cause of the problem: This is a common aggregation problem in pgsql. Before the SQL3 standard, the field selected for display must appear in  GROUP BY the

Assuming that there is a makerar table, the data is as follows:

 cname  | wmname |          avg           
--------+-------------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

I would like tocname grouping query to avg value of the largest data, sql as follows

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

However, error

ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate function 
LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

So, I putAfter the group that wmname joined

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

The result is as follows, but this is not the data I want

cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

My desired data result is

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

There are two ways to solve this problem:

1. Using nested sql, compute the aggregate in a subquery, then join it with itself to get the additional columns that need to be displayed

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

2. Use the special DISTINCT ON expression

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar
ORDER BY 
    cname, avg DESC ;

Combined with your own business, use the first method to transform sql

    SELECT
        pihs.pat_status,
        b.bed_class_id,
        pih.charge_class_id,
        pihs.pat_condition,
        pih.pat_id,
        pihs.room_with_mother_baby_flag,
        pc.pat_in_charge_doc_name,
        pc.pat_in_charge_doc_id,
        b.bed_show_no,
        pih.pat_age AS age,
        pih.pat_in_hos_id,
        pih.pat_in_hos_code,
        pihs.pat_in_status,
        pc.state_of_critical_value,
        pih.pat_in_time,
        pc.chief_doc_id,
        pc.chief_doc_name,
        pc.dur_nurse_id,
        pc.dur_nurse_name,
        pc.first_ann_bed_time,
        pc.manage_bed_nurse_name,
        pih.pat_type_id,
        pc.pat_clinic_id,
        pc.pat_ward_id,
        b.bed_id,
        b.bed_status,
        b.is_share,
        pc.nursing_class,
        pc.nursing_class_name,
        pihs.pat_in_bed_share_status,
        pih.seven_days_again_in_flag,
        b.price,
        T.change_in_time
        FROM
        bed b
        LEFT JOIN pat_in_hospital_status pihs ON b.bed_id = pihs.bed_id
        LEFT JOIN pat_in_hospital pih ON b.pat_in_hos_id = pih.pat_in_hos_id
        LEFT JOIN pat_clinican pc ON pih.pat_in_hos_id = pc.pat_in_hos_id
        LEFT JOIN ( SELECT pat_in_hos_id, MAX ( pat_in_out_ward_time ) AS change_in_time FROM change_ward_record WHERE complete_flag = '1' GROUP BY pat_in_hos_id ) T ON pih.pat_in_hos_id = T.pat_in_hos_id
        where b.ward_id='1234'

Reference link:

https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function#

[Solved] xtrabackup: error: xb_load_tablespaces() failed with error code 57

Problem description: Some errors occur when running the xtrabackup backup script on the database

DB_version:mysql8.0.26

Xtrabackup: percona-xtrabackup-8.0.27-19-Linux-x86_64.glibc2.12.tar.gz

[[email protected] scripts]# $xtrDir --defaults-file=$mysql_cnf --user=$mysql_user --password=$mysql_password --socket=$mysql_socket --compress --compress-threads= 2 --backup -- target-dir= $target_dir
xtrabackup: recognized server arguments: --datadir=/home/mysql/db_orch2/data --tmpdir=/home/mysql/db_orch2/tmp --log_bin=/home/mysql/db_orch2/binlog/orch2-bin --log- bin-index=/home/mysql/db_orch2/binlog/orch2-bin.index --server-id= 1330611 --innodb_open_files= 63000 --innodb_data_home_dir=/home/mysql/db_orch2/data --innodb_log_group_home_dir=/home/mysql /db_orch2/data --innodb_log_file_size=8G --innodb_log_files_in_group= 4 --innodb_undo_directory=/home/mysql/db_orch2/ulog --innodb_undo_tablespaces= 3 --innodb_flush_log_at_trx_commit= 2 --innodb_flush_method=O_DIRECT --innodb_io_capacity= 3000--innodb_buffer_pool_size=64G --innodb_log_buffer_size=32M --innodb_max_dirty_pages_pct= 85 --innodb_adaptive_hash_index= 1 --innodb_data_file_path=ibdata1:512M:autoextend --innodb_write_io_threads= 16 --innodb_read_io_threads = 16  
xtrabackup: recognized client arguments --password=* --socket=/home/mysql/db_orch2/mysql.sock --compress --compress-threads= 2 --backup= 1 --target-dir=/home/mysql/backup/ 13306 // 2022-04-06 
/root/percona-xtrabackup/bin/xtrabackup version 8.0 . 27 - 19 based on MySQL server 8.0 .27 Linux (x86_64) (revision id: 50dbc8dadda)
Can ' t locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl / usr/lib64/perl5 /usr/share/perl5 .) at - line 749. 
BEGIN failed--compilation aborted at - line 749 .
 220406  12 : 26 : 14 Connecting to MySQL server host: localhost, user: root, password: set , port: not set , socket : /home/mysql/db_orch2/mysql.sock
Using server version 8.0 . 26 
220406  12 : 26 : 14 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/mysql/db_orch2/ data
xtrabackup: open files limit requested 0 , set to 1024 
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = /home/mysql/db_orch2/ data
xtrabackup: innodb_data_file_path = ibdata1:512M:autoextend
xtrabackup: innodb_log_group_home_dir = /home/mysql/db_orch2/ data
xtrabackup: innodb_log_files_in_group = 4 
xtrabackup: innodb_log_file_size = 8589934592 
xtrabackup: using O_DIRECT
Number of pools: 1
xtrabackup: initialize_service_handles suceeded
220406  12 : 26 : 14 Connecting to MySQL server host: localhost, user: root, password: set , port: not set , socket: /home/mysql/db_orch2/mysql.sock
xtrabackup: Redo Log Archiving is not set up.
Starting to parse redo log at lsn = 1276785485862 
220406  12 : 26 : 14 >> log scanned up to ( 1276786464962 )
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning ' ./ ' 
Scanning ' /home/mysql/db_orch2/ulog/ ' 
Completed space ID check of 2 files.
Allocated tablespace ID 2198  for eomaqzy_data/app_source_rela, old maximum was 0 
220406  12 : 26 : 15 >> log scanned up to ( 1276786574060 )
Undo tablespace number 1 was being truncated when mysqld quit.
Cannot recover a truncated undo tablespace in read- only mode
 xtrabackup: error: xb_load_tablespaces() failed with error code 57

 

Check whether there are undo files in the data file directory, clean up the undo files and test the backup again

 

mv undo*.log /tmp

 

 

 

 

 

Error two:

 

 

xtrabackup: Can ' t create/write to file ' /home/mysql/backup/ 13306 / 2022-04-06 /eomaqzy_data/pl02_inv_meter_data.ibd.qp ' ( OS errno 24 - Too many open files) You 
are in / var /spool There are mails in /mail/root

 

 

OS file count unadjusted limit

[[email protected] 13306 ]# ulimit - n
 1024

[[email protected] ~]# grep -i nofile /etc/security/limits.conf
#         - nofile - max number of open file descriptors

[[email protected] ~]# vim /etc/security/limits.conf
 * hard nofile 65535 
* soft nofile 65535

Disconnect and log back in
[[email protected] ~]# ulimit - n
 65535

 

back up again

+ echo =========================Run full backup beginning =================== =======
+ /root/percona-xtrabackup/bin/xtrabackup --defaults-file=/home/mysql/db_orch2/conf/orch2.cnf --user=root --password=XXXXX --socket=/home/mysql/db_orch2/ mysql.sock --compress --compress-threads= 2 --backup --target - dir=/home/mysql/backup/ 13306 / 2022-04-06 + 
echo =========== ============Run full backup 
finished successfully ======================================================================================================================================== There are mails in /mail/root

[Solved] Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by serv

mysql reports Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Cause: An error is reported due to the mysql8.0 encryption method.

 

Solution:

execute instruction

mysql -u root -p

123456

use mysql;

alter user ‘root’@’localhost’ identified with mysql_native_password by ‘123456’;

flush privileges;

Note: 123456 is my own password to connect to the database

[Solved] Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by serv

mysql reports Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Cause: An error is reported due to the mysql8.0 encryption method.

Solution:

execute instruction

mysql -u root -p

123456

use mysql;

alter user ‘root’@’localhost’ identified with mysql_native_password by ‘123456’;

flush privileges;

Note: 123456 is my own password to connect to the database

[Solved] MYSQL Error: [Warning] Changed limits: max_open_files: 1024

The mysql log reports an error:

2022-02-22T03:21:39.505055Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2022-02-22T03:21:39.505065Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

 

problem causes:

The number of files that the user needs to open exceeds the upper limit, which can be viewed through the command “ulimit -a”

 

Solution:

ulimit -n 65535

The ulimit command is used to limit system users’ access to shell resources, but it only takes effect temporarily. To take effect permanently, you need to configure the /etc/security/limits.conf file. The syntax and common configurations are as follows:

vi /etc/security/limits.conf # The linux resource limit configuration file is /etc/security/limits.conf; limiting the number of user processes is very important for the stability of the linux system. The limits.conf file limits the maximum number of files a user can use, maximum threads, maximum memory and other resource usage.

Add the following two lines to set
mysql hard nofile 65535        

mysql soft nofile 65535     #The maximum number of file descriptors a mysql user can open is 1024 by default, the value here will limit tcp connections. soft is a warning value, while hard is a real threshold value, exceeding it will result in an error.

 

vi /usr/lib/systemd/system/mysqld.service Add the following line
LimitNOFILE=65535

 

# systemctl daemon-reload
# systemctl restart mysql.service

Database Start Error after Recovery: ORA-01092: ORA-30012: undo tablespace

After data recovery, open the database and report an error

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: alter db command (on 01/19/2022 09:40:13) failed
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'devices' does not exist or of wrong type
Process ID: 9320
Session ID: 5 Sequence number: 11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: Not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: alter db command (on 01/19/2022 09:40:13) failed
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'devices' does not exist or of wrong type
Process ID: 9320
Session ID: 5 Sequence number:  11

Report undo error, check undo parameters

SQL> show parameter undo;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
undo_management                      string
AUTO
undo_retention                       integer
900
undo_tablespace                      string
devices

The original undo name is UNDOTBS1, modify the pfile parameter, add undo_tablespace=UNDOTBS1 in the last line

After reboot and startup, it is normal.

[Solved] MySQL Delete or Update Data Error: Error Code: 1175

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.

Because MySQL runs in the safe-updates mode, the update or delete commands cannot be executed under non primary key conditions.

1. show variables like ‘SQL_SAFE_UPDATES’; Check the switch status.

2. Execute command set SET SQL_SAFE_UPDATES = 0; Modify database mode

 

[Solved] JMeter Connect Database error: unblock with ‘mysqladmin flush hosts’

It can be seen that the causes are:

Blocking caused by too many (exceeding the maximum value of max_connect_errors for mysql database) broken database connections from the same ip in a short period of time

Solution 1: Change the value of max_connect_errors
(1) Go to the Mysql database and check the max_connect_errors.

show variables like ‘%max_connect_errors%’;

(2) Modify the value of max_connect_errors:

set global max_connect_errors = 100;

(3) Check whether the modification is successful or not

show variables like ‘%max_connect_errors%’;

Solution 2: use mysqladmin flush-hosts command to clean up the hosts file

(1) Use the command to modify under the found Directory: mysqladmin -u xxx -p flush-hosts

perhaps

flush hosts;

Solution: mysqld> Restart

I solved it by restarting mysql