Description:
We have several queries which had been working up through version 5.6.23 (in Amazon RDS). When the database was upgraded to 5.6.34, the queries returned incorrect results/errors.
Not sure whether this may be related to https://bugs.mysql.com/bug.php?id=82495, as the queries do use a combination of AES and LOWER.
We restored the database back to the older version, but RDS is due to be upgraded again in July, so any workaround suggestions would be appreciated.
How to repeat:
Create table:
DROP TABLE IF EXISTS `cdl_fail`;
CREATE TABLE `cdl_fail` (
`GUID` varchar(45) NOT NULL COMMENT 'Device GUID',
`Client_ID` varchar(40) NOT NULL COMMENT 'ID of the Client using this lookup',
`lookup_type` char(3) NOT NULL DEFAULT '' COMMENT 'The Type of lookup being used',
`lookup_value` varbinary(128) NOT NULL COMMENT 'The value assigned to this type',
`pTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update Timestamp',
PRIMARY KEY (`Client_ID`,`lookup_type`,`GUID`),
UNIQUE KEY `Unique_Value` (`Client_ID`,`lookup_type`,`lookup_value`),
KEY `idx1_client_device_lookup` (`Client_ID`,`GUID`),
KEY `idx2_client_device_lookup` (`GUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Device Lookup table for client specific identifiers';
-- Insert a row
INSERT INTO cdl_fail (GUID, Client_ID, lookup_type, lookup_value) VALUES
('272ab204-9e9c-fe47-1574-f4fe3v510c7aa7c3d86f', '3400', 'tel', AES_ENCRYPT('5859431164', 'H67E8L8CP34F4'));
-- Select the row - Works in 5.6.23, fails in 5.6.34 and 5.7 (returns nothing)
SELECT
*,
TRIM(CAST(AES_DECRYPT(lookup_value, 'H67E8L8CP34F4') AS CHAR)) as lookup_value
FROM cdl_fail
WHERE
lookup_value = AES_ENCRYPT(LOWER('5859431164'), 'H67E8L8CP34F4') AND client_id = '3400'
AND lookup_type = 'tel'
;
-- Select with COLLATE - works in all versions
SELECT
*,
TRIM(CAST(AES_DECRYPT(lookup_value, 'H67E8L8CP34F4') AS CHAR)) as lookup_value
FROM cdl_fail
WHERE
lookup_value = AES_ENCRYPT(LOWER('5859431164'), 'H67E8L8CP34F4') AND client_id = '3400'
AND lookup_type = 'tel' COLLATE latin1_german2_ci
;
-- Remove lookup_type check - works
SELECT
*,
TRIM(CAST(AES_DECRYPT(lookup_value, 'H67E8L8CP34F4') AS CHAR)) as lookup_value
FROM cdl_fail
WHERE
lookup_value = AES_ENCRYPT(LOWER('5859431164'), 'H67E8L8CP34F4') AND client_id = '3400'
;
-- Remove 'LOWER' (include lookup_type check) - works
SELECT
*,
TRIM(CAST(AES_DECRYPT(lookup_value, 'H67E8L8CP34F4') AS CHAR)) as lookup_value
FROM cdl_fail
WHERE
lookup_value = AES_ENCRYPT('5859431164', 'H67E8L8CP34F4') AND client_id = '3400'
AND lookup_type = 'tel'
;
-- INSERT with 'LOWER' and 'TRIM' - works in 5.6.23, fails in 5.6.34 and 5.7 (returns error "Column 'lookup_value' cannot be null")
INSERT INTO cdl_fail (GUID, Client_ID, lookup_type, lookup_value) VALUES
('272ab204-9e9c-fe47-1574-f4fe3v510c7aa7c3d86f', '3400', 'mac', AES_ENCRYPT(LOWER(TRIM('02:00:00:00:00:00')), 'H67E8L8CP34F4'))
;
-- INSERT with 'LOWER' - no 'TRIM' - works in 5.6.23, fails in 5.6.34 and 5.7 (returns error "Column 'lookup_value' cannot be null")
INSERT INTO cdl_fail (GUID, Client_ID, lookup_type, lookup_value) VALUES
('272ab204-9e9c-fe47-1574-f4fe3v510c7aa7c3d86f', '3400', 'mac', AES_ENCRYPT(LOWER('02:00:00:00:00:00'), 'H67E8L8CP34F4'))
;
