Fails to insert data into MariaDB

[sekulicb]

Describe the bug logs table doesn't get created and fails on inserting data

MariaDb is 11.2

Here is my appsettings.development.json:

And my connection string:

Here is how I use and configure logger, which works fine for files and console:

To Reproduce

Expected behavior Should insert data into database

Screenshots If applicable, add screenshots to help explain your problem.

This looks to me like failed insert into logs database. It doesn't get automatically created even thou I set autoCreateTable": true, After I manually create logs table, this is what happens when I try to reach SerilogUI web page.

[followynne]

Hello @sekulicb

can I ask you to share a small sample record from your database logs?

The only int I see in the Log Model of SerilogUI is the row number, which should act as the table primary key. It's possible but it seems awkward for a primary key to be null...

[sekulicb]

Hi.

What logs records do you need? Nothing gets written into the logs database. When I start my API, and Swagger gets booted, then I navigate to the serilog-ui web dashboard, that's the moment I get this error. Logs table is empty all the time.

Thanks

[followynne]

@sekulicb

I'd say the problem should be on your Serilog configuration - if you don't have data in the Maria DB table you configured, serilog-ui won't be able to query anything from it.

If you check in your database the table logs, do you have any records in it? If that's not the case, I don't think there's an issue with serilog-ui.

Does it make sense to you?

[sekulicb]

Hmmm....I just copy pasted configuration from sinks-mariadb

Nothing is in the database

Okay I will look into the configuration in appsettings.development.json and report back

[sekulicb]

@followynne Okay I managed to get it working. Now Logs table gets autogenerated and logs are there, but when i navigate to Serilog.UI i still get same message back, so actually this looks like Serilog.UI problem:

Response from Serilog.UI

Data from logs table:

From output in Visual Studio:

So to sum it up, it's no longer INSERT issue but READ or getting data from MariaDb

[followynne]

@sekulicb

Thanks. Can you check if there's a record with id null?

[sekulicb]

@followynne No, all fields have primary key populated

[followynne]

@sekulicb

Thanks for the check. I'll try to debug the issue in the next days, in the meantime can I ask you to attach in this thread:

• a json file with the appsettings Serilog configuration
• a csv file with the extract of the first 50 rows in the logs table
• the version you use of Serilog,serilog mariadb sink, Serilog ui, Serilog ui MySql

Cheers 🙂, Matteo

[sekulicb]

@followynne here you go:

database.csv

appsettings.Development.json

As for the versions, I listed them under first comment, under To Reproduce section

[mo-esmp]

@followynne I just checked the SCs and provided a CSV file, and I guess the Exception column caused the error.

[followynne]

@mo-esmp I think you right, probably we'll need to set as nullable both exception and props columns :)

[followynne]

@sekulicb

I tried running the setup you use on a test project and it's working fine. I'm using your appsettings configuration, your Program services configuration, with the following Nuget packages:

<PackageReference Include="Serilog.AspNetCore" Version="8.0.1" />
<PackageReference Include="Serilog.Sinks.MariaDB" Version="1.0.1" />
<PackageReference Include="Serilog.UI" Version="2.6.0" />
<PackageReference Include="Serilog.UI.MySqlProvider" Version="2.2.1" />

Result:

I suggest you to delete the logs table and retry to run the application with a clean state. If it doesn't work, please provide:

• logs table schema info you have on MariaDB, with the specific columns configuration
• a text file that contains SQL statements that I can run to import a 30 lines sample of the logs you have in the table

Thanks a lot!

[sekulicb]

@followynne What version of MariaDB server are you using for test ?

[sekulicb]

@followynne

I tried cleaning everything and running again, and I got the same problem as before. Here is the CREATE code from HeidiSQL:

CREATE TABLE logs ( Id BIGINT(20) NOT NULL AUTO_INCREMENT, Exception TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', LogLevel TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', Message TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', MessageTemplate TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', Properties TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', Timestamp TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', PRIMARY KEY (Id) USING BTREE ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB AUTO_INCREMENT=416 ;

And here is the database, not as text file but as json file, I don't see option to export as text file in HeidiSQL database.json

[followynne]

@sekulicb

I'm running v11.2.3, through docker image docker pull mariadb:latest

I tried running your setup again and it still works fine.

Can I ask you to run the following:

1) Run this script on SQL and create a complete new logs table

CREATE TABLE logstestwithmanualtable (
Id BIGINT(20) NOT NULL AUTO_INCREMENT,
Exception TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
LogLevel TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
Message TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
MessageTemplate TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
Properties TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
Timestamp TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
PRIMARY KEY (Id) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

In your application, replace logs with logstestwithmanualtable both in appsettings and in serilog-ui DI definition. Set in the appsettings autoCreateTable to false.

"Serilog": {
    "Using": ["Serilog.Sinks.MariaDB"],
    "MinimumLevel": "Debug",
    "AuditTo": [
      {
        "Name": "MariaDB",
        "Args": {
          "connectionString": "Server=127.0.0.1;Port=3306;Database=yourdb;User=root;Password=[...];",
          "autoCreateTable": false,
          "tableName": "logstest"
        }
      }
    ]
  }

Run app and check if it works

2) Instead of manually creating the logs table, set in the appsettings autoCreateTable to true and use as table name a new name. Run the app and check if it works

Thanks!

[sekulicb]

@followynne

Still has the same problem. The only thing left is for me to try with Docker as well....

[followynne]

@sekulicb

if you have time, please give it a last try with Docker :) otherwise we'll have to close the issue as not reproducible, unless you can create a sample repo to reproduce the issue...

[sekulicb]

@followynne

What hostname did you use for Docker image? I leave it blank, and it works with HeidiSQL, I also leave it blank in appsettings, and I can see that logs table gets created, and populated with data, and then when I navigate to Serilog.UI I get following error:

Unable to connect to any of the specified MySQL hosts.

EDIT: On docker logs I get following message:

[Warning] Aborted connection 4 to db: 'm7' user: 'root' host: '172.17.0.1' (Got an error reading communication packets)

In the logs database itself, there are exceptions that are written:

"Message": "Fatal error encountered attempting to read the resultset.", "SourceContext": "Serilog.Ui.Web.Endpoints.SerilogUiEndpoints",

[followynne]

@sekulicb

connection string I'm using is Server=127.0.0.1;Port=3306;Database=test;User=root;Password=examplepassword; while Serilog.Ui registration is options.UseMySqlServer(builder.Configuration.GetConnectionString("DefaultConnection"), "logstest") but again, this is related to my setup.

I think that right now this issue is not related to Serilog.UI but it's more setup-oriented, which is out-of-scope with this repository.

To be able to support you onwards, please provide us a sample repo to reproduce the issue, otherwise it's difficult to help you identify the actual point of failure. 🥲

[sekulicb]

@followynne Closing since on another machine it works by default, so it has be something on my machine, I just can figure out what.

[followynne]

Hey @sekulicb 😃

A small heads-up on this issue: it will be fixed in serilog-ui v3, where we replaced the MySql.Data package with MySqlConnector.

The first package has issues accessing older versions of MariaDB (I don't have a freaking idea how I found it but here's the issue that got me on the good path 🤣).

I'm not far away from the final development, hopefully v3 will come real soon 😃