Mariadb – Can you update invisible columns

mariadbmariadb-10.3

I'm trying to update the row_start column on a table with Versioning set. The row_start column is invisible.

UPDATE pgwebp.t_prov a
INNER JOIN pgwebp.t_prov b ON a.prov_id_a = b.prov_id 
SET a.row_start = b.system_ts_start;

Error message:

Unknown column 'a.row_start' in 'field list'

Best Answer

This looks like the bug/feature request MDEV-16546.

This pertains to the system versioned tables columns like row_start explicitly as normal invisible columns are currently still updateable.

Related Solutions

MariaDB – Why INSERT Fails with NOT NULL but UPDATE Works

The problem is in your database configuration. MariaDB by default SQL_MODE isn't STRICT and UPDATEs even if it's set to NOT NULL.

But this is a pointer to the answer:

A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode. With strict mode not set (the default), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.

With strict mode set, the statements will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

So you need to set the appropriate SQL_MOD as described at: https://mariadb.com/kb/en/mariadb/sql_mode/

Mysql – Updating nested rows

There are two problems

PROBLEM #1

The visit_time needs to be prefixed with NEW.

PROBLEM #2

It needs to be a BEFORE UPDATE, not AFTER UPDATE because you cannot change values in an AFTER trigger.

Your code should read

DELIMITER //
CREATE TRIGGER myTrigger BEFORE UPDATE ON cityies
FOR EACH ROW
BEGIN
    IF NEW.visitors <> OLD.visitors THEN
        SET NEW.visit_time = now();
    END IF;
END
//
DELIMITER ;

Best Answer

Related Solutions

MariaDB – Why INSERT Fails with NOT NULL but UPDATE Works

Mysql – Updating nested rows

Related Question