Re: [firebird-support] Firebird stopped after 190 connections
On 02/11/17 20:26, mirc...@gmail.com [firebird-support] wrote: Hi. Today I had this situation - on CentOS 7 server, while Firebird Superserver was working, suddenly it stopped receiving new connections. First I thought that the server was crashed and not started automatically from the guardian. But ps -aux | greb fb showed that there are 2 firebird processes running - fbguard and fbserver. So I stopped it manually with systemctl stop firebird-superserver. Then in the log I saw a message that server is shutting down with 190 connections to 19 databases indicating that there were working connections. Is there a limitations for the number of connections in Superserver or this is a limitation of the OS? Because the number 190 is strange. Did it actually refuse new connections, or did new connections attempts just hang indefinitely? I have problems with new connections to Firebird just hanging. If you have a connection then you can delete the others (delete from MON$ATTACHMENTS) and everything will be good, otherwise you have to disconnect ALL your clients or just restart the server to get it working again. This was supposedly fixed in one of the 2.5 point releases but it is still happening in 2.5.6 for me. Hamish
Re: [firebird-support] Sorting-Problem on recursive query (window functions)
Hi again, Josef! I like SQL puzzles, and decided to spend a bit of time this afternoon trying to solve yours. Not using Fb 3, my knowledge of windowing functions is too limited to offer any such answer, but I found something that seems to get the result you want in Fb 2.5. Your main problem is that your cte is bottom-up and you try to sort by something that is only available top-down. I.e. you need an additional recursive query. With your testdata, I got your desired output like this: with recursive cte as (select id, id_parent, green, SortText from MyTable where Green = 'Yes' union all select T2.id, T2.id_parent, T2.green, T2.SortText from MyTable T2 join cte on T2.ID = cte.id_parent), cte2 as /*since the parents doesn't have to be green, we cannot limit them here*/ (select t3.id, t3.SortText SortOrder from MyTable t3 where t3.parent_id is null union all select T2.id, cte2.SortOrder||'.'||T2.SortText from MyTable T2 join cte2 on cte2.ID = t2.id_parent) select distinct cte.id, cte.id_parent, cte.green, cte.SortText from cte join cte2 on cte.id = cte2.id order by cte2.SortOrder I fear it will be slow on huge tables, and I cannot guarantee it will work with different data (e.g. can SortText contain some values that makes the '.' in SortOrder mess up the sorting or are your actual data equally nice as your example data?). HTH, Set
Re: [firebird-support] Re: Firebird stopped after 190 connections
Are you seeing any error messages in /var/log/messages or from running dmesg? On Tue, Nov 7, 2017 at 5:37 AM, mirc...@gmail.com [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > Isn't fb_inte_server the process for Firebird Classic? I'm using > superserver. The active process is fbserver. > >
[firebird-support] Re: Firebird stopped after 190 connections
Isn't fb_inte_server the process for Firebird Classic? I'm using superserver. The active process is fbserver.
[firebird-support] Re: Sorting-Problem on recursive query (window functions)
Hi, This shows how the resulting tree should look like. A1 B4 A3 B1 C5 C6 B2 C3 Therefore the resultset should be ordered like this: A1 B4 A3 B1 C5 C6 B2 C3 Each level should be sorted alphabetically within the range of the same parent. Regards, Josef
Re: [firebird-support] Sorting-Problem on recursive query (window functions)
Hi, Please show how exactly resultset should be because i now do not understand your needs. Query return path and return in order a before b what more? Regards,Karol Bieniaszewski Oryginalna wiadomość Od: "josef.gschwendt...@quattro-soft.de [firebird-support]" Data: 07.11.2017 08:53 (GMT+01:00) Do: firebird-support@yahoogroups.com Temat: Re: [firebird-support] Sorting-Problem on recursive query (window functions) Hi, thank you for your ideas. But I think your solution would only work if the anchor query would select treemembers of level 1. Whereas my query starts with "where Green = 'Yes'" and these elements are in different levels. I have read that this should be possible with "window functions", but I can't find an example which works for me. Regards, Josef