As an inheritance from Vitess, we’ve got a system database named _vt, which contains some system tables.
It is advisable to not grant users permission to read or modify the system database. In fact, it would be ideal if users were completely unaware of its existence.
We need to do the following work:

1. Hide the system database for users. for example, show databases will not display it.
2. ...

Overview of the Issue

The current Dockerfile used for docker build comes out with 3.7GB of image, which is too big.
Optimize it by mulit stage and docker build --squash.

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Background

We've done a considerable amount of work on read-write-splitting. This included implementing the core functionality(#2), read-after-write capability(#20), and the load-balancer(#64).
However, the cluster remains a black box for users, and they have no visibility on how the workload is distributed within the cluster.
Consequently, it would be a great idea to introduce a 'show' command to offer users current workload awareness of every tablet. This enhancement would appreciate the cluster's observability.

Usage

mysql> show workload;
+--------------+---------+-------------------+-------------------+---------------------+------------+
| Tablet Alias | Type    | TotalQueries(60s) | Qps               | AvgLatency(ms)      | QueryError |
+--------------+---------+-------------------+-------------------+---------------------+------------+
| 100          | PRIMARY | 107166            | 410.1166666666667 | 0.549385266468891   | 0          |
| 101          | REPLICA | 214388            | 820.7             | 0.22406482094553432 | 0          |
| 102          | REPLICA | 214268            | 819.7             | 0.22423095431255335 | 0          |
+--------------+---------+-------------------+-------------------+---------------------+------------+
3 rows in set (0.00 sec)

About the stats

The columns in the stats above are self-explanatory.

As to the Observability, we may add more stats in the future:

1. DML count
2. DDL count
3. gtid_executed
4. transactions
5. WAIT_FOR_EXECUTED_GTID_SET execution counts/probability for read-after-write
6. ...

Currently, all of our select, insert, delete, and update SQL statements are using V4 Planner, and we have made some modifications to V4 Planner to support our unshard architecture. The V3 Planner is no longer needed and can be deleted.

Problem

Wesql-Scale is a proxy for underlying mysql, the underlying mysql may already have some databases created by user. And system databases like mysql, information_schema, sys, performance_schema (, and so on... ) are created defaultly.
But Wesql-Scale needs keyspace metadata to recognize these databases in the underlying mysql.
Users can use command create database [dbname] if not exists; to build keyspace metadata in Wesql-Scale, but it's tedious.
A better solution is that Wesql-Scale build the keyspace metadata automatically.

Design

This feature is very suitable for implementation in the schema tracker: vttablet is responsible for obtaining existed database schema from the MySQL server, and vtgate is responsible for constructing the keyspace metadata based on that information.

We will make some modifications to the schema tracker:

1. Adapt to the unsharded mode.
2. Add keyspace metadata synchronization function.
3. The ability to obtain table and view metadata is disabled by default because it is not needed in unsharded mode for now. However, it will be needed in the optimizer module in the future.

TestWorkflowListAll

Overview of the Issue

show tables failed in system database

possible Solution

All system databases (expect _vt) don't have keyspaces, if user specifies 'mysql' as dbname, VTGate will use 'mysql' as keyspace. However, the planner will eventually find out that there's no keyspace named 'mysql', thus the planner will return an error.

Reproduction Steps

1. execute use mysql
2. execute show tables

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Feature Description

Now, leader detection of wesql-server consensus group are initialized in vtconsensus periodically, it works, however, may have a significant delay sometimes.

A better approach is to initialize leader change detection in vtgate, when the running SQLs(or ping) do not return exceeding a threshold time, or they returns error value indicating the server is not leader, a leader detection action should be started immediately.

Use Case(s)

No response

Feature Description

When changing roles between follower and learner in wesql-server, wesql-scale does not support corresponding tablet type changes. According to the design of wesql-scale, the tablet type corresponding to follower role is REPLICA, and the tablet type corresponding to learner role is RDONLY.

Use Case(s)

No response

Overview of the Issue

With my-wesqlscale heml package v0.5.0-beta.26, after do a leader change, the status of wesql-server cat in nyancat demo doesn't recover.

Reproduction Steps

No response

Binary Version

Operating System and Environment details

1. do a switch over operation

MySQL [(none)]> call dbms_consensus.change_leader('my-wesqlscale-cluster-mysql-2.my-wesqlscale-cluster-mysql-headless:13306');

2. watch the nyancat demo

Log Fragments

No response

We will discuss problems here: #257
Issues are tracked by milestome: https://github.com/apecloud/wesql-scale/milestone/1

Is your improvement request related to a problem? Please describe.

in original vitess topology, keyspace means logic database, and each keyspace contains multiple shards. each tablet manages no more than one keyspace. vitess addresses tablets with keyspace.

