Skip to content

Commit 8f53149

Browse files
authored
[opt](compatibility) report MySQL-compatible key metadata so clients can find the primary key (#66732)
### What problem does this PR solve? Issue Number: close #31206 Problem Summary: A Doris `UNIQUE KEY` or `AGGREGATE KEY` table makes its key columns unique, but that fact never reached any of the places a MySQL client looks for it, so no driver could discover a primary key: - `SHOW KEYS` / `SHOW INDEX` only listed secondary indexes, so a unique key table with no inverted index returned **nothing at all**. The output was also not the MySQL shape: one row per index with the column names joined by commas, and `Non_unique` / `Seq_in_index` / `Collation` / `Cardinality` / `Null` all empty strings. - `information_schema.STATISTICS`, `KEY_COLUMN_USAGE` and `TABLE_CONSTRAINTS` are declared on the FE but have no BE scanner, so they fall through to `SchemaDummyScanner` and are always empty. - `information_schema.COLUMNS.COLUMN_KEY` reported the table model (`UNI` / `AGG` / `DUP`) rather than MySQL's `PRI` / `UNI` / `MUL`. `AGG` and `DUP` do not exist in MySQL at all. #31206 reported this for JDBC. It also explains a report from a user linking a Doris table into Microsoft Access over the MySQL ODBC driver: without a primary key Access falls back to a read-only snapshot and downloads the whole table instead of using a keyset cursor. Connector/ODBC answers `SQLPrimaryKeys`, `SQLStatistics` and `SQLSpecialColumns` by running ``SHOW KEYS FROM `db`.`tbl` `` and reading the rows whose key name is exactly `PRIMARY` (`driver/catalog_no_i_s.cc`, `server_list_dbkeys()`); the `*_i_s()` variants delegate to the same code. Connector/J with `useInformationSchema=true` instead queries `INFORMATION_SCHEMA.STATISTICS ... WHERE INDEX_NAME='PRIMARY'`. Both paths are covered here. **How it works** `TableKeyMeta` becomes the single producer of these rows, so `SHOW KEYS` and the `information_schema` tables cannot drift apart. What counts as the primary key, in priority order: 1. a declared `PRIMARY KEY` constraint, if the table has one; 2. the key columns of a `UNIQUE KEY` or `AGGREGATE KEY` table — both models enforce uniqueness on the key. The key of a `DUPLICATE KEY` table is only a sort prefix and is **not** unique, so it is reported as a non-unique index named `DUPLICATE` instead. Reporting it as a primary key would let a client such as Access believe it can address a single row by it, which corrupts edits silently. An owner of a duplicate key table whose data really is unique can opt in with `ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (id)`. Also lets the generic schema table channel send SQL NULL. `SchemaScanner::insert_block_column` ignored `TCell.isNull` and unconditionally pushed `false` to the null map, so a column that does not apply to an index (`SUB_PART`, `PACKED`, `EXPRESSION`) had to report a stand-in value — and a `CARDINALITY` of 0 on an empty table reads to a client as "this index selects nothing". ### Release note Added session variable `enable_mysql_compatible_index_metadata` (default `false`). When enabled, `SHOW KEYS` / `SHOW INDEX` returns one row per indexed column in the MySQL layout and exposes the `UNIQUE KEY` / `AGGREGATE KEY` of a table as an index named `PRIMARY`, and `information_schema.columns.COLUMN_KEY` reports `PRI` / `UNI` / `MUL` instead of `UNI` / `AGG` / `DUP`. Set it globally to let MySQL ODBC/JDBC drivers and BI tools discover primary keys for every client of a cluster. `information_schema.STATISTICS`, `KEY_COLUMN_USAGE` and `TABLE_CONSTRAINTS` are now populated. They were always empty before, so this needs no switch. ### Check List (For Author) - Test - [x] Regression test - [ ] Unit Test - [x] Manual test (add detailed scripts or steps below) New suite `regression-test/suites/query_p0/show/test_show_index_mysql_compatible.groovy` covers UNIQUE(MOR) / UNIQUE(MOW) / AGGREGATE / DUPLICATE models, composite keys, nullable key columns, secondary indexes, declared PRIMARY KEY / UNIQUE / FOREIGN KEY constraints, the four spellings of the statement, and both states of the switch — including asserting that with the switch off the legacy output is unchanged. `test_query_sys_tables.groovy` gains scoped assertions on the three `information_schema` tables. Manual test against the table from #31206: ```sql CREATE TABLE example_tbl_unique2 ( `user_id` LARGEINT NOT NULL, `username` VARCHAR(50) NOT NULL, `city` VARCHAR(20) ) UNIQUE KEY(`user_id`) DISTRIBUTED BY HASH(`user_id`) BUCKETS 1; -- switch off: 0 rows, the behaviour reported in the issue SHOW KEYS FROM `cc_test`.`example_tbl_unique2`; SET enable_mysql_compatible_index_metadata = true; SHOW KEYS FROM `cc_test`.`example_tbl_unique2`; +---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | +---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+ | example_tbl_unique2 | 0 | PRIMARY | 1 | user_id | A | NULL | NULL | NULL | | BTREE | +---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+ -- Connector/J getPrimaryKeys(), verbatim, not behind the switch SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'cc_test' AND TABLE_NAME = 'example_tbl_unique2' AND INDEX_NAME = 'PRIMARY'; | internal | cc_test | example_tbl_unique2 | user_id | 1 | PRIMARY | ``` - Behavior changed: - [x] Yes. `SHOW KEYS` / `SHOW INDEX` and `information_schema.columns.COLUMN_KEY` change shape and values, but only when `enable_mysql_compatible_index_metadata` is turned on. With the default `false` their output is byte for byte what it is today, and the regression test asserts that. The three `information_schema` tables start returning rows unconditionally; they were always empty before, so nothing could have depended on their contents.
1 parent 788e488 commit 8f53149

38 files changed

Lines changed: 1935 additions & 794 deletions

‎be/src/exec/operator/schema_scan_operator.cpp‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,6 +124,11 @@ Status SchemaScanOperatorX::init(const TPlanNode& tnode, RuntimeState* state) {
124124
_common_scanner_param->thread_id = tnode.schema_scan_node.thread_id;
125125
}
126126

127+
if (tnode.schema_scan_node.__isset.mysql_compatible_index_metadata) {
128+
_common_scanner_param->mysql_compatible_index_metadata =
129+
tnode.schema_scan_node.mysql_compatible_index_metadata;
130+
}
131+
127132
if (tnode.schema_scan_node.__isset.catalog) {
128133
_common_scanner_param->catalog =
129134
state->obj_pool()->add(new std::string(tnode.schema_scan_node.catalog));

‎be/src/information_schema/schema_columns_scanner.cpp‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -324,6 +324,8 @@ Status SchemaColumnsScanner::_get_new_desc() {
324324
desc_params.__set_user_ip(*(_param->common_param->user_ip));
325325
}
326326
}
327+
desc_params.__set_mysql_compatible_index_metadata(
328+
_param->common_param->mysql_compatible_index_metadata);
327329

328330
if (nullptr != _param->common_param->ip && 0 != _param->common_param->port) {
329331
RETURN_IF_ERROR(SchemaHelper::describe_tables(*(_param->common_param->ip),

‎be/src/information_schema/schema_database_properties_scanner.cpp‎

Lines changed: 2 additions & 128 deletions
Original file line numberDiff line numberDiff line change
@@ -17,15 +17,7 @@
1717

1818
#include "information_schema/schema_database_properties_scanner.h"
1919

20-
#include "core/block/block.h"
21-
#include "core/data_type/data_type_factory.hpp"
2220
#include "core/string_ref.h"
23-
#include "information_schema/schema_helper.h"
24-
#include "runtime/cluster_info.h"
25-
#include "runtime/exec_env.h"
26-
#include "runtime/runtime_state.h"
27-
#include "util/client_cache.h"
28-
#include "util/thrift_rpc_helper.h"
2921

3022
namespace doris {
3123

@@ -37,125 +29,7 @@ std::vector<SchemaScanner::ColumnDesc> SchemaDatabasePropertiesScanner::_s_tbls_
3729
};
3830

3931
SchemaDatabasePropertiesScanner::SchemaDatabasePropertiesScanner()
40-
: SchemaScanner(_s_tbls_columns, TSchemaTableType::SCH_DATABASE_PROPERTIES) {}
41-
42-
Status SchemaDatabasePropertiesScanner::start(RuntimeState* state) {
43-
if (!_is_init) {
44-
return Status::InternalError("used before initialized.");
45-
}
46-
SCOPED_TIMER(_get_db_timer);
47-
TGetDbsParams db_params;
48-
if (_param->common_param->catalog) {
49-
db_params.__set_catalog(*(_param->common_param->catalog));
50-
}
51-
if (_param->common_param->current_user_ident) {
52-
db_params.__set_current_user_ident(*(_param->common_param->current_user_ident));
53-
}
54-
if (_param->common_param->ip && 0 != _param->common_param->port) {
55-
RETURN_IF_ERROR(SchemaHelper::get_db_names(
56-
*(_param->common_param->ip), _param->common_param->port, db_params, &_db_result));
57-
} else {
58-
return Status::InternalError("IP or port doesn't exists");
59-
}
60-
_block_rows_limit = state->batch_size();
61-
_rpc_timeout_ms = state->execution_timeout() * 1000;
62-
return Status::OK();
63-
}
64-
65-
Status SchemaDatabasePropertiesScanner::get_onedb_info_from_fe(int64_t dbId) {
66-
TNetworkAddress master_addr = ExecEnv::GetInstance()->cluster_info()->master_fe_addr;
67-
68-
TSchemaTableRequestParams schema_table_request_params;
69-
for (int i = 0; i < _s_tbls_columns.size(); i++) {
70-
schema_table_request_params.__isset.columns_name = true;
71-
schema_table_request_params.columns_name.emplace_back(_s_tbls_columns[i].name);
72-
}
73-
schema_table_request_params.__set_current_user_ident(*_param->common_param->current_user_ident);
74-
schema_table_request_params.__set_catalog(*_param->common_param->catalog);
75-
schema_table_request_params.__set_dbId(dbId);
76-
77-
TFetchSchemaTableDataRequest request;
78-
request.__set_schema_table_name(TSchemaTableName::DATABASE_PROPERTIES);
79-
request.__set_schema_table_params(schema_table_request_params);
80-
81-
TFetchSchemaTableDataResult result;
82-
RETURN_IF_ERROR(ThriftRpcHelper::rpc<FrontendServiceClient>(
83-
master_addr.hostname, master_addr.port,
84-
[&request, &result](FrontendServiceConnection& client) {
85-
client->fetchSchemaTableData(result, request);
86-
},
87-
_rpc_timeout_ms));
88-
89-
Status status(Status::create(result.status));
90-
if (!status.ok()) {
91-
return status;
92-
}
93-
std::vector<TRow> result_data = result.data_batch;
94-
95-
_dbproperties_block = Block::create_unique();
96-
for (int i = 0; i < _s_tbls_columns.size(); ++i) {
97-
auto data_type =
98-
DataTypeFactory::instance().create_data_type(_s_tbls_columns[i].type, true);
99-
_dbproperties_block->insert(ColumnWithTypeAndName(data_type->create_column(), data_type,
100-
_s_tbls_columns[i].name));
101-
}
102-
_dbproperties_block->reserve(_block_rows_limit);
103-
if (result_data.size() > 0) {
104-
auto col_size = result_data[0].column_value.size();
105-
if (col_size != _s_tbls_columns.size()) {
106-
return Status::InternalError<false>(
107-
"database properties schema is not match for FE and BE");
108-
}
109-
}
110-
111-
for (int i = 0; i < result_data.size(); i++) {
112-
TRow row = result_data[i];
113-
for (int j = 0; j < _s_tbls_columns.size(); j++) {
114-
RETURN_IF_ERROR(insert_block_column(row.column_value[j], j, _dbproperties_block.get(),
115-
_s_tbls_columns[j].type));
116-
}
117-
}
118-
return Status::OK();
119-
}
120-
121-
bool SchemaDatabasePropertiesScanner::check_and_mark_eos(bool* eos) const {
122-
if (_row_idx == _total_rows) {
123-
*eos = true;
124-
if (_db_index < _db_result.db_ids.size()) {
125-
*eos = false;
126-
}
127-
return true;
128-
}
129-
return false;
130-
}
131-
132-
Status SchemaDatabasePropertiesScanner::get_next_block_internal(Block* block, bool* eos) {
133-
if (!_is_init) {
134-
return Status::InternalError("Used before initialized.");
135-
}
136-
if (nullptr == block || nullptr == eos) {
137-
return Status::InternalError("input pointer is nullptr.");
138-
}
139-
if ((_dbproperties_block == nullptr) || (_row_idx == _total_rows)) {
140-
if (_db_index < _db_result.db_ids.size()) {
141-
RETURN_IF_ERROR(get_onedb_info_from_fe(_db_result.db_ids[_db_index]));
142-
_row_idx = 0;
143-
_total_rows = (int)_dbproperties_block->rows();
144-
_db_index++;
145-
}
146-
}
147-
if (check_and_mark_eos(eos)) {
148-
return Status::OK();
149-
}
150-
int current_batch_rows = std::min(_block_rows_limit, _total_rows - _row_idx);
151-
ScopedMutableBlock scoped_mblock(block);
152-
auto& mblock = scoped_mblock.mutable_block();
153-
RETURN_IF_ERROR(mblock.add_rows(_dbproperties_block.get(), _row_idx, current_batch_rows));
154-
_row_idx += current_batch_rows;
155-
if (!check_and_mark_eos(eos)) {
156-
*eos = false;
157-
}
158-
return Status::OK();
159-
}
32+
: SchemaPerDbScanner(_s_tbls_columns, TSchemaTableType::SCH_DATABASE_PROPERTIES,
33+
TSchemaTableName::DATABASE_PROPERTIES, "database properties") {}
16034

16135
} // namespace doris

‎be/src/information_schema/schema_database_properties_scanner.h‎

Lines changed: 3 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -16,38 +16,20 @@
1616
// under the License.
1717

1818
#pragma once
19-
#include <gen_cpp/FrontendService_types.h>
20-
2119
#include <vector>
2220

23-
#include "common/status.h"
24-
#include "information_schema/schema_scanner.h"
21+
#include "information_schema/schema_per_db_scanner.h"
2522

2623
namespace doris {
27-
class RuntimeState;
28-
class Block;
2924

30-
class SchemaDatabasePropertiesScanner : public SchemaScanner {
25+
class SchemaDatabasePropertiesScanner : public SchemaPerDbScanner {
3126
ENABLE_FACTORY_CREATOR(SchemaDatabasePropertiesScanner);
3227

3328
public:
3429
SchemaDatabasePropertiesScanner();
3530
~SchemaDatabasePropertiesScanner() override = default;
3631

37-
Status start(RuntimeState* state) override;
38-
Status get_next_block_internal(Block* block, bool* eos) override;
39-
4032
static std::vector<SchemaScanner::ColumnDesc> _s_tbls_columns;
41-
42-
private:
43-
Status get_onedb_info_from_fe(int64_t dbId);
44-
bool check_and_mark_eos(bool* eos) const;
45-
int _block_rows_limit = 4096;
46-
int _row_idx = 0;
47-
int _total_rows = 0;
48-
int _db_index = 0;
49-
TGetDbsResult _db_result;
50-
std::unique_ptr<Block> _dbproperties_block = nullptr;
51-
int _rpc_timeout_ms = 3000;
5233
};
34+
5335
} // namespace doris

‎be/src/information_schema/schema_helper.cpp‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -162,11 +162,13 @@ Status SchemaHelper::fetch_load_job(const std::string& ip, const int32_t port,
162162

163163
Status SchemaHelper::fetch_schema_table_data(const std::string& ip, const int32_t port,
164164
const TFetchSchemaTableDataRequest& request,
165-
TFetchSchemaTableDataResult* result) {
165+
TFetchSchemaTableDataResult* result, int timeout_ms) {
166166
return ThriftRpcHelper::rpc<FrontendServiceClient>(
167-
ip, port, [&request, &result](FrontendServiceConnection& client) {
167+
ip, port,
168+
[&request, &result](FrontendServiceConnection& client) {
168169
client->fetchSchemaTableData(*result, request);
169-
});
170+
},
171+
timeout_ms);
170172
}
171173

172174
Status SchemaHelper::get_master_keys(const std::string& ip, const int32_t port,

‎be/src/information_schema/schema_helper.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -99,9 +99,11 @@ class SchemaHelper {
9999
static Status fetch_load_job(const std::string& ip, const int32_t port,
100100
const TFetchLoadJobRequest& request, TFetchLoadJobResult* result);
101101

102+
// `timeout_ms` bounds how long to wait for the master FE. Callers pass the execution
103+
// timeout of the query they serve, so the RPC never outlives that query.
102104
static Status fetch_schema_table_data(const std::string& ip, const int32_t port,
103105
const TFetchSchemaTableDataRequest& request,
104-
TFetchSchemaTableDataResult* result);
106+
TFetchSchemaTableDataResult* result, int timeout_ms);
105107

106108
static Status get_master_keys(const std::string& ip, const int32_t port,
107109
const TGetEncryptionKeysRequest& request,
Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
#include "information_schema/schema_key_column_usage_scanner.h"
19+
20+
#include "core/string_ref.h"
21+
22+
namespace doris {
23+
24+
// Must stay in the same order and of the same types as the `key_column_usage` entry of
25+
// SchemaTable.TABLE_MAP on the FE, which builds these rows.
26+
std::vector<SchemaScanner::ColumnDesc> SchemaKeyColumnUsageScanner::_s_tbls_columns = {
27+
{"CONSTRAINT_CATALOG", TYPE_VARCHAR, sizeof(StringRef), true},
28+
{"CONSTRAINT_SCHEMA", TYPE_VARCHAR, sizeof(StringRef), true},
29+
{"CONSTRAINT_NAME", TYPE_VARCHAR, sizeof(StringRef), true},
30+
{"TABLE_CATALOG", TYPE_VARCHAR, sizeof(StringRef), true},
31+
{"TABLE_SCHEMA", TYPE_VARCHAR, sizeof(StringRef), true},
32+
{"TABLE_NAME", TYPE_VARCHAR, sizeof(StringRef), true},
33+
{"COLUMN_NAME", TYPE_VARCHAR, sizeof(StringRef), true},
34+
{"ORDINAL_POSITION", TYPE_BIGINT, sizeof(int64_t), true},
35+
{"POSITION_IN_UNIQUE_CONSTRAINT", TYPE_BIGINT, sizeof(int64_t), true},
36+
{"REFERENCED_TABLE_SCHEMA", TYPE_VARCHAR, sizeof(StringRef), true},
37+
{"REFERENCED_TABLE_NAME", TYPE_VARCHAR, sizeof(StringRef), true},
38+
{"REFERENCED_COLUMN_NAME", TYPE_VARCHAR, sizeof(StringRef), true},
39+
};
40+
41+
SchemaKeyColumnUsageScanner::SchemaKeyColumnUsageScanner()
42+
: SchemaPerDbScanner(_s_tbls_columns, TSchemaTableType::SCH_KEY_COLUMN_USAGE,
43+
TSchemaTableName::KEY_COLUMN_USAGE, "key column usage") {}
44+
45+
} // namespace doris
Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
#pragma once
19+
#include <vector>
20+
21+
#include "information_schema/schema_per_db_scanner.h"
22+
23+
namespace doris {
24+
25+
// information_schema.key_column_usage: the columns each key constraint of a table covers,
26+
// including what a foreign key points at.
27+
class SchemaKeyColumnUsageScanner : public SchemaPerDbScanner {
28+
ENABLE_FACTORY_CREATOR(SchemaKeyColumnUsageScanner);
29+
30+
public:
31+
SchemaKeyColumnUsageScanner();
32+
~SchemaKeyColumnUsageScanner() override = default;
33+
34+
static std::vector<SchemaScanner::ColumnDesc> _s_tbls_columns;
35+
};
36+
37+
} // namespace doris

0 commit comments

Comments
 (0)