MySQL Bugs: #39554: select statement with joins and group by seems to be an endless loop

Bug #39554	select statement with joins and group by seems to be an endless loop
Submitted:	20 Sep 2008 9:08	Modified:	19 Nov 2009 19:16
Reporter:	Pawel Parys	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.28-rc-community-log	OS:	Windows (2003 Server)
Assigned to:		CPU Architecture:	Any
Tags:	endless loop, GROUP BY, join, long, partitions, SELECT

Description:
Hi,

With versions 5.0.xx I was making periodically the following query:

select linia, brygada, typ, opis, count(*) as ile from kasowanie join legenda_bilety on typ_biletu = typ where data_zdarzenia between '2008-08-01' and '2008-08-31' and linia in (81, 82, 83, 84, 87) and rodzaj_skasowania <> 'N' group by linia, brygada, typ;

That MyISAM table has indexes on all columns used and about 500 million records. It usually took at most one hour to get the result.

I have started to use 5.1.2x versions (now the newest one) with partitioning (11 partitions on year(data_zdarzenia)) and tried to execute this query. I expected it will take similar time to execute query. It is now running for almost 40 hours and I'm not sure if it's actually doing anything. Query state is "Copying to tmp table", CPU usage of mysqld is about 0 and memory usage about 80 MB and not changing for hours.

I ran this query on slave and on master server with the same results.

