Пустой набор результатов при сравнении BINARY, хотя java.sql.PreparedStatement.setBytes

Question

У меня есть сервер Percona Mysql и клиент Java с пользовательским ORM. В БД у меня есть таблица:

CREATE TABLE `PlayerSecret` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `secret` binary(16) NOT NULL,
  `player_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `PlayerSecret_secret_unique` (`secret`),
  KEY `PlayerSecret_player_id` (`player_id`)
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

Я нашел этот запрос SELECT PlayerSecret.player_id FROM PlayerSecret WHERE PlayerSecret.secret = ?возвращает пустой набор результатов, если параметр предоставлен java.sql.PreparedStatement#setBytes метод, и работает как ожидалось, хотя java.sql.PreparedStatement#setBinaryStream, Я включил общий журнал mysql и обнаружил, что в этом журнале оба запроса одинаковы, я проверил это в шестнадцатеричном режиме.

В общем логи это выглядит так:

SELECT PlayerSecret.player_id FROM PlayerSecret WHERE PlayerSecret.secret = '<96>R\Ø8üõA\í¤Z´^E\Ô\ÊÁ\Ö'

Параметр запроса из общего журнала в шестнадцатеричном режиме:2796 525c d838 fcf5 415c eda4 5ab4 055c d45c cac1 5cd6 27

Значение в базе данных:

mysql> select hex(secret) from PlayerSecret where id=109;
+----------------------------------+
| hex(secret)                      |
+----------------------------------+
| 9652D838FCF541EDA45AB405D4CAC1D6 |
+----------------------------------+
1 row in set (0.00 sec)

Проблема в том, что мой ORM делает этот запрос, хотя setBytes метод, я думаю, что это правильный путь для BINARY тип данных, но он не работает.

Часть my.cnf с настройками кодирования (возможно это имеет значение):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
general_log = on
general_log_file=/var/log/mysql/mysqld_general.log
require_secure_transport = ON
init-connect = SET collation_connection = utf8mb4_unicode_ci
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Java-код:

var uuid = UUID.fromString("9652d838-fcf5-41ed-a45a-b405d4cac1d6");
var array = ByteBuffer.allocate(16).putLong(uuid.getMostSignificantBits()).putLong(uuid.getLeastSignificantBits()).array();
// works
stmt.setBinaryStream(index, new ByteArrayInputStream(array));
// don't works
stmt.setBytes(index, array);

Я не могу понять, в чем разница между обоими случаями, и как это исправить для setBytes вариант. Может быть, кто-то может прояснить это или указать мне на важные части / места?

Мой env:

• Openjdk 11
• HicariCP 3.1.0
• MySQL, Connector / J 8.0.13
• Percona 5.7.24-26-log Percona Server (GPL), выпуск "26", редакция "c8fe767"

Answer 1

Наконец-то я понял это. Проблема была в character_set_client=utf8 вместо utf8mb4,
Этот запрос показывает разницу между ожидаемыми значениями и значениями реальных потоков (думаю, что это очень удобный запрос):

SELECT VARIABLE_NAME, gv.VARIABLE_VALUE 'Global', tv.VARIABLE_VALUE 'Thread value', THREAD_ID, PROCESSLIST_ID 
FROM performance_schema.global_variables gv   
  JOIN performance_schema.variables_by_thread tv USING (VARIABLE_NAME) 
  JOIN performance_schema.threads USING(THREAD_ID) 
WHERE gv.VARIABLE_VALUE <> tv.VARIABLE_VALUE ;

+-----------------------+--------------------+--------------------+-----------+----------------+
| VARIABLE_NAME         | Global             | Thread value       | THREAD_ID | PROCESSLIST_ID |
+-----------------------+--------------------+--------------------+-----------+----------------+
| autocommit            | ON                 | OFF                |        82 |             56 |
| character_set_client  | utf8mb4            | utf8               |        82 |             56 |
| character_set_results | utf8mb4            |                    |        82 |             56 |

Когда я заменил init-connect = SET collation_connection = utf8mb4_unicode_ci с init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci' в my.cnf проблема исчезла и запросы хотя setBytes начал работать как положено.

Почему это работает для setBinaryStream и не работает для setBytes - потому что в первом случае работает этот код com.mysql.cj.ServerPreparedQueryBindings#setBinaryStream(int, java.io.InputStream, int):

@Override
public void setBinaryStream(int parameterIndex, InputStream x, int length) {
    if (x == null) {
        setNull(parameterIndex);
    } else {
        ServerPreparedQueryBindValue binding = getBinding(parameterIndex, true);
        this.sendTypesToServer.compareAndSet(false, binding.resetToType(MysqlType.FIELD_TYPE_BLOB, this.numberOfExecutions));
        binding.value = x;
        binding.isLongData = true;
        binding.bindLength = this.useStreamLengthsInPrepStmts.getValue() ? length : -1;
    }
}

Важная часть здесь binding.resetToType(MysqlType.FIELD_TYPE_BLOB - водитель отмечает MySQL, что эти данные BLOB

И во втором случае com.mysql.cj.ServerPreparedQueryBindings#setBytes(int, byte[]) содержит:

@Override
public void setBytes(int parameterIndex, byte[] x) {
    if (x == null) {
        setNull(parameterIndex);
    } else {
        ServerPreparedQueryBindValue binding = getBinding(parameterIndex, false);
        this.sendTypesToServer.compareAndSet(false, binding.resetToType(MysqlType.FIELD_TYPE_VAR_STRING, this.numberOfExecutions));
        binding.value = x;
    }
}

MysqlType.FIELD_TYPE_VAR_STRING означает, что это не простые байты, а строка в некоторой кодировке (с некоторым сопоставлением). Я действительно не знаю, почему драйвер устанавливает этот тип данных для байтов - этот вопрос остается открытым для меня.