multiple um system

Issue was reproduced by development and the below workaround corrected the issue. These 2 commands need to be run on tables affected.

Drop Table xxx RESTRICT from um1
Create Table xxx comment='schema sync only' on um1

Faced the same problem on a singlenode system

We will be working on a script to solve this short-term as well as a minor fix to ColumnStore to allow the script to work. In addition MDEV-19120 has been opened to track this in MariaDB Server.

Possible easier workaround, the following forces a rebuild the FRM file for a table with the correct ID:

alter table t1 change a a int comment '';

Where 'a' is a column which is already an integer.

This patch enables ALTER TABLE to change table comments and adds a stored procedure which updates the table comment for every ColumnStore table in a system. This causes the FRM files to be rebuilt and therefore the engine IDs.

With this patch, when user is doing a major upgrade (or if the user has already done a major upgrade) they will need to execute:

call columnstore_info.columnstore_upgrade();

The downside is an table comment the user has will be erased. The alternative is the user could manually change table comments (even if it is to existing comments) after this patch.

For QA: two things you should probably test:
1. Calling the above when there are lots of tables (can't see there being any problems).
2. To reproduce the problem you need to hex edit a ColumnStore table's FRM file, change the value 2D to 2C (should be the 4th byte) and run the above. It should snap back to 2D again.

Build tested: 1.2.4-1 nightly

server commit:
137b9a8
engine commit:
d4d7f55

After upgrading 1.1.5-1 to 1.2.4-1, the same issue still exist. It still need to execute the Columnstore_info.columnstore_upgrade() to fix the problem. The ./bin/mysql_upgrade script should perform the same logic the columnstore_upgrade() stored procedure does so that the user would not need to run columnstore_upgrade() separately.

Reproduced the issue when upgrading from 1.1.5-1 to 1.2.2-1 with a 2um2pm configuration

1. installed 1.1.5-1 on a 1um2pm stack
2. loaded 10g dbt3 database
3. upgrade to 1.2.2-1
4. run upgrade script mysql_upgrade
5. added um2
6. ran upgrade script mysql_upgrade on um2
7. truncate table orders from um1

MariaDB [tpch10]> truncate table orders;
ERROR 1031 (HY000): Storage engine Columnstore of the table `tpch10`.`orders` doesn't have this option

Verified the following scenarios:

1. Fix one table using 'alter table' command.

MariaDB [tpch10]> alter table orders change o_orderkey o_orderkey int comment '';
Query OK, 0 rows affected (0.301 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [tpch10]> truncate table orders;
Query OK, 0 rows affected (0.589 sec)

2. With existing 2um2pm stack from step #1, upgrade to 1.2.4-1

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.13-MariaDB-log Columnstore 1.2.4-1

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [tpch10]> truncate table nation;
ERROR 1031 (HY000): Storage engine Columnstore of the table `tpch10`.`nation` doesn't have this option
MariaDB [tpch10]> call columnstore_info.columnstore_upgrade();
Query OK, 0 rows affected (0.643 sec)

MariaDB [tpch10]> truncate table nation;
Query OK, 0 rows affected (0.319 sec)

3. Upgrade 1.1.5-1 to 1.2.4-1 directly

Using the same steps described in the "Reproduced the issue" section to upgrade 1.1.5-1 to 1.2.4-1, I encounter the same errors. Executing the "call columnstore_info.columnstore_upgrade();" command would fix the issue, just like in step #2

The fix works. We need to document the additional step in the 1.2.4-1 upgrade guide.