MySQL + Percona XtraDB Cluster 5.6

Administration, Info.Tech, Linux System Administration, SQL: Structured Query Language Comments Off on MySQL + Percona XtraDB Cluster 5.6

I have been experimenting on MySQL + Percona XtraDB Cluster (version 5.6). In my case, I used VMWare/VirtualBox. I created two images and label as DB1 and DB2 using Ubuntu 14.04.2 32bit (Trusty Tahr). My goal is to replicate the database from DB1 to DB2. If DB1 will be down, then DB2 will take over DB1. While fixing DB1, DB2 will serve then if DB1 is back online, DB1 will be the “Joiner” to the “Donor” (DB2). …and vice versa.

Installation

First thing you need is to prepare the installation of Percona XtraDB Cluster. (must be root or right high privileges on performing this installation)

root@db1:~# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

One thing you need to ensure is you have all Ubuntu official repositories plus Percona APT repository. So what I did is replace /etc/apt/source.list with:

# deb cdrom:[Ubuntu-Server 14.04.2 LTS _Trusty Tahr_ - Release i386 (20150218.1)]/ trusty main restricted

###### Ubuntu Main Repos
deb http://us.archive.ubuntu.com/ubuntu/ trusty main restricted universe multiverse
deb-src http://us.archive.ubuntu.com/ubuntu/ trusty main restricted universe multiverse

## Ubuntu Security Updates
deb http://security.ubuntu.com/ubuntu trusty-security main
deb-src http://security.ubuntu.com/ubuntu trusty-security main
deb http://security.ubuntu.com/ubuntu trusty-security universe
deb-src http://security.ubuntu.com/ubuntu trusty-security universe
deb http://security.ubuntu.com/ubuntu trusty-security multiverse
deb-src http://security.ubuntu.com/ubuntu trusty-security multiverse

## Uncomment the following two lines to add software from Canonical's
## 'partner' repository.
## This software is not part of Ubuntu, but is offered by Canonical and the
## respective vendors as a service to Ubuntu users.
deb http://archive.canonical.com/ubuntu trusty partner
deb-src http://archive.canonical.com/ubuntu trusty partner

## Uncomment the following two lines to add software from Ubuntu's
## 'extras' repository.
## This software is not part of Ubuntu, but is offered by third-party
## developers who want to ship their latest software.
deb http://extras.ubuntu.com/ubuntu trusty main
deb-src http://extras.ubuntu.com/ubuntu trusty main

###### Ubuntu Update Repos
deb http://us.archive.ubuntu.com/ubuntu/ trusty-security main restricted universe multiverse
deb http://us.archive.ubuntu.com/ubuntu/ trusty-updates main restricted universe multiverse
deb http://us.archive.ubuntu.com/ubuntu/ trusty-backports main restricted universe multiverse
deb-src http://us.archive.ubuntu.com/ubuntu/ trusty-security main restricted universe multiverse
deb-src http://us.archive.ubuntu.com/ubuntu/ trusty-updates main restricted universe multiverse
deb-src http://us.archive.ubuntu.com/ubuntu/ trusty-backports main restricted universe multiverse

# Percona XtraDB Cluster
deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main

After updating the APT source list, execute:

root@db1:~# apt-get update

After that, you can install Percona XtraDB Cluster server and client packages:

root@db1:~# apt-get install percona-server-server-5.6 percona-server-client-5.6

This is where I got stuck when trying to connect the nodes. Remove apparmor!!! You can check first if apparmor is running:

root@db1:~# apparmor_status

If its running, remove it before causing problems to Percona.

root@db1:~# apt-get remove apparmor

Important Notes: Do the same installation in the 2nd node or DB2.

Then to run the primary node (donor), DB1 in our case:

root@db1:~# service mysql bootstrap-pxc
 * Bootstrapping Percona XtraDB Cluster database server mysqld                       [ OK ]
root@db1:~#

Then run the 2nd node or joiner (DB2):

root@db2:~# service mysql start
mysql start/running, process 1550
root@db2:~#

~ or ~

root@db2:~# service mysql restart
mysql stop/waiting
mysql start/running, process 1550
root@db2:~#

Note: Always start first the primary node (DB1) then the next node (DB2)

Testing

Check if primary node (DB1) is working…

