I am trying to set my backend db to a mysql instance. In my

.env

file, I have

MELTANO_DATABASE_URI='<mysql+pymysql://username:password@127.0.0.1/db=meltanobackend?host=localhost?port=3306>'

When I do a run, I get

No module named 'pymysql'

. I have tried

pip install

as well as added a custom utility but still get the same error. Does anyone know how to set up a mysql backend?

utilities:
  - name: pymysql
    namespace: pymysql
    pip_url: git+<https://github.com/PyMySQL/PyMySQL.git>
    executable: pymysql

@nancy_wong I dont think MySQL is supported as a backend DB yet https://github.com/meltano/meltano/issues/6529. In https://docs.meltano.com/concepts/project#system-database I see Postgres, SqLite, and MS SQL Server

I know the docs say it is unsupported but this PR implied that it works with MySQL.

Oh good find! It looks like it never got documented. I wonder if it works. The mssql version says that you need to install the mssql extra https://docs.meltano.com/guide/advanced-topics#installing-optional-components so I guess for the MySQL version you'd install it with meltano like

pipx install meltano[mysqlclient]

. Its worth a try to see if it works for you

Thanks for the suggestion Pat!

mysqlclient

was not in the

pyproject.toml

. I added it and was able to create an elt with a mysql backend. However, I am getting an error during table creation:

2023-04-25T22:53:55.953350Z [error    ] (MySQLdb.OperationalError) (1071, 'Specified key was too long; max key length is 3072 bytes')
[SQL: 
CREATE TABLE subscriptions (
        id CHAR(32) NOT NULL, 
        recipient VARCHAR(255) NOT NULL, 
        event_type VARCHAR(255) NOT NULL, 
        source_type VARCHAR(255), 
        source_id VARCHAR(255), 
        created_at DATETIME, 
        PRIMARY KEY (id), 
        UNIQUE (recipient, event_type, source_type, source_id)

@nancy_wong it looks like during that PR review there was a comment where they decided to move the mysql piece of it out to its own branch. I'm not familiar with the internals of meltano here but it looks like these character length issues are the types of things that the mssql PR needed to implement support for

Yeah, I had a draft PR (very stale now) that addressed all those mysql-specific constraints

Awesome! Will this PR be merged at some point, and is there anything I can do to help move it along?

@nancy_wong I'd defer to Edgar but I don't think the Meltano team is prioritizing support for MySQL right now, although contributions are always welcome and it looks like the draft PR just needs to be cleaned up and tested if you wanted to give it a shot

Good to know, thank you Pat! I will check with my team about contributing.