search

golistic / pxmysql

Go MySQL driver using X Protocol
MIT License
14 stars 0 forks source link

readme

pxmysql - Driver using the MySQL X Protocol

Copyright (c) 2022, 2023, Geert JM Vanderkelen

Go: 1.21 license: MIT

The pxmysql package is a low-level interface that facilitates communication with a MySQL server using structured data serialized with Protocol Buffers. It also provides an adapter (driver) for the standard Go database/sql interface.

Important note: This package uses the MySQL X Protocol, which is an alternative to the conventional, well-known text-based MySQL protocol.

If you are looking for a driver that uses the MySQL port 3306, please consider using the excellent github.com/go-sql-driver/mysql driver. The driver included with the pxmysql package does not strive to be compatible with github.com/go-sql-driver/mysql.

Installation

go get -u github.com/golistic/pxmysql

Example Usage

Example using the standard Go database/sql

The driver provided by pxmysql should be compatible with any examples and tutorials that demonstrate Go's database/sql usage (using MySQL).

Note: This uses the MySQL Protocol X with TCP port 33060 (not 3306)!

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/golistic/pxmysql/register" // register the driver
)

func main() {
    db, err := sql.Open("pxmysql", "scott:tiger@tcp(127.0.0.1:33060)/somedb?useTLS=true")
    if err != nil {
        log.Fatalln(err)
    }

    var ts time.Time
    if err := db.QueryRow("SELECT NOW()").Scan(&ts); err != nil {
        log.Fatalln(err)
    }

    fmt.Printf("Server time: %s", ts)
}

The useTLS option is required when you use a user set to utilize the authentication method caching_sha2_password.

Example using the MySQL X DevAPI

Warning: during the v0.9 pre-releases, the below is subject to change.

package main

import (
    "context"
    "fmt"
    "log"
    "time"

    "github.com/golistic/xgo/xstrings"

    "github.com/golistic/pxmysql/null"
    "github.com/golistic/pxmysql/xmysql"
)

func main() {
    config := &xmysql.ConnectConfig{
        Address:  "127.0.0.1:53360", // default X Plugin port
        Username: "user_native",
        Password: xstrings.Pointer("pwd_user_native"),
        UseTLS:   true,
    }

    ctx := context.Background()

    session, err := xmysql.GetSession(ctx, config)
    if err != nil {
        log.Fatal(err)
    }

    query := "SELECT TABLE_NAME, TABLE_ROWS, CREATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = ?"
    result, err := session.ExecuteStatement(ctx, query, "pxmysql_tests")
    if err != nil {
        log.Fatal(err)
    }

    for _, row := range result.Rows {
        tableName := row.Values[0].(null.String)
        tableRows := row.Values[1].(null.Uint64)
        tableCreateTime := row.Values[2].(time.Time)

        fmt.Printf("Table %s has %d rows and was created %s.\n", tableName.String, tableRows.Uint64, tableCreateTime)
    }
}

Features

We implement the X Protocol coming with MySQL 8.0. Earlier versions of MySQL are not supported.

The following is a to-do list which shows also which features we are implementing. The order might vary, but it should give an idea where we are heading (bit like a roadmap).

Requirements

Dependencies

We strive to have as little dependencies as possible using only the Go standard library, extension or experimental packages.

We do, however, generate Go code from MySQL Server Protocol Buffer files.

Usage

This package offers two ways to communicate with the MySQL server over the X Plugin:

  1. either using Go's sql package (the "standard" way)
  2. and more directly using the subpackage xmysql

Without Using Go's SQL Driver

  1. Create a Connection object which holds configuration with which sessions are opened.
  2. Using the Connection.NewSession() method, we create or more Session objects which open the actual connection and authenticate using the MySQL Plugin.

The following example uses a user which has its authentication plugin set as mysql_native_password:

MySQL Types to Go

The below table gives an overview which type assertion needs to be used to get the Go variant of the MySQL value.

The above shows how for each row of the result, there is a slice of any-values which need to be type asserted.

