matrixone [Bug]: Connection persists after an account is suspended

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

1.1-dev

Commit ID

v1.1.2-c66ab12ea-2024-03-27

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Create an account
Connect to MO with the account admin, run some SQLs
Suspend the account
The connection created in step 2 persists and we can still run SQLs

Expected Behavior

To be determined by the PM

Steps to Reproduce

Refer to Actual Behavior

Additional information

No response

Mar 27 '24 12:03 DanielZhangQD

yes, it's a bug. when account suspend, no connection can be run immediately

Mar 27 '24 12:03 LiSong0214

still working

Apr 02 '24 10:04 YANGGMM

create account abc ADMIN_NAME 'admin' IDENTIFIED BY '123456';

--mysql -h127.0.0.1 -uabc:admin -P6009 -p123456
show databases;


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

alter account  abc suspend;

show databases;

mysql> show databases;
No connection. Trying to reconnect...
ERROR 1045 (28000): Access denied for user abc:admin. internal error: Account abc is suspended
ERROR: 
Can't connect to the server

main 上没有复现

Apr 03 '24 02:04 YANGGMM


mysql> alter account abc restricted;
Query OK, 0 rows affected (0.02 sec)

--mysql -h127.0.0.1 -uabc:admin -P6009 -p123456

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

mysql> 
mysql> 
mysql> create database test;
ERROR 20101 (HY000): internal error: do not have privilege to execute the statement

Apr 03 '24 03:04 YANGGMM

在1.1-dev 上

mysql> create account abc ADMIN_NAME 'admin' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.33 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mo_catalog         |
| system             |
| system_metrics     |
| information_schema |
| mysql              |
+--------------------+
5 rows in set (0.01 sec)

mysql> 

mysql> alter account  abc suspend;
Query OK, 0 rows affected (0.02 sec)


mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 1045 (28000): Access denied for user abc:admin. internal error: Account abc is suspended
ERROR: 
Can't connect to the server

Apr 08 '24 03:04 YANGGMM

mysql> alter account abc restricted;
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mo_catalog         |
| system             |
| system_metrics     |
| information_schema |
| mysql              |
+--------------------+
5 rows in set (0.01 sec)

mysql> create database test;
ERROR 20101 (HY000): internal error: do not have privilege to execute the statement
mysql> 
mysql> 
mysql> load data inline format='csv', data='1000-01-01,0001-01-01,1970-01-01 00:00:01,0
    '> 9999-12-31,9999-12-31,2038-01-19,1
    '> ' into table t4;
ERROR 20101 (HY000): internal error: do not have privilege to execute the statement

Apr 08 '24 03:04 YANGGMM

mysql> alter account abc restricted;
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mo_catalog         |
| system             |
| system_metrics     |
| information_schema |
| mysql              |
+--------------------+
5 rows in set (0.01 sec)

mysql> create database test;
ERROR 20101 (HY000): internal error: do not have privilege to execute the statement
mysql> 
mysql> 
mysql> load data inline format='csv', data='1000-01-01,0001-01-01,1970-01-01 00:00:01,0
    '> 9999-12-31,9999-12-31,2038-01-19,1
    '> ' into table t4;
ERROR 20101 (HY000): internal error: do not have privilege to execute the statement

Apr 09 '24 07:04 YANGGMM

cloud 测试测试环境有问题，等后面再看

Apr 09 '24 07:04 YANGGMM

等待复现

Apr 12 '24 10:04 YANGGMM

still working

Apr 30 '24 10:04 YANGGMM

still working

May 06 '24 11:05 YANGGMM

dev 环境现在有问题

create account 之后登陆Auth failed: info: access denied

May 08 '24 03:05 YANGGMM

