Showing posts with label MySQL/MariaDB/Percona. Show all posts
Showing posts with label MySQL/MariaDB/Percona. Show all posts

Friday, 31 July 2020

[AWS Aurora MySQL] Kill a session or a running query on Aurora MySQL

How do you kill (end) a session or a running query on Aurora MySQL?

Background

If you're DBA, normally you can kill session/query by using "KILL" command on MySQL.
But it might not work on AWS Aurora MySQL.

Permissions are differences between you and the system admin user

Even if you're a DBA, but you're not an actual DBA.

Take a look here.

Of course, 'rdsadmin'@'localhost' which is a system admin has all permissions.



But, you - as a DBA - do not have all permissions.




Now what?

AWS Aurora MySQL provides 2 system stored procedures.

If you want to kill a session, then you can call mysql.rds_kill procedure.
CALL mysql.rds_kill(thread-ID);

For e.g. process id(session id) is 199, then
CALL mysql.rds_kill(199);


If you want to kill a specific running query, then you can call mysql.rds_kill_query procedure.
CALL mysql.rds_kill_query(thread-ID);

For e.g. process id(session id) is 199, then
CALL mysql.rds_kill_query(199);

That's all.
Now, you can kill the user queries. :)


Curiosity

Somehow, I was curious about how does it work.
Luckily, "SHOW CREATE PROCEDURE" command worked.

SHOW CREATE PROCEDURE mysql.rds_kill;

CREATE DEFINER = `rdsadmin` @`localhost`
PROCEDURE `rds_kill`(IN thread BIGINT)
   READS SQL DATA
   DETERMINISTIC
BEGIN
   DECLARE l_user   varchar(16);
   DECLARE l_host   varchar(64);
   DECLARE foo      varchar(255);

   SELECT user, host
   INTO l_user, l_host
   FROM information_schema.processlist
   WHERE id = thread;

   IF l_user = 'rdsadmin' AND l_host LIKE 'localhost%'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSADMIN SESSION`
      INTO foo;
   ELSEIF l_user = 'rdsrepladmin'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSREPLADMIN SESSION`
      INTO foo;
   ELSE
      KILL thread;
   END IF;
END



SHOW CREATE PROCEDURE mysql.rds_kill_query;

CREATE DEFINER = `rdsadmin` @`localhost`
PROCEDURE `rds_kill_query`(IN thread BIGINT)
   READS SQL DATA
   DETERMINISTIC
BEGIN
   DECLARE l_user   varchar(16);
   DECLARE l_host   varchar(64);
   DECLARE foo      varchar(255);

   SELECT user, host
   INTO l_user, l_host
   FROM information_schema.processlist
   WHERE id = thread;

   IF l_user = 'rdsadmin' AND l_host LIKE 'localhost%'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSADMIN QUERY`
      INTO foo;
   ELSEIF l_user = 'rdsrepladmin'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSREPLADMIN QUERY`
      INTO foo;
   ELSE
      KILL QUERY thread;
   END IF;
END


Turned out they are doing the exact same as "KILL" and/or "KILL QUERY" but executing by the local admin user.

References

at No comments:
Labels: , ,

Thursday, 28 May 2020

[MySQL] Zabbix Error - Access denied : SHOW SLAVE STATUS

[MySQL] Zabbix Error - Access denied : SHOW SLAVE STATUS

How to grant a privilege to zabbix user for "SHOW SLAVE STATUS"

When you install zabbix on MySQL, you might see the following error message.
2017-01-01 01:01:01 zabbix[zabbix] @ localhost [] ERROR 1227: Access denied;
 you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation : SHOW SLAVE STATUS


Now, let's check the privileges.
SELECT USER, HOST, SUPER_PRIV, REPL_CLIENT_PRIV 
FROM mysql.user
WHERE USER = 'zabbix';


zabbix user does not have "REPL_CLIENT_"PRIV".


Let's grant the replication client permission to zabbix user.
Method #1. GRANT. (Recommendation)
GRANT REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'zabbix'@'127.0.0.1';

Method #2. UPDATE mysql.user Table
UPDATE mysql.user
SET
  REPL_CLIENT_PRIV = 'Y'
WHERE USER = 'zabbix';

Now, check the privileges again.
SELECT USER, HOST, SUPER_PRIV, REPL_CLIENT_PRIV 
FROM mysql.user
WHERE USER = 'zabbix';



That's it.

It will work.
at No comments:
Labels: , ,

Saturday, 16 May 2020

