search

etianen / django-watson

Full-text multi-table search application for Django. Easy to install and use, with good performance.
BSD 3-Clause "New" or "Revised" License
1.2k stars 130 forks source link

Statement violates GTID consistency #172

Closed neerdoc closed 6 years ago

neerdoc commented 8 years ago

Hi,

I've been using watson for a couple of years without problem, but since I moved one of my sites to a new server I get the following error whenever I try to add/update/delete information via django-admin.

(1785, 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.')

If I comment out the "watson.register" lines in my apps.py, I do not get the error.

I'm using MySQL as backend.

The traceback shows this: Django Version: 1.8.13 Python Version: 2.7.12 Traceback: File "/usr/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response

  1. response = wrapped_callback(request, _callback_args, *_callback_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in wrapper
  2. return self.admin_site.admin_view(view)(_args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
  3. response = view_func(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  4. response = view_func(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/sites.py" in inner
  5. return view(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in change_view
  6. return self.changeform_view(request, object_id, form_url, extra_context) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapper
  7. return bound_func(_args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
  8. response = view_func(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in bound_func
  9. return func.get(self, type(self))(_args2, *_kwargs2) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in inner
  10. return func(_args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in changeform_view
  11. self.save_related(request, form, formsets, not add) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in save_related
  12. self.save_formset(request, form, formset, change=change) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in save_formset
  13. formset.save() File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save
  14. return self.save_existing_objects(commit) + self.save_new_objects(commit) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save_existing_objects
  15. saved_instances.append(self.save_existing(form, obj, commit=commit)) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save_existing
  16. return form.save(commit=commit) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save
  17. construct=False) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save_instance
  18. instance.save() File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py" in save
  19. force_update=force_update, update_fields=update_fields) File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py" in save_base
  20. update_fields=update_fields, raw=raw, using=using) File "/usr/local/lib/python2.7/site-packages/django/dispatch/dispatcher.py" in send
  21. response = receiver(signal=self, sender=sender, **named) File "/usr/local/lib/python2.7/site-packages/watson/search.py" in _post_save_receiver
  22. self.update_obj_index(instance) File "/usr/local/lib/python2.7/site-packages/watson/search.py" in update_obj_index
  23. _bulk_save_search_entries(list(self._update_obj_index_iter(obj))) File "/usr/local/lib/python2.7/site-packages/watson/search.py" in _update_obj_index_iter
  24. update_count = search_entries.update(**search_entry_data) File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py" in update
  25. rows = query.get_compiler(self.db).execute_sql(CURSOR) File "/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  26. cursor = super(SQLUpdateCompiler, self).execute_sql(result_type) File "/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  27. cursor.execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  28. return super(CursorDebugWrapper, self).execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  29. return self.cursor.execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/utils.py" in exit
  30. six.reraise(dj_exc_type, dj_exc_value, traceback) File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  31. return self.cursor.execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
  32. return self.cursor.execute(query, args) File "/usr/local/lib/python2.7/site-packages/MySQLdb/cursors.py" in execute
  33. self.errorhandler(self, exc, value) File "/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  34. raise errorclass, errorvalue

Any help appreciated.

etianen commented 8 years ago

You need to set sysvar_enforce_gtid_consistency to false in your settings file.

On Thu, 28 Jul 2016 at 17:49 neerdragon notifications@github.com wrote:

Hi,

I've been using watson for a couple of years without problem, but since I moved one of my sites to a new server I get the following error whenever I try to add/update/delete information via django-admin.

(1785, 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.')

If I comment out the "watson.register" lines in my apps.py, I do not get the error.

I'm using MySQL as backend.

The traceback shows this: Django Version: 1.8.13 Python Version: 2.7.12 Traceback: File "/usr/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response

  1. response = wrapped_callback(request, _callback_args, *_callback_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in wrapper
  2. return self.admin_site.admin_view(view)(_args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
  3. response = view_func(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  4. response = view_func(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/sites.py" in inner
  5. return view(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in change_view
  6. return self.changeform_view(request, object_id, form_url, extra_context) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapper
  7. return bound_func(_args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
  8. response = view_func(request, _args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in bound_func
  9. return func.get(self, type(self))(_args2, *_kwargs2) File "/usr/local/lib/python2.7/site-packages/django/utils/decorators.py" in inner
  10. return func(_args, *_kwargs) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in changeform_view
  11. self.save_related(request, form, formsets, not add) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in save_related
  12. self.save_formset(request, form, formset, change=change) File "/usr/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in save_formset
  13. formset.save() File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save
  14. return self.save_existing_objects(commit) + self.save_new_objects(commit) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save_existing_objects
  15. saved_instances.append(self.save_existing(form, obj, commit=commit)) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save_existing
  16. return form.save(commit=commit) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save
  17. construct=False) File "/usr/local/lib/python2.7/site-packages/django/forms/models.py" in save_instance
  18. instance.save() File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py" in save
  19. force_update=force_update, update_fields=update_fields) File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py" in save_base
  20. update_fields=update_fields, raw=raw, using=using) File "/usr/local/lib/python2.7/site-packages/django/dispatch/dispatcher.py" in send
  21. response = receiver(signal=self, sender=sender, **named) File "/usr/local/lib/python2.7/site-packages/watson/search.py" in _post_save_receiver
  22. self.update_obj_index(instance) File "/usr/local/lib/python2.7/site-packages/watson/search.py" in update_obj_index
  23. _bulk_save_search_entries(list(self._update_obj_index_iter(obj))) File "/usr/local/lib/python2.7/site-packages/watson/search.py" in _update_obj_index_iter
  24. update_count = search_entries.update(**search_entry_data) File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py" in update
  25. rows = query.get_compiler(self.db).execute_sql(CURSOR) File "/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  26. cursor = super(SQLUpdateCompiler, self).execute_sql(result_type) File "/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  27. cursor.execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  28. return super(CursorDebugWrapper, self).execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  29. return self.cursor.execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/utils.py" in exit
  30. six.reraise(dj_exc_type, dj_exc_value, traceback) File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  31. return self.cursor.execute(sql, params) File "/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
  32. return self.cursor.execute(query, args) File "/usr/local/lib/python2.7/site-packages/MySQLdb/cursors.py" in execute
  33. self.errorhandler(self, exc, value) File "/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  34. raise errorclass, errorvalue

Any help appreciated.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/etianen/django-watson/issues/172, or mute the thread https://github.com/notifications/unsubscribe-auth/AAJFCBBeEcl3Pn6psqI9TORR9vwFvpDrks5qaN2IgaJpZM4JXaoe .

neerdoc commented 8 years ago

Well, that's not really an option since MySQL will not start in that case unless I also turn GTID off completely.

Although I'm currently only using a single database, replication to slave databases are planned if/when the site grows, so GTID is definitely something that I want to have enabled.

If possible, I would rather have watson fixed so that the SQL statements are transactionally safe.

etianen commented 8 years ago

MySQL only offers full text search on InnoDb in 5.6 and above, and it's implementation is a bit weird:

https://www.percona.com/blog/2013/02/26/myisam-vs-innodb-full-text-search-in-mysql-5-6-part-1/

Which means that, realistically, MyIssam is still a good choice for full text search tables. Except that it generates these errors if you have GTID enforcing on.

I'd like to support InnoDB for full text search, but last time I tried the implementation proved pretty unworkable:

https://github.com/etianen/django-watson/issues/120 On Fri, 29 Jul 2016 at 19:49, neerdragon notifications@github.com wrote:

Well, that's not really an option since MySQL will not start in that case unless I also turn GTID off completely.

Although I'm currently only using a single database, replication to slave databases are planned if/when the site grows, so GTID is definitely something that I want to have enabled.

If possible, I would rather have watson fixed so that the SQL statements are transactionally safe.

— You are receiving this because you commented.

Reply to this email directly, view it on GitHub https://github.com/etianen/django-watson/issues/172#issuecomment-236263099, or mute the thread https://github.com/notifications/unsubscribe-auth/AAJFCEnwoRmc5-w_mrV_yqr8D3LY7ro-ks5qaks2gaJpZM4JXaoe .

madchap commented 4 years ago

Which means that, realistically, MyIssam is still a good choice for full text search tables.

Possibly. Yet, its support from major vendors is vanishing, such as on GCP's CloudSQL.