MySQL Database setup for Stand Alone Dashboard

Post any 3rd party software here.
shortbg
Posts: 4
Joined: Mon Jan 26, 2015 9:51 pm

MySQL Database setup for Stand Alone Dashboard

Post by shortbg » Sun Mar 22, 2015 2:40 pm

Trying to run Stand Alone Dashboard using MySQL database instead of SQLite. I created the database and put all parameters in the config file, database.php. I would have expected the database tables, etc. to get initialized automatically when I add the device. This did not happen. So I dumped the clean SQLite db file and converted it to a MySQL formatted file and imported it to initialize my database. I was then able to add the device but not all fields were created. And, it won't log the data. I'm assuming there is a problem with my database tables because of the way I initialized it. Was there a setup step that I missed that would have initialized the database correctly?
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Mon Mar 23, 2015 9:37 am

shortbg wrote:Trying to run Stand Alone Dashboard using MySQL database instead of SQLite. I created the database and put all parameters in the config file, database.php. I would have expected the database tables, etc. to get initialized automatically when I add the device. This did not happen. So I dumped the clean SQLite db file and converted it to a MySQL formatted file and imported it to initialize my database. I was then able to add the device but not all fields were created. And, it won't log the data. I'm assuming there is a problem with my database tables because of the way I initialized it. Was there a setup step that I missed that would have initialized the database correctly?
The problem is likely the functions in the query calls. MySQL does not support strftime.

http://stackoverflow.com/questions/2436 ... t-to-mysql
Ben
Brultech Research Inc.
E: ben(at)brultech.com
jcowens
Posts: 5
Joined: Mon Jul 20, 2015 2:24 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by jcowens » Mon Jul 20, 2015 4:01 pm

Any luck getting this to work with mysql?

I've done the conversion per the above post, but can't get any data to log.
I have the dashboard connecting to mysql and creating the entry for the GEM but it stops there. I don't get any raw data coming through.

I get a mysql syntax error about minutedata at line 1, I've not been able to track down where this is coming from.

Any hints as to where I should look?
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Tue Jul 21, 2015 10:41 am

jcowens wrote:Any luck getting this to work with mysql?

I've done the conversion per the above post, but can't get any data to log.
I have the dashboard connecting to mysql and creating the entry for the GEM but it stops there. I don't get any raw data coming through.

I get a mysql syntax error about minutedata at line 1, I've not been able to track down where this is coming from.

Any hints as to where I should look?
Any better errors in the btech/logs folder? You can also check the MySQL log files.

It's likely some sort of unsupported function.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
jcowens
Posts: 5
Joined: Mon Jul 20, 2015 2:24 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by jcowens » Fri Jul 24, 2015 11:09 am

Making some progress. I've got it connecting to mysql and logging the data.
The data is not correct, but I think its just a matter of updating the sql statements.

There are 2 tables in the sqlite database "sqlite_sequence" and "sqlite_stat1" are these unique to sqlite or will they be needed with mysql?
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Fri Jul 24, 2015 11:15 am

jcowens wrote:Making some progress. I've got it connecting to mysql and logging the data.
The data is not correct, but I think its just a matter of updating the sql statements.

There are 2 tables in the sqlite database "sqlite_sequence" and "sqlite_stat1" are these unique to sqlite or will they be needed with mysql?
Those are unique to SQLite.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
jcowens
Posts: 5
Joined: Mon Jul 20, 2015 2:24 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by jcowens » Wed Jul 29, 2015 3:34 pm

I have this working with mysql
It's been running for over 24 hours and every thing looks ok. I've tested everything except add-ons (not sure what that's for) and it all works as it did with sqlite.

If your ok with it I will upload the dashboard package and sql script to create the database.
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Thu Jul 30, 2015 9:40 am

jcowens wrote:I have this working with mysql
It's been running for over 24 hours and every thing looks ok. I've tested everything except add-ons (not sure what that's for) and it all works as it did with sqlite.

If your ok with it I will upload the dashboard package and sql script to create the database.
Addons is for forwarding to services like SmartEnergyGroups.

Yeah, go ahead with the dashboard package.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
jcowens
Posts: 5
Joined: Mon Jul 20, 2015 2:24 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by jcowens » Thu Jul 30, 2015 1:03 pm

Here is the Standalone Dashboard that uses mysql.

Before running the dashboard, execute the sql script to create the database.
In the database config file set the database host name, user name and password.

I run Mariadb so the sql script was generated from that and uses the aria engine.
If you use mysql you will have to change the engine to what ever you prefer.

I don't have much time to do support so it is pretty much as is, but I will answer question if I can.
Attachments
Standalone_Dashboard_MySQL.zip
(4.58 MiB) Downloaded 535 times
dashboard-create-mysqldb.sql.zip
(1.28 KiB) Downloaded 539 times
shortwh
Posts: 25
Joined: Tue Jan 06, 2015 1:18 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortwh » Mon Aug 03, 2015 5:44 pm

How is it going with MYSQL? shortbg ended up leaving the database as sqlite but now that 6months have gone by the database is huge and entering pages is very slow. We are still wondering if the MYSQL route would make the usage with large databases better. Also, it seems that there is a lot of file IO on SQLITE version for GEM pushes.
Post Reply