while in wesql-scale, we allows a tablet manages multiple databases, and there is only one global implict keyspace for one backend wesql-server cluster. so the concept "keyspace" is of little use now.

In the future, wesql-scale may supports multi-tenants, that is, a single wesql-cluster may serve multiple wesql-server clusters. In this scenario, we should introduce a concept named "tenant", each tenant means a standalone wesql-server cluster, which contains multiple databases.

The similiarity of "keyspace" and "tenant" is that, vitess will distinguish, organize and lookup tablet using this concept.

So my suggestion is, we can use "tenant" to replace "keyspace", and before we supports multi-tenant, there is only one tenant for each vitess cluster.

Overview of the Issue

wesql-scale: not support drop multiple tables in different db.
MySQL [user]> create table db1.music(c1 int, c2 int);
Query OK, 0 rows affected (2.761 sec)

MySQL [user]> create table user.music(c1 int, c2 int);
Query OK, 0 rows affected (0.039 sec)

MySQL [user]> drop table db1.music, user.music;
ERROR 1235 (42000): VT12001: unsupported: Tables or Views specified in the query do not belong to the same destination

wesql-server:
MySQL [(none)]> drop table db1.music, user.music;
Query OK, 0 rows affected (0.023 sec)

Reproduction Steps

drop database db1;
drop database user;
create database db1;
create database user;
create table db1.music(c1 int, c2 int);
create table user.music(c1 int, c2 int);
drop table db1.music, user.music;

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

The examples directory contains scripts that developers frequently use to launch a cluster for test and development purposes. However, some of these scripts are no longer needed, and some could be simplified to make them more user-friendly.

In the interests of user convenience and reducing unnecessary complexity, we plan to simplify the examples directory scripts. We intend to achieve this by:

1. Identifying and removing scripts that are no longer needed
2. Simplifying existing scripts and removing unnecessary features
3. Improving the documentation and commenting of the scripts to aid in their usage

Simplifying the examples directory scripts will make it easier for developers to use and build clusters for testing and development purposes. We welcome the feedback and contributions of the community in achieving this goal.

Overview of the Issue

described in title

Reproduction Steps

1. create database

[root@my-wesqlscale-cluster-vtconsensus-0 vt]$mysql -hmy-wesqlscale-cluster-vtgate -uroot -proot -P15306
...
MySQL [(none)]> create database commerce;
Query OK, 1 row affected (0.036 sec)

2. use vtctldclient

[root@my-wesqlscale-cluster-vtconsensus-0 vt]$vtctldclient --server my-wesqlscale-cluster-vtctld-headless:15999 GetTablets  --keyspace "commerce" --shard "0"
ERROR: logging before flag.Parse: E0519 03:53:48.082260      67 syslogger.go:149] can't connect to syslog

expected:

should have some results like :

vtctldclient --server my-wesqlscale-cluster-vtctld-headless:15999 GetTablets  --keyspace "_vt" --shard "0"
ERROR: logging before flag.Parse: E0519 04:07:20.111172     172 syslogger.go:149] can't connect to syslog
zone1-0000000002 _vt 0 replica my-wesqlscale-cluster-mysql-2.my-wesqlscale-cluster-mysql-headless:15100 my-wesqlscale-cluster-mysql-2.my-wesqlscale-cluster-mysql-headless:3306 [] <null>
zone1-0000000001 _vt 0 primary my-wesqlscale-cluster-mysql-1.my-wesqlscale-cluster-mysql-headless:15100 my-wesqlscale-cluster-mysql-1.my-wesqlscale-cluster-mysql-headless:3306 [] 2023-05-18T09:04:47Z
zone1-0000000000 _vt 0 replica my-wesqlscale-cluster-mysql-0.my-wesqlscale-cluster-mysql-headless:15100 my-wesqlscale-cluster-mysql-0.my-wesqlscale-cluster-mysql-headless:3306 [] <null>

Binary Version

0.5.0-beta.26

Operating System and Environment details

No response

Log Fragments

No response

Background

Flaky tests in Go packages are causing the entire package to be flagged as having flaky tests, leading to skipped testing. We need to address this to ensure all packages are tested properly.

Solution

To address this issue, we propose testing all packages regardless of whether they contain flaky tests or not. If a package has flaky tests, we can still execute the tests and measure their pass/fail ratio. If a certain percentage of tests consistently fail across multiple test runs, only then should we mark the package as flaky.

Feature Description

After I kill the wesql-server leader node, wesql-scale will immdiately return an error for subsequent SQL queries until a new leader is elected and the wesql-server cluster is recovered.

kill the leader node

