Showing posts with label MySQL. Show all posts

Thursday, 23 May 2024

Datagrip with Amazon Q - Simple test

Explain Code

Refactor Code

Fix Code

Optimize code

Friday, 11 June 2021

[Aurora/MySQL] What are differences between Amazon Aurora MySQL-compatible and MySQL

Differences between Amazon Aurora MySQL-compatible and MySQL

There are many articles about the fundamental differences between Amazon Aurora MySQL-compatible(Hereafter referred to as Aurora MySQL) and MySQL(Hereafter referred to as Native MySQL).

So, in this page I would like to say differences between two databases from my experience focused on Aurora MySQL - I assume that you know Native MySQL.

Aurora MySQL Administrator does not have permission to configure I/O.

This is actually very obvious, even if you have an database administrative permission, you cannot config any I/O related configuration at all. Because host machine is controlled by the cloud(AWS).

You need to configure variables/settings using parameter group for Aurora MySQL.

In Native MySQL, you can change some configuration using "SET GLOBAL" command

In Addition, you can configure the settings using my.cnf (or something similar file) in native MySQL.

But you cannot do that because you don't have control of the configuration file.

Instead, you can configure it using parameter groups. You need to configure parameter groups and link with the cluster(or database).

You need to learn some additional commands because you are not the owner(real owner) of the database.

If you're a DBA, you can use "KILL <spid>" in order to kill a session. But it does not work on Aurora MySQL(if you are not the owner of the query), you need to use some AWS developed system procedure such as "CALL mysql._rds_kill(thread-ID);".

As you do not have permission to control system tables, you need to use some other commands for the log files as well. As an example, "CALL mysql.rds_rotate_general_log;" will recycle the ganeral_log (if you have already configured to use general_log).

You should be really careful about the programmable object due to the definer.

If you're DBA and you use Native MySQL, then you can change definer of the programmable object(Procedure, Function. views, event etc) as you want.

But you cannot define the definer by yourself (unless you use the master user) in Aurora MySQL. It just does not work. This cause an issue when you backup data and import data to another database. You need to remove definer or replace the definer(to a masteruser) from the dump file.

In case, the user name does not exist anymore, then you need to 1) backup the object, 2) drop the object and 3) recreate the object without the definer (or a master user).

Useful Articles

Amazon Aurora MySQL-compatible

Common DBA tasks for MySQL DB instances: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html
MySQL on Amazon RDS SQL reference: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.SQLRef.html
Resolve Definer Errors When Importing to Amazon RDS MySQL: https://aws.amazon.com/premiumsupport/knowledge-center/definer-error-mysqldump/
When Should I Use Amazon Aurora and When Should I use RDS MySQL?: https://www.percona.com/blog/2018/07/17/when-should-i-use-amazon-aurora-and-when-should-i-use-rds-mysql/

MySQL/MariaDB

SET Syntax for Variable Assignment: https://dev.mysql.com/doc/refman/5.7/en/set-variable.html
Server Status Variables: https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html
Using Option Files: https://dev.mysql.com/doc/refman/5.7/en/option-files.html

Good luck!

Monday, 17 August 2020

[MySQL/MariaDB] Reset MySQL Root Password

Reset Root Password in MySQL(MariaDB)

Let's check how to Reset Root Password for MySQL or MariaDB.

The script can be slightly different depending on OS and MySQL Editions.

Stop current running MySQL(MariaDB)

OS: Red Hat, Centos etc. DB: MySQL

sudo /etc/init.d/mysqld stop

OS: Ubuntu, Debian. DB: MariaDB

sudo /etc/init.d/mysql stop

-- OR

sudo service mysql stop

-- OR

sudo systemctl stop mysql

Start MySQL(MariaDB) skipping permission check

sudo mysqld_safe --skip-grant-tables &

Note!

From MySQL 8.0, you need to use "--init-file" option instead.
From MariaDB 10.4, this way will not work.

Set new Password for Root User

Connect to MySQL without a password.

mysql -uroot

Update Root Password.

UPDATE mysql.user
SET
  authentication_string=PASSWORD("<new strong password>")
WHERE User='root';

-- OR

UPDATE mysql.user
SET
  PASSWORD=PASSWORD("<new strong password>")
WHERE User='root';

-- OR 
ALTER USER 'root'@'localhost' IDENTIFIED BY VIA mysql_native_password USING PASSWORD('<new strong password');

Apply changes.

flush privileges;

Exit from MySQL

quit;

Stop & Start MySQL(MariaDB)

OS: Red Hat, Centos etc. DB: MySQL

sudo /etc/init.d/mysqld stop

sudo /etc/init.d/mysqld start

OS: Ubuntu, Debian. DB: MariaDB

sudo /etc/init.d/mysql stop

-- OR 
sudo service mysql stop

-- OR
sudo systemctl stop mysql

And

sudo /etc/init.d/mysql start

-- OR 
sudo service mysql start

-- OR
sudo systemctl start mysql

Connect to MySQL(MariaDB) with the new password

mysql -u root -p

References

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

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.

Wednesday, 13 May 2020

[MySQL] Concat string, Split string

[MySQL] Concat string, Split string

* Concat strings with a delimiter
GROUP_CONCAT

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

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)

Monday, 11 May 2020

[MySQL] Retrieve special character in MySQL.

[MySQL] Retrieve special character in MySQL.

-- Create a test table

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')
;

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.

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.

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

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

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

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