[cygnus-ngsi][NGSIMySQLSink] Error when persisting bulks of rows with different number of columns (with "datetime" coluumns)

smartcitydevops commented 5 years ago

We have found several cygnus traces, with this kind of message as an example:

 msg=com.telefonica.iot.cygnus.sinks.NGSISink[561] : BadContextData (SQLException). Data insertion error (Data truncation: Incorrect datetime value: '' for column 'scheduled_datetime' at row 2). Stack trace: [com.telefonica.iot.cygnus.backends.mysql.MySQLBackendImpl.insertContextData(MySQLBackendImpl.java:162), com.telefonica.iot.cygnus.sinks.NGSIMySQLSink.persistAggregation(NGSIMySQLSink.java:556), com.telefonica.iot.cygnus.sinks.NGSIMySQLSink.persistBatch(NGSIMySQLSink.java:200), com.telefonica.iot.cygnus.sinks.NGSISink.processNewBatches(NGSISink.java:558), com.telefonica.iot.cygnus.sinks.NGSISink.process(NGSISink.java:370), org.apache.flume.sink.DefaultSinkProcessor.process(DefaultSinkProcessor.java:68), org.apache.flume.SinkRunner$PollingRunner.run(SinkRunner.java:147), java.lang.Thread.run(Thread.java:745)]

The previous related message is this one:

 msg=com.telefonica.iot.cygnus.sinks.NGSIMySQLSink[545] : [mysql-sink-mysql] Persisting data at NGSIMySQLSink. Database (sc_place), Table (sys_snincidences), Fields ((recvTime,fiwareServicePath,entityId,entityType,ID,ID_md,category,category_md,incidenceCode,incidenceCode_md,jurisdiction,jurisdiction_md,latitude,latitude_md,longitude,longitude_md,priority,priority_md,scheduled_datetime,scheduled_datetime_md,status,status_md,status_datetime,status_datetime_md,subject,subject_md)), Values (('2018-10-18T10:49:45.897','/sys','PLACE30077','SNIncidence','PLACE30077','[]','Transport','[]','5bc81ef013bd9c620c8b45bd','[]','55a68da46aa918e3048b4568','[]','40.6311311','[]','-3.1518481000001','[]','0','[]','2018-10-18 13:00:00','[]','pending','[]','2018-10-18 10:49:43','[]','INCIDENCIAS','[]'),('2018-10-18T10:49:48.721','/sys','PLACE30093','SNIncidence','PLACE30093','[]','Status','[]','5bc86203701bdc6b088b4569','[]','55a68da46aa918e3048b4568','[]','40.6377029','[]','-3.1621116','[]','0','[]','','','pending','[]','2018-10-18 10:49:47','[]','BALDEO','[]'))

There is a "scheduled_datetime" value for the first register to be inserted, but not for the second register. In fact, cygnus places an empty '' value for this column in the second register. The type for the "scheduled_datetime" column in "sys_snincidences" table is "datetime".

Could it be posible to avoid the change of the type of the table?

AlvaroVega commented 5 years ago

Possible fix: use Varchar in that type Possible fix long tail: check configuration flag before use ""

AlvaroVega commented 5 years ago

@smartcitydevops could you provide us the database schema (SQL> describe table_name) ?

smartcitydevops commented 5 years ago

mysql> desc mtc_snincidences ;
+-----------------------+---------------+------+-----+-------------------+-------+
| Field                 | Type          | Null | Key | Default           | Extra |
+-----------------------+---------------+------+-----+-------------------+-------+
| incidenceCode         | varchar(255)  | NO   | PRI | NA                |       |
| ID                    | varchar(50)   | NO   |     | NA                |       |
| category              | varchar(255)  | YES  |     | NULL              |       |
| subject               | varchar(1000) | YES  |     | NULL              |       |
| jurisdiction          | varchar(50)   | YES  |     | NULL              |       |
| priority              | tinyint(4)    | YES  |     | NULL              |       |
| latitude              | decimal(11,8) | YES  |     | NULL              |       |
| longitude             | decimal(11,8) | YES  |     | NULL              |       |
| scheduled_datetime    | datetime      | YES  |     | NULL              |       |
| status                | varchar(10)   | YES  |     | NULL              |       |
| status_datetime       | datetime      | YES  |     | NULL              |       |
| recvTime              | timestamp     | NO   | PRI | CURRENT_TIMESTAMP |       |
| fiwareServicePath     | varchar(255)  | YES  |     | NULL              |       |
| entityId              | varchar(255)  | YES  |     | NULL              |       |
| entityType            | varchar(255)  | YES  |     | NULL              |       |
| incidenceCode_md      | varchar(255)  | YES  |     | NULL              |       |
| ID_md                 | varchar(255)  | YES  |     | NULL              |       |
| category_md           | varchar(255)  | YES  |     | NULL              |       |
| subject_md            | varchar(1000) | YES  |     | NULL              |       |
| jurisdiction_md       | varchar(255)  | YES  |     | NULL              |       |
| priority_md           | varchar(255)  | YES  |     | NULL              |       |
| latitude_md           | varchar(255)  | YES  |     | NULL              |       |
| longitude_md          | varchar(255)  | YES  |     | NULL              |       |
| scheduled_datetime_md | varchar(255)  | YES  |     | NULL              |       |
| status_md             | varchar(255)  | YES  |     | NULL              |       |
| status_datetime_md    | varchar(255)  | YES  |     | NULL              |       |
+-----------------------+---------------+------+-----+-------------------+-------+

The problem is related with '' data tried to be inserted in the scheduled_datetime column. Its value can be NULL, but not '' because this is a datetime column

pmo-sdr commented 5 years ago

@smartcitydevops There's no way Cygnus knows mysql column types before sending insert statements to decide whether to send "" or null.

Have you tryed adding a trigger to your database table? I mean, something like: mtc_snincidences_before_insert

BEGIN

    if new.scheduled_datetime='' then
        set new.scheduled_datetime= NULL;
    end if;

    if new.status_datetime='' then
        set new.status_datetime= NULL;
    end if;

END

telefonicaid / fiware-cygnus

[cygnus-ngsi][NGSIMySQLSink] Error when persisting bulks of rows with different number of columns (with "datetime" coluumns) #1528