A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

Last update: Jan 2, 2023

Comments: 16

go-mysql-server

go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax) and executes queries on data sources of your choice. A simple in-memory database and table implementation are provided, and you can query any data source you want by implementing a few interfaces.

go-mysql-server also provides a server implementation compatible with the MySQL wire protocol. That means it is compatible with MySQL ODBC, JDBC, or the default MySQL client shell interface.

Dolt, a SQL database with Git-style versioning, is the main database implementation of this package. Check out that project for reference implementations.

Scope of this project

These are the goals of go-mysql-server:

Be a generic extensible SQL engine that performs queries on your data sources.
Provide a simple database implementation suitable for use in tests.
Define interfaces you can implement to query your own data sources.
Provide a runnable server speaking the MySQL wire protocol, connected to data sources of your choice.
Optimize query plans.
Allow implementors to add their own analysis steps and optimizations.
Support indexed lookups and joins on data tables that support them.
Support external index driver implementations such as pilosa.
With few caveats and using a full database implementation, be a drop-in MySQL database replacement.

Non-goals of go-mysql-server:

Be an application/server you can use directly.
Provide any kind of backend implementation (other than the memory one used for testing) such as json, csv, yaml. That's for clients to implement and use.

What's the use case of go-mysql-server?

go-mysql-server has two primary uses case:

Stand-in for MySQL in a golang test environment, using the built-in memory database implementation.
Providing access to aribtrary data sources with SQL queries by implementing a handful of interfaces. The most complete real-world implementation is Dolt.

Installation

The import path for the package is github.com/dolthub/go-mysql-server.

To install it, run:

go get github.com/dolthub/go-mysql-server

Go Documentation

go-mysql-server godoc

SQL syntax

The goal of go-mysql-server is to support 100% of the statements that MySQL does. We are continuously adding more functionality to the engine, but not everything is supported yet. To see what is currently included check the SUPPORTED file.

Third-party clients

We support and actively test against certain third-party clients to ensure compatibility between them and go-mysql-server. You can check out the list of supported third party clients in the SUPPORTED_CLIENTS file along with some examples on how to connect to go-mysql-server using them.

Available functions

