query_checksum

NAME

query_checksum(): checksum the result set of a query

TYPE

Procedure

DESCRIPTION

Given a query, this procedure produces a deterministic checksum on the query's result set.

The query is subject to the following limitations:

  • It must be a SELECT
  • It is limited to 9 columns
  • Columns must be explicitly indicated; thus, the "star" form (e.g. SELECT * FROM ...) is not allowed
  • Columns are limited to 64K characters (values are translated as text)
Otherwise the query may produce any type of columns, use expressions, functions, etc. Resulting values may be NULL.

The routine produces a checksum that is calculated via repetitive usage of the MD5 algorithm. While the operation is deterministic, it uses some internal heuristics (such as converting NULLs to '\0' so as to be able to process the MD5 calculation). Knowing the internal heuristics it is possible to intentionally produce two different results sets which lead to same resulting checksum. The incidental appearance of such queries, though, is unlikely. Moral is that this routine is useful in checking for data integrity in terms of possible errors, and is not suitable as a security threat elimination.

The resulting checksum is also written to the @query_checksum_result session variable.

SYNOPSIS

query_checksum(in query TEXT CHARSET utf8) 
  READS SQL DATA

Input:

  • query: query to execute; checksum run on result set

EXAMPLES

Checksum three queries. The first two return the exact same result:

mysql> call query_checksum('select distinct n from (select cast(n/10 as unsigned) as n from numbers) s1 order by n');
+----------------------------------+
| checksum                         |
+----------------------------------+
| 314c86787aab14525759b29f81ac9664 |
+----------------------------------+

mysql> call query_checksum('select n from (select cast(n/10 as unsigned) as n from numbers) s1 group by n order by n');
+----------------------------------+
| checksum                         |
+----------------------------------+
| 314c86787aab14525759b29f81ac9664 |
+----------------------------------+

mysql> call query_checksum('select distinct n+1 from (select cast(n/10 as unsigned) as n from numbers) s1 order by n');
+----------------------------------+
| checksum                         |
+----------------------------------+
| f4ea2e7f04d6edd28e9dd3e9419ec92c |
+----------------------------------+

mysql> select @query_checksum_result;
+----------------------------------+
| @query_checksum_result           |
+----------------------------------+
| f4ea2e7f04d6edd28e9dd3e9419ec92c |
+----------------------------------+

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

crc64(), exec(), random_hash()

AUTHOR

Shlomi Noach
 
common_schema documentation