root@db1:~# mysql -u root -p -e "show status where Variable_name like '%wsrep_cluster%' OR Variable_name like '%wsrep_ready%';"
Enter password:
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id    | 5                                    |
| wsrep_cluster_size       | 1                                    |
| wsrep_cluster_state_uuid | 1fa1e1fc-cf8e-11e4-9664-3ea415c4a429 |
| wsrep_cluster_status     | Primary                              |
| wsrep_ready              | ON                                   |
+--------------------------+--------------------------------------+

root@db1:~# mysql -u root -p -e "show binary logs;"
Enter password:
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+

Next, check the 2nd node (DB2)…

root@db2:/etc/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.22-72.0-56-log Percona XtraDB Cluster (GPL), Release rel72.0, Revision 978, WSREP version 25.8, wsrep_25.8.r4150

Copyright (coffee) 2009-2014 Percona LLC and/or its affiliates
Copyright (coffee) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

More TEST: I created a PHP script for DB1 and DB2 to test the replication.

DB1 PHP Script (test_percona.php):

<?php

$connect = mysql_connect('localhost','root','secret');

mysql_select_db('test', $connect);

// insert if table not exists...
@mysql_query("create table if not exists data_test(
 id bigint(20) unsigned not null auto_increment primary key,
 data varchar(128),
 created datetime
);");

// inject data infinitely
while (true) {
        $data = sha1(time().rand(100,99999));
        echo $data." -> ".date("j M Y g:i.s a - l\n");
        @mysql_query("INSERT INTO data_test (data,created) VALUES ('".$data."', NOW())");
}

?>

DB2 PHP Script (monitor_percona.php):

<?php

$connect = mysql_connect('localhost','root','secret');

mysql_select_db('test', $connect);

$last_id = 0;

while (true) {
        $result = mysql_query("SELECT * FROM data_test WHERE id > ".$last_id." ORDER BY id ASC");
        $max = mysql_num_rows($result);
        if ($max > 0) {
                for ($i = 0; $i < $max; $i++) {
                        echo mysql_result($result,$i,"data")." -> ".date("j M Y g:i.s a - l", strtotime(mysql_result($result,$i,"created")))."\n";
                        $last_id = mysql_result($result,$i,"id");
                }
        }
}

?>

Then you can run the script on both nodes. To run in DB1 node, do:

root@db1:~# php test_percona.php

Then for the 2nd node:

root@db2:~# php monitor_percona.php

After that, you must see same output both nodes.

percona_xc_test_result2

percona_xc_test_result

 Encountered Issues (tail -f /var/log/mysql/error.log):

2015-03-21 20:19:11 6613 [Warning] WSREP: Gap in state sequence. Need state transfer.
2015-03-21 20:19:11 6613 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.9.103' --auth 'sstuser:secret' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '6613'  '' '
2015-03-21 20:19:11 6613 [ERROR] execlp() failed: Permission denied
2015-03-21 20:19:11 6613 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.9.103' --auth 'sstuser:secret' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '6613'  '' Read: '(null)'
2015-03-21 20:19:11 6613 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.9.103' --auth 'sstuser:secret' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '6613'  '' : 1 (Operation not permitted)
2015-03-21 20:19:11 6613 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
2015-03-21 20:19:11 6613 [ERROR] Aborting

To solve the issue is to remove the apparmor

Reference: 

  • http://www.percona.com/doc/percona-server/5.6/installation/apt_repo.html
  • http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/ubuntu_howto.html
  • http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/pit_recovery_ibk.html

MySQL Constraints: Import

Info.Tech, Linux System Administration, SQL: Structured Query Language, Web Development Comments Off on MySQL Constraints: Import

I stumbled down again of database constraints when importing SQL dump file.

ERROR 1217 (23000) at line 128: Cannot delete or update a parent row: a foreign key constraint fails

The solution is add the following line in the beginning of the SQL dump file:

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0

That should work.

PHP and MySQL TimeZone Synchronization

Info.Tech, PHP, SQL: Structured Query Language, Web Development Comments Off on PHP and MySQL TimeZone Synchronization

We all know the PHP and MySQL holds different timezone… what if we want both PHP and MySQL sync together by adjusting your PHP code only…

Here’s the solution:

<?php

define('TIMEZONE', 'Australia/Brisbane');

@date_default_timezone_set(TIMEZONE);

$dt = new DateTime();

// get time offset
$offset = $dt->format("P"); 

// update mysql timezone
mysql_query("SET time_zone='$offset';");

?>

 

Thanks to Craig Buckler…