Name	Description
`ABS(expr)`	returns the absolute value of an expression
`ACOS(expr)`	returns the arccos of an expression
`ARRAY_LENGTH(json)`	if the json representation is an array, this function returns its size.
`ASIN(expr)`	returns the arcsin of an expression
`ATAN(expr)`	returs the arctan of an expression
`AVG(expr)`	returns the average value of expr in all rows.
`CEIL(number)`	returns the smallest integer value that is greater than or equal to `number`.
`CEILING(number)`	returns the smallest integer value that is greater than or equal to `number`.
`CHARACTER_LENGTH(str)`	returns the length of the string in characters.
`CHAR_LENGTH(str)`	returns the length of the string in characters.
`COALESCE(...)`	returns the first non-null value in a list.
`CONCAT(...)`	concatenates any group of fields into a single string.
`CONCAT_WS(sep, ...)`	concatenates any group of fields into a single string. The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.
`CONNECTION_ID()`	returns the current connection ID.
`COS(expr)`	returns the cosine of an expression.
`COT(expr)`	returns the arctangent of an expression.
`COUNT(expr)`	returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement.
`CURRENT_USER()`	returns the current user
`DATE(date)`	returns the date part of the given `date`.
`DATETIME(expr)`	returns a `DATETIME` value for the expression given (e.g. the string '2020-01-02').
`DATE_ADD(date, interval)`	adds the interval to the given `date`.
`DATE_SUB(date, interval)`	subtracts the interval from the given `date`.
`DAY(date)`	is a synonym for DAYOFMONTH().
`DAYOFMONTH(date)`	returns the day of the month (0-31).
`DAYOFWEEK(date)`	returns the day of the week of the given `date`.
`DAYOFYEAR(date)`	returns the day of the year of the given `date`.
`DEGREES(expr)`	returns the number of degrees in the radian expression given.
`EXPLODE(...)`	generates a new row in the result set for each element in the expressions provided.
`FIRST(expr)`	returns the first value in a sequence of elements of an aggregation.
`FLOOR(number)`	returns the largest integer value that is less than or equal to `number`.
`FROM_BASE64(str)`	decodes the base64-encoded string `str`.
`GREATEST(...)`	returns the greatest numeric or string value.
`HOUR(date)`	returns the hours of the given `date`.
`IFNULL(expr1, expr2)`	if `expr1` is not NULL, it returns `expr1`; otherwise it returns `expr2`.
`IF(expr1, expr2, expr3)`	if `expr1` evaluates to true, retuns `expr2`. Otherwise returns `expr3`.
`INSTR(str1, str2)`	returns the 1-based index of the first occurence of `str2` in `str1`, or 0 if it does not occur.
`IS_BINARY(blob)`	returns whether a `blob` is a binary file or not.
`JSON_EXTRACT(json_doc, path, ...)`	extracts data from a json document using json paths. Extracting a string will result in that string being quoted. To avoid this, use `JSON_UNQUOTE(JSON_EXTRACT(json_doc, path, ...))`.
`JSON_UNQUOTE(json)`	unquotes JSON value and returns the result as a utf8mb4 string.
`LAST(expr)`	returns the last value in a sequence of elements of an aggregation.
`LEAST(...)`	returns the smaller numeric or string value.
`LEFT(str, int)`	returns the first N characters in the string given.
`LENGTH(str)`	returns the length of the string in bytes.
`LN(X)`	returns the natural logarithm of `X`.
`LOG(X), LOG(B, X)`	if called with one parameter, this function returns the natural logarithm of `X`. If called with two parameters, this function returns the logarithm of `X` to the base `B`. If `X` is less than or equal to 0, or if `B` is less than or equal to 1, then NULL is returned.
`LOG10(X)`	returns the base-10 logarithm of `X`.
`LOG2(X)`	returns the base-2 logarithm of `X`.
`LOWER(str)`	returns the string `str` with all characters in lower case.
`LPAD(str, len, padstr)`	returns the string `str`, left-padded with the string `padstr` to a length of `len` characters.
`LTRIM(str)`	returns the string `str` with leading space characters removed.
`MAX(expr)`	returns the maximum value of `expr` in all rows.
`MID(str, pos, [len])`	returns a substring from the provided string starting at `pos` with a length of `len` characters. If no `len` is provided, all characters from `pos` until the end will be taken.
`MIN(expr)`	returns the minimum value of `expr` in all rows.
`MINUTE(date)`	returns the minutes of the given `date`.
`MONTH(date)`	returns the month of the given `date`.
`NOW()`	returns the current timestamp.
`NULLIF(expr1, expr2)`	returns NULL if `expr1 = expr2` is true, otherwise returns `expr1`.
`POW(X, Y)`	returns the value of `X` raised to the power of `Y`.
`POWER(X, Y)`	synonym for `POW`
`RADIANS(expr)`	returns the radian value of the degrees argument given
`RAND(expr?)`	returns a random number in the range 0 <= x < 1. If an argument is given, it is used to seed the random number generator.
`REGEXP_MATCHES(text, pattern, [flags])`	returns an array with the matches of the `pattern` in the given `text`. Flags can be given to control certain behaviours of the regular expression. Currently, only the `i` flag is supported, to make the comparison case insensitive.
`REPEAT(str, count)`	returns a string consisting of the string `str` repeated `count` times.
`REPLACE(str,from_str,to_str)`	returns the string `str` with all occurrences of the string `from_str` replaced by the string `to_str`.
`REVERSE(str)`	returns the string `str` with the order of the characters reversed.
`ROUND(number, decimals)`	rounds the `number` to `decimals` decimal places.
`RPAD(str, len, padstr)`	returns the string `str`, right-padded with the string `padstr` to a length of `len` characters.
`RTRIM(str)`	returns the string `str` with trailing space characters removed.
`SECOND(date)`	returns the seconds of the given `date`.
`SIN(expr)`	returns the sine of the expression given.
`SLEEP(seconds)`	waits for the specified number of seconds (can be fractional).
`SOUNDEX(str)`	returns the soundex of a string.
`SPLIT(str,sep)`	returns the parts of the string `str` split by the separator `sep` as a JSON array of strings.
`SQRT(X)`	returns the square root of a nonnegative number `X`.
`SUBSTR(str, pos, [len])`	returns a substring from the string `str` starting at `pos` with a length of `len` characters. If no `len` is provided, all characters from `pos` until the end will be taken.
`SUBSTRING(str, pos, [len])`	returns a substring from the string `str` starting at `pos` with a length of `len` characters. If no `len` is provided, all characters from `pos` until the end will be taken.
`SUBSTRING_INDEX(str, delim, count)`	Returns a substring after `count` appearances of `delim`. If `count` is negative, counts from the right side of the string.
`SUM(expr)`	returns the sum of `expr` in all rows.
`TAN(expr)`	returns the tangent of the expression given.
`TIMEDIFF(expr1, expr2)`	returns expr1 − expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.
`TIMESTAMP(expr)`	returns a timestamp value for the expression given (e.g. the string '2020-01-02').
`TO_BASE64(str)`	encodes the string `str` in base64 format.
`TRIM(str)`	returns the string `str` with all spaces removed.
`UNIX_TIMESTAMP(expr?)`	returns the datetime argument to the number of seconds since the Unix epoch. With nor argument, returns the number of execonds since the Unix epoch for the current time.
`UPPER(str)`	returns the string `str` with all characters in upper case.
`USER()`	returns the current user name.
`UTC_TIMESTAMP()`	returns the current UTC timestamp.
`WEEKDAY(date)`	returns the weekday of the given `date`.
`YEAR(date)`	returns the year of the given `date`.
`YEARWEEK(date, mode)`	returns year and week for a date. The year in the result may be different from the year in the date argument for the first and the last week of the year.

