Tag Archives: database

Quick tip: MySQL disallow database creation which certain name

Leave a reply

If we want to prevent a name to be used as a schema name, it is pretty easy to accomplish:

# move to your mysql data folder, default /var/lib/mysql
cd /var/lib/mysql
# create an empty folder with the desired name
mkdir dontusethisname
# set permissions to 000
chmod 000 dontusethisname
# change ownership to mysql user
chown mysql:mysql dontusethisname

If you try to create this schema now, you will receive the following error:

mysql> create schema dontusethisname;
ERROR 1007 (HY000): Can't create database

 

 

Transactions with MySQL.

Leave a reply

If you want to use transactions with MySQL, you will have to use InnoDB engine, because the default engine on MySQL 5.1 (MyISAM) does not support transactions. Otherwise, if you use MySQL 5.5 you do not have to worry about it, because default engine is InnoDB.

A transaction is a set of SQL instructions which are run as a unit and can be cancelled if it is required.There are confirm and cancel operations

Transactional systems are characterized by their features:

  • Atomicity: Transaction’s instructions form a logic unit.

  • Coherence: Database must coherent before and after transaction. Can’t affect negatively.

  • Isolation: Transactions are not affected among them

  • Durability: If the transaction was executed succesfully, changes will be stored permanently.

    To start a transaction you can use BEGIN or START TRANSACTION

    To create a savepoint use SAVEPOINT savepointname

    And for rollback use whether ROLLBACK (to return to the BEGIN point) or ROLLBACK TO savepointname (to return another specific point)

    Example of use:

    Example of transactions use with MySQL
    Example of transactions use with MySQL