[HELP-9149] [fiware-stackoverflow] Cygnus MySQL sink error (attr_persistence = column) - JIRA

Details

Type: Monitor
Status: Closed
Priority: Major
Resolution: Done
Affects Version/s: None
Fix Version/s: 2021
Component/s: FIWARE-TECH-HELP
Labels:
- fiware
- fiware-cygnus

Source URL:
https://stackoverflow.com/questions/31937580/cygnus-mysql-sink-error-attr-persistence-column
HD-Chapter:
Data
HD-Enabler:
Cosmos

Description

Created question in FIWARE Q/A platform on 11-08-2015 at 11:08
Please, ANSWER this question AT https://stackoverflow.com/questions/31937580/cygnus-mysql-sink-error-attr-persistence-column

Question:
Cygnus MySQL sink error (attr_persistence = column)

Description:
I'm using cygnus to store data in both MySQL Database and Cosmos.
Storing the data in cosmos works ok, but when storing in MySQL, especifically with the attribute "cygnusagent.sinks.mysql-sink.attr_persistence = column" instead of "cygnusagent.sinks.mysql-sink.attr_persistence = row" the data is not stored and I'm getting some errors in flume log.

As the table needs to be created previously when using the column attribute(more info : https://github.com/telefonicaid/fiware-cygnus/blob/master/doc/design/OrionMySQLSink.md#important-notes-regarding-the-persistence-mode), I create the table:

CREATE TABLE def_servpath_sensorreading4_sensorreading(systemid int,value float, sensorid int, nodeid int);

DESCRIBE def_servpath_sensorreading4_sensorreading(systemid int,value float, sensorid int, nodeid int);
mysql> DESCRIBE def_servpath_sensorreading4_sensorreading;
------------------------------------+

Field

Type

Null

Key

Default

Extra

------------------------------------+

systemid	int(11)	YES	NULL
value	float	YES	NULL
sensorid	int(11)	YES	NULL
nodeid	int(11)	YES	NULL

------------------------------------+
4 rows in set (0.00 sec)

This is the flume log:

11 Aug 2015 11:40:31,977 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionMySQLSink.persist:240) - [mysql-sink] Persisting data at OrionMySQLSink. Database: def_serv, Table: def_servpath_sensorreading3_sensorreading, Timestamp: 2015-08-11T08:40:31.969, Data (attrs):

{systemid=1, value=29.2, sensorid=2, nodeid=1}

, (metadata):

{sensorid_md=[], systemid_md=[], value_md=[], nodeid_md=[]}

11 Aug 2015 11:40:31,978 WARN [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:182) - Bad context data (Unknown column 'systemid' in 'field list')
11 Aug 2015 11:40:31,978 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:193) - Finishing transaction (1439278647-655-0000000005)
11 Aug 2015 11:40:32,369 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionHDFSSink.persist:356) - [hdfs-sink] Persisting data at OrionHDFSSink. HDFS file (def_serv/def_servpath/sensorreading3_sensorreading/sensorreading3_sensorreading.txt), Data (

{"recvTime":"2015-08-11T08:40:31.969Z","nodeid":"1", "nodeid_md":[],"sensorid":"2", "sensorid_md":[],"systemid":"1", "systemid_md":[],"value":"29.2", "value_md":[]}

)
11 Aug 2015 11:40:32,858 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:193) - Finishing transaction (1439278647-655-0000000005)

Where im getting the following error:

Bad context data (Unknown column 'systemid' in 'field list')

Do I need to create any more columns in the table related with the metadata? Am I creating the table correctly?

Thanks in advance.

Activity

Ascending order - Click to sort in descending order

Backlog Manager created issue - 22/May/17 9:11 PM

Hide

Permalink

Backlog Manager added a comment - 22/May/17 9:11 PM

2017-05-22 21:13|CREATED monitor | # answers= 1, accepted answer= True

Show

Backlog Manager added a comment - 22/May/17 9:11 PM 2017-05-22 21:13|CREATED monitor | # answers= 1, accepted answer= True

Backlog Manager made changes - 22/May/17 9:11 PM

Field	Original Value	New Value
Component/s		FIWARE-TECH-HELP [ 10278 ]

Backlog Manager made changes - 23/May/17 12:04 AM

Status

Open [ 1 ]

