Can not drop virtual column | ERROR 1054 (42S22): Unknown column in 'GENERATED ALWAYS'

Question

I'm just running a command: optimize table some_table_name_here;

But it results with:

Table | Op | Msg_type | Msg_text

db.some_table_name_here | optimize | note | Table does not support optimize, doing recreate + analyze instead
db.some_table_name_here | optimize | error | Unknown column '`db`.`t`.`total_area`' in 'GENERATED ALWAYS'
db.some_table_name_here | optimize | status | Operation failed

3 rows in set, 1 warning (0.001 sec)

Server version: 10.5.10-MariaDB-1:10.5.10+maria~buster-log mariadb.org binary distribution.

This problem appeared after upgrade from mariadb 10.3 to 10.5 (via 10.4).

// EDIT

I've recently found that the problem is related to generated (virtual) persistent/stored columns that I have in this table.

Here are both columns that may have problems:

`calcOne` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price` - 1) * 100,2)))) STORED

`calcTwo` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price_analog` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price_analog` - 1) * 100,2)))) STORED

I've already tried ALTER TABLE some_table_name_here DROP COLUMN calcOne; to drop column and add it again, but error happens: ERROR 1054 (42S22): Unknown column '`db`.`t`.`total_area`' in 'GENERATED ALWAYS'

// EDIT

As requested (result of SHOW CREATE TABLE some_table_name_here\G):

CREATE TABLE `some_table_name_here` (
  `id` bigint(11) NOT NULL,
  `title` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 NOT NULL,
  `price_actual` int(15) NOT NULL,
  `auction` int(11) DEFAULT 0,
  `square_meter_price` int(11) DEFAULT 0,
  `square_meter_price_analog` int(11) DEFAULT 0,
  `accuracy` int(11) DEFAULT 0,
  `accuracy_analog` int(11) DEFAULT 0,
  `total_area` float DEFAULT 0,
  `calcOne` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price` - 1) * 100,2)))) STORED,
  `calcTwo` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price_analog` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price_analog` - 1) * 100,2)))) STORED,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_price_actual` (`price_actual`),
  KEY `idx_auction` (`auction`),
  KEY `idx_square_meter_price` (`square_meter_price`),
  KEY `idx_square_meter_price_analog` (`square_meter_price_analog`),
  KEY `idx_accuracy` (`accuracy`),
  KEY `idx_accuracy_analog` (`accuracy_analog`),
  KEY `idx_total_area` (`total_area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

// EDIT

When I added those virtual (generated) columns (on original mariadb server 10.3.27) I used this command/query:

ALTER TABLE some_table_name_here ADD COLUMN `sootn` float AS (IF(total_area=0, 0, IF(square_meter_price=0, 0, ROUND(((((price_actual-auction)/total_area/square_meter_price)-1)*100), 2)))) PERSISTENT;
ALTER TABLE some_table_name_here ADD COLUMN `sootn_analog` float AS (IF(total_area=0, 0, IF(square_meter_price_analog=0, 0, ROUND(((((price_actual-auction)/total_area/square_meter_price_analog)-1)*100), 2)))) PERSISTENT;

There were no such problems before. I used optimize table command in order to free space from unexisting/dropped indexes. I've upgraded mariadb 100% using this instructions: https://mariadb.com/kb/en/upgrading-from-mariadb-103-to-mariadb-104/

https://mariadb.com/kb/en/upgrading-from-mariadb-104-to-mariadb-105/

https://mariadb.com/docs/deploy/upgrade-community-server/

Is there any way how can I find where and why this problem occurs?

For now I only see this way in fixing this problem:

