Archive

Archive for the ‘mysql’ Category

Using mysql transaction

May 10, 2011 2 comments

There are times when mysql tables need to be truncated (or certain rows need to be removed) before new records are inserted, transaction statement should be used to ensure data integrity.

Example:



// blah blah establish mysql connection

mysql_query('START TRANSACTION');
$res1=mysql_query('TRUNCATE TABLE tbl_test');

// insert new rows into tbl_test, if all went well
$res2=true;   // or false if something goes wrong

If ( $res1 && $res2 ) {
    mysql_query('COMMIT');
} else {
    mysql_query('ROLLBACK');
}

// blah blah close connection

Note: transaction feature is only available when InnoDB engine is used.

Advertisements
Categories: mysql, Programming Tags: , ,

Useful “IN” in mysql

April 29, 2011 Leave a comment

When using IN in mysql statement, the number of columns after the keyword WHERE can be more than one, for example,

SELECT * from student_info WHERE (native_language, interest) in (
    ('English', 'singing'),
    ('Spanish', 'dancing'),
    ('Franch', 'movie')
)

The same technique can be used to compare two tables with the same primary (or unique index) keys that are formed by multiple columns (also known as the composite key). For example:

SELECT * from tbl_A WHERE ( student_id, last_name, ssn4 ) 
NOT IN ( SELECT student_id, last_name, ssn4 FROM tbl_B )

Note: Columns student_id, last_name, ssn4 form the primary (or unique index) key in both table tbl_A and tbl_B, the column names don’t have to be the same in tbl_B though, as long as each corresponding column holds the same type of data as the other table.

Rows that don’t have the same primary keys can be easily spotted by simply swapping the table names (and adjust the column names if different) in the above example, if necessary.

Categories: mysql, Programming, Tip

Fix mysql alter table error in agile yii book

September 11, 2010 5 comments

This is the second post regarding my learning yii through this book: Agile Web Application Development with Yii1.1 and PHP5. The previous post regarding a gii 403 error can be found here.

The code on page 96, Chapter 6 shows the mandatory steps to create the relationships between the tables and the one didn’t get run successfully is this:


ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_project' FOREIGN KEY
('project_id') REFERENCES 'tbl_project' ('id') ON DELETE CASCADE ON
UPDATE RESTRICT;

Changing ‘ to ` (or just remove the single quotation marks) only solves the syntax error problem, the real trouble is this:

ERROR 1005 (HY000): Can't create table './trackstar/#sql-xxxx_xx.frm' (errno: 150)

The cause turns out to be, the table tbl_project was created using a non-InnoDB database engine, which is the engine type for the rest of the tables in the same database. The reason for that is, if we look at the code on page 63, tbl_project is created using the default database engine – MyISAM – which is the case on my ubuntu 9.04 and Centos 5.5. So the fix is pretty obvious – to change the engine type of tbl_project from MyISAM to InnoDB:

alter table tbl_project ENGINE=InnoDB;

After that, running the alter table statement returns OK.

[ added moments later ]
If Issue model is created before the above problem was solved, it needs to be re-created:
1) delete projected/models/Issue.php
2) go to index.php?r=gii/model and re-create model Issue

Categories: mysql, Programming, yii

Duplicate (transfer) MySQL database between hosts

January 3, 2010 Leave a comment

A simple step:

On the source host, enter the following command:

mysqldump -u root -ppasswd1 dbname | ssh -p rmt_ssh_port rmt_usr@dsthost mysql -u root -ppassword2 dbname

[ Reference ]

Categories: mysql, Tip