HVAC-Talk: Heating, Air & Refrigeration Discussion banner
Cancel
Create thread New Forums

ALC Webctrl database upgrade

Tags
alc automated logic database sql sql database webctrl
Jump to Latest
1 - 20 of 21 Posts
A

alliedans

· Registered
Joined
·
2 Posts
Discussion starter · #1 ·
We have to upgrade WEBCTRL4.1 to 8.5 version . but since the database is 55 GB(Might be a corrupt ibdata file ) which is very large in MySQL free version. Please advise how to proceed for upgradation as it might take days to convert this large database .

Also please advice any method with which we can reduce this size or change this ibdata1 corrupt file as it take 5-6 hours to take backup of the database
 

Attachments

#2 ·
The free version of MySQL does not optimize the db online. You must shut down WC and run that yourself. If you've never done it there could be huge size reductions there.

"C:\Program Files\MysQL\MySQL Server 5.6\bin\mysqlcheck.exe" -o -A -s -u ***** -p*****

Or if you dump the DB and start with new that will also get space back. You basically should do that between major version upgrades anyway, or at least we do as it's easier to ship something already set up right and import customer data.
 
#3 ·
You're screwing yourself because trends db structure changed and you should run a trends upgrade utility first.

I would look to eliminate MySQL and move migrate to postresql. Coming from WebCTRL 4.1. My first step would be to replicate that site without trends, audit, or alarms db. Main only. He will be bailout. ie., I will turn to this guy when I get mad and want to gtfo of dodge. My second step would be replicate a second (replicant2) including all databases because I don't don't know how tom, john or jerry setup mysql.

I'd then upgrade my replicant(s) to WebCTRL6.1 or 6.5(?) and then create another replicant with derby databases.

Then upgrade that 6.1 or 6.5(?) derby replicant to WebCTRL8.5. Then finally replicate over to postgresql if I need good trustworthy trends.
 
#5 ·
I hear ya. I can't remember what I did yesterday. Whatever it was I'm sure the mrs will remind me I was in the wrong.

fwiw I'm sure his upgrade will be successful if he walks away and let it run.

fwiw Wrestling with mysql and mariadb taught me there's less disappointment(s) replicating to derby and operating from there.
 
#7 ·
Cool breeze 38 said:
delete the trend files to reduce the DB size.
Click to expand...
If only our customers would be so accommodating. Spent the last day or so working with a single trend that's 2.7GB on disk and crashes WebCTRL out of memory when you try to view it. Customer wants it so we are working with support to figure out if it's possible. It wasn't set up right and had a low COV. Also thinking about third party tools to dump it if it comes to that.
 
#8 ·
T Brewer said:
I would look to eliminate MySQL and move migrate to postresql. Coming from WebCTRL 4.1. My first step would be to replicate that site without trends, audit, or alarms db. Main only. He will be bailout. ie., I will turn to this guy when I get mad and want to gtfo of dodge. My second step would be replicate a second (replicant2) including all databases because I don't don't know how tom, john or jerry setup mysql.

I'd then upgrade my replicant(s) to WebCTRL6.1 or 6.5(?) and then create another replicant with derby databases.

Then upgrade that 6.1 or 6.5(?) derby replicant to WebCTRL8.5. Then finally replicate over to postgresql if I need good trustworthy trends.
Click to expand...
This reminds me of the quote that basically says, when you pay me $200/hr, you are not paying for the hour, you are paying for the years it took to get good enough to do this.

There is a story that involves blood and tears behind every step
 
#9 ·
fwiw I make a stop at 6.1 because before then default db was access. 6.1 supported both access and derby without windows complaining so, for me, it's an easy stop to get it off of access and over to derby. Yeah I like portable databases. Especially for recovery. Doesn't mean I don't backup psq and mysql dumps. I just prefer to keep a derby copy somewhere near.
 
