MySQL: Updating the Configuration of a MySQL DB System on OCI

 

- Overview:

• A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to the my.ini or my.cnf files that you use in on-premises MySQL server installation.
• Each configuration is linked to a shape of the MySQL DB system.
• There are two types of configuration:
• Default Configuration are defined in MySQL Database service and designed for a specific shape and contains a default set of configuration variables.
• Custom Configuration are user defined.
• Types of Configuration Variables:
• System variables are not user configurable. For the same shape, there are different configurations for standalone and HA.
• User variables are those variables that you can edit when create or copy a configuration.
• Initialization variables apply for the life span of the MySQL instance and, once you apply it, you cannot change it later. There is currently only one initialization variable lower_case_table_name 
• Service-specific variables are those variables that are specific only to MySQL database OCI service. Currently there are two variables:
• thread_pool_dedicated_listeners
• thread_pool_max_transactions_limit
• Updating DB system configuration to a new config that contains only dynamic user variables does not require DB system restart. 

In this blog, I'll demonstrate the steps to update the configuration of an existing MySQL DB system to use another MySQL configuration (MyConfig3) from the OCI console. 

 - Prerequisites:

• An Oracle cloud fee trial or paid account.
• An existing MySQL DB system. 
• An existing MySQL configuration. You can follow blog to create MySQL configuration.

 - Limitations:

• You cannot update/select a configuration that change initialization variables of a DB system.

Steps to Update a MySQL DB System Configuration

1. Sign in to the OCI console.

2. Open the navigation menu and navigate to "Databases -> DB Systems".

3. Choose your compartment. The list of MySQL DB system is displayed. Click your DB system name.

4. In DB system details page and under DB system configuration section, click Edit next to Configuration option.

5. In "Edit DB system" window and under Configuration section, click "change configuration" button.

6. In the Browse configurations window, select MyConfig3 from the list.

Note: in my example selecting MyConfig2 is not an option because it attempts to change the initialization variables of the DB system.

7. Once you select the new config, Edit DB system window will show the name of new config and a compare configurations table that shows variables compares. Click "save changes" buttons. 

8. DB system status will be UPDATING, but the database will NOT restart because all changed variables are dynamic.

9. Once update finishes, the configuration name will be changed to new config.

MySQL: Creating a MySQL Configuration on OCI

 

- Overview:

• A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to the my.ini or my.cnf files that you use in on-premises MySQL server installation.
• Each configuration is linked to a shape of the MySQL DB system.
• There are two types of configuration:
• Default Configuration are defined in MySQL Database service and designed for a specific shape and contains a default set of configuration variables.
• Custom Configuration are user defined.
• Types of Configuration Variables:
• System variables are not user configurable. For the same shape, there are different configurations for standalone and HA.
• User variables are those variables that you can edit when create or copy a configuration.
• Initialization variables apply for the life span of the MySQL instance and, once you apply it, you cannot change it later. There is currently only one initialization variable lower_case_table_name 
• Service-specific variables are those variables that are specific only to MySQL database OCI service. Currently there are two variables:
• thread_pool_dedicated_listeners
• thread_pool_max_transactions_limit

In this blog, I'll demonstrate the steps to create MySQL configuration using OCI console for MySQL.VM.Standard.E3.1.8GB shape, then create new MySQL standalone DB system using the new configuration.

- Prerequisites:

• An Oracle cloud fee trial or paid account.
• OCI compartment.

Steps to Create a MySQL Configuration

1. Sign in to the OCI console.

2. Open the navigation menu and navigate to "Databases -> Configurations".

3. The list of configurations is displayed. Choose your compartment.

4. Click "Create Configuration" button.

5. In "Create Configuration" window, enter and select below options, then click "Create" button.

    - Enter  name and description.

    - Select compartment.

    - Click "Change shape" button and select MySQL.VM.Standard.E3.1.8GB shape from the list.

    - Under the "User variables" section, add below variables:

       - max_connections = 200

       - sort_buffer_size = 500000

       - time_zone = -5:00

 

6. Once create configuration finishes, the configuration details page is displayed.

7. We can now use the new configuration when creating MySQL DB system. In "Create DB system" wizard: 

    - Under "Configure hardware" section, make sure to select "MySQL.VM.Standard.E3.1.8GB" shape. 

    - Click "Show advanced options", select "Configuration" tab and click "Select configuration" button, then select "MyConfig" configuration from the available list.

