MySQL and FTP usernames

[HansR]

@HansR true - I hadn't realised the database was on the same host. Even so, I would do a database process restart rather than a reboot. Also, the old username was being logged in the MX log file, so will be obtained from MX config rather than the database.

@freddie: true, good point. Nevertheless in case of problems I almost automatically reset the whole system before pointing with the finger to the guilty ones.

[billy]

@HansR true - I hadn't realised the database was on the same host. Even so, I would do a database process restart rather than a reboot. Also, the old username was being logged in the MX log file, so will be obtained from MX config rather than the database.

Just t be clear (because I can get confused by some terminology) the database is on the same (remote) host as the web server - at a Siteground facility in Singapore. I'm in Perth Western Australia.

[freddie]

@billy is the database on the same host as CumulusMX? I ask because the error message you posted earlier showed user tillyspa@localhost - which suggests MX and the database are on the same host.

Having said that - I don't think I've seen that particular error message before (the "definer" bit), so perhaps you would like to zip up your MXdiags file and attach to a reply on this thread?

[HansR]

Just t be clear (because I can get confused by some terminology) the database is on the same (remote) host as the web server - at a Siteground facility in Singapore. I'm in Perth Western Australia.

Well, that seems to be clear it is not on the localhost. That is interesting because the call you indicated appears to be to the localhost.
Interesting! You might want to ask yourself: is it an error at all?

[HansR]

Indeed: what @freddie says the MXdiags.

[billy]

OK here is the mxdiag file after a startup I have just done (including a reboot). The monthly log update interval is 5 minutes, and as you will see it fails at 20:40 (near end of diag file). The custom mysql update of the realtime file proceeded correctly, and this morning the dayfile was updated correctly.

[HansR]

@billy:
Well the MXdiags in combination with the code shows that the error comes from creating a new SqlConnection object so it is BEFORE even trying to connect. There are only two places where the connection id is set.

I think it is better Mark looks at this.

But your 'tillyspa'@'localhost' must come from some setting somewhere. In the mean time you could try a find it on the system (from the CumulusMX directory):

Code: Select all

grep -rnw './' -e 'tillyspa'

[billy]

But your 'tillyspa'@'localhost' must come from some setting somewhere. In the mean time you could try a find it on the system (from the CumulusMX directory):

Code: Select all

grep -rnw './' -e 'tillyspa'

Thanks - nothing unexpected - it reports it occurs in (a) recent mxdiags files ; (b) an old ftplog.txt - because initially after the siteground upgrade I simply used the previous username ; and for the same reason (c) in previous Cumulus.ini files in the daily backups .

The positive in this is you have taught me how to search in multiple files in Linux - thanks

[HansR]

The positive in this is you have taught me how to search in multiple files in Linux - thanks

You're welcome

You could also try to find it anywhere on the system: so as user root you use the same command starting in /
Although it would surprise me but you never know.

[freddie]

I've just been googling the error message. It is an error that comes from mysql and is to do with database internals not being incorrect (typically if you use a "view" and the definer of the view - the user that created it - no longer exists). It can occur when you migrate your database using mysqldump.

One way around it is to add the missing user, and grant it appropriate privileges. I think that is overkill for Cumulus databases where you can recreate tables any time from the contents of your data files.

The simplest way around it is to drop the table, and use the ExportMySQL utility to recreate it.

Shut down Cumulus, and then drop your Feb 2021 table. You will need to do this through your cPanel interface.

Then open a terminal window, cd to your Cumulus installation and issue the following command.

Code: Select all

sudo mono ExportMySQL.exe data/Feb21log.txt

Once it has completed then restart Cumulus.

[billy]

The problem is solved - thanks to Freddie and Hans. I had guessed this was a CMX problem, but it wasn't . Freddie's suggestion that it was the MySQL database/table itself causing the problem was spot-on, and it makes sense - with the benefit of hind-site - because the other uploads were working fine. The specific reason was I have a trigger in my Monthly table which has a "Definer" that was tillyspa@localhost. I had never noticed this before (this is the only trigger I have). I simply deleted that box and the system automatically replaced it with another username@localhost and bingo - the uploads started working (the username inserted is a scrambled set of letters and digits that make no sense to me ).

The trigger, incidentally, activates after each insert into the monthly table and calculates and stores, in another table, the hourly rainfall, sunshine hours and insolation. This gives the data for the "Drill Down" tabs for these measures that you can see here https://tillyspaws.com/vp2waw/graphs_historic.php. This is another treasure borrowed from Mark Crossley's Wilmslow site .

So, once again to Hans and Freddie for being so helpful. I would not have got there without your guidance

[HansR]

Good.
Have to start using a database sometime...
Just to have problems i never knew

[billy]

I came to MySQL as a complete novice (along with weather station, linux, rpi, php etc etc) only a handful of years ago, and I am still one. But the database side was pretty straightforward, and it does provide a great way to extract and utilize the weather station data. The sql language is a bit tedious and limiting but I bumble along. Most importantly, the problem of the last couple of days was the only significant issue I've had or at least that I remember - maybe that's the problem . This infrequency in having problems means I have to reinvent the wheel - or more correctly, my understanding of how the wheel works - every now and then. If the problems were more frequent maybe I would learn and retain more .