|
I've two mysql backend server 'G0' and 'G1'. And there is a test table on them respectively.
G0:
mysql> select * from test.t1;
|
+----+--------+
|
| id | node |
|
+----+--------+
|
| 2 | DB-S/2 |
|
| 4 | DB-S/4 |
|
+----+--------+
|
2 rows in set (0.00 sec)
|
G1:
+----+--------+
|
| id | node |
|
+----+--------+
|
| 1 | DB-S/1 |
|
| 3 | DB-S/3 |
|
+----+--------+
|
2 rows in set (0.00 sec)
|
Both G0 and G1 are running MySQL 8.0.18.
I'm then using the spider engine offered by MariaDB 10.4 to combine them like this:
CREATE TABLE t1 (
|
id BIGINT PRIMARY KEY,
|
node TEXT
|
) ENGINE=Spider
|
COMMENT='wrapper "mysql"'
|
PARTITION BY HASH (id) (
|
PARTITION par0 COMMENT = 'srv "G0", tbl "t1"',
|
PARTITION par1 COMMENT = 'srv "G1", tbl "t1"'
|
);
|
It's working well for a simple query like this:
MariaDB [(none)]> select * from test.t1;
|
+----+--------+
|
| id | node |
|
+----+--------+
|
| 2 | DB-S/2 |
|
| 4 | DB-S/4 |
|
| 1 | DB-S/1 |
|
| 3 | DB-S/3 |
|
+----+--------+
|
4 rows in set (0.019 sec)
|
But it broken with a simple where condition:
MariaDB [(none)]> select * from test.t1 where id > 2;
|
ERROR 12710 (HY000): Invalid information from remote table 'test.t1'
|
The corresponding log shows (I've a HAProxy listen on the localhost to connect to G0 and G1):
200516 9:06:50 76 Query select * from test.t1 where id > 2
|
19 Query mysql 127.0.0.1 show table status from `test` like 't1'
|
17 Query mysql 127.0.0.1 show table status from `test` like 't1'
|
76 Query mysql 127.0.0.1 set session transaction isolation level repeatable read;set session sql_log_off = 0;set session wait_timeout = 604800;set session sql_mode = 'strict_trans_tables,error_for_division_by_zero,no_engine_substitution';start transaction
|
76 Query mysql 127.0.0.1 show index from `test`.`t1`
|
76 Query mysql 127.0.0.1 rollback
|
It's seems that spider could not recognize the mysql 8.0's index info, I run it manually at G0:
mysql> show index from `test`.`t1`;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
| t1 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
1 row in set (0.00 sec)
|
There are two new columns "Visible" and "Expression" added since 8.0, is this the cause of the problem?
If yes, could we let the spider engine accept the new index info format?
|