A Little Noise

A customer showed that a particular client reported a less-than-helpful error message when it tried to display some meta-data about a table.

I couldn’t repeat the behavior with just a copy of the schema, so I suspected it was because of the size of data in the customer’s server – somebody had used an int where they needed a long.

The customer’s data was quite large – many hundreds of GB – more than I could easily whip up on my laptop to test. But, I didn’t really need all that data, or even any data at all; I just needed MySQL to pretend it had all that data. Specifically, I needed information_schema to report a large data_length.

Enter Rewriter, the query rewrite plugin that ships with MySQL 5.7 and later.

First, the general query log gave the exact query sent by the client:

select * FROM information_schema.partitions WHERE TABLE_SCHEMA = 'schemaNameHere' AND TABLE_NAME = 'tableNameHere'

Create a copy of that table:

CREATE DATABASE debug_schema;
CREATE TABLE debug_schema.partitions LIKE information_schema.partitions;
INSERT INTO debug_schema.partitions SELECT * FROM information_schema.partitions;

Adjust the copy to report a different value:

UPDATE debug_schema.partitions SET data_length = POW(2, 32);

Install the Rewriter plugin, and add a rule to modify the schema name:

INSERT INTO query_rewrite.rewrite_rules(pattern, replacement) VALUES (
'select * FROM information_schema.partitions WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?',
'select * FROM debug_schema.partitions WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?'
);

CALL query_rewrite.flush_rewrite_rules();

Now when I try the client, it thinks information_schema has reported a huge data_length, and I get the same error which my customer had reported. Success!

Workbench announced support for a spatial view in 6.2, but examples are somewhat lacking. Just how do you get a SHP into MySQL?

Download and unpack a SHP file such as these country boundaries.

In the Workbench installation directory, you’ll find a program “ogr2ogr” that can convert .shp or GeoJSON and load it straight into MySQL.

“C:\Program Files\MySQL\MySQL Workbench 8.0\ogr2ogr.exe” -f MySQL MySQL:databaseNameHere,user=userNameHere,password=passwordHere World_Countries_Generalized.shp -nln worldmap -lco GEOMETRY_NAME=p

(It might give the error ERROR 1: PROJ: proj_identify: Cannot find proj.db which it seems can be ignored.)

Now just select rows of interest in Workbench, click the Spatial View format button, and there’s your world map.

You can run multiple selects (such as the citylot data from yesterday’s post) to overlay on top of the world map.

MySQL 5.7 came out with support for JSON, improved geometry, and virtual columns. Here’s an example showing them all playing together.

Download citylots.json.

It comes as one big object, so we’ll break it up into separate lines:
grep "^{ .type" citylots.json > properties.json

Connect to a 5.7 instance of MySQL.

CREATE TABLE citylots (id serial, j json, p geometry as (ST_GeomFromGeoJSON(j, 2)));
LOAD DATA LOCAL INFILE 'properties.json' INTO TABLE citylots (j);

A few of the rows don’t contain useful data:
DELETE FROM citylots WHERE j->'$.geometry.type' IS NULL;

In MySQL Workbench, do:
SELECT id, p FROM citylots;

Then click on Spatial View. It takes a couple of minutes for 200k rows, but there’s a map of San Francisco.

The default projection, ‘Robinson’, is designed for showing the whole world at once and so is pretty distorted for this particular data set. Mercator or Equirectangular are better choices. Fortunately, Workbench repaints the data in just a few seconds.

If you selected some other fields, you can click on the map and see the relevant data for that particular geometry.

mysql < quarto.sql

Example game play:

mysql> -- Start the game and pass the first piece in the lower nibble
mysql> CALL SetupGame(0x0A);
-----------------------------------------------------------------------+
| rules                                                                |
-----------------------------------------------------------------------+
| Quarto: 4 in a line (row, column, or long diagonal) with at least one bit in common wins.
CALL Play(move); -- high 4 bits are board position, low 4 bits are piece for next player
CALL PrintBoard(base); -- to display the board. Useful bases are 16 and 2. |
-----------------------------------------------------------------------+

+------------------------+
| instructions           |
+------------------------+
| Player 1, play piece A |
+------------------------+

mysql> CALL Play(0x00);
+---------+
| board   |
+---------+
| A| | |  |
|  | | |  |
|  | | |  |
|  | | |  |
+---------+

+------------------------+
| instructions           |
+------------------------+
| Player 0, play piece 0 |
+------------------------+

mysql> CALL Play(0x1C);
+---------+
| board   |
+---------+
| A|0| |  |
|  | | |  |
|  | | |  |
|  | | |  |
+---------+

+------------------------+
| instructions           |
+------------------------+
| Player 1, play piece C |
+------------------------+

mysql> CALL Play(0x2E);
+---------+
| board   |
+---------+
| A|0|C|  |
|  | | |  |
|  | | |  |
|  | | |  |
+---------+

+------------------------+
| instructions           |
+------------------------+
| Player 0, play piece E |
+------------------------+

