Loopback: MySQL Error

[tomcooksey]

I have tried to recreate this is Sandbox and can't, so I don't know where the issue is but I am getting an SQL error when processing a PUT through the API to one of my models (POST and DELETE work fine). Is there a way I can view the whole MySQL statement being sent to the server to give more clues?

Error is: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=1' at line 1

Data being sent is:

{"id":1,"title":"Test Advert ","translations":[{"language_id":1,"title":"Test Advert "}]}

(The translations aspect is used by a mixin not specified in the model definition below because removing it all still results in the error)
Model looks like this:

{
   "name":"Advert",
   "base":"PersistedModel",
   "idInjection":true,
   "options":{
      "validateUpsert":true
   },
   "mixins":{

   },
   "properties":{
      "organisation_id":{
         "type":"number",
         "required":true
      }
   },
   "validations":[

   ],
   "acls":[

   ],
   "methods":{

   }
}

As you can see the model is extremely simple.
This appears to have started happening since a npm update, but I might be wrong!

[tomcooksey]

So, the plot thickens..!

If I add a new field to the model called 'another_property' type, string, and then send that field in a PUT everything works fine again. Curiously this model is the only model I have that only has one field (organisation_id).

Any ideas?

This works:

{"id":3,"another_property":"dfdf"}

Model Definition:

{
   "name":"Advert",
   "base":"PersistedModel",
   "idInjection":true,
   "options":{
      "validateUpsert":true
   },
   "mixins":{

   },
   "scope":{

   },
   "properties":{
      "another_property":{
         "type":"string"
      },
      "organisation_id":{
         "type":"number",
         "required":true
      }
   },
   "validations":[

   ],
   "relations":{

   },
   "acls":[

   ],
   "methods":{

   }
}

[tomcooksey]

If I don't send the field (even if just null value) another_property I get an SQL parse error.

[tomcooksey]

I now seem to have confirmed that if you have a model with only one property then PUTs don't work, they result in an SQL error. I removed all but one fields of another model and got the same error..!

[tomcooksey]

Ah OK, sorry for the rambling. I think I have nailed it, having one field can be symptomatic of this problem. In my app I have an organisation_id but I fill this in automatically on the server based on their credentials. For the model I first found this issue with, I only have one field - organisation_id as all the other fields like title, content etc are in a separate linked table for translation purposes. So the bug appears to be performing a PUT without sending at least one field to update. The client in my app doesn't send organisation_id, hence the issue. I am comfortable that this is probably correct behavior, however an SQL error seems a bit uncaught, we could do with an error stating to update at least one field or something.

[loay]

@tomcooksey
let me read from the top first so I can understand the whole situation :)

[loay]

Is there a way I can view the whole MySQL statement being sent to the server to give more clues?

you can use console.log() in your code.

For the rest of the issues, can you create a sandbox repo or if you already have a github rep, can you share the link, so I can have a look . Thanks.

[tomcooksey]

Hi, I have forked the repo here and created a sandbox - it requires a database to recreate the issue:

https://github.com/tomcooksey/loopback-sandbox

If you POST to adverts an empty JSON object, a new model gets created as in one of my mixins organisation_id is being populated so it doesn't fail validation. If you then perform a put on that record Adverts/{id} with the same empty object you get the SQL parse error.

[raymondfeng]

Please use DEBUG=loopback:connector:mysql env var to dump out the offending SQL statements.

[loay]

using your repo as is, I connected the datasource to a remote phpmyadmin db and I tested the outcome.
POST works fine.
PUT works fine for the first time, but not the 2nd time.

So I tested few possibilites with create and update in the create-models.js file .. I found this error.

C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcooksey\node_modules\mysql\lib\pr
otocol\Parser.js:77
        throw err; // Rethrow non-MySQL errors
        ^

TypeError: Cannot read property 'get' of null
    at C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcooksey\server\mixins\perm