Configuration

The behaviour of certain parts of go-mysql-server can be configured using either environment variables or session variables.

Session variables are set using the following SQL queries:

SET <variable name> = <value>

Name	Type	Description
`INMEMORY_JOINS`	environment	If set it will perform all joins in memory. Default is off.
`inmemory_joins`	session	If set it will perform all joins in memory. Default is off. This has precedence over `INMEMORY_JOINS`.
`MAX_MEMORY`	environment	The maximum number of memory, in megabytes, that can be consumed by go-mysql-server. Any in-memory caches or computations will no longer try to use memory when the limit is reached. Note that this may cause certain queries to fail if there is not enough memory available, such as queries using DISTINCT, ORDER BY or GROUP BY with groupings.
`DEBUG_ANALYZER`	environment	If set, the analyzer will print debug messages. Default is off.

Example

go-mysql-server contains a SQL engine and server implementation. So, if you want to start a server, first instantiate the engine and pass your sql.Database implementation.

It will be in charge of handling all the logic to retrieve the data from your source. Here you can see an example using the in-memory database implementation:

package main

import (
    "time"

    "github.com/dolthub/go-mysql-server/auth"
    "github.com/dolthub/go-mysql-server/memory"
    "github.com/dolthub/go-mysql-server/server"
    "github.com/dolthub/go-mysql-server/sql"
    sqle "github.com/dolthub/go-mysql-server"
)

func main() {
    driver := sqle.NewDefault()
    driver.AddDatabase(createTestDatabase())

    config := server.Config{
        Protocol: "tcp",
        Address:  "localhost:3306",
        Auth:     auth.NewNativeSingle("user", "pass", auth.AllPermissions),
    }

    s, err := server.NewDefaultServer(config, driver)
    if err != nil {
        panic(err)
    }

    s.Start()
}

func createTestDatabase() *memory.Database {
    const (
        dbName    = "test"
        tableName = "mytable"
    )

    db := memory.NewDatabase(dbName)
    table := memory.NewTable(tableName, sql.Schema{
        {Name: "name", Type: sql.Text, Nullable: false, Source: tableName},
        {Name: "email", Type: sql.Text, Nullable: false, Source: tableName},
        {Name: "phone_numbers", Type: sql.JSON, Nullable: false, Source: tableName},
        {Name: "created_at", Type: sql.Timestamp, Nullable: false, Source: tableName},
    })

    db.AddTable(tableName, table)
    ctx := sql.NewEmptyContext()

    rows := []sql.Row{
        sql.NewRow("John Doe", "[email protected]", []string{"555-555-555"}, time.Now()),
        sql.NewRow("John Doe", "[email protected]", []string{}, time.Now()),
        sql.NewRow("Jane Doe", "[email protected]", []string{}, time.Now()),
        sql.NewRow("Evil Bob", "[email protected]", []string{"555-666-555", "666-666-666"}, time.Now()),
	}

    for _, row := range rows {
        table.Insert(ctx, row)
    }

    return db
}

Then, you can connect to the server with any MySQL client:

> mysql --host=127.0.0.1 --port=3306 -u user -ppass test -e "SELECT * FROM mytable"
+----------+-------------------+-------------------------------+---------------------+
| name     | email             | phone_numbers                 | created_at          |
+----------+-------------------+-------------------------------+---------------------+
| John Doe | [email protected]      | ["555-555-555"]               | 2018-04-18 10:42:58 |
| John Doe | [email protected]   | []                            | 2018-04-18 10:42:58 |
| Jane Doe | [email protected]      | []                            | 2018-04-18 10:42:58 |
| Evil Bob | [email protected] | ["555-666-555","666-666-666"] | 2018-04-18 10:42:58 |
+----------+-------------------+-------------------------------+---------------------+

See the complete example here.

Queries examples

SELECT count(name) FROM mytable
+---------------------+
| COUNT(mytable.name) |
+---------------------+
|                   4 |
+---------------------+

SELECT name,year(created_at) FROM mytable
+----------+--------------------------+
| name     | YEAR(mytable.created_at) |
+----------+--------------------------+
| John Doe |                     2018 |
| John Doe |                     2018 |
| Jane Doe |                     2018 |
| Evil Bob |                     2018 |
+----------+--------------------------+

SELECT email FROM mytable WHERE name = 'Evil Bob'
+-------------------+
| email             |
+-------------------+
| [email protected] |
+-------------------+

Custom data source implementation

To create your own data source implementation you need to implement the following interfaces:

