[Warning] Aborted connection (Got an error reading communication packets) n8n backend db

[christoboucher]

Describe the issue/error/question

I have moved from the SQLite default to mariadb in preparation to scale to QUEUE mode.

When running any workflow, this WARNING mentioned below appears in the mariadb docker logs.

I have searched for this and not found much.

Have tried changing the variables in mariadb in the my_custom.cnf as shown below.

I have tested this on:

• the mariadb 10.10 as well
• different hardware

It appears that the executions still is written successfully into the database

my_custom.cnf

[mysqld]
max_allowed_packet=64M
net_read_timeout=120
net_write_timeout=120
connect_timeout=120

n8n Docker Compose

version: '3.3'

services:
  n8n:
    image: n8nio/n8n
    ports:
      - "5678:5678"
    volumes:
      - /mnt/dockervol1/n8n:/home/node/.n8n
      - /mnt/dockervol1/n8n/files:/files

    environment:
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=#####
      - N8N_BASIC_AUTH_PASSWORD=#####
      - GENERIC_TIMEZONE=Africa/Johannesburg
      - N8N_HOST=n8n.ubuntutester.#####.co.za
      - N8N_PORT=5678
      - N8N_PROTOCOL=http
      - NODE_ENV=production
      - WEBHOOK_URL=http://n8n.ubuntutester.#####.co.za:5678/
      - DB_TYPE=mariadb
      - DB_MYSQLDB_DATABASE=n8n
      - DB_MYSQLDB_HOST=154.###.204.158
      - DB_MYSQLDB_PORT=23306
      - DB_MYSQLDB_USER=n8n
      - DB_MYSQLDB_PASSWORD=###
      - EXECUTIONS_DATA_PRUNE=true
      - EXECUTIONS_DATA_MAX_AGE=48

mariadb docker-compose

version: '3.1'

services:
  maria:
    image: mariadb:10.3.21
    restart: always
    ports:
      - "23306:3306"
    volumes:
      - /var/lib/mysql:/var/lib/mysql
      - /srv/docker/mysql:/etc/mysql/conf.d
      - /srv/docker/mysql/upload:/var/lib/phpMyAdmin/upload
      - /srv/docker/mysql/save:/var/lib/phpMyAdmin/save
    environment:
      MYSQL_ROOT_PASSWORD: "#######"
      MYSQL_ROOT_HOST: "%"
      MARIADB_USER: "n8n"
      MARIADB_PASSWORD: "###"

What is the error message (if any)?

Error message received in the mariadb logs. (docker logs)
2023-02-15 9:05:03 29 [Warning] Aborted connection 29 to db: ‘n8n’ user: ‘n8n’ host: ‘10.0.0.2’ (Got an error reading communication packets)

Please share the workflow

Not workflow related

Share the output returned by the last node

Not workflow related

Information on your n8n setup

• n8n version: 0.215.2
• Database you’re using (default: SQLite): mariadb:10.3.21 / mariadb:10.10
• Running n8n with the execution process [own(default), main]: not workflow related
• Running n8n via [Docker, npm, n8n.cloud, desktop app]: docker / portainer

[christoboucher]

Just some more information.
This comes from the mysql general logs file and shows the query that is running causing this WARNING message:

MariaDB logs file Line

2023-02-15 11:50:29 113 [Warning] Aborted connection 113 to db: 'n8n' user: 'n8n' host: '10.0.2.10' (Got an error reading communication packets)

MariaDB General Log File entry

The 113 Corresponds from the 1st log entry to these.

230215 11:50:28	   113 Connect	[email protected] on n8n using TCP/IP
		   113 Query	SELECT VERSION() AS `version`
		   113 Query	SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'n8n' AND `TABLE_NAME` = 'migrations'
		   113 Query	SELECT * FROM `n8n`.`migrations` `migrations` ORDER BY `id` DESC
		   113 Query	SELECT `Settings`.`key` AS `Settings_key`, `Settings`.`value` AS `Settings_value`, `Settings`.`loadOnStartup` AS `Settings_loadOnStartup` FROM `settings` `Settings` WHERE (`Settings`.`key` = 'license.cert') LIMIT 1

[Jon]

Hey @christoboucher,

Welcome to the community

My first thought is this isn’t going to be an n8n issue directly and will likely be some odd network hiccup, Not really sure where to start though. I assume your MariaDB is set to those values after some trial and error so I guess it could be interesting to know if there is any response at all or if the connection is just being killed somewhere.

[christoboucher]

Thanx for your Response Jon

I don’t think there is a network or Communication issue. Both the n8n and MariaDB are on the same VM, in the Same Docker Environment and on the same Docker network.

To test, I created an n8n work flow that writes random data to the same MariaDB Instance, Different database & table and a different database user.

All the queries for the MySQL node in the workflow do not give an error whatsoever, only the username the n8n uses to connect to the MariaDB comes up in the errors, so it seems to be the n8n backend that is talking to MariaDB were their could be a bug???

Would really like to get this resolved.

If there is something I should do and send logs / screenshots, please let me know.

Thanx

[Jon]

Hey @christoboucher,

Have you manually ran that logged query as the same user to see what comes back?

There is a possibility that we are not handling the response but the query seems fairly simple so it would be surprising but not impossible. I have seen the same error a bunch over the years from various applications so my first thought is always network or resource related with that sort of thing.

If you check the env options I think we have one to debug the database side of things and there is also a debug option for logging that could help.

Personally if you are looking at using queue mode I would look at Postgres over MariaDB as we have been talking internally about possibly dropping support for it in the future.

[christoboucher]

Hi Jon,

Again, thanx for your reply.

I have always been partial to MariaDB, it is what I know and have been very apprehensive to use PostgreSQL.

You last statement drove the “nail in the coffin” for me.

I have been able to setup and PostgreSQL, 2 node, replication in my Docker / Portainer Environment, together with PGAdmin 4 and all seems to be good on that front.

If have found that other Soft wares that we use are also dropping support for MariaDB in their next major release so that just re-enforces the move to PostgreSQL in certain instances.

I still think looking into the issue with MariaDB is worth taking a look at as others may be experiencing what I have with MariaDB.

Kind Regards

[Jon]

Hey @christoboucher,

You are not wrong it would be worth looking into more, I checked one of my older instances that is using MariaDB and all seems to be good there so I might give it an upgrade and see what happens.