issions.js:15:31
    at notifySingleObserver (C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcook
sey\node_modules\loopback-datasource-juggler\lib\observer.js:103:22)
    at iterate (C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcooksey\node_modu
les\loopback-datasource-juggler\node_modules\async\lib\async.js:181:13)
    at Object.async.eachSeries (C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomc
ooksey\node_modules\loopback-datasource-juggler\node_modules\async\lib\async.js:
197:9)
    at doNotify (C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcooksey\node_mod
ules\loopback-datasource-juggler\lib\observer.js:100:11)
    at doNotify (C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcooksey\node_mod
ules\loopback-datasource-juggler\lib\observer.js:98:49)
    at doNotify (C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcooksey\node_mod
ules\loopback-datasource-juggler\lib\observer.js:98:49)
    at doNotify (C:\Users\IBM_ADMIN\Desktop\loopback-sandbox_tomcooksey\node_mod
ules\loopback-datasource-juggler\lib\observer.js:98:49)
    at Function.ObserverMixin._notifyBaseObservers (C:\Users\IBM_ADMIN\Desktop\l
oopback-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer
.js:121:5)
    at Function.ObserverMixin.notifyObserversOf (C:\Users\IBM_ADMIN\Desktop\loop
back-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer.js
:96:8)
    at Function.ObserverMixin._notifyBaseObservers (C:\Users\IBM_ADMIN\Desktop\l
oopback-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer
.js:119:15)
    at Function.ObserverMixin.notifyObserversOf (C:\Users\IBM_ADMIN\Desktop\loop
back-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer.js
:96:8)
    at Function.ObserverMixin._notifyBaseObservers (C:\Users\IBM_ADMIN\Desktop\l
oopback-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer
.js:119:15)
    at Function.ObserverMixin.notifyObserversOf (C:\Users\IBM_ADMIN\Desktop\loop
back-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer.js
:96:8)
    at Function.ObserverMixin._notifyBaseObservers (C:\Users\IBM_ADMIN\Desktop\l
oopback-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer
.js:119:15)
    at Function.ObserverMixin.notifyObserversOf (C:\Users\IBM_ADMIN\Desktop\loop
back-sandbox_tomcooksey\node_modules\loopback-datasource-juggler\lib\observer.js
:96:8)

Now, I isolated the problem in ownership and permissions files. Once, I commented out the observe functions, I can create, update and delete without any errors. I understand you will need these files for mixins, but as far as I see it, the issue is there.

Let me know if you need help further debugging.

[tomcooksey]

Hey, thanks for taking a look. The error you got suggests that loopback.getCurrentContext() is returning null, I'd be interested as to the circumstances that can cause this as I've never come across that.

However, I think my original problem still persists, I have committed a new change to my sandbox removing all the mixins. In this case if you create a new Advert through POST sending:

POST /api/Adverts
{
    "organisation_id": 1
}

It's created successfully. If you then perform a PUT with an empty object:

PUT /api/Adverts/{id}
{
}

I still get an SQL error.

[loay]

I am not sure what are you trying to do?
You can't use blank values to update when the values are required.
if you use sql to run the same operation, you will get the same error.
what are you trying to do in native mysql?? can you please correct the following statement?

UPDATE `Advert`  WHERE `id`=1

?

[tomcooksey]

Yea, I realise it's not the right thing to do, however what I believe that needs to be fixed is a more meaningful error message, ie the error should be caught before it gets to actually executing the query. The main goal of an ORM layer is to abstract away from the database it self and I don't think the framework is doing that very well in this instance.

[loay]

I am not sure if you used Raymond's suggestion above .. and also with the console.log in the boot data for creation model, I got that error:

{ [Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual
 that corresponds to your MySQL server version for the right syntax to use near
'' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

which is similar to what you got in explorer as well.

There is a new error handler in development right now. Hopefully that will provide more info. if not, we can look into adding a feature to get the proper error message.

[necevil]

Hey Guys,
I ran into a similar issue and setting my DEBUG=loopback:connector:mysql environmental variable really helped troubleshoot the query issue. For those not familiar on the terminal, run:

export DEBUG=loopback:connector:mysql

Thanks @loay and @raymondfeng
Wanted to re-post that suggestion since it solved a problem that I had worked on for ~3 hours prior to finding this discussion.