Bug #103765 Statement prepare hangs
Submitted: 21 May 9:28 Modified: 21 May 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

[21 May 9:28] Janos Ruszo
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".
[21 May 9:28] Janos Ruszo
Functioning Java test case

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

[21 May 10:41] MySQL Verification Team
Hello Janos Ruszo,

Thank you for the report and test case.

regards,
Umesh
[29 Jun 8:11] Alexander Soklakov
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
[29 Jun 8:13] Alexander Soklakov
COM_STMT_PREPARE and COM_STMT_PREPARE Response

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

[29 Jun 12:52] Roy Lyseng
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.
[3 Aug 10:40] Steinar Gunderson
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.