Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package mysql
import (
"context"
"database/sql"
"database/sql/driver"
"io"
"strings"
"testing"
"github.com/go-sql-driver/mysql"
"github.com/pkg/errors"
"github.com/stretchr/testify/require"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
)
var (
// testMySQLExplainJSON is the plan the fake MySQL server returns for EXPLAIN FORMAT=JSON.
testMySQLExplainJSON string
// testMySQLExplainTable is the plan the fake MySQL server returns for a tabular EXPLAIN, with the
// columns in testMySQLExplainTableColumns.
testMySQLExplainTable [][]driver.Value
// testMySQLSetErr is the error the fake MySQL server returns for SET statements.
testMySQLSetErr error
// testMySQLStatements records every statement the fake MySQL server receives, in order.
testMySQLStatements []testMySQLStatement
testMySQLConnections int
)
type testMySQLStatement struct {
conn int
query string
}
func init() {
sql.Register("test_mysql_explain", testMySQLExplainDriver{})
}
type testMySQLExplainDriver struct{}
func (testMySQLExplainDriver) Open(string) (driver.Conn, error) {
testMySQLConnections++
return &testMySQLExplainConn{id: testMySQLConnections}, nil
}
type testMySQLExplainConn struct {
id int
}
func (*testMySQLExplainConn) Prepare(string) (driver.Stmt, error) {
return nil, driver.ErrSkip
}
func (*testMySQLExplainConn) Close() error {
return nil
}
func (*testMySQLExplainConn) Begin() (driver.Tx, error) {
return nil, driver.ErrSkip
}
func (c *testMySQLExplainConn) ExecContext(_ context.Context, query string, _ []driver.NamedValue) (driver.Result, error) {
testMySQLStatements = append(testMySQLStatements, testMySQLStatement{conn: c.id, query: query})
if !strings.HasPrefix(query, "SET ") {
return nil, errors.Errorf("unexpected exec %q", query)
}
if testMySQLSetErr != nil {
return nil, testMySQLSetErr
}
return driver.ResultNoRows, nil
}
var testMySQLExplainTableColumns = []string{"id", "select_type", "table", "rows", "filtered"}
func (c *testMySQLExplainConn) QueryContext(_ context.Context, query string, _ []driver.NamedValue) (driver.Rows, error) {
testMySQLStatements = append(testMySQLStatements, testMySQLStatement{conn: c.id, query: query})
switch {
case strings.HasPrefix(query, "EXPLAIN FORMAT=JSON "):
return &testExplainResultRows{rows: [][]driver.Value{{testMySQLExplainJSON}}}, nil
case strings.HasPrefix(query, "EXPLAIN "):
return &testExplainResultRows{columns: testMySQLExplainTableColumns, rows: testMySQLExplainTable}, nil
default:
return nil, errors.Errorf("unexpected query %q", query)
}
}
type testExplainResultRows struct {
// columns defaults to the single EXPLAIN column of a JSON plan.
columns []string
rows [][]driver.Value
idx int
}
func (r *testExplainResultRows) Columns() []string {
if r.columns != nil {
return r.columns
}
return []string{"EXPLAIN"}
}
func (*testExplainResultRows) Close() error {
return nil
}
func (r *testExplainResultRows) Next(dest []driver.Value) error {
if r.idx >= len(r.rows) {
return io.EOF
}
copy(dest, r.rows[r.idx])
r.idx++
return nil
}
// newTestMySQLDriver returns a driver over the fake MySQL server whose pool keeps no idle
// connections, so statements that do not share a pinned connection land on different ones.
func newTestMySQLDriver(t *testing.T, plan string) *Driver {
t.Helper()
return newTestDriver(t, storepb.Engine_MYSQL, plan)
}
func newTestMariaDBDriver(t *testing.T, plan string) *Driver {
t.Helper()
return newTestDriver(t, storepb.Engine_MARIADB, plan)
}
func newTestDriver(t *testing.T, engine storepb.Engine, plan string) *Driver {
t.Helper()
testMySQLExplainJSON = plan
testMySQLExplainTable = nil
testMySQLSetErr = nil
testMySQLStatements = nil
db, err := sql.Open("test_mysql_explain", "")
require.NoError(t, err)
db.SetMaxIdleConns(0)
t.Cleanup(func() { _ = db.Close() })
return &Driver{dbType: engine, db: db}
}
const (
// The flagged UPDATE target is scanned first; the join filter on s comes after it.
testUpdateTargetFirstPlan = `{"query_block":{"select_id":1,"nested_loop":[
{"table":{"update":true,"table_name":"big","access_type":"ALL","rows_examined_per_scan":11330,"rows_produced_per_join":11330,"filtered":"100.00"}},
{"table":{"table_name":"s","access_type":"eq_ref","rows_examined_per_scan":1,"rows_produced_per_join":1133,"filtered":"10.00"}}
]}}`
testInsertSelectPlan = `{"query_block":{"select_id":1,
"table":{"insert":true,"table_name":"t2","access_type":"ALL"},
"insert_from":{"table":{"table_name":"t","access_type":"ALL","rows_examined_per_scan":1000,"rows_produced_per_join":100,"filtered":"10.00"}}
}}`
)
func TestCountAffectedRowsEstimatesFromJSONPlan(t *testing.T) {
// A single-table plan of the SELECT a single-table UPDATE or DELETE is explained as.
const filteredScanPlan = `{"query_block":{"select_id":1,"table":{"table_name":"td","access_type":"ALL","rows_examined_per_scan":1000,"rows_produced_per_join":100,"filtered":"10.00"}}}`
for _, tc := range []struct {
name string
plan string
statement string
explained string
want int64
}{
{
name: "update bounded by the join after the target",
plan: testUpdateTargetFirstPlan,
statement: "UPDATE big STRAIGHT_JOIN s ON big.s_id = s.id SET big.v = big.v + 1 WHERE s.flag = 1;",
explained: "UPDATE big STRAIGHT_JOIN s ON big.s_id = s.id SET big.v = big.v + 1 WHERE s.flag = 1;",
want: 1133,
},
{
name: "single-table update explained as a select",
plan: filteredScanPlan,
statement: "UPDATE td SET c = 1 WHERE d = 0;",
explained: "SELECT 1 FROM td WHERE d = 0",
want: 100,
},
{
name: "update capped by LIMIT",
plan: filteredScanPlan,
statement: "UPDATE td SET c = 1 WHERE d = 0 LIMIT 10;",
explained: "SELECT 1 FROM td WHERE d = 0 LIMIT 10",
want: 10,
},
{
name: "delete capped by LIMIT",
plan: filteredScanPlan,
statement: "DELETE FROM td WHERE d = 0 ORDER BY id LIMIT 20;",
explained: "SELECT 1 FROM td WHERE d = 0 ORDER BY id LIMIT 20",
want: 20,
},
{
name: "insert select reads the source plan",
plan: testInsertSelectPlan,
statement: "INSERT INTO t2 SELECT * FROM t WHERE d = 3;",
explained: "INSERT INTO t2 SELECT * FROM t WHERE d = 3;",
want: 100,
},
{
name: "insert select capped by LIMIT",
plan: testInsertSelectPlan,
statement: "INSERT INTO t2 SELECT * FROM t WHERE d = 3 LIMIT 30;",
explained: "INSERT INTO t2 SELECT * FROM t WHERE d = 3 LIMIT 30;",
want: 30,
},
} {
t.Run(tc.name, func(t *testing.T) {
d := newTestMySQLDriver(t, tc.plan)
got, err := d.CountAffectedRows(context.Background(), tc.statement)
require.NoError(t, err)
require.Equal(t, tc.want, got)
require.Equal(t, "EXPLAIN FORMAT=JSON "+tc.explained, testMySQLStatements[1].query)
})
}
}
func TestCountAffectedRowsScopesJSONFormatVersionToTheExplain(t *testing.T) {
const statement = "INSERT INTO t2 SELECT * FROM t WHERE d = 3;"
for _, tc := range []struct {
name string
setErr error
wantStatements []string
}{
{
name: "server with explain_json_format_version",
wantStatements: []string{
"SET SESSION explain_json_format_version = 1",
"EXPLAIN FORMAT=JSON " + statement,
"SET SESSION explain_json_format_version = DEFAULT",
},
},
{
name: "server without explain_json_format_version",
setErr: &mysql.MySQLError{Number: 1193, Message: "Unknown system variable 'explain_json_format_version'"},
wantStatements: []string{
"SET SESSION explain_json_format_version = 1",
"EXPLAIN FORMAT=JSON " + statement,
},
},
} {
t.Run(tc.name, func(t *testing.T) {
d := newTestMySQLDriver(t, testInsertSelectPlan)
testMySQLSetErr = tc.setErr
got, err := d.CountAffectedRows(context.Background(), statement)
require.NoError(t, err)
require.Equal(t, int64(100), got)
var statements []string
for _, s := range testMySQLStatements {
statements = append(statements, s.query)
require.Equal(t, testMySQLStatements[0].conn, s.conn)
}
require.Equal(t, tc.wantStatements, statements)
})
}
t.Run("other SET failures stop the estimate", func(t *testing.T) {
d := newTestMySQLDriver(t, testInsertSelectPlan)
testMySQLSetErr = &mysql.MySQLError{Number: 1227, Message: "Access denied"}
_, err := d.CountAffectedRows(context.Background(), statement)
require.ErrorContains(t, err, "failed to set explain_json_format_version")
require.Len(t, testMySQLStatements, 1)
})
}
func TestCountAffectedRowsFallsBackToTabularPlan(t *testing.T) {
// MySQL 8.0 prints no source plan for an INSERT ... SELECT that computes window functions.
const plan = `{"query_block":{"select_id":1,"table":{"insert":true,"select_id":1,"table_name":"t2","access_type":"ALL"}}}`
for _, tc := range []struct {
name string
statement string
// table is the tabular plan, which defaults to a single table with 1000 rows and 33.33% filtered.
table [][]driver.Value
want int64
}{
{
name: "rows of the first table estimated, scaled by filtered",
statement: "INSERT INTO t2 SELECT id, ROW_NUMBER() OVER (ORDER BY id) FROM t WHERE c < 10;",
want: 333,
},
{
name: "capped by LIMIT",
statement: "INSERT INTO t2 SELECT id, ROW_NUMBER() OVER (ORDER BY id) FROM t WHERE c < 10 LIMIT 50;",
want: 50,
},
{
// 3 small rows each join 100 big rows; the subquery's own block does not count.
name: "rows joined in the first query block",
statement: "INSERT INTO t2 SELECT big.id, ROW_NUMBER() OVER () FROM small JOIN big ON big.s_id = small.id WHERE small.id IN (SELECT id FROM s);",
table: [][]driver.Value{
{int64(1), "INSERT", "t2", nil, nil},
{int64(1), "SIMPLE", "small", int64(3), 100.0},
{int64(1), "SIMPLE", "big", int64(100), 100.0},
{int64(2), "SUBQUERY", "s", int64(100), 10.0},
},
want: 300,
},
{
// Each of the 300 joined rows changes a row of a and a row of b.
name: "multi-table update counts each target",
statement: "UPDATE small JOIN big ON big.s_id = small.id SET small.x = 1, big.v = 1;",
table: [][]driver.Value{
{int64(1), "UPDATE", "small", int64(3), 100.0},
{int64(1), "UPDATE", "big", int64(100), 100.0},
},
want: 600,
},
} {
t.Run(tc.name, func(t *testing.T) {
d := newTestMySQLDriver(t, plan)
testMySQLExplainTable = tc.table
if testMySQLExplainTable == nil {
testMySQLExplainTable = [][]driver.Value{
{int64(1), "INSERT", "t2", nil, nil},
{int64(1), "SIMPLE", "t", int64(1000), 33.33},
}
}
got, err := d.CountAffectedRows(context.Background(), tc.statement)
require.NoError(t, err)
require.Equal(t, tc.want, got)
require.Equal(t, "EXPLAIN FORMAT=TRADITIONAL "+tc.statement, testMySQLStatements[len(testMySQLStatements)-1].query)
})
}
}
func TestCountAffectedRowsReportsUninterpretablePlan(t *testing.T) {
// A plan without an estimate, with no tabular estimate either, fails rather than reading as zero rows.
d := newTestMySQLDriver(t, `{"query_block":{"select_id":1,"table":{"insert":true,"select_id":1,"table_name":"t2","access_type":"ALL"}}}`)
_, err := d.CountAffectedRows(context.Background(), "INSERT INTO t2 SELECT id, ROW_NUMBER() OVER () FROM t;")
require.ErrorContains(t, err, `table "t2" in the plan has no row estimate`)
}
// TestCountAffectedRowsReadsMariaDBSyntax covers the engine the driver is shared
// with but the parser is not. ParseMySQL rejects RETURNING, so a MariaDB DELETE
// carrying one never reached an EXPLAIN and the summary reported no affected rows
// for a statement that deletes many; approval rules read that count.
func TestCountAffectedRowsReadsMariaDBSyntax(t *testing.T) {
const filteredScanPlan = `{"query_block":{"select_id":1,"table":{"table_name":"td","access_type":"ALL","rows_examined_per_scan":1000,"rows_produced_per_join":100,"filtered":"10.00"}}}`
for _, tc := range []struct {
name string
statement string
explained string
want int64
}{
{
name: "delete with RETURNING",
statement: "DELETE FROM td WHERE d = 0 RETURNING c;",
explained: "SELECT 1 FROM td WHERE d = 0",
want: 100,
},
{
name: "delete with RETURNING after ORDER BY and LIMIT",
statement: "DELETE FROM td WHERE d = 0 ORDER BY id LIMIT 20 RETURNING c;",
explained: "SELECT 1 FROM td WHERE d = 0 ORDER BY id LIMIT 20",
want: 20,
},
{
name: "a form both grammars share still estimates",
statement: "UPDATE td SET c = 1 WHERE d = 0;",
explained: "SELECT 1 FROM td WHERE d = 0",
want: 100,
},
} {
t.Run(tc.name, func(t *testing.T) {
d := newTestMariaDBDriver(t, filteredScanPlan)
got, err := d.CountAffectedRows(context.Background(), tc.statement)
require.NoError(t, err)
require.Equal(t, tc.want, got)
require.Equal(t, "EXPLAIN FORMAT=JSON "+tc.explained, testMySQLStatements[1].query)
})
}
}
// TestCountAffectedRowsRejectsMariaDBSyntaxOnMySQL is the other side: the same
// statement is not MySQL, and the MySQL path must still refuse it rather than
// quietly estimate something.
func TestCountAffectedRowsRejectsMariaDBSyntaxOnMySQL(t *testing.T) {
d := newTestMySQLDriver(t, `{"query_block":{"select_id":1}}`)
_, err := d.CountAffectedRows(context.Background(), "DELETE FROM td WHERE d = 0 RETURNING c;")
require.Error(t, err)
}