sql.Database interface. This interface will provide tables from your data source. You can also implement other interfaces on your database to unlock additional functionality:
- sql.TableCreator to support creating new tables
- sql.TableDropper to support dropping tables
- sql.TableRenamer to support renaming tables
- sql.ViewCreator to support creating persisted views on your tables
- sql.ViewDropper to support dropping persisted views
sql.Table interface. This interface will provide rows of values from your data source. You can also implement other interfaces on your table to unlock additional functionality:
- sql.InsertableTable to allow your data source to be updated with INSERT statements.
- sql.UpdateableTable to allow your data source to be updated with UPDATE statements.
- sql.DeletableTable to allow your data source to be updated with DELETE statements.
- sql.ReplaceableTable to allow your data source to be updated with REPLACE statements.
- sql.AlterableTable to allow your data source to have its schema modified by adding, dropping, and altering columns.
- sql.IndexedTable to declare your table's native indexes to speed up query execution.
- sql.IndexAlterableTable to accept the creation of new native indexes.
- sql.ForeignKeyAlterableTable to signal your support of foreign key constraints in your table's schema and data.
- sql.ProjectedTable to return rows that only contain a subset of the columns in the table. This can make query execution faster.
- sql.FilteredTable to filter the rows returned by your table to those matching a given expression. This can make query execution faster (if your table implementation can filter rows more efficiently than checking an expression on every row in a table).

You can see a really simple data source implementation in the memory package.

Testing your data source implementation

go-mysql-server provides a suite of engine tests that you can use to validate that your implementation works as expected. See the enginetest package for details and examples.

Indexes

go-mysql-server exposes a series of interfaces to allow you to implement your own indexes so you can speed up your queries.

Native indexes

Tables can declare that they support native indexes, which means that they support efficiently returning a subset of their rows that match an expression. The memory package contains an example of this behavior, but please note that it is only for example purposes and doesn't actually make queries faster (although we could change this in the future).

Integrators should implement the sql.IndexedTable interface to declare which indexes their tables support and provide a means of returning a subset of the rows based on an sql.IndexLookup provided by their sql.Index implementation. There are a variety of extensions to sql.Index that can be implemented, each of which unlocks additional capabilities:

sql.Index. Base-level interface, supporting equality lookups for an index.
sql.AscendIndex. Adds support for > and >= indexed lookups.
sql.DescendIndex. Adds support for < and <= indexed lookups.
sql.NegateIndex. Adds support for negating other index lookups.
sql.MergeableIndexLookup. Adds support for merging two sql.IndexLookups together to create a new one, representing AND and OR expressions on indexed columns.

Custom index driver implementation

Index drivers provide different backends for storing and querying indexes, without the need for a table to store and query its own native indexes. To implement a custom index driver you need to implement a few things:

sql.IndexDriver interface, which will be the driver itself. Not that your driver must return an unique ID in the ID method. This ID is unique for your driver and should not clash with any other registered driver. It's the driver's responsibility to be fault tolerant and be able to automatically detect and recover from corruption in indexes.
sql.Index interface, returned by your driver when an index is loaded or created.
sql.IndexValueIter interface, which will be returned by your sql.IndexLookup and should return the values of the index.
Don't forget to register the index driver in your sql.Context using context.RegisterIndexDriver(mydriver) to be able to use it.

To create indexes using your custom index driver you need to use extension syntax USING driverid on the index creation statement. For example:

CREATE INDEX foo ON table USING driverid (col1, col2)

go-mysql-server does not provide a production index driver implementation. We previously provided a pilosa implementation, but removed it due to the difficulty of supporting it on all platforms (pilosa doesn't work on Windows).

You can see an example of a driver implementation in the memory package.

Metrics

go-mysql-server utilizes github.com/go-kit/kit/metrics module to expose metrics (counters, gauges, histograms) for certain packages (so far for engine, analyzer, regex). If you already have metrics server (prometheus, statsd/statsite, influxdb, etc.) and you want to gather metrics also from go-mysql-server components, you will need to initialize some global variables by particular implementations to satisfy following interfaces:

// Counter describes a metric that accumulates values monotonically.
type Counter interface {
	With(labelValues ...string) Counter
	Add(delta float64)
}

// Gauge describes a metric that takes specific values over time.
type Gauge interface {
	With(labelValues ...string) Gauge
	Set(value float64)
	Add(delta float64)
}

// Histogram describes a metric that takes repeated observations of the same
// kind of thing, and produces a statistical summary of those observations,
// typically expressed as quantiles or buckets.
type Histogram interface {
	With(labelValues ...string) Histogram
	Observe(value float64)
}

You can use one of go-kit implementations or try your own. For instance, we want to expose metrics for prometheus server. So, before we start mysql engine, we have to set up the following variables:

import(
    "github.com/go-kit/kit/metrics/prometheus"
    promopts "github.com/prometheus/client_golang/prometheus"
    "github.com/prometheus/client_golang/prometheus/promhttp"
)

//....

// engine metrics
sqle.QueryCounter = prometheus.NewCounterFrom(promopts.CounterOpts{
		Namespace: "go_mysql_server",
		Subsystem: "engine",
		Name:      "query_counter",
	}, []string{
		"query",
	})
sqle.QueryErrorCounter = prometheus.NewCounterFrom(promopts.CounterOpts{
    Namespace: "go_mysql_server",
    Subsystem: "engine",
    Name:      "query_error_counter",
}, []string{
    "query",
    "error",
})
sqle.QueryHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "engine",
    Name:      "query_histogram",
}, []string{
    "query",
    "duration",
})

