A Little Noise

4Nov/170

GTID_INTERSECT

There's a GTID_SUBTRACT function, and the manual shows how to write your own cheap GTID_UNION, but not a GTID_INTERSECT. Fortunately it's easy to write your own, as it's just subtracting twice.

CREATE FUNCTION GTID_INTERSECT(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(g1, GTID_SUBTRACT(g1, g2));

What use is it?

SET @slave_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681577,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1267098:1267100-1267416:1267418-1589733';

SET @master_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-730294,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';

SET @master_purged = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681582,
421d139e-04b9-11e7-b702-0050569935dc:1-13077260,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';

SELECT @slave_needs := GTID_SUBTRACT(@master_executed, @slave_executed);
/*-----------------------------------------------------+
| 33738f8c-c1a5-11e7-8fc3-0a002700000f:681578-730294, |
| 52b9a949-d79d-11e3-80dd-0050568d193e:1267099:1267417 |
+-----------------------------------------------------*/

SELECT @slave_will_not_get := GTID_INTERSECT(@master_purged, @slave_needs);
/*-----------------------------------------------------+
| 33738f8c-c1a5-11e7-8fc3-0a002700000f:681578-681582, |
| 52b9a949-d79d-11e3-80dd-0050568d193e:1267099:1267417 |
+-----------------------------------------------------*/

While we're on the subject, the manual's GTID_UNION just concatenates the two sets, which means the output will list values twice. A small improvement will clean that up:

CREATE FUNCTION GTID_UNION(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(CONCAT(g1,',',g2), '');

Filed under: MySQL No Comments
31Aug/170

Change Switched on Schoolhouse client host

Our homeschool curriculum choice has been Switched On Schoolhouse, by Alpha Omega Publications. AOP offers the same curriculum in an online format called "Monarch"; Switched On Schoolhouse is their disc-based version. We chose SOS instead of Monarch because it allows our students to do their work anywhere - in the car or a doctor's waiting room - regardless of the available Internet connection.

SOS has two program interfaces, "student" and "teacher", and two installation modes, which they call "full" and "client". If you only have one computer, then you do a "full" install of both "student" and "teacher", and both the student's work and any grading are done all in the same place. However, that means that for me to do any grading, my students can't be using their computers, and I also have to do be seated at their computer rather than using my own, which isn't ideal for us.

AOP has attempted to resolve this inconvenience with their "client" offering. The intended layout is a "full" installation on some host computer (probably the teacher's machine), and then a "client" installation of the student interface on each student's machine. This setup allows the teacher to see all students in the school within a single interface, switching between them with a simple dropdown menu. However, it also requires that the host computer be available on a local network to the students at all times, which would be even worse for us than the online requirement of Monarch (we'd have to set up a local network in the car, and I'd have to bring my computer when I would not normally).

It's possible to reverse the installation: do a full install on the student's machine, and a client install on the teacher's. This layout allows the students to work from anywhere, and when we are on the same local network, I can access their machine from mine and do grading, even while they are using their computer for something else. For a single student, it's ideal, but there's one snag: we have multiple students, each with their own computer. During client installation, you must specify which machine is running the full installation, and there is nothing in the interface that allows you to easily change it later. So I can set it up to connect to Iona's computer, but then can't switch to grade Eden's work.

However, all is not lost. The client stores the name of the computer to which it connects in a configuration file. Edit the file, change the name of the computer, restart, and you're now connected to the other student.

The configuration file is C:\Program Files (x86)\AOP\Switched-On Schoolhouse 2016 Home\SOSHApp.exe.config

(throughout this text, anywhere it says "2016", adjust for the current year.)

As plain text, it can be edited with Notepad, Wordpad, vim, or any similar program. I usually avoid using Microsoft Word or similar word processors to edit config files, because they often try to modify the encoding, replace straight quotes with "pretty" quotes, and other similar things which make the program which is trying to read that file very unhappy. It's a protected file, which means the editor must be running with elevated privileges (right click, "Run as administrator").

About halfway down the file is a line that looks like this:

<add key="ConnectionString" value="Initial Catalog=SOSHOME2016;Data Source=IONA-LAPTOP\SOSHOME80;" />

Change IONA-LAPTOP to EDEN-LAPTOP, save the file, start up SOS, and now I can grade Eden's work instead.

Editing a config file manually every time I want to switch students is tiresome, boring, and repetitive - exactly the kind of things computers do way better than humans. A script beckons.

Download SOS-Switcher.zip (you might be warned by your browser or anti-virus that this is dangerous program. They're right; it has the potential to be dangerous. You should examine the contents and know what they do before blindly trusting them with your computer.)

Since Windows doesn't have a nice regular expression replace program like sed built in, we'll need our own - that's what replace.vbs does. Then we'll need a batch file to call it with the correct parameters - that's what switch.bat does.

Extract these two files and put them in the C:\Program Files (x86)\AOP\Switched-On Schoolhouse 2016 Home\ directory (which you'll have to do as administrator).

Now, for each student, right-click on switch.bat and choose "Create Shortcut". It will ask if it can create the shortcut on the desktop; that's fine.

Right click, Create Shortcut

Right-click on the newly created shortcut, and choose Properties. On the Shortcut tab, there's a Target box, which should say something like:

"C:\Program Files (x86)\AOP\Switched-On Schoolhouse 2016 Home\switch.bat"

Add the name of the student's computer to that, outside the quotes:

"C:\Program Files (x86)\AOP\Switched-On Schoolhouse 2016 Home\switch.bat" IONA-LAPTOP

Click "Advanced" and check "Run as administrator".

You may also choose "Change Icon" - it will tell you that switch.bat doesn't contain any icons, and bring up a window with a bunch of standard Windows icons to choose from. To get the SOS icon, click on Browse..., navigate to C:\Program Files (x86)\AOP\Switched-On Schoolhouse 2016 Home, and open SOSHApp.exe.

On the General tab, you can change the shortcut name. It's probably "switch.bat - Shortcut"; I changed it to "SOS Iona".

Repeat the process for the other students - create a shortcut, modify properties, change the target:

"C:\Program Files (x86)\AOP\Switched-On Schoolhouse 2016 Home\switch.bat" EDEN-LAPTOP

Now, make sure that SOS is not running. Double click on one of the shortcuts. After a moment (and possibly a question if you want to let it change settings on your computer - choose Yes or OK), it should open SOS, and you'll find that you have access to one student's work. Close SOS, launch the other shortcut, and verify that you now have access to the other student's work.

If there's a very long pause, and then an error message saying it can't connect, either you did not put the correct computer name in the shortcut target, or that computer is not running or has a firewall preventing access. The SOS installation disc 2 has some utilities to help you configure all that, and their FAQ and tech support can walk you through that bit.

Filed under: Kids No Comments
18Jul/170

ERROR 3037 (22023): Invalid GIS data provided to function st_geometryfromtext.

1. Watch the parentheses. It's:

ST_GeomFromText('POLYGON((outerRing), (innerRing), (innerRing), ...)')

The inner rings are optional. If you have just the outer ring, then it's still:

ST_GeomFromText('POLYGON((outerRing))')

and not:

ST_GeomFromText('POLYGON(outerRing)')

2. Polygons have to start and end at the same point.

3. Watch the commas. Rings are comma-delimited sets of of whitespace-delimited coordinate pairs:

x1 y1, x2 y2, x3 y3, x4 y4

not:
x1 y1 x2 y2 x3 y3
x1, y1, x2, y2, x3, y3, x4, y4
(x1, y1), (x2, y2), (x3, y3)
or other variations on that theme.

Filed under: MySQL No Comments
11Jul/171

On slave_parallel_workers and the logical clock

How can you tell if a given workload on the master could be replicated with many parallel workers on the slave?

The slave_parallel_type=LOGICAL_CLOCK is an implementation of a Lamport clock. The implementation is described in WL #7165 - including a neat little ASCII-art graphic.

Each event in the binary log is stamped with two values:
- The sequence_number increments for each commit
- The last_committed is the sequence_number which was in effect when this transaction entered the prepare phase.

A slave worker cannot begin doing a transaction until the last_committed value is smaller than the sequence_number of all other running threads.

mysqlbinlog mysql-bin.0000x | grep last_committed

#160118 15:31:34 server id 3  end_log_pos 1527 CRC32 0xcdf6bd8d         GTID    last_committed=0        sequence_number=1
#160118 15:31:34 server id 3  end_log_pos 2627 CRC32 0x72e5fa80         GTID    last_committed=1        sequence_number=2
#160119  9:59:17 server id 3  end_log_pos 2952 CRC32 0x7290d02f         GTID    last_committed=2        sequence_number=3
#160119  9:59:17 server id 3  end_log_pos 3248 CRC32 0x227fe513         GTID    last_committed=3        sequence_number=4
#160119  9:59:17 server id 3  end_log_pos 3544 CRC32 0x8a6a2b78         GTID    last_committed=4        sequence_number=5
#160119  9:59:17 server id 3  end_log_pos 5315 CRC32 0x35bca421         GTID    last_committed=4        sequence_number=6
#160119  9:59:17 server id 3  end_log_pos 7094 CRC32 0x75584a42         GTID    last_committed=4        sequence_number=7
#160119  9:59:17 server id 3  end_log_pos 8865 CRC32 0xe4ee1b54         GTID    last_committed=6        sequence_number=8
#160119  9:59:17 server id 3  end_log_pos 10636 CRC32 0xed1f7c48        GTID    last_committed=6        sequence_number=9
#160119  9:59:17 server id 3  end_log_pos 12409 CRC32 0x7802f625        GTID    last_committed=9        sequence_number=10

In the first transaction, last_committed=0, sequence_number=1
The first worker thread takes that transaction and goes to work.

In the second transaction, last_committed=1, sequence_number=2
This transaction cannot start until the first one finishes, because last_committed=1 is not smaller than the previous sequence_number=1. So the first two transactions must be done in series.

In the third transaction, last_committed=2, sequence_number=3. Again, no worker could start this transaction until that previous trx with sequence_number=2 finishes.

Although these first four transactions might actually be assigned to different workers, effectively they execute in series, just like single-threaded replication would work.

Now, we come to the transactions with last_committed=4. As soon as that transaction with sequence_number=4 finishes, these next three transactions can go in parallel.

last_committed=4        sequence_number=5
last_committed=4        sequence_number=6
last_committed=4        sequence_number=7

Once the first two finish, two more can start:

last_committed=6        sequence_number=8
last_committed=6        sequence_number=9

They can run even while the one with sequence_number=7 is still running.

But this one has to wait for everything before it:

last_committed=9        sequence_number=10

We can see that for these 10 transactions, only three workers could ever be operating at the same time.

You might have spotted a pattern: if the difference (sequence_number - last_committed) = 1, then the transaction has to wait for everything before it to finish. If the difference is 2, then the transactions can run in parallel with just the previous transaction, and so on.

You can count how much of your transaction history falls into each category:

mysqlbinlog mysql-bin.0000x | grep -o 'last_committed.*' | sed 's/=\|\s/ /g' | awk '{print $4-$2}' | sort -g | uniq -c

 20953 1
  8998 2
  6021 3
  4209 4
  3083 5
  2180 6
  1403 7
   820 8
   418 9
   176 10
     7 11
     2 12
     1 13
     1 15
     1 16

In that binary log, 20953 transactions had to wait for all previous transactions to commit before starting. 8998 could run in parallel with one previous transaction. Only a very few could run in parallel with more than 10 previous transactions. Jut 3 worker threads would cover 75% of the possible parallel transactions. 8 worker threads would cover 99%.

See also Percona's blog for discussion on how to view similar information via the performance_schema on a running slave.

Filed under: MySQL 1 Comment
22Mar/170

LOAD DATA INFILE into a BIT field

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.

Filed under: MySQL, One liners No Comments
16Mar/170

Quiz: Drop non-global users

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 ▼

Filed under: MySQL, One liners No Comments
27Oct/160

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

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.

Filed under: Technical No Comments
20Sep/161

Debugging Large Data with Rewriter

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!

Filed under: MySQL 1 Comment
8Sep/161

Aggregate JSON function in MySQL

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"}] |
+-------------------------------------------------------------+
Filed under: MySQL FAQ 1 Comment
8Jun/161

Disassembly of the Royal P80 Electric Pencil Sharpener

(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.

Filed under: Technical 1 Comment