Author Topic: Can't connect to local MySQL server (SOLVED)  (Read 3536 times)

0 Members and 2 Guests are viewing this topic.

Offline Larry29936

  • Member
  • *
  • Posts: 16
Can't connect to local MySQL server (SOLVED)
« 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.
« Last Edit: February 06, 2019, 06:47:14 PM by Larry29936 »

Offline Theo

  • Senior Member
  • **
  • Posts: 80
Re: Can't connect to local MySQL server
« Reply #1 on: February 03, 2019, 02:44:24 PM »
Are you sure mysql is running?  What operating system are you using?

Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #2 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.

Offline freddie

  • Member
  • *
  • Posts: 24
    • Alcaston Weather
Re: Can't connect to local MySQL server
« Reply #3 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.
Freddie

Offline ConligWX

  • Forecaster
  • *****
  • Posts: 836
  • #conligwx
    • conligwx.org
Re: Can't connect to local MySQL server
« Reply #4 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

Regards Simon
Davis Vantage Pro2 Plus (6162UK) • Daytime FARS • WeatherLink Live • AirLink • PurpleAir PA-II-SD • CumulusMX •


Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #5 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.
« Last Edit: February 03, 2019, 04:35:39 PM by Larry29936 »

Offline freddie

  • Member
  • *
  • Posts: 24
    • Alcaston Weather
Re: Can't connect to local MySQL server
« Reply #6 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?
Freddie

Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #7 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?
« Last Edit: February 03, 2019, 04:52:16 PM by Larry29936 »

Offline freddie

  • Member
  • *
  • Posts: 24
    • Alcaston Weather
Re: Can't connect to local MySQL server
« Reply #8 on: February 03, 2019, 04:58:13 PM »
What happens if you try using 127.0.0.1 as IP address?
Freddie

Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #9 on: February 03, 2019, 05:06:35 PM »
Basically the same error. "Can't connect to MySQL server on '127.0.0.1' (146)"

Offline freddie

  • Member
  • *
  • Posts: 24
    • Alcaston Weather
Re: Can't connect to local MySQL server
« Reply #10 on: February 03, 2019, 05:20:38 PM »
Is your gui client connecting via unix socket or ip address?
Freddie

Offline ConligWX

  • Forecaster
  • *****
  • Posts: 836
  • #conligwx
    • conligwx.org
Re: Can't connect to local MySQL server
« Reply #11 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.
Regards Simon
Davis Vantage Pro2 Plus (6162UK) • Daytime FARS • WeatherLink Live • AirLink • PurpleAir PA-II-SD • CumulusMX •


Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #12 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.

Offline ConligWX

  • Forecaster
  • *****
  • Posts: 836
  • #conligwx
    • conligwx.org
Re: Can't connect to local MySQL server
« Reply #13 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.
« Last Edit: February 03, 2019, 06:28:26 PM by ConligWX »
Regards Simon
Davis Vantage Pro2 Plus (6162UK) • Daytime FARS • WeatherLink Live • AirLink • PurpleAir PA-II-SD • CumulusMX •


Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #14 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.

Offline ConligWX

  • Forecaster
  • *****
  • Posts: 836
  • #conligwx
    • conligwx.org
Re: Can't connect to local MySQL server
« Reply #15 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

Regards Simon
Davis Vantage Pro2 Plus (6162UK) • Daytime FARS • WeatherLink Live • AirLink • PurpleAir PA-II-SD • CumulusMX •


Offline freddie

  • Member
  • *
  • Posts: 24
    • Alcaston Weather
Re: Can't connect to local MySQL server
« Reply #16 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.
Freddie

Offline Bushman

  • Forecaster
  • *****
  • Posts: 7549
    • Eagle Bay Weather
Re: Can't connect to local MySQL server
« Reply #17 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/
Need low cost IP monitoring?  http://wirelesstag.net/wta.aspx?link=NisJxz6FhUa4V67/cwCRWA or PM me for 50% off Wirelesstags!!

Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #18 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.

Offline freddie

  • Member
  • *
  • Posts: 24
    • Alcaston Weather
Re: Can't connect to local MySQL server
« Reply #19 on: February 05, 2019, 03:11:15 PM »
I thought your socket name would be something like /tmp/mysql.sock rather than just /tmp.
Freddie

Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #20 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.

Offline hymrog

  • Senior Member
  • **
  • Posts: 52
    • Gahanna Weather
Re: Can't connect to local MySQL server
« Reply #21 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

Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #22 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
« Last Edit: February 06, 2019, 08:16:40 AM by Larry29936 »

Offline galfert

  • Global Moderator
  • Forecaster
  • *****
  • Posts: 6822
Re: Can't connect to local MySQL server
« Reply #23 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.

« Last Edit: February 06, 2019, 08:40:48 AM by galfert »
Ecowitt GW1000 | Meteobridge on Raspberry Pi
WU: KFLWINTE111  |  PWSweather: KFLWINTE111
CWOP: FW3708  |  AWEKAS: 14814
Windy: pws-f075acbe
Weather Underground Issue Tracking
Tele-Pole

Offline Larry29936

  • Member
  • *
  • Posts: 16
Re: Can't connect to local MySQL server
« Reply #24 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
« Last Edit: February 06, 2019, 08:51:52 AM by Larry29936 »

 

anything