https://linen.dev logo
Join Slack
Powered by
Hey folks, I'm trying to run a very basic command:...
# troubleshooting
k
Hey folks, I'm trying to run a very basic command: 
meltano --log-level=debug run tap-postgres target-mysql
. The pipeline fails, and essentially I get this error:
Copy code
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1044, "Access denied for user 'root'@'%' to database 'information_schema'")
Any suggestions?
✅ 1
v
The error looks pretty clear to me, 
root
doesn't have access to 
information_schema
?
k
@visch Yeah I realise the obvious error, but 
information-schema
is a MySQL system table, and every user should supposedly have read only access. From what I could research, you cannot "grant" privileges to a user for this table.
So the cycle I am stuck in is, the error tells me that the user is not able to access that table, but mysql doesn't allow me to grant access. I was wondering if someone had experience with this error.
v
k
@visch Hey what I am trying to do is really simple, I shouldn't really have to go in and do that, plus they're doing something else. Maybe I should open a ticket so you could have a look at the docker compose setup?
@visch It's as simple as this, mysql setup in docker compose.
Copy code
mysql:
    image: bitnami/mysql
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: root # Password for the MySQL root user
      MYSQL_DATABASE: acko0 # Default database to create
      MYSQL_USER: root # Default user to create
      MYSQL_PASSWORD: root # Password for the default user
    ports:
      - '3306:3306'
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init/sql/mysql:/docker-entrypoint-initdb.d
    networks:
      - acko-network
and the meltano.yml
Copy code
version: 1
default_environment: dev
project_id: df9096f2-1b1c-43a2-8a70-ddeafcc4c69e
environments:
  - name: dev
  - name: staging
  - name: prod
plugins:
  extractors:
    - name: tap-mysql
      config:
        host: $MYSQL_HOST
        port: $MYSQL_PORT
        user: $MYSQL_USER
        password: $MYSQL_PASSWORD
        database: $MYSQL_DATABASE
      variant: transferwise
      pip_url: pipelinewise-tap-mysql
    - name: tap-postgres
      config:
        host: $PG_HOST
        port: $PG_PORT
        user: $PG_USER
        password: $PG_PASSWORD
        database: $PG_DATABASE
      variant: meltanolabs
      pip_url: git+<https://github.com/MeltanoLabs/tap-postgres.git>
    - name: tap-csv
      variant: meltanolabs
      pip_url: git+<https://github.com/MeltanoLabs/tap-csv.git>
  loaders:
    - name: target-mysql
      config:
        host: $MYSQL_HOST
        port: $MYSQL_PORT
        user: $MYSQL_USER
        password: $MYSQL_PASSWORD
        database: $MYSQL_DATABASE
      variant: thkwag
      pip_url: thk-target-mysql
    - name: target-postgres
      config:
        host: $PG_HOST
        port: $PG_PORT
        user: $PG_USER
        password: $PG_PASSWORD
        database: $PG_DATABASE
      variant: meltanolabs
      pip_url: meltanolabs-target-postgres
    - name: target-csv
      variant: meltanolabs
      pip_url: git+<https://github.com/MeltanoLabs/target-csv.git>
v
oh, man that makes next steps much easier to help you with. Try the meltano labs variant https://hub.meltano.com/extractors/tap-mysql--meltanolabs/
iirc I don't use information schema but I could be mis remembering
e
read access to information_schema should be required for discovery iiuc 🤔
k
I'm using the meltano variant. im running 
meltano run tap-postgres target-mysql
, the error comes during the target part.
@Edgar Ramírez (Arch.dev) in the same boat as you
no idea why that's required, i'm not doing that on my end
v
Can you post the full error? I don't understand why that'd happen on the target side
k
output.log
output.log
@visch i've attached the error logs
e
Hmm, it's trying to create tables inside information_schema?? I see
Copy code
CREATE TABLE information_schema.pg_foreign_data_wrappers_
Oh, I think it's because tap-postgres is extracting a 
information_schema-pg_foreign_data_wrappers
stream 🤦‍♂️
@Kaustav Mukhopadhyay can you exclude 
information_schema.*
streams in your tap-postgres config?
🙌 2
k
Hey that worked out! Thanks man
just putting this out here in case someone else faces this err later, just add this to your config
Copy code
- name: tap-postgres
      config:
        host: $PG_HOST
        port: $PG_PORT
        user: $PG_USER
        password: $PG_PASSWORD
        database: $PG_DATABASE
        filter_schemas:
          - '!<table-to-exclue>'
👌 1
v
very nice, another way to do that @Kaustav Mukhopadhyay is to do it in select but this works just as well!
2 Views
Open in Slack
PreviousNext
Powered by