1181730 - Attempt to watch a collection results in internal server error

Status: VERIFIED FIXED

(addons.mozilla.org Graveyard :: Collections, defect)

Description

[Kris Maglione [:kmag]]

This is currently one of the most common sentry errors.

It seems to happen whenever I try to watch any collection (e.g., https://addons.mozilla.org/collections/mozilla/privacy/).


Sentry URL:

http://sentry.mktmon.services.phx1.mozilla.com/mkt/addonsmozillaorg/group/6018/


Aggregate Details:

* First Seen: April 19, 2014
* Last Seen: just now
* Number of tracebacks: undefined


Traceback:

Stacktrace (most recent call last):

  File "django/core/handlers/base.py", line 112, in get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "amo/decorators.py", line 32, in wrapper
    return func(request, *args, **kw)
  File "amo/decorators.py", line 51, in wrapper
    return f(request, *args, **kw)
  File "amo/decorators.py", line 128, in wrapper
    response = func(*args, **kw)
  File "bandwagon/views.py", line 586, in watch
    CollectionWatcher.objects.create(**d)
  File "django/db/models/manager.py", line 157, in create
    return self.get_queryset().create(**kwargs)
  File "django/db/models/query.py", line 322, in create
    obj.save(force_insert=True, using=self.db)
  File "django/db/models/base.py", line 545, in save
    force_update=force_update, update_fields=update_fields)
  File "django/db/models/base.py", line 573, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "django/db/models/base.py", line 654, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "django/db/models/base.py", line 687, in _do_insert
    using=using, raw=raw)
  File "django/db/models/manager.py", line 232, in _insert
    return insert_query(self.model, objs, fields, **kwargs)
  File "django/db/models/query.py", line 1514, in insert_query
    return query.get_compiler(using=using).execute_sql(return_id)
  File "django/db/models/sql/compiler.py", line 903, in execute_sql
    cursor.execute(sql, params)
  File "django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "django/db/utils.py", line 99, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "django/db/backends/mysql/base.py", line 124, in execute
    return self.cursor.execute(query, args)
  File "MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue

Comment 1

[Kris Maglione [:kmag]]

This works fine on my local instance, so I suspect it may have something to do with how the `amo_user` RequestUser object maps to a user ID on prod.

Comment 2

[Kris Maglione [:kmag]]

Actually, works fine on dev and stage too. Only fails on prod.

Comment 3

[Mathieu Agopian [:magopian]]

I'm not sure, but it could be because there's an extra constraint on the table:

| collection_subscriptions | CREATE TABLE `collection_subscriptions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `collection_id` int(11) unsigned NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `collection_id` (`collection_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `user_created_idx` (`user_id`,`created`),
  CONSTRAINT `collections_subscriptions_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `collections_subscriptions_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`),
  CONSTRAINT `collection_subscriptions_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=349501 DEFAULT CHARSET=utf8 |

As you can see, there's two constraints on the user_id, and I'm pretty sure we should only have one.

Jason, do you think you could drop one, so we can validate that it fixes the issue?

Comment 4

[Jason Thomas [:jason]]

Done. I made a backup of the table before proceeding.

mysql> ALTER TABLE `collection_subscriptions` DROP FOREIGN KEY `collection_subscriptions_ibfk_2`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table collection_subscriptions;
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| collection_subscriptions | CREATE TABLE `collection_subscriptions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `collection_id` int(11) unsigned NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `collection_id` (`collection_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `user_created_idx` (`user_id`,`created`),
  CONSTRAINT `collections_subscriptions_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `collections_subscriptions_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=353224 DEFAULT CHARSET=utf8 |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Comment 5

[Jason Thomas [:jason]]

I've ran an optimize on this table (which is a recreate for Innodb engine), which caused the original user_id foreign key constraint to disappear. I've re-added the constraint and I am no longer hitting the traceback. I am still investigating why this may have happened to begin with but not seeing much of anything in mysql logs.

Comment 6

[Kris Maglione [:kmag]]

It looks like this was added by migration 792. It dropped and re-added `collections_subscriptions_ibfk_2`, but the other constraint was called `collection_subscriptions_ibfk_2`, so it stuck around.

Thanks for looking into this!

Comment 7

[Mathieu Agopian [:magopian]]

Yeah, no clue where this collection_subscriptions_ibfk_2 was coming from (only on prod?!)