mysql> CALL Play(0x3F);
+---------+
| board   |
+---------+
| A|0|C|E |
|  | | |  |
|  | | |  |
|  | | |  |
+---------+

+---------------+
| result        |
+---------------+
| player 0 wins |
+---------------+

Because somebody asked for it on Freenode:

CREATE FUNCTION DATE_TRUNC(field ENUM('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', 'millennium'), source datetime(6))
RETURNS datetime(6)
DETERMINISTIC
BEGIN
  IF field IN ('millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) % 1000 MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second') THEN SET source = source - INTERVAL SECOND(source) SECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute') THEN SET source = source - INTERVAL MINUTE(source) MINUTE; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour') THEN SET source = source - INTERVAL HOUR(source) HOUR; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day') THEN SET source = source - INTERVAL DAYOFWEEK(source) - 1 DAY; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week') THEN SET source = source - INTERVAL DAY(source) - 1 DAY; END IF;
  IF field IN ('quarter') THEN SET source = source - INTERVAL MONTH(source) % 3 - 1 MONTH; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'week', 'day', 'month', 'quarter') THEN SET source = source - INTERVAL MONTH(source) - 1 MONTH; END IF;

  -- Year ranges go from 1 - 10, e.g. 1961-1970, not 1960-1969. The third millenium started 2001, not 2000. If you want it the other way, remove the "- 1" from each of the following.
  IF field IN ('decade') THEN SET source = source - INTERVAL YEAR(source) % 10 - 1 YEAR; END IF;
  IF field IN ('century') THEN SET source = source - INTERVAL YEAR(source) % 100  - 1 YEAR; END IF;
  IF field IN ('millennium') THEN SET source = source - INTERVAL YEAR(source) % 1000 - 1 YEAR; END IF;
 
  RETURN source;
END

When called with the date ‘1996-02-29 12:28:53.123456’, returns the following:

CREATE FUNCTION SWAP_ENDIAN(inString text)
RETURNS TEXT
DETERMINISTIC
-- Expects a hex string: AbCdEf
-- Returns the string swapped for endianness: EfCdAb

BEGIN
  DECLARE position INT DEFAULT 1;
  DECLARE holder TEXT DEFAULT '';

  WHILE position < LENGTH(inString) DO
    SET holder = CONCAT(SUBSTRING(inString, position, 2), holder);
    SET position = position + 2;
  END WHILE;

  RETURN holder;
END

So you can do things like:

SELECT
  FROM_UNIXTIME(
    CONV(
      SWAP_ENDIAN(
        SUBSTRING(
          HEX(
            FROM_BASE64(
              'Yk3XVQ8pAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAABBAAAAAAAAAAAAAAAA'
              'AAAAAAAAAAAAAAAAAAAAAAAABiTddVEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC'
            )
          ), 1, 8
        )
      ), 16, 10)
  ) AS event_timestamp;

and without creating a table to pass the state around (really just an excuse to use the named locks feature).

DELIMITER //
DROP FUNCTION IF EXISTS SET_BREAKPOINT//
CREATE FUNCTION SET_BREAKPOINT()
RETURNS tinyint
NO SQL
BEGIN
	-- Acquire lock 1
	-- Wait until lock 2 is taken to signal that we may continue
	DO GET_LOCK(CONCAT('lock_1_', CONNECTION_ID()), -1);
	REPEAT
		DO 1;
	UNTIL IS_USED_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT;
	DO RELEASE_LOCK(CONCAT('lock_1_', CONNECTION_ID()));

	-- Acquire lock 3 to acknowledge message to continue.
	-- Wait for lock 2 to be released as signal of receipt.
	DO GET_LOCK(CONCAT('lock_3_', CONNECTION_ID()), -1);
	REPEAT
		DO 1;
	UNTIL IS_FREE_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT;
	DO RELEASE_LOCK(CONCAT('lock_3_', CONNECTION_ID()));

	RETURN 1;
END//

DROP FUNCTION IF EXISTS NEXT_BREAKPOINT//
CREATE FUNCTION NEXT_BREAKPOINT(connection_id int)
RETURNS tinyint
NO SQL
BEGIN
	-- Acquire lock 2 as a signal to go past the breakpoint
	-- Wait until lock 3 is taken as signal of receipt.
	DO GET_LOCK(CONCAT('lock_2_', connection_id), -1);
	REPEAT
		DO 1;
	UNTIL IS_USED_LOCK(CONCAT('lock_3_', connection_id)) END REPEAT;
	DO RELEASE_LOCK(CONCAT('lock_2_', connection_id));

	RETURN 1;
END//

DROP PROCEDURE IF EXISTS test_the_breakpoints//
CREATE PROCEDURE test_the_breakpoints()
NO SQL
BEGIN
	SELECT CONCAT('In another session: DO NEXT_BREAKPOINT(', CONNECTION_ID(), ');') as `instructions`;

	DO SET_BREAKPOINT();

	SELECT 'do it again' as `now:`;

	DO SET_BREAKPOINT();

	SELECT 'end' as `the`;
END//
DELIMITER ;

CALL test_the_breakpoints();