| Bug #72061 | cannot import mysqldump of table partitions if the table name is relatively long | ||
|---|---|---|---|
| Submitted: | 17 Mar 2014 15:15 | Modified: | 2 Feb 2017 3:57 |
| Reporter: | harry sheng | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.6.14, 5.6.18 | OS: | Any (windows, linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysqldump, partition | ||
[17 Mar 2014 15:18]
harry sheng
Error message from importing the dump: ERROR 1062 (23000) at line 6257: Duplicate entry 'indosat-completeserviceegresspacketoctetslogrecorddaily#P#Monthl' for key 'PRIMARY' Please see the related INSERT statement from the dump file in the attached file.
[17 Mar 2014 15:20]
harry sheng
The INSERT statement that rejects the long partition name
Attachment: import_partitioned_table.sql (application/octet-stream, text), 65.71 KiB.
[17 Mar 2014 16:21]
harry sheng
My original mysqldump used the "--all-databases" option.
If I mysqldump the "indosat" schema only with option "--databases indosat", then I can import this dump without problem.
mysql> desc mysql.innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(64) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| n_rows | bigint(20) unsigned | NO | | NULL | |
| clustered_index_size | bigint(20) unsigned | NO | | NULL | |
| sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL | |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.01 sec)
mysql> select database_name, table_name, n_rows from mysql.innodb_table_stats where length(table_name) > 64;
+---------------+-----------------------------------------------------------------------+--------+
| database_name | table_name | n_rows |
+---------------+-----------------------------------------------------------------------+--------+
| indosat | completeserviceegresspacketoctetslogrecorddaily#p#monthly_2013_04_01 | 0 |
| indosat | completeserviceegresspacketoctetslogrecorddaily#p#monthly_2013_05_01 | 0 |
| indosat | completeserviceegresspacketoctetslogrecorddaily#p#monthly_2013_06_01 | 0 |
| indosat | completeserviceegresspacketoctetslogrecorddaily#p#monthly_2013_07_01 | 0 |
| indosat | completeserviceegresspacketoctetslogrecorddaily#p#monthly_2013_08_01 | 0 |
| indosat | completeserviceegresspacketoctetslogrecorddaily#p#monthly_2013_09_01 | 0 |
| indosat | completeserviceegresspacketoctetslogrecorddaily#p#monthly_2013_10_01 | 0 |
[18 Mar 2014 7:22]
MySQL Verification Team
Hello Harry, Thank you for the bug report. Verified as described. Thanks, Umesh
[18 Mar 2014 7:24]
MySQL Verification Team
// How to repeat
use test;
DROP TABLE IF EXISTS AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;
CREATE TABLE AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA (val INT)
PARTITION BY LIST(val)(
PARTITION A VALUES IN (1,3,5),
PARTITION B VALUES IN (2,4,6)
);
// 5.6.18 - affected
mysql> use test;
Database changed
mysql> DROP TABLE IF EXISTS AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA (val INT)
-> PARTITION BY LIST(val)(
-> PARTITION A VALUES IN (1,3,5),
-> PARTITION B VALUES IN (2,4,6)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show create table AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\G
*************************** 1. row ***************************
Table: AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Create Table: CREATE TABLE `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA` (
`val` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (val)
(PARTITION A VALUES IN (1,3,5) ENGINE = InnoDB,
PARTITION B VALUES IN (2,4,6) ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats where table_name like 'AA%'\G
*************************** 1. row ***************************
database_name: test
table_name: AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA#P#A
last_update: 2014-03-20 03:15:35
n_rows: 0
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 2. row ***************************
database_name: test
table_name: AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA#P#B
last_update: 2014-03-20 03:15:35
n_rows: 0
clustered_index_size: 1
sum_of_other_index_sizes: 0
2 rows in set (0.00 sec)
// Export all databases
[root@cluster-repo mysql-5.6.18]# bin/mysqldump -u root -p --all-databases > /tmp/all.sql
Enter password:
// Import the dump in another instance
[root@cluster-repo mysql-5.6.18]# bin/mysql -u root -p < /tmp/all.sql
Enter password:
ERROR 1062 (23000) at line 298: Duplicate entry 'test-AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' for key 'PRIMARY'
[31 Mar 2014 13:11]
Hartmut Holzgraefe
A quick look at the "AAAA.." test case shows that the table_name column now contains names with a length of 68 characters even though that column is defined as VARCHAR(64) only (UTF8 characters though, so the max. size is 192 bytes) So what happens on restore is that the names are now truncated to 64 characters, and that again leads to the restore error as the partition table names only differ in the last, 68ths, character After a first look at dict0stats.cc I assume that the InnoSQL procedure code that is used to update the tables internally is not unicode-aware and just treats table_name as a simple single-byte charater VARCHAR(192) column? Anyway, as table_name stores internal partition table names and not just the SQL level names 64 characters is too small for it, it needs to be large enough to keep up with the high level table name and the partition specific name suffixes ...
[31 Mar 2014 13:41]
Hartmut Holzgraefe
I was trying to test with a table name consisting of 64 Unicode characters with 3 byte UTF8 representation ( >= \U8000 ) to see whether the partition suffix can be handled at all in that case, but this isn't even possible as with the "new" (since 5.1) table name -> file name mapping each character is encoded as an @ followed by the unicode code point number as four hex digits (5 bytes total), so the effective table name character limit is 50 instead of 64 in that case (50x5 + LENGTH(".frm") = 254, with 255 being the file name length limit more or less all relevant file systems these days) ... separate bug report for this following soon ...
[2 Feb 2017 3:57]
Jon Stephens
Fixed in MySQL 8.0.0. Documented in the 8.0.0 changelog as follows:
A partitioned table whose table name and any partition name had
a combined length in excess of 61 characters could not be
imported from a backup created using mysqldump. When the table
also employed subpartitioning, then the combined length of the
table name, any partition name, and the name of any subpartition
of this partition could not exceed 57 characters without
triggering the same issue.
This was due to the fact that the internal
mysql.innodb_table_stats table allowed a maximum of 64
characters for the column used to store the table name, even
though InnoDB stores, for a partitioned or subpartitioned table,
a row in innodb_table_stats for each partition or subpartition
wherein the value actually used to represent the table name
follows the pattern 'table_name#P#partition_name' or
'table_name#P#partition_name#SP#subpartition_name', respectively.
This issue is fixed by changing the definition of
innodb_table_stats to accommodate the maximum combined length of
these attributes plus '#P#' and '#SP#' (199 characters).
Closed.
Description: I have partitioned tables with relatively long table name, e.g. table_name: completeserviceegresspacketoctetslogrecordhourly partition_name: completeserviceegresspacketoctetslogrecordhourly#P#Daily_2014_02_12 The database does not complain when the partition is created. But it will not accept the overly long partition name if I mysqldump the database and import it to another mysql instance. How to repeat: create a partitioned table with relatively long table name, mysqldump the database and import the dump to another mysql instance.