SOLVED problem after restore: there is no primary key for referenced table "res_users"

[Nicola Riolini - Micronaet]

Hi, my installation: Ubu 10.4, pg 8.4 openerp 6.0.3 updated from launchpad.
I usually use auto_backup but I try also with save DB in GTK menu (for daily backup).
I try a restore (with u all after restore) on other server (ubu 10.4, pg 8.4, openerp 6.0.3 updated with same module in addons folder) I'm able to log in and use it but if I try all new module installation this is my error:

ProgrammingError: there is no primary key for referenced table "res_users"

I try also reindex from psql original DB, "backup-restore-updateall" but the same result, fortunally original DB works fine, so I can install new module back up and restore.
This problem occurs 4 times for me, till now I correct restoring an old DB or delete if is a testing db, but I open this bug because I wont' figure if I can restore DB in production environment...
Thanks, if other information are request, please ask, I do much test operations :)

all server log:
Environment Information :
System : Linux-2.6.38-12-generic-x86_64-with-Ubuntu-11.04-natty
OS Name : posix
Distributor ID: Ubuntu
Description: Ubuntu 11.04
Release: 11.04
Codename: natty
Operating System Release : 2.6.38-12-generic
Operating System Version : #51-Ubuntu SMP Wed Sep 28 14:27:32 UTC 2011
Operating System Architecture : 64bit
Operating System Locale : it_IT.UTF8
Python Version : 2.7.1+
OpenERP-Client Version : 6.0.3
Last revision No. & ID :Bazaar Package not Found !Traceback (most recent call last):
  File "/home/administrator/Launchpad/openerp/server/bin/netsvc.py", line 489, in dispatch
    result = ExportService.getService(service_name).dispatch(method, auth, params)
  File "/home/administrator/Launchpad/openerp/server/bin/service/web_services.py", line 599, in dispatch
    res = fn(db, uid, *params)
  File "/home/administrator/Launchpad/openerp/server/bin/osv/osv.py", line 122, in wrapper
    return f(self, dbname, *args, **kwargs)
  File "/home/administrator/Launchpad/openerp/server/bin/osv/osv.py", line 176, in execute
    res = self.execute_cr(cr, uid, obj, method, *args, **kw)
  File "/home/administrator/Launchpad/openerp/server/bin/osv/osv.py", line 167, in execute_cr
    return getattr(object, method)(cr, uid, *args, **kw)
  File "/home/administrator/Launchpad/openerp/server/bin/addons/base/module/wizard/base_module_upgrade.py", line 98, in upgrade_module
    _db, pool = pooler.restart_pool(cr.dbname, update_module=True)
  File "/home/administrator/Launchpad/openerp/server/bin/pooler.py", line 60, in restart_pool
    return get_db_and_pool(db_name, force_demo, status, update_module=update_module)
  File "/home/administrator/Launchpad/openerp/server/bin/pooler.py", line 39, in get_db_and_pool
    addons.load_modules(db, force_demo, status, update_module)
  File "/home/administrator/Launchpad/openerp/server/bin/addons/__init__.py", line 883, in load_modules
    processed_modules.extend(load_module_graph(cr, graph, status, report=report, skip_modules=processed_modules))
  File "/home/administrator/Launchpad/openerp/server/bin/addons/__init__.py", line 719, in load_module_graph
    init_module_objects(cr, package.name, modules)
  File "/home/administrator/Launchpad/openerp/server/bin/addons/__init__.py", line 414, in init_module_objects
    result = obj._auto_init(cr, {'module': module_name})
  File "/home/administrator/Launchpad/openerp/server/bin/osv/orm.py", line 2430, in _auto_init
    cr.execute('ALTER TABLE "%s" ADD COLUMN "%s" %s' % (self._table, k, logs[k]))
  File "/home/administrator/Launchpad/openerp/server/bin/sql_db.py", line 78, in wrapper
    return f(self, *args, **kwargs)
  File "/home/administrator/Launchpad/openerp/server/bin/sql_db.py", line 131, in execute
    res = self._obj.execute(query, params)
ProgrammingError: there is no primary key for referenced table "res_users"

[Nicola Riolini - Micronaet (nicolariolini)]

for workaroud I try via psql in restored DB:
ALTER TABLE res_user ADD PRIMARY KEY (id);
I see that the primary key is back but this procedure I'll need to do for all the tables that I find without.
what's going wrong in my backup procedure, is better use pg_dump / pg_restore?
Thanks

[Vishal Parmar(Open ERP) (vpa-openerp)]

Hello Nicola Riolini,

I have checked your issue with trunk and stable-6 both. from gtk I have restore db and try to installed other modules and I am able to install all module successfully at my end. what is your end configuration I don't know ?.
would you please elaborate more like you end configuration or which different scenario you did applied so I can
understand what exactly problem.

Thanks and waiting for your reply.

[Nicola Riolini - Micronaet (nicolariolini)]

