SQL Hint Not Working

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: SQL Hint不好使

| username: HACK

To improve efficiency, please provide the following information. Clear problem descriptions can be resolved faster:
[TiDB Usage Environment] Online, Testing, Research
[TiDB Version]
[Encountered Problem]
In an environment with only one TiFlash server node, I shut down this TiFlash and then executed count(). Because the only TiFlash service was shut down, an error was reported:
mysql> select count() from sbtest1;
ERROR 9012 (HY000): TiFlash server timeout

I want to use a hint to make this count go through the TiKV replica.
When connecting to the MySQL client, add the --comments option:
$ /home/tidb/tidb_init/mysql-5.7.26-el7-x86_64/bin/mysql -h xxx.xxx.xxx.xxx -uroot -P 4000 -p --comments

mysql> select /*+ read_from_storage(tikv[sbtest1]) / count() from sbtest1;
ERROR 9012 (HY000): TiFlash server timeout

It didn’t work; it still went through the TiFlash replica.

[Reproduction Path] What operations were performed to encounter the problem
[Problem Phenomenon and Impact]

[Attachments]

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: h5n1 | Original post link

read_from_storage(tikv[sbtest1])

3
| username: HACK | Original post link

Typed incorrectly
mysql> select /*+ read_from_storage(tikv[sbtest1]) */ count(*) from bench.sbtest1;
ERROR 9012 (HY000): TiFlash server timeout

4
| username: h5n1 | Original post link

Try adding the database name to /*+ READ_FROM_STORAGE(tikv[bench.sbtest1]) */

5
| username: HACK | Original post link

Adding the database name worked, :sweat_smile:
I didn’t see any explanation in the documentation either.

mysql> select /+ read_from_storage(tikv[bench.sbtest1]) / count() from bench.sbtest1;
±---------+
| count() |
±---------+
| 1232864 |
±---------+
1 row in set (0.71 sec)

6
| username: h5n1 | Original post link

Indeed, it wasn’t explained.

7
| username: forever | Original post link

This should be considered a bug. Sometimes the project requires it without the database name.

8
| username: h5n1 | Original post link

Add an alias, and use the alias.

9
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.