hhdb数据库介绍(9-25)

<property name="idcId">2</property><!-- 机房ID, 1:中心机房，2:容灾机房 -->
<property name="idcNodeHost">192.168.220.188:3325,192.168.220.189:3325</property><!-- 另一个机房的连接信息（Computer node info in the other IDC）-->

<property name="idleTimeout">28800</property><!-- 前端空闲连接超时时间，单位：秒-->

mysql > show processlist;
+----+------+-----------------------+--------------------+---------+------+-----------+------------------+
| Id | User | Host                  | db                 | Command | Time | State     | Info             |
+----+------+-----------------------+--------------------+---------+------+-----------+------------------+
| 9  | root | 192.168.220.211:26568 | NULL               | Query   | 0    | executing | show processlist |
| 7  | ztm  | 192.168.220.211:26470 | INFORMATION_SCHEMA | Sleep   | 59   |           | NULL             |
+----+------+-----------------------+--------------------+---------+------+-----------+------------------+
2 rows in set (0.00 sec)

mysql > show processlist;
+----+------+-----------------------+--------------------+---------+------+-----------+------------------+
| Id | User | Host                  | db                 | Command | Time | State     | Info             |
+----+------+-----------------------+--------------------+---------+------+-----------+------------------+
| 9  | root | 192.168.220.211:26568 | NULL               | Query   | 0    | executing | show processlist |
| 7  | ztm  | 192.168.220.211:26470 | INFORMATION_SCHEMA | Sleep   | 60   |           | NULL             |
+----+------+-----------------------+--------------------+---------+------+-----------+------------------+
2 rows in set (0.00 sec)

mysql > show processlist;
+----+------+-----------------------+------+---------+------+-----------+------------------+
| Id | User | Host                  | db   | Command | Time | State     | Info             |
+----+------+-----------------------+------+---------+------+-----------+------------------+
| 9  | root | 192.168.220.211:26568 | NULL | Query   | 0    | executing | show processlist |
+----+------+-----------------------+------+---------+------+-----------+------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| DATABASE           |
+--------------------+
| INFORMATION_SCHEMA |
+--------------------+
1 row in set (0.00 sec)

mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 10
Current database: INFORMATION_SCHEMA

<property name="instanceReadOnly">0</property><!-- 是否开启只读实例，默认不开启(Enable instance readonly or not) -->

root@192.168.240.161:(none) 5.7.24 04:21:23> create database test;
ERROR 1289 (HY000): Command CREATE_DATABASE not allowed in Read-Only mode.

<property name="inplaceOnlineDdlTakesPrecedence">1</property><!--      是否onlineDDL语句均优先按照inplace的模式执行(Whether onlineDDL statements are executed on inplace mode first)      -->

onlineDDL “alter table tablea add column c int;”
通过存储节点的general.log可查看下发到存储节点中的SQL如下：
存储节点版本为5.7时：
ALTER TABLE TABLEA ADD COLUMN C INT, ALGORITHM=INPLACE
存储节点版本为8.0时：
ALTER TABLE TABLEA ADD COLUMN C INT, ALGORITHM=INSTANT

<property name="ignoreSetGlobalVariables">false</property><!--   忽略设置全局参数(ignore set global variables)   -->

<property name="ignoreXaRu">false</property><!-- 设置为true时忽略XA模式下READ-UNCOMMITTED隔离级别的设置报错, 默认为false(Ignore unsupported error message of READ-UNCOMMITTED isolation setting in XA mode when the parameter is set to true, default is false) -->

mysql> select * from join_cross_a_jwy a inner join join_cross_b_jwy b on a.adnid between 108 and 110;

ERROR 1064 (HY000): joinable is not configured.

mysql> select a.adept from join_a_jwy a join join_b_jwy b on a.adept=b.bdept limit 5;

ERROR 1064 (HY000): joinable is not configured.

mysql> select a.adept from join_a_jwy a join join_b_jwy b on a.adept=b.bdept limit 5;
+-------+
| adept |
+-------+
| aa    |
| bb    |
| cc    |
+-------+
3 rows in set (0.03 sec)

<property name="joinBatchSize">3</property><!---JOIN等值查询时每批量转成IN查询的记录数 -->

select b.* from customer_auto_1 a join customer_auto_3 b on a.id=b.id where a.postcode=123456;

1993 Query SELECT B.`ID`, B.`name`, B.`telephone`, B.`provinceid`, B.`province`, B.`city`, B.`address`, B.`postcode`, B.`birthday`, b.id AS `hotdb_tmp_col_alias_1` FROM customer_auto_3 AS b WHERE B.ID IN **(4064622, 4068449, 4071461)**
1993 Query SELECT B.`ID`, B.`name`, B.`telephone`, B.`provinceid`, B.`province`, B.`city`, B.`address`, B.`postcode`, B.`birthday`, b.id AS `hotdb_tmp_col_alias_1` FROM customer_auto_3 AS b WHERE B.ID IN **(4043006, 4053408, 4056542)**
...省略更多...

root> pwd

/usr/local/hhdb-14.0.0/hgdb-server/HHDB-TEMP
You have mail in /var/spool/mail/root

root> ll

-rw-r--r-- 1 root root 8778410 May 9 17:28 positions_5302007528422328273.tmp
-rw-r--r-- 1 root root 141868981 May 9 17:28 row_411809270296834018.tmp
-rw-r--r-- 1 root root 26113612 May 9 18:01 row_4342139033645193593.tmp

<property name="joinLoopSize">1000</property><!-- 使用BNL算法做JOIN时各节点每批次查询数量 -->

select * from bn_a_Jwy as a inner join bn_b_jwy as b on a.a=b.a limit 9000;

1187022 Query SELECT A.id, A.a, A.bchar, A.cdeci, A.dtime FROM bn_a_jwy AS a ORDER BY A.ID LIMIT 1001
1187022 Query SELECT C.id, C.a, C.bchar, C.cdeci, C.dtime FROM bn_c_jwy AS c WHERE C.id IN (0) ORDER BY C.ID LIMIT 0 , 1001
1187022 Query SELECT B.id, B.a, B.bchar, B.cdeci, B.dtime FROM bn_b_jwy AS b WHERE B.a COLLATE utf8_general_ci IN ('d') ORDER BY B.ID LIMIT 0 , 1001 ...省略更多...

<property name="joinThreadConcurrency">5</property><!-- 大数据量count from innerjoin时的并发处理线程个数 -->

<property name="keyStore">/server.jks</property><!-- 指定用于TLS连接的数据证书.jks文件的路径 -->

<property name="keyStorePass">BB5A70F75DD5FEB214A5623DD171CEEB</property><!-- 指定用于TLS连接的数据证书.jks文件的密码 -->

<property name="lockWaitTimeout">31536000</property> <!-- 元数据锁超时时间 -->

<property name="loginRetryTimes">0</property><!--账户登录失败重试次数限制(Account login failure retry limit) -->

ERROR 1045 (HY000): Account is locked for user '<当前用户名>'

unlock account '当前用户名'@'用户名所对应的主机';