# TDSQL3

### tdsql-cntl常用命令

./tdsql-cntl --config ../config/cluster-config_9527.toml  --module all --option rebuild

 ./tdsql-cntl --config ../config/cluster-config_9527.toml --module all --option uninstall

 ./tdsql-cntl --config ../config/cluster-config_9527.toml --module all --option install

 ./tdsql-cntl --config ../config/cluster-config_9527.toml --module all --option uninstall

2022-10-08 16:45:29.869 | MainProcess:40404 | INFO - sqlengine create user success, you can use "mysql -h'9.30.17.168' -P9527 -u'test' -p'test123'" login your database.

mysql -h'9.135.218.12' -P3307 -u'root' -p'mysql'
mysql -h'9.135.218.12' -P3307 -u'root' -p'mysql'

mysql -h'9.30.17.168' -P9527 -u'test' -p'test123'

sed -i 's#/Users/lappy/Git_repos_mine/MySQL_IMDb_Project#/data/imdb/ft_local#g' imdb-load-data.sql

sed -i 's#/Users/lappy/Git_repos_mine/MySQL_IMDb_Project#/data1/home/axingguchen/imdb_data/ft_local/data#g' imdb-load-data.sql

### SQLEngine开发
1. 开发路径
    源代码目录：/data1/home/axingguchen/TDSQL3-TOOLS/tdsql3/src/SQLEngine
    tdsql-cnt目录：/data1/home/axingguchen/TDSQL3-TOOLS/bin
    客户端登陆：/data1/home/axingguchen/tdsql3_installs/9527/SQLEngine/install "./jmysql.sh 9527"

2. 代码重编译

    cd /data1/home/axingguchen/TDSQL3-TOOLS/tdsql3/src/SQLEngine
    ./make.sh -d 1
    rm -rf /data1/home/axingguchen/TDSQL3-TOOLS/tdsql3/release/SQLEngine
    rm -rf /data1/home/axingguchen/TDSQL3-TOOLS/tdsql3/release/TDStore
    rm -rf  /data1/home/axingguchen/tdsql3_installs/9527/SQLEngine
    rm -rf  /data1/home/axingguchen/tdsql3_installs/9527/TDStore
    cd /data1/home/axingguchen/TDSQL3-TOOLS/tdsql3/src/SQLEngine/mysql_install
    cp  -rf ./*  /data1/home/axingguchen/TDSQL3-TOOLS/tdsql3/release/
    cp  -rf ./* ../../../release/  # same as above
    cp -rf ./* /data1/home/axingguchen/tdsql3_installs/9527/
    sudo rm -rf *

3. core文件调试

     cd    /data1/home/axingguchen/tdsql3_installs/9527/SQLEngine/data/9527/dbdata_raw/data
     gdb   /data1/home/axingguchen/tdsql3_installs/9527/SQLEngine/bin/sqlengine  core.

打开mysql dbug
https://www.zhb127.com/archives/mysql-use-the-dbug-package.html
set debug="d:F:i:L:n:N:o,/data1/home/axingguchen/mysqld.trace:t";

client:
/data1/home/axingguchen/tdsql3_installs/9527/SQLEngine/install

bin:
/data1/home/axingguchen/TDSQL3-TOOLS/bin

src:
/data1/home/axingguchen/TDSQL3-TOOLS/tdsql3/src/SQLEngine

lsof -i:9527

gdb attach 39851

b storage/rocksdb/ha_rocksdb.cc:2592

c

x/24xb 0x29526398

x/24tb

/*****************************test cases for decoding the pk in storage format******************/

