[MDEV-20611] MRR scan over partitioned InnoDB table produces "Out of memory" error - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.3.21, 10.4.11, 10.5.0
Component/s: Partitioning
Labels:
None

Description

Run this testcase

--source include/have_partition.inc

--source include/have_innodb.inc

create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table one_k(a int);

insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;

create table t1 (

  ID bigint(20) NOT NULL AUTO_INCREMENT,

  part_id int,

  key_col int,

  col2 int,

  key(key_col),

  PRIMARY KEY (ID,part_id)

) ENGINE=InnoDB

 PARTITION BY RANGE (part_id)

(PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB,

 PARTITION p2 VALUES LESS THAN (7) ENGINE = InnoDB,

 PARTITION p3 VALUES LESS THAN (10) ENGINE = InnoDB

);

insert into t1 select

  A.a+10*B.a,

  A.a,

  B.a,

from ten A, ten B;

set optimizer_switch='mrr=on';

explain

select * from t1 force index (key_col) where key_col < 10;

select * from t1 force index (key_col) where key_col < 10;

drop table ten,one_k,t1;

And it will fail with:

mysqltest: At line 35: query 'select * from t1 force index (key_col) where key_col < 10' failed: 1041: Out of memory.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mariadb-logo.png
5 kB
2019-11-19 15:10

Issue Links

relates to

MDEV-21068 Test MRR scans over partitioned tables

Closed

Activity

Ascending order - Click to sort in descending order

View 6 older comments

Assen Totin (Inactive) added a comment - 2019-11-12 16:01

As of the issue title, this seems not to be InnoDB-specific - just reproduced the same with MySIAM & Aria on customer request.

Assen Totin (Inactive) added a comment - 2019-11-12 16:01 As of the issue title, this seems not to be InnoDB-specific - just reproduced the same with MySIAM & Aria on customer request.

Sergei Petrunia added a comment - 2019-11-15 20:43

The patch is in bb-10.3-mdev20611 branch

Sergei Petrunia added a comment - 2019-11-15 20:43 The patch is in bb-10.3-mdev20611 branch

Sergei Petrunia added a comment - 2019-11-16 15:14

The fix is pushed into 10.3 branch

Sergei Petrunia added a comment - 2019-11-16 15:14 The fix is pushed into 10.3 branch

Assen Totin (Inactive) added a comment - 2019-11-19 15:10

Hi, Sergei,

Many thanks for your quick help on this.

Apologies for the stupid question, is this fix going to 10.4 mainline also?
It seems to be equally affected as 10.3, but ticket only says 10.3 got
updated.

With my best regards,

Assen

On Tue, 2019-11-19 at 11:14 +0000, Sergei Petrunia (Jira) wrote:

[

https://jira.mariadb.org/browse/MDEV-20611?focusedWorklogId=62211&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-62211

]

Sergei Petrunia logged work on ~~MDEV-20611~~:

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

Author: Sergei Petrunia

Created on: 2019-11-19 11:13

Start Date: 2019-11-19 11:13

Worklog Time Spent: 1d

Issue Time Tracking

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

Time Spent: 2d 1.5h (was: 1d 1.5h)

Worklog Id: (was: 62211)

MRR scan over partitioned InnoDB table produces "Out of memory" error

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

Key: ~~MDEV-20611~~

URL:

https://jira.mariadb.org/browse/MDEV-20611

Project: MariaDB Server

Issue Type: Bug

Components: Partitioning

Affects Versions: 10.3

Reporter: Sergei Petrunia

Assignee: Sergei Petrunia

Priority: Major

Fix For: 10.3.21

Original Estimate: 0d

Time Spent: 2d 1.5h

Remaining Estimate: 0d

Run this testcase

--source include/have_partition.inc

--source include/have_innodb.inc

create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table one_k(a int);

insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;

create table t1 (

  ID bigint(20) NOT NULL AUTO_INCREMENT,

  part_id int,

  key_col int,

  col2 int,

  key(key_col),

  PRIMARY KEY (ID,part_id)

) ENGINE=InnoDB

 PARTITION BY RANGE (part_id)

(PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB,

 PARTITION p2 VALUES LESS THAN (7) ENGINE = InnoDB,

 PARTITION p3 VALUES LESS THAN (10) ENGINE = InnoDB

);

insert into t1 select

  A.a+10*B.a,

  A.a,

  B.a,

from ten A, ten B;

set optimizer_switch='mrr=on';

explain

select * from t1 force index (key_col) where key_col < 10;

select * from t1 force index (key_col) where key_col < 10;

drop table ten,one_k,t1;

And it will fail with:

mysqltest: At line 35: query 'select * from t1 force index (key_col)

where key_col < 10' failed: 1041: Out of memory.

