[MDEV-19632] Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode Created: 2019-05-29  Updated: 2021-04-19  Resolved: 2020-08-01

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.3.16, 10.3.18, 10.3, 10.4
Fix Version/s: 10.3.24, 10.4.14, 10.5.5

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: oracle

Issue Links:
Blocks
is blocked by MDEV-20263 sql_mode=ORACLE: BLOB(65535) should n... Closed
Relates
relates to MDEV-23353 Qualified data types in SP Open
relates to MDEV-23005 sql_mode mixture: a table with DECODE... Open
relates to MDEV-23040 sql_mode mixture: a table with TRIM()... Open

 Description   

Test case 1

 
--source include/have_binlog_format_row.inc
 
--source include/master-slave.inc
 
 
 
CREATE TABLE t1 (a BLOB);
 
INSERT INTO t1 VALUES (0);
 
SET SQL_MODE= 'ORACLE';
 
CREATE TABLE t2 SELECT * FROM t1;
 
--sync_slave_with_master
 
 
 
# Cleanup
 
--connection master
 
DROP TABLE t1, t2;
 
--source include/rpl_end.inc

10.3 617d34ae

 
2019-05-29 15:19:53 13 [ERROR] Slave SQL: Column 0 of table 'test.t2' cannot be converted from type 'tinyblob' to type 'longblob', Gtid 0-1-3, Internal MariaDB error code: 1677
 
2019-05-29 15:19:53 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 708


master-bin.000001	708	Gtid	1	750	BEGIN GTID 0-1-3
 
master-bin.000001	750	Query	1	862	use `test`; CREATE TABLE "t2" (
 
  "a" blob DEFAULT NULL
 
)
 
master-bin.000001	862	Annotate_rows	1	917	CREATE TABLE t2 SELECT * FROM t1
 
master-bin.000001	917	Table_map	1	963	table_id: 33 (test.t2)
 
master-bin.000001	963	Write_rows_v1	1	1000	table_id: 33 flags: STMT_END_F
 
master-bin.000001	1000	Query	1	1073	COMMIT

Same idea, different types:

Test case 2

 
--source include/have_binlog_format_row.inc
 
--source include/master-slave.inc
 
 
 
CREATE TABLE t1 (a DATE);
 
INSERT INTO t1 VALUES (NULL);
 
SET SQL_MODE= 'ORACLE';
 
CREATE TABLE t2 SELECT * FROM t1;
 
--sync_slave_with_master
 
 
 
# Cleanup
 
--connection master
 
DROP TABLE t1, t2;
 
--source include/rpl_end.inc


2019-05-29 19:53:05 13 [ERROR] Slave SQL: Column 0 of table 'test.t2' cannot be converted from type 'date' to type 'datetime', Gtid 0-1-3, Internal MariaDB error code: 1677
 
2019-05-29 19:53:05 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 707



 Comments   
Comment by Andrei Elkin [ 2020-06-22 ]

sujatha.sivakumar: So sql_mode force the input DATE type in CREATE TABLE become DATETYPE as SHOW can prove:

15:58:33 [test]> set @@sql_mode='oracle';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
15:58:39 [test]> CREATE TABLE t1_o (a DATE);
 
 
 
15:58:55 [test]> show create table t1_o;
 
+-------+-----------------------------------------------------+
 
| Table | Create Table                                        |
 
+-------+-----------------------------------------------------+
 
| t1_o  | CREATE TABLE "t1_o" (
 
  "a" datetime DEFAULT NULL
 
)

Notice CREATE-LIKE when run in oracle mode also converts (NO, it does not, the created one should have been LIKE t1 not what is below):

15:58:55 [test]> show create table t1_o;
 
+-------+-----------------------------------------------------+
 
| Table | Create Table                                        |
 
+-------+-----------------------------------------------------+
 
| t1_o  | CREATE TABLE "t1_o" (
 
  "a" datetime DEFAULT NULL
 
) 
 
 
15:59:00 [test]> create table t1_o2 like t1_o;
 
 
 
15:59:37 [test]> show create table t1_o;
 
+-------+-----------------------------------------------------+
 
| Table | Create Table                                        |
 
+-------+-----------------------------------------------------+
 
| t1_o  | CREATE TABLE "t1_o" (
 
  "a" datetime DEFAULT NULL
 
)

But CREATE-SELECT does not - 

15:59:49 [test]> CREATE TABLE t1_cs_o SELECT * from t1;
 
 
 
16:00:29 [test]> show create table t1_cs_o;
 
+---------+----------------------------------------------------+
 
| Table   | Create Table                                       |
 
+---------+----------------------------------------------------+
 
| t1_cs_o | CREATE TABLE "t1_cs_o" (
 
  "a" date DEFAULT NULL
 
)