$ kubectl delete pod my-wesqlscale-cluster-mysql-0
pod "my-wesqlscale-cluster-mysql-0" deleted

wesql-scale returns error like this

MySQL [information_schema]> select * from information_schema.wesql_cluster_global;
ERROR 1105 (HY000): target: _vt.0.primary: primary is not serving, there is a reparent operation in progress

This behavior is not expected by users, because usually applications use database connection pooling, when any error happens to the connection, the connection will be terminated and removed from conn_pool, a new connection will be created and put in the pool. If the wesql-server cluster can be recovered in a short period of time, these error handling operations bring unnecessary costs.

I would like SQL queries are paused for a while and answer as usual after the wesql-server cluster recovers, as

MySQL [information_schema]> select * from information_schema.wesql_cluster_global;

... waiting for several seconds (of course the shorter the better) ...

+-----------+------------------------------------------------------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+
| SERVER_ID | IP_PORT                                                          | MATCH_INDEX | NEXT_INDEX | ROLE     | HAS_VOTED | FORCE_SYNC | ELECTION_WEIGHT | LEARNER_SOURCE | APPLIED_INDEX | PIPELINING | SEND_APPLIED |
+-----------+------------------------------------------------------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+
|         1 | my-wesqlscale-cluster-mysql-0.my-wesqlscale-cluster-mysql-headle |           0 |         78 | Follower | No        | No         |               5 |              0 |             0 | Yes        | No           |
|         2 | my-wesqlscale-cluster-mysql-1.my-wesqlscale-cluster-mysql-headle |          78 |          0 | Leader   | Yes       | No         |               5 |              0 |            77 | No         | No           |
|         3 | my-wesqlscale-cluster-mysql-2.my-wesqlscale-cluster-mysql-headle |          78 |         79 | Follower | Yes       | No         |               5 |              0 |            77 | Yes        | No           |
+-----------+------------------------------------------------------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+

Use Case(s)

No response

          in case you had multiple wesql-scale nodes, and the traffic was distributed balancedly into those nodes, so the gtid seen by any wesql-scale node was a monithic increaing but discontinued number, then the gtid set was an array consisting of many discontinued numbers. The question is, will the gtid set stored in gateway consume significant amount of memory after a long run and finally OOM?

Originally posted by @weicao in #23 (comment)

If we deploy multiple VTGate, the gtidset in LastSeenGtid will not be continuous, and potentially will make the SQL too long. So we need to compress the gtidset.

But how?
By making it continuous!

Each VTGate is responsible for maintaining the gtidset, which represents its up-to-date understanding of the existing gtids for that specific instance of VTGate. During execution stage, the VTGate need to use the gtidset to ensure read-after-write-consistency.
for example: gtidset=df74afe2-d9b4-11ed-b2c8-f8b7ac3813b5:10:20:30:40:50:300:500

How can we make it continuous?
By looking up the mysql server!

If all the mysql server in the cluster contains gtidset: df74afe2-d9b4-11ed-b2c8-f8b7ac3813b5:1-300, the VTGate has no reason to matain any gtid whose sequence number less equal than 300.
So we can:

1. Remove them from VTGate's memory datastructure, the gtidset will be df74afe2-d9b4-11ed-b2c8-f8b7ac3813b5:500
2. Or compress it, the gtidset will be df74afe2-d9b4-11ed-b2c8-f8b7ac3813b5:1-300:500

The latter may be eaiser to implement and more robust.

Overview of the Issue