Thanks for reply Vishal,
I have a normal installation with l10_it module, crm and sale modules plus 5/6 customization. I dont' think you could replicate my situation but, as I got this problem in more than one installation, my hope is that the error tells you what is cause or a workaround to regenerate key, like "alter table" that I suggest... (or if the error is yet happend, in lp I see only one tread that sound like: create a new DB an migrate important data :) )

[Vishal Parmar(Open ERP) (vpa-openerp)]

Hello Nicola Riolini,

Thanks for your quick reply.

It may be happen at your end. I have tried again same scenario as you have applied but still I didn't face any problem at my end just faced the warring which is from yml testing. would you please provide related video
so I can understand which steps done by your end.

Thanks and waiting for your reply.

[Nicola Riolini - Micronaet (nicolariolini)]

Sorry for late, I realize a video (I often paused it for reduce dimension of file, cutting "dead time" like ftp or backup.
as you can see (via pgadmin3) in original server res_users table has "primary key" id, after backup, publish via dropbox, download, restore with GTK in destination server "primary key" id is loosed...
waiting for your comments, bye and thanks
Nicola

[Kelbethor (csi-kelbethor)]

Same problem for me.

trunk-addons
extra-trunk
openerp-spain/6.1

Backing database from web and restoring to web both 6.1, both trunk (Setting two indentical openerp installation on different server environments),
Server source: Centos 5.5, Python2.7, Postgres8.4
Server destination: Ubuntu 10, Python2.6, Postgres8.4

[Nicola Riolini - Micronaet (nicolariolini)]

Kelbethor,
till now I do a reindex of tables that loose key attribute, so when I see this error, for ex. when I update module, doing some operation etc. I run via psql this query (here a list of broken tables finded in my DB, for now...), HTH:

sudo su - postgres
psql broken_db

alter table res_users add primary key (id);
alter table res_partner add primary key (id);
alter table res_groups add primary key (id);
alter table ir_act_window add primary key (id);
alter table ir_model_fields add primary key (id);
alter table ir_model add primary key (id);
alter table ir_act_report_xml add primary key (id);
alter table ir_values add primary key (id);
alter table wkf add primary key (id);
alter table wkf_activity add primary key (id);
alter table ir_act_server add primary key (id);
alter table ir_attachment add primary key (id);
alter table product_product add primary key (id);

[Kelbethor (csi-kelbethor)]

I don't know if this could be dangerous, it worked for me as a workarroud...

As postgres or database owner run this:
"for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" DATABASE_NAME` ; do psql -c "alter table $tbl ADD CONSTRAINT "$tbl"_pkey PRIMARY KEY (id);" DATABASE_NAME ; done"

I repeat, I think it's really dangerous, you'd better filter more accurate tables to alter...

[Kelbethor (csi-kelbethor)]

Well, I think a server "reload and update" of database fixed the problem and now is restoring keys. (I noticed before that foreign keys were missing too) I don't know if I could reproduce it again... I'll try it.

[Nicola Riolini - Micronaet (nicolariolini)]

Kelbethor, with reload and update you mean a new restore of DB? because I try to restore and "update all" the DB restored few time but with same "leak" of primary keys...
I put a video with the separated operation of backup and retore but I hope it's a particular case but I dont' find the origin of problem...

[Vishal Parmar(Open ERP) (vpa-openerp)]

Hello,

I have restored database successfully but in pdadmin I seen <no primary key> and not reproducible each time.
@Framework Team : Would you please double check this issue and take appropriate decision for it.

Thanks.

[Antony Lesuisse (OpenERP) (al-openerp)]

We need the precise steps to reproduce this problem to consider this as a bug. The steps must look like:
- create a new database
- install this and this
- backup database
- restore database -> problem
We also need the details about your postgresql version.

[Nicola Riolini - Micronaet (nicolariolini)]

Hi Antony, thank for reply, as I said in my first post:
my installation: Ubu 10.4, pg 8.4 openerp 6.0.3 updated from launchpad.
and also, there's not a bug procedure to follow, in 2 different installation I have this problems, the only reproducing steps is the video I attach in post #5, simply backup via GTK - dropbox the file - restore via GTK on a twin server installed in my office and "update all" the DB...
If you want I can send you backup SQL file, the installations has some customization but I dont' think error comes from this (I can also compress addons if you want)

[Juan Márquez (jmarquez)]

I Found this bug after publish my own: https://bugs.launchpad.net/openobject-server/+bug/1197515

This bug is more than one year old, but "still alive".

For Nicola and Kelbethor, I lost most primary key and lost foreign keys too, If you can please check this issue in your's databases.

[Nicola Riolini - Micronaet (nicolariolini)]

Got a better solution, thanks to enlightx:
I have always this problem if I restore my database and, when openerp seems finish, I stop console server and restart service (or ac onsole without update all).
The problem is that postgres is still runnig (test with: top). Solution: let postgres running and when in top finish using CPU resource, stop the console server.

No primary key was deleted during the making of this database! :)