MySQL Bugs: #66944: strange situation with non-existing select

Bug #66944	strange situation with non-existing select
Submitted:	24 Sep 2012 10:52	Modified:	27 Sep 2012 15:54
Reporter:	Yurii Korotia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.1.63-0ubuntu0.11.04.1-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
example of select

SELECT 1 FROM t2 new
LEFT JOIN t1 old ON old.st_id = new.st_id
WHERE old.st_id IS NULL;

situation is simple - I want all new records on return.
Database with InnoDB and 300k records just hung.

how can I achieve required goal?

regards

How to repeat:

1. duplicate table like
create t2 select from t1;

2. add some rows to t2

3. try to select new records

current workaround is

SELECT * FROM
(
SELECT * FROM t1
UNION ALL
SELECT * FROM t2
) t
GROUP BY st_id
HAVING count(st_id) = 1;

Thank you for the bug report. Please provide complete test case 5.X.XX MySQL version, create table, insert data, print actual result, expected result. Thanks.

version: 5.1.63-0ubuntu0.11.04.1-log

here's dummy clone of the table against which I'm trying to get new rows

------------- - - - - - - - -

drop database if exists db1;

create database db1;
use db1;

CREATE TABLE t1 (
  `st_id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `column_3` int(11) NOT NULL,
  `column_4` int(11) NOT NULL,
  `column_5` int(11) NOT NULL,
  `column_6` float NOT NULL DEFAULT '0',
  `column_7` float NOT NULL DEFAULT '0',
  `column_8` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `column_9` int(11) NOT NULL DEFAULT '0',
  `column_10` int(11) NOT NULL DEFAULT '0',
  `column_11` tinyint(4) NOT NULL DEFAULT '0',
  `column_12` int(11) NOT NULL DEFAULT '0',
  `column_13` int(11) DEFAULT NULL,
  PRIMARY KEY (`st_id`),
  KEY `column_4` (`column_4`),
  KEY `column_5` (`column_5`),
  KEY `column_9` (`column_9`),
  KEY `column_13` (`column_13`),
  KEY `column_6` (`column_6`),
  KEY `column_3` (`column_3`),
  KEY `created` (`created`),
  KEY `column_11` (`column_11`),
  KEY `column_12` (`column_12`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

delimiter $$

create procedure sp_300k()
begin
    declare c int default 300000;
        
    l:loop
        if c = 0 then
            leave l;
        else
            set c = c - 1;
        end if;
        INSERT INTO t1
(
`column_3`,
`column_4`,
`column_5`,
`column_6`,
`column_7`,
`column_9`,
`column_10`,
`column_11`,
`column_12`,
`column_13`)
VALUES
(
c,c,c,c,c,c,c,c,c,c
);

    end loop l;
end$$

delimiter ;

call sp_300k();

alter table t1 ENGINE=InnoDB;
alter table t1 ENGINE=MyISAM;

create table t2 select * from t1;

INSERT INTO t2
(
`column_3`,
`column_4`,
`column_5`,
`column_6`,
`column_7`,
`column_9`,
`column_10`,
`column_11`,
`column_12`,
`column_13`)
VALUES
(
-1,-1,-1,-1,-1,-1,-1,-1,-1,-1
);

SELECT * FROM t2 new
LEFT JOIN t1 old ON old.st_id = new.st_id
WHERE old.st_id IS NULL;

------------------ - - - - - - 

somehow on this dummy data it worked in 0.5 sec which is acceptable.

Can table be broken?

p.s.

as another possible bug, added row to clonned table t2 has id 0 and date 0000-00-00

yeah. it lags when you add to table t1, not t2, so t2 acts like old

example

INSERT INTO t1
(
`column_3`,
`column_4`,
`column_5`,
`column_6`,
`column_7`,
`column_9`,
`column_10`,
`column_11`,
`column_12`,
`column_13`)
VALUES
(
-1,-1,-1,-1,-1,-1,-1,-1,-1,-1
);

SELECT * FROM t1 new
LEFT JOIN t2 old ON old.st_id = new.st_id
WHERE old.st_id IS NULL;

final slow query. negate my posts above

- - - - -- - - - 

drop database if exists db1;

create database db1;
use db1;

CREATE TABLE t1 (
  `st_id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `column_3` int(11) NOT NULL,
  `column_4` int(11) NOT NULL,
  `column_5` int(11) NOT NULL,
  `column_6` float NOT NULL DEFAULT '0',
  `column_7` float NOT NULL DEFAULT '0',
  `column_8` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `column_9` int(11) NOT NULL DEFAULT '0',
  `column_10` int(11) NOT NULL DEFAULT '0',
  `column_11` tinyint(4) NOT NULL DEFAULT '0',
  `column_12` int(11) NOT NULL DEFAULT '0',
  `column_13` int(11) DEFAULT NULL,
  PRIMARY KEY (`st_id`),
  KEY `column_4` (`column_4`),
  KEY `column_5` (`column_5`),
  KEY `column_9` (`column_9`),
  KEY `column_13` (`column_13`),
  KEY `column_6` (`column_6`),
  KEY `column_3` (`column_3`),
  KEY `created` (`created`),
  KEY `column_11` (`column_11`),
  KEY `column_12` (`column_12`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

delimiter $$

create procedure sp_300k()
begin
    declare c int default 300000;
        
    l:loop
        if c = 0 then
            leave l;
        else
            set c = c - 1;
        end if;
        INSERT INTO t1
(
`column_3`,
`column_4`,
`column_5`,
`column_6`,
`column_7`,
`column_9`,
`column_10`,
`column_11`,
`column_12`,
`column_13`)
VALUES
(
c,c,c,c,c,c,c,c,c,c
);

    end loop l;
end$$

delimiter ;

call sp_300k();

alter table t1 ENGINE=InnoDB;
alter table t1 ENGINE=MyISAM;

create table t2 select * from t1;

INSERT INTO t1
(
`column_3`,
`column_4`,
`column_5`,
`column_6`,
`column_7`,
`column_9`,
`column_10`,
`column_11`,
`column_12`,
`column_13`)
VALUES
(
-1,-1,-1,-1,-1,-1,-1,-1,-1,-1
);

# bugged slow query
SELECT * FROM t1 new
LEFT JOIN t2 old ON old.st_id = new.st_id
WHERE old.st_id IS NULL;

Please, in order to clear out some dilemmas, reply to my questions:

* Does hanging happens on InnoDB as well as on MyISAM tables ???

* Have you tried using InnoDB Plugin on 5.1 ????

* You were able to get a hung server with the last printed test case in each of it's runs ???

* Hanging goes away if only Primary Key is dropped ???

* Can you leave a test case running over night (with PK in the table) and see if it finishes. This is very important as hanging can be just a case of a known deficiency in 5.1 optimizer

* If it hangs even after several hours of running, what status is it in, with "SHOW PROCESSLIST" ??? Is it "Statistics" ????

* If a process does finish, how much time did it take ???

Thanks in advance ...

So, every step I tried this query
SELECT * FROM t1 new
LEFT JOIN t2 old ON old.st_id = new.st_id
WHERE old.st_id IS NULL;

I terminated slow query after 60 seconds except step #5

-- [1] Does hanging happens on InnoDB as well as on MyISAM tables ???
alter table t1 ENGINE=InnoDB;

YES. We moved table back to MyISAM as docs say it is faster. 
In fact, some subqueries went to 40 seconds in InnodDB vs 5 seconds in MyISAM against this table (on 8 cores with 16gb memory)

-- [2] Have you tried using InnoDB Plugin on 5.1 ????
NO

-- [3] You were able to get a hung server with the last printed test case in each of it's runs ???
YES. I never finishes in 60 seconds

-- [4] Hanging goes away if only Primary Key is dropped ???
ALTER TABLE t1 CHANGE COLUMN st_id st_id INT(11) NOT NULL, DROP PRIMARY KEY;

NO

--  [5] Can you leave a test case running over night (with PK in the table) and see if it finishes. This is very important as hanging can be just a case of a known deficiency in 5.1 optimizer
ALTER TABLE t1 CHANGE COLUMN st_id st_id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (st_id);

I did a mistake and put query in workbench, session has died in 600 seconds.
workbench
Error Code: 2013. Lost connection to MySQL server during query	600.510 sec

So, as far as I see mysql process has stopped somewhere within 10 hours. I think with success. If you need end rsult I can rerun it (from console this time).

As a note, during executing mysql took 100% of cpu, and process list looked like this all the time

| 175332 | some_user | x.x.x.x:xxxx | db1                  | Query   |     9 | Sending data | SELECT * FROM t1 new
LEFT JOIN t2 old ON old.st_id = new.st_id
WHERE old.st_id IS NULL

- - - - -

I wonder, maybe it cannot handle this amount of indexes?

tried on 5.5.15 Windows. Same thing.

But after adding PK to clonned table

ALTER TABLE t2 CHANGE COLUMN st_id st_id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (st_id);

slow query works fine. It was ended in 0.5 seconds.
UNION ended in 7 seconds, same as it was before

tests I run on 2gb and 4gb RAM machines with 2 cores and 4 cores cpus respectively

Yurii,

Actually, this is not a bug. Please, let me explain.

When you run:

create table t2 select * from t1;

it will create table t2 with default storage engine (which could be different then t1) and with all columns as in t1. But, NONE OF THE INDEXES will be created !!!!

That means that for each row in t1, entire table t2 will be scanned, from top to bottom.

Hence, much better approach is to do:

create table t2 like t1;

insert into t2 select * from t1;

then your query will always work fast.

I hope that everything is clear.

thanks. I'm sure I will update some files with your snippet.

execution of query with explain showed that mysql didn't use indexes from another table, so both tables were scanned with type ALL. And, right join changed direction of execution, so t1 searches in t2, not t2 in t1

# query 1
explain extended SELECT * FROM t1 new
LEFT JOIN t2 old  ON old.st_id = new.st_id
WHERE old.st_id IS NULL;

# query 2
explain SELECT * FROM t2 old #FORCE KEY (PRIMARY)
RIGHT JOIN t1 new  FORCE KEY (PRIMARY) ON old.st_id = new.st_id
WHERE old.st_id IS NULL;

they are the same with explain

so, it is normal behavior?

__
Yurii

Yurii,

Yes , that is normal behavior, because you are using OUTER JOIN, in your case LEFT JOIN.

With INNER JOIN indices in both tables would have been used, as it is explained in our manual.

It is possible that your test case, without indices on table t2 would run faster with hash joins. Those joins are planned for some future release, not determined yet.

thank you very much! Your code snippet is magic.

Also I ended up with explain extended + show warning\G
It looks more clear now how engine works.

hash joins would be cool.

thank you
have a nice time