Reference: http://www.sitepoint.com/synchronize-php-mysql-timezone-configuration/

PHP + Firebird SQL Installation

Info.Tech, PHP, SQL: Structured Query Language, Web Development Comments Off on PHP + Firebird SQL Installation

Download the following: (In my case, it’s 64bit)

  1. PHP
  2. Apache
  3. Firebird (http://www.firebirdsql.org/en/firebird-2-5-2-upd1/)

After installation of PHP, edit the php.ini and uncomment:

  • php_interbase.dll
  • php_pdo_firebird.dll

At command prompt, cast php -v and you will have an error:

PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\php\ext\php_interbase.dll' - The specified module could not be found.
 in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\php\ext\php_pdo_firebird.dll' - The specified module could not be found.
 in Unknown on line 0
PHP 5.4.9 (cli) (built: Nov 21 2012 19:54:46)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies

Install the Firebird…

Try again casting a command php -v

C:\Workspace\localhost\vshbdata>php -v
PHP 5.4.9 (cli) (built: Nov 21 2012 19:54:46)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies

Meaning, the firebird is successfully installed.

Second test is create a php script:

<?php

foreach(PDO::getAvailableDrivers() as $driver) {
  echo $driver.'<br />';
}

?>

You should see firebird in the list…

MySQL SPATIAL Index for GeoIP Database

Info.Tech, PHP, SQL: Structured Query Language, Web Development Comments Off on MySQL SPATIAL Index for GeoIP Database

Few days ago, I had an experimental project called Website Visitor Tracker which will track the visitor what city or country they from and plot their location to Google WebGL Globe and the source of the data have to query in 2M+ rows for the IP blocks and 290k+ rows for the cities or 91k+ rows for the countries. The problem occurs is the the query which it will take 2 to 4 seconds each IP query from the database and it is bad sign.

I found a solution suggested by BOTP from Kagaya-anon Linux Users Group (Thanks master BOTP) that I have to use SPATIAL Index. So I did and it works like a charm.

This is the output without using SPATIAL Index…

mysql> SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b, geoip_locations AS l WHERE b.locID = l.locID AND INET_ATON('203.114.138.95') BETWEEN b.startIpNum AND b.endIpNum;
+---------+------------------+----------+------------+
| id      | city             | latitude | longtitude |
+---------+------------------+----------+------------+
| 1859525 | Palmerston North | -40.3500 |   175.6167 |
+---------+------------------+----------+------------+
1 row in set (1.71 sec)

mysql>

So here’s the steps implementing SPATIAL index…

First I created a new table…

create table geoip_blocks (
	id bigint(20) unsigned not null auto_increment primary key,
	locID bigint(20) not null default 0,
	startIpNum bigint(20) unsigned not null,
	endIpNum bigint(20) unsigned not null,
	ip_poly POLYGON not null,
	SPATIAL INDEX(ip_poly)
) engine=myisam;

Note: use MyISAM engine for this to apply SPATIAL index, otherwise you will encounter some issues. 😛

Next, export your current table of your GeoIP…

WINDOWS: 

select locID, startIpNum, endIpNum into outfile 
'c:/geoip_blocks.dat' fields terminated by "," enclosed by "\"" 
lines terminated by "\n" from geoip_ip_blocks;

LINUX:

select locID, startIpNum, endIpNum into outfile 
'/home/user/geoip_blocks.dat' fields terminated by "," 
enclosed by "\"" lines terminated by "\n" from geoip_ip_blocks;

Then import again but this time into the new table where you will insert into POLYGON type field.

WINDOWS:

LOAD DATA LOCAL INFILE "c:/geoip_blocks.dat"
         INTO TABLE geoip_blocks 
         FIELDS
            TERMINATED BY ","
             ENCLOSED BY "\""
         LINES
              TERMINATED BY "\n"
           (
               @locID, @startIpNum, @endIpNum 
             )
       SET
            id := NULL,
            locID := @locID,
           startIpNum := @startIpNum,
           endIpNum := @endIpNum,
           ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
          POINT(@startIpNum, -1),
          POINT(@endIpNum, -1),
          POINT(@endIpNum, 1),
          POINT(@startIpNum, 1),
         POINT(@startIpNum, -1))));

LINUX:

LOAD DATA LOCAL INFILE "/home/user/geoip_blocks.dat"
         INTO TABLE geoip_blocks 
         FIELDS
            TERMINATED BY ","
             ENCLOSED BY "\""
         LINES
              TERMINATED BY "\n"
           (
               @locID, @startIpNum, @endIpNum 
             )
       SET
            id := NULL,
            locID := @locID,
           startIpNum := @startIpNum,
           endIpNum := @endIpNum,
           ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
          POINT(@startIpNum, -1),
          POINT(@endIpNum, -1),
          POINT(@endIpNum, 1),
          POINT(@startIpNum, 1),
         POINT(@startIpNum, -1))));

And that’s it.. You are good to go for testing…

OUTPUT AFTER SPATIAL INDEX IMPLEMENTED:

mysql> SELECT geoip_blocks.locID, geoip_blocks.startIpNum, geoip_blocks.endIpNum
    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID
    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.95'), 0)));
+--------+------------+------------+
| locID  | startIpNum | endIpNum   |
+--------+------------+------------+
| 199902 | 3413280768 | 3413283071 |
+--------+------------+------------+
1 row in set (0.19 sec)

mysql> SELECT geoip_blocks.locID, geoip_locations.city, geoip_blocks.startIpNum, geoip_blocks.endIpNum
    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID
    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.95'), 0)));
+--------+------------------+------------+------------+
| locID  | city             | startIpNum | endIpNum   |
+--------+------------------+------------+------------+
| 199902 | Palmerston North | 3413280768 | 3413283071 |
+--------+------------------+------------+------------+
1 row in set (0.00 sec)

Hope this help for those who encounter the same problem I had… 🙂

Thanks again to Master BOTP & DATA BOB JR.

Reference: http://databobjr.blogspot.co.nz/2010/07/ip-to-country-lookup-table-using-mysql.html

Inserting and Retrieving File into MySQL Database

Info.Tech, PHP, SQL: Structured Query Language, Web Development Comments Off on Inserting and Retrieving File into MySQL Database

Today,  I encounter a task to save the file into the database and its been a while that I have been saving files into the database method. I usually save just the path and filename. So this is just recall… 🙂

create table pdf_files (
	id bigint(20) unsigned not null auto_increment primary key,
	filename varchar(64),
	file_path varchar(64),
	binfile blob,
	created datetime
);

There’s a lot of method out there, but here’s my way of inserting the file to the database.

$path = "public/pdf/";
$file = "test.pdf";

$fileHandle = fopen($path.$file, "r");
$fileContent = fread($fileHandle, filesize($path.$file));
$fileContent = addslashes($fileContent);
fclose($fileHandle);
mysql_query("INSERT INTO pdf_files (filename,file_path,binfile,created) VALUES ('$file','$path','$fileContent',NOW())");

You can also use LOAD_FILE(”/path/filename.xxx’) in MySQL to save the file in the database.. 🙂

Again, there’s a lot of way… and this is my way how to retrieve the file…

$res = mysql_query(sprintf("SELECT filename,file_path,binfile FROM pdf_files WHERE id = %d", $xid));
if (mysql_num_rows($res) > 0) {
    $row = mysql_fetch_array($res);
    $bin_data = $row['binfile'];
    $filename_target = $row['file_path'].md5($row['filename'].'_'.time()).'.pdf';
    file_put_contents($filename_target, $bin_data);
	echo '<script>
            location.href="/'.$filename_target.'";
          </script>';
} else {
	echo 'File not found!';
	die();
}

And another way, force-download…

$res = mysql_query(sprintf("SELECT filename,file_path,binfile FROM pdf_files WHERE id = %d", $xid));
if (mysql_num_rows($res) > 0) {
    $row = mysql_fetch_array($res);
    $bin_data = $row['binfile'];
    $filename_target = $row['file_path'].md5($row['filename'].'_'.time()).'.pdf';
    file_put_contents($filename_target, $bin_data);
	// let the user download the file...
	header('Content-Description: File Transfer');
    header('Content-Type: application/force-download');
    header('Content-Disposition: attachment; filename='.basename($filename_target));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($filename_target));
    ob_clean();
    flush();
    readfile($filename_target);
    exit;
} else {
	echo 'File not found!';
	die();
}

Hope this will help you… Cheers!

MySQL 5.5 changing data folder

Administration, Info.Tech, Operating Systems, SQL: Structured Query Language Comments Off on MySQL 5.5 changing data folder

