{"action":"closed","issue":{"url":"https://api.github.com/repos/oar-team/python-oar-utils/issues/1","labels_url":"https://api.github.com/repos/oar-team/python-oar-utils/issues/1/labels{/name}","comments_url":"https://api.github.com/repos/oar-team/python-oar-utils/issues/1/comments","events_url":"https://api.github.com/repos/oar-team/python-oar-utils/issues/1/events","html_url":"https://github.com/oar-team/python-oar-utils/issues/1","id":73647138,"number":1,"title":"Database integrity issues","user":{"login":"pmorillon","id":386517,"avatar_url":"https://avatars.githubusercontent.com/u/386517?v=3","gravatar_id":"","url":"https://api.github.com/users/pmorillon","html_url":"https://github.com/pmorillon","followers_url":"https://api.github.com/users/pmorillon/followers","following_url":"https://api.github.com/users/pmorillon/following{/other_user}","gists_url":"https://api.github.com/users/pmorillon/gists{/gist_id}","starred_url":"https://api.github.com/users/pmorillon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pmorillon/subscriptions","organizations_url":"https://api.github.com/users/pmorillon/orgs","repos_url":"https://api.github.com/users/pmorillon/repos","events_url":"https://api.github.com/users/pmorillon/events{/privacy}","received_events_url":"https://api.github.com/users/pmorillon/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":{"login":"SalemHarrache","id":463226,"avatar_url":"https://avatars.githubusercontent.com/u/463226?v=3","gravatar_id":"","url":"https://api.github.com/users/SalemHarrache","html_url":"https://github.com/SalemHarrache","followers_url":"https://api.github.com/users/SalemHarrache/followers","following_url":"https://api.github.com/users/SalemHarrache/following{/other_user}","gists_url":"https://api.github.com/users/SalemHarrache/gists{/gist_id}","starred_url":"https://api.github.com/users/SalemHarrache/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SalemHarrache/subscriptions","organizations_url":"https://api.github.com/users/SalemHarrache/orgs","repos_url":"https://api.github.com/users/SalemHarrache/repos","events_url":"https://api.github.com/users/SalemHarrache/events{/privacy}","received_events_url":"https://api.github.com/users/SalemHarrache/received_events","type":"User","site_admin":false},"milestone":null,"comments":3,"created_at":"2015-05-06T14:54:37Z","updated_at":"2015-06-05T10:06:47Z","closed_at":"2015-06-05T10:06:47Z","body":"With our OAR database, there is integrity issue on a lot of tables, and when i re-launched the script, a lot of rows are ignored : \r\n\r\n```\r\n# oar-database-archive sync --archive-db-url postgresql://oar:oar@dharana:5432/oar_archive\r\nPlease enter the url for your database.\r\n\r\nFor example:\r\nPostgreSQL: postgresql://scott:tiger@localhost/foo\r\nMySQL: mysql://scott:tiger@localhost/bar\r\n\r\nOAR database URL [postgresql://oar:****@localhost:5432/oar2]: \r\nContinue to copy old resources and jobs to the archive database? [Y/n]: Y\r\n create ~> table accounting\r\n create ~> table admission_rules\r\n create ~> table assigned_resources\r\n create ~> table challenges\r\n create ~> table event_log_hostnames\r\n create ~> table event_logs\r\n create ~> table files\r\n create ~> table frag_jobs\r\n create ~> table gantt_jobs_predictions\r\n create ~> table gantt_jobs_predictions_log\r\n create ~> table gantt_jobs_predictions_visu\r\n create ~> table gantt_jobs_resources\r\n create ~> table gantt_jobs_resources_log\r\n create ~> table gantt_jobs_resources_visu\r\n create ~> table job_dependencies\r\n create ~> table job_resource_descriptions\r\n create ~> table job_resource_groups\r\n create ~> table job_state_logs\r\n create ~> table job_types\r\n create ~> table jobs\r\n         (psycopg2.ProgrammingError) relation \"state_id\" already exists\r\n          [SQL: 'CREATE INDEX state_id ON jobs (state, job_id)']\r\n create ~> table moldable_job_descriptions\r\n create ~> table queues\r\n create ~> table resource_logs\r\n create ~> table resources\r\n create ~> table scheduler\r\n create ~> table schema\r\n   copy ~> table admission_rules (24/24)\r\n   copy ~> table assigned_resources (540000/14433684)\r\nError: (psycopg2.IntegrityError) duplicate key value violates unique constraint \"assigned_resources_pkey\"\r\nDETAIL:  Key (moldable_job_id, resource_id)=(4135814, 1601) already exists.\r\n [SQL: 'INSERT INTO assigned_resources (moldable_job_id, resource_id, assigned_resource_index) VALUES (%(moldable_job_id)s, %(resource_id)s, %(assigned_resource_index)s)'] [parameters: ({'assigned_resource_index': u'LOG', 'moldable_job_id': 4570807, 'resource_id': 1084}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4570807, 'resource_id': 1086}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4570803, 'resource_id': 723}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4570811, 'resource_id': 496}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4570811, 'resource_id': 737}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4570811, 'resource_id': 193}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4570806, 'resource_id': 452}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4570806, 'resource_id': 1529}  ... displaying 10 of 10000 total bound parameter sets ...  {'assigned_resource_index': u'LOG', 'moldable_job_id': 4550839, 'resource_id': 1585}, {'assigned_resource_index': u'LOG', 'moldable_job_id': 4550839, 'resource_id': 1586})]\r\n# oar-database-archive sync --archive-db-url postgresql://oar:oar@dharana:5432/oar_archive\r\nPlease enter the url for your database.\r\n\r\nFor example:\r\nPostgreSQL: postgresql://scott:tiger@localhost/foo\r\nMySQL: mysql://scott:tiger@localhost/bar\r\n\r\nOAR database URL [postgresql://oar:****@localhost:5432/oar2]: \r\nContinue to copy old resources and jobs to the archive database? [Y/n]: Y\r\n   copy ~> table assigned_resources (74962/74962)\r\n   copy ~> table challenges (120000/718022)\r\nError: (psycopg2.IntegrityError) duplicate key value violates unique constraint \"challenges_pkey\"\r\nDETAIL:  Key (job_id)=(7042260) already exists.\r\n```\r\n\r\n```\r\noar2=# select * FROM assigned_resources WHERE (moldable_job_id, resource_id)=(4135814, 1601);\r\n moldable_job_id | resource_id | assigned_resource_index \r\n-----------------+-------------+-------------------------\r\n         4135814 |        1601 | LOG\r\n(1 row)\r\n```\r\n"}}