User throws _id error when calling update user api call in MySQL

[piere129]

Informations

• Node.js version: 9.9.0
• npm version: 5.6.0
• Strapi version: 3.0.0-alpha.12.1.2
• Database: MySQL
• Operating system: Windows

What is the current behavior?

Updating a User with the api call '/user/:id' throws the following error in MySQL:
{"statusCode":400,"error":"Bad Request","message":"select users-permissions_user.* from users-permissions_userwhereusers-permissions_user._id = '2' limit 1 - ER_BAD_FIELD_ERROR: Unknown column 'users-permissions_user._id' in 'where clause'"} . Updating User in the Admin UI does work however. I also noticed using the api call from frontend does not allow you to update the username alone: it throws an error that the Email is already taken, which makes sense because it doesn't need to be edited.

Steps to reproduce the problem

1. Create a new User. This one has id = 2 in my example
2. do an API call from seperate frontend calling '/user/2'
3. error gets thrown

What is the expected behavior?

User should be updated

Suggested solutions

I'm posting an issue because I'm not sure how to do a pull request. I fixed the problem by navigating to ./plugins/services/User and editing the fetch method:

return strapi.query('user', 'users-permissions').findOne(_.pick(params, [ 'id', '_id']));
became
return strapi.query('user', 'users-permissions').findOne(_.pick(params, [ 'id']));

After this it worked as intended :) I know this is a fix on MySQL but I'm not sure about how this fix would work on Mongo databases, but it should at least point to the right direction for fixing this issue!

---

• I'm sure that this feature hasn't already been referenced.

[derrickmehaffy]

@piere129 I think there is a pull request to prevent id duplication (the id and _id) so this may fix itself as the id duplication fixes happen.

[lauriejim]

Hello @piere129 your suggestion will break mongoose application. We have to know why you have _id with SQL database. It's strange.

[derrickmehaffy]

@lauriejim I'm not sure what you mean? SQL only has one ID column id so I think passing the _id in the query is what is causing the issue (not sure why mongo has a _id and an id column ¯\(ツ)/¯ )

[lauriejim]

@piere129 if your proposition work for Mongoose and Bookshelf generated applications feel free to submit a PR.

Check out the contributing guide to get started: https://github.com/strapi/strapi/blob/master/.github/CONTRIBUTING.md

[piere129]

I just got the same error as #1037 but with a mongoose database when calling GET /user/:id , looking into what could be the issue

[piere129]

@omar1989omb could it be that your error:
error Cast to ObjectId failed for value "3" at path "_id" for model "UsersPermissionsUser"
was because you entered a wrong id? When testing, I noticed I always got this error when entering a wrong id in mongoose.

[lauriejim]

@piere129 did you have this issue when you try to update data which have been created with and old version of Strapi ?

[lauriejim]

@piere129 Did you try with a new fresh app from alpha.12.2 version ?

[lauriejim]

I am closing this issue because we don’t have any update/needed informations about it.