Select Into OUTFILE Mariadb/MYSQL to export data in csv format

At  times where we need to export Data into csv format in mariadb/mysql,

SELECT INTO OUTFILE will write resulting rows from a query into a file - csv..

Below is a simple example on this :

select * from table INTO OUTFILE  '/path/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ' " '
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

Handling Null values -

Another thing we will notice is having /N in output where we have Null values in table, in few cases requirement is to have blank Fields instead of /N in output. in these cases we need to use IFNULL function in the query

example,

Select name, IFNULL (Date, ' ') from table INTO OUTFILE  '/path/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ' " '
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

With the above we replaced Null with blank in the csv output.

Scheduling Events in Mariadb/MYSQL , event scheduler/Job scheduler

Event schedulers are a way of scheduling Database jobs in Mariadb/MYSQL databases, it is similar to job schedulers used in Oracle and other databases.
There are times where we need to run certain reports or run queries at specified times, these can be accomplished using event schedulers in MariaDB database.
Before scheduling events we need to turn ON event_scheduler variable, we can check the current status using below command :

show Variables like "event_scheduler";

output :
---------------------------------
Variable_Name     |    Value
---------------------------------
event_scheduler    |      ON
---------------------------------

It is visible above that scheduler is turned ON, and hence we can proceed with scheduling jobs.

If the event scheduler is not running and event_scheduler has been set to OFF, use:

SET GLOBAL event_scheduler = ON;

Create event syntax can be studied in detail on Mariadb/MYSQL website, i will create a simple event to demonstrate here.

Create event 'test' On schedule every 24 hours starts 'Date' ENDS 'Date' On Completion Preserve Enable DO
Begin
 select * from test.test1;

We create an event named Test scheduled to run every 24 hours at a specified time with start and END date. If we don't specify On completion Preserve the event will be dropped from the database and need to recreate, hence it is important ot mention On completion Preserve.
Event is also enabled using the Enable clause.

To check event scheduled we can query information_schema.events table, that will provide information about currently scheduled events and their execution statistics.

More details regarding the syntax and scheduling can be found in mariadb website.

mysqldump backup in MariaDB/mysql

As a DBA taking backup is a common task, and mysqldump utility works with MariadB , i find it a simplest way of taking backups.
Below are few examples of taking backups using mysqldump -

1. Taking full database backup :

mysqldump --all-databases -u root -p > /backup/full-backup.sql

The above will take full backup , backup of all databases

2. Taking backup with triggers,routines,events :

mysqldump --triggers --routines --events --all-databases -u root -p > /backup/full-backup.sql

The above command is useful in taking backups of triggers, routines,events for all the databases.

3. Backup individual databases with triggers,routines,events :

mysqldump --triggers --routines --events --all-databases -u root -p dbname > /backup/full-backup.sql

Replace dbname with your database name in above

4. Export database structure without any data :

mysqldump -u root -p --no-data dbname >  /backup/db-backup.sql

5. Backup a specific table in a database :

mysqldump -u username -p db1 table1 > /backup/db1-table.sql

6. Backup a specific database :

mysqldump - u username -p db1 > /backup/db1-backup.sql

MariaDB

I have been working on a mariadb from sometime now, earlier i have worked on Mysql but lately got an opportunity to work on Mariadb. I have found things similar to MYSQL (i use all the same commands as i have used in MYSQL). Will share new posts related to the same in upcoming days

MEMORY_TARGET in oracle 11g

In oracle 10g , we have worked upon SGA_TARGET wherein components of SGA only were allocated dynamically as per the workload requirement.

In Oracle 11g  , oracle further introduced a new parameter name MEMORY_TARGET, thereby automating management of SGA + PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload

Like in case of SGA_TARGET the parameter that controlled the maximum allocation was SGA_MAX_SIZE, in MEMORY_TARGET the parameter is MEMORY_MAX_TARGET.

When MEMORY_TARGET is configured, the following components are auto tuned: DB BUFFER CACHE, SHARED POOL, JAVA POOL, LARGE POOL and STREAMS POOL.

Now to enable this parameter i will list down some steps with screenshot.

I tried the below parameters in vmware and  for testing purposes i am setting it to 300 MB only

503 Service Unavailable : Oracle Grid Control

This happened recently when I discovered that Grid Control is not working and the error displayed was:

503 service unavailable servlet error service is not initialized correctly.

I bounced all services from services.msc, our server is on windows .

However the error was still there. I searched on metalink and found note ID 418159.1

On reading the note and diagnosing i found a file emoms.properties was nullified, this file is used by oms when services start.

As per the document this happens when disk space gets full and server is restarted.

So i followed the document and created a new file as no backup was available.

After creating file, Services started nicely...:-)