Here is what is discernable from the messages you gave
db.some_table_name_here | optimize | note |
Table does not support optimize, doing recreate + analyze instead
This is perfectly OK. This tells me that the table uses the InnoDB storage engine. This exact message comes out when doing
OPTIMIZE TABLE against an InnoDB table.
In other words, doing
OPTIMIZE TABLE mytable is implemented in InnoDB something like this
CREATE TABLE mynewtable LIKE mytable;
INSERT INTO mynewtable SELECT * FROM mytable;
DROP TABLE mytable;
RENAME TABLE mynewtable TO mytable;
ANALYZE TABLE mynewtable;
I have discussed this before in the DBA StackExchange
db.some_table_name_here | optimize | error |
Unknown column '`db`.`t`.`total_area`' in 'GENERATED ALWAYS'
This tells me that
db.some_table_name_here has system versioning integrated. The MariaDB Documentation has a Section on it
The CREATE TABLE syntax has been extended to permit creating a system-versioned table. To be system-versioned, according to SQL:2011,
a table must have two generated columns, a period, and a special table
CREATE TABLE t(
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
In MariaDB one can also use a simplified syntax:
CREATE TABLE t (
) WITH SYSTEM VERSIONING;
In the latter case no extra columns will be created and they won’t clutter the output of, say, SELECT * FROM t. The versioning
information will still be stored, and it can be accessed via the
pseudo-columns ROW_START and ROW_END:
SELECT x, ROW_START, ROW_END FROM t;
System Versioning was introduced in MariaDB 10.3. You should check the release notes on MariaDB 10.4 and 10.5 and see if you needed to do special upgrading of system versioned tables.
Until you figure out the proper way to upgrade system versioned tables, you may have to do the
optimize table manually as I mentioned before.
CREATE TABLE some_new_table_name_here LIKE some_table_name_here;
INSERT INTO some_new_table_name_here SELECT * FROM some_table_name_here;
DROP TABLE some_table_name_here;
RENAME TABLE some_new_table_name_here TO some_table_name_here;
ANALYZE TABLE some_new_table_name_here ;
You still need to look up release notes anyway if you are using system versioned tables.
1 column: `calcOne` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price` = 0,0,round(((`price` - `auction`) / `total_area` / `square_meter_price` - 1) * 100,2)))) STORED
2 column: `calcTwo` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price_analog` = 0,0,round(((`price` - `auction`) / `total_area` / `square_meter_price_analog` - 1) * 100,2)))) STORED
It is complaining about
total_area. Is this column missing from the table ? If it is missing, then add the column back and try again.