Author Topic: Having Difficulty connecting to a MySQL Server on a shared host  (Read 2753 times)

0 Members and 1 Guest are viewing this topic.

Offline SimonB

  • Contributor
  • ***
  • Posts: 139
    • meteo.capetown
Having Difficulty connecting to a MySQL Server on a shared host
« on: September 16, 2015, 11:18:56 AM »
I got the Meteobridge with the specific intention on uploading to a remote MySQL database.

I can log into the remote database using MySQL Workbench, so there's nothing wrong with regard to the remote servers privileges.

However, I am unable to get MeteoBridge to log into the server. I get the error "Error: Failed to connect to MYSQL database: Access denied for user 'elucXXXX_wx'@'105.X.X.76' (using password: YES) (no more tries):"

Now what it looks like is that the logon request being passed to MySQL is 'username', or username@'localIP', as opposed to 'username'@'hostIP'.

Now there's nothing wrong with the username or username@'localIP' as that is a valid userstring, but mySQL interprets it as a different user and denies login.  This causes difficulty for hosted solutions as often they don't have a way to create that user.

Is there any workaround from the meteobridge side?  Or am I going to have to resort to fighting with my hosting provider

If I cant get this to work I'm going to have to resort to other, more difficult hacks, and ditch the meteo.

Thanks



Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8605
    • Meteotemplate
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #1 on: September 16, 2015, 12:13:00 PM »
I got the Meteobridge with the specific intention on uploading to a remote MySQL database.

I can log into the remote database using MySQL Workbench, so there's nothing wrong with regard to the remote servers privileges.

However, I am unable to get MeteoBridge to log into the server. I get the error "Error: Failed to connect to MYSQL database: Access denied for user 'elucXXXX_wx'@'105.X.X.76' (using password: YES) (no more tries):"

Now what it looks like is that the logon request being passed to MySQL is 'username', or username@'localIP', as opposed to 'username'@'hostIP'.

Now there's nothing wrong with the username or username@'localIP' as that is a valid userstring, but mySQL interprets it as a different user and denies login.  This causes difficulty for hosted solutions as often they don't have a way to create that user.

Is there any workaround from the meteobridge side?  Or am I going to have to resort to fighting with my hosting provider

If I cant get this to work I'm going to have to resort to other, more difficult hacks, and ditch the meteo.

Thanks

Hi,

do you have a webhosting somewhere? I think the easiest thing to do would be making a very simple PHP script, where you can specify the username and pass and then in meteobridge use the HHTP Upload for this script instead of direct MySQL (btw. I have MB too and I remember long time ago, when I tried this it also did not work, because in case of my webhosting, Im not allowed mysql connection from external servers).

Offline SimonB

  • Contributor
  • ***
  • Posts: 139
    • meteo.capetown
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #2 on: September 16, 2015, 12:36:30 PM »
Hi,

do you have a webhosting somewhere? I think the easiest thing to do would be making a very simple PHP script, where you can specify the username and pass and then in meteobridge use the HHTP Upload for this script instead of direct MySQL (btw. I have MB too and I remember long time ago, when I tried this it also did not work, because in case of my webhosting, Im not allowed mysql connection from external servers).

Yeah it's shared hosting - they have disabled remote connections. So PHP looks like it's my only option.  Sigh, I'm not very good with PHP...

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8605
    • Meteotemplate
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #3 on: September 16, 2015, 01:06:53 PM »
Hi Simon,

absolutely no problem, I can write the script for you easily if you have hosting available.

Send me the MySQL query, I will make the script and then give you instructions how to use it and set up MB.

Offline SimonB

  • Contributor
  • ***
  • Posts: 139
    • meteo.capetown
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #4 on: September 16, 2015, 01:18:00 PM »
It's basically the same as Steve Jenkin's one, but I want to adapt it in various ways

I can log onto the database and execute SQL easily enough but I just need to work out how to pass the MB templates onto the php script.

Code: [Select]
INSERT INTO `databasename`.`tablename` (`ID`, `DateTime`, `TempOutCur`, `HumOutCur`, `PressCur`, `DewCur`, `HeatIdxCur`, `WindChillCur`, `TempInCur`, `HumInCur`, `WindSpeedCur`, `WindAvgSpeedCur`, `WindDirCur`, `WindDirCurEng`, `WindGust10`, `WindDirAvg10`, `WindDirAvg10Eng`, `RainRateCur`, `RainDay`, `RainYest`, `RainMonth`, `RainYear`) VALUES (NULL, '[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]', '[th0temp-act=F]', '[th0hum-act]', '[thb0seapress-act=inHg.2]', '[th0dew-act=F]', '[th0heatindex-act=F]', '[wind0chill-act=F]', '[thb0temp-act=F]', '[thb0hum-act]', '[wind0wind-act=mph]', '[wind0avgwind-act=mph]', '[wind0dir-act]', '[wind0dir-act=endir]', '[wind0wind-max10=mph]', '[wind0dir-avg10]', '[wind0dir-avg10=endir]', '[rain0rate-act=in.2]', '[rain0total-daysum=in.2]', '[rain0total-ydaysum=in.2]', '[rain0total-monthsum=in.2]', '[rain0total-yearsum=in.2]')

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8605
    • Meteotemplate
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #5 on: September 16, 2015, 01:35:51 PM »
OK, give me a minute and I will post here a script for you to upload to your server and info how to set it up in MB and we will see if it helps

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8605
    • Meteotemplate
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #6 on: September 16, 2015, 02:09:13 PM »
OK Simon, I cannot guarantee it will work for the first time as I couldnt test it, but try this:

