Apologies for a late reply.
1) The query that tries to insert the invalid entry into Table2 fails.
Therefore, if you have 3 separate queries as in the first case, the
last one fails, but the first 2 are successful. In the second case,
they're all in one query, and if one fails, they all fail.
For optimization purposes, MySQL doesn't turn a query in extended
insert syntax into multiple queries. The whole point of extended
insert is that it batch processes inserts faster than individual
inserst.
2) Sure, there are plenty of ways. Look up IF(), user variables, and
most importanly, ROLLBACK. Basically, you want to find out if the
inserts were successful, and if any one insert wasn't successful, you
rollback your transaction. Your example never actually has a decision
point where you decide whether or not to commit or rollback.
3) There is no way to figure out which value to be inserted made an error.
On 6/14/06, Konrad Baginski <[EMAIL PROTECTED]> wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
