Oracle 的特殊 SQL 紀錄

最近因為工作需要，去接觸老系統的資料庫

該資料庫是 Oracle 11，裡面有很多特殊的 SQL 語法。

在此紀錄一下

MySQL / Clickhouse 查詢「每個ＯＯ中，前幾個ＸＸ」的情境

clickhouse 有個很方便的語法——limit by

MySQL 連線太多次導致連線失敗

MySQL 有個機制是如果有 IP 連線失敗多次，就會將該 IP block

檢查

檢查方式：

mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.09 sec)

mysql>

以上表示一個 IP 最多可以失敗 100 次

至於有哪些 IP 發生這問題，要去 performance_schema.host_cache （需要特別開權限才能進該 schema）看

mysql> select IP, HOST, SUM_CONNECT_ERRORS, COUNT_HOST_BLOCKED_ERRORS, FIRST_ERROR_SEEN, LAST_ERROR_SEEN from performance_schema.host_cache;
+----------------+------+--------------------+---------------------------+---------------------+---------------------+
| IP             | HOST | SUM_CONNECT_ERRORS | COUNT_HOST_BLOCKED_ERRORS | FIRST_ERROR_SEEN    | LAST_ERROR_SEEN     |
+----------------+------+--------------------+---------------------------+---------------------+---------------------+
| 10.8.8.237     | NULL |                101 |                    577863 | 2023-07-27 15:12:11 | 2023-07-28 03:28:00 |
| 10.200.21.54   | NULL |                100 |                      3276 | 2023-07-28 00:44:19 | 2023-07-28 03:28:00 |
| 10.240.2.19    | NULL |                  0 |                         0 | 2023-07-28 03:26:01 | 2023-07-28 03:26:01 |
| 10.200.26.30   | NULL |                101 |                      5967 | 2023-07-28 00:56:40 | 2023-07-28 03:25:16 |
| 10.200.23.34   | NULL |                  0 |                         0 | 2023-07-28 01:57:29 | 2023-07-28 01:57:29 |
| 10.200.21.62   | NULL |                  0 |                         0 | 2023-07-28 03:08:13 | 2023-07-28 03:08:13 |
| 10.200.206.216 | NULL |                  0 |                         0 | 2023-07-28 02:53:28 | 2023-07-28 02:53:28 |
| 10.200.21.63   | NULL |                  0 |                         0 | 2023-07-28 00:37:29 | 2023-07-28 00:37:29 |
+----------------+------+--------------------+---------------------------+---------------------+---------------------+
8 rows in set (0.08 sec)

mysql>

這樣可以看到 10.8.8.237、10.200.21.54、10.200.26.30這 3 個 IP 都出現這問題。

解決辦法

解決辦法是執行以下 SQL

mysql> flush hosts;

但該指令有限制要最高權限才能執行，沒有權限者會出現以下 error

ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

參考資料

KuBee, (2020, September 10). MySQL-客户端连接被拒绝问题. 博客园. https://www.cnblogs.com/binliubiao/p/13648294.html

yinxuep, (2019, December 11). Mysql连接错误过多的问题处理_mysql sum_connect_errors. CSDN博客. https://blog.csdn.net/yinxuep/article/details/103501044

Clickhouse 實現 MySQL 的 group_concat() 的作法

MySQL 有 group-concat function，能將 group by 後的資料合併，方便查詢、處理。

例如

mysql> select id, group_concat(arr) from (
    -> select 1 as id, 'orange' as arr
    -> union all
    -> select 1, 'banana'
    -> union all
    -> select 2, 'apple'
    -> union all
    -> select 1, 'banana'
    -> union all
    -> select 3, 'peach'
    -> union all
    -> select 1, 'grava'
    -> union all
    -> select 4, 'pineapple'
    -> union all
    -> select 4, 'pear'
    -> ) as f
    -> group by id;
+----+----------------------------+
| id | group_concat(arr)          |
+----+----------------------------+
|  1 | banana,grava,orange,banana |
|  2 | apple                      |
|  3 | peach                      |
|  4 | pineapple,pear             |
+----+----------------------------+
4 rows in set (0.00 sec)

然而這畢竟是 MySQL 特有的 function， clickhouse 並無此 function，要做到同樣的事就得用其他 function 來處理

You can't specify target table '資料庫表名' for update in FROM clause

這是最近使用 Hibernate 時遇到的錯誤訊息
查了一下，結果這是 MySQL 特有的問題。說是不能夠在變更資料時 select 同一個 table……

使用虛擬機安裝MySQL與設定

使用環境：

• local machine：win8
• 虛擬機：VMWare Player
• 虛擬機OS：Xubuntu 15.04 i386