// analyzer metrics
analyzer.ParallelQueryCounter = prometheus.NewCounterFrom(promopts.CounterOpts{
    Namespace: "go_mysql_server",
    Subsystem: "analyzer",
    Name:      "parallel_query_counter",
}, []string{
    "parallelism",
})

// regex metrics
regex.CompileHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "regex",
    Name:      "compile_histogram",
}, []string{
    "regex",
    "duration",
})
regex.MatchHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "regex",
    Name:      "match_histogram",
}, []string{
    "string",
    "duration",
})

One important note - internally we set some labels for metrics, that's why have to pass those keys like "duration", "query", "driver", ... when we register metrics in prometheus. Other systems may have different requirements.

Powered by go-mysql-server

dolt
gitbase (defunct)

Acknowledgements

go-mysql-server was originally developed by the {source-d} organzation, and this repository was originally forked from src-d. We want to thank the entire {source-d} development team for their work on this project, especially Miguel Molina (@erizocosmico) and Juanjo Álvarez Martinez (@juanjux).

License

Apache License 2.0, see LICENSE

Owner

DoltHub

https://github.com/liquidata-inc/go-mysql-server/

Comments

Unbound variables in subqueries

When using the "driver" package, I kept running into the following errors:

DEBU[0115] [6.873ms] [rows:-] SELECT count(*) FROM information_schema.tables WHERE table_schema = 'test-db' AND table_name = 'entries' AND table_type = 'BASE TABLE' 
DEBU[0115] [0.853ms] [rows:0] CREATE TABLE `entries` (`id` bigint AUTO_INCREMENT,`value` bigint,PRIMARY KEY (`id`)) 
ERRO[0115] unbound variable "v1" in query: [0.778ms] [rows:0] INSERT INTO `entries` (`value`,`id`) VALUES (4,1) 
ERRO[0115] unbound variable "v1" in query: [0.614ms] [rows:0] INSERT INTO `entries` (`value`,`id`) VALUES (10,2) 
ERRO[0115] unbound variable "v1" in query: [0.622ms] [rows:0] INSERT INTO `entries` (`value`,`id`) VALUES (30,3)

The fix appears to be changing driver/value.go to add a "v" prefix to ordinal placeholder values:

                        name = "v"+strconv.FormatInt(int64(v.Ordinal), 10)

... because the SQL plan code appears to expect "v1" "v2" and so on.

group_by: collation-aware grouping
Hi doltfriends! Here's another compatibility fix. This one I'm more confident about: GROUP BY is not collation aware when grouping!

The included test reproduces the issue trivially, but for completeness: when using a GROUP BY statement that includes a textual column, the column's collation needs to be taken into account when creating the aggregation groups.

E.g., for the following schema and query

CREATE TABLE test (id bigint, title varchar(255) collate utf8mb4_0900_ai_ci, PRIMARY KEY(id)); INSERT INTO `test` (title) VALUES ('val1'), ('VAL1'), ('val2'), ('VAL2') SELECT COUNT(*) FROM test GROUP BY title

We should expect the result of the SELECT to contain two rows, as there are only two groups when grouping by title. Even though we've inserted 4 unique values, the utf8mb4_0900_ai_ci with which the column is collated is case insensitive, so in practice, 'val1' = 'VAL1' and 'val2' = 'VAL2' when grouping. This is not the current behavior.

The fix is straightforward: use a weight string when hashing to generate the grouping key instead of using the expression's literal value. I did a small refactoring in collations.go so that we can write the weight string directly into the grouping key's hasher and continue using the same code for the existing HashUint API.

cc @dbussink

Unique indexes do not work in the in-memory implementation

This might be a problem with the way I define the index.

I have the following database:

func testDatabase() *memory.Database {
	ctx := sql.NewEmptyContext()
	db := memory.NewDatabase("users")

	tableName := "users"
	table := memory.NewTable(tableName, sql.Schema{
		{Name: "id", Type: newVarChar(26), Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "namespace", Type: newVarChar(50), Nullable: false, Source: tableName},
		{Name: "name", Type: newVarChar(50), Nullable: false, Source: tableName},
		{Name: "label", Type: newVarChar(255), Nullable: false, Source: tableName},
		{Name: "created_at", Type: sql.Datetime, Nullable: false, Source: tableName},
		{Name: "updated_at", Type: sql.Datetime, Nullable: false, Source: tableName},
	})
	table.CreateIndex(ctx, "namespace__name", sql.IndexUsing_Default, sql.IndexConstraint_Unique, []sql.IndexColumn{
		{Name: "namespace"}, {Name: "name"},
	}, "")
	db.AddTable(tableName, table)

	return db
}

func newVarChar(length int64) sql.StringType {
	return sql.MustCreateStringWithDefaults(sqltypes.VarChar, length)
}

