Update return value issue

[DavitSo]

Environment

Knex version:0.95.2 Database + version: mysql 5.7

Bug

1. transaction.from(tableName) .where(...) .update({ UserID: userID, CreatedDate: createdDate, UpdatedDateTime: updatedDateTime }); CURRENT BEHAVIOUR : returns result 1. But there is no data change Same query I run in Workbanch and result is 0 row(s) affected Rows matched: 1 Changed: 0 Warnings: 0 EXPECTED: return result 0.

[DavitSo]

I get this result for raw query { "fieldCount": 0, "affectedRows": 1, "insertId": 0, "serverStatus": 3, "warningCount": 0, "message": "(Rows matched: 1 Changed: 0 Warnings: 0", "protocol41": true, "changedRows": 0 }

[DavitSo]

same query using builder I result equal to 1

[Naddiseo]

IIRC, .update will returning the number of rows affected. From the docs:

// Returns [1] in "mysql", "sqlite", "oracle"; [] in "postgresql" unless the 'returning' parameter is set.
knex('books').update('title', 'Slaughterhouse Five')

[U-4-E-A]

Any solution to this? I have tried the knex('books').update('title', 'Slaughterhouse Five') solution above but it does not work.

[Naddiseo]

@U-4-E-A does not work in what way?

[U-4-E-A]

My bad, I just needed to change the knex connection config to include flags: ['-FOUND_ROWS'] to remove that default.

[pz129]

I'm having this same issue, but I'm not in a position to change the knex connection config, any other options? The docs say that you can add a parameter to the .update function to get columns of rows changed but thats not working.

[Naddiseo]

@pz129, which issue specifically, and what database engine are you using?

[DavitSo]

Hi @Naddiseo you mentioned docs. I see this but its not working as expected update always return 1, no meter do update or not.

[DavitSo]

Sorry seems I found issue, I was using client: 'mysql'. Changed to client: 'mysql2' at first sight is starting to work :)

[Naddiseo]

@DavitSo

Hi @Naddiseo you mentioned docs. I see this but its not working as expected update always return 1, no meter do update or not.

That is working as expected, isn't it? The update will return that there was "1" row updated. From your query, I would expect the updatedDateTime variable to be new each time, so would always update a row.

[DavitSo]

@Naddiseo updatedDateTime is the same every time, no change in data, Also I have ON UPDATE CURRENT_TIMESTAMP field which is not updated. but return value of update(row) is always 1

[DavitSo]

Find solution pass connection { ..., flags: '-FOUND_ROWS', ... } by default this is true(- is false :) )

[ryhn]

@DavitSo i have a same issue. Can you provide a connection parameters that you use. I am using MySQL8

 self.knex
      .transaction(function (trx) {
        return trx(self.table)
          .where(where)
          .update(data)
          .then(function (count) {
            return { success: count };
          });
      })
      .catch(function (error) {
        console.error(error);
        return self._err(error);
      });

[DavitSo]

This is mysql full flag list var defaultFlags = [ '-COMPRESS', // Compression protocol NOT supported '-CONNECT_ATTRS', // Does NOT send connection attributes in Protocol::HandshakeResponse41 '+CONNECT_WITH_DB', // One can specify db on connect in Handshake Response Packet '+FOUND_ROWS', // Send found rows instead of affected rows '+IGNORE_SIGPIPE', // Don't issue SIGPIPE if network failures '+IGNORE_SPACE', // Let the parser ignore spaces before '(' '+LOCAL_FILES', // Can use LOAD DATA LOCAL '+LONG_FLAG', // Longer flags in Protocol::ColumnDefinition320 '+LONG_PASSWORD', // Use the improved version of Old Password Authentication '+MULTI_RESULTS', // Can handle multiple resultsets for COM_QUERY '+ODBC', // Special handling of ODBC behaviour '-PLUGIN_AUTH', // Does NOT support auth plugins '+PROTOCOL_41', // Uses the 4.1 protocol '+PS_MULTI_RESULTS', // Can handle multiple resultsets for COM_STMT_EXECUTE '+RESERVED', // Unused '+SECURE_CONNECTION', // Supports Authentication::Native41 '+TRANSACTIONS' // Expects status flags ]; from this list you can pass to knex by following way knex( ..., connecton: { ..., flags: '+FOUND_ROWS,-PLUGIN_AUTH', ... } )

[ryhn]

@DavitSo thanks for reply. I tried but the result is the same.

[DavitSo]

Another way is use default connection settings and use raw query 1. construct query 2. self.knex.raw(query.toSQL().toNative().sql, query.toSQL().toNative().bindings) if (result[0].affectedRows === 1 && result[0].changedRows === 0) { // affected returns matched + changed, changedRows is actual changed by default // Do nothing data exists and no change } else if (result[0].affectedRows === 1 && result[0].changedRows === 1) { // data exists and updated } else if (result[0].affectedRows === 0) { // data not exists, do insertion }