Non-provided columns filled with NULL, conflicts with default UUID

[theis188]

I have a field pkey which defaults to a generated uuid as follows:

pkey UUID NOT NULL DEFAULT uuid_generate_v1(),

I think the plugin is autofilling any missing column with NULL:

2020-08-07 17:14:28 -0400 [error]: #0 Got deterministic error again. Dump a record error_class=ActiveRecord::NotNullViolation error="PG::NotNullViolation: ERROR:  null value in column \"pkey\" violates not-null constraint\nDETAIL:  Failing row contains (null, 2016-06-22 19:10:25, [\"a\", 3], cool_model, 2.0, 0.5).\n: INSERT INTO \"fluentd_test\" (\"pkey\",\"my_datetime\",\"model_data\",\"ml_model_name\",\"model_version\",\"score\") VALUES (NULL,'2016-06-22 19:10:25','[\"a\",3]','cool_model','2.0',0.5)" record=#<Fluent::Plugin::SQLOutput::BaseModel_12752544::FluentdTest pkey: nil, my_datetime: "2016-06-22 19:10:25", model_data: ["a", 3], ml_model_name: "cool_model", model_version: "2.0", score: 0.5>

The INSERT statement could omit the pkey field and the NULL statement and the database would auto-fill with uuid_generate_v1(), so the message would not have to contain uuid.

[pikhovkin]

I have the same problem. I have

CREATE TABLE MY_TABLE 
   (	"ID" NUMBER(11,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"MSG" NVARCHAR2(100), 
        "MSG2"  NVARCHAR2(100)
...
)

<table>
    table my_table
    column_mapping 'msg'
</table>

I send a lot of messages, for example {'msg': 'abc'} and I get the error

2020-09-02 18:15:39 +0000 [error]: #0 Got deterministic error again. Dump a record error_class=ActiveRecord::RecordNotUnique error="OCIError: ORA-00001: unique constraint (MYTBL.SYS_C0011689) violated" record=#<Fluent::Plugin::SQLOutput::BaseModel_144228547::My_table id: 0, msg2: nil, msg: "abc">

Why is the id field replaced with 0?

[theis188]

Luckily I was able to generate a UUID at the message source, but that may not be possible with incremented ID.

Out of curiosity, what database/version is this. Is this oracle? I have not seen these statements for default values before.

[pikhovkin]

It's Oracle 12.2.0.1 adapter oracle_enhanced

[pikhovkin]

It works with adapter postgresql (postgres 13)

[harsh288]

I have a fluentd configuration that pulls data from the file and pushes to the SQL server however there is a primary key with the auto-incremented column, so, in my fluentd configuration if I don't mention that column it throws an error saying that the field is missing and if I include the column in the configuration it gives identity error, in below configuration "Id" is the primary and auto-incremented column, also let me know if adapter "sqlserver" is the right thing to use.

<filter record.**> @type record_transformer enable_ruby true Id ${id} timestamp ${time}

<filter record.**>
  @type stdout
</filter>

<match record.**> 
    
    @type sql
    host myhost
    username myuser
    password mypassword
    database mydb
    adapter sqlserver

    <table>
      table simple_table
      column_mapping 'Id:Id,timestamp:timestamp'
    </table>
    
    flush_interval 1s
    # disable_retry_limit
    # num_threads 8
    # slow_flush_log_threshold 40.0
</match>

[harsh288]

@theis188 @pikhovkin Could you please help with the above issue ??

[pikhovkin]

I refused to write directly to Oracle, I write events through the Kafka and the handler, and already in the handler I write to Oracle

[harsh288]

@pikhovkin May I know, why did you go via that path, did you find fluentd configuration difficult/problamatic??