Help me please, st this moment my database is almost unusable because of this bug. :(

How to repeat:
Create table partitioned by year(any_date), fill it with 500 million records (mostly years 2008 and 2007, little 2005 and other years) and try to make query described above. Try to do the same with non-partitioned table and compare time it took (if partitioned finishes at all).

Added Windows version.

Now I tried to remove join and plain query (select linia, brygada, typ, opis, count(*) as ile from kasowanie join legenda_bilety on typ_biletu = typ where data_zdarzenia between '2008-08-01' and '2008-08-31' and linia in (81, 82, 83, 84, 87) and rodzaj_skasowania <> 'N' group by linia, brygada, typ;) executed in 39 seconds...

Wrong copy & paste last comment, query should be:

select linia, brygada, typ_biletu, count(*) as ile from kasowanie where data_zdarzenia between '2008-08-01' and '2008-08-31' and linia in (81, 82, 83, 84, 87) and rodzaj_skasowania <> 'N' group by linia, brygada, typ_biletu;

Thank you for a problem report. Please, send the results of EXPLAIN results for your query from 5.0.x and 5.1.28. Send also EXPLAIN PARTITIONS results from 5.1.28 (see http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html).

EXPLAIN PARTITIONS from 5.1.28:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: legenda_bilety
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 106
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: kasowanie
   partitions: rok2008
         type: ref
possible_keys: i_typ,i_rodzaj_skasowania,i_data,i_linia
          key: i_typ
      key_len: 3
          ref: bilety.legenda_bilety.typ
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

EXPLAIN SELECT from 5.1.28:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: legenda_bilety
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 106
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: kasowanie
         type: ref
possible_keys: i_typ,i_rodzaj_skasowania,i_data,i_linia
          key: i_typ
      key_len: 3
          ref: bilety.legenda_bilety.typ
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

I cannot get EXPLAIN SELECT from 5.0.x as I no longer have access to that database. :(

Thank you for the report.

EXPLAIN PARTITIONS does not have "partitions" part. Did you paste EXPLAIN output? Please send corrected one.

Please also send output of SHOW CREATE TABLE and SHOW TABLE STATUS for tables legenda_bilety and kasowanie

I earlier pasted full EXPLAIN PARTITIONS result - it looks just like above. Only partition rok2008 is used.

mysql> show create table kasowanie\G
*************************** 1. row ***************************
       Table: kasowanie
Create Table: CREATE TABLE `kasowanie` (
  `rodzaj_urzadzenia` enum('ASCOM','MACRO-SYSTEM','AVISTA','MENNICA - TERMINAL','MENNICA - AUTOMAT') DEFAULT NULL,
  `rodzaj_skasowania` varchar(8) DEFAULT NULL,
  `taborowy` smallint(5) unsigned DEFAULT NULL,
  `linia` smallint(5) unsigned DEFAULT NULL,
  `brygada` smallint(5) unsigned DEFAULT NULL,
  `strefa` smallint(5) unsigned DEFAULT NULL,
  `data_zdarzenia` date DEFAULT NULL,
  `czas_zdarzenia` time DEFAULT NULL,
  `nosnik` enum('SC','MG') DEFAULT NULL,
  `typ_biletu` smallint(4) unsigned DEFAULT NULL,
  `data_aktywacji` date DEFAULT NULL,
  `wazny_do` date DEFAULT NULL,
  `przejazdow_pozostalo` smallint(5) unsigned DEFAULT NULL,
  `siec_sprzedazy` tinyint(3) unsigned DEFAULT NULL,
  `nr_sekwencyjny_operacji` smallint(5) unsigned DEFAULT NULL,
  `wykupiono_na_linie` smallint(5) unsigned DEFAULT NULL,
  `kod_odrzucenia` smallint(4) unsigned DEFAULT NULL,
  `seria` tinyint(3) unsigned zerofill DEFAULT NULL,
  `seryjny` mediumint(8) unsigned zerofill DEFAULT NULL,
  `plik_zrodlowy` varchar(100) DEFAULT NULL,
  `pozycja` mediumint(8) unsigned DEFAULT NULL,
  `hash` char(32) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `duplikaty` (`hash`,`data_zdarzenia`),
  KEY `i_sernum` (`seria`,`seryjny`),
  KEY `i_typ` (`typ_biletu`),
  KEY `i_strefa` (`strefa`),
  KEY `plik` (`plik_zrodlowy`,`pozycja`),
  KEY `czas_importu` (`timestamp`),
  KEY `i_rodzaj_skasowania` (`rodzaj_skasowania`),
  KEY `i_nosnik` (`nosnik`),
  KEY `i_data` (`data_zdarzenia`),
  KEY `i_czas` (`czas_zdarzenia`),
  KEY `i_taborowy` (`taborowy`),
  KEY `i_linia` (`linia`),
  KEY `i_brygada` (`brygada`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250 /*!50100 PARTITION BY RANGE (YEAR(data_zdarzenia)) (PARTITION rok1999 VALUES LESS THAN (2000) ENGINE = MyISAM, PARTITION rok2000 VALUES LESS THAN (2001) ENGINE = MyISAM, PARTITION rok2001 VALUES LESS THAN (2002) ENGINE = MyISAM, PARTITION rok2002 VALUES LESS THAN (2003) ENGINE = MyISAM, PARTITION rok2003 VALUES LESS THAN (2004) ENGINE = MyISAM, PARTITION rok2004 VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION rok2005 VALUES LESS THAN (2006) ENGINE = MyIS
AM, PARTITION rok2006 VALUES LESS THAN (2007) ENGINE = MyISAM, PARTITION rok2007 VALUES LESS THAN (2008) ENGINE = MyISAM, PARTITION rok2008 VALUES LESS THAN (2009) ENGINE = MyISAM, PARTITION rok2009 VALUES LESS THAN (2010) ENGINE = MyISAM, PARTITION rok2010 VALUES LESS THAN (2011) ENGINE = MyISAM, PARTITION rok2011 VALUES LESS THAN (2012) ENGINE = MyISAM, PARTITION rok2012 VALUES LESS THAN (2013) ENGINE = MyISAM, PARTITION rok2013 VALUES LESS THAN (2014) ENGINE = MyISAM, PARTITION rok2014 VALUES
 LESS THAN (2015) ENGINE = MyISAM, PARTITION rok2015 VALUES LESS THAN (2016) ENGINE = MyISAM, PARTITION rok2016 VALUES LESS THAN (2017) ENGINE = MyISAM, PARTITION rok2017 VALUES LESS THAN (2018) ENGINE = MyISAM, PARTITION rok2018 VALUES LESS THAN (2019) ENGINE = MyISAM, PARTITION rok2019 VALUES LESS THAN (2020) ENGINE = MyISAM, PARTITION nowsze VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql> show table status like 'kasowanie'\G
*************************** 1. row ***************************
           Name: kasowanie
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 481418708
 Avg_row_length: 131
    Data_length: 63105367824
Max_data_length: 0
   Index_length: 120613948416
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-05-08 14:09:43
    Update_time: 2008-09-21 10:38:37
     Check_time: 2008-05-08 16:37:21
      Collation: cp1250_general_ci
       Checksum: NULL
 Create_options: partitioned
        Comment:
1 row in set (0.86 sec)

mysql> show create table legenda_bilety\G
*************************** 1. row ***************************
       Table: legenda_bilety
Create Table: CREATE TABLE `legenda_bilety` (
  `naglowek` varchar(256) DEFAULT NULL,
  `typ` smallint(4) unsigned DEFAULT NULL,
  `opis` varchar(512) DEFAULT NULL,
  `nosnik` varchar(16) DEFAULT NULL,
  `cena` decimal(6,2) DEFAULT '0.00'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show table status like 'legenda_bilety'\G
*************************** 1. row ***************************
           Name: legenda_bilety
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 106
 Avg_row_length: 76
    Data_length: 8100
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-06-15 17:17:25
    Update_time: 2008-08-17 18:36:31
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Rewriting the query using subqueries helped - I got my results in 5 minutes so this stopped to be critical for me.

Is this problem repeatable for you with newer version, 5.1.40?

I tried again with version 5.1.41 and problem is not showing. Everything seems to be OK. Thanks for your support. :)

Not repeatable with 5.1.41.