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:15]
harry sheng
[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.