WL#5151: Conversion between different types when replicating
Affects: Server-5.5
—
Status: Complete
Rationale ========= In statement-based replication, it is possible to replicate between tables with different definitions since the statement is just executed on the slave. For row-based replication, the raw data is replicated, so it is currently not possible to replicate between columns with different types. Typical examples are replicating between different types of integers, different types of decimal numbers, or from one string type to a shorter string type. See also BUG#46584. Description =========== This worklog strives to implement promotion and demotion between columns of different types when using row-based replication, where we refer to the type of the column on the master as the *source type* and the column on the slave as the *target type*. - Conversion between any of the integer types TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT (including unsigned versions of them) shall be supported. - Conversion between any of the decimal types DECIMAL, FLOAT, DOUBLE, NUMERIC shall be supported. - Conversion between any of the string types CHAR(N), VARCHAR(N), TEXT; even between different lengths of types shall be supported. - Conversion between any of the binary data types BINARY(N), VARBINARY(N), BLOB; even between different lengths shall be supported. - Conversion between bit types BIT(X) and BIT(Y) for any values of X and Y shall be supported. - All other conversions shall not be permitted. Lossy conversion ---------------- In the event that the target type cannot represent the value being inserted, a decision has to be made on how to handle the conversion. We call this conversion *lossy conversion*. - For integer types, lossy conversion can either be implemented using saturated arithmetics (assigning the max value for the type to the field), using NULL for columns that can be NULL, or using the default value for the column. - For string and binary types, lossy conversion is done by taking the initial prefix of the string and insert that. - For decimal types, lossy conversion can either be implemented using truncation or rounding. - For the BIT type, the lossy conversion can be handled by either taking the least significant part or the most significant part of the source value. Options ------- The implementation shall support the options of: - Requiring identical type on slave - Permitting lossy conversions: replication will truncate (for a suitable definition of truncate) the value but not stop. - Not permitting lossy conversions: replication will not perform the conversion on the value and instead stop replication if truncation is necessary. Testing ------- The tests provided by the developer, together with the overall testing of the source tree, covers the new feature. Tests of WL#4840 should be updated according new behavior of replication after push the feature. QA approved. -- Serge, 2010-01-19
Open issues
===========
Value or type checking
----------------------
Shall the code in Example 1 throw an error in the case that we allow promotions
but not demotions?
If we just consider the *value* it can be fully contained in the target type and
does not introduce a problem in the conversion. The table, however, have a
narrower type on the slave than on the master and reporting an error in this
case could eliminate the risk of a problem later, when a larger value is
produced that does not fit in the target type.
:Decision: Base conversions on the type of the column [Mats].
Description
===========
The purpose of the worklog is to support conversion from columns of the master
to columns on the slave when using row-based replication. We refer to the type
of the column on the master as the *source type* and the type of the column on
the slave as the *target type*.
Note that for the master, we are concerned with the type of the column on the
master and not with the value being sent from the master. The distinction is
most easily seen by considering the code in Example 1, where a value that can be
fully contained in both the target type and the source type is being inserted on
the master.
Example 1:
master> CREATE TABLE foo (a INT);
slave> CREATE TABLE foo (a TINYINT);
master> INSERT INTO foo VALUES (1);
In this case, it is an error to perform the conversion unless integers can be
demoted.
New Options
===========
A new global server variable ``SLAVE_TYPE_CONVERSIONS``is introduced. The
variable is a set of elements picked from the following list:
``ALL_LOSSY``
Conversions to a type that would mean loss of information
is allowed. Note that this conversion does not automatically
mean that non-lossy conversions are allowed, it just means
that either no conversion or lossy conversions are allowed so,
for example, enabling this conversion *only* will allow
conversion INT ==> TINY, but *not* TINY ==> INT.
``ALL_NON_LOSSY``
Conversions to a different type with a wider range than the
source type is allowed.
If the set is empty, types are required to be identical (to the extend that the
implementation can detect it). The caveat is added because, for example, since
the character set for a string is currently not replicated, it is impossible for
the slave to notice a difference in characters sets between strings on the
master and on the slave.
The default is '' (the empty set), which means that neither non-lossy nor lossy
conversions are allowed.
The slave have to be stopped and re-started for any changes to the variable to
take effect.
Examples::
# Do not allow any conversions
SET GLOBAL SLAVE_TYPE_CONVERSIONS = '';
# Use both lossy and non-lossy conversions
SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY,ALL_NON_LOSSY';
The semantics of the variable is picked to allow extensions of the variable by
introducing additional conversions. This will allow "splitting" existing
conversions by introducing new conversions that cover the original conversions.
For example, if two new conversions were introduced to allow lossy conversions
for the different types mentioned below, say ``LOSSY_INTEGER`` and
``LOSSY_STRING``, the effect would be that:
- ``ALL_LOSSY`` implies ``LOSSY_INTEGER``
- ``ALL_LOSSY`` implies ``LOSSY_STRING``
Supported Conversions
=====================
For statement-based replication the syntax of the query decides what is
permissible, so considering Example 1 above, the statement will replicate fine
since the value 1 can be inserted into the table on the slave. The disadvantage
of this approach is that subtle problems can occur if, for example, larger
values than what is supported on the slave are being inserted on the master.
Since the source and target types are not matched, these problems can occur late
in development, and even after deployment, due to intentional changes or due to
bugs.
For row-based replication we therefore compare the source and target
types of the columns on the master and slave and report an error if the types
are not convertible according to the conversions enabled.
Note that only the conversions listed below are supported in this worklog. In
particular, conversions from or to ``ENUM`` types or ``SET`` types is not part
of this worklog.
Integer types
-------------
Conversions between signed and unsigned versions of the integer types TINYINT,
SMALLINT, MEDIUMINT, INT, and BIGINT are supported as described in this section.
The ranges allowed by the integer types are given in the following table:
========= ===== ============ =============
Type Bytes Signed Unsigned
----- ------ ----- -------
Min Max Min Max
========= ===== ============ =============
TINYINT 1 -2^7 2^7-1 0 2^8-1
SMALLINT 2 -2^15 2^15-1 0 2^16-1
MEDIUMINT 3 -2^23 2^23-1 0 2^24-1
INT 4 -2^31 2^31-1 0 2^32-1
BIGINT 8 -2^63 2^63-1 0 2^64-1
Non-lossy conversions between integer types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Non-lossy conversions from type X to type Y are supported if and only if
``ALL_NON_LOSSY`` is in ``SLAVE_TYPE_CONVERSIONS`` and all values of type
X can be represented in type Y.
For example:
- ``TINYINT UNSIGNED`` to ``SMALLINT`` is allowed since the range [0, 2^8-1] is
a subset of the range [-2^15, 2^15-1].
- ``TINYINT UNSIGNED`` to ``TINYINT`` is not allowed since the range [0, 2^8-1]
is not a subset of the range [-2^7,2^7-1].
Lossy conversions between integer types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lossy conversions from type X to type Y are supported if and only if
``ALL_LOSSY`` is in ``SLAVE_TYPE_CONVERSIONS``.
When inserting a value into the target column:
- A value in the range of the target type will be inserted into the column.
- A value that is outside the range of the target type will be truncated to the
nearest legal value in range for the column.
Examples::
master> CREATE TABLE foo (a INT);
slave> CREATE TABLE foo (a TINYINT);
master> INSERT INTO foo VALUES (1); # Master: 1, Slave: 1
master> INSERT INTO foo VALUES (356); # Master: 356, Slave: 127
master> CREATE TABLE foo (a TINYINT);
slave> CREATE TABLE foo (a TINYINT UNSIGNED);
master> INSERT INTO foo VALUES (1); # Master: 1, Slave: 1
master> INSERT INTO foo VALUES (-1); # Master: -1, Slave: 0
Decimal types
-------------
Conversions between decimal types FLOAT, DOUBLE, and DECIMAL(M,D) are supported
as described in this section.
The REAL type is a synonym for either FLOAT or DOUBLE depending on the whether
``FLOAT_AS_REAL`` is set.
If parameters are supplied to FLOAT, they are only used to alter the display of
the value and to select either FLOAT representation or DOUBLE representation.
These cases are covered by all the decimal types listed above.
The NUMERIC(M,D) type is an alias for DECIMAL(M,D).
Non-lossy conversions between decimal types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Non-lossy conversions are supported if and only if ``ALL_NON_LOSSY`` is in
``SLAVE_TYPE_CONVERSIONS``. The following table lists all non-lossy conversions:
============= =============== ===================
From To Condition
============= =============== ===================
FLOAT DOUBLE
DECIMAL(M,D) DECIMAL(M',D') M' > M and D' >= D
============= =============== ===================
Note that non-lossy conversions from floating-point types to fixed-point types
are not allowed since they can potentially lose precision.
Also note that conversions from DECIMAL(M,D) to DECIMAL(M,D) is not considered a
non-lossy conversion since they are identical types.
Lossy conversions between decimal types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lossy conversions are supported if and only if ``ALL_LOSSY`` is in
``SLAVE_TYPE_CONVERSIONS``. The following table lists all lossy conversions:
============= =============== ===================
From To Condition
============= =============== ===================
DOUBLE FLOAT
DOUBLE DECIMAL(M,D)
FLOAT DECIMAL(M,D)
DECIMAL(M,D) DOUBLE
DECIMAL(M,D) FLOAT
DECIMAL(M,D) DECIMAL(M',D') M' < M or D' < D
============= =============== ===================
If a value to be stored cannot be fit in the target type, the values is rounded
according to the rounding rules defined for the server (Section 11.14.4.
"Rounding Behavior" in the 5.1 manual).
Also note that conversions from DECIMAL(M,D) to DECIMAL(M,D) is not considered a
lossy conversion since they are identical types.
String types
------------
Conversions between string types CHAR(N), VARCHAR(N), TINYTEXT, TEXT,
MEDIUMTEXT, and LONGTEXT are supported as described in this section.
With each column of the above types is associated a character set. Note that
replication between columns of different character sets is *not* supported and
that no check is (currently) made to ensure that the strings have the same
characters set. This means that attempts to convert between columns of different
character sets is *undefined*.
Conversions between columns of the different types given above is supported, and
we will collectively let STRING(N) denote an arbitrary type as given above (that
is, it can be either CHAR(N), VARCHAR(N), TINYTEXT, TEXT, MEDIUMTEXT, or
LONGTEXT). For the TEXT types, N is given according to the following table:
========== ======
Type N
========== ======
TINYTEXT 2^8-1
TEXT 2^16-1
MEDIUMTEXT 2^24-1
LONGTEXT 2^32-1
========== ======
Non-lossy conversions between string types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Non-lossy conversions from STRING(N) to STRING'(M), where M > N or M = N and
STRING != STRING', is supported if and only if ``ALL_NON_LOSSY`` is in
``SLAVE_TYPE_CONVERSIONS``.
Examples::
master> CREATE TABLE foo (a CHAR(10));
slave> CREATE TABLE foo (a CHAR(20));
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
master> CREATE TABLE foo (a CHAR(10));
slave> CREATE TABLE foo (a VARCHAR(20));
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
master> CREATE TABLE foo (a CHAR(20));
slave> CREATE TABLE foo (a VARCHAR(20));
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
master> CREATE TABLE foo (a CHAR(10));
slave> CREATE TABLE foo (a TEXT);
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
Note that if M = N and STRING = STRING' it is not considered a non-lossy
conversion.
Lossy conversions between string types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lossy conversion from STRING(M) to STRING'(N), where M > N, is supported if and
only if ``ALL_LOSSY`` is in ``SLAVE_TYPE_CONVERSIONS``.
If a string of length L is replicated to a column of type STRING'(N) and L > N,
the string is truncated. For multi-byte character sets, only full characters are
stored in the target column.
master> CREATE TABLE foo (a CHAR(30));
slave> CREATE TABLE foo (a CHAR(10));
master> INSERT INTO foo
-> VALUES ('Careful with that axe Eugene!');
# Master: 'Careful with that axe Eugene!'
# Slave: 'Careful wi'
master> CREATE TABLE foo (a CHAR(30));
slave> CREATE TABLE foo (a VARCHAR(10));
master> INSERT INTO foo
-> VALUES ('Careful with that axe Eugene!');
# Master: 'Careful with that axe Eugene!'
# Slave: 'Careful wi'
master> CREATE TABLE foo (a TEXT);
slave> CREATE TABLE foo (a CHAR(10));
master> INSERT INTO foo
-> VALUES ('Careful with that axe Eugene!');
# Master: 'Careful with that axe Eugene!'
# Slave: 'Careful wi'
Note that the case when M = N and STRING = STRING' is not considered a lossy
conversion.
Binary types
------------
Conversion between binary types BINARY(N), VARBINARY(N), TINYBLOB, BLOB,
MEDIUMBLOB, and LONGBLOB are supported as described in this section.
In contrast to the string types, there is no character set associated with the
column and the length given is in bytes.
Conversions between columns of the different types given above is supported, and
we will collectively let BINARY(N) denote an arbitrary type as given above (that
is, it can be either BINARY(N), VARBINARY(N), or a BLOB). For the BLOB types, N
is given according to the following table:
========== ======
Type N
========== ======
TINYBLOB 2^8-1
BLOB 2^16-1
MEDIUMBLOB 2^24-1
LONGBLOB 2^32-1
========== ======
Non-lossy conversions between binary types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Non-lossy conversions from BINARY(N) to BINARY'(M), where M > N or M = N and
BINARY != BINARY', is supported if and only if ``ALL_NON_LOSSY`` is in
``SLAVE_TYPE_CONVERSIONS``.
Examples::
master> CREATE TABLE foo (a BINARY(10));
slave> CREATE TABLE foo (a BINARY(20));
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
master> CREATE TABLE foo (a BINARY(10));
slave> CREATE TABLE foo (a VARBINARY(20));
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
master> CREATE TABLE foo (a BINARY(20));
slave> CREATE TABLE foo (a VARBINARY(20));
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
master> CREATE TABLE foo (a BINARY(10));
slave> CREATE TABLE foo (a BLOB);
master> INSERT INTO foo VALUES ('Hi!'); # Master: 'Hi!', Slave: 'Hi!'
Lossy conversions between binary types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lossy conversion from BINARY(M) to BINARY'(N), where M > N, is supported if and
only if ``ALL_LOSSY`` is in ``SLAVE_TYPE_CONVERSIONS``.
If a binary string of length L is replicated to a column of type BINARY'(N) and
L > N, the string is truncated.
Bit type
--------
Conversion between BIT types are supported as described in this section.
Non-lossy conversions between bit types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Non-lossy conversions from type BIT(N) to BIT(M), for M > N, are supported if
and only if ``ALL_NON_LOSSY`` is in ``SLAVE_TYPE_CONVERSIONS``.
When inserting a value from a BIT(N) column into a BIT(M) column, where M > N,
the most significant bits of the BIT(M) columns are clear (set to zero) and the
N bits of the BIT(N) value is set as the least significant bits of the BIT(M)
column.
Lossy conversions between bit types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lossy conversions from a BIT(M) type to a BIT(N) type, for M > N, is supported
if and only if ``ALL_LOSSY`` is in ``SLAVE_TYPE_CONVERSIONS``.
When inserting a value from a BIT(M) column into a BIT(N) column, for M > N, the
maximum possible value for the column is assigned, i.e., an all-set value is
assigned.
Note that this is a very drafty specification.
Performing conversions on the slave
-----------------------------------
The pack and unpack functions for the fields are designed to unpack from an
arbitrary source and write to an arbitrary place. To be able to unpack the data
from the master and store it in the slave table, there are basically two approaches:
1. Create a fake table on the slave and use that to unpack the data from the
master to that table. The data can then be read from the record of the fake
table and inserted into the table on the slave.
2. Rewrite the packed format for the table on the slave so that unpack() can be
called to unpack the correct format into the slave table.
For this worklog, the first alternative is chosen. It offers the best
flexibility w.r.t. future extensions and require a minimum of maintenance since
it builds on existing code.
- Whenever a table map is seen, conversions are allowed, and the table
definition is different on the master and the slave, create a fake table
and save it for later use.
The table will contain a field definition for each field that require
conversion, we means that it is easy to check if the conversion should
be applied by just checking if the field pointer is non-NULL.
This functionality can be added to the table_def class, since it maintains
the information about the master definition.
Note that the fake table have to be saved elsewhere, since the table
definition instance is destroyed once the tables are open. We elect to store
this in a new field of TABLE_LIST and then copy it to the table_map when the
tables have been opened.
- Create a function that decide if conversions are required, i.e., if the
type of the table on the master and the slave differs.
If the definitions differ, return a function pointer that performs the
conversion, otherwise, return NULL.
- When unpacking the row, pick either the real table (if no conversion is
required) or the fake table (if conversions are required) and unpack the
field.
If conversions were required, the field from the fake table have to be
converted using the returned function pointer. Otherwise, the field is already
placed correctly.
Slave processing
----------------
Down is a rough outline of how table definition processing is handled, and the
additions are marked with + before the line. Just to confuse you, I am using
Python with decorators for the pseudo-code. (During drafting, lines that are
incorrect are preceded with >>> and followed by <<<).
tables_to_lock = []
thd = THD()
@event
def Table_map_event(name, table_id, coltype, field_metadata, null_bits):
tabledef = TableDef(coltype, field_metadata, null_bits)
elem = TABLE_LIST(name=name, table_id=table_id, tabledef=tabledef)
tables_to_lock.append(elem)
@event
def Rows_event():
if not thd.lock:
thd.lock = simple_open_n_lock_tables(thd, tables_to_lock)
for elem in tables_to_lock:
>>>
if not elem.table_def.compatible_with(elem.table):
<<<
raise WrongTableDef(elem.tabledef, elem.table)
for elem in tables_to_lock:
- table_map[elem.table_id] = (elem.table_id, elem.table)
+ table_map[elem.table_id] = (elem.table_id, elem.table, elem.tmp_table)
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.