SQL Table of Content




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


MySQL Trigger

 

A MySQL trigger is a stored program that is invoked automatically in response to an event such as INSERT, UPDATE, or DELETE to a particualar table.

There are two types of triggers

  1. Row-level trigger: a row-level trigger is activated for each row that is inserted, updated, or deleted.
  2. Statement-level trigger: a statement-level trigger is executed once for each transaction regardless of how many rows are inserted, updated, or deleted. MySQL supports only row-level triggers. It doesn’t support statement-level triggers.

Advantages of triggers

  1. Triggers provide another way to check the integrity of data
  2. Triggers handle errors from the database layer
  3. Triggers are another way to run cron jobs.
  4. Triggers can be useful for auditing the data changes in tables.

Disadvantages of triggers

  1. Triggers can only use extended validations but not all validations. You can use NOT NULL, UNIQUE, CHECK, and FOREIGN KEY contraints.
  2. Triggers can be difficult to troubleshoot because they execute automatically in the database which may not be visible to clients.
  3. Triggers may increase the overhead of the MySQL server.

Create a Trigger

  1. Specify the name of the trigger – CREATE TRIGGER trigger_name
  2. Specify trigger type – AFTER INSERT
  3. Specify the name of the table for which you want to create the trigger for – ON table_name
  4. Specify the trigger body that will run every the trigger is invoked.
DELIMITER $$
 
CREATE TRIGGER trigger_name
    AFTER INSERT
    ON table_name FOR EACH ROW
BEGIN
   -- statements
   -- variable declarations

   -- trigger code
END$$    
 
DELIMITER ;

Example

DELIMITER $$
 
CREATE TRIGGER after_members_insert
AFTER INSERT
ON members FOR EACH ROW
BEGIN
    IF NEW.birthDate IS NULL THEN
        INSERT INTO reminders(memberId, message)
        VALUES(new.id,CONCAT('Hi ', NEW.name, ', please update your date of birth.'));
    END IF;
END$$
 
DELIMITER ;

Triggers only run for the new, updated, or deleted rows. They don’t touch those rows that didn’t get updated.

Show triggers

SHOW TRIGGERS;

SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];
SHOW TRIGGERS; -- return all triggers in all databases
SHOW TRIGGERS 
FROM database_name; -- show triggers from database_name

The only way to edit a trigger is by dropping and recreating it.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

 

December 9, 2020

MySQL Dump

Most of the times, you want to backup your databases to have backups to prevent loosing data. If you don’t backup your databases, a code bug or a server/disk failure could be a disaster. To help save you lots of time and frustration, it is strongly recommended that you take the precaution of regularly backing up your MySQL databases. You use mysqldump to back up your databases.

shell> mysqldump [options] > backup.sql

options – mysqldump options

backup.sql – the back up file

If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the gzip utility, and redirect it to a file as shown below:

mysqldump -u - root -p database_name | gzip > database_name.sql.gz

Authentication with username, password, and host

To get into the server, you must have the username and password for authentication. You use the -u for username and -p for password. By default –host is localhost so you can omit it when working with your localhost. Make sure you specify your host uri when you are not pointing to your localhost.

mysqldump -u username -p --host localhost database_name > backup.sql

You can even specify the username and password like this

mysqldump -uusername -ppassword -hlocalhost database_name > backup.sql

Dump all databases

To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.

mysqldump -u root -p --all-databases > all_databases.sql

Dump a single database

To backup a single MySQL database with one command you need to use the --databases option followed by the name of the database. You can also omit the –database option which is a more common way of doing this. Note that by using the --databases option, you will have the CREATE DATABASE command which is useful on restoring.

mysqldump -u root -p database_name > backup.sql
mysqldump -u root -p --databases database_name > backup.sql

Dump a single database with a timestamp

If you want to keep more than one backup in the same location, then you can add the current date to the backup filename:

mysqldump  database_name > database_name-$(date +%Y%m%d).sql

Dump multiple databases

To backup multiple MySQL databases with one command you need to use the --database option followed by the list of databases you want to backup. Each database name must be separated by space.  mysqldump  treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names.

mysqldump -u root -p --databases database_name_a database_name_b > backup_a_b.sql

Dump only some tables within a database

If you use the –databases then you have to use the –tables option to specify the tables.

mysqldump -u username -p database_name table_name1 table_name2 > backup.sql

