SSL Connection Error while using MySQL Connector with Python

Question 1

I'm using Python 3.6 (but I get the same error with Python 2.7) and mysql-connector-python in an Anaconda's environment to code a simple script to access my database hosted in Hostgator. This is the error:

Traceback (most recent call last):
  File "/home/usuario/anaconda3/envs/fakebook/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 176, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: SSL connection error: SSL_CTX_set_tmp_dh failed

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "teste.py", line 6, in <module>
    passwd="senha"
  File "/home/usuario/anaconda3/envs/fakebook/lib/python3.6/site-packages/mysql/connector/__init__.py", line 172, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/home/usuario/anaconda3/envs/fakebook/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 78, in __init__
    self.connect(**kwargs)
  File "/home/usuario/anaconda3/envs/fakebook/lib/python3.6/site-packages/mysql/connector/abstracts.py", line 731, in connect
    self._open_connection()
  File "/home/usuario/anaconda3/envs/fakebook/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 179, in _open_connection
    sqlstate=exc.sqlstate)
mysql.connector.errors.InterfaceError: 2026 (HY000): SSL connection error: SSL_CTX_set_tmp_dh failed

My code is very simple, it just try to connect to the database:

import mysql.connector

mydb = mysql.connector.connect(
    host="192.xxx.xxx.xx",
    user="root",
    passwd="senha"
)

print(mydb)

I've used this library several times on other computers, I've also connected to this same database through my computer, using the same library and it always worked with this code. I tried with MySQL Workbench and seems it is connecting to the database using the same credentials that are in my code. I've already tried to ask help to the server support, my IP is allowed to access the database and even so I can't connect with Python. I tried to reinstall the library, but nothing changed.

Thank you everyone!

Question 2

This worked for me (Ubuntu 16.04 LTS). From terminal:

re-create certificates (datadir is of your choice):

mysql_ssl_rsa_setup --datadir=/data/dir/

Add the following to /etc/mysql/mysql.conf.d/mysqld.cnf:

ssl-ca=/data/dir/cacert.pem

ssl-cert=/data/dir/server-cert.pem

ssl-key=/data/dir/server-key.pem

Restart mysql server: sudo service mysql restart

Question 3

You need to use re.search and grouping,and Note that re.match match the pattern from beginning of the string :

>>> s="SKU 9780136058281, (ASIN B00A2KNZ2S, (binding Merchant: 'paperback' / 'hardcover'))"
>>> import re
>>> re.search(r'SKU (d+)',s).group(1)
'9780136058281'

r'SKU (d+) will match any combination of digits (d) with length 1 or more that came after SKU and a space!

Question 4

The problem is caused by a mix of Latin and Cyrillic characters. They print exactly the same in most policies, but are still different characters and do have different codes.

The output in the question is for Python 2.7 (what original question asker used) but it is easy to have equivalent behaviour in Python 3:

>>> print(test_str.encode('UTF8'))
b'Question: The cryptocurrency Bitcoin Cash (BCH/USD) settled at 1368 USD at 07:00 AM UTC at the Bitfinex exchange on Monday, April 23. In your opinion, will BCH/USD trade above 1500 USD (+9.65%) at anxd1x83 timxd0xb5 bxd0xb5fore xd0x90xd1x80ril 28? Indicxd0xb0txd0xber: 60.76%'

The unicodedata module helps to better understand what actually happens:

>>> for i in b'xd1x83xd0xb5xd0x90xd1x80xd0xbe'.decode('utf8'):
    print(i, hex(ord(i)), i.encode('utf8'), unicodedata.name(i))

у 0x443 b'xd1x83' CYRILLIC SMALL LETTER U
е 0x435 b'xd0xb5' CYRILLIC SMALL LETTER IE
А 0x410 b'xd0x90' CYRILLIC CAPITAL LETTER A
р 0x440 b'xd1x80' CYRILLIC SMALL LETTER ER
о 0x43e b'xd0xbe' CYRILLIC SMALL LETTER O

So the original text contains cyrillic letters and for comparisons, they are not the same of their latin equivalent, even if they print the same. The problem has nothing to do with split but is just a poor original string.

Question 5

I can explain your question "the length time it takes to get the connection to MySQL Open after no calls to the database have been made for about 3 or 4 minutes. If no database calls are made for a few minutes it takes 8 or 9 seconds or more to open the connection again." why it happens:

The Windows Azure websites uses concept of hot (active) and cold (inactive) sites in which if a websites has no active connection the site goes it cold state means the host IIS process exits. When a new connection is made to that websites it takes a few seconds to get the site ready and working. While you have MySQL backend associated to this website, it take a few more seconds longer to get the requested served as there is some time taken by IIS host process to get started. That is the reason after few minutes of in activity the the response time is longer.

You can see the following presentation for more details on Windows Azure Hot (active) and Cold (inactive) Websites: http://video.ch9.ms/teched/2012/na/AZR305.pptx

As this time, I am not sure and do not know how you can keep the websites always hot, even if moving to shared website or it is not possible at all. What I can suggest you to write your issue to Windows Azure WebSites Forum and someone from that team will provide you an appropriate answer.

Question 6

Try something like this (I didn't check any possible versions/configurations, but it works currently on my Vista x64 for MySql some 5.5... and .net connector 6.4.3.0 - using mysql.data.dll for v4 from .net/mono download).

Make sure the referenced mysql.data.dll assembly below is in your current directory.

using(var dt = new DataTable()) {
    dt.Columns.Add("Name");
    dt.Columns.Add("Description");
    dt.Columns.Add("InvariantName");
    dt.Columns.Add("AssemblyQualifiedName");
    dt.Rows.Add("Mysql something", 
        "mysql more", 
        "mysqlClient",
        "MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d");

    var f = DbProviderFactories.GetFactory(dt.Rows[0]);
    using(var conn = f.CreateConnection()) {
        conn.ConnectionString = "your string here";
        conn.Open();
        // and do your work here.
        Console.WriteLine(conn);
    }
}

SSL Connection Error while using MySQL Connector with Python

Answers

python,mysql,python-3.x,python-2.7