Grouping SQL errors

Flare gets a lot of errors every day. To keep a good overview, we try to group them as best as possible because you don't want to end with thousands of errors in your Flare project when there's only one bug within your codebase.

For most errors, grouping is relatively easy. We look at the top frame within the call stack where the error occurred and keep track of the class, method and line where the error occurred. When Flare receives another error, we again look at this top frame and group it by these parameters.

This method works pretty well for most exceptions thrown within PHP. But there are cases where this approach isn't giving the best results.

SQL Exceptions

A Laravel application will throw a QueryException whenever something goes wrong with your database. When we would group by top frame, all database errors would be grouped into a single item. That's something we don't want since those SQL errors could be entirely different.

We could not use the top frame of the call stack. But go deeper in the stack until we find a frame within your application. But what then if an external package caused the exception? It could be that we never reach an application frame. Also, the deeper we go through the call stack, the more general the path of execution becomes. We could be grouping errors that have nothing to do with each other.

Flare uses an alternative solution. We do not group by top frame in cases of a QueryException but by exception class and message. Now, these two errors become two different items within Flare:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'titl' in 'field list' (SQL: SELECT * FROM `posts` WHERE `titl` = 'flare groupings')

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'publishe' in 'field list' (SQL: UPDATE `posts` SET `publishe` = 1 WHERE `id` = 1)

Great! But what happens to the grouping when we run this piece of code, and it fails?

public function publish(Post $post, bool $published): void
{
	$post->update(['publishe' => $published]);
}

The answer isn't that clear. It depends since a post can have multiple id's and published can be 0 or 1. The following two exceptions could be thrown when this piece of code fails:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'publishe' in 'field list' (SQL: UPDATE `posts` SET `publishe` = 0 WHERE `id` = 1)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'publishe' in 'field list' (SQL: UPDATE `posts` SET `publishe` = 1 WHERE `id` = 1)

This will cause Flare to create two entries for errors that should be grouped since they represent the same bug. We want to avoid these scenarios at all costs!

Let's take a look at the two queries:

UPDATE `posts` SET `publishe` = 0 WHERE `id` = 1

UPDATE `posts` SET `publishe` = 1 WHERE `id` = 1

When we would use prepared statements with bindings where we sent the values separate from the query to the database server then, those queries would look like this:

UPDATE `posts` SET `publishe` = ? WHERE `id` = ?

UPDATE `posts` SET `publishe` = ? WHERE `id` = ?

Great! The two queries are identical. We can now group using the exception class: QueryException and this query with bindings. Sadly enough, often prepared statements are entirely ignored, so we cannot use them.

But what if we could strip all values from these queries to look like queries with bindings?

Parsing queries

SQL queries are complicated, we've seen some simple queries earlier, but they can become quite complex. That's why we'll use a SQL parser to parse the queries into tokens so we know what should be replaced and whatnot.

The first thing we need to do is parse the query:

(new PHPSQLParser($query))->parsed;

Such a parsed query looks like this:

array:3 [
  "UPDATE" => array:1 [
    0 => array:10 [
      "expr_type" => "table"
      "table" => "`posts`"
      "no_quotes" => array:2 [
        "delim" => false
        "parts" => array:1 [
          0 => "posts"
        ]
      ]
      "alias" => false
      "hints" => false
      "join_type" => "JOIN"
      "ref_type" => false
      "ref_clause" => false
      "base_expr" => "`posts`"
      "sub_tree" => false
    ]
  ]
  "SET" => array:1 [
    0 => array:3 [
      "expr_type" => "expression"
      "base_expr" => "`publishe` = 0"
      "sub_tree" => array:3 [
        0 => array:4 [
          "expr_type" => "colref"
          "base_expr" => "`publishe`"
          "no_quotes" => array:2 [
            "delim" => false
            "parts" => array:1 [
              0 => "publishe"
            ]
          ]
          "sub_tree" => false
        ]
        1 => array:3 [
          "expr_type" => "operator"
          "base_expr" => "="
          "sub_tree" => false
        ]
        2 => array:3 [
          "expr_type" => "const"
          "base_expr" => "0"
          "sub_tree" => false
        ]
      ]
    ]
  ]
  "WHERE" => array:3 [
    0 => array:4 [
      "expr_type" => "colref"
      "base_expr" => "`id`"
      "no_quotes" => array:2 [
        "delim" => false
        "parts" => array:1 [
          0 => "id"
        ]
      ]
      "sub_tree" => false
    ]
    1 => array:3 [
      "expr_type" => "operator"
      "base_expr" => "="
      "sub_tree" => false
    ]
    2 => array:3 [
      "expr_type" => "const"
      "base_expr" => "1"
      "sub_tree" => false
    ]
  ]
]

We only want to replace the constants within the query, so we update each token where the expr_type is const to not use a value like 1 or 0 but to use a ? just like with the bindings in prepared statements:

array:3 [
  "expr_type" => "const"
  "base_expr" => "1"
  "sub_tree" => false
]

Becomes:

array:3 [
  "expr_type" => "const"
  "base_expr" => "?"
  "sub_tree" => false
]

The cool thing with this parser package is that we can convert these tokens back to a SQL query:

(new PHPSQLCreator())->create($parsed);

And we're done! Our query now looks like this:

UPDATE `posts` SET `publishe` = ? WHERE `id` = ?

Within Flare, the algorithm of stripping the token tree is a bit more complex than we show here, but the gist is the same. Take some raw values and replace them with question marks.

More exotic queries

But this is not where we end, although the parser is quite good at parsing the wildest queries. Sometimes it just cannot understand what's happening. For example:

UPDATE users SET uuid = 89637150-f807-11ea-aee4-51fe58f2b2b3

In some database configurations, this query is valid. And our parser will only strip the first part of the UUID because it thinks the - signs are operators, and the other parts of the UUID are references to columns within your table:

UPDATE users SET uuid = ? - f807 - 11ea - aee4 - 51fe58f2b2b3

The same thing happens with dates:

UPDATE users SET date = ? - ? - 2020T17:24:34.012345Z

That's why, before we parse the query, we preprocess it by stripping out some well-known types of strings like dates, emails, UUIDs:

preg_replace(
	[
		'/\S+@\S+\.\S+/', // emails
		'/\d{2,4}-\d{2}-\d{2,4}[ T]?(\d{2}:\d{2}:\d{2}([+-]\d{2}:\d{2})?(\.\d{6}Z)?)?/', // dates
		'/[0-9a-f]{8}\b-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-\b[0-9a-f]{12}/', // uuids
	],
	['?', '?', '?'],
	$query
);

Preprocessing makes the queries a bit more readable for our parser, which improves our final result drastically!

Connection issues

A QueryException always looks like this:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'publishe' in 'field list' (SQL: UPDATE `posts` SET `publishe` = 0 WHERE `id` = 1)

Did you notice the SQLSTATE[42S22] part? It is a code telling you what went wrong with your database. For example, a connection issue in a MySql database has the following code: [HY000][2002] looks familiar?

Within Flare, we'll also take a look at these codes. When we notice the error is caused by connection issues and not a bug within your code, then we group all these errors since your database server was probably just a few moments down.

Impossible queries

Let's take a look at a final example:

SELECT * FROM invoices WHERE number = 123ABC

Such a query is a difficult one. The parser doesn't know if 123ABC is a constant or a reference to a column within your database table.

In the latter case, we don't want to replace the column name with a ? since these are not values that change between exceptions.

In such cases, we do not strip the value and keep that part of the query.