buildWhere() function does not handle cases of 'like' and 'nlike' condition types.

Is it possible to set mysql config to connect RDS read replica DB?

I am seeing ctx.isNewInstance = undefined/true on an existing instance save. My script just loads all Person entities, then loops over and updates a single attrib value. Works fine when I use .updateAttribute() but it should work for .save() since I'm calling a function on an instance with an ID.

Versions:

"loopback": "^2.18.0",
"loopback-boot": "^2.8.1",
"loopback-connector-mongodb": "^1.11.1",
"loopback-connector-mysql": "^2.1.1",
"loopback-datasource-juggler": "^2.30.0",

My update script:

Person.find(function(err, everyone) {
  if(err) {
    console.log(err);

  } else {
    var cnt = 0;

    _.forEach(everyone, function(person) {

      console.log(person); // id is present!

      var existingHash = person.hash;

      person.hash = getHash(person.email);

      person.save(function(err, personSaved) {
        if(err) {console.log(err);}

        cnt += 1;

        if(cnt === everyone.length) {
          mysqlDS.disconnect();
          process.exit(0);
        }

      });

    });

  }

});

My before save hook:

Person.observe('before save', function(ctx, next) {

  console.log('[before save] ctx.isNewInstance', ctx.isNewInstance);

  next();

});

My after save hook:

Person.observe('after save', function(ctx, next) {

  console.log('[after save] ctx.isNewInstance', ctx.isNewInstance);

  next();

});

Output when I run the script:

[before save] ctx.isNewInstance undefined
[after save] ctx.isNewInstance true

Shouldn't these both (explicitly) be false when calling .save() on a person entity with an id?

I define models like:

{
    price:Number,
    ...
}

But all of the properties of type 'Number' are generated as INT(11) instead of float, double or decimal as expected in MySQL. I think there should be another property to define whether the field is decimal or integer but I cannot find any documents on that.

File https://github.com/strongloop/loopback-connector-mysql/blob/9dc6353b18a6cdc685b29fa64738c9df769a164c/lib/discovery.js

Line 64
Line 98

I receive Object #<Object> has no method 'esacpe' when trying to "discover models" from data source

Each time I use npm update in the project scope, the npm always replace the latest 1.2.0 version with the version 1.1.1.

The part of my package.json is like this:

"dependencies": {
        "async": "^0.2.10",
        "cors": "^2.1.1",
        "formidable": "^1.0.14",
        "grunt-loopback-angular": "^1.1.0",
        "loopback": "~1.7.0",
        "loopback-connector-mongodb": "^1.1.8",
        "loopback-connector-mysql": "~1.2.0",
        "loopback-connector-rest": "~1.1.3",
        "loopback-datasource-juggler": "^1.3.11",
        "loopback-explorer": "~1.1.0",
        "loopback-storage-service": "^1.0.0",
        "mkdirp": "^0.3.5",
        "mocha": "^1.17.1",
        "rc": "~0.3.4",
        "request": "~2.34.0",
        "strong-task-emitter": "~0.0.5",
        "supertest": "^0.9.2"
    },

Suppose I have three tables

User
--------
user_id
firstname
lastname

Car
--------
car_id
user_id
color_id
name

Color
--------
color_id
name

If I run the query:

User.find({
  include: [{car: 'color'}]
}, function(err, user) {});

I would expect it to execute a single query that joins all three tables and massages the data correspondingly into a JSON structure. However, when logging the mysql queries I see that this will perform a separate query for each color that belongs to the car.

This seems very inefficient and slow depending on your data.

Hello!

I've been playing with loopback for a few days and I'm having trouble with a specific field in my model:

 "myField": {
   "type": [
     "object"
   ],
   "required": false
 }

If I try to set it like so:

...
myModel.myField = [{'atr1': "value1", 'atr2': "value2"}];
...
MyModel.upsert(myModel, function (err) {
  if (err) console.log(err);
});

I get something weird in the resulting row field:

+-----------------+                         +----------------------------------------+
| myField         |                         | myField                                |
+-----------------+        instead of       +----------------------------------------+
| [object Object] |                         | [{"atr1":"value1","atr2":"value2"}]    |
+-----------------+                         +----------------------------------------+

Could someone enlighten me as to what I'm doing wrong?

I have a model with a hasMany relationship and it is NO LONGER returning an empty array if there are no records in found in the relation

this seems to have changed because it used to return that field but with an empty array.

what should be the expected behaviour? was this documented somewhere?

I have a unique use case.
I have two MySQL datasources both with almost identical tables (the new table has some optional data that can be added) and models.

I am setting up a cron to regularly upsert data from the one datasource table to the other's datasource table.

I thought I'd be able to do a find() on the old and upsert() to the new, but I get this error on updates (not inserts).

