Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24461

Virtual column expression error with binlog_format=ROW

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4, 10.5, 10.5.6
    • Fix Version/s: 10.3, 10.4, 10.5
    • Component/s: Virtual Columns
    • Labels:
      None
    • Environment:
      Linux Centos 7.9

      Description

      When a virtual column expression returns an error based on the value, this seems to be checked at different times. When binlog_format is ROW and the binlog is on it looks like it is checked at INSERT time, but when the binlog is off or binlog_format is not ROW, then it isn't checked until SELECT, and then you get a warning instead of an error.

      This is best illustrated with an example:
      First:

      MariaDB>  show variables like 'log_bin';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | log_bin       | OFF   |
      +---------------+-------+
      1 row in set (0.002 sec)
       
      MariaDB> show variables like 'binlog_format';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | binlog_format | MIXED |
      +---------------+-------+
      1 row in set (0.002 sec)
       
      MariaDB> CREATE OR REPLACE TABLE bug(c1 INT, c2 int as (c1 / 0) virtual);
      Query OK, 0 rows affected (0.036 sec)
       
      MariaDB> insert into bug(c1) values(1);
      Query OK, 1 row affected (0.005 sec)
       
      MariaDB> select * from bug;
      +------+------+
      | c1   | c2   |
      +------+------+
      |    1 | NULL |
      +------+------+
      1 row in set, 1 warning (0.001 sec)
       
      MariaDB> show warnings;
      +---------+------+---------------+
      | Level   | Code | Message       |
      +---------+------+---------------+
      | Warning | 1365 | Division by 0 |
      +---------+------+---------------+
      1 row in set (0.000 sec)
      

      Then we try with binlog on. Same result:

      MariaDB> show variables like 'log_bin';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | log_bin       | ON    |
      +---------------+-------+
      1 row in set (0.002 sec)
       
      MariaDB> show variables like 'binlog_format';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | binlog_format | MIXED |
      +---------------+-------+
      1 row in set (0.002 sec)
       
      MariaDB> CREATE OR REPLACE TABLE bug(c1 INT, c2 int as (c1 / 0) virtual);
      Query OK, 0 rows affected (0.041 sec)
       
      MariaDB> insert into bug(c1) values(1);
      Query OK, 1 row affected (0.005 sec)
       
      MariaDB> select * from bug;
      +------+------+
      | c1   | c2   |
      +------+------+
      |    1 | NULL |
      +------+------+
      1 row in set, 1 warning (0.001 sec)
       
      MariaDB> show warnings;
      +---------+------+---------------+
      | Level   | Code | Message       |
      +---------+------+---------------+
      | Warning | 1365 | Division by 0 |
      +---------+------+---------------+
      1 row in set (0.000 sec)
      

      Then we set binlog_format to ROW:

      MariaDB> show variables like 'log_bin';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | log_bin       | ON    |
      +---------------+-------+
      1 row in set (0.002 sec)
       
      MariaDB> show variables like 'binlog_format';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | binlog_format | ROW   |
      +---------------+-------+
      1 row in set (0.002 sec)
       
      MariaDB> CREATE OR REPLACE TABLE bug(c1 INT, c2 int as (c1 / 0) virtual);
      Query OK, 0 rows affected (0.037 sec)
       
      MariaDB> insert into bug(c1) values(1);
      ERROR 1365 (22012): Division by 0
      

        Attachments

          Activity

            People

            Assignee:
            nikitamalyavin Nikita Malyavin
            Reporter:
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration