A Little Noise

March 22, 2017

LOAD DATA INFILE into a BIT field

Filed under: MySQL,One liners — snoyes @ 11:35 am

https://dev.mysql.com/doc/en/load-data.html

BIT values cannot be loaded using binary notation (for example, b’011010′).

$ cat test.txt

b'101010'
0b111000

-----

CREATE TABLE loadTest (b BIT(6));

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE loadTest;

/*--------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1406 | Data too long for column 'b' at row 1 |
| Warning | 1406 | Data too long for column 'b' at row 2 |
+---------+------+--------------------------------------*/

-- Note the wrong values:

SELECT BIN(b) FROM loadTest;
/*-------+
| BIN(b) |
+--------+
| 111111 |
| 111111 |
+-------*/

TRUNCATE loadTest;

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE loadTest (@b) 
  SET b = CAST(CONV(TRIM('''' FROM SUBSTRING_INDEX(@b, 'b', -1)), 2, 10) AS UNSIGNED);

SELECT BIN(b) FROM loadTest;
/*-------+
| BIN(b) |
+--------+
| 101010 |
| 111000 |
+-------*/

Deconstruction:

SUBSTRING_INDEX(@b, 'b', -1)

removes the leading ‘b’ or ‘0b’.

TRIM('''' FROM ...)

removes the quotes if there are any.

CONV(..., 2, 10)

converts the string of 0s and 1s into a string of decimal digits.

CAST(... AS UNSIGNED)

turns the string of decimal digits into an integer.
MySQL automatically casts integers into bits when inserting into a BIT type.

March 16, 2017

Quiz: Drop non-global users

Filed under: MySQL,One liners — snoyes @ 10:18 am

Somebody asked on Freenode. I don’t know why they wanted it. How would you drop all MySQL users who do not have “GRANT ALL ON *.* … WITH GRANT OPTION”? That is, drop any users who have ‘N’ in any of the privilege columns in `mysql`.`user`.

My solution shown below. Did you think of a different approach?

My solution ▼

October 27, 2016

Square Root of a Complex Number using Compass and Straight Edge Geometry

Filed under: Technical — snoyes @ 1:09 am

Also available as an interactive applet (scrollwheel or pinch to zoom so you can adjust the axis how you want).

Descartes allowed a line segment of unit length to find roots. The rest of the construction is completed with just Euclid’s compass and straight edge.
euclid-and-descartes-complex-root

Given: a complex number C plotted on the complex plane.
Bisect the angle formed between C, the origin O, and the positive X (real) axis.
Extend OC past O by a unit line segment to U.
Find the midpoint, M, between U and C.
Construct a half-circle centered at M through U and C.
Draw the perpendicular to UC through O to intersect the half-circle at I.
Draw a circle centered at O through I.
The circle intersects the bisector at S.
S is a square root of C.

September 20, 2016

Debugging Large Data with Rewriter

Filed under: MySQL — snoyes @ 7:43 pm

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.

A less-than-helpful error message that hints at an int.

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!

September 8, 2016

Aggregate JSON function in MySQL

Filed under: MySQL FAQ — snoyes @ 9:22 am

There is not yet an equivalent to GROUP_CONCAT that produces a JSON array. (There is in MySQL 8, but that’s not GA yet.) Until then, you can hack it together with string functions:

SELECT * FROM t;
+------+--------+
| id   | data   |
+------+--------+
|    1 | First  |
|    2 | Second |
+------+--------+

SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id, 'value', data) SEPARATOR ', '), ']') AS j FROM t;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

Or you can use all JSON functions but hack the grouping:

SELECT j FROM (
       SELECT
         @c := @c + 1 AS c,
         @j := JSON_MERGE(@j, JSON_OBJECT('id', id, 'value', data)) AS j
       FROM t
       JOIN (SELECT @c := 0,  @j := JSON_ARRAY()) dt1
     ) dt2 ORDER BY c DESC LIMIT 1;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

June 8, 2016

Disassembly of the Royal P80 Electric Pencil Sharpener

Filed under: Technical — snoyes @ 2:39 pm

(the one you bought at Sam’s Club)

Remove both of the screws marked with red arrows.
Remove either pair of the screws marked with green or yellow arrows (or both pairs, but then the whole thing falls apart and goes bang.)
Royal P80 screws

Push down on the down arrow side and up on the up arrow side to unhook the little plastic hook thing.
Royal P80 tabs

If you still have the P50 instead of the P80, visit John’s blog instead.

November 3, 2015

I’m really quite good with maps

Filed under: MySQL — snoyes @ 1:45 pm

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

worldmap

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 to .csv. Run it like this:

"C:\Program Files\MySQL\MySQL Workbench 8.0\ogr2ogr.exe" -f CSV countries.csv countries.shp -lco GEOMETRY=AS_WKT

Now create a table and load the CSV.

CREATE TABLE worldmap (
	OBJECTID smallint unsigned,
	NAME varchar(50),
	ISO3 char(3),
	ISO2 char(2),
	FIPS varchar(5),
	COUNTRY varchar(50),
	ENGLISH varchar(50),
	FRENCH varchar(50),
	SPANISH varchar(50),
	LOCAL varchar(50),
	FAO varchar(50),
	WAS_ISO varchar(3),
	SOVEREIGN varchar(50),
	CONTINENT varchar(15),
	UNREG1 varchar(30),
	UNREG2 varchar(15),
	EU boolean,
	SQKM decimal(20,11),
	g geometry
);

LOAD DATA LOCAL INFILE 'countries.csv' INTO TABLE worldmap FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
(@WKT, OBJECTID, NAME, ISO3, ISO2, FIPS, COUNTRY, ENGLISH, FRENCH, SPANISH, LOCAL, FAO, WAS_ISO, SOVEREIGN, CONTINENT, UNREG1, UNREG2, EU, SQKM)
SET g = ST_GeomFromText(@WKT);

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.

worldmap_overlay

November 2, 2015

The world is not in your books and maps.

Filed under: MySQL — snoyes @ 3:16 pm

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

click to embiggen

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.

October 9, 2015

Quarto

Filed under: MySQL — snoyes @ 1:10 pm

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 |
+---------------+

October 7, 2015

DATE_TRUNC for MySQL

Filed under: MySQL — snoyes @ 12:52 pm

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:

FIELD Returned value
MICROSECOND 1996-02-29 12:28:53.123456
MILLISECOND 1996-02-29 12:28:53.123000
SECOND 1996-02-29 12:28:53.000000
MINUTE 1996-02-29 12:28:00.000000
HOUR 1996-02-29 12:00:00.000000
DAY 1996-02-29 00:00:00.000000
WEEK 1996-02-25 00:00:00.000000
MONTH 1996-02-01 00:00:00.000000
QUARTER 1996-01-01 00:00:00.000000
YEAR 1996-01-01 00:00:00.000000
DECADE 1991-01-01 00:00:00.000000
CENTURY 1901-01-01 00:00:00.000000
MILLENNIUM 1001-01-01 00:00:00.000000
« Newer PostsOlder Posts »

Powered by WordPress