{"level":"ERROR","time":"2024/05/08 03:49:02.327386 +0000","name":"proxy-service","caller":"proxy/handler.go:168","msg":"failed to create server conn","uuid":"65303461-6530-6664-6239-666238333231","error":"Auth failed: info: access denied for account acc_idx"}
{"level":"ERROR","time":"2024/05/08 03:49:02.327365 +0000","name":"proxy-service","caller":"proxy/client_conn.go:281","msg":"failed to connect to backend","uuid":"65303461-6530-6664-6239-666238333231","error":"Auth failed: info: access denied for account acc_idx"}
{"level":"ERROR","time":"2024/05/08 03:49:02.327317 +0000","caller":"proxy/plugin.go:76","msg":"error: info: access denied for account acc_idx"}
{"level":"error","ts":"2024-05-08T03:49:02Z","caller":"handler/handler.go:116","msg":"get tenant cluster failed","error":"internal error: cluster not found for tenant acc_idx","stacktrace":"github.com/matrixone-cloud/plugin/pkg/handler.(*Handler).handle\n\t/workspace/pkg/handler/handler.go:116\ngithub.com/matrixone-cloud/plugin/pkg/handler.(*Handler).HandleRequest.func1\n\t/workspace/pkg/handler/handler.go:103"}
2024/05/08 03:49:02.326915 +0000 DEBUG received request {"name": "proxy-plugin", "sequence": 1729320, "client": "127.0.0.1:46398", "request-id": 1729320, "request": "1729320: tenant:\"acc_idx\" username:\"root\" originIP:\"116.230.67.64\" labelSelector:<key:\"account\" value:\"acc_idx\" > /"}

May 08 '24 06:05 YANGGMM

在dev 环境下复现成功

May 10 '24 10:05 YANGGMM

先忙其他事情

May 15 '24 11:05 YANGGMM

still working

May 17 '24 10:05 YANGGMM

still working

May 22 '24 11:05 YANGGMM

still working

May 27 '24 11:05 YANGGMM

still working

May 30 '24 10:05 YANGGMM

还在看

Jun 04 '24 10:06 YANGGMM

还在看

Jun 07 '24 12:06 YANGGMM

还在看

Jun 12 '24 11:06 YANGGMM

还在看

Jun 17 '24 11:06 YANGGMM

还在看

Jun 20 '24 11:06 YANGGMM

还在看

Jun 25 '24 11:06 YANGGMM

还在看

Jun 28 '24 10:06 YANGGMM

当前bug 会导致cloud 功能受影响, 而且长时间没有进度, 升级为s-1

Jul 02 '24 07:07 tianyahui-python

还在看

Jul 03 '24 10:07 YANGGMM

测试记录

