MySQL BIT(1) is mapped to 'buffer' not bool as the document states

[yonghongren]

The documentation (version 5.1.0) states MySQL BIT is mapped to 'buffer' and BIT(1) is mapped to boolean. But testing shows that BIT(1) is actually mapped to 'buffer'.

Also, the documentation doesn't mention how MySQL 'BOOL' is mapped. Testing suggests it's mapped to number. Shouldn't it be mapped as boolean? BOOL is what I am really looking to use in both MySQL schema and loopback model.

Description/Steps to reproduce

Define a table with a column defined as BIT(1). Run loopback-discover. It will shows the column is discovered as type 'buffer'.

Link to reproduction sandbox

Expected result

Additional information

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[bluermind]

I believe this is happening to me also. In my case I am trying to connect with Korean database (which i only have access to select), and it has euckr-korean-ci collation. Then loopback takes all non alphanumeric values as Buffers. Yet to find a resolution for this. Tried converting the buffers using iconv from euc-kr to utf-8 with no success.

Are there anybody using other language having similar probs?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.

[bajtos]

Reopening based on https://stackoverflow.com/questions/52905139/loopback-mysql-connector-bit1-is-recognized-always-true

When i configure loopback to covert BIT(1) mysql column as boolean it always returns true

"isActive": {
  "type": "Boolean",
  "required": false,
  "length": null,
  "precision": 1,
  "scale": null,
  "mysql": {
    "columnName": "is_active",
    "dataType": "bit",
    "dataLength": null,
    "dataPrecision": 1,
    "dataScale": null,
    "nullable": "Y"
  }
},

[bajtos]

Reopening based on https://stackoverflow.com/questions/52905139/loopback-mysql-connector-bit1-is-recognized-always-true

When i configure loopback to covert BIT(1) mysql column as boolean it always returns true

"isActive": {
  "type": "Boolean",
  "required": false,
  "length": null,
  "precision": 1,
  "scale": null,
  "mysql": {
    "columnName": "is_active",
    "dataType": "bit",
    "dataLength": null,
    "dataPrecision": 1,
    "dataScale": null,
    "nullable": "Y"
  }
},

[bajtos]

Actually, it looks like it is already possible to treat BIT(1) as booleans. In https://github.com/strongloop/loopback-connector-mysql/pull/232, we introduced three new flags that you can pass in discovery options argument to decide how boolean-like types should be treated.

Introducing three flags:

• treatCHAR1AsString: default false - treats CHAR(1) as a String instead of a Boolean
• treatBIT1AsBit: default true - treats BIT(1) as a Boolean instead of a Binary
• treatTINYINT1AsTinyInt: default true - treats TINYINT(1) as a Boolean instead of a Number

I am not sure what remains to be done here. Are we missing a documentation for these three feature flags? Do we need to change the runtime part (how the connector builds SQL queries to read/write data) to support these three transformations too?

[bajtos]

Cross-posting @yongzhi-chen's https://github.com/strongloop/loopback-connector-mysql/issues/151#issuecomment-355848225

still getting <Buffer 01> when it is converting mysql to model property (in this function MySQL.prototype.fromColumnValue). As a result even in the db value is 0, model property is set to true since buffer is evaluated to true

[yongzhi-chen]

Oh this has been a while... This is how I fixed it in my fork for my case https://github.com/yongzhi-chen/loopback-connector-mysql/commit/95df9da9c1bd248dcadb6f10b9dd845a9e20354b

[jgwinner]

Good grief, this bug loops around in a circle. Some bot marks it as stale.

This really needs to be fixed.

[Flash619]

I can confirm the following will always return true even when the value of a field is 0.

    "inactive": {
      "type": "Boolean",
      "required": false,
      "length": null,
      "precision": 1,
      "scale": null,
      "mysql": {
        "columnName": "inactive",
        "dataType": "bit",
        "dataLength": null,
        "dataPrecision": 1,
        "dataScale": null,
        "nullable": "N"
      }
    }
  }

[jgwinner]

Right, it's an Object so it would always resolve to true.

Strongloop/IBM, @yongzhi-chen showed a patch. Can we get that rolled into the official code?

@bajtos The problem isn't the documentation, it's that the flags don't work, or at least the default doesn't apply. The default is supposed to treat BIT(1) as boolean, but we still get the buffer stuff.

    == John ==

[jgwinner]

Anything?

[jgwinner]

Can we get any kind of update? Either the docs need changing, or the patch needs loading.

    == John ==

[bajtos]

I am afraid we don't have bandwidth to address this issue right now. Can somebody take the fix shown in https://github.com/yongzhi-chen/loopback-connector-mysql/commit/95df9da9c1bd248dcadb6f10b9dd845a9e20354b, add tests to verify the functionality and contribute it all in a pull request? See https://loopback.io/doc/en/contrib/index.html and https://github.com/strongloop/loopback-connector-mysql#running-tests to get started.

I am happy to help you along the way once you get to the point of opening a pull request - just mention my handle in the pull request description or comments. Unfortunately, I don't have bandwidth to monitor discussion in issues :(

[yongzhi-chen]

I can do a pull request for this

[qxl1231]

my bit also discover as buffer. version is "loopback-connector-mysql": "^5.4.2",

"deleted": { "type": "Binary", "required": true, "length": null, "precision": 1, "scale": null, "mysql": { "columnName": "deleted", "dataType": "bit", "dataLength": null, "dataPrecision": 1, "dataScale": null, "nullable": "N" } },