// if --databases option is specified
mysqldump -u username -p --databases database_name --tables table_name1 table_name2 > backup.sql

Dump only some table rows within a database table

The –where option is applied to all tables not just one. There is only one –where option. You might be thinking to use the –where option for each table but that does not work. In case you configure this command with multiple –where options, only the last –where option will be evaluated.

mysqldump -u username -p database_name table_name --where="id<=100" > few_rows_backup.sql

Dump with store procedures

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

  • --routines – FALSE by default
  • --triggers – TRUE by default

so in mysqldump command , add --routines. Include triggers for each dumped table in the output. This option is enabled by default; disable it with --skip-triggers. --routines includes stored routines (procedures and functions) for the dumped databases in the output. This option requires the global SELECT privilege.

mysqldump -u root -p --triggers --routines --databases database_name > backup.sql

 

Create a cron job to backup a database on your mac

  1. Type crontab -e on your terminal to open your cron jobs
  2. Add a crontab command
  3. * * * * *  command to execute
    │ │ │ │ │
    │ │ │ │ └─── day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0)
    │ │ │ └──────── month (1 - 12)
    │ │ └───────────── day of month (1 - 31)
    │ └────────────────── hour (0 - 23)
    └─────────────────────── min (0 - 59)
  4. 0 * * * * /usr/local/mysql/bin/mysqldump -u root database_name > {location}/database_name-$(date +"%Y%m%d").sql

    Command to execute is the dump command which, in this case, is run every hour.

  5. Delete file over 30 days old
    0 * * * * /Users/folaukaveinga/db_backups -type f -name "*.sql" -mtime +30 -delete

     

Restore MySQL Dump

You can restore a MySQL dump using the mysql command. 

mysql -u username -p  database_name < backup.sql

In some cases, you need to create a database to import into because you did not use the –databases option when creating the dumb.

mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < backup.sql

Restore a single database

If you backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the --one-database option.

mysql -u username -p --one-database database_name < backup.sql

Export and Import a MySQL database in one command

Instead of creating a dump file from one database and then import the backup into another MySQL database you can use the following one-liner.

mysqldump -uLocalUsername -pLocalPassword --databases local_database_name | mysql -hRemoteHost -uRemoteUsername -pRemotePassword remote_database_name

 

October 14, 2020

MySQL Deadlock

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in multiple tables (through statements such as  UPDATE  or  SELECT ... FOR
UPDATE
 ), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue.

To avoid deadlock

To reduce the possibility of deadlocks, use transactions rather than  LOCK TABLES  statements; keep transactions that insert or update data small enough that they do not stay open for long periods of time; when different transactions update multiple tables or large ranges of rows, use the same order of operations (such as  SELECT ... FOR
UPDATE
 ) in each transaction. Using both row-level locking and the TRANSACTION_READ_COMMITTED isolation level makes it likely that you will avoid deadlocks (both settings are Derby defaults). However, deadlocks are still possible.

Minimize and handle deadlocks

Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted.

Keep transactions small and short in duration to make them less prone to collision.

Commit transactions immediately after making a set of related changes to make them less prone to collision. In particular, do not leave an interactive mysql session open for a long time with an uncommitted transaction.

When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock. For example, organize database operations into functions within your application, or call stored routines, rather than coding multiple similar sequences of INSERT, UPDATE, and DELETE statements in different places.

 

Deadlock detection

When a transaction waits more than a specific amount of time to obtain a lock (called the deadlock timeout), Derby can detect whether the transaction is involved in a deadlock.

When you configure your system for deadlock and lockwait timeouts and an application could be chosen as a victim when the transaction times out, you should program your application to handle them.

Run this command to show the latest deadlock

SHOW ENGINE INNODB STATUS ;

 

October 13, 2020

MySQL INFORMATION_SCHEMA

Information Schema

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a  USE  statement, you can only read the contents of tables, not perform  INSERT ,  UPDATE , or  DELETE  operations on them.

Information Schema Table

Information Schema table provides information about tables in databases.

SELECT *
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLE STATUS
  FROM db_name
  [LIKE 'wild']

TABLE_ROWS represents the number of rows per table. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. This is important in case where you want to know how many rows or how much data a table has. For MyISAMDATA_LENGTH is the length of the data file, in bytes. For InnoDBDATA_LENGTH is the approximate amount of space allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.

SELECT 
    TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
    MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'monomono';

