openedx/ecommerce-job: django.db.utils.IntegrityError: (1062, "Duplicate entry '1' for key 'default_site_id'")

[lpm0073]

The ecommerce job does not take upgrades into consideration. In my case the ecommerce database comes from a native installation of Lilac and contains considerable transaction data that i definitely do not want to have to attempt to migrate table-by-table given the complexity of the oscar db.

openedx/ecommerce-job raises the following error:

Traceback (most recent call last):     
   File "./manage.py", line 11, in <module>       
     execute_from_command_line(sys.argv)
   File "/openedx/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line    
     utility.execute()        
   File "/openedx/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute  
     self.fetch_command(subcommand).run_from_argv(self.argv)
   File "/openedx/venv/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
     self.execute(*args, **cmd_options) 
   File "/openedx/venv/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute      
     output = self.handle(*args, **options)       
   File "/openedx/ecommerce/ecommerce/core/management/commands/create_or_update_site.py", line 206, in handle 
     partner.save() 
   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 739, in save     
     self.save_base(using=using, force_insert=force_insert, 
   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 776, in save_base
     updated = self._save_table(        
   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 858, in _save_table        
     updated = self._do_update(base_qs, using, pk_val, values, update_fields,   
   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 912, in _do_update         
     return filtered._update(values) > 0
   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 802, in _update 
     return query.get_compiler(self.db).execute_sql(CURSOR) 
   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1559, in execute_sql         
     cursor = super().execute_sql(result_type)    
   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql         
     cursor.execute(sql, params)        
   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)  
   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers     
     return executor(sql, params, many, context)  
   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute         
     return self.cursor.execute(sql, params)      
   File "/openedx/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__        
     raise dj_exc_value.with_traceback(traceback) from exc_value      
   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute         
     return self.cursor.execute(sql, params)      
   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute     
     return self.cursor.execute(query, args)      
   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 209, in execute        
     res = self._query(query) 
   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 315, in _query         
     db.query(q)         
     db.query(q)    
   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 239, in query      
     _mysql.connection.query(self, query)         
 django.db.utils.IntegrityError: (1062, "Duplicate entry '1' for key 'default_site_id'")

[lpm0073]

this is addressed in #27, which removes the site-id input param from ./manage.py create_or_update_site

[regisb]

Hi Lawrence, sorry about the delayed answer.

I think that removing the --site-id is not an adequate solution to our problem here. The problem that we are facing is that when we pass --site-id=... we are attempting to create multiple "Partner" objects for the same default_site_id:

   File "/openedx/ecommerce/ecommerce/core/management/commands/create_or_update_site.py", line 206, in handle 
     partner.save() 

This is what's causing the ecommerce job to fail.

Sure, removing the --site-id argument will resolve the error, but then we'll face another problem. Let's assume that the platform administrator changes the Ecommerce site domain: this happens very often, for instance when they switch between production/staging/local url. Then a different Site (and partner) will be created, because of the following line in create_or_update_site:

site, site_created = Site.objects.get_or_create(domain=site_domain)

Then, all existing ecommerce objects will no longer be associated to that site. This situation is a real pain to resolve for platform administrators.

I understand that you are facing this issue because you have existing data. To resolve this situation, may I suggest that you modify your existing partner codes ? for site ID 1 it should be "dev" and for site ID 2 "openedx".

[regisb]

Closing this for now. Feel free to re-open if the issue is still affecting you.

[lpm0073]

This is not resolved. Anyone migrating an ecommerce database from pre-tutor will be affected by this problem. i followed your suggestion above for my most recent project. that worked, thanks. however ....

this problem exists because the plugin forcefully and explicitly adds a dev site as site_id = 1, which, as one might reasonably assume, is probably going to be the existing site_id for any legacy db being migrated. your suggestion worked, but it's cumbersome and aggressive. i'd very much like to not have to repeat it for future migrations.

maybe a more graceful solution would be to make the dev site an option that's defaulted to true? i'm happy to do that work, and submit it as a PR, provided however that you're already on board with the strategy, conceptually.

[regisb]

I agree that hard-coding the site ID is inappropriate; unfortunately I don't have time right now to have a more in-depth look at this issue. I'll re-open this and will take some time to figure out whether removing the --site-id option is the right solution for everyone.

[regisb]

FTR, this same error was also reported here: https://discuss.openedx.org/t/ecommerce-plugin-error-duplicate-entry-1-for-key-default-site-id/9463