WXforum.net

Weather Software => Meteohub/Meteobridge => Topic started by: Larry29936 on February 03, 2019, 02:21:26 PM

Title: Can't connect to local MySQL server (SOLVED)
Post by: Larry29936 on February 03, 2019, 02:21:26 PM
I've configured my Mysql database but get the following error in Meteobridge when trying to configure a MySQL service using localhost as the host: Error: Failed to connect to MYSQL database: Can't connect to local MySQL server through socket '/tmp' (146). If I use the IP of my machine, I get a similar error: Error: Failed to connect to MYSQL database: Can't connect to MySQL server on '192.168.1.4' (146). I use the same ID and password whether I'm connecting via the terminal or Meteobridge.  I can connect to the database through the terminal, so I know it's running.

I used Steve Jenkins script to create both the database and table.

Any help is much appreciated.
Title: Re: Can't connect to local MySQL server
Post by: Theo on February 03, 2019, 02:44:24 PM
Are you sure mysql is running?  What operating system are you using?
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 03, 2019, 03:52:54 PM
Running linux Mint Mate. In the Terminal, I go into a MySQL shell and can run queries so I know it's running.
Title: Re: Can't connect to local MySQL server
Post by: freddie on February 03, 2019, 04:10:33 PM
Are you using the same user to access via the terminal as via Meteobridge? If not, it may be that the Meteobridge user has insufficient permissions. I'm assuming you have double-checked the username/password in the Meteobridge configuration.
Title: Re: Can't connect to local MySQL server
Post by: ConligWX on February 03, 2019, 04:15:08 PM
Is you database server on a different device? If so have you opened the default port of 3306?

Sent from my ONEPLUS A6003 using Tapatalk

Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 03, 2019, 04:19:12 PM
ConligWX, server is on the same device.

freddie, I'm using the same ID and password both places.

Incidentally, I can access the database through a GUI client as well as the MySQL shell in the terminal.

Also, there's a red X in front of the 'Host' box whether I use 'localhost' or the actual IP address of this machine. Do you think that being on a WiFi connection has anything to do with it? I wouldn't think so seeing as I can connect to the database using a SQL client.
Title: Re: Can't connect to local MySQL server
Post by: freddie on February 03, 2019, 04:35:30 PM
freddie, I'm using the same ID and password both places.
Okay, so are you using IP address or Unix socket to connect from the clients that work?
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 03, 2019, 04:38:36 PM
freddie, I'm using 'localhost' and the same port  on the client that works. Could this be a problem with meteobridge not working with MySQL 8.x?
Title: Re: Can't connect to local MySQL server
Post by: freddie on February 03, 2019, 04:58:13 PM
What happens if you try using 127.0.0.1 as IP address?
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 03, 2019, 05:06:35 PM
Basically the same error. "Can't connect to MySQL server on '127.0.0.1' (146)"
Title: Re: Can't connect to local MySQL server
Post by: freddie on February 03, 2019, 05:20:38 PM
Is your gui client connecting via unix socket or ip address?
Title: Re: Can't connect to local MySQL server
Post by: ConligWX on February 03, 2019, 05:21:54 PM
ok can you login to your sql server via ssh like so:

mysql -u username -p<enter> 

then enter your password. once that is done, type the following:

SHOW GRANTS FOR CURRENT_USER;

this will show what permissions you have for that user. post the output.
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 03, 2019, 06:03:45 PM
Here you go:

SHOW GRANTS FOR CURRENT_USER;

GRANT USAGE ON *.* TO 'larry'@'localhost' 
GRANT ALL PRIVILEGES ON `meteobridge`.* TO 'larry'@'localhost'

I read on another site that I need to allow remote access to the db for meteobridge to connect.
Title: Re: Can't connect to local MySQL server
Post by: ConligWX on February 03, 2019, 06:24:13 PM
I said before was the database server on a different device? If so have you opened the default port of 3306?

so it is on a different device.

so you need to enable remote access  on the mysql server (default port 3306)

config is usually: /etc/mysql/my.cnf

