2

I have been trying to make a Python program that connects to a Planetscale MySQL DB, I have used 2 libraries that are mysql-connector-python and mysqlclient.

I think I have entered the correct details every time with both but it hasn't worked.

I tried Planetscale's recommended way which is the following (it didn't work for me btw) :

import MySQLdb # pip install mysqlclient

# This is planetscale's copy/paste method to connect to the DB

conn = MySQLdb.connect(
  host=[HOST],
  user=[USER],
  passwd=[PASSWORD],
  db=[DATABASE],
  ssl_mode = "VERIFY_IDENTITY",
  ssl      = {
    "ca": "/etc/ssl/cert.pem"
  }
)

This is the current code I'm using

import mysql.connector # pip install mysql-connector-python

config = {
    "user": hidden for security purposes,
    "password": hidden for security purposes,
    "host": hidden for security purposes,
    "database": hidden for security purposes,
    "ssl_verify_identity": True,
    "ssl_ca": "/etc/ssl/cert.pem",
}
conn = mysql.connector.connect(**config)

cursor = conn.cursor()

cursor.execute("""CREATE TABLE IF NOT EXISTS qr_table (
    type VARCHAR(255),
    date VARCHAR(255),
    path VARCHAR(255),
    unique_id VARCHAR(255)
)""")

conn.commit()
cursor.close()
conn.close()

This is the error I'm getting

Traceback (most recent call last):
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\connection_cext.py", line 268, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: SSL connection error: SSL_CTX_set_default_verify_paths failed

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Axelr\PycharmProjects\PC01\main\Special Main\ARC QR Generator\SQL Script.py", line 26, in <module>
    conn = mysql.connector.connect(**config)
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\pooling.py", line 286, in connect
    return CMySQLConnection(*args, **kwargs)
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\connection_cext.py", line 101, in __init__
    self.connect(**kwargs)
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\abstracts.py", line 1108, in connect
    self._open_connection()
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\connection_cext.py", line 273, in _open_connection
    raise get_mysql_exception(
mysql.connector.errors.InterfaceError: 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed

Process finished with exit code 1

Can someone please help me make a connection that works?

Also, it wouldn't be a problem to use another library to connect.

[Python 3.10]

axelrothing
  • 51
  • 1
  • 1
  • 10
  • 1
    I added a first try of answer using the official the documentation to check the use of the connector which seems wrong. If you could provide more details about the error you are getting it would be helpful (traceback for instance) – AlexTorx Apr 15 '23 at 16:52
  • I have now updated the question with much more information including code and errors. – axelrothing Apr 15 '23 at 17:49
  • 1
    Thanks for updated ! This looks like : https://stackoverflow.com/questions/54485148/ssl-connection-error-while-using-mysql-connector-with-python – AlexTorx Apr 16 '23 at 07:56
  • Usually SSL issues are related to : 1/ not having an SSL client on the machine running the script 2/ Having misconfigured SSL on database side (wrong certificate for instance) – AlexTorx Apr 16 '23 at 07:57
  • Plus you can have certificates at two levels : host certificate (for the URL of the server that hosts the database) and at the database level (that secures access to the data itself) – AlexTorx Apr 16 '23 at 07:58
  • Thank You @AlexTorx, the question you linked helped me very much, the issue has now been resolved. What i did is to remove the ssl – axelrothing Apr 16 '23 at 10:10

2 Answers2

0

You should just not be using lists for input parameters I guess. Simply use strings for all parameters. Also, you should check documentation for correct parameters names (see https://mysqlclient.readthedocs.io/user_guide.html#mysqldb)

A working piece of code should be :

import MySQLdb

conn = MySQLdb.connect(
  host=HOST,
  user=USER,
  password=PASSWORD,
  database=DATABASE,
  ssl_mode = "VERIFY_IDENTITY",
  ssl      = {
    "ca": "/etc/ssl/cert.pem"
  }
)
AlexTorx
  • 753
  • 4
  • 9
  • I'm sorry for the confusion, the brackets around where just for people to see that its where i have placed the correct details, i already have seen the documentation and it hasn't worked for me. The reason i hide the details is for security purposes. – axelrothing Apr 15 '23 at 17:32
0

I have fixed the issue.

The problem was the SSL and that I used a dictionary to store the details, then used **config to use the dictionary in the connection.

Here is the correct and working code for me:

import mysql.connector as sql

conn = sql.connect(host="*Hidden For Security Purposes*",
                   database="*Hidden For Security Purposes*",
                   user="*Hidden For Security Purposes*",
                   password="*Hidden For Security Purposes*",
                    )

Thanks to everyone helping me with this problem and i hope this StackOverflow question will help many with the same problem.

axelrothing
  • 51
  • 1
  • 1
  • 10