MySQL driver querying information_schema with a table_schema value of the user instead of the database

[DominicChiampi]

The first queries that data-diff performs is getting the columns from the specified table; however, the query is using the user as the TABLE_SCHEMA instead of the database.

data-diff --debug mysql://root:root_password@mysql5:3306/coolname Transaction mysql://user:user_password/@mysql:3306/coolname Transaction
[11:33:45] DEBUG - Running SQL (MySQL): SELECT column_name, data_type, datetime_precision, numeric_precision, numeric_scale FROM information_schema.columns WHERE table_name = 'Transaction' AND table_schema = 'root'
[11:33:45] DEBUG - Running SQL (MySQL): SELECT column_name, data_type, datetime_precision, numeric_precision, numeric_scale FROM information_schema.columns WHERE table_name = 'Transaction' AND table_schema = 'user'

In MySQL's information schema the TABLE_SCHEMA is the database, not the user
https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.0/en/information-schema-columns-table.html

MySQL [(none)]> select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, engine from information_schema.tables where table_name = 'Transaction'\G
*************************** 1. row ***************************
TABLE_SCHEMA: coolname
  TABLE_NAME: Transaction
  TABLE_TYPE: BASE TABLE
      ENGINE: InnoDB
1 row in set (0.003 sec)

This is causing data-diff to fail to find the table and columns so data-diff cannot function

RuntimeError: MySQL: Table 'Transaction' does not exist, or has no columns

Versions:

root@22b19e3a116f:/# python3 --version
Python 3.7.3
root@22b19e3a116f:/# pip3 list
Package                Version
---------------------- ---------
asn1crypto             0.24.0
awscli                 1.16.113
botocore               1.12.103
certifi                2018.8.24
chardet                3.0.4
click                  8.1.3
colorama               0.4.5
commonmark             0.9.1
cryptography           2.6.1
data-diff              0.2.0
docutils               0.14
dsnparse               0.1.15
entrypoints            0.3
idna                   2.6
importlib-metadata     4.11.4
jmespath               0.9.4
keyring                17.1.1
keyrings.alt           3.1.1
mysql-connector-python 8.0.29
pip                    18.1
protobuf               4.21.1
pyasn1                 0.4.2
pycrypto               2.6.1
Pygments               2.12.0
PyGObject              3.30.4
python-dateutil        2.7.3
pyxdg                  0.25
PyYAML                 3.13
requests               2.21.0
rich                   10.16.2
roman                  2.0.0
rsa                    4.0
runtype                0.2.6
s3transfer             0.2.0
SecretStorage          2.3.1
setuptools             40.8.0
six                    1.12.0
typing-extensions      4.2.0
urllib3                1.24.1
wheel                  0.32.3
zipp                   3.8.0

[erezsh]

Yes, you're right. The code has a "typo" -

        self.default_schema = user

We just fixed it and already merged to master. We'll soon push it to pypi too.

[erezsh]

Released in 0.2.1. Give it a try!

[DominicChiampi]

It works great! Thanks so much for your quick response and turn around.

[sirupsen]

@DominicChiampi is your use-case MySQL <-> MySQL? Would love to learn what you're doing with data-diff 😍

[DominicChiampi]

@sirupsen I have some (Amazon Aurora) managed MySQL 5.6 databases. Aurora's 5.6 engine is reaching end-of-life early next year. As a result I am exploring different upgrade strategies, for example, upgrading the existing cluster in-place 5.6 -> 5.7 or 5.6 -> 5.7 -> 8.0 or staging a new database server cluster at 8.0 and doing a mysqldump -> import from the 5.6 cluster to the 8.0 cluster.

I don't expect that any of these processes would introduce data integrity issues, but data-diff allows me to quickly collect evidence to support my assumptions.

I expect my final product will be several scripts that each implement one of these strategies with some data-diff usage at the end as part of an automated verification. Then on "go-live" night I will run them in parallel and swap to the cluster that finishes first and does not show any differences from the source database.

MySQL 5.7+ also introduces a JSON data type for columns that my developers are interested in using. There are a couple of columns that are JSON serialized into a varchar typed column. I am unsure if data-diff will work for verifying the column type conversion, but I plan on trying when we get there.