Merged PRs

dolt

• 5985: LOAD DATA bats tests improvements
  A customer ran into some issues using LOAD DATA with Hosted Dolt. Added some additional testing in response. Pleasantly surprised with our compatibility. Adds skipped tests for #5982 and #5983.
• 5966: Smart routing for sql command execution against running local servers
  When dolt sql commands are executed against a database which is in used by a sql server, connect to it through the sql port. Currently this does not support authenticated transport. If the root user is still active, we'll use that be default. Otherwise, the --user flag can be used to specify a user, but no authentication token is supported. That will come in the next round of changes.
  A step towards: #3922
• 5961: Use UTC timestamp in date comparison test.
  Allows tests to run on laptops which aren't in UTC.
• 5951: Handle unresolved/resolved foreign keys when determining equality
  When foreign keys are unresolved, the data on which columns they point to in the child table and the parent table is stored differently, and requires extra work to "resolve" the columns before we can test for equality. We currently do not always calculate equality correctly, which can result in confusing diff output (e.g. #5934).
  This PR adds a new Equals method on ForeignKey that takes in enough data to resolve FK column references, if needed, and test equality for any two foreign keys.
• 5949: go: sqle: cluster: commithook: Periodically heartbeat to a standby when we are primary. This allows replication_lag on the standby to more accurately reflect the possible drift locally.
• 5943: Don't panic when in detached head state.
  Fixes #5871
  Many commands currently panic when in a detached head state, such as commit (#5744), checkout (#5839), and many others. This turns those panics into errors.
• 5940: go: sqle: cluster: Implement dolt_cluster_ack_writes_timeout_secs, a way to block COMMIT ack until the commit is replicated to cluster standbys.
  Setting this system variable to a non-zero value on a primary replica in a sql-server cluster will cause dolt to block a SQL client performing a commit until that client's commit is fully replicated to the replicas.
  If there is a timeout, a session warning is placed on the SQL connection.

go-mysql-server

• 1777: Add SRID 3857 to list of "supported" SRIDs
  The ogr2ogr tool performs some information gathering queries beforehand, which change the queries it generates later on to populate MySQL tables.
  When running ogr2ogr -f MySQL "MYSQL:ogr,user=root,port=3307,host=127.0.0.1" "data.gpkg", ogr2ogr sends these queries

  SELECT VERSION();
  SHOW TABLES;
  SELECT SRS_ID FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE ORGANIZATION = 'EPSG' AND ORGANIZATION_COORDSYS_ID = 3857;
  SELECT SRS_ID FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE DEFINITION = 'PROJCS["WGS 84 / Pseudo-Mercator",GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.25722356
  3,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]],PROJECTION["Mercator_1SP"],PARAMETER["central_meridian",0],PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PARAMETER[
  "false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0 +lon_0=0 +x_0=0 +y_0=0 +k=1 +units=m +nadgrids=@null +wktext +no_defs"],AUTHORITY["EPSG","3857"]]'

  However, Dolt's information_schema.st_spatial_reference_system only contains SRID 0 and 4326, as a result both queries ogr2ogr makes return Empty Set.
  Consequently, this causes ogr2ogr to generate the create table queries like so

  CREATE TABLE `aeropuertos` ( fid INT UNIQUE NOT NULL AUTO_INCREMENT, geom GEOMETRY NOT NULL)

  When it should be

  CREATE TABLE `aeropuertos` ( fid INT UNIQUE NOT NULL AUTO_INCREMENT, geom GEOMETRY NOT NULL /*!80003 SRID 3857 */)

  On initial insert, this causes ogr2ogr to insert with SRID 0
  On further inserts/appends, this causes ogr2ogr to just not fill in SRID, leaving it as -2 aka not initialized
  This PR adds SRID 3857 to dolt's information_schema.st_spatial_reference_system.
  Now, the ogr2ogr command loudly fails with a useful error:

  ERROR 1: MySQL error message:unsupported feature: unsupported SRID value Description: CREATE TABLE `aeropuertos` (    fid INT UNIQUE NOT NULL AUTO_INCREMENT,    geom GEOMETRY NOT NULL /*!80003 SRID 3857 */)
  ERROR 1: Terminating translation prematurely after failed
  translation of layer aeropuertos (use -skipfailures to skip errors)

  Addresses these issues:
  • #5960
  • #5959
  • #5931
• 1776: Returning @@system_time_zone based on OS time zone
  The @@system_time_zone global system variable is a read-only system variable that shows the timezone that the server is running in. MySQL and Golang both load the system timezone from the OS, typically using the TZ env var or /etc/timezone. This change exposes that system timezone information through the @@system_time_zone variable, which was previously hardcoded to UTC.
  Because timezone settings can change while the system is running (thanks daylight savings time!), we need to check with the runtime for the system timezone whenever it is requested. I gave SystemVariable the ability to include a function that gets executed to return the sys var value and moved the uptime sys var over to that, too.
• 1775: Error for out of range SRIDs
  This PR fixes an error inconsistency between Dolt and MySQL.
  For SRIDs that are out of range [0, MAX_UINT32], MySQL throws a ERROR: 1690: SRID value is out of range in '<func_name>'.
  For SRIDS that are in range, but just don't exist, MySQL throws a ERROR: 3548: There's no spatial reference system with SRID <invalid srid>.
  Dolt used to only throw ERROR 3548, and would incorrectly report the negative numbers as overflowed positive uint32s
  partial fix for: #5948
• 1738: ICU Regex Implementation
  To try and prevent memory leaks, I'm having the regex only work under a callback. The idea is that we'll do all of our matches under the callback, using a node placed by an analyzer rule. I think this approach should work, and it'll expand to any other functions within the regex that need to hold memory that will be freed later.
  For now though, this portion works, and I have a small test showing such.

Closed Issues

• 5991: Compatibility issue with sqlalchemy table class, table manipulation
• 4427: REGEXP_LIKE collation support
• 5871: Many commands fail in detached head by trying to access nil pointer Working Set
• 5960: Support basic functional CRSs (-1 and -2)
• 5973: Agnostic Geometries from CRS
• 5970: charset introducer doesn't appear to work with default column values
• 5749: Unable to create table with default charset of utf8mb3
• 5932: Buggy table after fails to append data to a spatial table
• 5931: Can't append data to spatial tables with unsupported CRS
• 5948: CRS type should be signed
• 5825: Slow ANTI_JOINs
• 1771: utf8mb3_tolower_ci not implemented

Latency