Databases

Apple CloudKit uses FoundationDB Record Layer

Together, the Record Layer and FoundationDB form the backbone of Apple’s CloudKit. We wrote a paper describing how we built the Record Layer to run at massive scale and how CloudKit uses it. Today, you can read the preprint to learn more.

From an anonymous FoundationDB blog post introducing relational database capabilities built atop FoundationDB’s key-value store. The paper about CloudKit (PDF) is also worth a read. CloudKit is Apple’s free at any legitimate scale back-end as a service for all iOS and MacOS apps.

Detect MySQL’s “too many connections” error

WordPress appears to continue with execution even when MySQL refuses connections/queries after init. Here’s a comment in the MySQL docs suggesting how to detect the condition in raw PHP:

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
if (mysql_errno() == 1203) {
  // 1203 == ER_TOO_MANY_USER_CONNECTIONS (mysqld_error.h)
  header("Location: http://your.site.com/alternate_page.php");
  exit;
}

Just a note to myself, but I wonder if there’s opportunity here.

Speeding up MySQL joins on tables with TEXT columns, maybe

The thing about WordPress’ DB schema is that TEXT and VARCHAR content is mixed in the posts table (to say nothing of the frustrations of DATETIME columns). That’s not such a problem for a blog with a few hundred posts, but it’s a different matter when you have a few hundred thousand posts. And it wouldn’t even […] » about 500 words

What is the difference utf8_unicode_ci and utf8_general_ci?

From the MySQL manual:

For any Unicode character set, operations performed using the xxx_general_ci collation are faster than those for the xxx_unicode_ci collation. For example, comparisons for the utf8_general_ci collation are faster, but slightly less correct, than comparisons for utf8_unicode_ci.

They have a amusing “examples of the effect of collation” set on “sorting German umlauts,” but it unhelpfully uses latin1_* collations. And another table that helpfully explains:

A difference between the collations is that this is true for utf8_general_ci:

ß = s

Whereas this is true for utf8_unicode_ci, which supports the German DIN-1 ordering (also known as dictionary order):

ß = ss

This forum post adds more info, but nowhere do they explain how a ☃ sorts against ☁ or ⛅.

How much faster is utf8_general_ci than utf8_unicode_ci, though? An August 2010 message in the MySQL forums seems to suggest the performance for specific operations could be 30% faster, but then dismisses the performance difference as unimportant compared to good indexing and writing efficient queries.

Cleaning Up Category Relationships In A WordPress Scriblio Site

A few lines of SQL I used to clean up a Scriblio site. It’s probably useless to anybody but me. I’m not suggesting anybody else use this code, as it will result in changed or deleted data.

Update the post author for catalog records (identified because they have a specific post meta entry):

UPDATE wp_8_postmeta
JOIN wp_8_posts ON wp_8_posts.ID = wp_8_postmeta.post_id
SET post_author = 15
WHERE meta_key = 'scrib_meditor_content'

Get the categories attached to every catalog record (except the “catalog” category):

SELECT tr.object_id , tr.term_taxonomy_id
FROM wp_8_term_relationships tr
JOIN wp_8_posts p ON p.ID = tr.object_id
WHERE tr.term_taxonomy_id IN (
	SELECT term_taxonomy_id
	FROM wp_8_term_taxonomy
	WHERE taxonomy = "category"
	AND term_id != 30
)
AND post_author = 15
ORDER BY tr.object_id , tr.term_taxonomy_id

Using the above list of object ids and term taxonomy ids, build a series of queries like the following to delete them:

DELETE FROM wp_8_term_relationships WHERE object_id = 12275 AND term_taxonomy_id = 271872 ;

Insert a catalog category relationship for all catalog records:

INSERT INTO wp_8_term_relationships
SELECT p.ID , '271871' , '0'
FROM wp_8_posts p
LEFT JOIN wp_8_term_relationships tr ON p.ID = tr.object_id AND tr.term_taxonomy_id = 271871
WHERE post_author = 15
AND tr.term_taxonomy_id IS NULL