vtgate cannot recognize the package name dbms_consensus added by wesql-server.
The result is that the wesql-server native procedure cannot be executed.
+----------------+-----------------------+------------+----------------------------------------------+
| SCHEMA_NAME | PROC_NAME | PROC_TYPE | PARAMETERS |
+----------------+-----------------------+------------+----------------------------------------------+
| dbms_admin | show_native_procedure | ADMIN_PROC | NULL |
| dbms_ccl | add_ccl_rule | ADMIN_PROC | VARCHAR, VARCHAR, VARCHAR, LONGLONG, VARCHAR |
| dbms_ccl | del_ccl_rule | ADMIN_PROC | LONGLONG |
| dbms_ccl | flush_ccl_queue | ADMIN_PROC | NULL |
| dbms_ccl | flush_ccl_rule | ADMIN_PROC | NULL |
| dbms_ccl | show_ccl_queue | ADMIN_PROC | NULL |
| dbms_ccl | show_ccl_rule | ADMIN_PROC | NULL |
| dbms_consensus | add_follower | ADMIN_PROC | VARCHAR |
| dbms_consensus | add_learner | ADMIN_PROC | VARCHAR |
| dbms_consensus | change_leader | ADMIN_PROC | VARCHAR |
| dbms_consensus | configure_follower | ADMIN_PROC | VARCHAR, LONGLONG, LONGLONG |
| dbms_consensus | configure_learner | ADMIN_PROC | VARCHAR, VARCHAR, LONGLONG |
| dbms_consensus | downgrade_follower | ADMIN_PROC | VARCHAR |
| dbms_consensus | drop_learner | ADMIN_PROC | VARCHAR |
| dbms_consensus | drop_prefetch_channel | ADMIN_PROC | LONGLONG |
| dbms_consensus | fix_cluster_id | ADMIN_PROC | LONGLONG |
| dbms_consensus | fix_matchindex | ADMIN_PROC | VARCHAR, LONGLONG |
| dbms_consensus | force_purge_log | ADMIN_PROC | LONGLONG |
| dbms_consensus | force_single_mode | ADMIN_PROC | NULL |
| dbms_consensus | local_purge_log | ADMIN_PROC | LONGLONG |
| dbms_consensus | purge_log | ADMIN_PROC | LONGLONG |
| dbms_consensus | refresh_learner_meta | ADMIN_PROC | NULL |
| dbms_consensus | show_cluster_global | ADMIN_PROC | NULL |
| dbms_consensus | show_cluster_local | ADMIN_PROC | NULL |
| dbms_consensus | show_logs | ADMIN_PROC | NULL |
| dbms_consensus | upgrade_learner | ADMIN_PROC | VARCHAR |
| mysql | dummy | ADMIN_PROC | NULL |
| mysql | dummy_2 | ADMIN_PROC | LONGLONG, VARCHAR |
+----------------+-----------------------+------------+----------------------------------------------+

etc.
It can only create database dbms_consensus on wesql-scale by mysql client, and can be recognized by vtgate.

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

As we continue to develop WeSQL-Scale, we have identified a need for a Docker image that includes all the components required to start a cluster, in order to make it easier for users to deploy and use the platform.

We are looking to create a Docker image that is similar to the docker/local image but also includes all the required additional components that are needed for WeSQL-Scale. By providing a Docker image that integrates all the necessary components, users will be able to start a WeSQL-Scale cluster with just one command, which will simplify the setup and deployment process.

Our aim is to provide a seamless experience for our users to enable them to quickly and easily start a WeSQL-Scale cluster. We are committed to ensuring our platform is user-friendly and accessible, and this Docker image is one of many steps we are taking to achieve this goal.

We welcome feedback and input from the community in regards to this initiative, and we will work closely with our users and contributors to ensure the Docker image meets their needs.

Thank you for your ongoing support of our project.

Overview of the Issue

I did some failover experiments using kill pod command.

then I found results of SHOW VITESS_REPLICATION_STATUS has only two nodes.

MySQL [(none)]> SHOW VITESS_REPLICATION_STATUS;
+----------+-------+------------+------------------+--------------------------------------------------------------------+-------------------+------------------------------------------------------------------------+----------------+-----------------------------------------+
| Keyspace | Shard | TabletType | Alias            | Hostname                                                           | ReplicationSource | ReplicationHealth                                                      | ReplicationLag | ThrottlerStatus                         |
+----------+-------+------------+------------------+--------------------------------------------------------------------+-------------------+------------------------------------------------------------------------+----------------+-----------------------------------------+
| _vt      | 0     | PRIMARY    | zone1-0000000001 | my-wesqlscale-cluster-mysql-1.my-wesqlscale-cluster-mysql-headless | :3306             | {"EventStreamRunning":"No","EventApplierRunning":"No","LastError":""}  | 0              | {"state":"OK","load":0.00,"message":""} |
| _vt      | 0     | REPLICA    | zone1-0000000000 | my-wesqlscale-cluster-mysql-0.my-wesqlscale-cluster-mysql-headless | :3306             | {"EventStreamRunning":"No","EventApplierRunning":"Yes","LastError":""} | 0              | {"state":"OK","load":0.00,"message":""} |
+----------+-------+------------+------------------+--------------------------------------------------------------------+-------------------+------------------------------------------------------------------------+----------------+-----------------------------------------+
2 rows in set (0.015 sec)

while there are three nodes actually

MySQL [(none)]> show vitess_tablets;
+-------+----------+-------+------------+---------+------------------+--------------------------------------------------------------------+----------------------+
| Cell  | Keyspace | Shard | TabletType | State   | Alias            | Hostname                                                           | PrimaryTermStartTime |
+-------+----------+-------+------------+---------+------------------+--------------------------------------------------------------------+----------------------+
| zone1 | _vt      | 0     | PRIMARY    | SERVING | zone1-0000000001 | my-wesqlscale-cluster-mysql-1.my-wesqlscale-cluster-mysql-headless | 2023-05-18T09:04:47Z |
| zone1 | _vt      | 0     | REPLICA    | SERVING | zone1-0000000002 | my-wesqlscale-cluster-mysql-2.my-wesqlscale-cluster-mysql-headless |                      |
| zone1 | _vt      | 0     | REPLICA    | SERVING | zone1-0000000000 | my-wesqlscale-cluster-mysql-0.my-wesqlscale-cluster-mysql-headless |                      |
+-------+----------+-------+------------+---------+------------------+--------------------------------------------------------------------+----------------------+
3 rows in set (0.001 sec)

