Using MySQL error messages to ensure randomly generated strings are unique

For my home project GoStatic each deployment has a unique, randomly generated URL.

Example: https://er34oltia2yqkyvl.gostatic.app

The URL is generated using Laravel's Str::random() helper function:

strtolower(Str::random(1) . '.gostatic.app';

For aesthetic reasons, I wanted to find a balance between having a large number of available subdomains, and a relatively small total number of characters in the URL. Using all standard alphanumeric characters gives 36 possible values, and the number of combinations increases dramatically with each additional character:

• 1 char = 36^1 combinations = 36
• 2 chars = 36^2 combinations = 1,296
• 3 chars = 36^3 combinations = 46,656
• 4 chars = 36^4 combinations = 1,679,616
• 5 chars = 36^5 combinations = 60,466,176
• 6 chars = 36^6 combinations = 2,176,782,336
• 7 chars = 36^7 combinations = 78,364,164,096
• 8 chars = 36^8 combinations = 2,821,109,907,456

For comparison, the maximum value of an unsigned integer in MySQL is 4,294,967,295. We will therefore hit the integer limit on the primary id column before exhausting the available combinations, even with a 7 or 8 character subdomain.

The deployment process

1. Immediately create a subdomain for the deployment and mark the deployment as LOCKED
2. Upload the files, parse and deploy (this can take some time)
3. Mark the deployment as ACTIVE and return the subdomain string to the user

The problem & solution

The database field which holds the domain has a UNIQUE index across it.

I didn't want to waste a SELECT query before issuing each INSERT statement, so instead, I decided to leverage MySQL's own UNIQUE checking and catch the error.

The code

As you can see in the code snippet below, we catch the Illuminate\Database\QueryException and check for the specific MySQL 23000 exception code Can't write; duplicate key in table '%s'

If that specific exception is caught, we recursively call the createAndLockDeployment() function and a new random string is generated. If any other exception is caught, it is re-thrown and handled by the rest of our application.

You can test this by setting the number of random characters to 1 and running the function a handful of times... you'll soon find the 36 available characters (26 letters and 10 numbers) are reserved and the function correctly logs the fact and makes another attempt.

private function createAndLockDeployment(Request $request)
{
    try {
        $this->deployment = Deployment::create([
            'domain'    => strtolower(Str::random(1) . '.gostatic.app',
            'status'    => Deployment::STATUS_LOCKED,
            'user_id'   => $request->user()->getKey(),
        ]);
    }
    catch(QueryException $e) {
        /**
         * Duplicate entry for UNIQUE key
         */
        if($e->getCode() == 23000) {
            Log::info('lockDeployment collision', ['message' => $e->getMessage()]);
            /**
             * Try again with another random string
             */
            $this->createAndLockDeployment($request);
        }
        else {
            /**
             * Re-throw all other error types
             */
            throw $e;
        }
    }
}

Use MySQL? Check out BentoDB

BentoDB - Create a MySQL database with a single API call

Did you find this interesting?

Follow @jonmilsom