MySQL is a SQL database processing layer on top of a storage engine. The default engine is MyISAM which is very fast but does not support higher level functionality such as foreign keys or transactions. One must use the InnoDB database storage engine to support foreign keys and transactions. Since the purchase of MySQL by Oracle, the Oracle corporation has been developing the InnoDB database storage layer to include even more capabilities and to match the performance of MyISAM. The future of the MySQL database will be to use InnoDB. Other database storage engines are available but MyISAM and InnoDB are the most commonly used.
The database engine is set as a default or specified for a given table using the ALTER statement or during creation.
- Set as default: mysql> SET storage_engine=InnoDB;
- Alter the table after creation: mysql> ALTER TABLE employee TYPE=InnoDB;
- Specify during table creation:
CREATE TABLE employee ( IDpk INTEGER NOT NULL AUTO_INCREMENT, ssn CHAR(11) NOT NULL, name CHAR(64), phone CHAR(32), PRIMARY KEY (IDpk) ) ENGINE = InnoDB;
List the storage engines available: mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+ | Engine | Support | Comment | +------------+---------+----------------------------------------------------------------+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | YES | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE | NO | Example storage engine | | ARCHIVE | NO | Archive storage engine | | CSV | NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | +------------+---------+----------------------------------------------------------------+
This configures the MySQL database to allow the use of the InnoDB data storage engine.
File: /etc/my.cnf (MySQL 5.0 / RHEL 5.5)
[mysqld] #bind-address = XXX.XXX.XXX.XXX port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking ... .. ... thread_cache_size = 32 # starting with 2*(#CPUs)*(#Disks)... (see /proc/cpuinfo) innodb_thread_concurrency = 8 ... .. ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # # Set buffer pool size to 50-80% of your computer's memory innodb_buffer_pool_size = 4096M innodb_additional_mem_pool_size = 20M #innodb_flush_log_at_trx_commit = 1 #Use: (0=dramatic performance increase, w/ data loss on crash) # (2=no data loss) innodb_flush_log_at_trx_commit = 2 innodb_flush_method=O_DIRECT innodb_rollback_on_timeout=1 default-storage_engine=innodb ... ..
Database transaction support refers to the need to handle a bundle of SQL transactions as a single action. The entire group of SQL instructions must all occur or have none occur at all. This is often referred to as ACID: Atomicity, Consistency, Isolation and Durability.
The requirement is to support actions such as the following banking account transaction example:
- Subtract $500.00 from the account of Mark
- Add $500.00 to the account of John
ACID:
- Atomicity: The transaction must either all be carried out successfully, or none at all. If aborted, the previous operations are rolled back to their former state.
- Consistency: The database is always in a proper state and not in error.
- Isolation: Data being used for one transaction cannot be used by another transaction until the first transaction is complete.
- Durability: Once a transaction has completed correctly, its effects should remain, and not be reversible (unless another transaction or set of SQL commands purposefully directs a change).
-- Disable auto commit: SET autocommit=0; START TRANSACTION; UPDATE account1 SET balance=balance-500; UPDATE account2 SET balance=balance+500; COMMIT;
- The tables "account1" and "account2" must be defined with the type InnoDB.
- By default, MySQL runs with auto commit mode enabled.
Auto commit is disabled for a series of statements with the directive START TRANSACTION. - START TRANSACTION WITH CONSISTENT SNAPSHOT;
Auto-commit remains disabled until you end the transaction with COMMIT or ROLLBACK. The auto commit mode then reverts to its previous state. - BEGIN and BEGIN WORK are aliases for START TRANSACTION
This is NOT the same as the BEGIN/END compound statements which do NOT define a transaction. - MySQL.com documentation: MySQL START TRANSACTION, COMMIT, and ROLLBACK Syntax
The following Java JDBC transaction example uses a MySQL database configured in the YoLinux.com MySQL tutorial.
Note that the transaction of two inserts is surrounded by the JDBC calls to setAutoCommit(false) and commit(). The state is later returned to setAutoCommit(true). Upon failure on either insert, the exception block will execute and a JDBC call is made to rollback().
This ensures that both database actions (the transaction), are executed or neither are executed.
File: JdbcProg.java
import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;
public class JdbcProg {
public static void main(String[] args)
{
Connection dbConn = null;
Statement statement1 = null;
Statement statement2 = null;
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
System.out.println("MySQL Driver Class Instantiation");
}
catch (InstantiationException ie)
{
System.out.println("Class Instantiation Exception: " + ie);
}
catch (ClassNotFoundException cnf)
{
System.out.println("Class Not Found Exception: " + cnf);
}
catch (IllegalAccessException iae)
{
System.out.println("Illegal Access Exception: " + iae);
}
try
{
dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bedrock", "root", "wess-hpc12-MySQL");
System.out.println("MySQL connection made");
// Setup Transaction
// The following block executes a total of two inserts
dbConn.setAutoCommit(false);
String insert1 = "INSERT INTO employee ( Name, Dept ) VALUES( 'Joe Bossmann', 'Sales' )";
statement1 = dbConn.createStatement();
statement1.executeUpdate(insert1);
String insert2 = "INSERT INTO employee ( Name, Dept ) VALUES( 'Mr. Grunt', 'Sales' )";
statement2 = dbConn.createStatement();
statement2.executeUpdate(insert2);
dbConn.commit();
}
catch (SQLException ex) // executes upon SQL exception: This block will perform a rollback()
{
try
{
if( dbConn != null){
dbConn.rollback();
}
} catch (SQLException ex2) {
System.out.println("Caught SQL Exception: " + ex2);
}
while (ex != null) {
System.out.println ("SQL Exception: " + ex.getMessage ());
ex = ex.getNextException ();
}
}
finally // This always gets executed when the try block exits with or without an exception: No rollback()
{
try
{
if(statement1 != null)
{
statement1.close();
}
if(statement2 != null)
{
statement2.close();
}
if( dbConn != null){
dbConn.setAutoCommit(true);
dbConn.close();
}
} catch (SQLException exf) {
System.out.println("Caught SQL Exception: " + exf);
}
}
}
}
export CLASSPATH=/usr/java/latest/lib/tools.jar:/opt/java/lib/mysql-connector-java-5.1.14-bin.jar:./ javac JdbcProg.javaRun:
java JdbcProg
<?php
// trans.php
function begin()
{
@mysql_query("BEGIN");
}
function commit()
{
@mysql_query("COMMIT");
}
function rollback()
{
@mysql_query("ROLLBACK");
}
@mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());
@mysql_select_db("bedrock") or die(mysql_error());
$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";
begin(); // transaction begins
$result = @mysql_query($query);
if(!$result)
{
rollback(); // transaction rolls back
echo "transaction rolled back";
exit;
}
else
{
commit(); // transaction is committed
echo "Database transaction was successful";
}
?>
Support for foreign keys requires the InnoDB data storage engine and MySQL version 3.23.44 and later
The foreign key is the tie between database tables where an index (record locator) is stored in one table (referencing table) and used to find the specified data record in another table (referenced table). Foreign keys are subject to the following constraints:- Neither table can be a TEMPORARY table.
- BLOB and TEXT columns cannot be included in a foreign key. (Typically integers are used to point to an index.)
- CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table.
This will allow one delete of a record from parent table to automatically spawn a delete from a child table, using only one delete statement.
To avoid deleting the child record, one must first set the foreign key value of the parent record to NULL.
- ON DELETE CASCADE
- ON UPDATE CASCADE (MySQL version 4.0.8 and later)
- SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier.
- ON DELETE SET NULL
- ON UPDATE SET NULL (MySQL version 4.0.8 and later)
- NO ACTION: Do not delete record if there is a related foreign key value in the referenced table. One must delete the parent record first.
- RESTRICT: Rejects the delete or update operation for the parent table. Same as NO ACTION.
This is the default: ON DELETE RESTRICT (same as ON DELETE NO ACTION) - SET DEFAULT: recognized but rejected and not handled by InnoDB. Don't use this!
CREATE TABLE employee (
IDpk INTEGER NOT NULL AUTO_INCREMENT,
ssn CHAR(11) NOT NULL,
name CHAR(64),
phone CHAR(32),
dept_fk INTEGER NOT NULL,
PRIMARY KEY (IDpk)
) ENGINE = InnoDB;
CREATE TABLE department (
IDpk INTEGER NOT NULL AUTO_INCREMENT,
dept_name CHAR(64),
PRIMARY KEY (IDpk)
) ENGINE = InnoDB;
ALTER TABLE employee ADD FOREIGN KEY(dept_fk) REFERENCES department (IDpk) ON DELETE CASCADE;
INSERT INTO department (dept_name) VALUES ('accounting');
INSERT INTO department (dept_name) VALUES ('engineering');
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-6789','Matt','1-800-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-7890','Mark','1-801-555-1212', 2);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-8901','Luke','1-802-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-9012','John','1-803-555-1212', 2);
Now we demonstrate the cascading delete:
mysql> SELECT * FROM employee; +------+-------------+------+----------------+---------+ | IDpk | ssn | name | phone | dept_fk | +------+-------------+------+----------------+---------+ | 1 | 123-45-6789 | Matt | 1-800-555-1212 | 1 | | 2 | 123-45-7890 | Mark | 1-801-555-1212 | 2 | | 3 | 123-45-8901 | Luke | 1-802-555-1212 | 1 | | 4 | 123-45-9012 | John | 1-803-555-1212 | 2 | +------+-------------+------+----------------+---------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM department; +------+-------------+ | IDpk | dept_name | +------+-------------+ | 1 | accounting | | 2 | engineering | +------+-------------+ 2 rows in set (0.00 sec) mysql> DELETE FROM department WHERE dept_name='engineering'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM employee; +------+-------------+------+----------------+---------+ | IDpk | ssn | name | phone | dept_fk | +------+-------------+------+----------------+---------+ | 1 | 123-45-6789 | Matt | 1-800-555-1212 | 1 | | 3 | 123-45-8901 | Luke | 1-802-555-1212 | 1 | +------+-------------+------+----------------+---------+ 2 rows in set (0.00 sec)What does this mean? If the "engineering" department record is removed, then the employees in the group are fired and removed from the employee table. The other employees are retained.
If this is performed with the default MyISAM database storage engine, then no change will occur to the employee table. The commands will be accepted but the cascading delete will not happen. The InnoDB database storage engine is required.
To see how a table was generated and with what options, execute the command:
SHOW CREATE TABLE table_name_to_check
Books:
 |
"MySQL, Second edition"
by Paul DuBois ISBN # 0735712123, New Riders Publishing
|
|
 |
"Managing and Using MySQL"
by George Reese, Randy Jay Yarger, Tim King ISBN # 0596002114, O'Reilly
|
|
 |
"MySQL Cookbook"
by Paul DuBois ISBN # 0596001452, O'Reilly
|
|
 |
"High Performance MySQL"
by Jeremy D. Zawodny, Derek J. Balling ISBN # 0596003064, O'Reilly
|
|
 |
"The Definitive Guide to MySQL, Second Edition"
by Michael Kofler ISBN # 1590591445, APress
|
|
 |
"MySQL Certification Study Guide"
by Paul Dubois, Stefan Hinz, Carsten Pedersen ISBN # 0672326329, Sams
|
|