Oracle 23ai: Configuring SQL Firewall Using the DBMS_SQL_FIREWALL Package

 

- Overview:

• Oracle 23ai has SQL Firewall built into the database to effectively address both SQL injection attacks and compromised account issues. 
• By building Oracle SQL Firewall inside the database and streamlining its implementation, the performance overhead of Oracle SQL Firewall is negligible, making it suitable for all production workloads. Because Oracle SQL Firewall is inside the database, you don’t need to deploy or manage any external components.
• There are two methods of configuring SQL Firewall, either with Oracle Data Safe or with the DBMS_SQL_FIREWALL package. Both methods have their advantages, depending on how you want to use SQL Firewall. 

In this blog, I'll demonstrate the SQL Firewall functionality using the DBMS_SQL_FIREWALL Package.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• HR database schema.

SQL Firewall Process Flow:

SQL Firewall Configuration Steps

 

1. Connect to pluggable database as a user who has been granted the SQL_FIREWALL_ADMIN role. SYS user will be used for this example.

2. Enable SQL Firewall.

EXEC DBMS_SQL_FIREWALL.ENABLE;

3. Create and enable the SQL firewall capture for HR database user.

 BEGIN

          DBMS_SQL_FIREWALL.CREATE_CAPTURE (

               username         => 'HR',

               top_level_only   => TRUE,

               start_capture    => TRUE

          );

       END;

        /

top_level_only controls the level of SQL statements that are required. The default is FALSE. 

- TRUE generates capture logs only for top-level SQL statements that the user directly runs.

- FALSE generates capture logs for both top-level SQL statements and commands issued from PL/SQL units. 

start_capture controls when the capture will be effective. The default is TRUE.

- TRUE enables SQL Firewall to start capturing the target user's activities right away.

- FALSE creates a capture for the user, but does not start the capture right away. You can start the capture later on by running DBMS_SQL_FIREWALL.START_CAPTURE('<USERNAME>') 

4. At this stage, assuming that you have set  START_CAPTURE to TRUE. Connect to the database as a user HR to run the SQL statements that are expected to be executed directly later.

For example,

select FIRST_NAME,LAST_NAME,JOB_ID from employees where DEPARTMENT_ID=110;

select FIRST_NAME,LAST_NAME,MANAGER_ID from employees where JOB_ID='AD_VP';

select * from departments where DEPARTMENT_NAME='Payroll';

5. As s SYS user, review the capture logs and sessions logs to determine the adequacy of the capture.

SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'HR';

6. As a SYS user, stop the capture. 

EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('HR')

7. As a SYS user, generate the allow-list for the user. The allow-list defines the SQL statements that the user will be allowed to perform. SQL Firewall creates the allow-list based on data collected from existing capture logs for the user.

EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('HR')

8. As a SYS user, query DBA_SQL_FIREWALL_ALLOWED_SQL to find information about activities that the HR user is allowed to perform.

SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME = 'HR';

9. As SYS user, enable SQL Firewall protection by enabling the allow-list that was generated for the user. This enablement becomes effective immediately, even in the existing sessions of HR user.

BEGIN

  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (

    username       => 'HR',

    enforce        => DBMS_SQL_FIREWALL.ENFORCE_SQL,

    block          => TRUE

   );

END;

/

enforce specifies one of the following enforcement types:

-  DBMS_SQL_FIREWALL.ENFORCE_CONTEXT enforces the allowed contexts that have been configured.

- DBMS_SQL_FIREWALL.ENFORCE_SQL enforces the allowed SQL that has been configured.

- DBMS_SQL_FIREWALL.ENFORCE_ALL enforces both allowed contexts and allowed SQL. This setting is the default.

 

block 

- TRUE blocks the user's database connection or the user's SQL execution whenever the user violates the allow-list definition.

- FALSE allows unmatched user database connections or SQL commands to proceed. This setting is the default.

10. At this stage, if HR user attempts to perform a SQL query that violates the allow-list and you have specified SQL Firewall to block this SQL, then an ORA-47605 error appears. 

Connect as HR user and run SQL statements that are different than queries which have been captured.

select * from employees;

select FIRST_NAME,LAST_NAME,JOB_ID,salary from employees where DEPARTMENT_ID=110;

SELECT * FROM EMPLOYEES WHERE SALARY > 2000;

select * from departments where DEPARTMENT_NAME='Payroll';

