MySQL Forums
Forum List  »  Newbie

PROBLEM WITH TRANSACTION/ROLLBACK
Posted by: clandernet
Date: August 31, 2004 11:05AM

Pag 2 (Continue of PAGE 1)

If I want to know who is the root of the tree, I do it:

mysql> SELECT DISTINCT tblPadre.IDCatPadre
FROM tecnoinfo10_catpagpadre As tblPadre
LEFT JOIN tecnoinfo10_catpagpadre As tblHijos
ON tblPadre.IDCatPadre = tblHijos.IDCatPag
WHERE tblHijos.IDCatPag Is NULL;
+------------+
| IDCatPadre |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)



All OK? Now I want to test TRANSACTION/COMMIT/ROLLBACK and I do it:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE tecnoinfo10_catpaginas.*
FROM tecnoinfo10_catpaginas
WHERE tecnoinfo10_catpaginas.IDCatPag=1;
Query OK, 1 row affected (0.09 sec)

mysql> DELETE tecnoinfo10_catpaginas.*
FROM tecnoinfo10_catpaginas
WHERE tecnoinfo10_catpaginas.IDCatPag=2;
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM tecnoinfo10_catpaginas;
+----------+------------+
| IDCatPag | NameCatPag |
+----------+------------+
| 1 | Inicio |
| 2 | Admin |
| 3 | Config |
| 4 | Idiomas |
| 5 | Bloques |
| 6 | Categorias |
+----------+------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM tecnoinfo10_catpagpadre;
+----------+------------+
| IDCatPag | IDCatPadre |
+----------+------------+
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
+----------+------------+
5 rows in set (0.01 sec)


mysql> SELECT DISTINCT tblPadre.IDCatPadre
FROM tecnoinfo10_catpagpadre As tblPadre
LEFT JOIN tecnoinfo10_catpagpadre As tblHijos
ON tblPadre.IDCatPadre = tblHijos.IDCatPag
WHERE tblHijos.IDCatPag Is NULL;
Empty set (0.00 sec)


The problem is in the last sentence.
Why now I don`t have ROOT OF THE TREE

If I do next sentence you can see that the first row is incorrect, and for this reason
the previous sentence return EMPTY SET.

mysql> SELECT tblPadre.*, tblHijos.* FROM tecnoinfo10_catpagpadre As tblPadre LEFT JOIN tecnoinfo10_
catpagpadre As tblHijos ON tblPadre.IDCatPadre = tblHijos.IDCatPag;
+----------+------------+----------+------------+
| IDCatPag | IDCatPadre | IDCatPag | IDCatPadre |
+----------+------------+----------+------------+
| 2 | 1 | 2 | 1 |
| 3 | 2 | 2 | 1 |
| 4 | 2 | 2 | 1 |
| 5 | 2 | 2 | 1 |
| 6 | 2 | 2 | 1 |
+----------+------------+----------+------------+
5 rows in set (0.00 sec)

If I switch off the MYSQL's SERVICE and switch on it, all is ok. Why?

Sorry for my bad english.

Options: ReplyQuote


Subject
Written By
Posted
PROBLEM WITH TRANSACTION/ROLLBACK
August 31, 2004 11:05AM
September 01, 2004 03:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.