In Progress [ 3 ]

Hide

Permalink

Backlog Manager added a comment - 23/May/17 12:04 AM

2017-05-23 00:05|UPDATED status: transition Answer| # answers= 1, accepted answer= True

Show

Backlog Manager added a comment - 23/May/17 12:04 AM 2017-05-23 00:05|UPDATED status: transition Answer| # answers= 1, accepted answer= True

Backlog Manager made changes - 23/May/17 3:04 AM

Status

In Progress [ 3 ]

Answered [ 10104 ]

Hide

Permalink

Backlog Manager added a comment - 23/May/17 3:04 AM

2017-05-23 03:05|UPDATED status: transition Answered| # answers= 1, accepted answer= True

Show

Backlog Manager added a comment - 23/May/17 3:04 AM 2017-05-23 03:05|UPDATED status: transition Answered| # answers= 1, accepted answer= True

Fernando Lopez made changes - 29/May/17 7:42 AM

Assignee

Francisco Romero [ frb ]

Fernando Lopez made changes - 29/May/17 7:42 AM

HD-Enabler		Cosmos [ 10872 ]
Description	Created question in FIWARE Q/A platform on 11-08-2015 at 11:08 {color: red}Please, ANSWER this question AT{color} https://stackoverflow.com/questions/31937580/cygnus-mysql-sink-error-attr-persistence-column +Question:+ Cygnus MySQL sink error (attr_persistence = column) +Description:+ I'm using cygnus to store data in both MySQL Database and Cosmos. Storing the data in cosmos works ok, but when storing in MySQL, especifically with the attribute "cygnusagent.sinks.mysql-sink.attr_persistence = column" instead of "cygnusagent.sinks.mysql-sink.attr_persistence = row" the data is not stored and I'm getting some errors in flume log. As the table needs to be created previously when using the column attribute(more info : https://github.com/telefonicaid/fiware-cygnus/blob/master/doc/design/OrionMySQLSink.md#important-notes-regarding-the-persistence-mode), I create the table: CREATE TABLE def_servpath_sensorreading4_sensorreading(systemid int,value float, sensorid int, nodeid int); DESCRIBE def_servpath_sensorreading4_sensorreading(systemid int,value float, sensorid int, nodeid int); mysql> DESCRIBE def_servpath_sensorreading4_sensorreading; +----------+---------+------+-----+---------+-------+ \| Field \| Type \| Null \| Key \| Default \| Extra \| +----------+---------+------+-----+---------+-------+ \| systemid \| int(11) \| YES \| \| NULL \| \| \| value \| float \| YES \| \| NULL \| \| \| sensorid \| int(11) \| YES \| \| NULL \| \| \| nodeid \| int(11) \| YES \| \| NULL \| \| +----------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) This is the flume log: 11 Aug 2015 11:40:31,977 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionMySQLSink.persist:240) - [mysql-sink] Persisting data at OrionMySQLSink. Database: def_serv, Table: def_servpath_sensorreading3_sensorreading, Timestamp: 2015-08-11T08:40:31.969, Data (attrs): {systemid=1, value=29.2, sensorid=2, nodeid=1}, (metadata): {sensorid_md=[], systemid_md=[], value_md=[], nodeid_md=[]} 11 Aug 2015 11:40:31,978 WARN [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:182) - Bad context data (Unknown column 'systemid' in 'field list') 11 Aug 2015 11:40:31,978 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:193) - Finishing transaction (1439278647-655-0000000005) 11 Aug 2015 11:40:32,369 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionHDFSSink.persist:356) - [hdfs-sink] Persisting data at OrionHDFSSink. HDFS file (def_serv/def_servpath/sensorreading3_sensorreading/sensorreading3_sensorreading.txt), Data ({"recvTime":"2015-08-11T08:40:31.969Z","nodeid":"1", "nodeid_md":[],"sensorid":"2", "sensorid_md":[],"systemid":"1", "systemid_md":[],"value":"29.2", "value_md":[]}) 11 Aug 2015 11:40:32,858 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:193) - Finishing transaction (1439278647-655-0000000005) Where im getting the following error: Bad context data (Unknown column 'systemid' in 'field list') Do I need to create any more columns in the table related with the metadata? Am I creating the table correctly? Thanks in advance.	Created question in FIWARE Q/A platform on 11-08-2015 at 11:08 {color: red}Please, ANSWER this question AT{color} https://stackoverflow.com/questions/31937580/cygnus-mysql-sink-error-attr-persistence-column +Question:+ Cygnus MySQL sink error (attr_persistence = column) +Description:+ I'm using cygnus to store data in both MySQL Database and Cosmos. Storing the data in cosmos works ok, but when storing in MySQL, especifically with the attribute "cygnusagent.sinks.mysql-sink.attr_persistence = column" instead of "cygnusagent.sinks.mysql-sink.attr_persistence = row" the data is not stored and I'm getting some errors in flume log. As the table needs to be created previously when using the column attribute(more info : https://github.com/telefonicaid/fiware-cygnus/blob/master/doc/design/OrionMySQLSink.md#important-notes-regarding-the-persistence-mode), I create the table: CREATE TABLE def_servpath_sensorreading4_sensorreading(systemid int,value float, sensorid int, nodeid int); DESCRIBE def_servpath_sensorreading4_sensorreading(systemid int,value float, sensorid int, nodeid int); mysql> DESCRIBE def_servpath_sensorreading4_sensorreading; +----------+---------+------+-----+---------+-------+ \| Field \| Type \| Null \| Key \| Default \| Extra \| +----------+---------+------+-----+---------+-------+ \| systemid \| int(11) \| YES \| \| NULL \| \| \| value \| float \| YES \| \| NULL \| \| \| sensorid \| int(11) \| YES \| \| NULL \| \| \| nodeid \| int(11) \| YES \| \| NULL \| \| +----------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) This is the flume log: 11 Aug 2015 11:40:31,977 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionMySQLSink.persist:240) - [mysql-sink] Persisting data at OrionMySQLSink. Database: def_serv, Table: def_servpath_sensorreading3_sensorreading, Timestamp: 2015-08-11T08:40:31.969, Data (attrs): {systemid=1, value=29.2, sensorid=2, nodeid=1}, (metadata): {sensorid_md=[], systemid_md=[], value_md=[], nodeid_md=[]} 11 Aug 2015 11:40:31,978 WARN [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:182) - Bad context data (Unknown column 'systemid' in 'field list') 11 Aug 2015 11:40:31,978 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:193) - Finishing transaction (1439278647-655-0000000005) 11 Aug 2015 11:40:32,369 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionHDFSSink.persist:356) - [hdfs-sink] Persisting data at OrionHDFSSink. HDFS file (def_serv/def_servpath/sensorreading3_sensorreading/sensorreading3_sensorreading.txt), Data ({"recvTime":"2015-08-11T08:40:31.969Z","nodeid":"1", "nodeid_md":[],"sensorid":"2", "sensorid_md":[],"systemid":"1", "systemid_md":[],"value":"29.2", "value_md":[]}) 11 Aug 2015 11:40:32,858 INFO [SinkRunner-PollingRunner-DefaultSinkProcessor] (com.telefonica.iot.cygnus.sinks.OrionSink.process:193) - Finishing transaction (1439278647-655-0000000005) Where im getting the following error: Bad context data (Unknown column 'systemid' in 'field list') Do I need to create any more columns in the table related with the metadata? Am I creating the table correctly? Thanks in advance.
HD-Chapter		Data [ 10838 ]

Fernando Lopez made changes - 29/May/17 7:42 AM

Resolution		Done [ 10000 ]
Status	Answered [ 10104 ]	Closed [ 6 ]

Fernando Lopez made changes - 27/May/21 10:54 AM

Fix Version/s

2021 [ 12600 ]

Transition

Time In Source Status

Execution Times

Last Executer

Last Execution Date

Open

In Progress

2h 52m

Backlog Manager

23/May/17 12:04 AM

In Progress

Answered

Backlog Manager

23/May/17 3:04 AM

Answered

Closed

6d 4h 38m

Fernando Lopez

29/May/17 7:42 AM

People

Assignee:

Francisco Romero

Reporter:

Backlog Manager

Votes:

0 Vote for this issue

Watchers:

1 Start watching this issue

Dates

Created:

22/May/17 9:11 PM

Updated:

27/May/21 10:54 AM

Resolved:

29/May/17 7:42 AM

Agile

View on Board