Can't connect to database

Hello,

I recently upgraded my database server. It was working when it was running MySQL on Ubuntu Server 19.10, but when I upgraded it to Ubuntu 20.04, my Raspberry PIs running OSMC will no longer connect to the database specified in my advancedsettings.xml file.

HOWEVER - the Kodi windows app, Kodi windows STORE app and Android app all work (I have not tried Linux app yet). I can also connect from MySQL Workbench with that username and password.

The error I get is:
“ERROR: Unable to open database: MyVideos116 [2026](SSL connection error: unknown error number)”
…but for every possible # of the database version…

mysql (command line) is not available on the osmc “box” (maybe I should install?) to test connections to the host from the client.

Any ideas how to troubleshoot or, better yet, what is actually wrong?

Thanks,

–mobrien118

To get a better understanding of the problem you are experiencing we need more information from you. The best way to get this information is for you to upload logs that demonstrate your problem. You can learn more about how to submit a useful support request here.

Depending on the used skin you have to set the settings-level to standard or higher, in summary:

  • enable debug logging at settings->system->logging

  • reboot the OSMC device twice(!)

  • reproduce the issue

  • upload the log set (all configs and logs!) either using the Log Uploader method within the My OSMC menu in the GUI or the ssh method invoking command grab-logs -A

  • publish the provided URL from the log set upload, here

Thanks for your understanding. We hope that we can help you get up and running again shortly.

OSMC skin screenshot:

Always can install that with sudo apt-get install mysql-client that surely will make trouble shooting easier.
But sharing the logs first will give us best position to help

Logs are here:
https://paste.osmc.tv/lapoyunaho

Please note that I “unblocked” the host, but after about 15 failed connections it started denying it for trying too much.

Also, I took your advice and installed mysql-client. After flushing the hosts again, I was able to do this:

osmc@MC-Living-Room:~$ mysql -u xbmc -p -h 192.168.1.147
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 704
Server version: 8.0.19-0ubuntu5 (Ubuntu)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| CMII               |
| MyMusic72          |
| MyVideos116        |
| gallery3           |
| gurl               |
| information_schema |
| kworrell           |
| mobrien            |
| mysql              |
| performance_schema |
| which-app          |
+--------------------+
11 rows in set (0.01 sec)

MySQL [(none)]> use MyVideos116;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [MyVideos116]> show tables
    -> ;
+------------------------+
| Tables_in_MyVideos116  |
+------------------------+
| actor                  |
| actor_link             |
| art                    |
| bookmark               |
| country                |
| country_link           |
| director_link          |
| episode                |
| episode_view           |
| files                  |
| genre                  |
| genre_link             |
| movie                  |
| movie_view             |
| movielinktvshow        |
| musicvideo             |
| musicvideo_view        |
| path                   |
| rating                 |
| season_view            |
| seasons                |
| sets                   |
| settings               |
| stacktimes             |
| streamdetails          |
| studio                 |
| studio_link            |
| tag                    |
| tag_link               |
| tvshow                 |
| tvshow_view            |
| tvshowcounts           |
| tvshowlinkpath         |
| tvshowlinkpath_minview |
| uniqueid               |
| version                |
| writer_link            |
+------------------------+
37 rows in set (0.00 sec)

MySQL [MyVideos116]> exit
Bye
osmc@MC-Living-Room:~$

Last noteworthy report: I am able to connect without issue from yet another system with the same “advancedsettings.xml” - my Ubuntu 18.04 box. So, I have successfully connected with:

  1. Both Windows Kodi software types
  2. An android installation of Kodi
  3. Another Linux (Ubuntu 18.04 - x86_64)

However, same “advancedsettings.xml” on OSMC and - no dice.

Maybe an SSL negotiation issue with whatever connection string parameters OSMC is using for SSL? Cipher mismatch or something?

Thanks!

–mobrien118

Where did you “unblock” and what did you “unblock”???

Based on a quick google could be this problem

I think you might be the first one using mysql with SSL on OSMC.

Hmmm…

I wonder what would trigger that. This started when I upgraded my server from Ubuntu 19.10 to 20.04. Looking here, it looks like they both use MySQL 8.0.19 (doesn’t mean they didn’t change configs though):

The error you sent leads to an unconfirmed bug stating that the client and server need certs with different CNames. However, here is what I get on the server:

root@onlysql:/var/lib/mysql# openssl x509 -noout -subject -in server-cert.pem
subject=CN = MySQL_Server_8.0.19_Auto_Generated_Server_Certificate
root@onlysql:/var/lib/mysql# openssl x509 -noout -subject -in ca.pem
subject=CN = MySQL_Server_8.0.19_Auto_Generated_CA_Certificate

Not sure where to look for the .pem files on OSMC, but I doubt it is using SQL 8.0.19 already…? I can follow up on that. That would possibly explain why it works on windows, Ubuntu 18.04 and Android, but not OSMC, though - they are almost certainly not using MySQL with certs with the same CN. Also, I don’t remember where the setting is, but I believe MySQL Server is set to “Allow SSL” not to force it. Again, I need to follow back up on that.

To answer your previous question, I should have been more specific - in the log you will see that the MySQL errors change from

2020-04-29 23:28:03.112 T:1788637920   ERROR: Unable to open database: MyMusic72 [2026](SSL connection error: unknown error number)
2020-04-29 23:28:03.114 T:1788637920   ERROR: Unable to open database: MyMusic71 [2026](SSL connection error: unknown error number)
...

to

