Hello everyone I m using tap mssql and target mysql to extra Meltano #troubleshooting

Hello everyone. I'm using tap-mssql and target-my...

marcelo_schneider_fialho

02/13/2025, 5:07 PM

Hello everyone. I'm using tap-mssql and target-mysql to extract some data from mssql and load on mysql. But when I run meltano run tap-mssql target-mysql I get an error when meltano tries to merge the temp table to table This is my meltano.yml

Copy code

version: 1
default_environment: dev
project_id: ae46d2ed-e79b-428e-8f59-2591618af307
environments:
- name: dev
- name: staging
- name: prod
plugins:
  extractors:
  - name: tap-mssql
    variant: wintersrd
    pip_url: tap-mssql
    config:
      database: DI
      schema: dbo
      tables:
      - name: LOCALIZAPESSOA
      - name: DOCENTE
      host: 10.70.0.58
      port: '5763'
      user: sa
    select:
    - dbo-LOCALIZAPESSOA.nompes
    - dbo-LOCALIZAPESSOA.codema
    - dbo-LOCALIZAPESSOA.numtelfmt
    - dbo-LOCALIZAPESSOA.nomset
    - dbo-LOCALIZAPESSOA.tipvinext
    - dbo-DOCENTE.codpes as codpes_docente
    - dbo-LOCALIZAPESSOA.codpes as codpes_localizapessoa
  loaders:
  - name: target-mysql
    variant: thkwag
    pip_url: thk-target-mysql
    config:
      database: replicado
      host: 127.0.0.1
      port: '3306'
      user: root
      allow_column_alter: true
      schema: replicado 
      sqlalchemyy_uri: <mysql://root:root@127.0.0.1/replicado>
      column_overrides:
      disable_on_duplicate_key_update: true

and this is the error: ``

Copy code

2025-02-13T13:42:09.996225Z [info     ] sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1052, "Column 'tipvin' in field list is ambiguous") cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.996666Z [info     ] [SQL:                          cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.997103Z [info     ]             INSERT INTO dbo.localizapessoa (codpes, tipvin, numseqpes, codfncetr, tipvinext, nompes, nomset, codema, numtelfmt) cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.997466Z [info     ]                 SELECT codpes, tipvin, numseqpes, codfncetr, tipvinext, nompes, nomset, codema, numtelfmt cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.997828Z [info     ]                 FROM           cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.998217Z [info     ]                     dbo.localizapessoa_temp temp cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.998569Z [info     ]             ON DUPLICATE KEY UPDATE cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.999029Z [info     ]                 codpes = VALUES(codpes) and tipvin = VALUES(tipvin) and numseqpes = VALUES(numseqpes) and codfncetr = VALUES(codfncetr) cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:09.999875Z [info     ]         ]                      cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql
2025-02-13T13:42:10.000119Z [info     ] (Background on this error at: <https://sqlalche.me/e/14/e3q8>) cmd_type=elb consumer=True job_name=dev:tap-mssql-to-target-mysql name=target-mysql producer=False run_id=eafb9dd3-2983-4ae0-b993-8181018b875b stdio=stderr string_id=target-mysql

Edgar Ramírez (Arch.dev)

02/13/2025, 11:12 PM

I think that upsert condition should be instead

Copy code

ON DUPLICATE KEY UPDATE 
    codpes = NEW.codpes,
    tipvin = NEW.tipvin,
    numseqpes = NEW.numseqpes,
    codfncetr = NEW.codfncetr;

Edgar Ramírez (Arch.dev)

02/13/2025, 11:12 PM

https://github.com/thkwag/target-mysql/issues/7

marcelo_schneider_fialho

02/17/2025, 12:41 PM

Thank you @Edgar Ramírez (Arch.dev). How can I chage to the suggested code in meltano?

Edgar Ramírez (Arch.dev)

02/17/2025, 6:37 PM

You'll probably need to fork that repository, make the change, and follow this section of the plugin management guide to use that in your project: https://docs.meltano.com/guide/plugin-management/#using-a-custom-fork-of-a-plugin

fred_t

02/21/2025, 12:46 PM

Good afternoon I understand why this is an issue in MySQL 8.0, as the use of VALUES() was deprecated Puzzled as to why i am getting the same issue with MySQL 5.7 though...

fred_t

02/21/2025, 12:55 PM

Think i worked out what happened THis syntax generated by the plugin (or SQL Alchemy?) is wrong for 5.7

Copy code

INSERT INTO theswamp.coin (principal_id, timestamp_millis, state, datasource, blockchain, name, sub_unit_name, coin)
SELECT principal_id, timestamp_millis, state, datasource, blockchain, name, sub_unit_name, coin
 FROM
 theswamp.coin_temp
 ON DUPLICATE KEY UPDATE
	datasource = VALUES(datasource) AND coin = VALUES(coin)

The above on 5.7 returns : Error Code: 1052. Column 'coin' in field list is ambiguous But if i replace the AND with a comma, it gets executed successfully on my 5.7 server (line 6)

fred_t

02/21/2025, 1:01 PM

as per sinks.py which uses an AND instead of a comma for the upsert_on_condition

fred_t

02/21/2025, 1:11 PM

this fork has fixed it https://github.com/janlink/target-mysql/blob/main/target_mysql/sinks.py

fred_t

02/21/2025, 9:08 PM

i confirm the above fork solved my issue when installed as a plugin

Copy code

loaders:
  - name: target-mysql
    namespace: target_mysql
    pip_url: git+<https://github.com/janlink/target-mysql.git>

Edgar Ramírez (Arch.dev)

02/21/2025, 10:21 PM

Nice! Perhaps we need to add that fork to the hub

fred_t

02/24/2025, 6:36 PM

I guess not many people in a modern data stack populate a MySQL DB as an endpoint...which is why that fix is missing from the main branch Not 100% sure if it is a 5.7 or 8.0 dependent fix either.

9 Views

Open in Slack

Previous Next