Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.16, 10.3.18, 10.3(EOL), 10.4(EOL)
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
|
Attachments
Issue Links
- is blocked by
-
-
- Closed
-
- relates to
-
MDEV-23353 Qualified data types in SP
-
- Open
-
-
-
- Open
-
-
-
- Open
-
Activity
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.
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).
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'
|
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.