dump/export table
drop it (if it's possible)
create table structure
fill it with data

// FINAL

I've tried workaround provided by @RolandoMySQLDBA and it helped after slightly modification.

Command

INSERT INTO some_new_table_name_here SELECT * FROM some_table_name_here;

produced error:

ERROR 1906 (HY000): The value specified for generated column 'calcOne' in table 'some_table_name_here' has been ignored

Since there are generated columns there, it's not possible to INSERT value into that column, so I had to change INSERT query to this:

INSERT INTO some_new_table_name_here (`id`, `title`, `description`, `price_actual`, `auction`, `square_meter_price`, `square_meter_price_analog`, `accuracy`, `accuracy_analog`, `total_area`) SELECT `id`, `title`, `description`, `price_actual`, `auction`, `square_meter_price`, `square_meter_price_analog`, `accuracy`, `accuracy_analog`, `total_area` FROM some_table_name_here;

And afterwards command was fine too:

ANALYZE TABLE some_new_table_name_here;
+-----------------------------+---------+----------+----------+
| Table                       | Op      | Msg_type | Msg_text |
+-----------------------------+---------+----------+----------+
| db.some_new_table_name_here | analyze | status   | OK       |
+-----------------------------+---------+----------+----------+
1 row in set (2.137 sec)

I've also discovered one nice thing - after successfully duplicating table, renaming both of old and new of them and doing analyze, I tried to analyze and also optimize some_old_table_name_here table and it worked without errors!

IDK what was the problem. Maybe since the table previously was in use, it was not possible to alter it, while there are generated columns.

Suggest you file a bug report.
– Rick James
Commented Jun 13, 2021 at 18:12 — Rick James, Commented Jun 13, 2021 at 18:12

RolandoMySQLDBA · Accepted Answer · 2021-06-13 19:10:57Z

Here is what is discernable from the messages you gave

Message Line #1

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;
RENAME TABLE mytable TO myoldtable,mynewtable TO mytable;
DROP TABLE myoldtable;
ANALYZE TABLE mynewtable;

I have discussed this before in the DBA StackExchange

Sep 27, 2015 : How can I defragment tables in MySQL?
Feb 27, 2013 : What is the difference between optimize table and analyze table in mysql
Aug 04, 2011 : Optimizing InnoDB default settings

Message Line #2

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 option clause:
CREATE TABLE t(
   x INT,
   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 (
   x INT
) 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.

Possible Workaround

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.

Try this:

USE db
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;
RENAME TABLE
    some_table_name_here TO some_old_table_name_here,
    some_new_table_name_here TO some_table_name_here;
ANALYZE TABLE some_new_table_name_here;
DROP TABLE some_old_table_name_here;

You still need to look up release notes anyway if you are using system versioned tables.

UPDATE 2021-06-13 09:00 EDT

Look carefully

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.

UPDATE 2021-06-13 12:54 EDT

I decided to spin up a vagrant instance with MariaDB 10.5

rolan@LAPTOP-57FSQ99U ~/vagrant/mariadb-10.5
$ vagrant up && vagrant ssh && vagrant halt
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'chirontex/laemp' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'chirontex/laemp'
    default: URL: https://vagrantcloud.com/chirontex/laemp
==> default: Adding box 'chirontex/laemp' (v1.0.8) for provider: virtualbox
    default: Downloading: https://vagrantcloud.com/chirontex/boxes/laemp/versions/1.0.8/providers/virtualbox.box
    default: Download redirected to host: vagrantcloud-files-production.s3-accelerate.amazonaws.com
    default:
==> default: Successfully added box 'chirontex/laemp' (v1.0.8) for 'virtualbox'!
==> default: Importing base box 'chirontex/laemp'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'chirontex/laemp' is up to date...
==> default: Setting the name of the VM: mariadb-105_default_1623601974259_55634
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
    default: Warning: Connection reset. Retrying...
    default: Warning: Connection aborted. Retrying...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
    default: The guest additions on this VM do not match the installed version of
    default: VirtualBox! In most cases this is fine, but in rare cases it can
    default: prevent things such as shared folders from working properly. If you see
    default: shared folder errors, please make sure the guest additions within the
    default: virtual machine match the version of VirtualBox you have installed on
    default: your host and reload your VM.
    default:
    default: Guest Additions Version: 6.1.22
    default: VirtualBox Version: 6.0
==> default: Mounting shared folders...
    default: /vagrant => C:/cygwin64/home/rolan/vagrant/mariadb-10.5
Last login: Tue Jun  1 23:37:54 2021 from 10.0.2.2
[vagrant@localhost ~]$ sudo su - root
[root@localhost ~]# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.10-MariaDB MariaDB Server

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 [(none)]>

Next, I created a database and created the table in it

MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use mydb
Database changed
MariaDB [mydb]> CREATE TABLE `some_table_name_here` (
    ->   `id` bigint(11) NOT NULL,
    ->   `title` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL,
    ->   `description` text CHARACTER SET utf8mb4 NOT NULL,
    ->   `price_actual` int(15) NOT NULL,
    ->   `auction` int(11) DEFAULT 0,
    ->   `square_meter_price` int(11) DEFAULT 0,
    ->   `square_meter_price_analog` int(11) DEFAULT 0,
    ->   `accuracy` int(11) DEFAULT 0,
    ->   `accuracy_analog` int(11) DEFAULT 0,
    ->   `total_area` float DEFAULT 0,
    ->   `calcOne` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price` - 1) * 100,2)))) STORED,
    ->   `calcTwo` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price_analog` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price_analog` - 1) * 100,2)))) STORED,
    ->   PRIMARY KEY (`id`) USING BTREE,
    ->   KEY `idx_price_actual` (`price_actual`),
    ->   KEY `idx_auction` (`auction`),
    ->   KEY `idx_square_meter_price` (`square_meter_price`),
    ->   KEY `idx_square_meter_price_analog` (`square_meter_price_analog`),
    ->   KEY `idx_accuracy` (`accuracy`),
    ->   KEY `idx_accuracy_analog` (`accuracy_analog`),
    ->   KEY `idx_total_area` (`total_area`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.015 sec)

MariaDB [mydb]>

Now, let's just run OPTIMIZE TABLE some_table_name_here;

MariaDB [mydb]> optimize table some_table_name_here;
+---------------------------+----------+----------+-------------------------------------------------------------------+
| Table                     | Op       | Msg_type | Msg_text                                                          |
+---------------------------+----------+----------+-------------------------------------------------------------------+
| mydb.some_table_name_here | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mydb.some_table_name_here | optimize | status   | OK                                                                |
+---------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.034 sec)

MariaDB [mydb]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.5.10-MariaDB |
+-----------------+
1 row in set (0.000 sec)

MariaDB [mydb]> exit
Bye
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 8.3.2011
[root@localhost ~]#

OK, it works fine as an empty table. I am using CentOS.

I will try finding a vagrant instance using debian buster and I'll get back to you.

UPDATE 2021-06-13 13:33 EDT

Created Table and ran OPTIMIZE TABLE

MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> USE mydb
  `id` bigint(11) NOT NULL,
  `title` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 NOT NULL,
  `price_actual` int(15) NOT NULL,
Database changed
MariaDB [mydb]> CREATE TABLE `some_table_name_here` (
    ->   `id` bigint(11) NOT NULL,
    ->   `title` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL,
    ->   `description` text CHARACTER SET utf8mb4 NOT NULL,
    ->   `price_actual` int(15) NOT NULL,
    ->   `auction` int(11) DEFAULT 0,
    ->   `square_meter_price` int(11) DEFAULT 0,
    ->   `square_meter_price_analog` int(11) DEFAULT 0,
    ->   `accuracy` int(11) DEFAULT 0,
    ->   `accuracy_analog` int(11) DEFAULT 0,
    ->   `total_area` float DEFAULT 0,
    ->   `calcOne` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price` - 1) * 100,2)))) STORED,
    ->   `calcTwo` float GENERATED ALWAYS AS (if(`total_area` = 0,0,if(`square_meter_price_analog` = 0,0,round(((`price_actual` - `auction`) / `total_area` / `square_meter_price_analog` - 1) * 100,2)))) STORED,
    ->   PRIMARY KEY (`id`) USING BTREE,
    ->   KEY `idx_price_actual` (`price_actual`),
    ->   KEY `idx_auction` (`auction`),
    ->   KEY `idx_square_meter_price` (`square_meter_price`),
    ->   KEY `idx_square_meter_price_analog` (`square_meter_price_analog`),
    ->   KEY `idx_accuracy` (`accuracy`),
    ->   KEY `idx_accuracy_analog` (`accuracy_analog`),
    ->   KEY `idx_total_area` (`total_area`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.013 sec)

