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
- Row-level trigger: a row-level trigger is activated for each row that is inserted, updated, or deleted.
- 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
- Triggers provide another way to check the integrity of data
- Triggers handle errors from the database layer
- Triggers are another way to run cron jobs.
- Triggers can be useful for auditing the data changes in tables.
Disadvantages of triggers
- Triggers can only use extended validations but not all validations. You can use NOT NULL, UNIQUE, CHECK, and FOREIGN KEY contraints.
- Triggers can be difficult to troubleshoot because they execute automatically in the database which may not be visible to clients.
- Triggers may increase the overhead of the MySQL server.
Create a Trigger
- Specify the name of the trigger – CREATE TRIGGER trigger_name
- Specify trigger type – AFTER INSERT
- Specify the name of the table for which you want to create the trigger for – ON table_name
- 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
Hostheader. This option is not necessary if theHostdefinition 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
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.
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
- Type crontab -e on your terminal to open your cron jobs
- Add a crontab command
-
* * * * * 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)
-
0 * * * * /usr/local/mysql/bin/mysqldump -u root database_name > {location}/database_name-$(date +"%Y%m%d").sqlCommand to execute is the dump command which, in this case, is run every hour.
- 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
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 ), 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.
UPDATE
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 ) 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.
UPDATE
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 ;