Breaking referential integrity in MySql


Integrity constraint violation: 1452 Cannot add or update a child row: a foreign
key constraint fails (`sumaria_magento/mg_catalog_category_product_index`,
CONSTRAINT `FK_CATALOG_CATEGORY_PRODUCT_INDEX_PRODUCT_ENTITY` FOREIGN KEY
(`product_id`) REFERENCES `mg_catalog_product_entity` (`entity_id)

Reason..
The Constraint dont find any Parent keys in parent table. But children for these Product ids are present. That means… database have Children entries without Parent …

Is it possible to break the referential integrity when a foreign key constraint is set on an InnoDB table? The answer is an (unfortunate?) yes. It’s not a bad feature that it’s possible to temporarily turn @@foreign_key_checks off, because sometimes it’s necessary to change data in a way that referential integrity needs to be broken during the modification process. But most of the time, the data should be valid, when modification is finished. So what can be done?

A little example demonstrates this:

mysql> CREATE TABLE table_one (
->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
->   ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table_one (id) VALUES
->   (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table_two (
->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->   id_table_one INT NOT NULL,
->   FOREIGN KEY (id_table_one) REFERENCES table_one(id))
->   ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table_two (id_table_one) VALUES
->   (3), (2), (2), (3), (1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM table_one; SELECT * FROM table_two;
+—-+
| id |
+—-+
|  1 |
|  2 |
|  3 |
+—-+
3 rows in set (0.00 sec)

+—-+——-+
| id | id_table_one |
+—-+——-+
|  5 |     1 |
|  2 |     2 |
|  3 |     2 |
|  1 |     3 |
|  4 |     3 |
+—-+——-+
5 rows in set (0.00 sec)

mysql> INSERT INTO table_two (id_table_one) VALUES (4);
ERROR 1452 (23000): Cannot add or update a
child row: a foreign key constraint fails
(`test/table_two`, CONSTRAINT `table_two_ibfk_1` FOREIGN KEY
(`id_table_one`) REFERENCES `table_one` (`id`))

mysql> SET @@foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table_two (id_table_one) VALUES
->   (2), (1), (4), (2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SET @@foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM table_one; SELECT * FROM table_two;
+—-+
| id |
+—-+
|  1 |
|  2 |
|  3 |
+—-+
3 rows in set (0.00 sec)

+—-+——-+
| id | id_table_one |
+—-+——-+
|  5 |     1 |
|  7 |     1 |
|  2 |     2 |
|  3 |     2 |
|  6 |     2 |
|  9 |     2 |
|  1 |     3 |
|  4 |     3 |
|  8 |     4 |
+—-+——-+
9 rows in set (0.00 sec)

The record no 8 in table table_two has now an invalid value, due to the fact that we have set the foreign key verification temporarily to off (0).

Advertisements

How to Get the Current Page URL


Sometimes, you might want to get the current page URL that is shown in the browser URL window. For example if you want to let your visitors submit a blog post to Digg you need to get that same exact URL. Another example maybe a page URL has Querystring info appended to it and you need to send an email off to someone with that same exact URL and Querystring information. There are plenty of other reasons as well. Here is how you can do that.

Add the following code to a page:

<?php
function selfURL() {
$s = empty($_SERVER[“HTTPS”]) ? ” : ($_SERVER[“HTTPS”] == “on”) ? “s” : “”;
$protocol = strleft(strtolower($_SERVER[“SERVER_PROTOCOL”]), “/”).$s;
$port = ($_SERVER[“SERVER_PORT”] == “80”) ? “” : (“:”.$_SERVER[“SERVER_PORT”]);
return $protocol.”://”.$_SERVER[‘SERVER_NAME’].$port.$_SERVER[‘REQUEST_URI’];
}

function strleft($s1, $s2) {
$values = substr($s1, 0, strpos($s1, $s2));
return  $values;
}
?>
You can now get the current page URL using the line:
<?php
print $currenturl = selfURL();

OpenposbravoPOS Build from open source file


Few days ago I have discussed Openbravo POS systen intregation with MySql. Today I will discuss How to built OpenbravoPOS after modification logo, image etc.

 

Requirements:

 

Configuration:

  • First we have to need environment variable configuration
  • Go to System properties
  • Select Advanced
  • Click Environment variables from bottom
  • First create a User variable. Variable name: JAVA_HOME and Variable value: C:\Program Files\Java\jdk1.6.0_07 its depends upon your jdk installation directory and click ok.
  • Now we go System variables part. Select variable name Path and click Edit button. Add at the end of line ;C:\Program Files\Java\jdk1.6.0_07\bin;D:\openbravopos\apache-ant-1.7.1-bin\apache-ant-1.7.1\bin
  • C:\Program Files\Java\jdk1.6.0_07\bin its depends upon your jdk installation directory.
  • D:\openbravopos\apache-ant-1.7.1-bin\apache-ant-1.7.1\bin its depends upon your Apache ant directory where you extract Apache ant .

User Variable

System Variable

 

 

Build process:

  • Go to run from start menu, type cmd and click ok button.
  • Go to your Openbravopos directory. Suppose your Openbravopos source directory is d: now type command prompt like this E:\Openbravo POS\openbravopos_2.20_src
  • Now type ant and press Enter for check Apache ant working or not. If any problem then you will get an error or pos build will be successful. If successful then go to your Openbravopos directory/ build/dist folder and unzip openbravopos_2.20_bin.zip like this zip file.
  • Open your extract folder and click start.bat file. If you want default database configuration then click ok or click cancel and configure as your wish.