which must be the reason of the bug.
The fixes must make sure the conversion takes place in this case as well.
This is not really the replication issue and if you feel clueless about how to enforce the conversion we may send the bug to runtime.

Comment by Alexander Barkov [ 2020-06-22 ]

Andrei, I can't repeat your results.

These two scripts:

SET sql_mode=ORACLE;
 
CREATE OR REPLACE TABLE t1 (a DATE);
 
CREATE OR REPLACE TABLE t2 LIKE t1;
 
DESC t2;


SET sql_mode=ORACLE;
 
CREATE OR REPLACE TABLE t1 (a DATE);
 
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1;
 
DESC t2;

both return:

+-------+----------+------+-----+---------+-------+
 
| Field | Type     | Null | Key | Default | Extra |
 
+-------+----------+------+-----+---------+-------+
 
| a     | datetime | YES  |     | NULL    |       |
 
+-------+----------+------+-----+---------+-------+

Comment by Andrei Elkin [ 2020-06-22 ]

bar Alexander, thanks a lot for trying to help. I did not mention ROW binlog format (as being mentioned already in the desc) which is cruicial. Here is my re-try:

18:33:27 [test]> create  table t_date (d date);
 
18:33:51 [test]> show create table t_date;
 
+--------+----------------------------------------------------------------------------------------+
 
| Table  | Create Table                                                                           |
 
+--------+----------------------------------------------------------------------------------------+
 
