Why does DELETE statement fail silently with MySQL Connector/net when it works in MySql Workbench and PHP/PDO?

Question

I have a MariaDB database containing a table with information about mobile devices (both android and ios) running on a CentOS 7 server with Mono JIT compiler version 5.4.0.201 and MySql Connector/NET (latest version 8.0.13).

All is working great, except I can't delete devices based on push tokens from the Devices table via Connector/NET. Table definition is as follows:

CREATE TABLE `Devices` (
  `idDevices` int(11) NOT NULL AUTO_INCREMENT,
  `notificationId` varchar(160) DEFAULT NULL,
  `deviceId` varchar(64) NOT NULL,
  `isAndroid` tinyint(1) NOT NULL,
  `deviceModel` varchar(64) DEFAULT NULL,
  `appMajor` tinyint(4) DEFAULT NULL,
  `appMinor` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`idDevices`),
  UNIQUE KEY `isAndroid` (`isAndroid`,`deviceId`),
  UNIQUE KEY `notificationId_UNIQUE` (`isAndroid`,`notificationId`),
  KEY `appVersion` (`appMajor`,`appMinor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I run the DELETE statement from my Mono application, no exceptions occur, but the int value returned from ExecuteNonQuery() is always 0, and no rows have been deleted from the table. No errors or warnings are logged in mariadb.log (log_warning=4). If I copy the exact same SQL statement to MySql Workbench and execute the statement there, the rows are deleted as expected. If I create a PHP script and use PDO connection to the same db, it also works as expected. All three test cases use the same user (root). So it seems to be a problem specific to Connector/NET

            var tokenCount = 0;
            var sql = new StringBuilder ("DELETE FROM Devices WHERE isAndroid = 0 AND notificationId IN ('");

            while (...) {
                sql.Append (token);
                sql.Append ("','");
                tokenCount++;
            }

            sql.Length -= 2;
            sql.Append (')');

            using (var connection = new MySqlConnection (connectionString)) {
                connection.Open ();
                using (var command = new MySqlCommand (sql.ToString (), connection)) {
                    var affectedRows = command.ExecuteNonQuery ();
                    if (affectedRows != tokenCount) {
                        //Always logs affectedRows(0)
                        log.Warn ($"Remove tokenCount({tokenCount}) != affectedRows({affectedRows}). sql={sql}");
                    }
                }
            }

UPDATE: I enabled general_log and I see the correct db is initialized AND the statement is run - but the table is not changed! Output from log:

        3 Init DB   mydb
        3 Query DELETE FROM Devices WHERE isAndroid = 0 AND notificationId IN ('002B1C477DB4F20868D157A806DF70E05D61D3950562C03E092707CA9C5CCF23')

UPDATE #2: I tried changing the DELETE statement into a SELECT statement using the same WHERE clause, and lo and behold, no rows are returned. So it seems that the matching on notificationId is not working for some reason. Could it be an encoding issue? Why would it work in other clients but not my Connector/NET client?

Answer 1

OK, so I found the problem after hours of debugging. Turns out it has nothing to do with Connector/NET. The problem was in how I was constructing the tokens, I accidentally appended some characters which would not print in my log4net log, so the SQL seemed OK on the client side-- but the actual SQL string which was sent to the database server contained invalid tokens!