This project has moved. For the latest updates, please go here.

MySql Database configure

May 17, 2011 at 9:56 AM

Hi, I get an issue when I set mySql database in openPdc. 

Following the instructions from the guide, I set a MySQL database and set as  primary historian. I set in the historian screen :

Name Type            == MySqlAdapters.MySqlOutputAdapter
Assembly Name   == MySqlAdapters.dll
Connection String  == Database= OpenPdc; UID = MyID; Pwd = MyPwd

The database is built properly and I can see it in mysql, I can connect to the database through openpdc, But The error

"Column Count Does not match value count at row 1"

is always printed on screen openpdc console. This prevents me from any otheroperation.
Suggestions about this?

Thank

Developer
May 18, 2011 at 1:24 PM

Hi,

I have seen such a error before,

It is a general mysql error, if you search the net there is a good explanation for it, you can trace it to the code, i remember doing that, basically what happens is that it does not recognize the deliminator separating the values in the insert statement (in the MySQLAdapters.dll). This actually could be due to localization settings on your system. i.e. if you system your a comma for separation decimals instead of a dot, then the insert statement processes that comma as the end of the value and the decimal part as being a new value. you end up with more values that columns. e.g. 49.50 is ok, but if you have 49,50 then 49 is a value to insert and 50 is another value to insert.

Now what you could do, is to modify your table so that all columns are of datatype varchar (or its equivilant in mysql, sorry i am using many databases and i forget/get confused or the datatypes in different brands, but i think it is so in mySQL.).

I did that and it worked. you could try it.

 

Good Luck

 

May 18, 2011 at 3:56 PM

Hi, thankyou for the suggestion... 
Unfortunatly still dont working!.. I dont know if i am not able to fix it or other anyway I have tried to do a different way. Instead of put Mysql as primary historian, i put ADO as well, and it work in mysql interface. . ADO is more flexible and more detailed ..  
But my big question right now is, where I can see the data?? i mean the real data of measurements?? You provide this constructor Database (openpdc.sql) of openpdc, very descriptive of the system,of PMUs, and of related phasor available etc. .. but where are the real data??

I have to build a table alone or in theory the data should go somewhere in your database??
I tried to build a table like :


CREATE TABLE Data(
SignalID NCHAR(36) NULL,
Timestamp BIGINT NOT NULL DEFAULT 1,
Value DOUBLE NOT NULL,
Tag NCHAR(250) NULL DEFAULT 1,   
Time NCHAR(250) NOT NULL DEFAULT '00.00.00',   
TimestampQualityIsGood TINYINT NOT NULL DEFAULT 0,   
ValueQualityIsGood TINYINT NOT NULL DEFAULT 0
);


 Apart from the Mysql language, what i can see is that le query VALUE and the query TIME contains some data, VALUE is printing in random order, TIME print the actual time of the single package received..  Is this correct? Should I  build the table in a different way?
Thanku for the attention
Hope to have some more suggestions

Developer
May 18, 2011 at 5:13 PM

Hi,

Ohh I  had assumed you set up the mySQL database using this script http://openpdc.codeplex.com/SourceControl/changeset/view/66759#561057

The openPDC.sql, is the database for the system in general and does not include the actual measurements from remote devices, but rather the configuration information and metadata on the devices and the signals they send. 

If you are looking for the data, the data is stored in an efficient manner by the historian, if you have set up a standard default installation and used the in-process Historian then the data can be found Archive folder in the root directory.  i.e. if you openPDC is installed in C:\Program Files\OpenPDC then the data stored from your devices should be stored in : C:\Program Files\OpenPDC\Archive\. to extract the data and manipulate it with third party tools you can using the Historian Playback Utility which is included in the installations package and you can find it in the openPDC folder in the Start Menu.

The other way you can get the data (in real time) is either by subscribing to it (see: http://openpdc.codeplex.com/wikipage?title=Data%20Access%20Options&referringTitle=Documentation ), or by connecting to the PDC system using for example the PMU connection Tester.

Now I have found the MySQL adapters to be the easiest way to set up a RDBMS based historian, even though the database method is actually not efficient and you will lose data if you do not optimize you system. So if you still want to go for the database solution try the MySQL system again according to the link I provided.

I have not yet tried the ADO adapters but i guess you may need a connection string somewhere to specify which measurements you want to archive see http://openpdc.codeplex.com/wikipage?title=Connection%20Strings&referringTitle=Documentation#AdoOutputAdapter.


Good Luck

 

 

Coordinator
May 18, 2011 at 6:47 PM

You can see this thread for more information on the AdoOutputAdapter:

http://openpdc.codeplex.com/discussions/255282

Thanks!
Ritchie

May 26, 2011 at 4:28 PM

Thanks for the advice you gave me , were helpful. Now I can connect and configure mydatabase properly.

Sincerly