infiniDBコマンド集

*仕事で infiniDB を使うようになった。
SQL エンジンは、MySQL なのでなじみやすいところはあるが、infiniDB 独自のものも当然あるので、そのあたりで、使ったことのあるコマンドをまとめてみようと思う。*


> (shell) idbmysql


## エクステントマップの確認

その前に、calpontsysデータベースの確認

> mysql> use calpontsys
> Database changed
> 
> mysql> show tables;
> +----------------------+
> | Tables_in_calpontsys |
> +----------------------+
> | syscolumn            |
> | systable             |
> +----------------------+
> 2 rows in set (0.00 sec)
> 
> mysql> desc syscolumn;
> +-----------------+--------------+------+-----+---------+-------+
> | Field           | Type         | Null | Key | Default | Extra |
> +-----------------+--------------+------+-----+---------+-------+
> | schema          | varchar(128) | YES  |     | NULL    |       |
> | tablename       | varchar(128) | YES  |     | NULL    |       |
> | columnname      | varchar(128) | YES  |     | NULL    |       |
> | objectid        | int(11)      | YES  |     | NULL    |       |
> | dictobjectid    | int(11)      | YES  |     | NULL    |       |
> | listobjectid    | int(11)      | YES  |     | NULL    |       |
> | treeobjectid    | int(11)      | YES  |     | NULL    |       |
> | datatype        | int(11)      | YES  |     | NULL    |       |
> | columnlength    | int(11)      | YES  |     | NULL    |       |
> | columnposition  | int(11)      | YES  |     | NULL    |       |
> | lastupdate      | date         | YES  |     | NULL    |       |
> | defaultvalue    | varchar(64)  | YES  |     | NULL    |       |
> | nullable        | int(11)      | YES  |     | NULL    |       |
> | scale           | int(11)      | YES  |     | NULL    |       |
> | prec            | int(11)      | YES  |     | NULL    |       |
> | autoincrement   | char(1)      | YES  |     | NULL    |       |
> | distcount       | int(11)      | YES  |     | NULL    |       |
> | nullcount       | int(11)      | YES  |     | NULL    |       |
> | minvalue        | varchar(64)  | YES  |     | NULL    |       |
> | maxvalue        | varchar(64)  | YES  |     | NULL    |       |
> | compressiontype | int(11)      | YES  |     | NULL    |       |
> | nextvalue       | bigint(20)   | YES  |     | NULL    |       |
> +-----------------+--------------+------+-----+---------+-------+
> 22 rows in set (0.00 sec)
> 
> mysql> desc systable;
> +---------------+--------------+------+-----+---------+-------+
> | Field         | Type         | Null | Key | Default | Extra |
> +---------------+--------------+------+-----+---------+-------+
> | tablename     | varchar(128) | YES  |     | NULL    |       |
> | schema        | varchar(128) | YES  |     | NULL    |       |
> | objectid      | int(11)      | YES  |     | NULL    |       |
> | createdate    | date         | YES  |     | NULL    |       |
> | lastupdate    | date         | YES  |     | NULL    |       |
> | init          | int(11)      | YES  |     | NULL    |       |
> | next          | int(11)      | YES  |     | NULL    |       |
> | numofrows     | int(11)      | YES  |     | NULL    |       |
> | avgrowlen     | int(11)      | YES  |     | NULL    |       |
> | numofblocks   | int(11)      | YES  |     | NULL    |       |
> | autoincrement | int(11)      | YES  |     | NULL    |       |
> +---------------+--------------+------+-----+---------+-------+
> 11 rows in set (0.00 sec)
> 
> mysql> select * from systable where tablename='test10'\g
> +-----------+--------+----------+------------+------------+------+------+-----------+-----------+-------------+---------------+
> | tablename | schema | objectid | createdate | lastupdate | init | next | numofrows | avgrowlen | numofblocks | autoincrement |
> +-----------+--------+----------+------------+------------+------+------+-----------+-----------+-------------+---------------+
> | test10    | test   |     3493 | 2016-06-08 | NULL       | NULL | NULL |      NULL |      NULL |        NULL |             0 |
> +-----------+--------+----------+------------+------------+------+------+-----------+-----------+-------------+---------------+
> 1 row in set (0.02 sec)
> 

結構な勢いでNULLデータが。設定が悪いのかそもそも入ってないのか。。。

ここから実際の確認。