[MariaDB] How to change MariaDB error log from syslog to separated log file

[MariaDB] How to change MariaDB error log from syslog to separated log file

On my.cnf, you can see the message like this.

# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

Therefore, if you want to see MariaDB Error Log, then you need to check syslog.

For e.g.
$ cat /var/log/syslog | grep "mysql"

$ cat /var/log/syslog | grep "mariadb"


But, you can store error log to a certain file instead of syslog by editing  "/etc/mysql/conf.d/mysqld_safe_syslog.cnf".
$ sudo vi /etc/mysql/conf.d/mysqld_safe_syslog.cnf

[mysqld_safe]
skip_log_error
#syslog

Then, specify the error log file path on my.cnf.
$ vi /etc/mysql/my.cnf

log_error = /var/log/mysql/error.log

Restart MariaDB.
Then error log will be stored into /var/log/mysql/error.log.

Reference.
https://mariadb.com/kb/en/mariadb/error-log/
at No comments:
Labels: ,

Thursday, 14 May 2020

[MariaDB] How to fix unused shared library of user defined function error.

[MariaDB(MySQL)] How to fix unused shared library of user-defined function error.
e.g. Error related to libmy_json_udf_path.so (json user function)

Error Messages on errorlog file.
[ERROR] Can't open shared library 'libmy_json_udf_path.so' (errno: 0, cannot open shared object file: No such file or directory)

Check mysql.func table.
select * from mysql.func;






Delete data on mysql.func and mysql.install_jsonudfs.




delete from mysql.func
where dl = 'libmy_json_udf_path.so';

DROP PROCEDURE IF EXISTS mysql.install_jsonudfs;






Restart MySQL(MariaDB).














at





No comments:
  


















Labels:
,

















          

        

          

        

Wednesday, 13 May 2020


          

        









[MySQL] Concat string, Split string










[MySQL] Concat string, Split string



* Concat strings with a delimiter

GROUP_CONCAT








 1
 2
 3
 4
 5
 6
 7
 8


 9




DROP TABLE IF EXISTS group_concat_test;
CREATE TABLE group_concat_test
(
  SEQ INT NOT NULL AUTO_INCREMENT,
  CITY VARCHAR(20),
  PRIMARY KEY `pk_group_concat_test` (SEQ)
) ENGINE = MEMORY;

INSERT INTO group_concat_test (CITY) VALUES ('Luxembourg') ,('London'), ('Seoul'), ('Paris');













 1
 2 


SELECT GROUP_CONCAT(CITY) AS ALL_CITY_NAMES
FROM group_concat_test;











+-------------------------------+
| ALL_CITY_NAMES                |
+-------------------------------+
| Luxembourg,London,Seoul,Paris |
+-------------------------------+
1 row in set (0.00 sec)













1
2


SELECT GROUP_CONCAT(CITY SEPARATOR '#') AS ALL_CITY_NAMES
FROM group_concat_test;











+-------------------------------+
| ALL_CITY_NAMES                |
+-------------------------------+
| Luxembourg#London#Seoul#Paris |
+-------------------------------+
1 row in set (0.00 sec)













1
2


SELECT GROUP_CONCAT(CITY SEPARATOR '$') AS ALL_CITY_NAMES
FROM group_concat_test;











+-------------------------------+
| ALL_CITY_NAMES                |
+-------------------------------+
| London$Luxembourg$Paris$Seoul |
+-------------------------------+
1 row in set (0.00 sec)













1


DROP TABLE IF EXISTS group_concat_test;












* Split string array using a specific delimiter

# Create a procedure, Call a procedure








 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
43
44


