Bug #103363 Too many columns on partitioned table makes database broken

• Submitted: 19 Apr 2021 8:21
• Modified: 18 May 2021 0:55
• Reporter: Albert Wang
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[19 Apr 2021 8:21] Albert Wang

Description:
Here's the steps to reproduce the issue, if the table is unpartitioned, adding too many columns will fail and table/database remains last working state.  

However, if the table is partitioned, add column query fails but also break the table and database, any further queries for this table would fail.

For reference, if step 5b is not executed, nothing broken, otherwise database and table are broken.

How to repeat:
Please download the files from: https://gist.github.com/albert361/801c22f4f248f6cfe263075b30b08a1e

# 1. start mysql database instance 
docker-compose up -d 

# 2. create test database 
docker-compose exec -T mysql mysql -u root < 01_create_database.sql 

# 3. create test table unpartitioned 
docker-compose exec -T mysql mysql -u root < 02_create_table_not_partitioned.sql 

# 4. create test table partitioned 
docker-compose exec -T mysql mysql -u root < 03_create_table_partitioned.sql 

# 5a. execute bad query on unpartitioned table - error but table and database ok 
docker-compose exec -T mysql mysql -u root < 04_bad_query_not_partitioned.sql-ERROR-1117-TABLE-OK 

# 5b. execute bad query on partitioned table - error table and database broken 
docker-compose exec -T mysql mysql -u root < 05_bad_query_partitioned.sql-ERROR-1117-TABLE-BROKEN 

# 6. check tables - errors on partitioned table (show, alter, insert, drop) (!!! - mysqld died on truncate ERROR 2013 - !!!) 
docker-compose exec -T mysql mysql -u root < 06_check_tables.sql-ERROR-ON-PARTITIONED 

# 7. database errors on recreate table docker-compose exec -T mysql mysql -u root < 07_recreate_database.sql-ERROR-ON-CREATE

[19 Apr 2021 13:49] MySQL Verification Team

Hi Mr. Wang,

Thank you for your bug report.

We have managed to repeat the corruption of the partitioned table.

Verified as reported.

[14 May 2021 13:59] Daniel Price

Posted by developer:
 
Fixed as of the upcoming 8.0.26 release, and here's the proposed changelog entry from the documentation team:

A failure occurred when truncating a partitioned table after an operation
that added too many columns to the table, exceeding the column limit. The
number of columns added is now evaluated before an ADD COLUMN operation is
permitted.

[17 May 2021 16:11] MySQL Verification Team

Thank you, Daniel .......

[18 May 2021 0:55] Albert Wang

Dear developers,

So, if I already have one broken table, can it be dropped after the new release?
Since it's on production server, we can't easily reset everything.