> (ObjectIDの確認)
> mysql> select
>     `schema`,
>     tablename,
>     columnname,
>     objectid
> from
>     calpontsys.syscolumn
> where
>     `schema` = 'test'
> and
>     tablename = 'test_01'
> and
>     columnname = 'column_01'
> ;
> +--------+-----------+------------+----------+
> | schema | tablename | columnname | objectid |
> +--------+-----------+------------+----------+
> | test   | test_01   | column_01  |     3881 |
> +--------+-----------+------------+----------+
> 1 row in set (0.02 sec)
> 
> (OSシェルに戻ってエクステントマップの確認)
> shell> editem -o 3881
> Col OID = 3881, NumExtents = 3, width = 4
> 5817344 - 5821439 (4096) min: 1453311000, max: 1453457400, seqNum: 245, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 0, HWM: 4095; status: avail
> 6865920 - 6870015 (4096) min: 1453457400, max: 1453602000, seqNum: 242, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 1, HWM: 4095; status: avail
> 8217600 - 8221695 (4096) min: 1453602000, max: 1453685400, seqNum: 140, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 2, HWM: 2363; status: avail


## ObjectIDからそのデータの最初のファイル位置を確認する

> mysql> use calpontsys
> Database changed
> 
> mysql> select columnname,objectid from syscolumn where `schema`='test' and tablename='test10' order by 2;
> +--------------+----------+
> | columnname   | objectid |
> +--------------+----------+
> | date         |     3494 |
> | time         |     3495 |
> | offset       |     3496 |
> | major        |     3497 |
> | major_n      |     3498 |
> | minor        |     3499 |
> | minor_n      |     3500 |
> | majorminor_n |     3501 |
> | node         |     3502 |
> | node_n       |     3503 |
> | item         |     3504 |
> | pname        |     3505 |
> | pvalue       |     3506 |
> +--------------+----------+
> 13 rows in set (0.01 sec)
> 
> (OSシェルに戻って)
> 
> (shell)> oid2file 3495
> /home/icm/insight/infinidb/data1/000.dir/000.dir/013.dir/167.dir
> 
> (shell)> ls -lR /home/icm/insight/infinidb/data1/000.dir/000.dir/013.dir/167.dir
> /home/test/infinidb/data1/000.dir/000.dir/013.dir/167.dir:
> total 20
> drwxr-xr-x 2 test test    4096 Jun  9 14:11 000.dir
> drwxr-xr-x 2 test test    4096 Jun  9 14:16 001.dir
> drwxr-xr-x 2 test test    4096 Jun  9 14:59 002.dir
> drwxr-xr-x 2 test test    4096 Jun  9 15:06 003.dir
> drwxr-xr-x 2 test test    4096 Jun  9 15:12 004.dir
> 
> /home/test/infinidb/data1/000.dir/000.dir/013.dir/167.dir/000.dir:
> total 13348
> -rw-r--r-- 1 test test    3416064 Jun  9 14:12 FILE000.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 14:13 FILE001.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 14:14 FILE002.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 14:14 FILE003.cdf
> 
> /home/test/infinidb/data1/000.dir/000.dir/013.dir/167.dir/001.dir:
> total 13344
> -rw-r--r-- 1 test test    3416064 Jun  9 14:55 FILE000.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 14:55 FILE001.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 14:56 FILE002.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 14:57 FILE003.cdf
> 
> /home/test/infinidb/data1/000.dir/000.dir/013.dir/167.dir/002.dir:
> total 13344
> -rw-r--r-- 1 test test    3416064 Jun  9 15:00 FILE000.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 15:01 FILE001.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 15:02 FILE002.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 15:02 FILE003.cdf
> 
> /home/test/infinidb/data1/000.dir/000.dir/013.dir/167.dir/003.dir:
> total 13344
> -rw-r--r-- 1 test test    3416064 Jun  9 15:08 FILE000.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 15:09 FILE001.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 15:09 FILE002.cdf
> -rw-r--r-- 1 test test    3416064 Jun  9 15:10 FILE003.cdf
> 
> /home/test/infinidb/data1/000.dir/000.dir/013.dir/167.dir/004.dir:
> total 42784
> -rw-r--r-- 1 test test     3416064 Jun  9 15:13 FILE000.cdf
> -rw-r--r-- 1 test test     3416064 Jun  9 15:14 FILE001.cdf
> -rw-r--r-- 1 test test    35266560 Jun  9 15:15 FILE002.cdf
> -rw-r--r-- 1 test test     1712128 Jun  9 15:13 FILE003.cdf

これらが実体。


## パーティションテーブルの確認

> mysql> select calShowPartitions('table_01', 'column_01');
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | calShowPartitions('table_01', 'column_01')                                                                                                                                                                                                                                                                                   |
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Part#     Min                           Max                           Status
>   0.0.1     1453311000                    1453457400                    Enabled
>   0.1.1     1453457400                    1453602000                    Enabled
>   0.2.1     1453602000                    1453687800                    Enabled |
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.22 sec)


## ユーザー登録・権限付与

(usernameに対して、db1データベースの全テーブルに全特権を付与)

> mysql> grant all privileges on db1.* to username identified by 'password';　①
> mysql> grant all privileges on db1.* to username@localhost identified by 'password';　②

