Hi.
I have a few questions regarding the transaction levels in mysql 5.0.20
using InnoDB tables.
we are trying to populate two tables in the two following ways, we thought
that they would be equivalent, apparently they are not.
have a look at the following (questions last).
FIRST METHOD:
========================
create database test10;
use test10;
DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
id BIGINT NOT NULL AUTO_INCREMENT,
logid VARCHAR(32) NULL,
PRIMARY KEY(id),
UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;
DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
id BIGINT NOT NULL AUTO_INCREMENT,
table1id BIGINT,
PRIMARY KEY(id),
FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1');
INSERT INTO Table1(logid) VALUES('2');
COMMIT;
START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1'); INSERT INTO Table2(table1id)
VALUES('2'); INSERT INTO Table2(table1id) VALUES('3');
COMMIT;
select * from Table1; select * from Table2;
+----+-------+
| id | logid |
+----+-------+
| 1 | 1 |
| 2 | 2 |
+----+-------+
2 rows in set (0.00 sec)
+----+----------+
| id | table1id |
+----+----------+
| 1 | 1 |
| 2 | 2 |
+----+----------+
2 rows in set (0.00 sec)
### END FIRST METHOD ###
SECOND METHOD:
========================
create database test10;
use test10;
DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
id BIGINT NOT NULL AUTO_INCREMENT,
logid VARCHAR(32) NULL,
PRIMARY KEY(id),
UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;
DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
id BIGINT NOT NULL AUTO_INCREMENT,
table1id BIGINT,
PRIMARY KEY(id),
FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1'), ('2'); COMMIT;
START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1'), ('2'), ('3'); COMMIT;
select * from Table1; select * from Table2;
+----+-------+
| id | logid |
+----+-------+
| 1 | 1 |
| 2 | 2 |
+----+-------+
2 rows in set (0.00 sec)
Empty set (0.00 sec)
### END SECOND METHOD ###
Questions
1. Why are the two ways of adding rows not equivalent, after all, they both
happen in a trancation?
2. Is there some way to make both of them either add the two first rows to
Table2 or not to add any row?
3. If we look at the second method to insert values, how can i find out
exacly which of the values made an error?
(in this case, the third value has no matching row in Table1).
/konrad baginski
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
