Re: [firebird-support] Cannot restore a database because of failed unique key index
Hello, Your query uses index, which is correct (it contains unique values). Try to run this query on partially restored database. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) Hi, We use Firebird 2.5.1 on Linux 64 bit Firebird fails to restore because of a unique constraint( see below ). However when interrogating the data, no duplicate is found. ( see below SQL, unique key is on the grouped by fields ) How can this be? Kind Regards, Robert. SELECT ID_VN, CD_ITM_SPR FROM AS_ITM_SPR GROUP BY ID_VN, CD_ITM_SPR HAVING COUNT(*) 1 gbak:activating and creating deferred index AK_AS_ITM_SPR gbak:cannot commit index AK_AS_ITM_SPR gbak: ERROR:attempt to store duplicate value (visible to active transactions) in unique index AK_AS_ITM_SPR gbak: ERROR:action cancelled by trigger (3) to preserve data integrity gbak: ERROR:Cannot deactivate index used by a PRIMARY/UNIQUE constraint gbak:Exiting before completion due to errors
Re: [firebird-support] Cannot restore a database because of failed unique key index
Your query needs an aggregate field. Count. Alan On Oct 24, 2012 1:49 PM, sir_wally_lewis rgilland1...@gmail.com wrote: Hi, We use Firebird 2.5.1 on Linux 64 bit Firebird fails to restore because of a unique constraint( see below ). However when interrogating the data, no duplicate is found. ( see below SQL, unique key is on the grouped by fields ) How can this be? Kind Regards, Robert. SELECT ID_VN, CD_ITM_SPR FROM AS_ITM_SPR GROUP BY ID_VN, CD_ITM_SPR HAVING COUNT(*) 1 gbak:activating and creating deferred index AK_AS_ITM_SPR gbak:cannot commit index AK_AS_ITM_SPR gbak: ERROR:attempt to store duplicate value (visible to active transactions) in unique index AK_AS_ITM_SPR gbak: ERROR:action cancelled by trigger (3) to preserve data integrity gbak: ERROR:Cannot deactivate index used by a PRIMARY/UNIQUE constraint gbak:Exiting before completion due to errors ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed]
[firebird-support] Cannot connect to server under heavy load
Hi! Cannot connect to server under heavy load. I get connection rejected by remote interface (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) L3S-4 Wed Oct 24 14:04:28 2012 I/O error for file C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:28 2012 lock conflict on no wait transaction L3S-4 Wed Oct 24 14:04:32 2012 Error while trying to access file L3S-4 Wed Oct 24 14:04:32 2012 no permission for read-write access to database C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:36 2012 The process cannot access the file because it is being used by another process
Re: [firebird-support] Cannot connect to server under heavy load
On Wed, 24 Oct 2012 12:18:01 -, ma_golyo ma_go...@yahoo.com wrote: Hi! Cannot connect to server under heavy load. I get connection rejected by remote interface (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) What is the exact version of your Firebird? Mark
[firebird-support] Re: Cannot connect to server under heavy load
Hi! 2.0.7.13318 --- In firebird-support@yahoogroups.com, Mark Rotteveel mark@... wrote: On Wed, 24 Oct 2012 12:18:01 -, ma_golyo ma_golyo@... wrote: Hi! Cannot connect to server under heavy load. I get connection rejected by remote interface (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) What is the exact version of your Firebird? Mark
[firebird-support] Re: Cannot connect to server under heavy load
Sometimes I get Unable to complete network request to host l3s-4 (335544721) error not 335544421. In this case nothing apperars in firebird.log. --- In firebird-support@yahoogroups.com, ma_golyo ma_golyo@... wrote: Hi! Cannot connect to server under heavy load. I get connection rejected by remote interface (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) L3S-4 Wed Oct 24 14:04:28 2012 I/O error for file C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:28 2012 lock conflict on no wait transaction L3S-4 Wed Oct 24 14:04:32 2012 Error while trying to access file L3S-4 Wed Oct 24 14:04:32 2012 no permission for read-write access to database C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:36 2012 The process cannot access the file because it is being used by another process
[firebird-support] Re: Cannot connect to server under heavy load
Now I get cannot attach to password database (335544653) error. Same log as first time. --- In firebird-support@yahoogroups.com, ma_golyo ma_golyo@... wrote: Hi! Cannot connect to server under heavy load. I get connection rejected by remote interface (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) L3S-4 Wed Oct 24 14:04:28 2012 I/O error for file C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:28 2012 lock conflict on no wait transaction L3S-4 Wed Oct 24 14:04:32 2012 Error while trying to access file L3S-4 Wed Oct 24 14:04:32 2012 no permission for read-write access to database C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:36 2012 The process cannot access the file because it is being used by another process
[firebird-support] Compound foreign key null value bug?
I'm modifying a foreign key from one column to two. I dropped the original foreign key from the child table, populated the new columns, and then created the new key. I forgot to populate the new column in the child table, so all the relationships looked like this: ChildParent --- Col1:null - Col1:A Col2:B- Col2:B The new foreign keys were created without any errors. Since (null,B) doesn't match anything, shouldn't the creation have failed?
[firebird-support] Re: Cannot connect to server under heavy load
Another strange fact : Running 1 application with 250 thread where each thread connects to 2 database and runs 2 select works perfectly, BUT Running 50 application with 5 thread where each thread connects to 2 database and runs 2 select throw errors mentioned eralier. --- In firebird-support@yahoogroups.com, ma_golyo ma_golyo@... wrote: Hi! Cannot connect to server under heavy load. I get connection rejected by remote interface (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) L3S-4 Wed Oct 24 14:04:28 2012 I/O error for file C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:28 2012 lock conflict on no wait transaction L3S-4 Wed Oct 24 14:04:32 2012 Error while trying to access file L3S-4 Wed Oct 24 14:04:32 2012 no permission for read-write access to database C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:36 2012 The process cannot access the file because it is being used by another process
RE: [firebird-support] Cannot connect to server under heavy load
Cannot connect to server under heavy load. I get connection rejected by remote interface (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) L3S-4 Wed Oct 24 14:04:28 2012 I/O error for file C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:28 2012 lock conflict on no wait transaction L3S-4 Wed Oct 24 14:04:32 2012 Error while trying to access file L3S-4 Wed Oct 24 14:04:32 2012 no permission for read-write access to database C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:36 2012 The process cannot access the file because it is being used by another process These errors suggest that there is some other process which is accessing the files... You need to find that other process, the problem may not exist in Firebird at all. Sean
[firebird-support] Solution for a redundant join?
The following simple query produces the results below: SELECT SCHEDULE.DATE_TIME, TEAMS.TEAM FROM SCHEDULE LEFT JOIN TEAMS ON SCHEDULE.HOME_TEAMID=TEAMS.PRIMARYKEY ***QUERY RESULTS (Showing 'Home Team' Column)*** 12/01/2012 TeamA 12/21/2012 TeamB 12/25/2012 TeamC How do I include the corresponding 'Away Team' column? Please see tables below: ***SCHEDULE TABLE*** PRIMARYKEY HOME_TEAMID AWAY_TEAMID DATE_TIME 1 1212/01/2012 2 2312/21/2012 3 3112/25/2012 ***TEAMS TABLE*** PRIMARYKEY TEAM_NAME 1 TeamA 2 TeamB 3 TeamC Please advise, thank you!
RE: [firebird-support] Solution for a redundant join?
The following simple query produces the results below: SELECT SCHEDULE.DATE_TIME, TEAMS.TEAM FROM SCHEDULE LEFT JOIN TEAMS ON SCHEDULE.HOME_TEAMID=TEAMS.PRIMARYKEY ***QUERY RESULTS (Showing 'Home Team' Column)*** 12/01/2012 TeamA 12/21/2012 TeamB 12/25/2012 TeamC How do I include the corresponding 'Away Team' column? Use tables aliases to include multiple references to a single table. SELECT SCHEDULE.DATE_TIME, HomeTeam.TEAM, AwayTeam.Team FROM SCHEDULE LEFT JOIN TEAMS HomeTeam ON HomeTeam.PRIMARYKEY = SCHEDULE.HOME_TEAMID LEFT JOIN TEAMS AwayTeam ON AwayTeam.PRIMARYKEY = SCHEDULE.AWAY_TEAMID Sean
Re: [firebird-support] Solution for a redundant join?
Try using aliases: SELECT SCHEDULE.DATE_TIME, HOME_TEAM.TEAM as HomeTeamName, AWAY_TEAM.TEAM as AwayTeamName FROM SCHEDULE LEFT JOIN TEAMS HOME_TEAM ON SCHEDULE.HOME_TEAMID=HOME_TEAM.PRIMARYKEY LEFT JOIN TEAMS AWAY_TEAM ON SCHEDULE.AWAY_TEAMID=AWAY_TEAM.PRIMARYKEY On Wed, Oct 24, 2012 at 1:42 PM, Jeff jeff_j_dun...@yahoo.com wrote: ** The following simple query produces the results below: SELECT SCHEDULE.DATE_TIME, TEAMS.TEAM FROM SCHEDULE LEFT JOIN TEAMS ON SCHEDULE.HOME_TEAMID=TEAMS.PRIMARYKEY ***QUERY RESULTS (Showing 'Home Team' Column)*** 12/01/2012 TeamA 12/21/2012 TeamB 12/25/2012 TeamC How do I include the corresponding 'Away Team' column? Please see tables below: ***SCHEDULE TABLE*** PRIMARYKEY HOME_TEAMID AWAY_TEAMID DATE_TIME 1 1 2 12/01/2012 2 2 3 12/21/2012 3 3 1 12/25/2012 ***TEAMS TABLE*** PRIMARYKEY TEAM_NAME 1 TeamA 2 TeamB 3 TeamC Please advise, thank you! [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Re: Solution for a redundant join?
--- In firebird-support@yahoogroups.com, Leyne, Sean Sean@... wrote: Use tables aliases to include multiple references to a single table. SELECT SCHEDULE.DATE_TIME, HomeTeam.TEAM, AwayTeam.Team FROM SCHEDULE LEFT JOIN TEAMS HomeTeam ON HomeTeam.PRIMARYKEY = SCHEDULE.HOME_TEAMID LEFT JOIN TEAMS AwayTeam ON AwayTeam.PRIMARYKEY = SCHEDULE.AWAY_TEAMID Sean Sean, aliases did the trick, thank you kindly!
[firebird-support] How do you 0/null results to a GROUP BY query
Hi All I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 etc. Any help will be appreciated! Cornie van Schoor InfoStar Software
RE: [firebird-support] How do you 0/null results to a GROUP BY query
I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 Use COALESCE(), as in: Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(COALESCE( DUE, 0)) from CLIENT_INVOICES group by YEARNO, WEEKNO. Sean
Re: [firebird-support] How do you 0/null results to a GROUP BY query
try something like this:
with recursive
Datas (Data) as
(select
Cast('2009-01-01' as Date)
From
rdb$database
union all
Select
Data + 1
From
Datas
Where
Data + 1 = Cast('2009-01-31' as Date))
select
D.Data, sum(PVI.QuantidadeOriginal)
from
Datas D left join
PedidoVendaItem PVI on (D.Data = PVI.DataEntrega)
group by
D.Data
change the field/table names to fit you needs
HTH
Em 24/10/2012 19:54, cornievs escreveu:
Hi All
I have query which extract the sales per day from a table
Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO,
SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO.
It works 100%, but only returns the dates with sales, I need it to also
include the dates with zero sales, for example:
YEARNO DAYNO SUM
2012 015000
2012 026000
2012 030 (or null will be fine)
2012 047000
etc.
Any help will be appreciated!
Cornie van Schoor
InfoStar Software
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups Links
Re: [firebird-support] How do you 0/null results to a GROUP BY query
But this won't return rows for dates where there is no record in the source table. On Wed, Oct 24, 2012 at 6:02 PM, Leyne, Sean s...@broadviewsoftware.comwrote: ** I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 01 5000 2012 02 6000 2012 03 0 (or null will be fine) 2012 04 7000 Use COALESCE(), as in: Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(COALESCE( DUE, 0)) from CLIENT_INVOICES group by YEARNO, WEEKNO. Sean [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it.
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:21, Doug Chamberlin escreveu: On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it. I used to do this way, but with CTE you create that table dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days
Re: [firebird-support] Re: Solution for a redundant join?
On Wed, Oct 24, 2012 at 4:34 PM, Jeff jeff_j_dun...@yahoo.com wrote: Use tables aliases to include multiple references to a single table. SELECT SCHEDULE.DATE_TIME, HomeTeam.TEAM, AwayTeam.Team FROM SCHEDULE LEFT JOIN TEAMS HomeTeam ON HomeTeam.PRIMARYKEY = SCHEDULE.HOME_TEAMID LEFT JOIN TEAMS AwayTeam ON AwayTeam.PRIMARYKEY = SCHEDULE.AWAY_TEAMID Sean, aliases did the trick, thank you kindly! Just out of curiosity, do you have any schedule records for which there are not matching home teams and away teams? If not, I would use two inner joins rather than two outer joins. That lets the optimizer do a better job of planning the execution path. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: Em 24/10/2012 20:21, Doug Chamberlin escreveu: On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it. I used to do this way, but with CTE you create that table dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days I saw that. However, I don't see the advantage. Using a reference table is simpler and clearer than using CTE or other code to generate the reference values.
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:37, Doug Chamberlin escreveu: On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: Em 24/10/2012 20:21, Doug Chamberlin escreveu: On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it. I used to do this way, but with CTE you create that table dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days I saw that. However, I don't see the advantage. Using a reference table is simpler and clearer than using CTE or other code to generate the reference values. One don't need to refill the values, like at the beginning of each year... of course you could pre-fill i with a century of days... :)
Re: [firebird-support] How do you 0/null results to a GROUP BY query
I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 01 5000 2012 02 6000 2012 03 0 (or null will be fine) 2012 04 7000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it. I used to do this way, but with CTE you create that table dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days I saw that. However, I don't see the advantage. Using a reference table is simpler and clearer than using CTE or other code to generate the reference values. Thanks! I will most likely go with the reference table(s) option, but I will test both. Regards Cornie [Non-text portions of this message have been removed]