// just row counts
SELECT 
    TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, 
    AUTO_INCREMENT, CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'monomono';

 

Information Schema Event

Information Schema event provides information about events in databases.

SELECT
    EVENT_SCHEMA, EVENT_NAME, DEFINER, TIME_ZONE, EVENT_TYPE, EXECUTE_AT,
    INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ORIGINATOR,
    CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE table_schema = 'db_name'
  [AND column_name LIKE 'wild']

SHOW EVENTS
  [FROM db_name]
  [LIKE 'wild']

Create an event

delimiter |

CREATE EVENT E_MIN_FIRE
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS CURRENT_TIMESTAMP
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO activity(event_name, event_fired_at)
          VALUES('test-event',NOW());
      END |

delimiter ;
SELECT * 
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = 'monomono';

SELECT 
    EVENT_SCHEMA, EVENT_NAME, CREATED, LAST_EXECUTED, DEFINER, TIME_ZONE, EVENT_DEFINITION,
    EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ON_COMPLETION, EVENT_COMMENT
FROM INFORMATION_SCHEMA.EVENTS 
WHERE EVENT_SCHEMA = 'monomono';

 

Information Schema Trigger

Information schema trigger provides information about triggers.

SELECT * 
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='database_name';


// favorite fields
SELECT 
     TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_SCHEMA,
     EVENT_OBJECT_TABLE, ACTION_STATEMENT, ACTION_ORIENTATION, ACTION_TIMING, CREATED
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='database_name';

 

Information Schema ProcessList

The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. 

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

SHOW FULL PROCESSLIST;

SHOW PROCESSLIST;

 

July 9, 2020

MySQL Event

MySQL Events or scheduled events are tasks that execute according to a specified schedule. They are stored in the database and executed at one or more intervals.

For example, you can create an event that optimizes a table by backfilling data from another table the database that runs at 1:00 AM every day.

MySQL Events are also known as “temporal triggers” because they are triggered by time, not by DML events like normal triggers. MySQL events are similar to a cronjob on Linux or a task scheduler on Windows. MySQL Event Scheduler manages the schedule and execution of Events. MySQL Events can be very useful in many cases such as optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak time.

 

Run SHOW PROCESSLIST to make sure that event_scheduler User shows up as an indication that you can create an event.

SHOW PROCESSLIST;

Show event details

You can use show events query to show details of events. You can also use the INFORMATION_SCHEMA.EVENTS table to do the same.

SELECT * 
FROM INFORMATION_SCHEMA.EVENTS 
WHERE EVENT_SCHEMA = 'database_name';

SELECT 
      EVENT_SCHEMA, EVENT_NAME, CREATED, LAST_EXECUTED, DEFINER, TIME_ZONE, EVENT_DEFINITION,
      EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ON_COMPLETION, EVENT_COMMENT
FROM INFORMATION_SCHEMA.EVENTS 
WHERE EVENT_SCHEMA = 'database_name';

SHOW EVENTS FROM database_name;

Turn on and off the event scheduler

-- turn on event_scheduler
SET GLOBAL event_scheduler = ON;

-- turn off event_scheduler
SET GLOBAL event_scheduler = OFF;

Create an event

CREATE EVENT  requires the EVENT privilege for the schema in which the event is to be created. If the DEFINER clause is present, the privileges required depend on the user value. An event is associated with a schema. If no schema is indicated as part of event_name, the default (current) schema is assumed.

// This event executes once—one hour following its creation
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.sometable SET somecol = somecol + 1;

Recurring event with end time

With ENDS the recurring event will stop its execution at that time.

CREATE EVENT IF NOT EXISTS E_MAX_FIRE
ON 
   SCHEDULE EVERY 1 MINUTE
   STARTS CURRENT_TIMESTAMP
   ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO activity(event_name, event_fired_at)
   VALUES('max-event',NOW());

Recurring event with stored procedure

Note that with no ENDS clause the recurring event will continue to execute at EVERY 1 MINUTE forever.

delimiter |

CREATE EVENT E_MIN_FIRE
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS CURRENT_TIMESTAMP
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO activity(event_name, event_fired_at)
          VALUES('min-event',NOW());
      END |

delimiter ;

Drop or remove and event

DROP EVENT [IF EXISTS] event_name;

// delete E_MIN_FIRE event
DROP EVENT IF EXISTS E_MIN_FIRE;

 

May 9, 2020