ER_BAD_FIELD_ERROR: Unknown column 'id' in 'field list'

[Shibu Basheer]

Hello,

I am trying to connect LoopBack with an existing mysql database and create REST APIs for a table called country.  I have added the db connection in datasource.json and have the following model defined in my models.json.

 "country": {

    "properties": {

      "country_id": {

        "type": "number"

      },

      "country_name": {

        "type": "string"

      },

      "country_code": {

        "type": "string"

      },

      "country_status": {

        "type": "number"

      }

    },

    "public": true,

    "dataSource": "mysql"

  }

When I try to GET countries, I get the following error.  I do not have an "id" field in the country table nor is it defined in the models.json.  Appreciate any pointers on this.  

"error": { "name": "Error", "status": 500, "message": "ER_BAD_FIELD_ERROR: Unknown column 'id' in 'field list'", "code": "ER_BAD_FIELD_ERROR", "errno": 1054, "sqlState": "42S22", "index": 0, "stack": "Error: ER_BAD_FIELD_ERROR: Unknown column 'id' in 'field list'\n at Query.Sequence._packetToError (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:30:14)\n at Query.ErrorPacket (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:82:18)\n at Protocol._parsePacket (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:202:24)\n at Parser.write (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Parser.js:62:12)\n at Protocol.write (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:37:16)\n at Socket.<anonymous> (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Connection.js:72:28)\n at Socket.EventEmitter.emit (events.js:95:17)\n at Socket.<anonymous> (_stream_readable.js:746:14)\n at Socket.EventEmitter.emit (events.js:92:17)\n at emitReadable_ (_stream_readable.js:408:10)\n --------------------\n at Protocol._enqueue (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:110:48)\n at PoolConnection.Connection.query (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Connection.js:165:25)\n at runQuery (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/lib/mysql.js:128:16)\n at /home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/lib/mysql.js:174:7\n at Pool.<anonymous> (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Pool.js:53:14)\n at Handshake.Sequence.end (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:78:24)\n at Handshake.Sequence.OkPacket (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:87:8)\n at Protocol._parsePacket (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:202:24)\n at Parser.write (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Parser.js:62:12)\n at Protocol.write (/home/shibu/loopback-mysql-example/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:37:16)" } }

Thanks,

Shibu

[Raymond Feng]

Which field is your PK? For example, if it is country_id, you need to tell LoopBack by adding “id”: true. Otherwise, LoopBack will assume there is a property named as ‘id’.

"country": {

    "properties": {

      "country_id": {

        "type": “number”,

          “id”: true

      },

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

--
You received this message because you are subscribed to the Google Groups "LoopbackJS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to loopbackjs+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Shibu Basheer]

That worked Raymond.  Thank you.  

Shibu

--
--
Shibu Basheer
CTO, Cabot Solutions

Phone : + 91 9446481825 (INDIA) , +1 (845) 262 3347 (US/Canada)

Skype : shibu.basheer
Web   : http://www.cabotsolutions.com

[Adithya Varma App Dev]

Well take a look at this code  #Raymond Feng 

var values = [ 

 { LinkedInID: '03WbF1cC9A',

      FirstName: 'Tony',

      LastName: 'Restell',

      UserID: 1,

      Type: 'LINKEDIN',

'LinkedInID' : true,

'UserID' : true},

 { LinkedInID: 'sAfOMFN4kr',

      FirstName: 'James',

      LastName: 'George',

      UserID: 2,

      Type: 'LINKEDIN', 

 'LinkedInID' : true,

'UserID' : true},

 { LinkedInID: 'z0C_MalJlx',

      FirstName: 'Rana Pratap',

      LastName: 'G',

      UserID: 3,

      Type: 'LINKEDIN' ,

 'LinkedInID' : true,

'UserID' : true} 

 ];

conn.query('CALL usp_InsertLinkedInConnections_linkedin_Insert(?)', values ,function(err, results) {

if (err) console.log(err);

console.log([values]);

    conn.end();

}); 


When i try to execute this program im getting the following error

{ [Error: ER_BAD_FIELD_ERROR: Unknown column 'LinkedInID' in 'field list']

  code: 'ER_BAD_FIELD_ERROR',

  errno: 1054,

  sqlState: '42S22',

  index: 0 }

[ [ { LinkedInID: true,

      FirstName: 'Tony',

      LastName: 'Restell',

      UserID: true,

      Type: 'LINKEDIN' },

    { LinkedInID: true,

      FirstName: 'James',

      LastName: 'George',

      UserID: true,

      Type: 'LINKEDIN' },

    { LinkedInID: true,

      FirstName: 'Rana Pratap',

      LastName: 'G',

      UserID: true,

      Type: 'LINKEDIN' } ] ]

[Ran Regev]

I discover the models using discoverAndBuildModels with views:true.

I can't GET the view:

Response Body

{
  "error": {
    "name": "Error",
    "status": 500,
    "message": "ER_BAD_FIELD_ERROR: Unknown column 'id' in 'field list'",
    "code": "ER_BAD_FIELD_ERROR",
    "errno": 1054,
    "sqlState": "42S22",
    "index": 0,
    "stack": "Error: ER_BAD_FIELD_ERROR: Unknown column 'id' in 'field list'\n    at Query.Sequence._packetToError (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)\n    at Query.ErrorPacket (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:82:18)\n    at Protocol._parsePacket (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:271:23)\n    at Parser.write (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Parser.js:77:12)\n    at Protocol.write (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:39:16)\n    at Socket.<anonymous> (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Connection.js:82:28)\n    at Socket.emit (events.js:95:17)\n    at Socket.<anonymous> (_stream_readable.js:765:14)\n    at Socket.emit (events.js:92:17)\n    at emitReadable_ (_stream_readable.js:427:10)\n    --------------------\n    at Protocol._enqueue (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:135:48)\n    at PoolConnection.Connection.query (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Connection.js:185:25)\n    at runQuery (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/lib/mysql.js:153:16)\n    at /home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/lib/mysql.js:199:7\n    at Ping.onPing (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Pool.js:94:5)\n    at Ping.<anonymous> (/home/ran/work/rf-server/datarest/node_modules/loopback/node_modules/continuation-local-storage/context.js:74:17)\n    at Ping.b [as _callback] (domain.js:183:18)\n    at Ping.Sequence.end (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)\n    at Ping.Sequence.OkPacket (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)\n    at Protocol._parsePacket (/home/ran/work/rf-server/datarest/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:271:23)"
  } 

} 


There is no primary key to views.

How to solve?

Ran.