/****************************************************************
**the test cases are originally designed by dongzhizhao. see: **
**https://iwiki.woa.com/pages/viewpage.action?pageId=254746235**
****************************************************************/
/*
tips: you may need to replace the first four bytes in the function myrock_decode
in the example, such as '00002713'. Here, the hex '00002713' corresponds
to the 10003 in decimal. the used tables in the test cases in the following
example are created when the database 'test' is empty.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
character set latin1
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE `t_latin1_bin` (
`f1` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`f2` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`f3` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (`f1`,`f2`)
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1;
------------------------------------------------------------------------------------------
insert into t_latin1_bin values('a', 'b', 'c');
select myrock_decode("000027130361202020202020200203622020202020202002") ==> (a, b)
-------------------------------------------------------------------------------------------
insert into t_latin1_bin values('123456789 ', 'b', 'c');
select myrock_decode("000027130331323334353637380339202020202020200203622020202020202002")
==>(123456789 ,b )
--------------------------------------------------------------------------------------------
insert into t_latin1_bin values('12345', 'b', 'c');
select myrock_decode("000027130331323334352020200203622020202020202002") ==> (12345, b)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
character set utf8
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE `t_utf8_bin` (
`f1` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`f2` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`f3` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`f1`,`f2`)
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
insert into t_utf8_bin values('123456789 ', 'b', 'c');
select myrock_decode("000027140300310032003300340300350036003700380300390020002000200203006200200020002002")
==> (123456789 ,b);
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
character set latin1_swedish_ci
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE `t_latin1_swedish_ci` (
`f1` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`f2` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`f3` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
PRIMARY KEY (`f1`,`f2`)
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1;
insert into t_latin1_swedish_ci values('aBCdEFGHI ', 'bC', 'c');
select myrock_decode("000027150341424344454647480349202020202020200203424320202020202002")
==>(aBCdEFGHI , bC,)
////////////////////////////////////////////////////////////////////////////////////////////////////
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>secondary key case<
secondary key decoded case:here,follows the seconadary key layout sequence in the TDStore,i.e.,
(tindex_number, encoded_sk, encoded_pk). the decoded ouput sequence is (decoded_sk, decoded_pk)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE `t_latin1_bin_sk` (
`f1` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`f2` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`f3` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`f4` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (`f1`, `f2`),
KEY(`f3`, `f4`)
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1;
------------------------------------------------------------------------------------------------------
insert into t_latin1_bin_sk(f1, f2, f4) values('a', 'b', 'A ');
select myrock_decode("000027170001034120202020202020020361202020202020200203622020202020202002") ==> (NULL,A ,a,b)
------------------------------------------------------------------------------------------------------
insert into t_latin1_bin_sk values('c', 'd', 'e', 'f');
select myrock_decode("00002717010365202020202020200201036620202020202020020363202020202020200203642020202020202002") ==> (e,f,c,d)
-----------------------------------------------------------------------------------------------------------------------
insert into t_latin1_bin_sk(f1, f2, f4) values('e', 'f', 'A ');
select myrock_decode("000027170001034120202020202020020365202020202020200203662020202020202002") ==> (NULL,A ,e,f)
*/

Debug

get pid and attach to gdb:

lsof -i:9527
gdb attach 66359

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE `yuxingtest1` (
`f1` INT NOT NULL,
`f2` INT NOT NULL,
PRIMARY KEY (`f1`,`f2`)
) ENGINE=ROCKSDB;
insert into yuxingtest1 values(0, 0),(0,1),(1,0),(1,1);
CREATE TABLE `yuxingtest2` (
`f2` INT NOT NULL,
`f3` INT NOT NULL,
PRIMARY KEY (`f2`,`f3`)
) ENGINE=ROCKSDB;
insert into yuxingtest2 values(0, 0),(0,1),(1,0),(1,1);
CREATE TABLE `yuxingtest3` (
`f1` INT NOT NULL,
`f3` INT NOT NULL,
PRIMARY KEY (`f1`,`f3`)
) ENGINE=ROCKSDB;
insert into yuxingtest3 values(0, 0),(0,1),(1,0),(1,1);
insert into yuxingtest1 values(3, 3);
DELETE FROM yuxingtest1 WHERE f1=3;
insert into yuxingtest1 values(3, 3);
insert into yuxingtest1 values(10, 10),(12, 12),(13, 13),(14, 14),(15, 15),(16, 16),(17, 17),(18, 18),(19, 19);
insert into yuxingtest1 values(20, 10),(22, 22),(23, 23),(24, 24),(25, 25),(26, 26),(27, 27),(28, 28),(29, 219);

CREATE INDEX c1 ON yuxingtest1 (f1);
DROP INDEX c1 ON yuxingtest1;

select * from yuxingtest1,yuxingtest2,yuxingtest3 where yuxingtest1.f2=yuxingtest2.f2 and yuxingtest2.f3=yuxingtest3.f3 and yuxingtest1.f1=yuxingtest3.f1;

Update Cardinality:
sql/dd/info_schema/table_stats.cc:363

CREATE INDEX Aliases_index ON Aliases(title_id);
DROP INDEX Aliases_index ON Aliases

SOURCE /data1/home/axingguchen/imdb_data/ft_local/sql_scripts/imdb-create-tables.sql

SOURCE /data1/home/axingguchen/imdb_data/ft_local/sql_scripts/test-imdb-load-data.sql

error logs:
vim ../logs/tdsql-cluster-9527.log

/data1/home/axingguchen/TDSQL3.0-test-14.1.0/TDSQL3-TOOLS/tdsql3/src/SQLEngine/storage/rocksdb/ha_rocksdb.cc