DELIMITER $$
DROP PROCEDURE IF EXISTS usp_split_array_test$$
CREATE PROCEDURE usp_split_array_test(
  iValueArray   VARCHAR(100) # Input array value ex) 'value1, value2, value3'
  ,iDelimiter   CHAR(1)      # Delimiter
)
BEGIN
  DECLARE vValueArry VARCHAR(100);
  DECLARE vValue VARCHAR(10);
  DECLARE vDelimiter CHAR(1);

  SET vDelimiter := iDelimiter; #Variable for delimiter
  IF IFNULL(vDelimiter,'') = '' THEN
    SET vDelimiter := ',';
  END IF;
  
  SET vValueArry := iValueArray; #Variable for split work
  IF RIGHT(vValueArry,1) != vDelimiter THEN
    SET vValueArry := CONCAT(vValueArry,vDelimiter); # 'value1, value2, value3' --> 'value1, value2, value3,'
  END IF;
  

  DROP TEMPORARY TABLE IF EXISTS temp_split_array;
  CREATE TEMPORARY TABLE temp_split_array
  (
    SEQ INT NOT NULL AUTO_INCREMENT,
    VALUE VARCHAR(20),
    PRIMARY KEY `PK_temp_split_array` (SEQ)
  ) ENGINE = MEMORY;

  #SET @vValueArry := 'value1, value2, value3,'
  WHILE (LOCATE(vDelimiter, vValueArry) > 0)
  DO
      SET vValue := LEFT(vValueArry, LOCATE(vDelimiter,vValueArry) - 1);    
      SET vValueArry := SUBSTRING(vValueArry, LOCATE(vDelimiter,vValueArry) + 1);
      INSERT INTO temp_split_array (SEQ, VALUE)
      VALUES (null, vValue);
  END WHILE;

  SELECT * FROM temp_split_array;

  DROP TEMPORARY TABLE IF EXISTS temp_split_array;
END$$
DELIMITER ;

















1


CALL usp_split_array_test('Luxembourg,London,Seoul,Paris', ',');











+-----+------------+
| SEQ | VALUE      |
+-----+------------+
|   1 | Luxembourg |
|   2 | London     |
|   3 | Seoul      |
|   4 | Paris      |
+-----+------------+
4 rows in set (0.00 sec)













1


CALL usp_split_array_test('Luxembourg;London;Seoul;Paris', ';');











+-----+------------+
| SEQ | VALUE      |
+-----+------------+
|   1 | Luxembourg |
|   2 | London     |
|   3 | Seoul      |
|   4 | Paris      |
+-----+------------+
4 rows in set (0.01 sec)



















at





1 comment:
  


















Labels:
,

















          

        

          

        

Monday, 11 May 2020


          

        









[MySQL] Retrieve special character in MySQL.










[MySQL] Retrieve special character in MySQL.



-- Create a test table








 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


DROP TEMPORARY TABLE IF EXISTS CharTest;
CREATE TEMPORARY TABLE CharTest
(value varchar(100));

INSERT INTO CharTest VALUES 
('ABXX'), ('ACYY'), 
('A%BXX'), ('A%CYY'), 
('A\\BXX'), ('A\\CYY'), 
('A''BXX'), ('A''CYY'),
('A/BXX'), ('A/CYY')
;

















 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


SELECT * FROM CharTest;
+-------+
| value |
+-------+
| ABXX  |
| ACYY  |
| A%BXX |
| A%CYY |
| A\BXX |
| A\CYY |
| A'BXX |
| A'CYY |
| A/BXX |
| A/CYY |
+-------+
10 rows in set (0.00 sec)








If you want to search for data start with "A%", it will not work as you've expected as below.










 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


SELECT * FROM CharTest where value like 'A%';
+-------+
| value |
+-------+
| ABXX  |
| ACYY  |
| A%BXX |
| A%CYY |
| A\BXX |
| A\CYY |
| A'BXX |
| A'CYY |
| A/BXX |
| A/CYY |
+-------+
10 rows in set (0.00 sec)










The result is exactly the same data as starting with "A".

So you need to use some escape sequence as below.










1
2
3
4
5
6
7


SELECT * FROM CharTest where value like 'A$%B%' ESCAPE '$';
+-------+
| value |
+-------+
| A%BXX |
+-------+
1 row in set (0.00 sec)

















1
2
3
4
5
6
7


SELECT * FROM CharTest where value like 'A\\B%' ESCAPE '$';
+-------+
| value |
+-------+
| A\BXX |
+-------+
1 row in set (0.00 sec)

















1
2
3
4
5
6
7


SELECT * FROM CharTest where value like 'A''B%';
+-------+
| value |
+-------+
| A'BXX |
+-------+
1 row in set (0.00 sec)

















1
2
3
4
5
6
7


SELECT * FROM CharTest where value like 'A/B%';
+-------+
| value |
+-------+
| A/BXX |
+-------+
1 row in set (0.00 sec)



















at





No comments:
  


















Labels:
,

















        

      









Subscribe to:
Posts (Atom)









(KOR) AI와 지속 가능한 엔지니어링 — 생성은 빠르게, 검증은 철저하게



영어 원문 : https://www.linkedin.com/pulse/ai-sustainable-engineering-generate-fast-verify-thoroughly-yoon-hclqf/ [공지 / 면책 조항] 이 글에 표현된 모든 견해는 전...