MySQL Types Go Type .. can be NULL
BINARY/VARBINARY []byte null.Bytes
BIT uint64 null.Uint64
BLOB/TINYBLOB/MEDIUMBLOB/LONGBLOB []byte null.Bytes
CHAR/VARCHAR string null.String
DATETIME/TIMESTAMP time.Time null.Time
DATE time.Time null.Time
DECIMAL/NUMERIC *decimal.Decimal null.Decimal
DOUBLE float64 null.Float64
ENUM string null.Strings
FLOAT float32 null.Float32
SET []string null.Strings
SIGNED TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT int64 null.Int64
TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT string null.String
TIME time.Duration null.Duration
UNSIGNED TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT uint64 null.Uint64
YEAR int null.Int64

MySQL DECIMAL type

The MySQL DECIMAL-type is decoded into decimal.Decimal which stores the integral and fractional (scale) parts as *big.Int. This way we support for example MySQL DECIMAL(65,1) or DECIMAL(32,30).

The decimal.Decimal struct can only be used for storage and retrieval. It is not possible to do calculations. Users that need to manipulate the values should use different types retrieving integer part using Integral() and the fraction using Fractional() method.

The above example might not be useful for to most, but it is possible. Users that need to calculate further will need to get the integer and precision parts out of the decimal.Decimal type (see Integral() and Fractional() methods) (or do it in MySQL).

The string representation of decimal.Decimal will add zero-padding to the fractional part. When MySQL returns, for example, 82.003400 then the zero on the right are not trimmed.

Configuration

When creating a new connection using pxmysql.NewConnection, it is possible to provide a ConnectConfig instance. If not provided (is nil), then the default configuration is used.

The ConnectConfig-type has the following attributes:

Driver name

We use the driver name "pxmysql", which needs to be used with Go's sql.Open. To register the driver, you use an anonymous import as follows:

package yourstuff

import (
    _ "github.com/golistic/pxmysql/register"
)

Note the extra sub-package, which is different from other drivers. We do like to explicitly load, and not register whenever the driver is imported.

Some projects require the "mysql" name to be registered, using the driver name as a SQL dialect. This can be achieved by using the sub-package register/mysql:

package yourstuff

import (
    _ "github.com/golistic/pxmysql/register/mysql"
)

We do not default to "mysql" as driver name, so it is possible to use other drivers using MySQL at the same time.

Authentication methods

The following authentication methods are supported:

By default AUTO or pxmysql.AuthMethodAuto which tries the above methods in order they are mentioned.

For SHA256_MEMORY or the caching_sha2_password plugin, you need to use TLS for the first time the user connects. After that, it is possible to use non-TLS with either AUTO or SHA256_MEMORY.

MySQL Documentation

The MySQL X Protocol is documented in the following locations (similar content, and the combination is more useful):

Development

Compile Go code from MySQL X Plugin Protocol Buffer definitions

The package pxmysql needs Go-code compiled from the MySQL X Plugin Protocol Buffer definitions. This requires the protoc compiler to be installed first. After, all that needs to be done is run the following from within the root of the repository:

go run ./cmd/genprotobuf

The above application will download the necessary files from the MySQL Server GitHub repository eliminating the need to have the MySQL sources locally. The generated Go code is stored under internal/xmysql.

In the same folder, an info.md containing the MySQL version that was used to generate the code, and the timestamp when the command was run.

Keep Collations Up-To-Date

We include the collation information of MySQL 8.0 by generating a Go source file called collations_data.go. Note that only the utf8mb3 collations are considered since the X Protocol only supports this character set.

Use the following command line to (re)generate the file:

go run ./cmd/gencollations -address localhost:33060

See go run ./cmd/gencollations -help for more information.

Run tests

Tests use a MySQL server running within a Docker container. It can be started using the Docker Compose configuration found under _support/mysqld:

cd _support/pxmysql-compose
docker compose up -d

The above uses the Docker compose plugin. Alternatively, use docker-compose.

Run tests using:

go test ./...

Environment variables

Environment variables which could be useful when developing and debugging:

Tests can be configured using the following environment variables:

About The Author

Geert Vanderkelen worked for about 13 years at MySQL AB/MySQL Inc/Sun/Oracle as Support Engineer and Developer. He is the original author of MySQL Connector/Python (which started as a hobby project), and MySQL Router. Today, Geert implements backend services and more using primarily Go/GraphQL, and advocates the goodness of MySQL and its relational kin.

License

Distributed under the MIT license. See LICENSE.md for more information.