Well, my brain become active again and i'm doing a copy of the database and will run the gfix against the copy. Stop firebird/copy database/start firebird.
On Thu, Nov 17, 2011 at 9:34 AM, Leonardo Carneiro <[email protected]>wrote: > Hi guys, > > Since the source database is still in production, i'm trying at max to > avoid running a gfix (although this seems to be inevitable) to avoid any > downtime. > > Anyway, i did follow Svein tip and tried > > SELECT MyIndexedField+0, count(*) FROM MyTable GROUP BY 1 HAVING count(*) > > 1 > > instead of > > SELECT MyIndexedField, count(*) FROM MyTable GROUP BY 1 HAVING count(*) > > 1 > > > AFAIK, adding the '+0' really tricks the planner and force a table scan. > Would still worth a try to run a gfix or this test exclude any chance of > having a page failure? The odd thing is that in the restored database i > have not one, but at least 10 dup keys in more than one table. > > On Thu, Nov 17, 2011 at 7:44 AM, Leonardo Carneiro <[email protected] > > wrote: > >> Tks for the tips guys. I'll try both of then. >> >> >> On Thu, Nov 17, 2011 at 6:10 AM, Tomasz Tyrakowski < >> [email protected]> wrote: >> >>> My point exactly. That's why I adviced checking for duplicates _after_ >>> removing indices and primary keys on problematic tables. I've never >>> tried the method proposed by Svein, but if it really omits indices, it's >>> better than mine (more subtle and doesn't mess with metadata). >>> >>> regards >>> Tomasz >>> >>> On 2011-11-17 08:10, Svein Erling Tysvær wrote: >>> >> Hi Tomasz. I already checked the source database for duplicated >>> entries, >>> >> and found none, but i did not tried to verify the database with gfix. >>> > >>> > Exactly how did you check it for duplicate entries? The point is that >>> if there's a problem with an index or key, then doing >>> > >>> > SELECT MyIndexedField, count(*) >>> > FROM MyTable >>> > GROUP BY 1 >>> > HAVING count(*)> 1 >>> > >>> > will use the index/key and not discover the duplicate entry, whereas >>> > >>> > SELECT MyIndexedField+0, count(*) >>> > FROM MyTable >>> > GROUP BY 1 >>> > HAVING count(*)> 1 >>> > >>> > cannot use the index and will find such duplicates. >>> > >>> > HTH, >>> > Set >>> > >>> >>> >>> -- >>> __--==============================--__ >>> __--== Tomasz Tyrakowski ==--__ >>> __--== SOL-SYSTEM ==--__ >>> __--== http://www.sol-system.pl ==--__ >>> __--==============================--__ >>> >>> >>> ------------------------------------ >>> >>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >>> >>> 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]
