MySQL Bugs: #5644: LIKE brings a MySQL Server to hang with 100% cpu

Bug #5644	LIKE brings a MySQL Server to hang with 100% cpu
Submitted:	18 Sep 2004 20:01	Modified:	18 Nov 2004 20:46
Reporter:	Monty Muth	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.4-gamma-nt	OS:	Windows (Windows XP Pro + all patches)
Assigned to:		CPU Architecture:	Any

Description:
This REGEXP query takes 20 seconds to bring a good result:

set @zeitraum='^2003+';
set @wache='SPN_1';

SELECT 
   ELT( WEEKDAY(n1.dzuteilung)+1, 'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag')as EM1Wochentag, 
   if(hour(n1.dzuteilung)>=18 or hour(n1.dzuteilung)<6 ,'Nacht','Tag') as EM1Tageszeit,
  count(*) as Anzahl
FROM nem n1 
JOIN neinsaetze n3 on n1.einsatznummer = n3.einsatznummer and n3.einsatzart not in ('T','W','I') 
JOIN neinsaetze n4 on n2.einsatznummer = n4.einsatznummer and n4.einsatzart not in ('T','W','I') 
JOIN nem n2 ON 
  n2.typ_im_einsatz = 83  
  and n2.standort_wache = @wache
  and n2.deinsatzanfang REGEXP @zeitraum
  and n2.dzuteilung REGEXP @zeitraum   
  and (n2.dzuteilung between n1.dzuteilung and n1.dende) 
  and n2.em<>n1.em 
WHERE 
  n1.typ_im_einsatz = 83 
  and n1.standort_wache = @wache
  and n2.deinsatzanfang REGEXP @zeitraum
  and n2.dzuteilung REGEXP @zeitraum   
GROUP BY EM1Wochentag, EM1Tageszeit
ORDER BY WEEKDAY(n1.dzuteilung)+1, EM1Tageszeit DESC

This LIKE query is the same query as above (4xREGEXP replaced with 4xLIKE), but it runs 45 minutes with 100% cpu load and no result ( i aborted after 45 Minutes):

set @zeitraum='2003%';
set @wache='SPN_1';
 
SELECT 
   ELT( WEEKDAY(n1.dzuteilung)+1, 'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag')as EM1Wochentag, 
   if(hour(n1.dzuteilung)>=18 or hour(n1.dzuteilung)<6 ,'Nacht','Tag') as EM1Tageszeit,
  count(*) as Anzahl
FROM nem n1 
JOIN neinsaetze n3 on n1.einsatznummer = n3.einsatznummer and n3.einsatzart not in ('T','W','I') 
JOIN neinsaetze n4 on n2.einsatznummer = n4.einsatznummer and n4.einsatzart not in ('T','W','I') 
JOIN nem n2 ON 
  n2.typ_im_einsatz = 83  
  and n2.standort_wache = @wache
  and n2.deinsatzanfang LIKE  @zeitraum
  and n2.dzuteilung LIKE @zeitraum
  and (n2.dzuteilung between n1.dzuteilung and n1.dende) 
  and n2.em<>n1.em 
WHERE 
  n1.typ_im_einsatz = 83 
  and n1.standort_wache = @wache
  and n1.deinsatzanfang LIKE @zeitraum
  and n1.dzuteilung LIKE  @zeitraum
GROUP BY EM1Wochentag, EM1Tageszeit
ORDER BY WEEKDAY(n1.dzuteilung)+1, EM1Tageszeit DESC
 

I know this is a bug, but i cant give you more informations. I'm not allowed to release this 2 tables structur to the public.

I used the mysql.exe to make this querys.

How to repeat:
i cant give you the tables

Suggested fix:
use REGEXP instead of LIKE

Yes please upload the tables (zipped) with a file name
identifying this big report at:

ftp://ftp.mysql.com/pub/mysql/upload/

thanks

I understand that you cannot release your tables to public.
But you can upload them to bugdb as "private" data (there's checkbox for that) - in this case only MySQL AB staff will be able to see them.

If you cannot do it either, you may try to create a test case, removing as much of the sensitive data from your tables as possible. E.g., use ALTER TABLE to delete all the columns and keys that are not necessary for the bug (try to delete them one by one, and repeat the bug after each deletion). Try to remove some rows leaving only the minimum. rename all the columns/tables that left.

After that procedure you should be able to share your data - there won't be anything sensitive left :)

Unfortunately we cannot fix a bug that we cannot repeat, so test case is absolutely necessary.

hmm the database is 2 GB big, makes no sense. 

You can delete this bug if you want.

:(

Actually I would prefer to fix the bug...

Could you tell that part of the table definition that concerns columns dzuteilung and deinsatzanfang. What type they are of, what charset/collation (if applicable), are they indexed, etc...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".