MySQL [(none)]> select * from information_schema.wesql_cluster_global;
+-----------+------------------------------------------------------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+
| SERVER_ID | IP_PORT                                                          | MATCH_INDEX | NEXT_INDEX | ROLE     | HAS_VOTED | FORCE_SYNC | ELECTION_WEIGHT | LEARNER_SOURCE | APPLIED_INDEX | PIPELINING | SEND_APPLIED |
+-----------+------------------------------------------------------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+
|         1 | my-wesqlscale-cluster-mysql-0.my-wesqlscale-cluster-mysql-headle |        1233 |       1234 | Follower | No        | No         |               5 |              0 |          1233 | Yes        | No           |
|         2 | my-wesqlscale-cluster-mysql-1.my-wesqlscale-cluster-mysql-headle |        1233 |          0 | Leader   | Yes       | No         |               5 |              0 |          1233 | No         | No           |
|         3 | my-wesqlscale-cluster-mysql-2.my-wesqlscale-cluster-mysql-headle |        1233 |       1234 | Follower | Yes       | No         |               5 |              0 |          1233 | Yes        | No           |
+-----------+------------------------------------------------------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+
3 rows in set (0.003 sec)

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Overview of the Issue

{
"comment": "json_table expressions",
"query": "SELECT * FROM JSON_TABLE('[ {"c1": null} ]','$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )) as jt",
"v3-plan": "VT12001: unsupported: JSON_TABLE expressions",
"gen4-plan": "VT12001: unsupported: json_table expressions"
},

