LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nishimry

infiniDBコマンド集

Last updated at Posted at 2016-01-25

仕事で 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)

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?