[MDEV-31072] InnoDB is USING HASH and Optimizer is confused - Jira

XML

Word

Printable

Details

Type: Task
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: N/A
Component/s: Documentation, Optimizer
Labels:
- UNIQUE
- documentation
- hash
- index
- optimizer

Description

MariaDB KB states: "HASH is only supported by the MEMORY storage engine."

Sources:

When we create a table like this:

CREATE TABLE `aaa_device` (

  `device_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `device_token` varchar(750) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,

  `device_app` varchar(24) NOT NULL,

  `device_app_version` smallint(5) unsigned DEFAULT NULL,

  `device_platform` varchar(16) NOT NULL,

  `device_user_agent` varchar(512) DEFAULT NULL,

  `device_push_enabled` tinyint(1) unsigned DEFAULT NULL,

  `device_name` varchar(64) DEFAULT NULL,

  `pg_id` smallint(5) unsigned DEFAULT NULL,

  `device_user_last` int(10) unsigned DEFAULT NULL,

  `device_user_last_logged_in` int(10) unsigned DEFAULT NULL,

  `device_cookie` text DEFAULT NULL,

  `device_session_id` varchar(32) DEFAULT NULL,

  `device_insert` datetime NOT NULL DEFAULT current_timestamp(),

  `device_update` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  `device_restart` datetime DEFAULT NULL,

  `device_use` datetime NOT NULL DEFAULT current_timestamp(),

  `device_cookie_len` int(10) unsigned GENERATED ALWAYS AS (octet_length(`device_cookie`)) STORED,

  PRIMARY KEY (`device_id`),

  UNIQUE KEY `ucTokenApp` (`device_token`,`device_app`),

  KEY `device_app_device_app_version` (`device_app`,`device_app_version`),

  KEY `device_user_last` (`device_user_last`),

  KEY `device_user_last_logged_in` (`device_user_last_logged_in`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

MariaDB converts this to this:

SHOW CREATE TABLE aaa_device\G

CREATE TABLE `aaa_device` (

  `device_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `device_token` varchar(750) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,

  `device_app` varchar(24) NOT NULL,

  `device_app_version` smallint(5) unsigned DEFAULT NULL,

  `device_platform` varchar(16) NOT NULL,

  `device_user_agent` varchar(512) DEFAULT NULL,

  `device_push_enabled` tinyint(1) unsigned DEFAULT NULL,

  `device_name` varchar(64) DEFAULT NULL,

  `pg_id` smallint(5) unsigned DEFAULT NULL,

  `device_user_last` int(10) unsigned DEFAULT NULL,

  `device_user_last_logged_in` int(10) unsigned DEFAULT NULL,

  `device_cookie` text DEFAULT NULL,

  `device_session_id` varchar(32) DEFAULT NULL,

  `device_insert` datetime NOT NULL DEFAULT current_timestamp(),

  `device_update` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  `device_restart` datetime DEFAULT NULL,

  `device_use` datetime NOT NULL DEFAULT current_timestamp(),

  `device_cookie_len` int(10) unsigned GENERATED ALWAYS AS (octet_length(`device_cookie`)) STORED,

  PRIMARY KEY (`device_id`),

  UNIQUE KEY `ucTokenApp` (`device_token`,`device_app`) USING HASH,

  KEY `device_app_device_app_version` (`device_app`,`device_app_version`),

  KEY `device_user_last` (`device_user_last`),

  KEY `device_user_last_logged_in` (`device_user_last_logged_in`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

Note: UNIQUE KEY `ucTokenApp` (`device_token`,`device_app`) USING HASH,

So far so good, I can live with it... But: After inserting some data:

INSERT INTO `aaa_device` (`device_id`, `device_token`, `device_app`, `device_app_version`, `device_platform`, `device_user_agent`, `device_push_enabled`, `device_name`, `pg_id`, `device_user_last`, `device_user_last_logged_in`, `device_cookie`, `device_session_id`, `device_insert`, `device_update`, `device_restart`, `device_use`, `device_cookie_len`)

VALUES

  (5,  'cpM6TlH5-VI:APA91bG5gEyiw8psmZGQzYCPFgYQU0TNsCFb5jeivYkLybTlL5p6844yrqBZVsGZENsSxTvZwb9MyTItT5oHz0eCWjvC42oixOm6HIKg-IPPi-Mt1bJRTdd0p-0FVKzppC3BTAvqITzg', 'pcco', NULL, 'ios',  NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   '2020-02-19 13:28:25',  '2022-07-20 19:05:49',  NULL,   '2022-07-16 14:39:04',  NULL)

, (8,  'dJMAAFywpGk:APA91bELVRD74PdZG5Vd8flXGeE0teS3iEzTL3vaW17S4CCBSeNQpb1CPU32s7fGfa35KLEQEuq1-W8SWssghQu26u5uDPrHiMGzHjisnGKZp_7l5F3q_uMAtZEjqUioHiGCFXws-r0v', 'pcco', 202,  'ios',  'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148',       1,      'Apple-iPad5,3',        679,    43204,  43204,  '{\"auth\":{\"authBy\":\"login\",\"login\":\"hans.meier@t-online.de\",\"password\":\"402c9fe50e9e3ff6dc01742df5fff05c\"},\"club\":\"129\",\"device_id\":\"8\",\"lang\":\"de\",\"visitor_uid\":\"79f3eca97c2ad28230521288429f8bf8\"}',       'vf3cvvBb1hZmDx7sVaDl2wUPYV',   '2020-02-19 13:28:53',  '2023-04-16 08:31:36',  '2023-04-16 08:30:50',  '2023-04-16 08:31:36',  200)

, (11, 'dQHXAXyQFwM:APA91bG7pY8qOtHdLTKJ41uhi87mwBEy2RybQyHy1nLNyGAW7oBWE4TZZ2HHhLabLxtRHmk1CBMibLOGeKp0M1sGGB-BkCqFdZ1UImvxcI3BXFuUO4V3PrN8M2VdFeKHkPYnkXeypoJC', 'pcco', NULL, 'ios',  NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   '2020-02-19 13:29:00',  '2022-07-20 19:05:49',  NULL,   '2022-07-16 14:39:04',  NULL)

, (14, 'edtFfXjgpRk:APA91bEKHLjwAW2RWb7dMwWgT3eRlmyaWYMlbXLhLrlkhFj2dgh-hOp3lb1nLMgEEqXwV6k_zb5qduMQcZoSLDo-NmYjvdl2CIeyHzElhBRbmGo5RE-WFH5oL6JacgJ-wZEhEWlOzjaU', 'pcco', NULL, 'android',      NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   '2020-02-19 13:29:10',  '2022-07-20 19:05:49',  NULL,   '2022-07-16 14:39:04',  NULL)

, (17, 'evOBMlbektw:APA91bFfkMTua4aYNcOxNpjmVCq9VCMnqvJjC5bll5cxpQR9MT7BKdMeOYXj1kBYYpPLEUgRZcmXfD9kguu0kvnybmbC4jaW86s59ynlKXAuKjT3eGQ2Pk7pWc_gs6eIrFAyYAkf8_wL', 'pcco', NULL, 'android',      NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   '2020-02-19 13:29:11',  '2022-07-20 19:05:49',  NULL,   '2022-07-16 14:39:04',  NULL)

, (20, 'eGx98rjhVa4:APA91bEdCz9XGtmhasDlXnUilCFiBE2KHyc6oAHqvAITAaDd-e-ntnlM5rrqo7KY7BZapBh26rsWW_zy3T0wIwf2nAnEEoyrXXrDoLogBZ0GcBvSJi9mdEKfWot4XusqYZv11JI_ldah', 'pcco', NULL, 'android',      NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   NULL,   '2020-02-19 13:29:12',  '2022-07-20 19:05:49',  NULL,   '2022-07-16 14:39:04',  NULL)

, (23, 'f5cBwRzHlcE:APA91bFsms_dQ1XY5NjjrTq-y0nzqArkR7u7mZ02IA5fejxsDWmoTZbclkzcRaNzRO3LEqMfQXGJZG-tswAnOKtgk2If73sAqezXOA_azqem4JjtSwONjYOYeEHqdbzeiQBuqJoladXS', 'pcco', 202,  'ios',  'Mozilla/5.0 (iPhone; CPU iPhone OS 15_7_3 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148',      1,      'Apple-iPhone8,4',      679,    433213, 433213, '{\"auth\":{\"authBy\":\"login\",\"login\":\"fritz.müller@web.de\",\"password\":\"6469e8701df05ecd7736343296a383a8\"},\"club\":\"694\",\"device_id\":\"23\",\"lang\":\"de\",\"visitor_uid\":\"b7519c67ad0320908d418c5a05ef3dfb\"}',    'ulBUTArKfWqKOwUWeD7lEGC4nW',   '2020-02-19 13:29:18',  '2023-02-14 17:01:02',  '2023-02-14 17:00:26',  '2023-02-14 17:01:02',  187)

The Query Execution Plan looks completely wrong:

EXPLAIN

SELECT * FROM aaa_device

 WHERE device_app = 'pcco'

   AND device_token = 'f5cBwRzHlcE:APA91bFsms_dQ1XY5NjjrTq-y0nzqArkR7u7mZ02IA5fejxsDWmoTZbclkzcRaNzRO3LEqMfQXGJZG-tswAnOKtgk2If73sAqezXOA_azqem4JjtSwONjYOYeEHqdbzeiQBuqJoladXS'

+------+-------------+------------+------+------------------------------------------+------+---------+------+------+-------------+

| id   | select_type | table      | type | possible_keys                            | key  | key_len | ref  | rows | Extra       |

+------+-------------+------------+------+------------------------------------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | aaa_device | ALL  | ucTokenApp,device_app_device_app_version | NULL | NULL    | NULL | 7    | Using where |

+------+-------------+------------+------+------------------------------------------+------+---------+------+------+-------------+

After changing the field length of device_token from 750 to 700 VARCHAR:

ALTER TABLE aaa_device MODIFY COLUMN `device_token` varchar(700) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

The USING HASH disappeared! And the Optimizer did the right choice:

EXPLAIN

SELECT * FROM aaa_device

 WHERE device_app = 'pcco'

   AND device_token = 'f5cBwRzHlcE:APA91bFsms_dQ1XY5NjjrTq-y0nzqArkR7u7mZ02IA5fejxsDWmoTZbclkzcRaNzRO3LEqMfQXGJZG-tswAnOKtgk2If73sAqezXOA_azqem4JjtSwONjYOYeEHqdbzeiQBuqJoladXS'

+------+-------------+------------+-------+------------------------------------------+------------+---------+-------------+------+-------+

| id   | select_type | table      | type  | possible_keys                            | key        | key_len | ref         | rows | Extra |

+------+-------------+------------+-------+------------------------------------------+------------+---------+-------------+------+-------+

|    1 | SIMPLE      | aaa_device | const | ucTokenApp,device_app_device_app_version | ucTokenApp | 2900    | const,const | 1    |       |

+------+-------------+------------+-------+------------------------------------------+------------+---------+-------------+------+-------+

So either there is something wrong in the documentation and/or the Optimizer does something really odd...

Tested it reproducible with 10.11.2 and 10.6.11.

Bonus information:

SQL> INSERT INTO aaa_device (device_id, device_token, device_app, device_platform)  select device_id, device_token, device_app, device_platform from test.aaa_device limit 100;

ERROR 1364 (HY000): Field 'DB_ROW_HASH_1' doesn't have a default value

Attachments

Issue Links

relates to

MDEV-371 Unique indexes for blobs

Closed

MDEV-13445 Hash-Index Type for InnoDB

Open

MDEV-22756 SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value

Closed

links to

SO example of same problem

Activity

People

Assignee:: Ian Gilfillan

Reporter:: Oli Sennhauser

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-04-18 08:24

Updated:: 2024-01-29 02:39

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.