2020-04-29 23:28:03.153 T:1788637920   ERROR: Unable to open database: MyMusic57 [1129](Host 'W.X.Y.Z' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts')
2020-04-29 23:28:03.155 T:1788637920   ERROR: Unable to open database: MyMusic56 [1129](Host 'W.X.Y.Z' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts')

when the client gets banned for failed attempts. I have to run “mysqladmin -u root -p flush-hosts” on the server to “unblock” it and try again…

HI,

Whats in /etc/my.cnf ?

Edit @mobrien118. Sorry just clarify thats my.cnf on the ubuntu server

Thanks Tom.

Not very much. Everything is in the branch file /etc/mysql/mysql.conf.d/mysqld.cnf. Here is everything that’s not commented out:

[mysqld]
user            = mysql
bind-address            =W.X.Y.Z
key_buffer_size         = 16M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
max_binlog_size   = 100M
# Added by me 4/25/2020
max_connections = 1000
default-authentication-plugin=mysql_native_password

having exact same issue, fire sticks and other PCs no issues just pi.
Originally i saw about the server bind-address 0.0.0.0 needing to be changed to server static IP but did that without improvement.

1 Like

Found this, too. When I connect from the OSMC machine using mysql client, I get this:

MySQL [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

Connection id:          792
Current database:
Current user:           xbmc@W.X.Y.Z
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         8.0.19-0ubuntu5 (Ubuntu)
Protocol version:       10
Connection:             A.B.C.D via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Uptime:                 4 days 5 hours 48 min 59 sec

Threads: 4  Questions: 10951  Slow queries: 0  Opens: 2804  Flush tables: 3  Open tables: 2542  Queries per second avg: 0.029
--------------

It looks like it is fine connecting without SSL from the host.

I wonder why the OSMC client would be any different.

I wonder if there is a way to find out what connection options OSMC is using. Since I can connect from the OSMC machine using the mysql-client directly, I have to think this has something to do with the connection properties. It may be a compound issue with using so much different versions of MySQL, too. Here are the versions:

osmc@MC-Living-Room:~$ apt list mysql-common
Listing... Done
mysql-common/oldstable,now 5.8+1.0.2 all [installed,automatic]
osmc@MC-Living-Room:~$ apt list mysql-client
Listing... Done
mysql-client/oldstable,now 5.5.9999+default armhf [installed]

vs

root@onlysql:/etc/mysql/mysql.conf.d# apt list mysql-common
Listing... Done
mysql-common/focal,now 5.8+1.0.5ubuntu2 all [installed,automatic]
root@onlysql:/etc/mysql/mysql.conf.d# apt list mysql-server
Listing... Done
mysql-server/focal,now 8.0.19-0ubuntu5 all [installed]

I mean, they both have a similar version of mysql-common, but I don’t know how all of the different package versions tie together. Maybe the client is trying to use SSL and the server says it accepts it, then when it tries to connect there is a cipher mismatch. I have no idea how to test that theory though.

And

/etc/my.cnf.d/mariadb-server.cnf

Thanks Tom.

No such file. The only folder matching "/etc/my* is /etc/mysql/

I’m not running MariaDB - just MySQL.

Also, I’ve looked through all of the loaded .cnf files and the only one containing actual settings is the one I sent you.

OK then, sorry I’m not sure what the issue is then. It may be worth contacting mysql support.

Thanks Tom.

Just an observation. It might not be relevant to your problem but it’s worth pointing out that you have SSH port forwarding set on your router and the machine is being actively targeted.

Apr 29 23:28:00 MC-Living-Room sshd[495]: Failed password for root from 36.90.212.84 port 54232 ssh2
Apr 29 23:28:00 MC-Living-Room sshd[495]: Connection closed by 36.90.212.84 port 54232 [preauth]
Apr 29 23:28:00 MC-Living-Room sshd[493]: Failed password for root from 186.206.129.160 port 43445 ssh2
Apr 29 23:28:00 MC-Living-Room sshd[493]: Received disconnect from 186.206.129.160 port 43445:11: Bye Bye [preauth]
Apr 29 23:28:00 MC-Living-Room sshd[493]: Disconnected from 186.206.129.160 port 43445 [preauth]
...
Apr 29 23:28:03 MC-Living-Room sshd[510]: Invalid user alex from 27.128.187.131 port 59752
Apr 29 23:28:03 MC-Living-Room sshd[510]: input_userauth_request: invalid user alex [preauth]
Apr 29 23:28:03 MC-Living-Room sshd[510]: pam_unix(sshd:auth): check pass; user unknown
Apr 29 23:28:03 MC-Living-Room sshd[510]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=27.128.187.131

So, it cannot be ruled out that your machine has been compromised and the problems you see are related.

As well as also experiencing it i have found few reports across the internet but not found a solution yet.
Kodi.tv
Ubuntu Launchpad

Yep - that’s another project I’m working on, but this machine access should be turned off, so thanks for reminding me.

This situation, however, is not a result of a compromised system. I have several reasons for determining this, some of which are already mentioned in this thread and others that I have not mentioned, but rest assured, this issue is not caused by a network intrusion.

That’s abviously your call.

MySQL performs encryption on a per-connection basis, and use of encryption for a given user can be optional or mandatory. [https://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html]

From the OSMC command line, you connected successfully and we see SSL: Not in use, so encryption isn’t globally mandatory.

Since you can access the database from the command line, without using encryption, I think you need to focus more on the Ubuntu configuration side. This page is a good starting point: https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html. I don’t think you’ve mentioned whether you want encryption enabled, but it looks like it should be easy enough to disable on the server.

Its not the best way, I think i will probably look to secure MYSQL in the near future for all connections.
But dirty fix right now to get back to normal is to add below at the bottom of /etc/mysql/mysql.conf.d/mysqld.cnf on the server.
skip_ssl

Restart the mysql server and then restart mediacenter on OSMC. Found this work around here