–

This message was sent by Atlassian Jira

(v7.13.1#713001)

–

Assen Totin

Senior Engineer R&D

mariadb.com
<https://www.google.com/url?q=https%3A%2F%2Fmariadb.com%2F&sa=D&sntz=1&usg=AFQjCNFS4fJgsXfpe7F6PINCUzBni5FtsA>
<https://www.google.com/url?q=https%3A%2F%2Ftwitter.com%2Fmariadb&sa=D&sntz=1&usg=AFQjCNERCXZqmq46PUqauiXzqbzGqgx9kQ>
<https://www.google.com/url?q=https%3A%2F%2Fwww.facebook.com%2FMariaDB.dbms%2F&sa=D&sntz=1&usg=AFQjCNF1Dsdcq_ddJzBgHTcTNYMpWunUZQ>
<https://www.google.com/url?q=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fmariadb-corporation%2F&sa=D&sntz=1&usg=AFQjCNGxMQaiP0NxDaeYJLxzWpSGQl4z-A>

Assen Totin (Inactive) added a comment - 2019-11-19 15:10 Hi, Sergei, Many thanks for your quick help on this. Apologies for the stupid question, is this fix going to 10.4 mainline also? It seems to be equally affected as 10.3, but ticket only says 10.3 got updated. With my best regards, Assen On Tue, 2019-11-19 at 11:14 +0000, Sergei Petrunia (Jira) wrote: [ https://jira.mariadb.org/browse/MDEV-20611?focusedWorklogId=62211&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-62211 ] Sergei Petrunia logged work on MDEV-20611 : ------------------------------------------ Author: Sergei Petrunia Created on: 2019-11-19 11:13 Start Date: 2019-11-19 11:13 Worklog Time Spent: 1d Issue Time Tracking ------------------- Time Spent: 2d 1.5h (was: 1d 1.5h) Worklog Id: (was: 62211) MRR scan over partitioned InnoDB table produces "Out of memory" error ---------------------------------------------------------------------- Key: MDEV-20611 URL: https://jira.mariadb.org/browse/MDEV-20611 Project: MariaDB Server Issue Type: Bug Components: Partitioning Affects Versions: 10.3 Reporter: Sergei Petrunia Assignee: Sergei Petrunia Priority: Major Fix For: 10.3.21 Original Estimate: 0d Time Spent: 2d 1.5h Remaining Estimate: 0d Run this testcase --source include/have_partition.inc --source include/have_innodb.inc create table ten(a int ); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k(a int ); insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table t1 ( ID bigint (20) NOT NULL AUTO_INCREMENT, part_id int , key_col int , col2 int , key (key_col), PRIMARY KEY (ID,part_id) ) ENGINE=InnoDB PARTITION BY RANGE (part_id) (PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (10) ENGINE = InnoDB ); insert into t1 select A.a+10*B.a, A.a, B.a, 123456 from ten A, ten B; set optimizer_switch= 'mrr=on' ; explain select * from t1 force index (key_col) where key_col < 10; select * from t1 force index (key_col) where key_col < 10; drop table ten,one_k,t1; And it will fail with: mysqltest: At line 35: query 'select * from t1 force index (key_col) where key_col < 10' failed: 1041: Out of memory. – This message was sent by Atlassian Jira (v7.13.1#713001) – Assen Totin Senior Engineer R&D mariadb.com < https://www.google.com/url?q=https%3A%2F%2Fmariadb.com%2F&sa=D&sntz=1&usg=AFQjCNFS4fJgsXfpe7F6PINCUzBni5FtsA > < https://www.google.com/url?q=https%3A%2F%2Ftwitter.com%2Fmariadb&sa=D&sntz=1&usg=AFQjCNERCXZqmq46PUqauiXzqbzGqgx9kQ > < https://www.google.com/url?q=https%3A%2F%2Fwww.facebook.com%2FMariaDB.dbms%2F&sa=D&sntz=1&usg=AFQjCNF1Dsdcq_ddJzBgHTcTNYMpWunUZQ > < https://www.google.com/url?q=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fmariadb-corporation%2F&sa=D&sntz=1&usg=AFQjCNGxMQaiP0NxDaeYJLxzWpSGQl4z-A >

Sergei Petrunia added a comment - 2019-11-21 07:53

Hi assen.totin,

Yes, the fix will be merged into 10.4 tree before the next 10.4 release. I've now set the fixVersion accordingly.

Sergei Petrunia added a comment - 2019-11-21 07:53 Hi assen.totin , Yes, the fix will be merged into 10.4 tree before the next 10.4 release. I've now set the fixVersion accordingly.

MariaDB Server

MRR scan over partitioned InnoDB table produces "Out of memory" error

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration