What Should I Do If an Error Occurs During the Migration of TINYINT Data from MySQL to DWS?

Symptom

During data migration from MySQL to DWS, error "ERROR:value '-1'is out of range for 8 b-bit integer" is reported.

Fault Analysis

1. Value -1 cannot be inserted into the destination. Check the field mapping at the destination.

   

2. Check the table creation statement.

3. According to the preceding figure, INT1 is the alias of the DWS field type TINYINT, so field mapping is correct. Check whether DWS TINYINT supports the range and why the error message indicates that -1 is not supported. According to the DWS field type description, DWS TINYINT supports the range [0,255] and does not support negative numbers. The TINYINT type of MySQL supports range [-128,127].

4. SMALLINT supports negative numbers. You are advised to use SMALLINT when creating tables at the destination.

Solution

1. The mapping field INT1 is the alias of the TINYINT type of DWS. The mapping is correct.
2. For DWS, the value of TINYINT ranges from 0 to 255. If the migration source is MySQL contains negative numbers such as –1, you are advised to use SMALLINT (whose value range is –32,768 to +32,767).

The value range of the TINYINT type of Hive and MySQL is [–128, 127]. The value range of the TINYINT type of DWS is [0,255].