MySQL Bugs: #103765: Statement prepare hangs

Bug #103765	Statement prepare hangs
Submitted:	21 May 2021 9:28	Modified:	21 May 2021 10:41
Reporter:	Janos Ruszo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	Java MySQL Driver 8.0.24, 8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After upgrading to 8.0.23, the application started to have issues with preparing the statement.
When the statement is sent for prepare the prepare never finishes and no response is received from MySQL.

During testing locally, every version was working properly until 8.0.22, but 8.0.22 and newer versions seem to be affected

The current workaround is to disable prepared statements, which worked.

How to repeat:
Create the following schemas:

CREATE TABLE `Receipt` (
                           `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'идентификатор чека',
                           `di` int DEFAULT NULL,
                           `printedNo` int DEFAULT NULL,
                           `devId` int DEFAULT NULL,
                           `isFiscal` bit(1) DEFAULT NULL,
                           `srcUrl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `serialNum` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `TSCcreated` datetime DEFAULT NULL,
                           `TSSreceived` datetime DEFAULT NULL,
                           `amountTotal` bigint DEFAULT NULL,
                           `amountCash` bigint DEFAULT NULL,
                           `amountCard` bigint DEFAULT NULL,
                           `type` enum('SALE','REFUND','TAKEAWAY','CONTRIBUTION') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `outletId` int DEFAULT NULL,
                           `amountDiscount` bigint DEFAULT NULL,
                           `comment` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `merchId` int DEFAULT NULL,
                           `amountBonus` bigint DEFAULT NULL,
                           `sendType` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `TSCcreatedOffset` bigint DEFAULT NULL,
                           `parentReceiptId` bigint DEFAULT NULL COMMENT 'идентификатор родительского чека',
                           `lang` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `cashRegisterId` int DEFAULT NULL,
                           `sessionOverride` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `isCashRegisterDeleted` bit(1) NOT NULL DEFAULT b'0',
                           `shiftId` int DEFAULT '0',
                           `fiscalNo` int DEFAULT NULL COMMENT 'номер чека из фискального принтера',
                           `amountChange` bigint DEFAULT '0',
                           `amountTips` bigint DEFAULT '0',
                           `openReceiptOrderNo` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `diningOptionId` int DEFAULT NULL,
                           `syncId` bigint DEFAULT NULL,
                           `cancelled` datetime DEFAULT NULL COMMENT 'Дата отмены чека',
                           `merchantCancelled` int DEFAULT NULL COMMENT 'Сотрудник, который отменил чек',
                           `appName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Имя приложения (API токена) создавшего чек',
                           PRIMARY KEY (`id`),
                           UNIQUE KEY `id_UNIQUE` (`id`),
                           KEY `fk_Receipt_Device_idx` (`devId`),
                           KEY `fk_Receipt_Outlet_idx` (`outletId`),
                           KEY `fk_Receipt_Merchant` (`merchId`),
                           KEY `idx_ts_outlet` (`TSCcreated`,`outletId`),
                           KEY `parentReceiptId` (`parentReceiptId`),
                           KEY `fk_Receipt_CashRegister_idx` (`cashRegisterId`),
                           KEY `Receipt` (`shiftId`),
                           KEY `idx_Outlet_Created` (`outletId`,`TSCcreated`),
                           KEY `fk_dining_option` (`diningOptionId`),
                           KEY `key_cashRegister_printedNo_index` (`cashRegisterId`,`printedNo`),
                           KEY `Receipt_Merchant_id_fk` (`merchantCancelled`),
                           KEY `idx_outlet_cancelled` (`cashRegisterId`,`cancelled`),
                           KEY `idx_Outlet_received` (`outletId`,`TSSreceived`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ReceiptPayment` (
                                  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'идентификатор платежа',
                                  `receiptId` bigint DEFAULT NULL COMMENT 'идентификатор чека',
                                  `paymentType` enum('CASH','CARD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'тип оплаты платежа',
                                  `paymentTotal` bigint DEFAULT NULL COMMENT 'сумма платежа',
                                  `amountChange` bigint DEFAULT NULL COMMENT 'сдача',
                                  `sendType` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `created` datetime DEFAULT NULL,
                                  `type` enum('FULL','PARTIAL') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `paymentTypeId` int DEFAULT NULL,
                                  `paymentRefId` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `transportKey` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `amountTips` bigint DEFAULT '0',
                                  `cardType` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `cardNumber` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `transactionNo` int DEFAULT NULL,
                                  `parentPaymentId` bigint DEFAULT NULL COMMENT 'идентификатор исходного платежа в чеке возврата',
                                  `emvAID` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `emvAppLabel` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `emvAppName` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `emvPinStatement` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `emvTVR` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `emvTSI` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `signatureData` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `signaturePresent` bit(1) DEFAULT NULL,
                                  `roundingAmount` bigint NOT NULL DEFAULT '0' COMMENT 'Сумма округления',
                                  PRIMARY KEY (`id`),
                                  UNIQUE KEY `id_UNIQUE` (`id`),
                                  KEY `fk_ReceiptPayment_PaymentType_idx` (`paymentTypeId`),
                                  KEY `fk_ReceiptPayment_Receipt_idx` (`receiptId`),
                                  KEY `fk_ReceiptPayment_ReceiptPayment` (`parentPaymentId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Таблица содержит платежи по чекам';

CREATE TABLE `RefIdToken` (
                              `id` bigint NOT NULL AUTO_INCREMENT,
                              `type` enum('CASH','NONINTEGRATEDCARD','CHEQUE','VANTIV','CAYENE','COINEY','IZETTLE','SUMUP','TYRO','NICEPAY','SMARTPAY','OTHER','YOCO','EZETAP','PAYGATE','FIRSTDATA','SOFTBANK','KICC','MERCADOPAGO','ONEPAY') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `refId` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `authCode` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `receiptPaymentId` bigint DEFAULT NULL COMMENT 'идентификатор платежа, которому соответствует транзакция',
                              `created` datetime DEFAULT NULL COMMENT 'дата создания транзакции',
                              `terminalId` int DEFAULT NULL COMMENT 'идентификатор записи платежного терминала из таблицы PaymentTerminal',
                              `acqRefData` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `refNo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `processData` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `command` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `entryMethod` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `amount` bigint DEFAULT NULL COMMENT 'Сумма транзакции',
                              `ownerId` int DEFAULT NULL,
                              `receiptType` enum('SALE','REFUND') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `amountSurcharge` bigint DEFAULT NULL,
                              `transactionInfo` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Поле с информации транзакции платежной системы',
                              `emvMerchantId` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `emvTerminalId` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `catId` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `businessNo` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `acquirer` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `installments` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `emvRRN` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                              `refNo2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'поле для чеков возврата платежки Ezetap',
                              `paymentData` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'данные о типах оплат в чеке',
                              PRIMARY KEY (`id`),
                              UNIQUE KEY `id_UNIQUE` (`id`),
                              UNIQUE KEY `RefIdToken_refid_owner_uindex` (`type`,`refId`,`receiptType`,`ownerId`),
                              KEY `fk_RefIdToken_PaymentTerminal_idx` (`terminalId`),
                              KEY `fk_RefIdToken_ReceiptPayment_idx` (`receiptPaymentId`),
                              KEY `idx_payment_ref` (`refId`,`receiptPaymentId`),
                              KEY `RefIdToken_owner_type_index` (`ownerId`,`receiptType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `PaymentType` (
                               `id` int NOT NULL AUTO_INCREMENT,
                               `ownerId` int DEFAULT NULL,
                               `method` enum('CASH','NONINTEGRATEDCARD','CHEQUE','VANTIV','CAYAN','COINEY','IZETTLE','SUMUP','TYRO','SMARTPAY','YOCO','NICEPAY','CASH_ROUNDING','OTHER','EZETAP','PAYGATE','FIRSTDATA','SOFTBANK','KICC','MERCADOPAGO','ONEPAY') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                               `name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                               `created` datetime DEFAULT NULL,
                               `changed` datetime DEFAULT NULL,
                               `deleted` datetime DEFAULT NULL,
                               `tipsConfig` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                               `tipsEnable` bit(1) DEFAULT NULL,
                               `tipsCustomEnable` bit(1) DEFAULT b'0',
                               `authToken` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                               `accountEmail` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                               `refreshToken` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                               `tokenExpired` datetime DEFAULT NULL,
                               `roundingValue` int NOT NULL DEFAULT '1' COMMENT 'Значение округления',
                               `roundingType` enum('MIDDLE_UP','MIDDLE_DOWN','ALWAYS_UP','ALWAYS_DOWN') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Алгоритм округления',
                               `uuid` binary(16) DEFAULT NULL,
                               `onePayMerchantId` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'id сотрудника в системе onepay',
                               `onePayPublicKey` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'публичный код для верификации ответа от onepay при транзакции',
                               PRIMARY KEY (`id`),
                               UNIQUE KEY `id_UNIQUE` (`id`),
                               KEY `fk_PaymentType_Owner_idx` (`ownerId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Run the java code attached as:
mvn exec:java -Dexec.args="hostname dbname user password".

Functioning Java test case

Attachment: mysql_bug_reproducing.zip (application/zip, text), 5.22 KiB.

Hello Janos Ruszo,

Thank you for the report and test case.

regards,
Umesh

Hi Janos,

First of all, it's not reproducible with default MySQL 8.0 server mode. For prepare() request the server replies with the error "Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cjtest_8_0.Receipt.TSCcreated' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by". After that c/J fallbacks to the client-side prepared statement and prepare() succeeds.

The ONLY_FULL_GROUP_BY is a default mode starting from MySQL 5.7, so I guess you're disabling it intentionally. Better you could should refactor your query according to https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html to make it compliant with the recent MySQL versions.

Regarding the hang, it's caused by the inconsistent server reply for the prepare request. I'll attach the protocol packets below, but in short, COM_STMT_PREPARE Response has num_params=21 but contains only 20 Parameter Definition Blocks. Connector/J can do nothing with that and it doesn't depend on c/J version, the same hang happens with c/J 8.0.21. I'll reassign the bug report to server team.

Thanks,
Alex

COM_STMT_PREPARE and COM_STMT_PREPARE Response

Attachment: bug103765_on_wire.txt (text/plain), 11.72 KiB.

I also suggest a couple more rewrites:

In this construct

  sum(case when `Receipt`.`type` = ? then ? else ? end) as `Transactions`

MySQL will now do type determination at preparation time, which means we cannot
deduce the type of the CASE statement with any certainty.

In worst case, the parameters supplied at execution time are incompatible with
the decision made at prepare time and an automatic reprepare will occur.

it is therefore suggested to rewrite the CAST with an explicit CAST around
at least one of the parameters of the CAST, like

  sum(case when `Receipt`.`type` = ? then CAST(? AS SIGNED) else ? end) as `Transactions`

The GROUP BY clause

  group by `ReceiptPayment`.`paymentTypeId`,`Receipt`.`outletId`, ?

looks weird, with a dynamic parameter in the GROUP BY list.
It is suggested to replace it with a column name.

Hi,

Is there any way of reproducing this bug without invoking a separate Java
program? We're going to need a way to do automated regression testing anyway,
and that needs to fit into mysqltest.

We see the same issue on MySQL server 8.0.31 with a prepared statement that was generated with a dynamic parameter to ORDER BY. Number of parameters in the response to prepare statement was 1 more than number of parameter definition blocks, causing Connector/J to hang forever on socket read.

We worked around the issue my making sure that we don't generate 'ORDER BY ?' in our query, but this is clearly a bug in the server. It should either reject the prepare statement request, or return a parameter definition block.

I am only able to reproduce the issue with a client library. Running e.g. 

  PREPARE my_statement FROM 'SELECT * FROM mysql.user ORDER BY ?'

in the MySQL console does not fail.

This is likely a duplicate of an internal bug that was fixed in 8.0.38,
and in 8.4.1.