在快速开始demo中，查询报公钥不正确问题

[daoshouchen]

按照步骤进行demo执行，最后一步时报异常，反复create user了几次，不知道问题出在哪个密码不正确？
异常信息：
SELECT ta.credit_rank, COUNT(*) as cnt, AVG(ta.income) as avg_income, AVG(tb.order_amount) as avg_amount FROM ta INNER JOIN tb ON ta.ID = tb.ID WHERE ta.age >= 20 AND ta.age <= 30 AND tb.is_active=1 GROUP BY ta.credit_rank;
[fetch]err: Code: 320, message:RunExecutionPlan create session(4d46b55c-9a5d-11ee-aa88-0242ac150002) failed, catch std::exception=[Enforce fail at engine/auth/authenticator.cc:55] self_public_key_ == pi.pub_key. self public key mismatched
Stacktrace:
#0 scql::engine::EngineServiceImpl::VerifyPublicKeys()+0x558fbb8923c8
#1 scql::engine::EngineServiceImpl::RunExecutionPlan()+0x558fbb897d40
#2 brpc::policy::ProcessHttpRequest()+0x558fbe2089b5
#3 brpc::ProcessInputMessage()+0x558fbe1b2067
#4 brpc::InputMessenger::OnNewMessages()+0x558fbe1b3641
#5 brpc::Socket::ProcessEvent()+0x558fbe2b149e
#6 bthread::TaskGroup::task_runner()+0x558fbe30b677
#7 bthread_make_fcontext+0x558fbe2f6a31

[daoshouchen]

另外，http请求参数用户相关信息都是密码明文传输吗？
{
"user": {
"user": {
"account_system_type": "NATIVE_USER",
"native_user": { "name": "alice", "password": "some_password" }
}
},
"query": "SELECT ta.credit_rank, COUNT(*) as cnt, AVG(ta.income) as avg_income, AVG(tb.order_amount) as avg_amount FROM ta INNER JOIN tb ON ta.ID = tb.ID WHERE ta.age >= 20 AND ta.age <= 30 AND tb.is_active=1 GROUP BY ta.credit_rank;",
"biz_request_id": "1234",
"db_name":"demo"
}

[jingshi-ant]

确认下：create user的时候，是从scqltool脚本执行后的输出语句中复制的吗？ 如果不是的话，会导致公钥不匹配（demo里的create user使用的私钥和你们本地跑的不一样）

[daoshouchen]

./scqltool genCreateUserStmt --user alice --passwd some_password --party alice --pem examples/scdb-tutorial/engine/alice/conf/ed25519key.pem

./scqltool genCreateUserStmt --user bob --passwd another_password --party bob --pem examples/scdb-tutorial/engine/bob/conf/ed25519key.pem

通过这两个脚本跑的和demo都测试过了不行。

[jingshi-ant]

可以通过开启https来保护账密，不过配置https比较复杂，辛苦参考配置说明：https://www.secretflow.org.cn/docs/scql/latest/zh-Hans/reference/engine-config#config-for-tls

[jingshi-ant]

状态会保留在mysql里面，重新多次create user是无法成功的。请问反复create user是通过先drop user，再 create user吗？ 或者清理掉环境。

[daoshouchen]

我突然明白了，ed25519key.pem是需要我用alice和bob节点生成的对吗？

[jingshi-ant]

运行setup.sh脚本会在本地生成alice和bob的ed25519key.pem

[daoshouchen]

状态会保留在mysql里面，重新多次create user是无法成功的。请问反复create user是通过先drop user，再 create user吗？ 或者清理掉环境。

对，先drop user 后 create user。

[daoshouchen]

我的部署方式是10节点部署alice和scdb，11节点部署bob，alice和bob部署时密钥分别是本地节点生成的。现在分别使用10和11的密钥进行alice和bob的create user，但是最终报bob无权限
bob> switch bob
bob> drop table demo.tb
[fetch]err: Code: 101, message:user bob authentication failed
而使用setup.sh脚本生成的，密钥进行create user ，最终在join脚本中报公钥不匹配。

[jingshi-ant]

我刚刚跑了下examples，没有复现你的问题。可以直接看公钥内容是否匹配：
1.配置里authorized_profile.json的值
2.mysql中users表里公钥的值：
docker exec -it scdb-tutorial-mysql-1 bash
mysql -u root -pxxxxx (xxxxx对应docker-compose.yml里MYSQL_ROOT_PASSWORD的值)
select * from scdb.users;

[daoshouchen]

我看到了配置文件中bob写错成alice我重新部署后验证一下。多谢！

[jingshi-ant]

改了配置直接restart就行了

[daoshouchen]

完成demo执行，使用http异步提交后进行结果查询，一直都是result not ready，demo执行1秒内就结果了，这是什么原因？{"status":{"code":104, "message":"result not ready, please retry later", "details":[]}, "out_columns":[], "scdb_session_id":"b5d3f356-9b14-11ee-89ab-0242ac150002", "affected_rows":"0", "warnings":[]}

[jingshi-ant]

应该是有报错，（engine，或者scdb配置异常之类的）可以docker logs 看下 engine或者scdb是否有报错。

