Hi Niels, There are 3 views that have multiple definition in the data dictionary. For example: select * from sys.tables where name='stav_skladu_historie' returns 11 rows like this: id name schema_id query type system commit_action readonly temporary 16884 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 17461 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 17691 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 17847 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 17951 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 18020 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07 union all select * from stav_skladu_historie_2014_08; 1 false 0 false 0 18115 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 18219 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 18373 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07; 1 false 0 false 0 18442 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07 union all select * from stav_skladu_historie_2014_08; 1 false 0 false 0 18537 stav_skladu_historie 5899 create view stav_skladu_historie as select * from stav_skladu_historie_2011 union all select * from stav_skladu_historie_2012 union all select * from stav_skladu_historie_2013_01 union all select * from stav_skladu_historie_2013_02 union all select * from stav_skladu_historie_2013_03 union all select * from stav_skladu_historie_2013_04 union all select * from stav_skladu_historie_2013_05 union all select * from stav_skladu_historie_2013_06 union all select * from stav_skladu_historie_2013_07 union all select * from stav_skladu_historie_2013_08 union all select * from stav_skladu_historie_2013_09 union all select * from stav_skladu_historie_2013_10 union all select * from stav_skladu_historie_2013_11 union all select * from stav_skladu_historie_2013_12 union all select * from stav_skladu_historie_2014_01 union all select * from stav_skladu_historie_2014_02 union all select * from stav_skladu_historie_2014_03 union all select * from stav_skladu_historie_2014_04 union all select * from stav_skladu_historie_2014_05 union all select * from stav_skladu_historie_2014_06 union all select * from stav_skladu_historie_2014_07 union all select * from stav_skladu_historie_2014_08; 1 false 0 false 0 Thank you, Radovan On 09/08/2014 08:46 AM, Niels Nes wrote:
On Mon, Sep 08, 2014 at 08:30:59AM +0200, Radovan Bičiště wrote:
Hello Niels, Thank for the tip. But the object drmaxtst.stav_skladu_historie is actually a view. There is no unique constraint on underlaying tables. Is there a way how to modify the data dictionary directly? Then the message must indicate the table name (view or not) isn't unique. Somehow old table definitions were left behind. This can be seen using select * from tables. If so we can remove them (as monetdb user). But first we need to find out which tables (and id's) weren't properly dropped.
Niels
Thank you, Radovan
On 09/07/2014 03:49 PM, Niels Nes wrote:
On Sun, Sep 07, 2014 at 01:39:19PM +0200, Radovan Bičiště wrote:
Hello, I tried to dump the database and received following error: table drmaxtst.stav_skladu_historie is not unique, corrupt catalog ? By dropping the unique constraint, you may be able to dump the db.
Niels
Any tips how to dump the database will be highly appreciated. Thank you, Radovan
On 09/05/2014 02:49 PM, Radovan Bičiště wrote:
Hello, I'm wondering if there is some hard core way to drop a view. Somehow I have three views that I'm not able to drop. I can issue drop statement several times and it returns success. Here is log from mclient: ceos@lux-drmax-ana:/u01/ceosdata/database/monetdb/drmax/crm$ mclient -d zkdev1 -u drmaxtst password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP2) Database: MonetDB v11.17.17 (Jan2014-SP2), 'mapi:monetdb://lux-drmax-ana:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql>drop view drmaxtst.prodeje_v; operation successful (3.168ms) sql>drop view drmaxtst.prodeje_v; operation successful (3.004ms) sql>drop view drmaxtst.prodeje_v; operation successful (7.501ms) sql>drop view drmaxtst.prodeje_v; operation successful (4.154ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.351ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.410ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.355ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.685ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.646ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.695ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.390ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.399ms) sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql>
I guess my only option is to dump a reload the database. :(
Radovan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list