| t_date | CREATE TABLE `t_date` (
 
  `d` date DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 
 
 
18:33:57 [test]> set @@sql_mode='oracle';
 
18:34:04 [test]> set @@binlog_format=row;
 
 
 
18:35:32 [test]> CREATE TABLE t_o_cs SELECT * from t_date /* in oracle mode */;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
18:35:41 [test]> show create table t_o_cs;
 
+--------+---------------------------------------------------+
 
| Table  | Create Table                                      |
 
+--------+---------------------------------------------------+
 
| t_o_cs | CREATE TABLE "t_o_cs" (
 
  "d" date DEFAULT NULL
 
)

Comment by Alexander Barkov [ 2020-06-22 ]

If I create the table with the DATE data type with the default sql_mode, and then run CREATE..LIKE or CREATE..SELECT with sql_mode=ORACLE, both create a column of the DATE data type:

SET sql_mode=DEFAULT;
 
CREATE OR REPLACE TABLE t1 (a DATE);
 
SET sql_mode=ORACLE;
 
CREATE OR REPLACE TABLE t2 LIKE t1;
 
DESC t2;


+-------+------+------+-----+---------+-------+
 
| Field | Type | Null | Key | Default | Extra |
 
+-------+------+------+-----+---------+-------+
 
| a     | date | YES  |     | NULL    |       |
 
+-------+------+------+-----+---------+-------+


SET sql_mode=DEFAULT;
 
CREATE OR REPLACE TABLE t1 (a DATE);
 
SET sql_mode=ORACLE;
 
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1;
 
DESC t2;


+-------+------+------+-----+---------+-------+
 
| Field | Type | Null | Key | Default | Extra |
 
+-------+------+------+-----+---------+-------+
 
| a     | date | YES  |     | NULL    |       |
 
+-------+------+------+-----+---------+-------+

So CREATE..LIKE and CREATE..SELECT work in the same way with a standalone server.

Comment by Alexander Barkov [ 2020-06-22 ]

The problem is that:

SET sql_mode=ORACLE;
 
CREATE TABLE t2 AS SELECT real_date_column FROM t1;

is replicated as:

SET sql_mode=ORACLE;
 
CREATE TABLE t2 (a DATE); -- this is wrong
 
INSERT INTO t2 SELECT real_date_column FROM t1;

It should be something like:

SET sql_mode=ORACLE;
 
CREATE TABLE t2 (a MARIADB_DATE); -- parser independent data type name
 
INSERT INTO t2 SELECT real_date_column FROM t1;

Comment by Andrei Elkin [ 2020-06-22 ]
So CREATE..LIKE and CREATE..SELECT work in the same way with a standalone server.

You're correct. Sorry for confusing, in my paste I compared CREATE from different "originals".

Comment by Alexander Barkov [ 2020-06-23 ]

Hi Elkin,

Serg proposed an idea.

Why we cannot replicate this:

SET sql_mode=ORACLE;
 
CREATE TABLE t2 AS SELECT real_date_column FROM t1;

as

SET sql_mode=ORACLE;
 
CREATE TABLE t2 AS SELECT real_date_column FROM t1 LIMIT 0; -- this will create DATE column as expected
 
INSERT INTO t2 VALUES (row1);
 
INSERT INTO t2 VALUES (rowN);

instead of the current way (which involves the parser):

SET sql_mode=ORACLE;
 
CREATE TABLE t2 AS SELECT (real_date_column DATE); -- this erroneously creates a DATETIME column
 
INSERT INTO t2 VALUES (row1);
 
INSERT INTO t2 VALUES (rowN);

Elkin, what do you think?

Comment by Andrei Elkin [ 2020-06-23 ]

bar You must mean to place into binlog CREATE-SELECT-LIMIT-0 as the table def, instead of the plain CREATE. Then CREATE-LIKE would be a bit more suitable, right?

Either way I think it makes sense. Another question comes should we always binlog such way or at sql_mode = 'oracle' and such?

Comment by Andrei Elkin [ 2020-06-23 ]

bar After discussing with sujatha.sivakumar, I also feel we could consider to omit sql_mode='oracle' in Query_log_event if that can help to log it correctly.

Comment by Alexander Barkov [ 2020-06-23 ]

Elkin, I don't think CREATE-LIKE would be suituble all to replicate CREATE-TABLE-SELECT, because SELECT can contain joins or unions.

Comment by Sergei Golubchik [ 2020-06-24 ]

Elkin, no, definitely not CREATE ... LIKE it produces different results. In particular, it creates indexes that CREATE ... SELECT doesn't. And you cannot do CREATE ... LIKE on, say, SELECT 1 from t1.

Another option, indeed, would be to log it as

set statement sql_mode='' create table t1 (...);

Comment by Elena Stepanova [ 2020-06-24 ]

I wonder whether it would work, given that MDEV-14760 (Replication does not take into account SET STATEMENT) is still open.

Comment by Andrei Elkin [ 2020-06-24 ]

serg set statement sql_mode='' is inferior 'cos sql_mode is a member of Query_log_event::status_var. So regardless of MDEV-14760 we should not have Query_log_event::status_var::sql_mode = 'oracle'. And setting it to '' in status_var would fix the bug being of the same tech trouble.

Comment by Sujatha Sivakumar (Inactive) [ 2020-06-24 ]

Hello elenst

MDEV-14760 is (not a bug). All variables on the master are not replicated. Few variables get replicated and few of them are not. For example 'auto_increment_increment', 'sql_mode'. When these variables used with SET STATEMENT they get replicated. There are variables like 'storage_engine' which are not replicated. When these variables are associated with SET STATEMENT they are no-op. Please refer https://jira.mariadb.org/browse/MDEV-14760?focusedCommentId=128669&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-128669

Comment by Andrei Elkin [ 2020-06-24 ]

sujatha.sivakumar, thanks for reminding on MDEV-14760 analysis.
Just to make it utmost clear, Few variables get replicated - through Query_log_event::"status_var".

Comment by Elena Stepanova [ 2020-06-24 ]

sujatha.sivakumar,

I've seen this, and I know that MDEV-14760 is leaning towards being not a bug. I was responding to the previous Sergei's comment about possible use of set statement to solve this issue. If it's ignored by the slave and is going to stay ignored, then it probably won't solve the problem.

Comment by Michael Widenius [ 2020-06-26 ]

The simplest solution is to introduce MARIADB_DATE as a synonym for DATE and to be used in ORACLE mode in case of SHOW CREATE when a original MariaDB date is used.

Comment by Alexander Barkov [ 2020-06-26 ]

After a discussion with Monty and Serg, we decided to fix it in 10.3 to 10.5 in the following way, so this script:

SET sql_mode=DEFAULT;
 
CREATE OR REPLACE TABLE t1 (a DATE);
 
SET sql_mode=ORACLE;
 
DESCRIBE t1;

will display:

+-------+--------------+------+-----+---------+-------+
 
| Field | Type         | Null | Key | Default | Extra |
 
+-------+--------------+------+-----+---------+-------+
 
| a     | date_mariadb | YES  |     | NULL    |       |
 
+-------+--------------+------+-----+---------+-------+

This will automatically fix this problem.

In 10.6 we'll possibly introduce a more generalized solution like MDEV-23023 (but this is not decided yet though).

Comment by Alexander Barkov [ 2020-06-29 ]

The same problem is repeatable with with sql_mode=MAXDB, which translates TIMESTAMP to DATETIME:

--source include/have_binlog_format_row.inc
 
--source include/master-slave.inc
 
 
 
CREATE TABLE t1 (a TIMESTAMP);
 
INSERT INTO t1 VALUES (NULL);
 
SET SQL_MODE= 'MAXDB';
 
CREATE TABLE t2 SELECT * FROM t1;
 
 
 
--sync_slave_with_master
 
 
# Cleanup
 
--connection master
 
DROP TABLE t1, t2;
 
 
 
--source include/rpl_end.inc

Replication fails with this error:

Column 0 of table 'test.t2' cannot be converted from type 'timestamp' to type 'datetime'

Comment by Alexander Barkov [ 2020-07-08 ]

Patches:

Generated at Thu Feb 08 08:53:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.