[daoshouchen]

同步查询返回正常，异步查询使用返回色session_id查询结果就不行。engine异常日志有，劳烦分析下是什么配置错误导致
scdb日志：
engine"}|PartyCode:alice|Url:http://10.199.0.10:32941/SCQLEngineService/RunExecutionPlan
2023-12-15 17:41:46.12155 INFO executor.go:100 |RequestID:|SessionID:296dac64-9b2e-11ee-89ab-0242ac150002|ActionName:EngineStub@RunExecutionPlan|CostTime:12.058608ms|Reason:|ErrorMsg:|Request:
2023-12-15 17:41:46.12155 INFO submit_handler.go:92 |RequestID:test|SessionID:296dac64-9b2e-11ee-89ab-0242ac150002|ActionName:SCDBQueryHandler@/public/submit_query|CostTime:32.687187ms|Reason:|ErrorMsg:|Request:user:{user:{account_system_type:NATIVE_USER native_user:{name:"alice"}}} query:"SELECT ta.credit_rank, COUNT(*) as cnt, AVG(ta.income) as avg_income, AVG(tb.order_amount) as avg_amount FROM ta INNER JOIN tb ON ta.ID = tb.ID WHERE ta.age >= 20 AND ta.age <= 30 AND tb.is_active=1 GROUP BY ta.credit_rank;" biz_request_id:"test" db_name:"demo"|ClientIP:10.200.116.250
2023-12-15 17:41:46.12155 INFO server.go:162 |GIN|status=200|method=POST|path=/public/submit_query|ip=10.200.116.250|latency=33.280149ms|
2023-12-15 17:41:57.12155 ERROR query_result_handler.go:63 |RequestID:|SessionID:296dac64-9b2e-11ee-89ab-0242ac150002|ActionName:FetchHandler@/public/fetch_result|CostTime:2.943041ms|Reason:InvalidRequest|ErrorMsg:result not ready, please retry later|Request:user:{user:{account_system_type:NATIVE_USER native_user:{name:"alice"}}} scdb_session_id:"296dac64-9b2e-11ee-89ab-0242ac150002"|ClientIP:10.200.116.250
2023-12-15 17:41:57.12155 INFO server.go:162 |GIN|status=200|method=POST|path=/public/fetch_result|ip=10.200.116.250|latency=3.109916ms|

engine日志：
2023-12-15 17:41:46.451 [error] [http_rpc_protocol.cpp:BRPC:1602] [scqlengine] Invalid host=scdb port=8080
2023-12-15 17:41:46.451 [error] [channel.cpp:BRPC:248] [scqlengine] Fail to parse address=`http://scdb:8080/cb/engine'
2023-12-15 17:41:46.451 [warning] [engine_service_impl.cc:ReportResult:250] [scqlengine] ReportResult(296dac64-9b2e-11ee-89ab-0242ac150002) failed, catch std::exception=[engine/link/channel_manager.cc:46] BrpcChannel Init failed, ret=-1, remote_addr=http://scdb:8080/cb/engine, role=2, protocal=http
Stacktrace:
#0 scql::engine::EngineServiceImpl::ReportResult()+0x55d14c75a90e
#1 scql::engine::EngineServiceImpl::RunPlanAsync()+0x55d14c75c90c
#2 std::_Function_handler<>::_M_invoke()+0x55d14c759a6a
#3 std::__future_base::_State_baseV2::_M_do_set()+0x55d14c754fcf
#4 __pthread_once_slow+0x7f63bea0fe67

[jingshi-ant]

从engine日志看，是没法访问scdb，是因为有一个engine和scdb不在同一个机器上吗？ 如果是跨机的情况，scdb的配置里scdb_host应该使用跨机可访问的地址。（1.docker-compose里，scdb service expose出来独立端口. 2.scdb_host修改为公开的host/ip + 公开的端口）

[daoshouchen]

engine和scdb在同一个节点，不同docker内。可能无法直接通过scdb访问；docker-compose的配置我具体可以参考哪里？因为docker拉起时具体端口配置参数是哪项不了解。
docker-compose.yaml配置：
version: "3.8"
services:
scdb:
image: secretflow/scql:latest
environment:
- SCQL_ROOT_PASSWORD=root
command:
- /home/admin/bin/scdbserver
- -config=/home/admin/configs/config.yml
restart: always
ports:
- 8080
volumes:
- ./config.yml:/home/admin/configs/config.yml

[jingshi-ant]

如果不是docker内部，反而更简单点：直接scdb的配置里scdb_host使用跨机可访问scdb的地址即可，
e.g: scdb_host: 30.30.30.3:333

[daoshouchen]

scdb启动的端口如何设定？docker每次起端口都不同

[jingshi-ant]

参考docker-compose.yml里的ports

[daoshouchen]

4a0278ad60e4 secretflow/scql:latest "/home/admin/bin/scd…" 5 hours ago Up 9 seconds 0.0.0.0:32943->8080/tcp, :::32943->8080/tcp scdb-scdb-1 32943这个端口应该如何指定？

[daoshouchen]

懂了，感谢🙏