mo 版本 main
commit 8a1e5f653b9599c45338daca98f6b0027e366da4 
Author: nitao <[email protected]>
Date:   Wed Jul 3 15:54:00 2024 +0800

    fix a bug that cause wrong parallelism for tpch (#17296)
    
    fix a bug that cause wrong parallelism for tpch
    
    Approved by: @ouyuanning, @m-schen

通过proxy 方式启动
 matrixone git:(fix-15202-1) ✗ ./mo-service -cfg etc/launch-with-proxy/log.toml > log.log 2>&1 &
[1] 19479
matrixone git:(fix-15202-1) ✗ ./mo-service -cfg etc/launch-with-proxy/tn.toml > tn.log 2>&1 &
[2] 19504
 matrixone git:(fix-15202-1) ✗ ./mo-service -cfg etc/launch-with-proxy/cn1.toml > cn1.log 2>&1 &
[3] 19530
 matrixone git:(fix-15202-1) ✗ ./mo-service -cfg etc/launch-with-proxy/cn2.toml >cn2.log 2>&1 &
[4] 19552
matrixone git:(fix-15202-1) ✗ ./mo-service -cfg etc/launch-with-proxy/proxy.toml > proxy.log 2>&1 &
[5] 19571

sys 租户登陆
➜  ~ mysql -h127.0.0.1 -udump -P6009 -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 201
Server version: 8.0.30-MatrixOne-v54256 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mo_debug           |
| mo_task            |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
7 rows in set (0.01 sec)

mysql> 
mysql> 
mysql> drop account if exists acc01;
Query OK, 0 rows affected (0.01 sec)

mysql> create account acc01 admin_name = 'test_account' identified by '111';
Query OK, 0 rows affected (0.52 sec)

acc01 登陆

➜  ~  mysql -h127.0.0.1 -uacc01:test_account -P6009 -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 202
Server version: 8.0.30-MatrixOne-v54256 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

mysql> create database abc;
Query OK, 1 row affected (0.02 sec)


系统租户更改普通租户状态

mysql> alter account acc01 suspend;
Query OK, 0 rows affected (0.01 sec)


查看普通租户

mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 1045 (28000): Access denied for user acc01:test_account. internal error: Account acc01 is suspended
ERROR: 
Can't connect to the server

没有复现

Jul 04 '24 02:07 YANGGMM

1.2-dev 测试记录

commit c71c20e8b72ebea52d95153c5d4f88012620f73f (HEAD -> account-status, upstream/1.2-dev)
Author: davis zhen <[email protected]>
Date:   Thu Jul 4 02:53:39 2024 +0800

    fix dual (#17279)
    
    对dual的修改：
    
    CASE 1:
    create table `dual`(a int);
    select * from dual;
    mysql responses:  No tables used
    mo和mysql认为是占位符。
    
    CASE 2: select * from `dual`;
    create table `dual`(a int);
    select * from `dual`;
    mysql responses: success.
    mysql认为是正常表。
    mo认为是占位符。这与mysql不同。目前的parser不好改为同mysql一致。
    
    CASE 3:
    create table `dual`(a int);
    select * from db.dual;
    select * from icp.`dual`;
    mysql responses: success.
    mysql和mo都认为是正常表。
    
    Approved by: @badboynt1, @zhangxu19830126, @ouyuanning, @aressu1985, @sukki37

通过proxy 方式启动

matrixone git:(account-status) ✗ ./mo-service -cfg etc/launch-with-proxy/log.toml > log.log 2>&1 &
[1] 21809
 matrixone git:(account-status) ✗ ./mo-service -cfg etc/launch-with-proxy/tn.toml > tn.log 2>&1 &
[2] 21827
 matrixone git:(account-status) ✗ ./mo-service -cfg etc/launch-with-proxy/cn1.toml > cn1.log 2>&1 &
[3] 21845
matrixone git:(account-status) ✗ ./mo-service -cfg etc/launch-with-proxy/cn2.toml >cn2.log 2>&1 &
[4] 21866
 matrixone git:(account-status) ✗ ./mo-service -cfg etc/launch-with-proxy/proxy.toml > proxy.log 2>&1 &
[5] 21885

sys 租户登陆
➜  ~ mysql -h127.0.0.1 -udump -P6009 -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 201
Server version: 8.0.30-MatrixOne-v54256 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mo_debug           |
| mo_task            |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
7 rows in set (0.01 sec)

mysql> drop account if exists acc01;
Query OK, 0 rows affected (0.01 sec)

mysql> create account acc01 admin_name = 'test_account' identified by '111';
Query OK, 0 rows affected (0.50 sec)

mysql> 


acc01 登陆

➜  ~  mysql -h127.0.0.1 -uacc01:test_account -P6009 -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 202
Server version: 8.0.30-MatrixOne-v54256 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show accounts;
+--------------+--------------+---------------------+--------+----------------+----------+-----------+------+----------+
| account_name | admin_name   | created_time        | status | suspended_time | db_count | tbl_count | size | comments |
+--------------+--------------+---------------------+--------+----------------+----------+-----------+------+----------+
| acc01        | test_account | 2024-07-04 02:39:25 | open   | NULL           |        5 |        63 |    0 |          |
+--------------+--------------+---------------------+--------+----------------+----------+-----------+------+----------+
1 row in set (0.01 sec)

系统租户更改普通租户状态

mysql> alter account acc01 suspend;
Query OK, 0 rows affected (0.01 sec)

查看普通租户
mysql> show accounts;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 1045 (28000): Access denied for user acc01:test_account. internal error: Account acc01 is suspended
ERROR: 
Can't connect to the server

Jul 04 '24 02:07 YANGGMM

matrixone matrixone copied to clipboard

[Bug]: Connection persists after an account is suspended

Is there an existing issue for the same bug?

Branch Name

Commit ID

Other Environment Information

Actual Behavior

Expected Behavior

Steps to Reproduce

Additional information

matrixone
matrixone copied to clipboard