#10 ·
MaxBurn said:
If only our customers would be so accommodating. Spent the last day or so working with a single trend that's 2.7GB on disk and crashes WebCTRL out of memory when you try to view it. Customer wants it so we are working with support to figure out if it's possible. It wasn't set up right and had a low COV. Also thinking about third party tools to dump it if it comes to that.
Click to expand...
What can be so important about that trend?
 
#12 ·
>Energy usage data is important to energy managers. Part of all our systems is data collection and analytics.

I'm sure you're miles a head of this suggestion but it might be helpful for the next person.

I want to say you can leverage report manager as manual backup of sorts. Max value for any date range likely is what your after. Then use an alarm action to write that report to a file and schedule/cron a task to copy that file. cp MeterReport.csv vars_-_$(date +%F). Might never need those files. But hard drive space is cheap.
 
#15 ·
T Brewer said:
If WebCTRL can't attach a report in an alarm action then you can always schedule/cron a task to email with attachment in powershell or BASH.
Click to expand...
This particular customer, we've presented a variety of solutions, they've waffled and "we'll get back to you" forever.

The report idea is helpful though, the trend graph on the front end seems to do a massive SQL select statement that then crashes WebCTRL as that's delivered and runs the application out of memory. If we can limit that in scope we can export sections of it. That has me thinking now, I need to pose ALC support some questions.
 
#16 ·
You can make it an SQL problem only. datasources table provides the source_path and data_series_table. Each row in the data_series_table provides start & end times but I haven't figured how to cast the aggregation_buffer. SELECT CAST (aggregation_buffer AS text) FROM dataseries_159 does something but am expecting BYTEA_FORMAT to be more revealing. If I get the formatting correct. Point a database guy at it or maybe ask ALC themselves. It'd be the first time tech support answered a question with some meat on it.
 
#17 ·
T Brewer said:
You can make it an SQL problem only. datasources table provides the source_path and data_series_table. Each row in the data_series_table provides start & end times but I haven't figured how to cast the aggregation_buffer. SELECT CAST (aggregation_buffer AS text) FROM dataseries_159 does something but am expecting BYTEA_FORMAT to be more revealing. If I get the formatting correct. Point a database guy at it or maybe ask ALC themselves. It'd be the first time tech support answered a question with some meat on it.
Click to expand...
ALC has historically shunned any question relating to db direct access, they don't want that. As I understand it post 6.5 it went to blob data or something which made it hard for outside entities to read but is way faster, I'm not a dba.

I've been working with support all week, and as of a couple days ago they have the entire system to tinker with.

Yesterday they felt confident the trend export addon (paid) would be able to handle this. I ran 1 day and 30 days successfully, but "all" brought the system down again. Additionally there's various intervals back from NOW, but no "do this particular month". So I might conceivably do a month at a time and walk that back without running out of resources but the utility front end doesn't support it.
 
#19 ·
numbawunfela said:
A 2.7 GB trend in 1 month intervals... that is a lot of clicking.
Click to expand...
Can’t even do it, the selection for a small amount of trends is like day, week, month, all. No provision to export a month in time. Dumb. It’s only about fourteen months, not that much clicking really.

Besides, there’s now a tariff on that, so working with CSV is a little expensive these days.

Image
 
#20 ·
>No provision to export a month in time.

I'm missing something obvious. If you have trend data then I expect you have enough there to work with. Report Manager --> Date Range (previous 1-31 Days) --> Frequency (every 15min or every hour).
Question becomes; am I looking for last value, first value or max value to capture what I'm after and then is the date off by +-1 if I work the data across columns in Report Manager. I would suck reassembling csv 1 day a time though. A 2 week interval would suck but not the end of the word (28-30 files).
 
1 - 20 of 21 Posts
You have insufficient privileges to reply here.
About this Discussion
20 Replies
5 Participants
Last post:
MaxBurn
HVAC-Talk: Heating, Air & Refrigeration Discussion
posts
5M
members
275K
Since
2000
The #1 HVACR Community
Full Forum Listing
Explore Our Forums
AOP Residential HVAC/Refrigeration(Beenthere Zone) General Discussion Tech to Tech Chat - Residential Controls Refrigeration & Ice Making