GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator
Blob type Row Hash validation fails between MySQL and Spanner
- 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
Assessment
This issue has not been assessed yet.