and look for Bind address entry. it is probabbly somthing like

bind-address = 127.0.0.1

just comment it out with a #

# bind-address = 127.0.0.1

save then restart mysql.

larry@localhost or larry@127.0.0.1 may not work even then since you are connecting via your nano ip address "remote" and not "local"

you'd then need to grant permissions via the ip address of the nano for user larry.
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 03, 2019, 06:41:58 PM
Simon, now I'm totally confused. The database is on the same machine as meteobridge. I created the database and users through the mysql shell on the same machine as meteobridge so I don't know why the user ID would indicate a remote.
Title: Re: Can't connect to local MySQL server
Post by: ConligWX on February 04, 2019, 02:01:05 AM


Quote from: Larry29936

I read on another site that I need to allow remote access to the db for meteobridge to connect.

This is why I said about setting up a remote connection.

So now you say you don't need a remote connection?

Sent from my ONEPLUS A6003 using Tapatalk

Title: Re: Can't connect to local MySQL server
Post by: freddie on February 04, 2019, 02:22:22 AM
The database is on the same machine as meteobridge. I created the database and users through the mysql shell on the same machine as meteobridge so I don't know why the user ID would indicate a remote.
I think it was your statement "I read on another site that I need to allow remote access to the db for meteobridge to connect." that prompted Simon to give that answer.

Okay, so the Meteobridge software is on the same host as your MySQL database.  There are two ways of connecting to a local database - Unix socket or TCP/IP socket.  One of those is obviously working, as you can connect to the database using the MySQL client and a GUI client.

I know next to nothing about Meteobridge.  I would suggest checking its configuration to see whether (a) it is using the same method of connection (unix or tcp/ip socket); (b) the credentials are correct; (c) there is no software on the host (firewall, etc.) that is preventing communication through the socket.
Title: Re: Can't connect to local MySQL server
Post by: Bushman on February 04, 2019, 11:55:07 AM
FWIW here is an excellent tutorial on connecting to SQL from MB:  https://www.stevejenkins.com/blog/2015/02/storing-weather-station-data-mysql-meteobridge/
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 05, 2019, 02:51:59 PM
Thanks for the reply, Bushman. I followed the link you suggested to initially set up everything but I'm still getting the "Error: Failed to connect to MYSQL database: Can't connect to local MySQL server through socket '/tmp' (146)". It's almost like the function was written for windoze, not for linux.
Title: Re: Can't connect to local MySQL server
Post by: freddie on February 05, 2019, 03:11:15 PM
I thought your socket name would be something like /tmp/mysql.sock rather than just /tmp.
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 05, 2019, 08:03:25 PM
freddie, that's why I think the function was written for Windows. In Linux, the mysqld.sock file is installed to the /var/run/mysqld folder.
Title: Re: Can't connect to local MySQL server
Post by: hymrog on February 06, 2019, 07:06:11 AM
Hello

Try this command mysql.server start  in the terminal. Also it would be helpful to know if you are running MySQL on LAMP or MAMP. Just curious of the steps/research you have taken to resolve

Is it possible to post a picture of your services screen from Meteobridge?  I have had no issues running MySQL in th past other than memory issues with the Meteobridge. One last thing, what do your error logs say?

g
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 06, 2019, 07:52:18 AM
hymrog, running Mysql on LAMP. I can connect to the database via both the terminal and a MySQL GUI client. Are you running on a Linux machine? The location where the error indicates it's looking for the .sock file indicates that the function might have been written for windows.

Here's a result of my privileges:

mysql> SHOW GRANTS for larry@localhost;
+----------------------------------------------------+
| Grants for larry@localhost                         |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'larry'@'localhost' |
+----------------------------------------------------+
1 row in set (0.04 sec)

Screenshot of Meteobridge is attached.

