Dolt – Git for Data
dolt_history tables: Issue a warning when types have changed
Adding a session warning when dolt_history tables can't create a rowConverter for a row due to a field's type having changed. In these cases, the old value can't always be returned safely as the new type, so we return NULL and issue a session warning. In the future, we could attempt to convert the old value to the new type.
Follow up to https://github.com/dolthub/dolt/pull/7771
create schema mySchema;
create schema otherSchema;
CREATE TABLE mySchema.test (pk BIGINT PRIMARY KEY, v1 BIGINT);
insert into mySchema.test values (1,1), (2,2);
CREATE TABLE otherSchema.test (pk BIGINT PRIMARY KEY, v1 BIGINT);
insert into otherSchema.test values (3,3), (4,4);
SELECT * FROM mySchema.test;
SELECT * FROM otherSchema.test;
root_val.go. In particular it seems likely to cause errors over time as people forget to include schema names in code changes where necessary, silently breaking doltgres. Another option I considered was to introduce some sort of RootReader interface on top of root value, which you create with a particular schema name, and thereafter all table accesses reference the given schema. There may be places where we have to juggle multiple RootReader instances at once, rather than just asking for qualified table names from a single root. But this is the pattern I used for the boundary between GMS and Dolt and it worked well, so maybe it could work for root_val.go as well.2482: don't check schema compatibility for select into
2481: aliasExpr inputExpression case-insensitive check
This PR adds case-insensitive check for aliasExpr.InputExpression and expr.String() to determine the expr is referencable.
2473: remove re-parsing select stmt in create view stmt Depends on https://github.com/dolthub/vitess/pull/339
2466: Schema-qualified table names This PR also fixes a couple unrelated issues:
alter table syntax
MySQL Docs; https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
fixes: https://github.com/dolthub/go-mysql-server/issues/2472
PARTITION BY
| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.07 | 3.02 | 1.5 |
| groupby_scan | 13.7 | 17.63 | 1.3 |
| index_join | 1.34 | 5.18 | 3.9 |
| index_join_scan | 1.27 | 2.26 | 1.8 |
| index_scan | 34.33 | 52.89 | 1.5 |
| oltp_point_select | 0.17 | 0.51 | 3.0 |
| oltp_read_only | 3.36 | 8.43 | 2.5 |
| select_random_points | 0.33 | 0.8 | 2.4 |
| select_random_ranges | 0.39 | 0.95 | 2.4 |
| table_scan | 34.33 | 53.85 | 1.6 |
| types_table_scan | 74.46 | 158.63 | 2.1 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.98 | 6.91 | 0.9 |
| oltp_insert | 3.75 | 3.43 | 0.9 |
| oltp_read_write | 8.43 | 16.12 | 1.9 |
| oltp_update_index | 3.82 | 3.55 | 0.9 |
| oltp_update_non_index | 3.82 | 3.43 | 0.9 |
| oltp_write_only | 5.37 | 7.98 | 1.5 |
| types_delete_insert | 7.7 | 7.56 | 1.0 |
| writes_mean_multiplier | 1.1 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 101.61 | 4.98 | 4.2 |
| tpcc_tps_multiplier | 4.2 |
| Overall Mean Multiple | 2.50 |
|---|
varchar to int), we assign a new column tag. This prevents us from easily identifying the column across type changes. For example:
dolt_patch() and dolt diff -r sql and also prevents us from generating correct DDL statements for binlog replication.
This PR changes schema alterations so that columns now retain their original column tag across type changes, which fixes the issues above.
A side effect of this change is that if a customer is working on a branch, creates a column, and changes its type, the tag won't match if the schema on another branch is updated to the final type change (i.e. without going through the initial/intermediary type). Code was originally added for this case (see: https://github.com/dolthub/dolt/issues/3950), however, since then, we have added support for schema merging and this isn't an issue anymore. I've confirmed that we can successfully merge non-matching column tags, from both directions, and added a test for this case, too.
Longer-term, I agree we should continue exploring removing tags completely, but in the short-term, this fixes a correctness problem with SQL patch statement generation that binlog support needs.
Related to: https://github.com/dolthub/dolt/issues/6710#issuecomment-1734318087
AUTO_INCREMENT columns have NOT NULL constraint
companion pr: https://github.com/dolthub/go-mysql-server/pull/2467
DESCRIBE columns and auto_increment bugs
This PR makes it so our DESCRIBE statement is more correct when there are unique keys.
There's an edge case we miss:
create table t (i int not null, j int not null, unique key (j), unique key (i));
describe t;
UNIQUE KEY j is created before UNIQUE KEY i, so describe makes j as PRI.
In Dolt, we store indexes in a map and return them in order of index name, so we mark i as PRI.
There are skipped tests for this
Additionally, this adds the NOT NULL constraint to columns that are marked AUTO_INCREMENT.
partially addresses: https://github.com/dolthub/dolt/issues/2289
dolt login works with DoltHub, but not a DoltLab instance| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.11 | 3.02 | 1.4 |
| groupby_scan | 13.22 | 17.63 | 1.3 |
| index_join | 1.34 | 5.18 | 3.9 |
| index_join_scan | 1.27 | 2.26 | 1.8 |
| index_scan | 33.72 | 53.85 | 1.6 |
| oltp_point_select | 0.17 | 0.52 | 3.1 |
| oltp_read_only | 3.36 | 8.58 | 2.6 |
| select_random_points | 0.33 | 0.83 | 2.5 |
| select_random_ranges | 0.39 | 0.99 | 2.5 |
| table_scan | 34.33 | 54.83 | 1.6 |
| types_table_scan | 75.82 | 161.51 | 2.1 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.84 | 6.79 | 0.9 |
| oltp_insert | 3.75 | 3.36 | 0.9 |
| oltp_read_write | 8.28 | 16.41 | 2.0 |
| oltp_update_index | 3.82 | 3.49 | 0.9 |
| oltp_update_non_index | 3.82 | 3.43 | 0.9 |
| oltp_write_only | 5.28 | 7.84 | 1.5 |
| types_delete_insert | 7.56 | 7.56 | 1.0 |
| writes_mean_multiplier | 1.2 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 101.85 | 2.36 | 3.7 |
| tpcc_tps_multiplier | 3.7 |
| Overall Mean Multiple | 2.37 |
|---|
traditional auto-increment lock mode hold the lock for the duration of the insert iter.
Fixes https://github.com/dolthub/dolt/issues/7634
This is the dolt half of https://github.com/dolthub/go-mysql-server/pull/2439
This adds support for innodb_autoinc_lock_mode=0 ("traditional"). When this system variable is set, the engine will guarantee that every insert statement generates consecutive IDs for AUTO INCREMENT columns.
This PR also allows the user to set innodb_autoinc_lock_mode=1 ("consecutive"), although the behavior is currently identical to "traditional". This is acceptable because both modes make the same guarantees (that each statement gets consecutive IDs), and the observed behavior is the same in almost all cases.
(The "consecutive" contains an additional optimization: if there is a known upper bound on the number of IDs that must be generated for an insert, under "consecutive" mode the engine will just increment the counter by that upper bound and immediately release the lock. In places where not all of those IDs are actually used, the excess are wasted. This PR does not include that optimization. Thus, with this PR, traditional and consecutive behave the same.)auto_inc with error
Hard to tell which PR or combination or PRs fixed this, but this issue no longer reproduces in GMS or Dolt.
This PR just adds an explicit test case for it.
fixes https://github.com/dolthub/dolt/issues/3157
last_days()
mysql docs:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day
information_schema.tables
fixes https://github.com/dolthub/dolt/issues/2389
to_days() and from_days()
This PR implements the TO_DAYS and FROM_DAYS MySQL functions.
Initially, I attempted to use the existing logic from Add/Sub Date along with the 0 year, but there were overflow issues.
So, there's a skipped test for Intervals.
to_days docs:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_to-days
from_days docs:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-days
gtid_subtract() function
Adds support for MySQL's gtid_subtract() built-in function:
https://dev.mysql.com/doc/refman/8.0/en/gtid-functions.html#function_gtid-subtract
SHOW BINARY LOG STATUS statement
Adds support for handling MySQL's SHOW BINARY LOG STATUS statement.
As with the other replication commands, we test the privilege checks in GMS and test the actual integration for data flowing through this statement in Dolt.SHOW VARIABLES LIKE should be case-insensitive
Bug fix to make SHOW VARIABLES LIKE '...' match MySQL's behavior by matching patterns case-insensitively.
Also includes changes to add the missing binlog_format system variable that some replication clients need to query, and initializes the server_uuid system variable.DECLARE in BEGIN...END BLOCK in TRIGGER
This PR allows us to use DECLARE statements in TRIGGERS.
The analyzer rule applyTriggers was altered to initialize ProcedureReference for TriggerBeginEndBlock.
The important part was ensuring that all relevant nodes (TriggerBeingEndBlock, BeginEndBlock, ProcedureParam) all had the same ProcedureReference and to search in all the nodes.
Additionally, nil guards are added to all receiver methods for ProcedureReference to prevent panics.
It seems like events might have this issue, but there's a banaid fix for it. Not sure if I want to touch that code.
fixes: https://github.com/dolthub/dolt/issues/7720
innodb_autoinc_lock_mode is set to the "consecutive" or "traditional" values. More specifically, it calls a new function in the AutoIncrementSetter interface which optionally acquires a lock and returns a callback for releasing the lock.
The in-memory DB doesn't have multithreading support, so when just using GMS, this is a no-op. A followup PR in Dolt will update its implementation of AutoIncrementSetter to handle the locking.gtid_subtract() built-in function. Also exposes the ParseMysql56GTIDSet function so that we can parse GTIDSet strings from GMS.
Related GMS PR: https://github.com/dolthub/go-mysql-server/pull/2455
FLUSH TABLES statement
Adds parser support for the FLUSH TABLES <tablename_list> [WITH READ LOCK] statement.
Also adds SHOW MASTER STATUS as an alias for SHOW BINARY LOG STATUS for compatibility with clients that use the older syntax (e.g. Debezium).INSERT INTO t VALUES (?, ?, ?) AS new ON DUPLICATE KEY v = new.v;
WITH CHECK OPTION support for CREATE VIEW
ALTER TABLE ... RENAME CONSTRAINT [FOREIGN KEY / CHECK]... for foreign key constraints.trim(trailing ',' from column) syntax not supportedselect ... not in ... returns wrong results for nullsdolt sql and other commands should connect to running databasedolt sql
dolt conflicts resolve <table> --combine-ours or --combine-theirs
:= operator in SELECTBEFORE INSERT trigger using DECLARE variabletraditional and consecutive behavior for innodb_autoinc_lock_mode
| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.07 | 3.02 | 1.5 |
| groupby_scan | 13.22 | 17.63 | 1.3 |
| index_join | 1.37 | 5.18 | 3.8 |
| index_join_scan | 1.3 | 2.26 | 1.7 |
| index_scan | 34.33 | 55.82 | 1.6 |
| oltp_point_select | 0.17 | 0.53 | 3.1 |
| oltp_read_only | 3.36 | 8.58 | 2.6 |
| select_random_points | 0.33 | 0.84 | 2.5 |
| select_random_ranges | 0.39 | 1.01 | 2.6 |
| table_scan | 34.33 | 55.82 | 1.6 |
| types_table_scan | 73.13 | 161.51 | 2.2 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.98 | 6.91 | 0.9 |
| oltp_insert | 3.75 | 3.43 | 0.9 |
| oltp_read_write | 8.43 | 16.41 | 1.9 |
| oltp_update_index | 3.82 | 3.55 | 0.9 |
| oltp_update_non_index | 3.82 | 3.49 | 0.9 |
| oltp_write_only | 5.37 | 7.98 | 1.5 |
| types_delete_insert | 7.7 | 7.7 | 1.0 |
| writes_mean_multiplier | 1.1 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 101.79 | 0.16 | 4.5 |
| tpcc_tps_multiplier | 4.5 |
| Overall Mean Multiple | 2.60 |
|---|
"<tbl_name>_ibfk_<num>", which matches MySQL behavior.
As a result, we now allow multiple foreign keys over the same sets of columns (as long as the names are different).
companion pr:
https://github.com/dolthub/go-mysql-server/pull/2438
fixes:
https://github.com/dolthub/dolt/issues/7650
traditional auto-increment lock mode hold the lock for the duration of the insert iter.
Fixes https://github.com/dolthub/dolt/issues/7634
This is the dolt half of https://github.com/dolthub/go-mysql-server/pull/2439
This adds support for innodb_autoinc_lock_mode=0 ("traditional"). When this system variable is set, the engine will guarantee that every insert statement generates consecutive IDs for AUTO INCREMENT columns.
This PR also allows the user to set innodb_autoinc_lock_mode=1 ("consecutive"), although the behavior is currently identical to "traditional". This is acceptable because both modes make the same guarantees (that each statement gets consecutive IDs), and the observed behavior is the same in almost all cases.
(The "consecutive" contains an additional optimization: if there is a known upper bound on the number of IDs that must be generated for an insert, under "consecutive" mode the engine will just increment the counter by that upper bound and immediately release the lock. In places where not all of those IDs are actually used, the excess are wasted. This PR does not include that optimization. Thus, with this PR, traditional and consecutive behave the same.)dolt sql-server doesn't currently print a message at all when a bad table file is encountered.ALTER TABLE ... RENAME CONSTRAINT ... for foreign key constraints
This PR adds support for ALTER TABLE ... RENAME CONSTRAINT ... for foreign key constraints.
This is a feature that is NOT supported in MySQL, but we're adding it to make it easier to resolve merge conflicts resulting from foreign key name collisions.
related: https://github.com/dolthub/go-mysql-server/pull/2438
innodb_autoinc_lock_mode is set to the "consecutive" or "traditional" values. More specifically, it calls a new function in the AutoIncrementSetter interface which optionally acquires a lock and returns a callback for releasing the lock.
The in-memory DB doesn't have multithreading support, so when just using GMS, this is a no-op. A followup PR in Dolt will update its implementation of AutoIncrementSetter to handle the locking.ALTER TABLE ... RENAME CONSTRAINT [FOREIGN KEY / CHECK]... for foreign key constraints.Length() function in the BinlogEvent interface so calling code can access the event size present in a binlog event's header. Needed for calculating binlog file position.FakeBinlogStream → BinlogStream so calling code can use it when serializing binlog events.traditional and consecutive behavior for innodb_autoinc_lock_mode
AUTO_INCREMENT clause missing in result from SHOW CREATE TABLE
| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.11 | 3.07 | 1.5 |
| groupby_scan | 12.98 | 17.63 | 1.4 |
| index_join | 1.34 | 5.28 | 3.9 |
| index_join_scan | 1.27 | 2.26 | 1.8 |
| index_scan | 34.33 | 54.83 | 1.6 |
| oltp_point_select | 0.17 | 0.54 | 3.2 |
| oltp_read_only | 3.36 | 8.74 | 2.6 |
| select_random_points | 0.33 | 0.84 | 2.5 |
| select_random_ranges | 0.39 | 0.99 | 2.5 |
| table_scan | 34.33 | 55.82 | 1.6 |
| types_table_scan | 74.46 | 164.45 | 2.2 |
| reads_mean_multiplier | 2.3 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.84 | 6.91 | 0.9 |
| oltp_insert | 3.75 | 3.36 | 0.9 |
| oltp_read_write | 8.28 | 16.41 | 2.0 |
| oltp_update_index | 3.82 | 3.49 | 0.9 |
| oltp_update_non_index | 3.82 | 3.43 | 0.9 |
| oltp_write_only | 5.28 | 7.98 | 1.5 |
| types_delete_insert | 7.7 | 7.56 | 1.0 |
| writes_mean_multiplier | 1.2 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 101.95 | 19.63 | 4.5 |
| tpcc_tps_multiplier | 4.5 |
| Overall Mean Multiple | 2.67 |
|---|
dolt_verify_constraints() support for all constraint types
The dolt_verify_constraints() stored procedure now supports verifying all constraint types: foreign keys, not null, unique, and check constraints. This new implementation reuses merge logic to calculate the constraint violations.
Note that the dolt constraints verify CLI command next needs to be migrated to use this stored procedure so that it can also verify all constraint violation types. Until then, customers can use dolt sql -q "CALL dolt_verify_constraints(...)" if they want to verify all constraint types from the CLI.
Doc updates: https://github.com/dolthub/docs/pull/2093
Fixes: https://github.com/dolthub/dolt/issues/6320
@@server_id default value match MySQLbinlog_checksum system variable
Small change to make the default value of the global binlog_checksum system variable match MySQL's default value (i.e. "CRC32").SHOW FIELDS/COLUMNS FROM <table> query would return the string "NULL" for Default column rather than nil.
This mattered for Knex, which relied on it being NULL and not "NULL".
fixes: https://github.com/dolthub/dolt/issues/7692
Threads_connected and Threads_running status variables
This PR adds support for Threads_connected and Threads_running status variables.
Additionally, the local enginetest are flaking consistently in dolt ci, so those have been removed;
we have handler tests for com_delete, com_insert, and com_update anyway.
Related: https://github.com/dolthub/dolt/issues/7646
Innodb_buffer_pool_pages_total to 1, to avoid an issue with Datadog's collector
Datadog's metric collector errors out with a divide by zero error if the Innodb_buffer_pool_pages_total status variable is 0; changing it to 1 avoids this and allows the agent to collect metrics from Dolt.@@session.collation_server during create database ...
This PR makes it so create database ... actually reads the @@session.collation_server variable.
Additionally, this ensures that settings @@character_set_server sets @@collation_server and vice versa.
Interestingly, it seems like MySQL actually ignores the global scope of these system variables, and reads the session scope instead.
fixes https://github.com/dolthub/dolt/issues/7651
Length() function in the BinlogEvent interface so calling code can access the event size present in a binlog event's header. Needed for calculating binlog file position.FakeBinlogStream → BinlogStream so calling code can use it when serializing binlog events.set global character_set_server not working the same way in MySQL, may lead inconsistencydolt verify-contraints does not check unique constraintssystem_variables field in config.yaml for sql-server command
The CLI docs for sql-server doesn't include the system_variables field available in config.yaml.ChunkStore interface to let the nodeStore access recently accessed chunks. Avoid adding a child ref to the pendingRef list when already present in nbs.hasCache. For TPC-C this appears to reduce the pending ref count by another ~80%.Com_delete, Com_insert, Com_update status variables
related: https://github.com/dolthub/dolt/issues/7646
@@session.collation_server during create database ...
This PR makes it so create database ... actually reads the @@session.collation_server variable.
Additionally, this ensures that settings @@character_set_server sets @@collation_server and vice versa.
Interestingly, it seems like MySQL actually ignores the global scope of these system variables, and reads the session scope instead.
fixes https://github.com/dolthub/dolt/issues/7651
time.Time types
This PR adds tests for using time.Time, some tests have to be skipped because we don't support Timespan correctly.
companion pr: Questions status variable
This PR adds logic to update status variable Questions.
This only works in the server context, probably doesn't through dolt sql cli.
https://github.com/dolthub/dolt/issues/7646
LIKE for show status/variables
MySQL stores session and global variables in a performance_schema database, and these tables have a case-insensitive collation on the variable names.
This PR emulates that behavior by hard coding the collation the schemas for ShowStatus and ShowVariables nodes.show create database to actually show charset/collation
This PR fixes the SHOW CREATE DATABASE ... statement to actually show the charset/collation that the db is under instead of always default.
Additionally, this PR parses the charset database option, instead of ignoring it like before.
partially fixes: https://github.com/dolthub/dolt/issues/7651
time.Time and decimal in bindvars
We were unable to use time.Time and decimal type variables bind vars, so they couldn't be used as arguments to prepare statements.
This PR addresses that issue.
fixes: https://github.com/dolthub/dolt/issues/7665
vitess.io/vitess – support for the COM_REGISTER_REPLICA command.COM_BINLOG_DUMP_GTID command.String functions to help with debugging.set global character_set_server not working the same way in MySQL, may lead inconsistencycalculateMergeConflicts during dolt pull if sql-server is running| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.07 | 3.07 | 1.5 |
| groupby_scan | 13.46 | 17.63 | 1.3 |
| index_join | 1.34 | 5.18 | 3.9 |
| index_join_scan | 1.27 | 2.26 | 1.8 |
| index_scan | 34.33 | 54.83 | 1.6 |
| oltp_point_select | 0.17 | 0.49 | 2.9 |
| oltp_read_only | 3.36 | 8.13 | 2.4 |
| select_random_points | 0.33 | 0.78 | 2.4 |
| select_random_ranges | 0.39 | 0.94 | 2.4 |
| table_scan | 34.95 | 54.83 | 1.6 |
| types_table_scan | 74.46 | 158.63 | 2.1 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 8.13 | 6.79 | 0.8 |
| oltp_insert | 3.75 | 3.36 | 0.9 |
| oltp_read_write | 8.43 | 15.55 | 1.8 |
| oltp_update_index | 3.82 | 3.49 | 0.9 |
| oltp_update_non_index | 3.82 | 3.36 | 0.9 |
| oltp_write_only | 5.37 | 7.7 | 1.4 |
| types_delete_insert | 7.7 | 7.43 | 1.0 |
| writes_mean_multiplier | 1.1 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 102.03 | 18.72 | 6.5 |
| tpcc_tps_multiplier | 6.5 |
| Overall Mean Multiple | 3.27 |
|---|
dolt pull command
This was reported by a user: https://github.com/dolthub/dolt/issues/7653
This was covered by a test, but it turns out that the pull, fetch, and push tests were all using SQL_ENGINE=remote-server incorrectly..dolt/stats, or an alternative implementation like a lsm that will have easier append only semantics. The implementation for the noms chunkstore isn't particularly efficient, we will not deduplicate common chunks between branches.
How the new code is organized: statspro has generic interfaces for how a Dolts-specific stats implementation works. statsnoms is an implementation that uses a second database at .dolt/stats to store statistic refs. The stats refs are the same, just now they are named by the branch they reference (ex: refs/statistics/main). So storage is the concern of the concrete implementation. The common interface forces the implementation to handle branches. The branch switching in statsnoms are just named refs.
A high level of what's happening during certain operations: There are still two operations, load and update. Load now either initializes the stats database at .dolt/stats or loads existing stats. Update is the same, ANALYZE or auto refresh.
Most of the changes are just forcing the logic through a generic interface. There were import cycle issues (dtables) and deadlock issues for creating a database (dolt provider takes a lock that prevents doing certain operation on the session in the stats provider) that motivated packaging decisions.BinlogPrimaryController interface. This new interface pretty closely mirrors the existing callback interface for replica callbacks, the BinlogReplicaController interface.
Related to https://github.com/dolthub/dolt/issues/7512
| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.07 | 3.07 | 1.5 |
| groupby_scan | 13.46 | 17.32 | 1.3 |
| index_join | 1.37 | 5.18 | 3.8 |
| index_join_scan | 1.27 | 2.26 | 1.8 |
| index_scan | 34.33 | 63.32 | 1.8 |
| oltp_point_select | 0.17 | 0.49 | 2.9 |
| oltp_read_only | 3.36 | 8.28 | 2.5 |
| select_random_points | 0.33 | 0.78 | 2.4 |
| select_random_ranges | 0.39 | 0.95 | 2.4 |
| table_scan | 34.33 | 63.32 | 1.8 |
| types_table_scan | 74.46 | 176.73 | 2.4 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.98 | 7.04 | 0.9 |
| oltp_insert | 3.75 | 3.49 | 0.9 |
| oltp_read_write | 8.43 | 16.12 | 1.9 |
| oltp_update_index | 3.82 | 3.62 | 0.9 |
| oltp_update_non_index | 3.82 | 3.55 | 0.9 |
| oltp_write_only | 5.37 | 8.13 | 1.5 |
| types_delete_insert | 7.7 | 7.84 | 1.0 |
| writes_mean_multiplier | 1.1 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 101.15 | 16.93 | 5.7 |
| tpcc_tps_multiplier | 5.7 |
| Overall Mean Multiple | 3.00 |
|---|
innodb_autoinc_lock_mode tests
companion pr: https://github.com/dolthub/go-mysql-server/pull/2410
resetScripts() in doltHarness to not drop and recreate all auto_increment tables.
This step is not necessary, and causes problems now because we actually display and read AUTO_INCREMENT table option in show create table statements.
Contains changes from: https://github.com/dolthub/dolt/pull/7631
Companion PR: https://github.com/dolthub/go-mysql-server/pull/2401
BinlogPrimaryController interface. This new interface pretty closely mirrors the existing callback interface for replica callbacks, the BinlogReplicaController interface.
Related to https://github.com/dolthub/dolt/issues/7512
json_search()
MySQL Docs: https://dev.mysql.com/doc/refman/8.3/en/json-search-functions.html#function_json-search
innodb_autoinc_lock_mode
We currently only support innodb_autoinc_lock_mode = 2, not 0 or 1.
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
related: https://github.com/dolthub/dolt/issues/7634
encodings/json package to marshall the value to a JSON string and then unmarshall it to a go map. This is not only slow, but it's incorrect for these additional types.
The main purpose of this PR is to add special handling for these types that allow them to be stored in JSON documents. We also avoid generating and parsing JSON in places where it's not actually necessary, and fix bugs where decimals get incorrectly converted into strings, or unsigned ints get converted into signed ints.
Finally, this fixes an issue where we send incorrect bytes for JSON-wrapped decimal values along the wire.dbName in the example is actually being used, instead of having a hardcoded "mydb" in createTestDatabase.
fixes #2402CREATE TABLE ... LIKE ... needs to preserve table opts, like commentsalter table add column ... auto_increment does not work when there are already rowsON and OFF values to to true and false when setting a system variable, which made it impossible to set system variables to those enum values. For example:
SET @@GLOBAL.gtid_mode='ON';
Variable 'gtid_mode' can't be set to the value of 'true'
SystemVariable and SystemVariableScope interfaces.
This allows doltgres to use these interfaces for defining and handling all the configuration parameters.
The SystemVariable struct is renamed to MysqlSystemVariable.
The SystemVariableScope byte is renamed to MysqlSVScopeType.SOURCE_AUTO_POSITION
When acting as a replica, Dolt implicitly assumes SOURCE_AUTO_POSITION is set to 1. This adds parser support so that it can be explicitly specified. Adding this so that when we're configuring a MySQL replica, we can use the same setup code without having to special case this parameter.vitess.io/vitess – support for the COM_REGISTER_REPLICA command.COM_BINLOG_DUMP_GTID command.String functions to help with debugging.COM_BINLOG_DUMP_GTID command.
Related to https://github.com/dolthub/dolt/issues/7512
dolt gc to SQL| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.07 | 3.02 | 1.5 |
| groupby_scan | 13.46 | 17.63 | 1.3 |
| index_join | 1.39 | 5.09 | 3.7 |
| index_join_scan | 1.32 | 2.22 | 1.7 |
| index_scan | 35.59 | 63.32 | 1.8 |
| oltp_point_select | 0.18 | 0.48 | 2.7 |
| oltp_read_only | 3.43 | 8.13 | 2.4 |
| select_random_points | 0.33 | 0.78 | 2.4 |
| select_random_ranges | 0.39 | 0.94 | 2.4 |
| table_scan | 35.59 | 63.32 | 1.8 |
| types_table_scan | 74.46 | 176.73 | 2.4 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.98 | 7.04 | 0.9 |
| oltp_insert | 3.75 | 3.49 | 0.9 |
| oltp_read_write | 8.43 | 16.12 | 1.9 |
| oltp_update_index | 3.82 | 3.62 | 0.9 |
| oltp_update_non_index | 3.82 | 3.55 | 0.9 |
| oltp_write_only | 5.37 | 8.13 | 1.5 |
| types_delete_insert | 7.7 | 7.84 | 1.0 |
| writes_mean_multiplier | 1.1 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 101.78 | 22.7 | 5.0 |
| tpcc_tps_multiplier | 5.0 |
| Overall Mean Multiple | 2.77 |
|---|
DEBU[0197] preparing query paramsCount=2 query="SELECT column_name, column_default, is_nullable = 'YES', data_type, character_maximum_length, column_type, column_key, extra, column_comment, numeric_precision, numeric_scale , datetime_precision FROM information_schema.columns WHERE table_schema = ? AND table_name = ? ORDER BY ORDINAL_POSITION" statementId=2
ERRO[0197] mysql_server caught panic:
interface conversion: sql.Table is *sqle.WritableDoltTable, not *sqle.AlterableDoltTable
/Users/maxhoffman/go/pkg/mod/github.com/dolthub/[email protected]/sql/planbuilder/parse.go:169 (0x101612ae7)
com/dolthub/go-mysql-server/sql/planbuilder.(*Builder).BindOnly.func1: panic(r)
/usr/local/go/src/runtime/panic.go:914 (0x1000d2187)
gopanic: done = runOpenDeferFrame(d)
/usr/local/go/src/runtime/iface.go:263 (0x1000a516f)
panicdottypeE: panic(&TypeAssertionError{iface, have, want, ""})
/usr/local/go/src/runtime/iface.go:273 (0x1000a5128)
panicdottypeI: panicdottypeE(t, want, iface)
/Users/maxhoffman/go/github.com/dolthub/dolt/go/libraries/doltcore/sqle/database.go:357 (0x101a1f1a7)
com/dolthub/dolt/go/libraries/doltcore/sqle.Database.getTableInsensitive: return NewHistoryTable(baseTable.(*AlterableDoltTable).DoltTable, db.ddb, head), true, nil
diff_table() function I think. That requires heavier refactoring to support prepared statements.sql_mode = 'NO_AUTO_VALUE_ON_ZERO'
This PR implements the sql_mode NO_AUTO_VALUE_ON_ZERO.
This makes it so that 0 values (not NULL) do not increment the auto_increment counter.
MySQL Docs: https://dev.mysql.com/doc/refman/8.3/en/example-auto-increment.html
fixes https://github.com/dolthub/dolt/issues/7600
memory.NewSessionBuilder method, now used in the example code.COM_BINLOG_DUMP_GTID command
Stubbing out support for two new mysql.Handler methods to support streaming binlog events from a server to a client.
Depends on Vitess PR: https://github.com/dolthub/vitess/pull/317
Related to https://github.com/dolthub/dolt/issues/7512
COM_BINLOG_DUMP_GTID command.
Related to https://github.com/dolthub/dolt/issues/7512
duplicate primary key given error| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.07 | 2.97 | 1.4 |
| groupby_scan | 12.98 | 17.32 | 1.3 |
| index_join | 1.32 | 5.09 | 3.9 |
| index_join_scan | 1.25 | 2.18 | 1.7 |
| index_scan | 33.72 | 63.32 | 1.9 |
| oltp_point_select | 0.17 | 0.48 | 2.8 |
| oltp_read_only | 3.36 | 7.98 | 2.4 |
| select_random_points | 0.32 | 0.77 | 2.4 |
| select_random_ranges | 0.38 | 0.94 | 2.5 |
| table_scan | 34.33 | 63.32 | 1.8 |
| types_table_scan | 74.46 | 176.73 | 2.4 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.98 | 6.91 | 0.9 |
| oltp_insert | 3.75 | 3.43 | 0.9 |
| oltp_read_write | 8.28 | 15.83 | 1.9 |
| oltp_update_index | 3.82 | 3.55 | 0.9 |
| oltp_update_non_index | 3.82 | 3.49 | 0.9 |
| oltp_write_only | 5.37 | 7.98 | 1.5 |
| types_delete_insert | 7.7 | 7.7 | 1.0 |
| writes_mean_multiplier | 1.1 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 101.1 | 22.45 | 4.6 |
| tpcc_tps_multiplier | 4.6 |
| Overall Mean Multiple | 2.63 |
|---|
IF NOT EXISTS and table exists
This PR addresses various issues related to CREATE TABLE IF NOT EXISTS ... queries.
Before, we simply ignored the table exists error, and continued creating indexes, foreign keys, and checks.
This led to errors when attempting to create indexes/foreign keys/checks that already exists.
Additionally, it would errorneously create indexes/foreng keys/checks that did exist.
The correct behavior is to do nothing if IF NOT EXISTS is specified and the table exists.
Also this contains some refactors and simplifications.
fixes https://github.com/dolthub/dolt/issues/7602
oos: linux
goarch: amd64
pkg: github.com/dolthub/go-mysql-server/sql
cpu: AMD Ryzen 9 7900 12-Core Processor
│ b1 │ b2 │
│ sec/op │ sec/op vs base │
HashOf-24 79.65n ± 4% 70.86n ± 7% -11.03% (p=0.002 n=6)
ParallelHashOf-24 10.47n ± 4% 11.85n ± 19% ~ (p=0.368 n=6)
geomean 28.88n 28.98n +0.32%
│ b1 │ b2 │
│ B/op │ B/op vs base │
HashOf-24 4.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
ParallelHashOf-24 4.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
geomean 4.000 ? ¹ ²
¹ summaries must be >0 to compute geomean
² ratios must be >0 to compute geomean
│ b1 │ b2 │
│ allocs/op │ allocs/op vs base │
HashOf-24 2.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
ParallelHashOf-24 2.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
geomean 2.000 ? ¹ ²
¹ summaries must be >0 to compute geomean
² ratios must be >0 to compute geomean
type.Promote() for all types.
Additionally, there are some inconsistencies with MySQL when performing these checks with NUL characters (\0). They are skipped tests for now.
related https://github.com/dolthub/dolt/issues/7588
json_pretty
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-pretty
binary as a valid charset option.
Additionally, this changes CHARSET to expand to synonym CHARACTER SET for parsing in GMS.
fixes https://github.com/dolthub/dolt/issues/7576
| Read Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| covering_index_scan | 2.11 | 3.02 | 1.4 |
| groupby_scan | 13.46 | 18.28 | 1.4 |
| index_join | 1.34 | 5.18 | 3.9 |
| index_join_scan | 1.25 | 2.14 | 1.7 |
| index_scan | 33.72 | 63.32 | 1.9 |
| oltp_point_select | 0.17 | 0.47 | 2.8 |
| oltp_read_only | 3.36 | 7.98 | 2.4 |
| select_random_points | 0.32 | 0.77 | 2.4 |
| select_random_ranges | 0.39 | 0.92 | 2.4 |
| table_scan | 33.72 | 63.32 | 1.9 |
| types_table_scan | 74.46 | 173.58 | 2.3 |
| reads_mean_multiplier | 2.2 |
| Write Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| oltp_delete_insert | 7.98 | 6.91 | 0.9 |
| oltp_insert | 3.75 | 3.43 | 0.9 |
| oltp_read_write | 8.43 | 15.83 | 1.9 |
| oltp_update_index | 3.82 | 3.55 | 0.9 |
| oltp_update_non_index | 3.82 | 3.49 | 0.9 |
| oltp_write_only | 5.37 | 7.98 | 1.5 |
| types_delete_insert | 7.7 | 7.7 | 1.0 |
| writes_mean_multiplier | 1.1 |
| TPC-C TPS Tests | MySQL | Dolt | Multiple |
|---|---|---|---|
| tpcc-scale-factor-1 | 100.71 | 23.38 | 5.4 |
| tpcc_tps_multiplier | 5.4 |
| Overall Mean Multiple | 2.90 |
|---|