Foreign key failure with meter values report

[chuck-h]

I have an OCPP1.5 charge point (Delta AC Mini, v02.05.30 firmware) which is configured for periodic meter values reporting. After powering up the charge point, the first meter values report fails.

Without analyzing code, I speculate that perhaps Steve isn't set up to accept meter readings that don't belong to a transaction?

Charge point POST (periodic meter values report):

eXtensible Markup Language
    <?xml
        version="1.0"
        encoding="UTF-8"
        ?>
    <SOAP-ENV:Envelope
        xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope"
        xmlns:SOAP-ENC="http://www.w3.org/2003/05/soap-encoding"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:ns1="urn://Ocpp/Cp/2012/06/"
        xmlns:chan="http://schemas.microsoft.com/ws/2005/02/duplex"
        xmlns:wsa5="http://www.w3.org/2005/08/addressing"
        xmlns:ns2="urn://Ocpp/Cs/2012/06/">
        <SOAP-ENV:Header>
            <ns2:chargeBoxIdentity
                SOAP-ENV:mustUnderstand="true">
                Delta01
                </ns2:chargeBoxIdentity>
            <wsa5:MessageID>
                urn:uuid:904b4b43-3b10-45bc-9afc-bd799c407dd7
                </wsa5:MessageID>
            <wsa5:From>
                <wsa5:Address>
                    http://192.168.1.128:8080/ChargeBox/OCPP15
                    </wsa5:Address>
                </wsa5:From>
            <wsa5:To
                SOAP-ENV:mustUnderstand="true">
                http://192.168.1.133:8080/steve/services/CentralSystemService
                </wsa5:To>
            <wsa5:Action
                SOAP-ENV:mustUnderstand="true">
                /MeterValues
                </wsa5:Action>
            </SOAP-ENV:Header>
        <SOAP-ENV:Body>
            <ns2:meterValuesRequest>
                <ns2:connectorId>
                    0
                    </ns2:connectorId>
                <ns2:transactionId>
                    0
                    </ns2:transactionId>
                <ns2:values>
                    <ns2:timestamp>
                        2018-05-06T17:40:23Z
                        </ns2:timestamp>
                    <ns2:value
                        unit="kWh"
                        location="Body"
                        measurand="Energy.Active.Import.Interval"
                        format="Raw"
                        context="Sample.Periodic">
                        0.00
                        </ns2:value>
                    </ns2:values>
                <ns2:values>
                    <ns2:timestamp>
                        2018-05-06T17:40:23Z
                        </ns2:timestamp>
                    <ns2:value
                        unit="kW"
                        location="Body"
                        measurand="Power.Active.Import"
                        format="Raw"
                        context="Sample.Periodic">
                        0.02
                        </ns2:value>
                    </ns2:values>
                <ns2:values>
                    <ns2:timestamp>
                        2018-05-06T17:40:23Z
                        </ns2:timestamp>
                    <ns2:value
                        unit="Amp"
                        location="Body"
                        measurand="Current.Import"
                        format="Raw"
                        context="Sample.Periodic">
                        0.06
                        </ns2:value>
                    </ns2:values>
                <ns2:values>
                    <ns2:timestamp>
                        2018-05-06T17:40:23Z
                        </ns2:timestamp>
                    <ns2:value
                        unit="Volt"
                        location="Body"
                        measurand="Voltage"
                        format="Raw"
                        context="Sample.Periodic">
                        238.14
                        </ns2:value>
                    </ns2:values>
                </ns2:meterValuesRequest>
            </SOAP-ENV:Body>
        </SOAP-ENV:Envelope>

Central System reply (database error)

eXtensible Markup Language
    <soap:Envelope
        xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
        <soap:Header>
            <Action
                xmlns="http://www.w3.org/2005/08/addressing">
                urn://Ocpp/Cs/2012/06/:CentralSystemService:MeterValues:Fault:DataAccessException
                </Action>
            <MessageID
                xmlns="http://www.w3.org/2005/08/addressing">
                urn:uuid:c2267cc0-4cba-4891-8b67-6e17d15d4852
                </MessageID>
            <To
                xmlns="http://www.w3.org/2005/08/addressing">
                http://www.w3.org/2005/08/addressing/anonymous
                </To>
            <RelatesTo
                xmlns="http://www.w3.org/2005/08/addressing">
                urn:uuid:904b4b43-3b10-45bc-9afc-bd799c407dd7
                </RelatesTo>
            </soap:Header>
        <soap:Body>
            <soap:Fault>
                <soap:Code>
                    <soap:Value>
                        soap:Receiver
                        </soap:Value>
                    </soap:Code>
                <soap:Reason>
                    <soap:Text
                        xml:lang="en">
                         SQL [insert into `stevedb`.`connector_meter_value` (`connector_pk`, `transaction_pk`, `value_timestamp`, `value`, `reading_context`, `format`, `measurand`, `location`, `unit`, `phase`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; Cannot add or update a child row: a foreign key constraint fails (`stevedb`.`connector_meter_value`, CONSTRAINT `FK_tid_cm` FOREIGN KEY (`transaction_pk`) REFERENCES `transaction` (`transaction_pk`) ON DELETE SET NULL ON UPDATE NO ACTION)
                        </soap:Text>
                    </soap:Reason>
                </soap:Fault>
            </soap:Body>
        </soap:Envelope>

[goekay]

Without analyzing code, I speculate that perhaps Steve isn't set up to accept meter readings that don't belong to a transaction?

in the meter values message, the optional transaction id parameter is set (<ns2:transactionId>0</ns2:transactionId>), but steve does not know anything about this transaction which should not happen, because the transaction id is generated and assigned by the central system, i.e. steve. the station should not use some arbitrary transaction ids out of nowhere.

steve indeed accepts meter readings that do not belong to a transaction, but in such cases the optional transaction id should not be set.

[chuck-h]

Sevket,
Thanks, your explanation makes sense. I have opened a ticket with Delta about this. I see the OCPP specification reads:
The request PDU SHALL contain for each sample:
[...]
2. The transaction id of the transaction to which these values are related, if applicable. If there is no transaction in progress or if the values are taken from the main meter, then transaction id may be omitted.
[...]
I suppose that one could argue this spec is not completely clear; it says the transaction id may be omitted, rather than it SHALL be omitted. Personally, I believe that the Delta message is noncompliant with OCPP.

Perhaps a SOAP fault text of "invalid transaction ID" would be clearer than the raw SQL error text. Not worth a lot of effort, though.

[goekay]

Perhaps a SOAP fault text of "invalid transaction ID" would be clearer than the raw SQL error text. Not worth a lot of effort, though.

i agree on both. since the database model already enforces such integrity/constraint checks, we trust it and do not do the same kind of check in application code.

for example, there are several messages which might reference transaction and/or reservation ids. before processing these messages, we could do another database round trip just to check whether this transaction/reservation exists and just to respond with a more appropriate error message. i'd argue that the benefit is not worth the extra processing overhead. or we could process the java exception (for example the one you got from db) to find out what the reason was. but we do not do this for reasons explained here.

[csamsel]

Usually i would advise against vendor specific workarounds, but treating an transaction id 0 in the same way as if was ommited seems not too bad.

[goekay]

@csamsel in wsdl files, transaction id is an xs:int. so... negative values are actually allowed. what if some other vendor decides to use -1 (or some other magic number) to express the same? it does not stop with only one magic number 0.

over time, steve already became more and more tolerant, since we dialed down its strictness. but this is one case i would be against.