GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator

Blob type Row Hash validation fails between MySQL and Spanner

Open
#1,068 0 comments 0 reactions 0 assignees View on GitHub
priority: p2
Dominant language
Python
Stars
524
Forks
171
Avg merge
5d 15h
Merged PRs (30d)
4

Description

Hi,

Row Hash validation fails between MySQL and Spanner for Blob fields. The equivalent of Blob type in Spanner is BINARY and the values are as follows:
```
data-validation query -c test_mysql -q 'select blob_column from sample_table where (id < 1814)'
[(b'abbigc',), (b'abbigc',), (b'abbigc',)]
data-validation query -c test_spanner -q 'select cast(blob_column AS String) from sample_table where (id < 1814) and (migration_shard_id="testShardJ")'
[['abbigc'], ['abbigc'], ['abbigc']]
```
Evidently these should match - however DVT appears to use the `encode` function with MySQL blob types and they don't match
```
data-validation query -c test_mysql -q 'select encode(blob_column, "utf-8") from sample_table where (id < 1814)'
[(b'\xaa\xa0.\x8b\xcb\xbb',), (b'\xaa\xa0.\x8b\xcb\xbb',), (b'\xaa\xa0.\x8b\xcb\xbb',)]
```
The recommended approach for casting a MySQL blob to string is:
```
data-validation query -c test_mysql -q 'select convert(blob_column using utf8) from sample_table where (id < 1814)'
[('abbigc',), ('abbigc',), ('abbigc',)]
```
Here is are the parts of the query for Source (MySQL) and Target (Spanner)
```
, encode(t6.blob_column, :encode_1) AS cast__blob_column
FROM sample_table AS t6) AS t5) AS t4) AS t3) AS t2) AS t1) AS t0
WHERE id<1814
12/10/2023 11:27:54 PM-INFO: {'data_client': , 'schema_name': 'mtest', 'table_name': 'sample_table', 'target_query': None}
12/10/2023 11:27:54 PM-INFO: -- ** Target Query ** --
12/10/2023 11:27:54 PM-INFO: WITH t0 AS (
SELECT t6.*, CAST(t6.`blob_column` AS STRING) AS `cast__blob_column`
FROM sample_table t6
```
Sundar Mudupalli

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.