※ ① だけやっても、localhostからはアクセスができないようなので②も必要。


ここは、Infini特別。

> mysql> grant all privileges on infinidb_vtable.* to username identified by 'password';
> mysql> grant all privileges on infinidb_vtable.* to username@localhost identified by 'password';

一時表として、infinidb_vtableというのを使うらしく、これが無いとDML等使えない。


一通り終わったら、フラッシュ。

> mysql> flush privileges;


結果確認

> mysql> use mysql
> Database changed

> mysql> select * from user;
> +---------------+-----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
> | Host          | User      | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
> +---------------+-----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
> ...
> | %             | username  | *hexadecimal string                       | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
> | localhost     | username  | *hexadecimal string                       | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
> ...
> +---------------+-----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+


権限はく奪の場合は、普通にrevoke。

> mysql> revoke all privileges on db1.* from username;



## カラム追加

> mysql> desc test_01;
> +----------+----------+------+-----+---------+-------+
> | Field    | Type     | Null | Key | Default | Extra |
> +----------+----------+------+-----+---------+-------+
> | column1  | char(50) | YES  |     | NULL    |       |
> | column2  | int(11)  | YES  |     | NULL    |       |
> +----------+----------+------+-----+---------+-------+


datetime型のcolumn3を追加する。

> mysql> select calonlinealter('alter table test_01 add column column3  datetime;');
> +--------------------------------------------------------------------+
> | calonlinealter('alter table test_01 add column column3 datetime;') |
> +--------------------------------------------------------------------+
|                                                                  0 |
> +--------------------------------------------------------------------+
> 1 row in set (53.89 sec)

登録されているデータの量（サイズ）によって、かかる時間が違う。

> mysql> alter table test_01 add column column3 datetime comment 'schema sync only';
> Query OK, 0 rows affected (0.34 sec)
> Records: 0  Duplicates: 0  Warnings: 0

これはさくっと終わる。

> mysql> desc test_01;
> +---------+----------+------+-----+---------+-------+
> | Field   | Type     | Null | Key | Default | Extra |
> +---------+----------+------+-----+---------+-------+
> | column1 | char(50) | YES  |     | NULL    |       |
> | column2 | int(11)  | YES  |     | NULL    |       |
> | column3 | datetime | YES  |     | NULL    |       |
> +---------+----------+------+-----+---------+-------+


## カラム削除

> mysql> create table test1 (
>     -> c1 int
>     -> ,c2 char(8)
>     -> ,c3 varchar(256)
>     -> ,c4 float
>     -> ,c5 date
>     -> ) engine=infinidb;
> Query OK, 0 rows affected (0.29 sec)
> 
> mysql> show create table test1;
> +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
>   `c1` int(11) DEFAULT NULL,
>   `c2` char(8) DEFAULT NULL,
>   `c3` varchar(256) DEFAULT NULL,
>   `c4` float DEFAULT NULL,
>   `c5` date DEFAULT NULL
> ) ENGINE=InfiniDB DEFAULT CHARSET=latin1 |
> +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
> 
> mysql> desc test1;
> +-------+--------------+------+-----+---------+-------+
> | Field | Type         | Null | Key | Default | Extra |
> +-------+--------------+------+-----+---------+-------+
> | c1    | int(11)      | YES  |     | NULL    |       |
> | c2    | char(8)      | YES  |     | NULL    |       |
> | c3    | varchar(256) | YES  |     | NULL    |       |
> | c4    | float        | YES  |     | NULL    |       |
> | c5    | date         | YES  |     | NULL    |       |
> +-------+--------------+------+-----+---------+-------+
> 5 rows in set (0.00 sec)
> 
> mysql> alter table test1 drop column c4;
> Query OK, 0 rows affected (2.04 sec)
> Records: 0  Duplicates: 0  Warnings: 0
> 
> mysql> show create table test1;
> +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table | Create Table                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | test1 | CREATE TABLE `test1` (
>   `c1` int(11) DEFAULT NULL,
>   `c2` char(8) DEFAULT NULL,
>   `c3` varchar(256) DEFAULT NULL,
>   `c5` date DEFAULT NULL
> ) ENGINE=InfiniDB DEFAULT CHARSET=latin1 |
> +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
> 
> mysql> desc test1;
> +-------+--------------+------+-----+---------+-------+
> | Field | Type         | Null | Key | Default | Extra |
> +-------+--------------+------+-----+---------+-------+
> | c1    | int(11)      | YES  |     | NULL    |       |
> | c2    | char(8)      | YES  |     | NULL    |       |
> | c3    | varchar(256) | YES  |     | NULL    |       |
> | c5    | date         | YES  |     | NULL    |       |
> +-------+--------------+------+-----+---------+-------+
> 4 rows in set (0.00 sec)