update with sub select produces invalid results from transaction_reader/transaction_log
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Drizzle |
Won't Fix
|
Medium
|
Jay Pipes |
Bug Description
I added the following test to the transaction_log suite, the test generated a missing WHERE clause when updating table t2. See below.
jdaly@rx7:
#
# Tests a update with a sub query
#
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings
CREATE TABLE t1 (
id INT NOT NULL
, padding VARCHAR(200) NOT NULL
);
INSERT INTO t1 VALUES (1, "I love testing.");
INSERT INTO t1 VALUES (2, "I hate testing.");
CREATE TABLE t2 (
id INT NOT NULL
, padding VARCHAR(200) NOT NULL
);
INSERT INTO t2 VALUES (4, "I like to procrastinate");
UPDATE t1 SET id=(SELECT MAX(id) FROM t2);
DROP TABLE t1;
jdaly@rx7:
This produced the following output from the transaction_reader:
jdaly@rx7:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (
id INT NOT NULL
, padding VARCHAR(200) NOT NULL
);
INSERT INTO t1 VALUES (1, "I love testing.");
INSERT INTO t1 VALUES (2, "I hate testing.");
CREATE TABLE t2 (
id INT NOT NULL
, padding VARCHAR(200) NOT NULL
);
INSERT INTO t2 VALUES (4, "I like to procrastinate");
UPDATE t1 SET id=(SELECT MAX(id) FROM t2);
DROP TABLE t1;
DROP TABLE IF EXISTS `t1`;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE t1 ( id INT NOT NULL , padding VARCHAR(200) NOT NULL );
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (1,'I love testing.');
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (2,'I hate testing.');
CREATE TABLE t2 ( id INT NOT NULL , padding VARCHAR(200) NOT NULL );
INSERT INTO `test`.`t2` (`id`,`padding`) VALUES (4,'I like to procrastinate');
START TRANSACTION;
UPDATE `test`.`t1` SET `id`=4 WHERE ; <------
UPDATE `test`.`t1` SET `id`=4 WHERE ; <------
COMMIT;
DROP TABLE `t1`;
SET GLOBAL transaction_
jdaly@rx7:
Changed in drizzle: | |
status: | Incomplete → Won't Fix |
Hi Joe!
So...you've run into an (undocumented) limitation of the transaction log and Drizzle replication. :) All tables which will be replicated must have a primary key or replication/ transaction log does not work.
So, I don't think this is a bug, however we *do* need to put some sort of error message together for when the transaction log notices there is no primary key available. Either we bomb out during the call to TransactionLog: :apply( ) or we bomb out in ReplicationServ ices::push( ). I think I'm favoring the latter option.
Thoughts?
-jay