[themusj]

I have currently my database separate on my Android TV and Windows PC. In time past, I tried having a central Kodi DB in MySQL on my My Cloud Ultra Ex2. Results worked but were too slow so I moved back to separate databases.

Now I am trying again with a new Qnap TS-233 and MariaDB. I've been all over the Internet and have not really found a definitive setup. I've pieced together the following.

Installed MariaDB app and phpMyadmin. MariaDB enabled TCP/IP connection on port 3307.
Edited phpMyadmin's config file (added three lines) in order to resolve errors where I could not connect to MariaDB.
Created kodi user in MariaDB user account.
Edited my.cnf file in MariaDB with vim editor and added #bind-address = 0.0.0.0 to allow for Remote Client Access. #skip-networking already set in file.

Firewall. I understand Qnap does NOT have any firewall turned on by default. I have it now installed, but not configured to simply setup for now. I shut unit down each time I finish attempting solution. So I do not think I have any firewall settings to configure at this point.

Created advancedsettings.xml and place in proper Kodi folder on Windows PC. Backed up kodi setup.

I launch Kodi and I can tell it is trying to process request. But it hangs. I check MariaDB for new databases and none are created. I verified I can login into database with kodi account via phpMyadmin.

I am really not sure where to go from here in trouble-shooting the issue. Certainly this has been more challenging that MySQL. But I read MariaDB would be faster.

Any help would be appreciated. Once I get this working, it is my desire to create a thread documenting the whole process to help others. I found the two links below the most helpful at this point.

https://forum.kodi.tv/showthread.php?tid=354738

https://community.synology.com/enu/forum/1/post/134323

[Klojum]

I launch Kodi and I can tell it is trying to process request. But it hangs. I check MariaDB for new databases and none are created. I verified I can login into database with kodi account via phpMyadmin.

Please provide Kodi's debug log (wiki) for more details.

[themusj]

I just looked at the log and its very clear the problem is "can't connect to the MySQL server on xxx.xxx.xxx.xxx error 10061."

So I've got to figure out why it cannot communicate with my Qnap and the MariaDB specifically. That has been the crux of the whole goal all along. 

When I do get this working, am I supposed to copy over my settings file as well to my menu and interface take on the characteristics of what I had before the move to SQL?

[themusj]

I just disabled MariaDB10 and installed MariaDB5 and the log clearly indicates they are not connecting. I think I am at the giving up point as I have spend each evening a week on this adventure. 

Does anyone know if regular MySQL can be installed on a Qnap NAS? There is so little documentation out there for doing what I am trying to do.  Plenty of info on configuring regular Linux systems, but I am only a struggling Linux user.

I obviously failed at researching this unit before I purchased a Qnap. I see some people use them as their entire Kodi server. Not sure I wanted to go there.

Anyone familiar with this working on Synology? I might just donate this unit to our local Mission and move to those units if someone can attest they are simpler to setup.

[Fuchs2468]

Edited my.cnf file in MariaDB with vim editor and added #bind-address = 0.0.0.0 to allow for Remote Client Access. #skip-networking already set in file.

I hope you did not write # 
because then the respective setting is not active.

[themusj]

Not sure what you mean exactly. Every piece of information I found on the Internet stated to add # in front of bind-address if it existed or create it if it did not.

"Now we can safely edit the my.cnf file and 'mark out' the [font=Calibri,sans-serif]bind-address = 0.0.0.0 line  with an # character." https://forum.kodi.tv/showthread.php?tid=354738

"Now we can safely edit the my.cnf file and mark out the following lines with an # character, like this.
   #bind-address = 0.0.0.0  https://community.synology.com/enu/forum/1/post/134323

https://mariadb.com/kb/en/configuring-ma...nt-access/

[Klojum]

Please provide Kodi's debug log

I just looked at the log

That's not the same thing. But if you are already squeamish about handing over a (probably) simple local ip number, I'm guessing we'll never get Kodi's log.

So is the kodi database user set up correctly in MariaDB?
Are all port references set the same for MariaDB?
The bind-address line should be disabled in the .cnf file.
Also, all of the cache settings are better off doubled in size.
Editing settings in PHPMyAdmin does not do anything for Kodi.
Disable your NAS firewall for now.

Installing MariaDB v5 is not a good idea. It has been EOL for some time now. Use a v10.x stable version wherever possible.

[themusj]

I’ll send the log tonight. I honestly thought based on what I saw it would be of limited help. Yes it’s a simple internal IP. I blocked it out thinking the Wiki suggested that practice under the PII heading. I see now it didn’t.

Thank you for your follow up. I don’t want to quit. I have to be close.

[themusj]

Thinking about this at work.

— I created my MariaDB Kodi user account from within the phpMyadmin tool. Created two accounts. One localhost and one with remote access as designated by %.

Both had full permissions to everything.

I’ve read where you can create users via the create user syntax when Putty to the database directly with VIM.

I have assumed phpMyadmin accomplishes the same thing as the latter.