MySQL [db1]> SELECT * FROM JSON_TABLE('[ {"c1": null} ]','$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )) as jt;
ERROR 1235 (42000): VT12001: unsupported: json_table expressions

expected result:
MySQL [mysql]> SELECT * FROM JSON_TABLE('[ {"c1": null} ]','$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )) as jt ;
+------+
| c1 |
+------+
| NULL |
+------+

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Background:

We have already implemented session-level and instance-level Read-After-Write according to the RFC. These implementations do not introduce additional network trips, so their impact on performance is minimal. However, to enhance usability and functionality, we should still implement global-level Read-After-Write.

Solution:

Implementing global-level Read-After-Write is straightforward. We can retrieve the latest GTID from the primary node before the user's SQL statement is sent, and then execute a WAIT_FOR_EXECUTED_GTID_SET command before running the user's SQL statement.

By implementing global-level Read-After-Write, we can provide our users with a more seamless experience and increased functionality. As always, we welcome feedback and contributions from the community to help us continue to improve our platform.

Schema tracker is a component that sends schema information (including tables, columns, and views) from the MySQL server to VTGate. Due to our current unshard architecture, SQL statements can still be executed properly even without the schema tracker, as we rarely need to perform SQL rewrite. However, if we need to enhance the capabilities of the SQL layer in the future, the schema tracker will be an essential component.

The current schema tracker has some heavy operations when loading schema for the first time, such as reading the entire schema information of the database at once, deleting/writing to the system table, and passing it back to VTGate via gRPC. If there are too many tables in the database, it can potentially cause errors in the program. Therefore, we need a better schema tracker to solve the above problem.

go/vt/vtgate/schema/tracker.go
go/vt/vttablet/tabletserver/health_streamer.go

regex format:

^(feat|fix|docs|style|refactor|perf|test|workflow|build|ci|chore|release|workflow)(\(#.+\))\s*: .{1,140}

Background

Currently, our end-to-end tests inherited from vitess and self-written tests are mostly ad-hoc, making it difficult to understand the logic of each test case. Additionally, these tests are tightly coupled to specific clusters and environments, making portability and scalability a challenge. If we want to support different backend MySQL databases in the future, these end-to-end test cases cannot be reused.

Goals

Our goals are to:

1. Support different environments
2. Support different backend MySQLs
3. Enable convenient test case writing, where most cases can be written as scripts, and only some require detailed go code.
4. Adapt tests to be compatible with MTR
5. Auto Generate Testcases

Non-Goals

This is not a replacement for MTR, nor does it support functions such as Perl commands in MTR. The best way to use it is to call it using Go code to reduce the burden of writing end-to-end tests.

Proposal

To achieve these goals, we propose the following solutions:

1. Introduce pingcap/mysql-tester, a go-based testing tool that is compatible with MTR. It consists of only a few hundred lines of code and can easily be integrated into our testing framework.
2. For future end-to-end tests, we will prioritize using mysql-tester instead of writing go code, as it is both convenient and compatible with MTR.
3. Revamp existing end-to-end tests to make them compatible with mysql-tester, and improve test readability and maintainability by de-coupling tests from specific clusters and environments.

By adopting these solutions, we believe we can significantly improve the quality, maintainability, and scalability of our end-to-end testing framework. We welcome contributions and suggestions from the community to help us accomplish this task.

Thank you for your continued support and participation in our open-source project.

Usage In Shell

# build the mysql-tester binary
make build-mysql-tester

# run the mysql-tester binary
make run-mysql-tester

# run the mysql-tester binary with Program Arguments
make run-mysql-tester args='-port 15306 -path go/test/mysql-tester'

Usage In Golang Code

mysql-tester work as a module, so anyone who wishes to use the code for mysql-tester can directly import it. Alternatively, they can call its binary through os.Exec ().

Add Testcase Best Practice

1. Write a test file under the t directory, using example.test as a reference.
2. Set up a MySQL database, then run the test with the -record parameter to generate an example.result file.
3. Set up a wesql-scale environment, and use example.test and example.result to test the functionality.

Currently, our sidecarDB is hardcoded to the name "_vt". We need to make the name configurable to allow for greater flexibility. Specifically, we would like to be able to use a different name, such as "mysql", as our sidecarDB name.

At present, much of our code references the constant SidecarDBName, but there are still instances where the string "_vt" is used in both code and scripts. To avoid potential issues and confusion, we need to unify these references into a single, configurable item.

Please help us implement this feature by creating a new configurable item that can be used to set the name of the SidecarDB. We also need guidance on how to update all of the existing references to SidecarDBName and "_vt" in our code and scripts to use the new configurable item.

Thank you for your help in improving our project.

TestWorkflowListStreams

Overview of the Issue

mysql> select get_lock('xyz', 10), LPAD('hi',4,'??') from dual;
ERROR 1235 (42000): VT12001: unsupported: LOCK function and other expression: [LPAD(:vtg3, :vtg4, :vtg5)] in same select query

expected:

mysql> select get_lock('xyz', 10), LPAD('hi',4,'??') from dual;
+---------------------+-------------------+
| get_lock('xyz', 10) | LPAD('hi',4,'??') |
+---------------------+-------------------+
| 1 | ??hi |
+---------------------+-------------------+
1 row in set (0.01 sec)

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Overview of the Issue

create view not support complex select queries.

MySQL [db1]> create view user.view_a as select sql_calc_found_rows * from unsharded limit 100;
ERROR 1235 (42000): VT12001: unsupported: Select query does not belong to the same keyspace as the view statement

vitess known issue:
{
"comment": "create view with sql_calc_found_rows with limit",
"query": "create view user.view_a as select sql_calc_found_rows * from music limit 100",
"plan": "VT12001: unsupported: Complex select queries are not supported in create or alter view statements"
}
https://github.com/vitessio/vitess/blob/main/go/vt/vtgate/planbuilder/testdata/unsupported_cases.json

Reproduction Steps

wesql-scale:
drop database db1;
drop database user;
create database db1;
create database user;
create table db1.music(c1 int, c2 int);
create view user.view_a as select sql_calc_found_rows * from db1.music limit 100;

wesql-server:
MySQL [(none)]> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [db1]> create view user.view_a as select sql_calc_found_rows * from music limit 100;
Query OK, 0 rows affected, 1 warning (0.012 sec)

MySQL [db1]> select * from user.view_a;
Empty set, 1 warning (0.004 sec)

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Feature Description

For wesql-scale, vttablet、vtctlclient and vtctld not support ChangeTabletType command.

Use Case(s)

vtctldclient --server localhost:15999 GetTablets
vtctldclient --server localhost:15999 ChangeTabletType zone1-00000012 replica

Fix or Disable/Delete unit tests.
If unit tests are disabled/deleted, please make a note here.

please use this branch:
test/make_unit_tests_all_pass

Below are failed test cases

Overview of the Issue

When connecting to the VTGate without specifying a DBName( which is also known as a Keyspace), show tables should return error, but we get tables in system database: _vt.

We expect show tables would return error message:

mysql> show tables;
ERROR 1046 (3D000): No database selected

Reproduction Steps

1. connect to the cluster, eg. mysql -h 127.0.0.1 -P 15306
2. execute show tables;

Possible Solutions

If there's only one keyspace in the system, and user didn't specify keyspace to use, parseDestinationTarget will return the only keyspace as the default one. This may be the root cause of the problem.
I don't know why vitess have this behaviour, maybe because vitess supports accessing tables without specifing DBNames.

Overview of the Issue

MySQL [db1]> with x as (select 1,2) select * from x union select * from x;
ERROR 1235 (42000): VT12001: unsupported: WITH expression in UNION statement

expected result:
MySQL [db1]> with x as (select 1,2) select * from x union select * from x;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Overview of the Issue

When executing SQL in a session without specifying any database, the error messages differ from MySQL.

mysql> select * from db1.t1, t2;
ERROR 1105 (HY000): table t2 not found

MySQL result:

mysql> select * from db1.t1, t2;
ERROR 1046 (3D000): No database selected

Reproduction Steps

drop database db1;
drop database db2;
create database db1;
create database db2;
create table db1.t1(c1 int, c2 int);
create table db2.t2(c1 int, c2 int);
select * from db1.t1, t2;

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Overview of the Issue

Wesql-scale has added a new component ‘vtconsensus‘, which needs to add the corresponding endtoend test code

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Overview of the Issue

MySQL [db1]> WITH cte AS ( SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte;
ERROR 1235 (42000): VT12001: unsupported: WITH expression in SELECT statement

expected result:
MySQL [db1]> WITH cte AS ( SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

A new DDL plugin called Pushdown is added in go/vt/vtgate/engine/dbddl_plugin.go. The core functions of the DDL plugin is CreateDatabase and DropDatabase.
Currently, guaranteeing atomicity may not be assured, but providing assurance of idempotence should be required.
We should add some unit_test and endtoend test for this DDL plugin to make sure it's idempotent.

Overview of the Issue

The system database currently defaults to using '_vt' as the keyspace, which can lead to some errors.
Each system database should use its own keyspace

Reproduction Steps

mysql> use mysql;
Database changed
mysql> show tables;
+--------------------+
| Tables_in__vt      |
+--------------------+
| copy_state         |
| dt_participant     |
| dt_state           |
| heartbeat          |
| post_copy_action   |
| redo_state         |
| redo_statement     |
| reparent_journal   |
| resharding_journal |
| schema_migrations  |
| schema_version     |
| schemacopy         |
| vdiff              |
| vdiff_log          |
| vdiff_table        |
| views              |
| vreplication       |
| vreplication_log   |
+--------------------+
18 rows in set (0.01 sec)

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Feature Description

Vitess original testcases can be devided into: unit_test, endtoend(single process), endtoend(cluster). However, vitess was designed mostly for sharding, the endtoend test is not suitable for our project, and since we modified the architecture to not support sharding, most of the test cases fail.

So we need our own end-to-end tests to:

1. Ensure that the SQL functionality that should work properly in wesql-scale is functioning correctly.
2. Understand the compatibility of wesql-scale.

The test suite should be as comprehensive as possible, and we can refer to MTR for guidance.
We can also use a test framework such as https://github.com/pingcap/mysql-tester.

Background

Currently, our ReadWriteSplitting(#2) and ReadAfterWrite(#20) features provide great read and write capabilities for our backend MySQL cluster. However, we are missing a key component - a load balancer.

In situations where we have multiple read-only nodes, each one capable of providing read services, we need to determine which read node should execute a user's SQL query.

Proposal

To address this issue, we propose implementing a Load Balancer module that provides different load balancing policies based on our requirements. Our initial considerations include:

1. LEAST_GLOBAL_QPS - Load balance queries across read nodes with the lowest query per second in mysql server(QPS)
2. LEAST_QPS - Load balance queries across read nodes with the lowest query per second in VTGate (QPS)
3. LEAST_RT - Load balance queries across read nodes with the lowest response time (RT)
4. LEAST_BEHIND_PRIMARY - Load balance queries across read nodes with the least lag behind the primary node
5. RANDOM - Randomly load balance queries across available read nodes

Usage

Users can select the load balancing strategy using the following commands:

set session read_write_splitting_policy='disable';   -- default
set session read_write_splitting_policy='random';
set session read_write_splitting_policy='least_global_qps';
set session read_write_splitting_policy='least_qps';
set session read_write_splitting_policy='least_rt';
set session read_write_splitting_policy='least_behind_primary';

Future Works

LoadBalance is a module that is strongly associated with workload and statistical information. In order to improve our LoadBalance module in the future, we need to enhance our statistical information and create a more convenient testing environment and observability to enhance our algorithms. This issue is just a beginning.

TestVExec

Overview of the Issue

{
"comment": "select get_lock with non-dual table",
"query": "select get_lock('xyz', 10) from user",
"v3-plan": "VT12001: unsupported: get_lock('xyz', 10) is allowed only with dual",
"gen4-plan": "get_lock('xyz', 10) allowed only with dual"
},
{
"comment": "select is_free_lock with non-dual table",
"query": "select is_free_lock('xyz') from user",
"v3-plan": "VT12001: unsupported: is_free_lock('xyz') is allowed only with dual",
"gen4-plan": "is_free_lock('xyz') allowed only with dual"
},

MySQL [(none)]> select get_lock('xyz', 10) from dual;
+---------------------+
| get_lock('xyz', 10) |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.025 sec)

MySQL [(none)]> select get_lock('xyz', 10) from db1.t1;
ERROR 1235 (42000): get_lock(:vtg1, :vtg2) allowed only with dual

expected result:
MySQL [mysql]> select get_lock('xyz', 10) from db1.t1;
+---------------------+
| get_lock('xyz', 10) |
+---------------------+
| 1 |
| 1 |
| 1 |
| 1 |
+---------------------+
4 rows in set (0.007 sec)

MySQL [mysql]> select get_lock('xyz', 10) from dual;
+---------------------+
| get_lock('xyz', 10) |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.004 sec)

Reproduction Steps

No response

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Summary

The proposed change is to add the ReadAfterWrite feature to WeSQL-Scale.

The ReadAfterWrite feature will ensure read operations to retrieve the data that was just written. This will result in a better user experience for clients and improved efficiency for developers.

Motivation

In a replicated MySQL cluster, the follower always takes some time to replay the binlog from the leader. Therefore, a read request may be forwarded to a lagging follower and not receive the data that was just written by the same client. The ReadWriteSplitting feature has exacerbated this phenomenon, as it automatically sends read requests to followers.

The ReadAfterWrite feature is being proposed to address the issue of read requests being forwarded to lagging followers in a replicated MySQL cluster, which can result in the client not receiving the data that was just written.

This feature would support use cases where read requests need to be performed immediately after write requests, such as in high-traffic applications or when dealing with time-sensitive data.

RFC: https://github.com/apecloud/wesql-scale/blob/feature/read_after_write/doc/design-docs/RFCS/20230414_ReadAfterWrite.md

Overview of the Issue

When VTGate is turned on, the parameter --enable-views needs to be added.

MySQL [(none)]> drop view db1.v1, db2.v1;
ERROR 1235 (42000): VT12001: unsupported: cannot drop views from multiple keyspace in a single statement

Reproduction Steps

drop database db1;
drop database db2;
create database db1;
create database db2;
create table db1.t1(c1 int);
create table db2.t1(c1 int);
create view db1.v1 as select * from db1.t1;
create view db2.v1 as select * from db2.t1;
drop view db1.v1, db2.v1;

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

related issue: #55

TestVDiff2Unsharded
TestVDiff2Sharded

TestVDiffUnsharded (0.00s)
TestVDiffSharded (0.00s)
TestVDiffAggregates (0.00s)
TestVDiffDefaults (0.00s)
TestVDiffReplicationWait (0.00s)

Overview of the Issue

{
"comment": "union with SQL_CALC_FOUND_ROWS",
"query": "(select sql_calc_found_rows id from user where id = 1 limit 1) union select id from user where id = 1",
"v3-plan": "VT12001: unsupported: SQL_CALC_FOUND_ROWS not supported with UNION",
"gen4-plan": "VT12001: unsupported: SQL_CALC_FOUND_ROWS not supported with union"
},

MySQL [db1]> (select sql_calc_found_rows id from user where id = 1 limit 1) union select id from user where id = 1;
ERROR 1235 (42000): VT12001: unsupported: SQL_CALC_FOUND_ROWS not supported with union

expected result:
MySQL [db1]> (select sql_calc_found_rows id from user where id = 1 limit 1) union select id from user where id = 1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.007 sec)

Reproduction Steps

drop database db1;
create database db1;
create table db1.user(id int, col int);
insert into db1.user values(1, 1);
use db1;
(select sql_calc_found_rows id from user where id = 1 limit 1) union select id from user where id = 1;

Binary Version

No response

Operating System and Environment details

No response

Log Fragments

No response

Github Actions runs on servers hosted by Github. If a proxy is started, it can prevent connectivity to the corresponding network. Currently, this may cause the unit tests to fail and there may be other unknown potential impacts.

TestMoveTables

Currently, when starting the vttablet process, we need to specify the init_keyspace and init_shard parameters, and these 2 parameters are used to specify which keyspace/shard the vttablet belongs to.
Since we have now normalized the vttablet, it no longer belongs to any keyspace/shard. As such, we can remove these two parameters and have it default to _vt

As the vttablet process initializes, it will persist the metadata of _vt in etcd.