Пустой набор результатов при сравнении BINARY, хотя java.sql.PreparedStatement.setBytes
У меня есть сервер 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 / J 8.0.13
- Percona 5.7.24-26-log Percona Server (GPL), выпуск 26, версия c8fe767
Добавлен @RealSkeptic, но поскольку оба запроса в общем журнале одинаковы - означает ли это, что разница должна быть в метаданных запроса или в любом другом месте?
У вас могут быть скрытые символы в вашем общем журнале. Я вижу, что шестнадцатеричное значение из общего журнала не соответствует шестнадцатеричному числу из базы данных. Лучше всего предоставить минимальный воспроизводимый пример.
Да, я тоже нашел это - он содержит обратную косую черту, экранирующую некоторые байты, и я думаю, что это странно для двоичных данных (возможно, экранирование происходит при записи в общий журнал) ... но он работает с ними, я не знаю, где следует ли искать причину такого поведения.
Ответы 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 означает, что это не простые байты, а строка в некоторой кодировке (с некоторым сопоставлением).
Я действительно не знаю, почему драйвер устанавливает этот тип данных для байтов - этот вопрос остается для меня открытым.
Покажите код, в котором вы используете
setBytesиsetBinaryStream. Мы не знаем, где вы берете байты и как ими манипулируете.