One of frequently asked question from customer to modify sql_mode system variable while running MySQL database service(MDS) instance.

As you know MySQL server can operate in different  SQL modes and can apply these modes differently for different clients, depending on the value of the sql_mode system variable.

MySQL DBAs/Developer can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

Let’s take an example to understand more about it.

Suppose I wanted to remove SQL_MODE= “ONLY_FULL_GROUP_BY”.

Below steps to follow to change the SQL_MODE from dashboard .

If you do it in traditional  way you might get an error as below

mysql> set global sql_mode=”STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION”;

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

 that’s by we have to modify those dynamic variable from OCI  dashboard  by following below steps :-

Step 01:- Go to Hamburger MenuDB SystemsConfigurationCreate MySQL Configuration

figure-1

Step 2: –  create MySQL Configuration by chossing the right shape (which means the shape to whom you wanted to modify the sql_mode)

figure 2

Step 3: –  Create MySQL Configuration by selecting the system variable “sql_mode”  and click on “Create”

figure 3Step 4:- Go to MySQL DB Instance for which you wanted to modify the SQL_MODE

figure 4

Step 5: –  Click on edit as shown above and click on show advanced options configuration >> as below

figure 5Step 6:- Click on “Change Configuration” and select the configuration which you had created earlier and finally click on “Save Changes”.

figure 6

Step 7:-  Let’s Verify the changes

Before modifying sql_mode

figure 8After applying sql_mode

figure 7

You can see marked in RED is unavailable which means sql_mode applied successfully !!!.

Now, couple of question

[Q.] does MySQL database service(MDS) will go for restart ?

[A.] No

[Q.]Does application will go for downtime for changing the configuration ?

[A.] No

Conclusion 

Changing global variable is very easy and convient with MDS and MDS is 100% developed ,managed and supported by MySQL team.

and major business benefit is one MySQL Database service(MDS) for transactions, analytics, and machine learning (ML). Real-time, secure analytics without the complexity, latency, and cost of extract, transform, and load (ETL) duplication.

This service is available on Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Microsoft Azure.