{"code":"ER_DUP_ENTRY","errno":1062,"sqlState":"23000","index":0}

Here is the code of the function being called to do it:

var updateAllBoard = function () {
    server.models.Oldallboards.find({}, function (err, oldServerData) {
        if (err) {
             console.log('%j', err)
            process.exit(1);
        };
        server.models.Allboards.upsert(oldServerData, function (err, result) {
            if (err) {
                 console.log('%j', err)
                process.exit(1);
            }
            else {
                console.log('Successful transfer of board data from old to new.');
                process.exit(0);
            }
        });
    });
};

e.g.

if I define the pk type to string in user.json as below, it will generate varchar(255) id column in user table, but for the other tables who has userId as foreign key column, it will generate varchar(512).

"properties": {
    "id": {
      "type": "string",
      "id": true
    }
  },

Hi,

I don't find any informations about that topic but the only way i found to use ACL with extern Database isn't really easy.

slc loopback:acl

This command allow you to add acl in model but that didn't add it in db.

That mean that we have to create all the acls with automigrate script, i think that could be cool to generate directly in db too ?

I haven't seen any documentation for connection pooling support for mysql. Is this supported in any way? I also found an old feature request from 2013 about connection pooling but I can't tell if its been implemented...

Cheers

I have table with the following properties:

  `NickName` varchar NOT NULL DEFAULT '',
  `Email` varchar NOT NULL DEFAULT '',
  `Password` varchar NOT NULL DEFAULT '',
  `Status` enum('Unconfirmed','Approval','Active','Rejected','Suspended')
      NOT NULL DEFAULT 'Unconfirmed',
  `Role` tinyint unsigned NOT NULL DEFAULT '1'
  -- and so on

and schema for this table:

"properties": {
  "nickname": {
    "required": true,
    "mysql": {
      "columnName": "NickName",
      "nullable": "N"
    }
  },
  "email": {
    "required": true,
    "mysql": {
      "columnName": "Email",
      "nullable": "N"
    }
  },
  "password": {
    "required": true,
    "mysql": {
      "columnName": "Password",
      "nullable": "N"
    }
  },
  "status": {
    "required": false,
    "mysql": {
      "nullable": "N"
    }
  },
  "role": {
    "required": false,
    "mysql": {
      "columnName": "Role",
      "nullable": "N"
    }
  }
}

Columns like Status and Role are not required, but they cannot be null.
The problem is that mysql connector tries to set NULL value for these columns instead of just skipping them.
The possible fix is to add check for nullable in MySQL.prototype.toFields function.

Upon initial inspection of the code it looks like master/slave configuration support is not handled by this mysql connector. Is this correct? If so, can it be a feature request?

When calling rest endpoing /api/model/?filter[where][and][]=string_here which is an invalid filter causes an uncaught error.

