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

Linux SSH

SSH stands for “Secure Shell”. It is a protocol used to securely connect to a remote server. ssh is secure in the sense that it transfers the data in encrypted form between the host and the client. It transfers inputs from the client to the host and relays back the output. ssh runs at TCP/IP port 22.

SSH with username and password

ssh {options} username@host

host can be ip address or domain name. You will be prompted to enter password

ssh ubuntu@19423455

 

SSH with private and public keys

ssh -i “/path-to-IdentityFile” username@host

With aws ec2, you have a .pem file as the identity file.

ssh -i "test.perm" ubuntu@folauk.com

 

Add custom connection options

When you ssh into a server, you most likely use a key. But it takes time to type out the key and other options. It would be nice to have to option of just typing out something like ssh my-linux-tester in which case you know exactly where to go. It turns out you can do this kind of thing. SSH has a config file in the ~/.ssh directory. This config file can be configured for your custom connections with these options:

  • HostName: The actual hostname that should be used to establish the connection. This replaces any alias defined in the Host header. This option is not necessary if the Host definition specifies the actual valid hostname to connect to.
  • User: The username to be used for the connection.
  • Port: The port that the remote SSH daemon is running on. It’s default to port 22 if not specified.
  • IdentityFile : The public identity file.
# Personal linux server for testing
Host my-linux-tester
  HostName ec2-tester.folaukaveinga.com
  User ubuntu
  AddKeysToAgent yes
  UseKeychain yes
  IdentityFile ~/.ssh/personal/test.perm

Now you can just do this to ssh into server. ssh {Host} in which case you will ssh into the ec2-tester.folaukaveinga.com server

ssh {Host}

ssh my-linux-tester

 

 

November 15, 2020

Git cherry-pick

The cherry-pick command is used to apply a commit from one branch to another branch.

Let’s say you have a feature branch that you are working on that have two stories. You finished the two stories but only need to push the first story to master. You have one commit for story number one and another commit for story number two. Note here that you can’t use merge because merge will bring all the changes(story one and two together). We don’t want that we only need the first story code. Here you can cherry-pick the commit for story number one to master.

Steps
1. Find commit id of the commit you need to cherry-pick
2. Checkout the branch you want to apply the commit to
3. git cherry-pick commit-id

git checkout master
git cherry-pick 1232349F

Here is another example. Let’s say you are at branch (F-G-H) and you want the change that’s made in E. To do this you need to cherry-pick E. Note that these notes are commit ids.

git cherry-pick E

If you want to cherry-pick more than one commits then do this

git cherry-pick commit-id-1 commit-id-2 commit-id-3

Cherry pick all commits of a specific branch

When you want to cherry pick all commits of a specific branch but not commits merged or cherry-picked into it. You can just cherry pick the whole branch. Maybe there are so many commits that it does not make sense to specify each commit.

Let say you have a branch call color, css, and grid. You working on the css branch but have changes from color branch that you don’t want to push to the grid branch. What you need to do is cherry pick all commits from the css branch to the grid branch. Here is how you do that.

git checkout css
// make changes
// commit and push(abc)


git merge color
// merged 1 commit(cde) from color to css(the working branch)

// make changes again
// commit and push(fhg)

// Cherry pick all of css commits(without color commits) to grid
git checkout grid

git cherry-pick css
// you have commits abc and fhg but not cde.

 

November 5, 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