Hi all.

I have a solaris netra t1 running solaris 7 with all the latest patches.

here i have installaed mysql 3.23.36 with the following compiler/configure options: CC=gcc CFLAGS="-O6 -mcpu=v8 -Wa,-xarch=v8plusa" CXX=gcc CXXFLAGS="-O6 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 -Wa,-xarch=v8plusa" export CC CFLAGS CXX CXXFLAGS ./configure --prefix=/usr/local/mysql-3.23.36 --enable-thread-safe-client --without-debug --with-charset=danish --enable-assembler --with-named-z-libs=no make && make install

(gcc version 2.95.2 19991024 (release))

now i have a database with 30 tables or so and with about 100.000 rows all in all.

for sthe tabels used below the sizes looks like this:

Udsendelser (ud) has approx 20.000 rows Kanal (ka) has approx 150 rows Genre (ge) has 96 rows Periode (pe) has 5 rows

and the tables all have indexes the right places to speed up queries.

now when i run the sql shown below mysqld crashes every second time or so with the following output in the ../mysql/var/*.err file:

mysqld got signal 10; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died.

the server has no load to speak of at the time, so it doesn't seem stress induced.

i also tried recompiling mysql with CC=gcc CFLAGS="-02" CXX=gcc CXXFLAGS="-O2 -felide-constructors -fno-exceptions -fno-rtti"

but it didn't have any effect, the problem persistet.

I have overcome this problem using php scripting to generate more simple sql-queries, but i thought you guys should know this.

the problem, it seems is in the part of the join that says:

and pe.ID=1 and (pe.Starttid >= 000000 and pe.Sluttid <= 060000 and date_add(curdate(), interval 1 day)=ud.dato and ud.StartTid >= if(pe.Starttid<sec_to_time(time_to_sec(curtime())-60*ud.Laengde),sec_to_time(time_to_sec(curtime())-60*ud.Laengde),pe.Starttid) and ud.StartTid < pe.Sluttid or pe.Starttid >= 060000 and pe.Sluttid <= 240000 and date_add(curdate(),interval 0 day)=ud.dato and ud.StartTid >= if(pe.Starttid<sec_to_time(time_to_sec(curtime())-60*ud.Laengde),sec_to_time(time_to_sec(curtime())-60*ud.Laengde),pe.Starttid) and ud.StartTid < pe.Sluttid or pe.Starttid >= 060000 and pe.Sluttid <= 060000 and (date_add(curdate(),interval 0 day) = ud.Dato and ud.StartTid >= if(pe.Starttid<sec_to_time(time_to_sec(curtime())-60*ud.Laengde),sec_to_time(time_to_sec(curtime())-60*ud.Laengde),pe.Starttid) or date_add(curdate(),interval 1 day) = ud.Dato and ud.StartTid < pe.Sluttid))

since the server stopped crashing when we rewrote this into php if's

sorry that i didn't use mysql-bug script but i am kinda busy developing under pressure, and this was easier for me.

Regards

Martin Jespersen

SQL:

select distinct ud.*, ka.ShortName, ka.LongName, ge.SelectorGenre as GenreDetail, if((if(curtime()>ud.StartTid,time_to_sec(curtime()),time_to_sec(curtime())+86400)-time_to_sec(ud.StartTid))/((time_to_sec(ud.StartTid)+(ud.Laengde*60))-time_to_sec(ud.StartTid))*100>100 or (if(curtime()>ud.StartTid,time_to_sec(curtime()),time_to_sec(curtime())+86400)-time_to_sec(ud.StartTid))/((time_to_sec(ud.StartTid)+(ud.Laengde*60))-time_to_sec(ud.StartTid))*100<0,0,(if(curtime()>ud.StartTid,time_to_sec(curtime()),time_to_sec(curtime())+86400)-time_to_sec(ud.StartTid))/((time_to_sec(ud.StartTid)+(ud.Laengde*60))-time_to_sec(ud.StartTid))*100) as Progress, time_format(ud.StartTid,'%H:%i') as StartTime, time_format(sec_to_time(mod(time_to_sec(ud.StartTid)+(ud.Laengde*60),86400)),'%H:%i') as EndTime from Udsendelser ud, Kanal ka, Genre ge, Periode pe where ud.KanalID=ka.ID and ud.GenreID=ge.ID and ge.GenreGruppeID=2 and pe.ID=1 and (pe.Starttid >= 000000 and pe.Sluttid <= 060000 and date_add(curdate(), interval 1 day)=ud.dato and ud.StartTid >= if(pe.Starttid<sec_to_time(time_to_sec(curtime())-60*ud.Laengde),sec_to_time(time_to_sec(curtime())-60*ud.Laengde),pe.Starttid) and ud.StartTid < pe.Sluttid or pe.Starttid >= 060000 and pe.Sluttid <= 240000 and date_add(curdate(),interval 0 day)=ud.dato and ud.StartTid >= if(pe.Starttid<sec_to_time(time_to_sec(curtime())-60*ud.Laengde),sec_to_time(time_to_sec(curtime())-60*ud.Laengde),pe.Starttid) and ud.StartTid < pe.Sluttid or pe.Starttid >= 060000 and pe.Sluttid <= 060000 and (date_add(curdate(),interval 0 day) = ud.Dato and ud.StartTid >= if(pe.Starttid<sec_to_time(time_to_sec(curtime())-60*ud.Laengde),sec_to_time(time_to_sec(curtime())-60*ud.Laengde),pe.Starttid) or date_add(curdate(),interval 1 day) = ud.Dato and ud.StartTid < pe.Sluttid)) order by ud.Dato, ud.StartTid, ka.ShortName limit 0,21