TencentDB for MariaDB is fully compatible with open-source MariaDB.
TencentDB for MariaDB is highly compatible with MySQL 5.6; therefore, code, applications, drivers, and tools that apply to MySQL databases can be directly used in TencentDB for MariaDB with no or only slight change required.
GTID
of TencentDB for MariaDB is incompatible with that of MySQL 5.6, i.e., MySQL cannot be used as a slave database of TencentDB for MariaDB.
Binlogs in TencentDB for MariaDB are in row format, while in native MySQL 5.6, MariaDB 10.2.3, and their earlier versions, binlogs are in statement format by default.
CREAT TABLE ... SELECT
commandTo ensure that the CREAT TABLE ... SELECT
command can work properly in both row-based and command-based replication, this command in TencentDB for MariaDB will be converted to and executed as the CREAT OR RPLACE
command in a slave database. The advantage of this mechanism is that the slave database can run properly after recovery from downtime.
When you create tables by using the Create table ... Select from
statement, the differences between the default values of fields in varchar(N)
type are as follows:
NULL
.Default value of a decimal column: in MySQL 5.5 and 5.6, it is deduced to 0.00; in MariaDB 10.1, it is deduced to NULL.
Sample code:
---------------- MySQL 5.5 -----------------------
create table t1
select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f1` varchar(1) CHARACTER SET latin5 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
-------------------- MySQL 5.7 ---------------------------------
create table t1
select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f1` varchar(1) CHARACTER SET latin5 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
------------------- MariaDB 10.1* --------------------------------
create table t1
select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f1` varchar(1) CHARACTER SET latin5 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select
statements in subqueriesIn this statement: SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
SELECT MAX(x), 12
is considered as SELECT MAX(x), 12 from t1
if it is located after in
; it is considered as SELECT x, 12
if it is located after =
, where "x" is the alias of a
in the current row.SELECT MAX(x), 12
always equals SELECT x, 12
, where x
is the alias of a
in the current row.Sample code:
----------------- MySQL 5.5/5.6 -----------------------
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (11);
SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12)
1 0 1
2 0 1
11 1 1
--------------------------- MariaDB 10.1.* or MySQL 5.7------------------------------
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (11);
SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12)
1 0 0
2 0 0
11 1 1
NULL
for ALL
and SOME
In MySQL 5.5, the NULL
in 10 >= ALL (NULL, 1, 10)
or 1 <= ALL (NULL, 1, 10)
is skipped, i.e., it is considered as non-existent, because NULL
is incomparable.
In MySQL 5.7 and TencentDB for MariaDB, NULL
is an unknown value and the result in the comparisons above is unknown too; therefore, NULL
will be returned.
alter table inplace
operationWhen alter table
is used to change the sequence of columns only, the inplace
algorithm can be used in TencentDB for MariaDB but not in MySQL.
When inplace alter table
is executed in TencentDB for MariaDB, the result of running show create table t1
will be the same as that of running ALGORITHM=COPY
in MySQL.
Undefined behavior is a feature of behavior that can be implemented through any method in MySQL or TencentDB for MariaDB, which may vary by version without the need to notify users or be specified. Implementation of undefined behaviors by MySQL and TencentDB for MariaDB may produce the same or different results.
For such same or different results in the current and future versions, TencentDB for MariaDB does not guarantee the results or ensure the same kernel optimization. For more information, please see official description of undefined behaviors.
Sorting (order by
clause) of character-type columns is generally case-insensitive, which means that the order of fields with the same content but different letter cases will be undefined after sorting. You can use the BINARY
keyword to force implement case-sensitive sorting, i.e., ORDER BY BINARY column name
.
Sample code:
The sorting of the following samples in MySQL and TencentDB for MariaDB may be completely random:
mysql> SELECT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL order by email;
+-------+
| email |
+-------+
| email |
| eMail |
| EMail |
+-------+
3 rows in set (0.00 sec)
Auto_increment
field overflowUndefined behavior specific to InnoDB:
Note:
Do not insert (incorrect) numbers into an auto-increment column.
FOUND ROWS
The returned value of FOUND_ROWS()
will be accurate only when UNION ALL
is used in the query statement.
If only UNION
is used without ALL
, TencentDB for MariaDB will remove duplicates in the statistics, while MySQL will retain duplicates. If the UNION
query statement is used without the LIMIT
clause, the SQL_CALC_FOUND_ROWS
keyword will be ignored, and the returned result of FOUND_ROWS()
will be the number of rows in the temporary table created when UNION
is executed.
LOCK TABLES
statementThe LOCK TABLES
statement locks tables in the following method: first, all tables that need to be locked are sorted based on the internally-defined method; however, from user's perspective, the sorting order in MySQL and TencentDB for MariaDB is undefined. For example, if you write LOCK TABLES t1, t2, t3
, TencentDB for MariaDB and MySQL will not lock the tables according to the sequence of t1, t2, t3
.
This behavior is undefined in MySQL and TencentDB for MariaDB; therefore, they may use different methods to sort t1, t2, and t3 and lock them based on the resulting sequence.
Therefore, you should not rely on locking sequence to ensure accuracy in your stored procedures or query code, as this may cause deadlock.
RESET MASTER
statementYou cannot run RESET MASTER
when any duplicate slave server is running; otherwise, the behaviors of master and slave servers will be undefined (and not supported) in TencentDB for MariaDB and MySQL. Various predictable errors may or may not occur during execution of RESET MASTER
. The official development teams of TencentDB for MariaDB and MySQL do not consider these errors as bugs and are not responsible for any errors that actually occur in this way.
In MySQL 5.5, when variables in year and date types are compared, the date type will be converted to the year type. For example, "2011-01-01" will be converted to "2011".
In MySQL 5.7 and TencentDB for MariaDB, variables in date type will stay unchanged; therefore, comparisons with variables in year type are different.
Similarly, TencentDB for MariaDB cannot convert the time type to year type, while MySQL 5.6 uses the year part in the timestamp of the current session as the year value for every value in time type, which means that the year in the timestamp of the current session will be used every time a time-type value needs to be converted to year type.
unhex
, an empty string ('') will be returned in MySQL 5.5/5.6/5.7, while a question mark character (?) will be returned in MariaDB 10.1.UPDATE t1 SET a=unhex(code) ORDER BY code
statement assigns a value to field a
in table t1; however, some of the assignment operations will fail as unhex
can only recognize and convert byte strings within a certain range.a
in any row in t1; however, all assigned values will be still stored in t1.a
in any row in t1, i.e., all assigned values will be rolled back as well.unhex
is unable to find a corresponding character for a byte string, a question mark (?, i.e., 0x3F) will be returned; therefore, the operation will always succeed no matter whether the storage engine is InnoDB or MyISAM.insert into
statement, if the corresponding utf8mb4
character is not found:SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
When the above statements are used, the ways to process them are different in MySQL 5.5/5.6 and MySQL 5.7/MariaDB 10.1:
TIME
and the default precision of TIME
is 0; therefore, the values after the decimal point of CAST('10:11:12.098700' AS TIME)
will be lost.You can use the following statement to keep the time precision.
SELECT CAST(CAST('10:11:12.098700' AS TIME(6)) AS DECIMAL(20,6));
+-----------------------------------------------------------+
| CAST(CAST('10:11:12.098700' AS TIME(6)) AS DECIMAL(20,6)) |
+-----------------------------------------------------------+
| 101112.098700 |
+-----------------------------------------------------------+
Note:
The default precision of
TIME
is not consistent. If time precision is required, you should specify a precision of time for upgrade or migration.
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
In MySQL 5.5/5.6, NULL
will be returned; in MariaDB 10.1 and MySQL 5.7, 2016-08-03 23:38:58
will be returned.
TIMESTAMP()
is in time type, the returned value will be NULL
as MySQL 5.5 cannot automatically convert it to timestamp type.Parameters with the same variable name have the same main feature.
Parameter Name | MariaDB 10.1 | MySQL 5.6 |
---|---|---|
old_passwords | OFF | 0 |
tmpdir | /tmp/5cXm2hHsWi/mysqld.1 | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/tmp/mysqld.1 |
version | 10.1.9-MariaDB-log | 5.6.31-log |
slow_query_log_file | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/build_dongzhi/mysql-test/var/mysqld.1/mysqld-slow.log | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/mysqld.1/mysqld-slow.log |
table_definition_cache | 400 | 1400 |
datadir | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/build_dongzhi/mysql-test/var/mysqld.1/data/ | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/mysqld.1/data/ |
pid_file | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/build_dongzhi/mysql-test/var/run/mysqld.1.pid | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/run/mysqld.1.pid |
max_seeks_for_key | 4294967295 | 18446744073709500000 |
slave_load_tmpdir | /tmp/5cXm2hHsWi/mysqld.1 | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/tmp/mysqld.1 |
secure_file_priv | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/build_dongzhi/mysql-test/var/ | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/ |
sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
ssl_cert | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/mysql-test/std_data/server-cert.pem | /data/home/tdengine/dongzhi/src/mysql-server-5.6/mysql-test/std_data/server-cert.pem |
ssl_ca | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/mysql-test/std_data/cacert.pem | /data/home/tdengine/dongzhi/src/mysql-server-5.6/mysql-test/std_data/cacert.pem |
open_files_limit | 1024 | 4161 |
binlog_checksum | NONE | CRC32 |
basedir | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1 | /data/home/tdengine/dongzhi/src/mysql-server-5.6 |
query_alloc_block_size | 16384 | 8192 |
innodb_max_dirty_pages_pct | 75.000000 | 75 |
ssl_key | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/mysql-test/std_data/server-key.pem | /data/home/tdengine/dongzhi/src/mysql-server-5.6/mysql-test/std_data/server-key.pem |
myisam_sort_buffer_size | 134216704 | 8388608 |
skip_name_resolve | ON | OFF |
pseudo_thread_id | 3 | 2 |
character_sets_dir | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/sql/share/charsets/ | /data/home/tdengine/dongzhi/src/mysql-server-5.6/sql/share/charsets/ |
innodb_adaptive_flushing_lwm | 10 | 10 |
myisam_recover_options | DEFAULT | OFF |
performance_schema_max_statement_classes | 179 | 168 |
innodb_version | 5.6.26-74.0 | 5.6.31 |
max_write_lock_count | 4294967295 | 18446744073709500000 |
thread_cache_size | 0 | 9 |
innodb_checksum_algorithm | INNODB | innodb |
optimizer_switch |
index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on |
index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on, mrr_cost_based=on, block_nested_loop=on, batched_key_access=off, materialization=on, semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on |
timestamp | 1471938276 | 1471937901 |
general_log_file | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/build_dongzhi/mysql-test/var/mysqld.1/mysqld.log | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/mysqld.1/mysqld.log |
myisam_stats_method | NULLS_UNEQUAL | nulls_unequal |
innodb_log_compressed_pages | OFF | ON |
query_prealloc_size | 24576 | 0 |
rand_seed2 | 297895171 | 0 |
rand_seed1 | 605568929 | 0 |
socket | /tmp/5cXm2hHsWi/mysqld.1.sock | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/mysql-test/var/tmp/mysqld.1.sock |
innodb_max_dirty_pages_pct_lwm | 0.001 | 0 |
lc_messages_dir | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/build_dongzhi/sql/share/ | /data/home/tdengine/dongzhi/src/mysql-server-5.6/build_dongzhi/sql/share/ |
max_relay_log_size | 1073741824 | 0 |
plugin_dir | /data/home/tdengine/dongzhi/src/tdsql-mariadb-10.1.9-release1/lib/plugin/ | /data/home/tdengine/dongzhi/src/mysql-server-5.6/lib/plugin/ |
thread_stack | 294912 | 262144 |
Was this page helpful?