Is that not so?

[Klojum]

Is that not so?

The methods can vary, depending on what OS/machine/server you are installing MySQL or MariaDB on.
I prefer to use an SSH connection and type everything via a command line in order to create users on the database server.
Did you use 'flush privileges' or reboot the database server in order to finalize the new users and settings?

[themusj]

I have only created user via phoMyadmin; but I rebooted the NAS itself several times. I cannot recall disabling and re enabling MariaDB after the user account was created by phpMyAdmin.

I can certainly try to use the syntax method tonight. I’ve been reading about how to do just that during my lunch break.

[Klojum]

The syntax method should result into something like this:

sql:
klojum@server:~$ ssh dtbsrvr

klojum@dtbsrvr`s password: 

Welcome to Ubuntu 18.04.6 LTS (GNU/Linux 5.4.0-117-generic x86_64)



klojum@dtbsrvr:~$ 

klojum@dtbsrvr:~$ sudo mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.38-0ubuntu0.18.04.1 (Ubuntu)



mysql> use mysql;

Database changed



mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| MyMusic82          |

| MyVideos119        |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

6 rows in set (0,00 sec)



mysql> select * from user where user="kodi";;

+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |

+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

| %    | kodi | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | **********************                    | N                | 2022-05-07 07:59:37   |              NULL | N              |

+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

1 row in set (0,00 sec)



mysql> 

[themusj]

1). The log file has my full name all over the place. Is that normal?

2). I have tried to login as root and follow your commands. Perplexing. 

• Cannot login in as root via Putty to Qnap IP address. Does not accept password.
  
• Qnap user accounts are admin, and the one I created for myself. I can login with Putty using both those accounts. Commands such as SHOW DATABASES and CREATE USER error with command not found.
  
• I can login to phpMyAdmin with root localhost all privileges. Root password was changed in MariaDB GUI via reset.
  
• When attempting to reset User Passwords in MariaDB10 Account and Database, it requires root password. Now root password is not accepte
  

So it seems I cannot run these Linux commands in this version of MariaDB10 and how these accounts interact is beyond me.

[Klojum]

1). The log file has my full name all over the place. Is that normal?

My guess is that you have your own full name as a computer for your Windows computer..? Kodi logs are stripped from login details (username, password). But if you have your full name also as a Windows user, including one or more spaces(?), those may pop up. Although I'm still a lil confused what you actually mean with "all over the place". With a simple text editor, you can do a "Find and replace" to change the log before you upload it.

2). I have tried to login as root and follow your commands. Perplexing. 

Type in your SSH session window mysql --version . Some info should appear.
The default port is 3306. If MariaDB uses 3307, add the port number on the command line when connecting: mysql -u root -pS@cure1Pass --port=3307

I don't know how much QNAP has sown shut the doors on SSH and/or its commands, but you need to be "in" MySQL/MariaDB itself first. The SQL commands do not work from the default Linux prompt area.
What happens when you type just mysql in the SSH session? It should change your prompt into "mysql>" like in my example.

I can login to phpMyAdmin with root localhost all privileges. Root password was changed in MariaDB GUI via reset.

That doesn't compute with me. Why would the root password (need to) be changed?

When attempting to reset User Passwords in MariaDB10 Account and Database, it requires root password. Now root password is not accepte

Just to make sure, but there are separate root passwords:
- Your NAS itself has a root system user with its password.
- MariaDB 5 server has a root user with its password.
- MariaDB 10 server has a root user with its password.
Those you need identify properly, although you could use the same password everywhere for convenience. It's not the most secure solution of course. For MariaDB10, could there have been a typo along the way when using password?

Perhaps, as a last resort, uninstall MariaDB10 from the NAS, and re-install it with the correct password.

[themusj]

First. Thank you for bearing with me on this 'project.' I really appreciate it!!

Second. I sent the log to https://paste.kodi.tv/. Trust I did it right. A first.

Third. I think you are correct about all these accounts. Frankly, I don't have the correlation figured out. What am I logging into? At what point am I logging into the MariaDB or attempting to? Does Putty SSH right into the database using the same IP as the Snap?

• I set Qnap up with a static internal IP address from my router. I can log into it all day long with my new user account (upon setup) and the re-enabled admin account. 
  
• I can only login to phpMyadmin with root.
  
• Using Putty, I can only login with my new user account (upon setup) and the re-enabled admin account. IE, using the same internal IP address as my Qnap.
  
• Logging in with Putty with either my user account or admin, most commands do not run.
  • SHOW DATABASES, or CREATE USER for example. Message was like no such command exists. 
    • When I login with admin, I am given a menu of choices, and getting into the terminal, I have a # prompt.
      
    • When I login with my user account, I am right at a terminal with a $ prompt. I received a message about "We trust you have received the usual lecture from the local System Administrator.
      
• BTW, I restarted completely from scratch this morning.
  
• The log file I sent from Kodi was using root. I figured if that could connect, at least I am going in the right direction.