First download the update.php script I am attaching. Open it in a text editor and at the top you can specify password (so that no-one can perform an update from your server, I set it now to 12345). Then fill in the db name, host, pass and login. Save and upload the script to your server somewhere.

Then in your MB, go to the Push Services tab and add a new HTTP request with the following URL:

Code: [Select]
http://www.yoursite.com/update.php?datetime=[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]&T=[th0temp-act=F]&H=[th0hum-act]&P=[thb0seapress-act=inHg.2]&D=[th0dew-act=F]&HI=[th0heatindex-act=F]&WC=[wind0chill-act=F]&Tin=[thb0temp-act=F]&Hin=[thb0hum-act]&W=[wind0wind-act=mph]&Wavg=[wind0avgwind-act=mph]&Wdir=[wind0dir-act]&WdirEng=[wind0dir-act=endir]&G=[wind0wind-max10=mph]&Wdir10=[wind0dir-avg10]&Wdir10eng=[wind0dir-avg10=endir]&RR=[rain0rate-act=in.2]&R=[rain0total-daysum=in.2]&Ryest=[rain0total-ydaysum=in.2]&Rmonth=[rain0total-monthsum=in.2]&Ryear=[rain0total-yearsum=in.2]&pass=12345
where you just have to change the "yoursite" at the beginning with whatever path you have to this script. And also notice the last parameter is the password you specified in the script.

Then set the interval and see if it works.... let me know and if there is some problem we can further work on it, as I said, I could not test it and there is lots of parameters etc. so I could make a typo somewhere along the way.

Offline SimonB

  • Contributor
  • ***
  • Posts: 139
    • meteo.capetown
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #7 on: September 16, 2015, 02:45:22 PM »
Awesome - thanks - now I understand how those parameters are passed.  I will work and test and modify as needed :)  But I have to do my paid work first :)

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8605
    • Meteotemplate
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #8 on: September 16, 2015, 02:59:51 PM »
LOL :)

Sure, have fun at work, I am still at work too :D But I finished my working hours about 2 hrs ago, but I usually stay here after hours and work on my own stuff, because my laptop at home is very slow, so it is better for me to work here :-)

I will go home soon however, but I will be online for at least another 4-5 hours later tonight when I get home, so let me know and I will help you if necessary.

Offline SimonB

  • Contributor
  • ***
  • Posts: 139
    • meteo.capetown
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #9 on: September 17, 2015, 04:42:01 AM »
Ok I've got it working and uploading data.

My only issues are that the following values are not uploading

  • wind0dir-act=endir
  • wind0dir-avg10=endir

Does this mean that my PWS (AW-1400-IP) does not support these parameters and not providing it to the MB?

Then these are reported as 0.00

  • rain0total-daysum
  • rain0total-monthsum
  • rain0total-yearsum

But they are there in my PWS.

EDIT: Ok it seems that the MB is not looking at the cumulated figures held by the PWS, and stores this on it's own, and loses the data on a power cycle.


« Last Edit: September 17, 2015, 04:51:32 AM by SimonB »

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8605
    • Meteotemplate
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #10 on: September 17, 2015, 05:01:12 AM »
Hi Simon,

with regards to the precipitation sums, these are not taken from your station I think. MB has its own remote server where it saves this (so it is not lost even during restart), but if you have just started logging these are still zero.

Otherwise Im glad the script worked and that you are able to update your db :-) Let me know if you ever need something.

Jachym

Offline SimonB

  • Contributor
  • ***
  • Posts: 139
    • meteo.capetown
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #11 on: September 17, 2015, 05:11:10 AM »
Hi Simon,

with regards to the precipitation sums, these are not taken from your station I think. MB has its own remote server where it saves this (so it is not lost even during restart), but if you have just started logging these are still zero.

Otherwise Im glad the script worked and that you are able to update your db :-) Let me know if you ever need something.

Jachym

Many thanks - I will do - I am stealing ideas from your templates :)

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8605
    • Meteotemplate
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #12 on: September 17, 2015, 05:41:54 AM »
Why not installing the whole thing then? :-)

Offline fartaritx

  • Member
  • *
  • Posts: 1
Re: Having Difficulty connecting to a MySQL Server on a shared host
« Reply #13 on: April 14, 2018, 03:45:20 PM »
I am having the same problem and although the post is already 3 years old the MB software is still not supporting database on a remote server it seem.
If anybody has a solution or workaround that is working I like to hear abut that.

 

anything