MySQL Bugs: #3672: MySQL service shutdown on complex script execution (on Windows)

Bug #3672	MySQL service shutdown on complex script execution (on Windows)
Submitted:	6 May 2004 3:02	Modified:	8 May 2004 19:52
Reporter:	Jordi Rovira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Windows (Windows 2000 Server SP4)
Assigned to:		CPU Architecture:	Any

Description:
If I add more complexity to a SQL script, MySQL service shutdown, and need to start again (from Services on Control Panel of MSWindows).
The added "percentatge_comercial" query_field is verified. 
The joins are correct.  If you want structure & data of database, email-me
to rcorpNOSPAMgrnDOTes

SELECT 
  ******If I add this section, service shutdown************
   (SELECT coalesce(percentatge_comercial , 0 )
   FROM contractes_renovacions cr
   where cr.id_article = cl.id_article
   and cr.num_renovacio <= 
truncate( 
   ( ( (year(cast('2005-12-31' as date)) * 12 +
   month(p.data_pago)  ) -
   (year(cast((select min(data_pago) from pagos pxx where pxx.id_producte = cl.id_producte) as date)) * 12 +
   month(cast((select min(data_pago) from pagos pxx where pxx.id_producte = cl.id_producte) as date))) )+1) / 
   a.periodicitat 
   ,0)
   order by cr.num_renovacio
   limit 1) as percentatge_comercial,
 *******end of section*************************
   cl.id_producte,
   c.id_client, cl.id_contracte, c.data_contracte, cl.id_producte , cl.descripcio, c.id_comercial,
   concat(cast(year(cast('2005-12-31' as date)) as char),'-',
               cast(month(p.data_pago) as char) ,'-', 
               cast(dayofmonth(p.data_pago) as char))  
      as facturas_lin_data_linia, 
   c.id_client , fl.id_producte , p.preu_pago 
from clients ct , contractes c , pagos p , contractes_lin cl, articles a
          left join facturas_lin fl
          on 
           p.id_producte = fl.id_producte
           and month(p.data_pago) = month(fl.data_linia)
           and dayofmonth(p.data_pago) = dayofmonth(fl.data_linia)
           and year(fl.data_linia) = year(cast('2005-12-31' as date)) 
where
   ct.id_client = c.id_client
   and c.id_contracte = cl.id_contracte
   and cl.id_producte = p.id_producte
   and (month(p.data_pago) *100) + dayofmonth(p.data_pago) >= 
      (month(cast('2005-06-01' as date)) * 100) + dayofmonth(cast('2005-06-01' as date))
   and (month(p.data_pago) *100) + dayofmonth(p.data_pago) <= 
      (month(cast('2005-12-31' as date)) * 100) + dayofmonth(cast('2005-12-31' as date))
   and fl.id_producte is null
   and cl.id_article = a.id

How to repeat:
Executing again the script

Hi,

If the service needs to be re-started means that the query
crashed the server. I need to test this on 4.1.2 but you
don't had provide enough information, so if you can to
provide me a table snapshot and the script I will appreciate.

You can download the zipped file at:

ftp://support.mysql.com/pub/mysql/secret/

and report here when done.

Thanks in advance.

Hi !

I reported a file explaining problem with example and database struct & data.
I upload the file on mysql ftp site.

Can you tell me if in 4.1.2 version work fine ?
Thanks a lot
  Jordi

Thank you for to provide the test case, however after I created the
tables and inserted data, applying the offended query I got:

ERROR 1146 (42S02): Table 'test.articles' doesn't exist

it something missed in your 2 files?

Gracias

I'm sorry.

I put a file on FTP site with struct and data for table 'articles'

Thanks a lot again
(Muchas gracias Miguel)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Thank you for the test case.