Bug #101984 partition by key distribute unbalance when PARTITIONS is even and some COLLATE

• Submitted: 13 Dec 2020 14:28
• Modified: 14 Dec 2020 6:52
• Reporter: y w
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 8.0.22, 5.7.32
• OS: CentOS
• CPU Architecture: x86
• Tags: partition

[13 Dec 2020 14:28] y w

Description:
Used PARTITION BY KEY to create partition table, when the KEY columns
PARTITIONS is even number and table CHARSET=utf8mb4 and
COLLATE=utf8mb4_unicode_ci or utf8mb4_general_ci, data distribute unbalance, when PARTITIONS is
odd number ,data uniform distribution. 

How to repeat:
First, create two partition tables.

mysql>CREATE TABLE `tp1` (
  `s1` char(32) NOT NULL,
  PRIMARY KEY (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
PARTITION BY KEY (s1)
PARTITIONS 10;

mysql>CREATE TABLE `tp2` (
  `s1` char(32) NOT NULL,
  PRIMARY KEY (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
PARTITION BY KEY (s1)
PARTITIONS 11 ;

Then, load data:

mysql>insert into tp1(s1)
SELECT
  @counter := @counter+1 AS counter 
FROM
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 600
  ) AS select1,
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 600
  ) AS select2,
  (
    SELECT 
      @counter := -1
    FROM
      DUAL
  ) AS select_counter
;

mysql>insert into tp2(s1)
SELECT
  @counter := @counter+1 AS counter 
FROM
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 600
  ) AS select1,
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 600
  ) AS select2,
  (
    SELECT 
      @counter := -1
    FROM
      DUAL
  ) AS select_counter
;

mysql>insert into tp2(s1)
SELECT
  @counter := @counter+1 AS counter 
FROM
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 600
  ) AS select1,
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 600
  ) AS select2,
  (
    SELECT 
      @counter := -1
    FROM
      DUAL
  ) AS select_counter
;

Then, check data distribute.

mysql> select
    -> partition_name,data_length from information_schema.partitions where
    -> table_schema='test' and table_name='tp1';
+----------------+-------------+
| partition_name | data_length |
+----------------+-------------+
| p0             |     8962048 |
| p1             |       98304 |
| p2             |     8962048 |
| p3             |      131072 |
| p4             |     8962048 |
| p5             |       98304 |
| p6             |     8962048 |
| p7             |      114688 |
| p8             |     8962048 |
| p9             |      114688 |
+----------------+-------------+
10 rows in set (0.00 sec)

mysql> select partition_name,data_length from information_schema.partitions where table_schema='test' and table_name='tp2';
+----------------+-------------+
| partition_name | data_length |
+----------------+-------------+
| p0             |       16384 |
| p1             |       16384 |
| p2             |       16384 |
| p3             |       16384 |
| p4             |       16384 |
| p5             |       16384 |
| p6             |       16384 |
| p7             |       16384 |
| p8             |       16384 |
| p9             |       16384 |
| p10            |       16384 |
+----------------+-------------+
11 rows in set (0.00 sec)

[14 Dec 2020 6:52] MySQL Verification Team

Hello!!

Thank you for the report and feedback.

regards,
Umesh