New constraint on ir_model_data causes errors

[Freerk Kalsbeek (Mindswitch BV)]

The new constraint in base: unique (name,module) on ir_model_data causes the following error when performing an update on an allready existing database:
[2009-06-30 15:10:07,681] INFO:init:module base: creating or updating database tables
[2009-06-30 15:10:08,464] DEBUG:sql:bad query: alter table "ir_model_data" add constraint "ir_model_data_module_name_uniq" unique(name, module)
[2009-06-30 15:10:08,465] DEBUG:sql:[01]: index "ir_model_data_module_name_uniq" kon niet worden aangemaakt
[2009-06-30 15:10:08,465] DEBUG:sql:[02]: DETAIL: De tabel bevat gedupliceerde waarden.
[2009-06-30 15:10:08,465] WARNING:orm:[01]: unable to add 'unique(name, module)' constraint on table ir_model_data !
[2009-06-30 15:10:08,465] WARNING:orm:[02]: If you want to have it, you should update the records and execute manually:
[2009-06-30 15:10:08,465] WARNING:orm:[03]: ALTER table ir_model_data ADD CONSTRAINT ir_model_data_module_name_uniq unique(name, module)

I have the following (name,module) combinations that are not unique! Checked a few databases and it always applies...

------------------------------------------------------------------+-------------------------------+-----
 field_ir_model_name | base | 2
 field_ir_model_state | base | 2
 field_ir_actions_type | base | 2
 field_ir_model_model | base | 2
 field_ir_actions_name | base | 2
 field_ir_model_field_id | base | 2
 field_ir_actions_usage | base | 2
 field_ir_model_info | base | 2
 field_ir_model_access_ids | base | 2

Question 1: Is the new constraint a valid one?
Question 2: If yes, how to fix the error condition?

[Freerk Kalsbeek (Mindswitch BV) (f-kalsbeek)]

When creating a new database, I see the same logs:

[2009-06-30 15:31:02,457] DEBUG:orm:creating new column translate of table ir_model_fields
[2009-06-30 15:31:02,720] DEBUG:sql:bad query: alter table "ir_model_data" add constraint "ir_model_data_module_name_uniq" unique(name, module)
[2009-06-30 15:31:02,720] DEBUG:sql:[01]: index "ir_model_data_module_name_uniq" kon niet worden aangemaakt
[2009-06-30 15:31:02,721] DEBUG:sql:[02]: DETAIL: De tabel bevat gedupliceerde waarden.
[2009-06-30 15:31:02,721] WARNING:orm:[01]: unable to add 'unique(name, module)' constraint on table ir_model_data !
[2009-06-30 15:31:02,721] WARNING:orm:[02]: If you want to have it, you should update the records and execute manually:
[2009-06-30 15:31:02,721] WARNING:orm:[03]: ALTER table ir_model_data ADD CONSTRAINT ir_model_data_module_name_uniq unique(name, module)

[Jay Vora (Serpent Consulting Services) (jayvora)]

Hello Freerk and Marcio,

Thanks a lot for reporting this critical issue.

Fixed by revision 1826 <email address hidden>.

Kindly check this query before and after applying the patch.

'select name,module,count(*) from ir_model_data group by name,module having count(1)>1'.

Thank you again.

[Márcio Santos (marcio.santos)]

Still seeing the same error.

Before :

select name,module,count(*) from ir_model_data group by name,module having count(1)>1;
           name | module | count
---------------------------+--------+-------
 field_ir_actions_type | base | 2
 field_ir_model_access_ids | base | 2
 field_ir_model_field_id | base | 2
 field_ir_actions_name | base | 2
 field_ir_model_state | base | 2
 field_ir_model_info | base | 2
 field_ir_actions_usage | base | 2
 field_ir_model_name | base | 2
 field_ir_model_model | base | 2
(9 rows)

After :

select name,module,count(*) from ir_model_data group by name,module having count(1)>1;
           name | module | count
---------------------------+--------+-------
 field_ir_actions_type | base | 2
 field_ir_model_access_ids | base | 2
 field_ir_model_field_id | base | 2
 field_ir_actions_name | base | 2
 field_ir_model_state | base | 2
 field_ir_model_info | base | 2
 field_ir_actions_usage | base | 2
 field_ir_model_name | base | 2
 field_ir_model_model | base | 2
(9 rows)

Log file from running : /opt/openerp-server/bin/openerp-server.py -dtesting --update=all --stop-after-init --log-level=debug --addons-path=/opt/openerp-addons

[2009-08-03 12:25:52,680] DEBUG:sql:bad query: alter table "ir_model_data" add constraint "ir_model_data_module_name_uniq" unique(name, module)
[2009-08-03 12:25:52,680] DEBUG:sql:[01]: could not create unique index "ir_model_data_module_name_uniq"
[2009-08-03 12:25:52,680] DEBUG:sql:[02]: DETAIL: Table contains duplicated values.
[2009-08-03 12:25:52,680] WARNING:orm:[01]: unable to add 'unique(name, module)' constraint on table ir_model_data !
[2009-08-03 12:25:52,680] WARNING:orm:[02]: If you want to have it, you should update the records and execute manually:
[2009-08-03 12:25:52,680] WARNING:orm:[03]: ALTER table ir_model_data ADD CONSTRAINT ir_model_data_module_name_uniq unique(name, module)

Running:

Server Stable Branch Revision 1827
Addons Stable Branch Revision 2405

Ubuntu 8.04 LTS

[Anup(SerpentCS) (anup-serpent)]

Hello Marcio

   I am using the same revisions of stable server and addons and i have checked it and i am not getting the error. The solution has been done as no duplicate names can be stored in the table.
   IMHO i would like to request you to restart the server create a new db and then check.

   Here is what my database gives with the same query

# select name,module,count(*) from ir_model_data group by name,module having count(1)>1;
 name | module | count
------+--------+-------
(0 rows)

Thank you

[Márcio Santos (marcio.santos)]

Restarting server and creating a new database results in no duplicate names.

bug=# select name,module,count(*) from ir_model_data group by name,module having count(1)>1;
 name | module | count
------+--------+-------
(0 rows)

bug=#

Is there a way to repair older databases or can the database remain has it is?

Will problem arise from the duplicate records?

[Jay Vora (Serpent Consulting Services) (jayvora)]

Hello Márcio Santos,

Thank you for confirming the solution.

These duplicate records were generated when 'base' was installed. You may try -u base -d dbname.
Either way, you may change the name of the duplicate records to get rid of the problem(from postgresql backend).

Thank you.

[Nicolas DS (zyphos)]

To resolve the duplicate record problem:

According to the patch 'ir_model_data_unique_constraint_error.diff'
you should edit the second duplicate record,
and add '_<id>' to 'name' field, with <id> = thisRow. 'res_id' field

ie in my case for "field_ir_actions_type":
id | module | res_id | name
238 | base | 90 | field_ir_actions_type
337 | base | 180 | field_ir_actions_type

You must modify the second one (id=337)
should become:
id | module | res_id | name
238 | base | 90 | field_ir_actions_type
337 | base | 180 | field_ir_actions_type_180

You could use this query to make it automatic:

update ir_model_data set name=name||'_'||res_id where id in (select max(id) from ir_model_data group by name,module having count(1)>1)

After that, the constraint could be apply.

ALTER table ir_model_data ADD CONSTRAINT ir_model_data_module_name_uniq unique(name, module)