Today, I’m having problem with my MySQL. I bought new computer and want to transfer my databases from my old PC (using winXP) to new PC which is using windows 7. When I copied all my databases from data folder from my old computer and paste it to my new PC at “C:\Program Files\MySQL\MySQL Server 5.5\Data”, and restart the MySQL, I can’t see my databases. I found a solution from www.fossplanet.com and here’s the step (I slightly modified the step since i have different problem…)

  1. create folder in “C:\MySQLData”
  2. copy all files from “C:\Users\All Users\MySQL\MySQL Server 5.5\data” to “C:\MySQLData”
  3. go to RUN and type services.msc and stop MySQL service
  4. at “C:\Program Files\MySQL\MySQL Server 5.5” — edit the my.ini and change the datadir value to your new MySQL data folder which is “C:/MySQLData/”
  5. after changing the datadir, at Services window (services.msc) start again your MySQL
  6. then cast command in MySQL CLI “show databases” and you should get your migrated databases showing… 🙂

 

——————-

thanks to:

Rolando A. Edwards
MySQL DBA (SCMDBA)

HowTo: Reset MySQL root password

Administration, Info.Tech, Linux System Administration, SQL: Structured Query Language Comments Off on HowTo: Reset MySQL root password

This is the way to reset MySQL server password in Centos Linux, steps below:

# /etc/init.d/mysql stop
# /usr/bin/mysqld_safe --skip-grant-tables --skip-networking &
# mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = '' WHERE User = 'root';
mysql> exit

# /etc/init.d/mysql stop
# /etc/init.d/mysql start

You may also use service to start and stop the mysql.

#service mysqld start
#service mysqld stop

MySQL: Understanding “On Update/Delete Cascade” Statement

Info.Tech, SQL: Structured Query Language Comments Off on MySQL: Understanding “On Update/Delete Cascade” Statement

This is a re-post article, I found it as significant answer on MySQL “On Cascade” statement. Thanks to Marc B @ StackOverflow.

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).

How do I make a SQL statment that DELETE ON CASCADE?

If I delete a category then how do I make sure that it would not delete products that also are related to other categories.

The pivot table “categories_products” creates a many-to-many relationship between the two other tables

categories
- id (INT)
- name (VARCHAR 255)

products
- id
- name
- price

categories_products
- categories_id
- products_id

ANSWER:

If your cascading deletes nuke a product because it was a member of a category that was killed, then you’ve set up your foreign keys improperly. Given you example tables, you should have the following table setup:

CREATE TABLE categories (
    id int unsigned not null primary key,
    name varchar(255) default null
);
CREATE TABLE products (
    id int unsigned not null primary key,
    name varchar(255) default null
);

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,

);

This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. The cascade won’t travel farther up the tree and delete the parent product/category table.

e.g.

products: boots, mittens, hats, coats
categories: red, green, blue, white, black

prod/cats: red boots, green mittens, red coats, black hats

f you delete the ‘red’ category, then only the ‘red’ entry in the categories table dies, as well as the two entries prod/cats: ‘red boots’ and ‘red coats’.

The delete will not cascade any farther and will not take out the ‘boots’ and ‘coats’ categories.

comment followup:

you’re still misunderstanding how cascaded deletes work. They only affect the tables in which the “on delete cascade” is defined. In this case, the cascade is set in the “categories_products” table. If you delete the ‘red’ category, the only records that will cascade delete in categories_products are those where category_id = red. It won’t touch any records where ‘category_id = blue’, and it would not travel onwards to the “products” table, because there’s no foreign key defined in that table.

Here’s a more concrete example:

categories:     products:
+----+------+   +----+---------+
| id | name |   | id | name    |
+----+------+   +----+---------+
| 1  | red  |   | 1  | mittens |
| 2  | blue |   | 2  | boots   |
+---++------+   +----+---------+

products_categories:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 1          | 2           | // blue mittens
| 2          | 1           | // red boots
| 2          | 2           | // blue boots
+------------+-------------+

Let’s say you delete category #2 (blue):

DELETE FROM categories WHERE (id = 2);

the DBMS will look at all the tables which have a foreign key pointing at the ‘categories’ table, and delete the records where the matching id is 2. Since we only defined the foreign key relationship inproducts_categories, you end up with this table once the delete completes:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 2          | 1           | // red boots
+------------+-------------+

There’s no foreign key defined in the products table, so the cascade will not work there, so you’ve still got boots and mittens listed. There’s just no ‘blue boots’ and no ‘blue mittens’ anymore.

Reference: http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete by Marc B.

 

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in