CODEWITHSUNDEEP
mysqlsails.jswaterline
Alan Yudhahutama
Alan Yudhahutama

Reputation: 53

Sails.JS + MySQL: Unknown column 'NaN' in 'where clause'

this is my first time using sails.js with mySQL database and I'm really confused about this error.

I have a controller & model called 'company'. It relates to table 'company' in mySQL database. The table itself has one-to-many associations with two tables called 'module' and 'user' (each user/module must connect with one company id). I've set the foreign key in the MySQL database.

The problem is.. each time I open any page/action from this controller, there is this error:

    error: Sending 500 ("Server Error") response: 
 Error (E_UNKNOWN) :: Encountered an unexpected error
: ER_BAD_FIELD_ERROR: Unknown column 'NaN' in 'where clause'
    at Query.Sequence._packetToError (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:30:14)
    at Query.ErrorPacket (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:82:18)
    at Protocol._parsePacket (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:213:24)
    at Parser.write (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Parser.js:62:12)
    at Protocol.write (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:37:16)
    at Socket.<anonymous> (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:75:28)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:764:14)
    at Socket.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:426:10)
    --------------------
    at Protocol._enqueue (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:110:48)


    at PoolConnection.Connection.query (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:166:25)
    at PoolConnection.liveConnection.query (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/lib/connections/spawn.js:98:16)
    at __FIND__ (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/lib/adapter.js:836:20)
    at afterwards (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/lib/connections/spawn.js:104:5)
    at /Users/alanyudh/Sites/matta/node_modules/sails-mysql/lib/connections/spawn.js:40:7
    at Pool.<anonymous> (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/Pool.js:51:14)
    at Handshake.Sequence.end (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:78:24)
    at Handshake.Sequence.OkPacket (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:87:8)
    at Protocol._parsePacket (/Users/alanyudh/Sites/matta/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:213:24)

Details:  Error: ER_BAD_FIELD_ERROR: Unknown column 'NaN' in 'where clause'

This is my company model:

    module.exports = {
  attributes: {
      name:{
        type:"string",
        required:true,
        unique:true,
        minLength: 2
      },
      type:{
        type:"string",
        enum: ['hq','sub'],
        defaultsTo: 'hq'
      },     
      desc:{
        type:"text",
        required:true
      },
      logo: {
        type: 'string'
      },
      address: {
        type: 'text'
      },
      city: {
        type: 'string'
      },
      region: {
        type: 'string'
      },
      country: {
        type: 'string'
      },
      zipcode: {
        type: 'string'
      },
      phone_number: {
        type: 'string'
      },
      website: {
        type: 'string'
      }
  },
};

I have tried using 'autoPK: false', 'schema: true', add 'columnName/fieldName', but none of them is working.

What is the error means? How do I fix it?

Thank you very much.

Upvotes: 2

Views: 9366

Answers (2)

Felix
Felix

Reputation: 1810

I do NOT have the exact answer, to your specific case, BUT, I need to point to you and at the same time ventilate here my frustration, that it is a pathetic notorious problem of various APIs, when they do not address the cause of an error specifically enough, and just let the exception stack lazily bubble out to the next error handler. This often will be completely misleading the developer about the cause of the actual exception. So be VERY mindful that the error text you see may be pretty much OFF the actual issue. Well, at least you know there is a problem. Now you have to use your own wits and endless variations or approaches to figure it out.

Upvotes: 0

MrVinz
MrVinz

Reputation: 874

Oppening localhost:1337/company/index is your problem not any mysql or other orm problem!

This is a route config probem. When you open this URL the default sails behavior is that you try to access a specific company record through the default REST api. Check the default rest routes behavior.

To be more explicit in your case sails interpret that you are querying a specific company with the id "index" which is not a number NaN thus you get this error.

RESTful routes, where the path is always /:modelIdentity or /:modelIdentity/:id.

Upvotes: 6

Related Questions