How to Clear the Slow Query Log on AWS RDS MySQL/ MariaDB

Category : How-to

Here is a super simple little tip for clearing the mysql.slow_log in MySQL or MariaDB when running an RDS on Amazon AWS. 

For more information on AWS RDS, please see: https://aws.amazon.com/codedeploy/

Unfortunately the usual approach of simply DELETing or TRUNCATING data from the table doesn’t work due to a permission error. This is true, even for the AWS created master database user.

Error Code: 1044. Access denied for user 'masteruser'@'%' to database 'mysql'

Luckily, the Amazon AWS team have put together a package that clears out the table for us. 

CALL mysql.rds_rotate_slow_log;

EXECUTE IMMEDIATE MySQL

Unlike recent versions of Maria DB, MySQL does not currently support the command EXECUTE IMMEDIATE.

Essentially EXECUTE IMMEDIATE is shorthand for perparing a statement, executing a statement and then finally deallocating the prepaired statement.

To get round this limitation in MySQL you can create a stored procedure that wraps up the commands required to execute a statement into a procedure so that you can call it as a one-liner.

CREATE PROCEDURE execute_immediate(IN query MEDIUMTEXT)
	MODIFIES SQL DATA
	SQL SECURITY DEFINER
BEGIN
	SET @q = query;
	PREPARE stmt FROM @q;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END

You can then call the procedure as a one-liner like this:

CALL execute_immediate('QUERY GOES HERE');

Run Multiple Bash Commands In Parallel

Tags :

Category : How-to

Bash, whilst great for simple things, can be tricky to use more advanced programming techniques that are easily exposed in things like Java, or Go.

Multithreading is one such problem. I often find myself with a series of tasks to perform that I’d like to run in parallel up to a predefined concurrency threshold.

My recent task which I’ll use as an example was to run multiple curl commands against an endpoint. These commands were standalone in the fact that they could be executed in any order and would benefit from running several API calls at once.

The first step is to create your list of commands in a file. For this, I’ll use the echo and sleep commands to demonstrate.

vi /tmp/myCommands

echo 1 && sleep 2
echo 2 && sleep 2
echo 3 && sleep 2
echo 4 && sleep 2
echo 5 && sleep 2
echo 6 && sleep 2
echo 7 && sleep 2
echo 8 && sleep 2
echo 9 && sleep 2
echo 0 && sleep 2

Once you have your list of commands, it’s time to run them!

cat /tmp/myCommands | while read n; do printf "%q\n" "$n"; done | xargs --max-procs=2 -I LC bash -c LC

The first command cat /tmp/myCommands is simply the path to your list of commands to run. The only other part to worry about is the —max-proxcs=2 attribute of xargs – this is what defines the concurrency and therefore how many ‘threads’ will run at once. xargs will do the rest – each command in your source file will be executed with 2 running at once!

So there you have it – threaded command execution in Bash!


Oracle ORA-02287: sequence number not allowed here

I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:

ORA-02287: sequence number not allowed here

I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.

I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.

Solution

The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:

CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/

You can now query the view without any pesky ORAs.

SELECT * FROM view_seq_test
/

    SEQ_ID D
---------- -
         1 X

1 row selected.

See the test script here.


Oracle View Sequence Test

CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/
SELECT * FROM view_seq_test
/
DROP SEQUENCE seq_test
/
DROP FUNCTION func_seq_test
/
DROP VIEW view_seq_test
/

 


Visit our advertisers

Quick Poll

What type of VPN protocol do you use?

Visit our advertisers