Error Modifying Column Type: Unsupported Modify Column

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 修改字段类型报错:Unsupported modify column

| username: 胡杨树旁

Original: person_month int(10) DEFAULT NULL COMMENT ‘Estimated person/month’,
ALTER TABLE test.biz_demand_assessment_info MODIFY COLUMN person_month decimal(10,2) NULL COMMENT ‘Estimated person/month’;
Error message: Unsupported modify column: type decimal(10,2) not match origin int(10)

The ALTER TABLE syntax in TiDB has the following limitations:

  • A single ALTER TABLE statement cannot perform multiple operations.
  • Currently, lossy changes are not supported, such as changing from BIGINT type to INT type.
  • Spatial data types are not supported.
    However, it does not mention that changing from int to decimal type is not supported. In version 7.1, modifying the data type can be done normally.
| username: Kongdom | Original post link

Lossy changes are not supported. Changing a 10-digit integer to an 8-digit integer with 2 decimal places is a lossy change.

You can change it to decimal(12,2), and it should not report an error.

| username: tidb菜鸟一只 | Original post link

Generally, changing int to decimal type involves creating a new table or column and then transferring the data over. There is no direct modification.

| username: Kongdom | Original post link

:flushed: Is there such a little tip? Why?

| username: 哈喽沃德 | Original post link

Add a new column, migrate the data, and then delete the old column. This approach results in a loss of precision.

| username: tidb菜鸟一只 | Original post link

Can you insert a 10-digit number and convert it to DECIMAL(10,2)?

| username: Kongdom | Original post link

:yum: This is a lossy change. Changing it to decimal(12,2) should eliminate the need for it, right?
I thought you were talking about a normal scenario~ I was just thinking, does changing a field type have to be this complicated~

| username: 胡杨树旁 | Original post link

Also reported this error

| username: linnana | Original post link

The original value of the int type should have a maximum decimal value exceeding 8 digits.

| username: linnana | Original post link

Run the query “select max(person_month) from tabname” to check the current maximum value.

| username: 胡杨树旁 | Original post link

I think the main reason is that the number of regions is too large. You can try to adjust the region-split-size and region-split-keys parameters to increase the size of each region, thereby reducing the number of regions.

| username: 胡杨树旁 | Original post link

The maximum value is 231, so there shouldn’t be any precision loss. It can be modified in version 7.1.

| username: linnana | Original post link

Version 4 probably does not support converting int type to decimal.

| username: linnana | Original post link

Add a new decimal field and try updating the data.

| username: Kongdom | Original post link

:flushed: It seems from this description that int type columns cannot be modified. Strange, strange~

| username: Kongdom | Original post link

How about trying decimal(16,4)?

I can change it in v6.5.2.
image

| username: Kongdom | Original post link

It seems to be supported starting from version 5.1. Those who have the relevant version environment can test and verify it~

| username: TiDB_C罗 | Original post link

It will not check your data line by line, because checking whether the type is safe is not a very standard way of making changes.

| username: wangccsy | Original post link

It should be caused by precision issues.

| username: 胡杨树旁 | Original post link

It is probably not supported for modification. I will try it in a 5.1* environment.