Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.32
-
None
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
-
-
- Closed
-
Activity
I think its all datatype based only currently. Interesting idea with value based.
Note to implemented - substantial refactor in 10.5 MDEV-19710. So listing that as the fix version.
Please add a complete list of what counts as LOSSY and what counts as invalid.
Also, make it clear whether LOSSY depends only on the datatype or also on value. For example, is a DECIMAL(6,2) 123.00 "lossy" when replicated to INT?