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

replication from timestamp to datetime cannot succeed (even with slave_type_conversions='ALL_LOSSY')

Details

    Description

      From https://www.reddit.com/r/mariadb/comments/17ufav9/changing_datatypes_on_replica_failing/

      When a replica has a dataype changed from timestamp to datetime it stops the replication even under slave_type_conversions='ALL_LOSSY';

      In a non-replication situation conversion occurs without even a warning:

      MariaDB [test]> create table t (t timestamp default now());
      		 
      Query OK, 0 rows affected (0.001 sec)
      		 
       
      		 
      MariaDB [test]> create table d (d datetime);
      		 
      Query OK, 0 rows affected (0.001 sec)
      		 
       
      		 
      MariaDB [test]> insert into t() values (),(),();
      		 
      Query OK, 3 rows affected (0.001 sec)
      		 
      Records: 3  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> select * from t;
      		 
      +---------------------+
      		 
      | t                   |
      		 
      +---------------------+
      		 
      | 2023-11-15 11:57:57 |
      		 
      | 2023-11-15 11:57:57 |
      		 
      | 2023-11-15 11:57:57 |
      		 
      +---------------------+
      		 
      3 rows in set (0.001 sec)
      		 
       
      		 
      MariaDB [test]> select * from d;
      		 
      +---------------------+
      		 
      | d                   |
      		 
      +---------------------+
      		 
      | 2023-11-15 11:57:57 |
      		 
      | 2023-11-15 11:57:57 |
      		 
      | 2023-11-15 11:57:57 |
      		 
      +---------------------+
      		 
      3 rows in set (0.001 sec)
      		 
      MariaDB [test]> insert into d select t from t;
      		 
      Query OK, 3 rows affected (0.001 sec)
      		 
      Records: 3  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> select * from d;
      		 
      +---------------------+
      		 
      | d                   |
      		 
      +---------------------+
      		 
      | 2023-11-15 11:57:57 |
      		 
      | 2023-11-15 11:57:57 |
      		 
      | 2023-11-15 11:57:57 |
      		 
      +---------------------+
      		 
      3 rows in set (0.001 sec)

      Request that replication from timestamp to datetime columns occur (preferably without even a lossy requirement if appropriate).

      rpl-lossy.test

      		 
      --source include/master-slave.inc
      		 
       
      		 
      connection master;
      		 
       
      		 
      CREATE TABLE t1 (t timestamp);
      		 
      INSERT INTO t1 values ('2023-11-15 11:57:57');
      		 
       
      		 
      sync_slave_with_master;
      		 
      connection slave;
      		 
       
      		 
      SET GLOBAL slave_type_conversions='ALL_LOSSY';
      		 
       
      		 
      ALTER TABLE t1 MODIFY t datetime;
      		 
       
      		 
      connection master;
      		 
       
      SET binlog_format=ROW;
      		 
      INSERT INTO t1 values ('2022-01-01 01:01:01');
      		 
       
      		 
      sync_slave_with_master;
      		 
       
      		 
      connection slave;
      		 
       
      		 
      SELECT * FROM t1;
      		 
       
      		 
      SET GLOBAL slave_type_conversions=DEFAULT;
      		 
      connection master;
      		 
      DROP TABLE t1;
      		 
      sync_slave_with_master;
      		 
      --source include/rpl_end.inc

      result

      		 
      CURRENT_TEST: main.rpl-lossy
      		 
      analyze: sync_with_master
      		 
      mysqltest: At line 20: sync_slave_with_master failed: 'select master_pos_wait('master-bin.000001', 985, 300, '')' returned NULL indicating slave SQL thread failure
      		 
       
      		 
       
      		 
       == /home/dan/repos/build-mariadb-server-10.4/mysql-test/var/tmp/analyze-sync_with_master-mysqld.2.err ==
      		 
       
      		 
      ############################## default ##############################
      		 
       
      		 
      **** SHOW WARNINGS on default ****
      		 
      SHOW WARNINGS;
      		 
      Level	Code	Message
      		 
       
      		 
      **** SELECT replication-related variables on default ****
      		 
      SELECT NOW(), @@SERVER_ID;
      		 
      NOW()	@@SERVER_ID
      		 
      2023-11-15 12:07:25	2
      		 
       
      		 
      **** SHOW SLAVE STATUS on default ****
      		 
      SHOW SLAVE STATUS;
      		 
      ...
      		 
      Last_Errno	1677
      		 
      Last_Error	Column 0 of table 'test.t1' cannot be converted from type 'timestamp' to type 'datetime'
      		 
      Skip_Counter	0

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17098 DATE -> DATETIME replication conversion not working, even in ALL_NON_LOSSY mode

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            danblack Daniel Black created issue -
            danblack Daniel Black made changes -
            Field Original Value New Value
            Description From https://www.reddit.com/r/mariadb/comments/17ufav9/changing_datatypes_on_replica_failing/

            When a replica has a dataype changed from timestamp to datetime it stops the replication even under slave_type_conversions='ALL_LOSSY';

            In a non-replication situation conversion occurs without even a warning:

            {noformat}
            MariaDB [test]> create table t (t timestamp default now());
            Query OK, 0 rows affected (0.001 sec)

            MariaDB [test]> create table d (d datetime);
            Query OK, 0 rows affected (0.001 sec)

            MariaDB [test]> insert into t() values (),(),();
            Query OK, 3 rows affected (0.001 sec)
            Records: 3 Duplicates: 0 Warnings: 0

            MariaDB [test]> select * from t;
            +---------------------+
            | t |
            +---------------------+
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            +---------------------+
            3 rows in set (0.001 sec)

            MariaDB [test]> insert into d select t from t;
            Query OK, 3 rows affected (0.001 sec)
            Records: 3 Duplicates: 0 Warnings: 0

            MariaDB [test]> select * from t;
            +---------------------+
            | t |
            +---------------------+
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            +---------------------+
            3 rows in set (0.001 sec)
            {noformat}

            Request that replication from timestamp to datetime columns occur (preferably without even a lossy requirement if appropriate).

            {noformat:title=rpl-lossy.test}
            --source include/master-slave.inc

            connection master;

            CREATE TABLE t1 (t timestamp);
            INSERT INTO t1 values ('2023-11-15 11:57:57');

            sync_slave_with_master;
            connection slave;

            SET GLOBAL slave_type_conversions='ALL_LOSSY';

            ALTER TABLE t1 MODIFY t datetime;

            connection master;
             
            SET binlog_format=ROW;
            INSERT INTO t1 values ('2022-01-01 01:01:01');

            sync_slave_with_master;

            connection slave;

            SELECT * FROM t1;

            SET GLOBAL slave_type_conversions=DEFAULT;
            connection master;
            DROP TABLE t1;
            sync_slave_with_master;
            --source include/rpl_end.inc
            {noformat}

            {noformat:title=result}
            CURRENT_TEST: main.rpl-lossy
            analyze: sync_with_master
            mysqltest: At line 20: sync_slave_with_master failed: 'select master_pos_wait('master-bin.000001', 985, 300, '')' returned NULL indicating slave SQL thread failure


             == /home/dan/repos/build-mariadb-server-10.4/mysql-test/var/tmp/analyze-sync_with_master-mysqld.2.err ==

            ############################## default ##############################

            **** SHOW WARNINGS on default ****
            SHOW WARNINGS;
            Level Code Message

            **** SELECT replication-related variables on default ****
            SELECT NOW(), @@SERVER_ID;
            NOW() @@SERVER_ID
            2023-11-15 12:07:25 2

            **** SHOW SLAVE STATUS on default ****
            SHOW SLAVE STATUS;
            ...
            Last_Errno 1677
            Last_Error Column 0 of table 'test.t1' cannot be converted from type 'timestamp' to type 'datetime'
            Skip_Counter 0
            {noformat}             		From https://www.reddit.com/r/mariadb/comments/17ufav9/changing_datatypes_on_replica_failing/

            When a replica has a dataype changed from timestamp to datetime it stops the replication even under slave_type_conversions='ALL_LOSSY';

            In a non-replication situation conversion occurs without even a warning:

            {noformat}
            MariaDB [test]> create table t (t timestamp default now());
            Query OK, 0 rows affected (0.001 sec)

            MariaDB [test]> create table d (d datetime);
            Query OK, 0 rows affected (0.001 sec)

            MariaDB [test]> insert into t() values (),(),();
            Query OK, 3 rows affected (0.001 sec)
            Records: 3 Duplicates: 0 Warnings: 0

            MariaDB [test]> select * from t;
            +---------------------+
            | t |
            +---------------------+
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            +---------------------+
            3 rows in set (0.001 sec)

            MariaDB [test]> select * from d;
            +---------------------+
            | d |
            +---------------------+
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            +---------------------+
            3 rows in set (0.001 sec)
            MariaDB [test]> insert into d select t from t;
            Query OK, 3 rows affected (0.001 sec)
            Records: 3 Duplicates: 0 Warnings: 0

            MariaDB [test]> select * from d;
            +---------------------+
            | d |
            +---------------------+
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            | 2023-11-15 11:57:57 |
            +---------------------+
            3 rows in set (0.001 sec)
            {noformat}

            Request that replication from timestamp to datetime columns occur (preferably without even a lossy requirement if appropriate).

            {noformat:title=rpl-lossy.test}
            --source include/master-slave.inc

            connection master;

            CREATE TABLE t1 (t timestamp);
            INSERT INTO t1 values ('2023-11-15 11:57:57');

            sync_slave_with_master;
            connection slave;

            SET GLOBAL slave_type_conversions='ALL_LOSSY';

            ALTER TABLE t1 MODIFY t datetime;

            connection master;
             
            SET binlog_format=ROW;
            INSERT INTO t1 values ('2022-01-01 01:01:01');

            sync_slave_with_master;

            connection slave;

            SELECT * FROM t1;

            SET GLOBAL slave_type_conversions=DEFAULT;
            connection master;
            DROP TABLE t1;
            sync_slave_with_master;
            --source include/rpl_end.inc
            {noformat}

            {noformat:title=result}
            CURRENT_TEST: main.rpl-lossy
            analyze: sync_with_master
            mysqltest: At line 20: sync_slave_with_master failed: 'select master_pos_wait('master-bin.000001', 985, 300, '')' returned NULL indicating slave SQL thread failure


             == /home/dan/repos/build-mariadb-server-10.4/mysql-test/var/tmp/analyze-sync_with_master-mysqld.2.err ==

            ############################## default ##############################

            **** SHOW WARNINGS on default ****
            SHOW WARNINGS;
            Level Code Message

            **** SELECT replication-related variables on default ****
            SELECT NOW(), @@SERVER_ID;
            NOW() @@SERVER_ID
            2023-11-15 12:07:25 2

            **** SHOW SLAVE STATUS on default ****
            SHOW SLAVE STATUS;
            ...
            Last_Errno 1677
            Last_Error Column 0 of table 'test.t1' cannot be converted from type 'timestamp' to type 'datetime'
            Skip_Counter 0
            {noformat}
            alice Alice Sherepa made changes -
            julien.fritsch Julien Fritsch made changes -
            Labels 10.5
            julien.fritsch Julien Fritsch made changes -
            Labels 10.5
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.5 [ 23123 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.4 [ 22408 ]

            People

              Unassigned Unassigned
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.