REST API authorization errors with POST requests only

Question

I've set up a REST API client (using Ruby) and Magento 1.9.1 back in 2015 and it worked flawlessly.

But a few weeks ago I noticed that every once in a while a got an exception:

Integrity constraint violation: 1062 Duplicate entry ... for key 'UNQ_OAUTH_NONCE_NONCE', the query was INSERT INTO oauth_nonce (nonce, timestamp) VALUES (?, ?)

and the request returns a 401 status.

After lots of trial and error I noticed that every time I send a POST request to the API (i.e create product, assign website, upload picture, ...) I receive that error. GET and PUT requests work without this error.

But surprisingly the requested operation is always performed successfully!

In other words, this happens:

1. I send the POST request.
2. Magento validates the Oauth parameters and stores the nonce.
3. Magento performes the requested operation.
4. Magento once again tries to check the Oauth parameters. This fails, because the nonce is already stored in the oauth_nonce table.
5. A get a 401 error, even if the original operation was successful.

My question: What triggers this behaviour and how can I stop it?

Additional information:

I already tried to remove the unique index in oauch_nonce. This effectively prevents the SQL error, but I get a "signature_invalid" error instead. Again, the requested operation is performed nevertheless.

The exception log just duplicates the error message already returned by the request.

UPDATE: I duplicated the Magento shop on a different machine, by simply copying the files and restoring a database dump. (i.e. no clean setup)

The error does not occur on the different machine. I'm suspecting that this problem might have an external cause. Perhaps an unfortunate combination of PHP/Apache/Magento/configuration files.

Answer 1

oauth_nonce table does not contain a foreign key and constraint, only unique index is set, but that would not throw an "integrity constraint" exception. So you have got some other error in some other place.

Check your var/log folder contents for "Mage_Oauth" exceptions.

I noticed that oauth_nonce row is saved during request parameters validation, and after that validation an oath_consumer and an oauth_token entities are initialized and saved into database. And tokens table does have the constraints set up: to the admin user entity (you can check if the needed Magento admin user still exists), to the oauth_consumer, and to the Magento customer entity.

Hope this helps.

Answer 2

In my case, Magento was answering with a 302 redirection and an empty Location header.

By default, my Kotlin client using HttpUrlConnection tried to follow this redirection, and I guess that doing so, it sent a new request with the same Authorization header, and thus, the same nonce.

That new connection failed with a 401 error, because nonce indeed already exist...

To fix the problem, I just added a connection.instanceFollowRedirects = false to my code, and considered the 302 answer as valid.