For a number of client-specific reasons as part of a data lakehouse project, I need to periodically ingest a datadump as a mysql database to an Azure MySQL flexible server.
I can connect to the db just fine using spark JDBC connectors from within a Databricks environment, and I am able to access the server host itself with Azure service principles, but I get blocked by MySQL Auth.
I've set up the mysql server to allow both MySQL and AAD auth, and I've tried the MySQL admin login and the aad login, with no luck. The server is setup to only allow networking within the virtual network, which also contains the client machine, but it does make debugging annoying.
Hoewver, I can connect using pyspark JDBC (python notebook in databricks env) using the MySQL username and password (corresponding to the placeholders 'myproject_admin' and 'Theactualpasswordincode') - see example belwo.
My first instinct would be to create a new user for me to use, but I keep getting syntax errors and I've seen a number of responses to Stack Overflow answers where people get the same errors, but no-one seems to have an explanation/solution.
I've tried with and without the semi-colon, various inverted comma permutations (yes, they are the right ones), and I still don't know what's up.
Does anyone know what is going on or have ideas for how to get the CLI prompt working? (ideally not ones involving parsing the datadump through JDBC)
this thread about differences in socket may hold a key, but I can't really do much without being able to change permissions.
Trying cli access using a service princpal and AAD auth:
%sh
az login --service-principal -u $AUTHID -p $AUTHPASSWORD --tenant $AZTENANT
mysql -h project-name-mysql-server.mysql.database.azure.com --user mysqlConnectorServicePrincipal@tenant.onmicrosoft.com@project-name --enable-cleartext-plugin --password=`az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken` -
ERROR 1045 (28000): Access denied for user 'mysqlConnectorServicePrincipal@tenant.onmicrosoft.com@pipeline'@'10.0.00.0' (using password: YES)
Trying cli access using native MySQL auth:
%sh
mysql -h project-name-mysql-server.mysql.database.azure.com -u myproject_admin -pTheactualpasswordincode
ERROR 1045 (28000): Access denied for user 'myproject_admin'@'10.0.00.0' (using password: YES)
Trying to create a user using JDBC:
%py
query: str = f"CREATE USER 'datadumpuploader'@'10.0.00.0' IDENTIFIED BY 'atestpassword';"
run_jdbc_query(source=source, query=query)
java.sql.SQLSyntaxErrorException: (conn=88) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE USER 'datadumpuploader'@'10.0.00.0' IDENTIFIED BY 'atestpassword';) SPARK_GEN_' at line 1
Trying a normal JDBC query: (also feat. helpful table):
%py
source: Source = Source(
name="data_dump",
server_url="jdbc:mysql://project-name-mysql-server.mysql.database.azure.com:3306/",
user=dbutils.secrets.get(secret_scope, "project-name-mysql-server-username"),
password=dbutils.secrets.get(secret_scope, "project-name-mysql-server-password"),
)
df: SparkDf = run_jdbc_query(source=source, query="SELECT user,authentication_string,plugin,host FROM mysql.user")
display(df)
df:
| user | authentication_string | plugin | host |
|---|---|---|---|
| mysqlConnectorServicePrincipal | AADAdmin:9999aa99-9aaa9-999a-a99a-a9999a999a9a:upn:mysqlConnectorServicePrincipal | aad_auth | % |
| [REDACTED] | *9999AA9999AAAAAAA999A99AAAA999A99AAAAA99 | mysql_native_password | % |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| azure_superuser | . | mysql_native_password | localhost |
| azure_superuser | . | mysql_native_password | 127.0.0.1 |
So it literally says "[REDACTED]" in the table, which I'm assuming corresponds to the admin username I put in during setup -i.e. placeholder myproject_admin - and I think it's fine, bc I can still connect using JDBC.
MySQL version 8.0