Once I start the db and do a "SHOW CREATE TABLE", I see my index:

CREATE TABLE `users` (
  `id` varchar(26) NOT NULL,
  `namespace` varchar(50) NOT NULL,
  `name` varchar(50) NOT NULL,
  `label` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `namespace__name` (`namespace`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

And yet, I can insert as many identical namespace/name pairs as I want, no error is returned.

LastInsertId always returns 0
When running several insert commands like

result, err := db.Exec("INSERT INTO mytable SET number = 18;") id, err := result.LastInsertId()

Against a simple table like

CREATE TABLE IF NOT EXISTS mytable ( id int unsigned NOT NULL AUTO_INCREMENT, number int unsigned DEFAULT NULL, PRIMARY KEY (id), ) DEFAULT CHARSET=utf8;

the returned id is always 0. While the go-mysql-driver returns the correct pkey.

Used libraries: github.com/dolthub/go-mysql-server v0.6.1-0.20201228192939-415fc40f3a71 github.com/go-sql-driver/mysql v1.5.0
Support for prepared statements

This is more important than we have been treating it, because there are many drivers that do this under the hood without clients explicitly asking for it. From https://github.com/liquidata-inc/go-mysql-server/issues/169

Intermittent DATA RACE error when creating MySQL Servers

We use the in-memory implementation provided during our tests and recently we've seen intermittent data race failures. Since they only happen on occasion they are annoying to debug because the entire stack trace is in this library, we thought you may be able to help.

WARNING: DATA RACE
Write at 0x00c00066a00f by goroutine 77:
  github.com/dolthub/vitess/go/mysql.writeUint32()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/go/mysql/encoding.go:111 +0x2d9
  github.com/dolthub/vitess/go/mysql.(*Conn).writeHandshakeV10()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/go/mysql/server.go:540 +0x23f
  github.com/dolthub/vitess/go/mysql.(*Listener).handle()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/go/mysql/server.go:299 +0x3a8

Previous read at 0x00c00066a00f by goroutine 66:
  runtime.slicebytetostring()
      /opt/hostedtoolcache/go/1.16.5/x64/src/runtime/string.go:80 +0x0
  github.com/dolthub/go-mysql-server/sql.stringType.Convert()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/sql/stringtype.go:254 +0x873
  github.com/dolthub/go-mysql-server/sql.(*stringType).Convert()
      <autogenerated>:1 +0xaa
  github.com/dolthub/go-mysql-server/server.bindingsToExprs()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/server/handler.go:197 +0x4d4
  github.com/dolthub/go-mysql-server/server.(*Handler).doQuery()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/server/handler.go:292 +0x1cfa
  github.com/dolthub/go-mysql-server/server.(*Handler).errorWrappedDoQuery()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/server/handler.go:477 +0x71
  github.com/dolthub/go-mysql-server/server.(*Handler).ComStmtExecute()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/server/handler.go:99 +0xaf
  github.com/dolthub/vitess/go/mysql.(*Conn).handleNextCommand()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/go/mysql/conn.go:1060 +0x3418
  github.com/dolthub/vitess/go/mysql.(*Listener).handle()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/go/mysql/server.go:472 +0x181a

Goroutine 77 (running) created at:
  github.com/dolthub/vitess/go/mysql.(*Listener).Accept()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/go/mysql/server.go:266 +0x146
  github.com/dolthub/go-mysql-server/server.(*Server).Start()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/server/server.go:118 +0x4f
  github.com/verticalscope/fl-core/pkg/test.NewMysqlServer.func2()
      /home/runner/work/fl-core/fl-core/pkg/test/mysql.go:100 +0x50

Goroutine 66 (running) created at:
  github.com/dolthub/vitess/go/mysql.(*Listener).Accept()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/go/mysql/server.go:266 +0x146
  github.com/dolthub/go-mysql-server/server.(*Server).Start()
      /home/runner/go/pkg/mod/github.com/dolthub/[email protected]/server/server.go:118 +0x4f
  github.com/verticalscope/fl-core/pkg/test.NewMysqlServer.func2()
      /home/runner/work/fl-core/fl-core/pkg/test/mysql.go:100 +0x50

These errors appear while running our tests with the std library testing library and asserts with the testify library.

Question/Feature Request: How can I increase the parallelism of expression evaluation?

I have a situation where I have a custom SQL function that is a bit slow (like a single network request slow). Because rows are demanded one at a time from a RowIter, these expressions are evaluated one at a time, meaning we run these network requests one at a time. I would like some way to evaluate these rows in parallel as this would greatly improve the speed of my queries. I can't prefetch everything because I do not have all the data needed for all the network requests until query execution. A while back I tried making a custom SQL plan node which wraps another node and prefetches rows from its child node in parallel, but I ran into some issues where the RowIter implementation I was calling misbehaved as it was not threadsafe. Do you have any suggestions for me? Was the parallel prefetch node a good/bad idea? I really appreciate your help with this, thanks.
Error with passing time.Time as a field for saving

I am trying this as for writing DB unit tests. Pretty much seeing if I can get the the relvant tables so we can write some tests against the DB code that tries to interact with them. So mostly trying to work with code and SQL that we know works against a MySQL instance and see if it can do the same things with this instance (so can't really change around the syntax or calling too much).

Having an issue where it's returning: Error 1105 (HY000): incompatible conversion to SQL type: TIMESTAMP

From the time.Now() instance passed as part of a Prepared Statement.

The field itself is being used with: CREATE TABLE IF NOT EXIXSTS ( ... TIMESTAMP NOT NULL ...) (which I thought should be safe enough)

A bit of digging around in your codebase shows it's coming from /sql/datetime.go->ConvertWithoutRangeCheck

It looks like it thinks the time instance being passed is a uint8[] instead of the actual time.Time or any of the other int types. Dumping it as-is (with Fmt.Println), shows the slice contains: [50 48 50 50 45 48 55 45 50 57 32 48 52 58 53 48 58 52 55 46 49 57 49 51 54 54] (which probably corresponds to something in my local GMT+10 instance)

This looks very similar to this issue: https://4rum.dev/t/unsupported-scan-storing-driver-value-type-uint8-into-type-time-time/103/2 That others have reported against MySQL itself.

Having a quick scan through the repo, nothing is standing out for me as a similar flag / config value that could be set to replicate this.

Unsure what the actual fix happened for this, but will add more if I get closer to figuring out ways around this.
Cast equivalent float64s to int64 rather than failing with ErrInvalidValue
When working with Pandas (both with and without doltpy) I ran into this issue multiple times - integer columns are converted to floats (due to the way Python handles nan values), which then causes dolt table import to complain with the following error despite the floats being "integral":

Rows Processed: 0, Additions: 0, Modifications: 0, Had No Effect: 0 A bad row was encountered while moving data. Bad Row: error: '10.0' is not a valid value for 'INT' These can be ignored using the '--continue'

This PR converts "integral"/equivalent floats to int64s to prevent this from happening. This still prevents non-integral floats from being imported, e.g.:

Rows Processed: 0, Additions: 0, Modifications: 0, Had No Effect: 0 A bad row was encountered while moving data. Bad Row: error: '10.1' is not a valid value for 'INT' These can be ignored using the '--continue'

I'm not sure how/where this should be tested, but if it is an acceptable PR I'll be happy to write the tests for it too.

P.S. The isIntegral function can be removed and used in the if-statement as a condition if that's more preferable, though I think it should be documented (perhaps in a comment) since it's purpose may not be immediately obvious.
Client hangs if go-mysql-server takes too long to return query results

I have written very simple implementations of Database and Table/Partition so that I can use go-mysql-server as a MySQL frontend to a custom in-house reporting API. Each Table corresponds to a particular report specification. The design at present lazily fetches the report schema from an API when Schema() is called, and lazily runs a backend report when PartitionRows() is called. And for relatively fast-running reports, it works very nicely!

But I have also run into an issue where various clients (MySQL CLI, Tableau) seem to hang forever if go-mysql-server takes a long time to run a query. The server is still responsive to new connections & queries, but the client that was waiting on the long-running query never gets the result, and generally has to be killed.

In my implementation, I have seen this both when PartitionRows is slow (say a backend report takes minutes to fetch the table rows), and when doing information_schema queries (which causes Schema() to be called for all tables, which can take minutes with all the API calls in the current naive implementation.) I can cache report schemas to make that case faster, but I don't have any way to avoid medium-to-long-running reports.

Do you have any ideas for why the clients would never get their query results in these cases?
Index error lost in parent call

Hi,

First, thank you for the great package!

I'm not sure if this is intentional, an error reported by a custom index implementation is not handled. The code is here: https://github.com/dolthub/go-mysql-server/blob/main/sql/analyzer/indexes.go#L71

Should errInAnalysis = err be added here (same as the previous if line 61) so the error is reported to the caller?

If so, I can send a PR for the fix. If not, how should a custom index implementation handle errors that should stop the process?

Thanks.
verify and fix info schema tables - 2
Complete tables: CHARACTER_SETS COLLATIONS ENGINES - only the InnoDB engine is supported SCHEMATA COLLATION_CHARACTER_SET_APPLICABILITY

Some info missing: COLUMN_STATISTICS - histogram json column only has buckets key values. PROCESSLIST - status is not defined completely; "Query" command is supported only.
Transaction support for in-memory database

Currently unsupported. All transaction statements e.g. start transaction, commit, rollback are no-ops.

Supporting this is challenging, as it requires implementing something like journaled storage.
verify and add more info schema tables
Complete tables:

Fixed and verified row values for tables: TRIGGERS, CHECK_CONSTRAINTS, KEY_COLUMN_USAGE, KEY_COLUMN_USAGE, TABLE_CONSTRAINTS and SCHEMATA

Added REFERENTAIL_CONSTRAINTS table

Fixed, but has missing info tables:

TABLES - missing columns/does not match MySQL values: avg_row_length, data_length, max_data_length, max_data_length, data_free

ROUTINES - missing stored functions

VIEWS - view_definition value does not match MySQL

Changes:

added CreateViewStmt member to View definition to keep the information for algorithm, definer and security when it's created first. These are needed for information_schema.views table.

added PeekNextAutoIncrementValue to AutoIncrementTable interface for getting the next auto_increment value without incrementing the current value. This is implemented for both memory.Table and WritableDoltTable.

memory database with binary column cannot be updated

when I try to update a table contains a binary column, the result is comparing uncomparable type []uint8 this issue is similar as issues/361, but when I read code, I found that update will check unique key, it will compare the new value and the old value, and that cause the error

// Update the given row from the table.
func (t *tableEditor) Update(ctx *sql.Context, oldRow sql.Row, newRow sql.Row) error {

	...

	// Throw a unique key error if any unique indexes are defined
	for _, cols := range t.uniqueIdxCols {
		if hasNullForAnyCols(newRow, cols) {
			continue
		}
		existing, found, err := t.ea.GetByCols(newRow, cols)  // this
		if err != nil {
			return err
		}

		if found {
			return sql.NewUniqueKeyErr(formatRow(newRow, cols), false, existing)
		}
	}

in GetByCols

func columnsMatch(colIndexes []int, row sql.Row, row2 sql.Row) bool {
	for _, i := range colIndexes {
		if row[i] != row2[i] {
			return false
		}
	}
	return true
}

Please help to confirm the problem~

[MySQL Compat] SUM result types
As discussed in https://github.com/dolthub/go-mysql-server/pull/1476#issuecomment-1352731490 --

The result type for SUM aggregations in go-mysql-server does not match MySQL's. Even though the actual SumBuffer that performs the computation internally uses either float64 or Decimal, the type for the resulting column in the planned schema defaults to the type of the underlying column.

As an example: in MySQL, summing a column of INT (i.e. int32) results in a DECIMAL result for the output row. This is the same when summing any other integral type, regardless of whether the type has overflowed or not. In go-mysql-server, summing a column of INT returns INT. In fact, summing any integral type returns the same integral type.

The general rule for MySQL types in SUMs is as follows:

Any accurate numeric type (e.g. INT, BIGINT, SMALLINT, TINYINT, DECIMAL, etc) sums into DECIMAL.

Any approximate numeric type (e.g. FLOAT, DOUBLE) sums into DOUBLE.

Would you be interested on a PR to mimic this behavior much more closely?

A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

go-mysql-server

Scope of this project

Installation

Go Documentation

SQL syntax

Third-party clients

Available functions

Configuration

Example

Queries examples

Custom data source implementation

Testing your data source implementation

Indexes

Native indexes

Custom index driver implementation

Metrics

Powered by go-mysql-server

Acknowledgements

License

Owner

DoltHub

Comments

Unbound variables in subqueries

group_by: collation-aware grouping

Unique indexes do not work in the in-memory implementation

LastInsertId always returns 0

Support for prepared statements

Intermittent DATA RACE error when creating MySQL Servers

Question/Feature Request: How can I increase the parallelism of expression evaluation?

Error with passing time.Time as a field for saving

Cast equivalent float64s to int64 rather than failing with ErrInvalidValue

Client hangs if go-mysql-server takes too long to return query results

Index error lost in parent call

verify and fix info schema tables - 2

Transaction support for in-memory database

verify and add more info schema tables

memory database with binary column cannot be updated

[MySQL Compat] SUM result types

Related tags

MySQL Storage engine conversion，Support mutual conversion between MyISAM and InnoDB engines.

GoRose(go orm), a mini database ORM for golang, which inspired by the famous php framwork laravle's eloquent. It will be friendly for php developer and python or ruby developer. Currently provides six major database drivers: mysql,sqlite3,postgres,oracle,mssql, Clickhouse.

Go-Postgresql-Query-Builder - A query builder for Postgresql in Go

BQB is a lightweight and easy to use query builder that works with sqlite, mysql, mariadb, postgres, and others.

mysql to mysql 轻量级多线程的库表数据同步

PgSQL compatible on distributed database TiDB

It's a Go console utility for migration from MSSQL to MySQL engine.

Go-get-it - Simple database query script for UNIX-terminal usage

Vitess is a database clustering system for horizontal scaling of MySQL.

Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

Vitess is a database clustering system for horizontal scaling of MySQL.

Interactive terminal user interface and CLI for database connections. MySQL, PostgreSQL. More to come.

Golang restAPI crud project with mySql database.

A Go rest API project that is following solid and common principles and is connected to local MySQL database.

CRUD API example is written in Go using net/http package and MySQL database.

A proxy is database proxy that de-identifies PII for PostgresDB and MySQL

[mirror] the database client and tools for the Go vulnerability database

Database - Example project of database realization using drivers and models

An experimental toolkit for injecting alternate authentication strategies into a PostgreSQL-compatible wire format.