11. As as SYS user, Monitor the violation log for abnormal SQL connection attempts or SQL queries that are reported if they are not in allow-list.

SELECT SQL_TEXT, FIREWALL_ACTION, IP_ADDRESS, CAUSE, OCCURRED_AT FROM DBA_SQL_FIREWALL_VIOLATIONS WHERE USERNAME = 'HR';

- Reference

https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/using-sql-firewall.html#GUID-1589CDC3-AA0C-4BBF-A6D7-266E0E1BA28E

https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms_sql_firewall.html#GUID-9DFE7C7F-32A7-4D3A-98C1-E90D8D0475D8

Oracle 23ai: INTERVAL data type aggregations

 

- Overview:

• Oracle 23ai introduces the use of SUM and AVG functions with INTERVAL datatype. 
• This enhancement makes it easier to calculate totals and averages over INTERVAL values. 

In this blog, I'll demonstrate the use of SUM and AVG functions with INTERVAL datatype.

Prerequisites:

• Oracle Database 23ai Free Developer Release.

Step #1: Preparation

The example in this blog requires the following table.

drop table if exists trips purge;

create table trips (

  id          number,

  start_time  timestamp,

  end_time    timestamp,

  duration    interval day to second generated always as (end_time - start_time) virtual

);

begin

insert into trips (id, start_time, end_time) 

values (1, timestamp '2024-01-20 08:45:00.0', timestamp '2024-01-20 18:01:00.0');

insert into trips (id, start_time, end_time) 

values (2, timestamp '2024-01-22 09:00:00.0', timestamp '2024-01-22 17:00:00.0');

insert into trips (id, start_time, end_time) 

values (3, timestamp '2024-01-25 08:00:00.0', timestamp '2024-01-25 17:45:00.0');

insert into trips (id, start_time, end_time) 

values (4, timestamp '2024-01-27 07:00:00.0', timestamp '2024-01-27 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (5, timestamp '2024-01-28 07:00:00.0', timestamp '2024-01-28 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (6, timestamp '2024-01-29 07:00:00.0', timestamp '2024-01-29 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (7, timestamp '2024-01-30 07:00:00.0', timestamp '2024-01-30 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (8, timestamp '2024-01-31 07:00:00.0', timestamp '2024-01-31 16:00:00.0');

commit;

end;

/

Step #2: Testing 

1. If we use SUM or AVG functions on an INTERVAL datatype on pre-23ai Oracle database, we will get an error as shown below.

2. Oracle 23ai database allows the use of SUM and AVG functions with INTERVAL datatype.

select sum(duration),avg(duration) from trips;

  

- We can also use SUM and AVG as analytics functions with INTERVAL datatype. 

select id,start_time,end_time,duration,

sum(duration) over (order by id rows unbounded preceding) as DUR_RUN_TOTAL,

avg(duration) over (order by id rows unbounded preceding) as DUR_RUN_AVG

from trips;

Oracle 23ai: Direct Joins for UPDATE and DELETE Statements

 

- Overview:

• Oracle 23ai now allows you to use direct joins to other tables in UPDATE and DELETE statements in the FROM clause.
• These other tables can limit the rows changed or be the source of new values.
• Direct joins make it easier to write SQL to change and delete data.

In this blog, I'll test executing UPDATE and DELETE commands with direct joins using the HR schema.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• HR database schema.

Update with Direct Join

Use-Case: Increase the salaries of the Finance department by 5%. 

1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.

SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary

FROM employees e, departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

 

2. Writing UPDATE in pre-23ai Oracle database using sub-query in the WHERE clause.

UPDATE employees e

SET e.salary=e.salary*1.05

WHERE e.department_id in (

SELECT department_id

FROM departments

WHERE department_name='Finance');

3. Writing UPDATE in Oracle 23ai database using direct join then query the tables.

When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.

explain plan for 

UPDATE employees e

SET e.salary=e.salary*1.05

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

UPDATE employees e

SET e.salary=e.salary*1.05

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

Delete with Direct Join 

Use-Case: delete the employees of the Finance department.

 1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.

SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary

FROM employees e, departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

2. Writing DELETE in pre-23ai Oracle database using sub-query in the WHERE clause.

DELETE employees e

WHERE e.department_id in (

SELECT department_id

FROM departments

WHERE department_name='Finance');

3. Writing DELETE in Oracle 23ai database using direct join then query the tables.

When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.

DELETE employees e

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';