/loopback-connector-mysql/lib/mysql.js:407
Object.keys(conds).forEach(function (key) {
^
TypeError: Object.keys called on non-object

Hello!
I have found an issue when working with transaction and upsert() function. When executing the upsert() in a transaction, it blocks until the query timeouts.

It seems to me that upsert(), even if used within a transaction, doesn't use the same connection used by the other functions in the transaction but opens a new one. I have logged the connection id (connection.threadId) used by the functions and indeed the upsert() opens a new connection.

I checked the code and it looks to me that the function (line 210 of loopback-connector-mysql/lib/mysql.js)

MySQL.prototype.updateOrCreate = MySQL.prototype.save =
  function(model, data, options, callback) {
....
}

take the options objects with transaction info in inputs but doesn't forward it to the execute method ( (line 237 of the same file):

this.execute(sql.sql, sql.params, function(err, info){
   ....

I have changed line 237 of 'loopback-connector-mysql/lib/mysql.js' from

this.execute(sql.sql, sql.params, function(err, info) {

to

this.execute(sql.sql, sql.params, options, function(err, info) { 

and this seems to resolve my issue: the connection id used by each function is the same.

Is it a real issue? Can it be fixed?
Thanks!

I test the hasmany through relation on mysql and mongodb using following version connector, it seems the connector cannot get the parameter from the scope :

loopback-connector-mysql version: 2.1.0
loopback-connector-mongodb:1.8.0

SysUser.json:
"coms": {
"type": "hasMany",
"model": "SysCom",
"foreignKey": "userId",
"through": "SysEmp"

},

SysEmp.json:

"relations": {

"sysCom": {
  "type": "belongsTo",
  "model": "SysCom",
  "foreignKey": "comId"
},

"sysUser": {
  "type": "belongsTo",
  "model": "SysUser",
  "foreignKey": "userId"
},

query data using SysUser model:

include: [
{
relation: 'coms',
scope: {
where: {id: 'comA'}
}
}
]

debug info:
SQL: SELECT id,name,parentId FROM SysCom WHERE id IN (?) ORDER BY id, params: [null] +11ms

Here is the link for the script I am using
https://gist.github.com/serkanserttop/64fc2d4465fb154066db

I was testing with multiple databases for an app, and the schema names are like "chosendb", "chosendb2", "chosendb3", "chosendb5", etc.
I am using "chosendb5", but the problem is that my first model is taken from "chosendb".
I can verify that from both "options.mysql.schema" field of the schema within the callback of "dataSource.discoverSchema".
All other models are correctly from "chosendb5".
Interestingly, strong-studio builds them correctly, hence I also tried using the "app.dataSources.chosendb5" field for the dataSource, but that did not solve the issue as well.

This is a very peculiar bug that is very difficult to figure out, any help would be appreciated.

Hi,

I am using the nearby function for a Person class like this:

Person.nearby = function(here, max, next) {
max = Number(max || 10);
Person.find({
limit: 40,
where: {
location: {
near: here,
maxDistance: max
}
}
}, next);
};

I have almost 300 thousand persons in my database, hence the query takes around eight seconds and also blocks node in that time...

I am wondering if the calculation is done via sql or in node itself? Any advice, how to improve the performance? Adding an index on the location column does not have any effect...

GET /auth/facebook 302 2ms - 428b
Error: ER_BAD_FIELD_ERROR: Unknown column '0' in 'field list'
at Query.Sequence._packetToError (/Users/michaellee/CodeSample/loopback-example-passport/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:30:14)
at Query.ErrorPacket (/Users/michaellee/CodeSample/loopback-example-passport/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:82:18)
at Protocol._parsePacket (/Users/michaellee/CodeSample/loopback-example-passport/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:213:24)
at Parser.write (/Users/michaellee/CodeSample/loopback-example-passport/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Parser.js:62:12)
at Protocol.write (/Users/michaellee/CodeSample/loopback-example-passport/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:37:16)
at Socket. (/Users/michaellee/CodeSample/loopback-example-passport/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Connection.js:75:28)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket. (stream_readable.js:745:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable (_stream_readable.js:407:10)

loopback:connector:mysql SQL: INSERT INTO userIdentity SET provider = 'facebook-login',authScheme = 'oAuth 2.0',externalId = '10202726402224237',profile = 0 = '{', 1 = '"', 2 = 'i', 3 = 'd', 4 = '"', 5 = ':', 6 = '"', 7 = '1', 8 = '0', 9 = '2', 10 = '0', 11 = '2', 12 = '7', 13 = '2', 14 = '6', 15 = '4', 16 = '0', 17 = '2', 18 = '2', 19 = '2', 20 = '4', 21 = '2', 22 = '3', 23 = '7', 24 = '"', 25 = ',', 26 = '"', 27 = 'd', 28 = 'i', 29 = 's', 30 = 'p', 31 = 'l', 32 = 'a', 33 = 'y', 34 = 'N', 35 = 'a', 36 = 'm', 37 = 'e', 38 = '"', 39 = ':', 40 = '"', 41 = 'M', 42 = 'i', 43 = 'c', 44 = 'h', 45 = 'a', 46 = 'e', 47 = 'l', 48 = ' ',
…
…
…
…
…
...
190 = 'g', 191 = 'k', 192 = '7', 193 = 'k', 194 = 'b', 195 = 'q', 196 = 'U', 197 = 'h', 198 = 's', 199 = '4', 200 = 'X', 201 = 'm', 202 = 'E', 203 = 'L', 204 = 'R', 205 = 'R', 206 = 'w', 207 = 'Z', 208 = 'D', 209 = '"', 210 = '}',created = "2014-05-25 07:17:29",modified = "2014-05-25 07:17:29",id = NULL,userId = 6 +8ms
Error: { [Error: ER_BAD_FIELD_ERROR: Unknown column '0' in 'field list']
code: 'ER_BAD_FIELD_ERROR',
errno: 1054,
sqlState: '42S22',
index: 0 }

Using Model.count([filter], callback), filter settings do not appear in MySQL's SELECT COUNT(*) statement.

Hi guys,

I have a strange problem. I am sending a POST request containing user information, especially the Facebook ID, to my loopback server (all up-to-date, as of today).
The strange thing is that the fbId gets corrupted at the last char. First I thought it has todo with column type bigInt, then I switched to varchar(64), but it did not help...

This i what my iOS app is logging (I am using RestKit):
REQUEST:
2014-08-19 23:03:25.661 PartyRadar[1419:60b] T restkit.network:RKObjectRequestOperation.m:148 POST 'http://api.partyradar.co/persons':
request.headers={
Accept = "application/json";
"Accept-Language" = "de;q=1, en;q=0.9, fr;q=0.8, zh-Hans;q=0.7, zh-Hant;q=0.6, ja;q=0.5";
"Content-Type" = "application/json; charset=utf-8";
"User-Agent" = "PartyRadar/1000 (iPhone; iOS 7.1.2; Scale/2.00)";
}
request.body={"fbId":"10152350030328371","gender":"male","firstName":"Ben","id":null,"lastName":"Marten"}

RESPONSE:
2014-08-19 23:03:25.834 PartyRadar[1419:3c07] D restkit.object_mapping:RKMapperOperation.m:377 Executing mapping operation for representation: {
fbId = 10152350030328372;
firstName = Ben;
gender = male;
id = 532046;
lastName = Marten;
}
and targetObject: <Person: 0x1702c25a0> (entity: Person; id: 0xd000000000040004 x-coredata://CB9960ED-F70A-4511-B7A6-C429039AC176/Person/p1 ; data: {
fbId = 10152350030328371;
firstName = Ben;
gender = male;
lastName = Marten;
})

Any ideas why the Facebook id gets increased by one at the end????

Hi,

I am trying to use where clause in count method for paginition purpose. Whenever I try to use where clause, it gives me a mysql syntax error for as simple query as below.

Ride.count({
where:{"id":20}
},function(err,totalCount){
if (err) {
log.info("Total error ", err);
fn(err);
}else {
log.info("Total count ", totalCount);
}
});

this is the error I get.

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 '20' at line 1\n at Query.Sequence._packetToError (D:\Code\liftee\rest-services\node_modules\loopback-connector-mysql\node_modules\mysql\lib\protocol\sequences\Sequence.js:48:14)\n at Query.ErrorPacket (D:\Code\liftee\rest-services\node_modules\loopback-connector-mysql\node_modules\mysql\lib\protocol\sequences\Query.js:83:18)\n at Protocol._parsePacket (D:\Code\liftee\rest-services\node_modules\loopback-connector-mysql\node_modules\mysql\lib\protocol\Protocol.js:271:23)\n at Parser.write (D:\Code\liftee\rest-services\node_modules\loopback-connector-mysql\node_modules\mysql\lib\protocol\Parser.js:77:12)\n at Protocol.write (D:\Code\liftee\rest-services\node_modules\loopback-connector-mysql\node_modules\mysql\lib\protocol\Protocol.js:39:16)\n at Socket. (D:\Code\liftee\rest-services\node_modules\loopback-connector-mysql\node_modules\mysql\lib\Connection.js:92:28)\n at Socket.emit (events.js:107:17)\n at readableAddChunk (_stream_readable.js:163:16)\n at Socket.Readable.push (_stream_readable.js:126:10)\n at TCP.onread (net.js:529:20)\n --------------------\n at Protocol._enqueue (D:\Code\liftee\rest-services\node_modules\loopback-co

Having worked on various enterpise projects in the pase it's odd for me to see camel-cased table names and table field names. it is more common to see underlined syntax, as is also found in loopback-connector-mysql source code.

It would be great to be able to specify the desired naming convention as an optional setting to preserve backward compatibility with existing projects and for new projects to have the chance to use proper naming conventions.

Does this allow for passing along settings for SSL support? If I'm understanding this thread correctly, it seems that the mysql module might want an "ssl" option to be passed to it. Is that something that is currently in place, or is that something that would have to be added to the options object in /lib/mysql.js?

loopback-workspace calls dataSource.connect to check if the connection settings are correct. It seems that the MySQL connector does not implement that method, thus the test is always passing.

The connector should establish a connection instead and report an error when the settings are not valid.

I am getting the following error, by letting loopback create my Tables:

{ [Error: ER_TOO_BIG_ROWSIZE: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs]
  code: 'ER_TOO_BIG_ROWSIZE',
  errno: 1118,
  sqlState: '42000',
  index: 0 }

I assume it is due to line 1026 in lib/mysql.js. Once you have two columns of size 32k, the rows become to large.

As discussed in this thread.
Creating issue for same.

{
  "name": { "type": "String", "index": true },
  "email": { "type": "String", "index": {"unique": true} },
  "age": "Number"
}

For above json config, email is not configured with UNIQUE index in mysql

mysql connector is sending to the db unnecesary null values for fields not populated on create method, overriding the default values that should be populated otherwise by the database.
for example : created timestamp NULL DEFAULT CURRENT_TIMESTAMP,

to fix this on line 275 , mysql.js
if ("NULL" !== value) {
fields.push(self.columnEscaped(model, key) + ' = ' + value);
}
note that the same is happening with updateOrCreate.

I'll apreciate if you can commit this change,
Regards,
Guille

I have the following models:

person.json:
{
  "name": "Person",
  "plural": "Persons",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "mysql": {
      "table": "PerPerson"
    }
  },
  "properties": { ...  },
  "validations": [],
  "relations": {
    "ministries": {
      "type": "hasMany",
      "model": "Ministry",
      "foreignKey": "personId",
      "through": "PersonMinistry"
    }
  },
  "acls": [],
  "methods": []
}

ministry.json
{
  "name": "Ministry",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "mysql": {
      "table": "PerMinistry"
    }
  },
  "properties": { ... },
  "validations": [],
  "relations": {
    "persons": {
      "type": "hasMany",
      "model": "Person",
      "foreignKey": "ministryId",
      "through": "PersonMinistry"
    }
  },
  "acls": [],
  "methods": []
}

person-ministry.json
{
  "name": "PersonMinistry",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "mysql": {
      "table": "PerPersonMinistry"
    }
  },
  "properties": {},
  "validations": [],
  "relations": {
    "person": {
      "type": "belongsTo",
      "model": "Person",
      "foreignKey": "personId"
    },
    "ministry": {
      "type": "belongsTo",
      "model": "Ministry",
      "foreignKey": "ministryId"
    }
  },
  "acls": [],
  "methods": []
}

Now, when I try to get a Person with the related ministries, I get the following error: "ER_NO_SUCH_TABLE: Table 'gemmii-test.PersonMinistry' doesn't exist".

When I remove the custom table name "PerPersonMinistry" from PersonMinistry, everything works.

Description

There's an issue launching the API Gateway when switched from memory datasource to MySQL datasource and overriding a model.

Expected Behavior

The server normally launches

 Steps to reproduce

• Get a fresh loopback-gateway install
• Create a file that overrides the Application model and set it in common/models
• (optional) Do some modification (for example add a dataType text to collaborators)
• Run the server

Messages

~/node_modules/loopback-datasource-juggler/lib/relation-definition.js:1120
    idName = modelTo.dataSource.idName(modelTo.modelName) || 'id';
                                ^
TypeError: Cannot call method 'idName' of null
    at Function.RelationDefinition.belongsTo (~/node_modules/loopback-datasource-juggler/lib/relation-definition.js:1120:33)
    at Function.RelationMixin.belongsTo (~/node_modules/loopback-datasource-juggler/lib/relations.js:123:29)
    at module.exports (~/node_modules/loopback-component-oauth2/lib/models/index.js:26:19)
    at Function.module.exports (~/node_modules/loopback-component-oauth2/lib/oauth2-loopback.js:145:16)
    at Object.<anonymous> (~/server/server.js:36:51)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)
    at Function.Module.runMain (module.js:497:10)

More info

https://groups.google.com/d/msg/loopbackjs/fbXg52tXe-M/5B-xtKEwCVYJ

I am getting following error while trying to login using REST as instructed in http://docs.strongloop.com/display/DOC/Creating+and+authenticating+users . This method is validating correctly because while providing wrong credentials it's returning 401 (as expected).
{ "error": { "name": "Error", "status": 500, "message": "ER_BAD_FIELD_ERROR: Unknown column 'userId' in 'field list'", "code": "ER_BAD_FIELD_ERROR", "errno": 1054, "sqlState": "42S22", "index": 0, "stack": "Error: ER_BAD_FIELD_ERROR: Unknown column 'userId' in 'field list'\n at Query.Sequence._packetToError (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\protocol\\sequences\\Sequence.js:32:14)\n at Query.ErrorPacket (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\protocol\\sequences\\Query.js:82:18)\n at Protocol._parsePacket (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\protocol\\Protocol.js:186:24)\n at Parser.write (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\protocol\\Parser.js:62:12)\n at Protocol.write (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\protocol\\Protocol.js:36:16)\n at Socket.ondata (stream.js:51:26)\n at Socket.EventEmitter.emit (events.js:117:20)\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 Query.Sequence (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\protocol\\sequences\\Sequence.js:15:20)\n at new Query (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\protocol\\sequences\\Query.js:12:12)\n at Function.Connection.createQuery (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\Connection.js:47:10)\n at PoolConnection.Connection.query (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\Connection.js:131:26)\n at runQuery (C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\lib\\mysql.js:127:20)\n at C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\lib\\mysql.js:171:13\n at C:\\Users\\IBM_ADMIN\\GitHub\\converbiz\\strongloop\\ssapi\\ssapi\\node_modules\\loopback-connector-mysql\\node_modules\\mysql\\lib\\Pool.js:34:14\n at process._tickDomainCallback (node.js:459:13)" } }

When updating a model containing an Object field, the upsert method erroneously unserializes the JSON Object notation, resulting in a faulty SQL Query.

Example:

When isnerting a new item, the create method is called, and a correct query is created:

{
  "name": "CLAPShoes",
  "displayName": "CLAP Shoes",
  "banner": "ClAP Shoes",
  "media": {"logo":"assets/images/projects/clap.JPG","main_media":"assets/images/projects/clap_big.JPG"},
  "descrip": null,
  "descrip2": null,
  "id": 0,
  "teamId": 0
}

translates to

INSERT INTO `project` SET `name` = 'CLAPShoes',`displayName` = 'CLAP Shoes',`banner` = 'ClAP Shoes',`media` = '{\"logo\":\"assets/images/projects/clap.JPG\",\"main_media\":\"assets/images/projects/clap_big.JPG\"}',`descrip` = NULL,`descrip2` = NULL,`id` = 0,`teamId` = 0

Now when the model is updated, the following data is sent to to the PUT REST endpoint:

{
  "name": "CLAPShoes",
  "displayName": "CLAP Shoes",
  "banner": "ClAP Shoes",
  "media": {"logo":"assets/images/projects/clap.JPG","main_media":"assets/images/projects/clap_big.JPG"},
  "descrip": null,
  "descrip2": null,
  "id": 23,
  "teamId": 0
}

Note that the only difference is the new id field indicating that the new model must be updated. But the mysql query sent is wrong because it specifies the Object field properties as DB fields, which is incorrect.

INSERT INTO `project` 
`name`, `displayName`, `banner`, `media`, `descrip`, `descrip2`, `id`, `teamId`) 
VALUES ('CLAPShoes', 'CLAP Shoes', 'ClAP Shoes', `logo` = 'assets/images/projects/clap.JPG', `main_media` = 'assets/images/projects/clap_big.JPG', NULL, NULL, 22, 0) 
ON DUPLICATE KEY UPDATE 
`name` = 'CLAPShoes', `displayName` = 'CLAP Shoes', `banner` = 'ClAP Shoes', `media` = `logo` = 'assets/images/projects/clap.JPG', `main_media` = 'assets/images/projects/clap_big.JPG', `descrip` = NULL, `descrip2` = NULL, `teamId` = 0.......#42S22Unknown column 'logo' in 'field list'

Cause

I traced it to the MySQL.prototype.toDatabase of the mysql.js file of this project.
line 348:

  if (prop.type === Object) {
    return this.client.escape(val);
  }

This calls to Pool.prototype.escape of the Pool.js file of the mysql dependency, which does:

 return mysql.escape(value, this.config.connectionConfig.stringifyObjects, this.config.connectionConfig.timezone);

The second argument of this call especifies whether the object will be stringified or not. In this case it need to be done, so the mysql dependency must be configured to do so at instantiation time.

This eventually leads to the following change in your own project:
In the file mysql.js, line 41:

  var options = {
    host: s.host || s.hostname || 'localhost',
    port: s.port || 3306,
    user: s.username || s.user,
    password: s.password,
    timezone: s.timezone,
    socketPath: s.socketPath,
    charset: s.collation.toUpperCase(), // Correct by docs despite seeming odd.
    supportBigNumbers: s.supportBigNumbers,
    connectionLimit: s.connectionLimit,
  };

TO

  var options = {
    host: s.host || s.hostname || 'localhost',
    port: s.port || 3306,
    user: s.username || s.user,
    password: s.password,
    timezone: s.timezone,
    socketPath: s.socketPath,
    charset: s.collation.toUpperCase(), // Correct by docs despite seeming odd.
    supportBigNumbers: s.supportBigNumbers,
    connectionLimit: s.connectionLimit,
    stringifyObjects: true
  };

So, in the end, the object field gets stringified and is not unserialized when updating the model, thus, working perfectly.

Possibly related to issue #65:
This is more an "annoyance" than a bug and could possibly be fixed quickly. If an array of numbers is used in a filter, it's values will get modified once used in a query:

var arrayOfNumbers = [1, 2];
app.models.MyModel.find({
  where: {
    myId: {
      inq: arrayOfNumbers
    }
  }, function(err, myModels) {
    console.log(typeof arrayOfNumbers[0]); // "string"
  });

This is annoying, if the array is required for further tasks later on. Work-around:

inq:arrayOfNumbers.slice()

Here a repo to replicate the issue: https://github.com/hotaru355/issue1287

Hello .
I get this error when requesting /api/switches?filter[include]=houses

Error: Invalid date: Invalid Date
    at DateType (/home/hithim/testo/node_modules/loopback-datasource-juggler/lib/model-builder.js:525:11)
    at ModelConstructor.Object.defineProperty.set [as specActExp] (/home/hithim/testo/node_modules/loopback-datasource-juggler/lib/model-builder.js:460:81)
    at ModelConstructor.ModelBaseClass._initProperties (/home/hithim/testo/node_modules/loopback-datasource-juggler/lib/model.js:177:17)
    at ModelConstructor.ModelBaseClass (/home/hithim/testo/node_modules/loopback-datasource-juggler/lib/model.js:46:8)
    at ModelConstructor (/home/hithim/testo/node_modules/loopback-datasource-juggler/lib/model-builder.js:177:22)
    at ModelConstructor (/home/hithim/testo/node_modules/loopback-datasource-juggler/lib/model-builder.js:177:22)
    at new ModelConstructor (/home/hithim/testo/node_modules/loopback-datasource-juggler/lib/model-builder.js:177:22)
    at /home/hithim/testo/node_modules/loopback-datasource-juggler/lib/include.js:172:48
    at /home/hithim/testo/node_modules/loopback-datasource-juggler/node_modules/async/lib/async.js:125:13
    at Array.forEach (native)

there are 2 models switches and houses with relations switches -> has one houses and houses -> has many switches, column specActExp may be NULL .
Maybe someone can tell me what i do wrong.

Connection limit is being overwritten if it IS a number:

if (!isNaN(s.connectionLimit)) {
s.connectionLimit = 10;
}

should be:

if (isNaN(s.connectionLimit)) {
s.connectionLimit = 10;
}

without the NOT.

line 37 loopback-connector-mysql / lib / mysql.js

@raymondfeng as noted last week, a lot of the tests fail on a fresh MySQL install (5.6.20 homebrew).

Notably errors regarding ER_TOO_LONG_KEY (almost any kind of index string column) and there seems to be a bug with isActual (the old/new data is a completely different format, causing it to always be negated - possibly related to strongloop/loopback#468)

It seems loopback is not adding foreign key constraint in MySQL Database table as per model definition. The same questions is asked in stackoverflow.

http://stackoverflow.com/questions/20877265/loopback-model-definition-is-not-adding-foreign-key-relation-in-database-table

I am not sure I am doing it wrong or it is by design of loopback so I am duplicating the same here.

I am using loopback for API design and data modeling. I am using MySQL as my database. Although my API rest URL successfully returns results e.g. /states/{id}/cities. I have following model but it seems no foreign key relation is added. Following are my model definition.

"state": {
  "options": {
    "relations": {
      "cities": {
        "type": "hasMany",
        "model": "city",
        "foreignKey": "stateId"
      }
    }
  },
  "properties": {
    "name": {
      "type": "string"
    }
  },
  "public": true,
  "dataSource": "db",
  "plural": "states"
},
"city": {
  "options": {
    "relations": {
      "state": {
        "type": "belongsTo",
        "model": "state",
        "foreignKey": "stateId"
      }
    }
  },
  "properties": {
    "name": {
      "type": "string"
    }
  },
  "public": true,
  "dataSource": "db",
  "plural": "cities"
}

and below is screenshot of city table.

And following is State table screenshot.

I may be doing it wrong here. Looking forward for any pointers.

Consider the following configuration in datasources.json:

  "db": {
    "connector": "mysql",
    "database": "slex",
    "username": "root",
    "password": "pass",
    "debug": true
  },

Run the discovery:

app.dataSources.db.discoverModelDefinitions(function(err, data) {
  console.log(Array.prototype.slice.call(arguments));
}

Actual result: data contains tables from other databases like performance_schema.

Expected result: when the settings define a database to use, model discovery returns only tables from that database by default.

The latest version of node-mysql is 2.1.1. 😄

I wanted to make a pull request to enable the creation of fulltext and spatial indexes, but the current master branch is failing the 11 tests contained in mysql.discover.test.js, because there isn't a STRONGLOOP database whatsoever. Perhaps we are missing a required setup file to create a dummy database?

It also fails on the index deep comparison (migration.test.js), but it might be because of convention changes in different mysql drivers. (For example, it returns a Comments and Index_comments fields, whereas the test is expecting only the former).

Can you please confirm that tests are passing on your side?

1. Create a simple model attached to a mysql datasource, e.g.

   var Country = loopback.createModel('Country',
     {
       id: { type: 'string', id: true },
       countryName: 'string'
     });
   

2. Create an instance of this model.

3. Call upsert with the id the instance created in step 2, add extra properties to the JSON payload, e.g.

   {
    "id": "id-from-step-2",
    "countryCode": "us",
    "countryName": "USA"
   }

Actual result

upsert fails

TypeError: Cannot read property 'id' of undefined
    at MySQL.Connector.id (node_modules/loopback-connector-mysql/node_modules/loopback-connector/lib/connector.js:110:11)
    at node_modules/loopback-connector-mysql/lib/mysql.js:239:29
    at Array.forEach (native)
    at MySQL.updateOrCreate.MySQL.save (node_modules/loopback-connector-mysql/lib/mysql.js:238:21)
    at Function.upsert (node_modules/loopback-datasource-juggler/lib/dao.js:224:36)

and a trace log is printed to console:

Trace: Property not found: Country.countryCode
    at MySQL.Connector.id (node_modules/loopback-connector-mysql/node_modules/loopback-connector/lib/connector.js:108:13)
    at node_modules/loopback-connector-mysql/lib/mysql.js:239:29
    at Array.forEach (native)
    at MySQL.updateOrCreate.MySQL.save (node_modules/loopback-connector-mysql/lib/mysql.js:238:21)
    at Function.upsert (node_modules/loopback-datasource-juggler/lib/dao.js:224:36)

Expected result

The connector ignores unknown properties and lets the model to decide how to handle them (e.g. depending on the value of the settings strict).

See also strongloop/loopback#600.

Hi, I have been exploring the code of this module for couple of days and I noticed that dates get converted to UTC before storage ( in the toMysqlDate() function) and then converted back to local time in the fromColumnValue() method.

I noticed a problem while using a TIMESTAMP field in the EDT timezone:

• I insert an EDT date: (Mon May 25 2015 05:53:03 GMT-0400 (EDT))
• It gets stored as UTC: (2015-05-25 09:53:03.000000)

So far so good but when I query it back:

• the connector tries to convert it back to local time with this line:

val = new Date(val.toString().replace(/GMT.*$/, 'GMT'));

but here val is a string and so:

• val.toString() outputs something like 2015-05-25 09:53:03.000000
• no conversion happens, my application gets an utc date instead of an edt one, everything breaks.

My field definition is:

   "lastUpdated": "date"

(I am extending the base loopback user model)

and I am using mariadb 10.0.19

I am doing something wrong? Or is it a legitimate bug?

JSON to MySQL types
contain: Point/GeoPoint: POINT
Same behavior in MySQL to JSON doesn't seem possible to me.

n + 1 次查询效率很低的啊。

I am trying to create a bigint(20) auto increment PK. But it seems that does not support auto increment if it is not default PK dataType.

Model properties:

 "properties": {
    "id": {
      "type": "number",
      "id": true,
      "mysql": {
        "dataType": "bigint"
      }
    }
  }

Searched codes, both
https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js#L962-L965
and
https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js#L1042-L1049

do not have a field to output 'AUTO_INCREMENT' if it is customized type for the PK.

Searching the doc: http://docs.strongloop.com/display/public/LB/Model+definition+JSON+file#ModeldefinitionJSONfile-Generalpropertyproperties

There is no such field to add 'AUTO_INCREMENT'.

Any idea?

When I try to update a subset of fields in a model instance, I get errno 1364:

Error: ER_NO_DEFAULT_FOR_FIELD': Field 'sample' doesn't have a default value

Field 'sample' is not one of the fields I wish to update. The function call fails because, for MySQL, the model.save function is an alias for updateOrCreate. It generates

INSERT INTO <table> (<columns>) VALUES(<values>) ON DUPLICATE KEY UPDATE <col=val>...

The INSERT fails before it checks whether the key exists - it throws an error for any not null field not being updated.

The save function should be implemented as an unconditional update as is done in the Postgresql connector:

UPDATE <table>
SET <col=val>...
WHERE <id> = <idval>

http://docs.strongloop.com/display/LB/MySQL+connector

In 'Properties':

connector | String | Connector name, either "loopback-connector-mssql" or "mssql"

...Expect that should read:

connector | String | Connector name, either "loopback-connector-mysql" or "mysql"

Hello!

// tag-map.json
{
  "name": "TagMap",
  "base": "PersistedModel",
  "idInjection": true,
  "properties": {
    "fripId": {
      "type": "number",
      "required": true
    },
    "tagId": {
      "type": "number",
      "required": true
    }
  },
  "indexes": {
    "fripId_tagId_index": {
      "keys": {
        "fripId": 1,
        "tagId": 1
      }
    }
  },
...relations, acls, ...
}

// autoupdate.js
var app = require('./server');
var dataSource = app.dataSources.db;
dataSource.autoupdate(['TagMap'], function(err) {
  if (err) {
    throw err;
  } else {
    dataSource.disconnect();
  }
});

$ node ./server/autoupdate.js

It throws following error:

Error: ER_KEY_COLUMN_DOES_NOT_EXITS: Key column 'undefined' doesn't exist in table

So i'm trying to logging query:

ALTER TABLE `TagMap` ADD  INDEX  `fripId_tagId_index` (undefined)

I referenced this documentation:
http://docs.strongloop.com/display/public/LB/Model+definition+JSON+file
But this code seems to be different:
https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js#L884-L904