Larry
Title: Re: Can't connect to local MySQL server
Post by: galfert on February 06, 2019, 08:28:49 AM
Looks to me like the problem is that the Meteobridge is configured to use the Host of localhost as the location of the SQL database system. Your SQL database is on a different device than the Meteobridge. When you use localhost as a network device name that is like saying that device itself. It is a self reference. It is equivalent to using 127.0.0.1 which is a reserved address that means "this device." Every device, computer, system on a network is capable of referring to itself as localhost or 127.0.0.1. The Meteobridge needs the Host setting to be the IP address of your SQL server which should be the IP address of your Linux Mint system.

Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 06, 2019, 08:45:52 AM
galfert, the MySQL database is loaded on the same machine as I'm accessing meteobridge on. If I plug in the IP address of this machine instead of 'localhost' I get the following error:  Error: Failed to connect to MYSQL database: Can't connect to MySQL server on '192.168.1.4' (146). As I previously stated, I can connect to the database thru a GUI client and thru the terminal. Here are the connection parameters from the GUI client:

Address: localhost:3306
Database: meteobridge
URL: jdbc:mysql://localhost:3306/meteobridge
Server: MySQL[5.7.25-0ubuntu0.18.04.2
Title: Re: Can't connect to local MySQL server
Post by: galfert on February 06, 2019, 08:48:21 AM
I've reread th OP and see that you have also tested using 192.168.1.4 as the Host address. Another problem could be how you have connected the Linux system on the network. If you have the Meteobridge set to connect to the network via WiFi then you can't connect the Linux system via the Meteobridge's Ethernet port. Even though you may be able to reach the Linux system this way via another computer on the network the Meteobridge can't. The Meteobridge looks for other devices via its default gateway bound interface which is the WiFi. So be sure to not use the Ethernet port if the Meteobridge is connected via WiFi.
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 06, 2019, 09:03:27 AM
galfert, the meteobridge is hardwired into the router with a static address of 192.168.1.79. The machine that the database is on (192.168.1.4) is connected to the network via WiFi (in fact, all of my machines are). Could this be the problem?
Title: Re: Can't connect to local MySQL server
Post by: ConligWX on February 06, 2019, 09:27:55 AM
lol now I'm confused.  #-o

Quote
The database is on the same machine as meteobridge.

then

Quote
meteobridge is hardwired into the router with a static address of 192.168.1.79. The machine that the database is on (192.168.1.4)


anyway I replied here: https://forum.meteohub.de/viewtopic.php?f=56&t=13596#p27852
Title: Re: Can't connect to local MySQL server
Post by: freddie on February 06, 2019, 10:12:09 AM
the meteobridge is hardwired into the router with a static address of 192.168.1.79
So you need the following in your privileges table:

Code: [Select]
GRANT ALL PRIVILEGES ON *.* TO 'larry'@'192.168.1.79'
From what you have described, MySQL is remote from Meteobridge, so you need to allow connections from Meteobridge via MySQL privileges.
Title: Re: Can't connect to local MySQL server
Post by: hymrog on February 06, 2019, 11:54:35 AM
Hello -

First, try using the ip address of the machine that is running the mySQL server as your Host on meteobridge. and if that does not work change the user from "Larry" to Meteobridge.  Also if I recall my setup (on Linux) you will need to create and grant permission to user Meteobridge on your mySQL db. 

Personally, I would use  phpmyadmin to make changes, etc to your databases instead of the terminal.

g
Title: Re: Can't connect to local MySQL server
Post by: Larry29936 on February 06, 2019, 01:20:21 PM
I installed phpmyadmin but I get a 404 Not Found error in my browser
Title: Re: Can't connect to local MySQL server
Post by: ConligWX on February 06, 2019, 02:00:55 PM
myphpadmin requires a web server to run afaik.

Sent from my ONEPLUS A6003 using Tapatalk

Title: Re: Can't connect to local MySQL server
Post by: galfert on February 06, 2019, 02:14:32 PM
Another tip...you should test your SQL server from a different computer on the network. Don't just do it from within the Linux SQL server.

If it works from within the Linux SQL server that is great. That means services are running and SQL is up. But if the Meteobridge can't connect then you have either network problems, firewall or port problems, or credentials / permissions problems. That is why you should be testing from another computer client that can give you more information than what the Meteobridge error messages give you.