MariaDB [mydb]> optimize table some_table_name_here;
+---------------------------+----------+----------+-------------------------------------------------------------------+
| Table                     | Op       | Msg_type | Msg_text                                                          |
+---------------------------+----------+----------+-------------------------------------------------------------------+
| mydb.some_table_name_here | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mydb.some_table_name_here | optimize | status   | OK                                                                |
+---------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.046 sec)

MariaDB [mydb]> select version();
+--------------------------------------------+
| version()                                  |
+--------------------------------------------+
| 10.4.19-MariaDB-1:10.4.19+maria~buster-log |
+--------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mydb]>

Things look good so far for MariaDB 10.4. Still looking ...

@RolandoMySQLDBA I don't know what was the reason of this problem, but after duplicating table and renaming both old and new tables, analyze/optimize commands successfully finished on both old and new tables! Thanks for taking time and help. — iorsa, Commented Jun 14, 2021 at 10:28

Stack Exchange Network

Can not drop virtual column | ERROR 1054 (42S22): Unknown column in 'GENERATED ALWAYS'

1 Answer 1

Message Line #1

Message Line #2

Possible Workaround

UPDATE 2021-06-13 09:00 EDT

UPDATE 2021-06-13 12:54 EDT

UPDATE 2021-06-13 13:33 EDT

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
innodb
mariadb
virtual-columns
generated-columns
or ask your own question.

Linked

Hot Network Questions

Can not drop virtual column | ERROR 1054 (42S22): Unknown column in 'GENERATED ALWAYS'

1 Answer 1

Message Line #1

Message Line #2

Possible Workaround

UPDATE 2021-06-13 09:00 EDT

UPDATE 2021-06-13 12:54 EDT

UPDATE 2021-06-13 13:33 EDT

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlinnodbmariadbvirtual-columnsgenerated-columns or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
innodb
mariadb
virtual-columns
generated-columns
or ask your own question.