MySQL Bugs: #73218: mysqlfabric hash sharding table shard_rangs lower

Bug #73218	mysqlfabric hash sharding table shard_rangs lower_bound may 32 byte length
Submitted:	7 Jul 2014 1:49	Modified:	16 Sep 2014 23:46
Reporter:	jack tom	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Fabric	Severity:	S1 (Critical)
Version:	Mysqlfabric 1.4.3	OS:	Linux
Assigned to:	Mats Kindahl	CPU Architecture:	Any
Tags:	mysqlfabric hash shard_ranges

Description:
mysqlfabric hash sharding table shard_rangs column lower_bound may 32 byte length

How to repeat:
first : like yours example 
add a shard: 
mysqlfabric sharding add_shard 1 group_id-1 --state=enabled 

In the cod: 
To add a shard, there has 2 ways 
inserting to shard_ranges table 
ONE: 
#Insert a HASH of keys and the server to which they belong. 

INSERT_HASH_SPECIFICATION = ( 
"INSERT INTO shard_ranges(" 
"shard_mapping_id, " 
"lower_bound, " 
"shard_id) " 
"VALUES(%s, UNHEX(MD5(%s)), %s)" 
) 
TWO: 
#Insert Split ranges. 
#NOTE: The split lower_bound does not need the md5 algorithm. 
INSERT_HASH_SPLIT_SPECIFICATION = ( 
"INSERT INTO shard_ranges(" 
"shard_mapping_id, " 
"lower_bound, " 
"shard_id) " 
"VALUES(%s, UNHEX(%s), %s)" 
) 

I found this: 
the lower_bound is unhex(md5('group_id-1')),the column lower_bound is 
`lower_bound` varbinary(16) NOT NULL. 

mysql> select shard_mapping_id, hex(lower_bound), shard_id from shard_ranges; 
+------------------+----------------------------------+----------+ 
| shard_mapping_id | hex(lower_bound) | shard_id | 
+------------------+----------------------------------+----------+ 
| 1 | 48B32E5AD3C6EC00A45578E0A34B092F | 1 | 
+------------------+----------------------------------+----------+ 

The second: 
add another shard_id,the command 
mysqlfabric sharding split_shard 1 group_id-2 
The code call: 
def get_upper_bound(lower_bound, shard_mapping_id, type, persister=None) 
The SQL is: 
max_query = "SELECT HEX(MD5(MAX(%s))) FROM %s" % \ 
( 
shard_mapping.column_name, 
shard_mapping.table_name 
) 
the proplem is md5 return 32 asscii，hex(md5('')) is 64 digital. 
then the upper_bound is 64 digital to insert. 
the insert sql to shard_ranges is 
#Insert Split ranges. 
#NOTE: The split lower_bound does not need the md5 algorithm. 
INSERT_HASH_SPLIT_SPECIFICATION = ( 
"INSERT INTO shard_ranges(" 
"shard_mapping_id, " 
"lower_bound, " 
"shard_id) " 
"VALUES(%s, UNHEX(%s), %s)" 
) 
The unhex(hex(md5(''))) return 32 length,but the table shard_range lower_bound column length is 16. 
my error is : 
/usr/local/bin/mysqlfabric sharding split_shard 1 group_id-2 
Procedure : 
{ uuid = e01e08d4-8193-4761-b4cf-6f997bd722b4, 
finished = True, 
success = False, 
return = DatabaseError: ("Command (INSERT INTO shard_ranges(shard_mapping_id, lower_bound, shard_id) VALUES(%s, UNHEX(%s), %s), (1, '1a1ab19a1c31b1b2993299321bb33132570a2fde1d15109903c66e0b82579db2', 3)) failed: 1406 (22001): Data too long for column 'lower_bound' at row 1", 1406), 
activities = 
} 

I have been alter the table shard_range's character to latin1 or utf8. it still 

now my question is: 
1. why the first shard's lower_bound is the group id 
2. lower_bound is 16 length is right or my test has errors? 

thanks

Suggested fix:
alter table shard_ranges modify lower_bound varbriary(32) not null;

But I found the data in the shard server not average.

@staticmethod
    def add(shard_mapping_id, shard_id, persister=None):
        """Add the HASH shard specification. This represents a single instance
        of a shard specification that maps a key HASH to a server.

        :param shard_mapping_id: The unique identification for a shard mapping.
        :param shard_id: An unique identification, a logical representation
                        for a shard of a particular table.
        """
        shard = Shards.fetch(shard_id)
        persister.exec_stmt(
            HashShardingSpecification.INSERT_HASH_SPECIFICATION, {
                "params":(
                    shard_mapping_id,
                    shard.group_id,
                    shard_id
                )
            }
        )

This is the the code, the lower_bound is group_id.
why is group_id ?
I think is may the shard's min value.

Thank you for the bug report! Verified as described.

Fixed as of the upcoming MySQL Utilities / Fabric 1.5.2 release, and here's the changelog entry:

Fabric could return an incorrect "lower_bound" length, depending on the
character set.

Thank you for the bug report.