Support mariadb as a database backend [GOT CREATED]

ajayjohn · April 14, 2020, 6:28am

Could you please add support for mariadb as a database backend, please?
Currently, if I use the mysqldb settings with a mariadb server, I get the following error. It seems like n8n was not able to create the default tables needed.

ln: /home/node/.n8n: File exists
Loading configuration overwrites from:
 - /root/.n8n/settings.json
(node:6) UnhandledPromiseRejectionWarning: QueryFailedError: Table 'n8n.workflow_entity' doesn't exist
    at new QueryFailedError (/usr/local/lib/node_modules/n8n/node_modules/typeorm/error/QueryFailedError.js:11:28)
    at Query.onResult (/usr/local/lib/node_modules/n8n/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:170:45)
    at Query.execute (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/commands/command.js:30:14)
    at PoolConnection.handlePacket (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/connection.js:417:32)
    at PacketParser.onPacket (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/connection.js:75:12)
    at PacketParser.executeStart (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/connection.js:82:25)
    at Socket.emit (events.js:310:20)
    at addChunk (_stream_readable.js:286:12)
    at readableAddChunk (_stream_readable.js:268:9)
(node:6) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:6) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

Also, until support for mariadb is added, is there any workaround that you could suggest for using it, maybe via manual steps I can execute on the db?

jan · April 14, 2020, 6:50am

Yes, should not be to complicated to add. We can have a look for the next version. Till then I have however no idea what you could do as I never used MariaDB and so do have no experience with it.

I would simply suggest using a supported database and then move the workflows over later.

tony · April 14, 2020, 12:41pm

I have also tried MySQL, and it does not really work well for logging executions (seems to work well for other stull), but decided to just go with PostgreSQL and it works better

On other note, what is the advantages of actually setting up PostgreSQL vs the Included SQL Lite? I am assuming it just speed if you need to log error executions (esp as my workflow process about 200-300 MB of JSON file/ run)

jan · April 14, 2020, 12:59pm

Honestly do not have much experience with n8n and MySQL. What kind of problems do you have?

Are generally not the most experiences DB person. So I am probably the wrong person to ask. Here a post of people that know more:

ajayjohn · April 14, 2020, 1:50pm

@jan,
Awesome. Thanks!
I’ll just wait till the next release then

@tony,
Thanks for the suggestion! I have never used PostgreSQL before. Will check it out.
I was just hoping to use MariaDB if possible, as I am a little more familiar with how it behaves.

jan · April 14, 2020, 5:57pm

Added now MariaDB support:
https://github.com/n8n-io/n8n/commit/303062623b3ab133eafab1d4037d6d38e071e980

Will be released with the next version.

But have no idea if it does now anything different than before, as all of it get handled by TypeORM. In my test it worked at least, it worked for me, however, also with having mysql set.

ajayjohn · April 14, 2020, 6:14pm

Amazing! Thanks
I checked the TypeORM specs and it seems to show that they are well integrated with MariaDB. But, in either case, I can test and let you know of any odd behavior.

jan · April 14, 2020, 6:17pm

Ah yes according to their documentation yes. But I assume that underneath the hood it uses the same code as for MySQL.

ajayjohn · April 14, 2020, 6:21pm

Yeah, that makes sense. This will help me store more persistent data in my flows.

jan · April 22, 2020, 10:14pm

Got released with [email protected]

ajayjohn · April 23, 2020, 2:06am

Thanks for the update @jan. I tested it and unfortunately, it doesn’t seem to work
I get the following lines showing up in my log now, when it fails to start.

Loading configuration overwrites from:
 - /root/.n8n/settings.json
(node:6) UnhandledPromiseRejectionWarning: QueryFailedError: Table 'n8n.workflow_entity' doesn't exist
    at new QueryFailedError (/usr/local/lib/node_modules/n8n/node_modules/typeorm/error/QueryFailedError.js:11:28)
    at Query.onResult (/usr/local/lib/node_modules/n8n/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:170:45)
    at Query.execute (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/commands/command.js:30:14)
    at PoolConnection.handlePacket (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/connection.js:417:32)
    at PacketParser.onPacket (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/connection.js:75:12)
    at PacketParser.executeStart (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/usr/local/lib/node_modules/n8n/node_modules/mysql2/lib/connection.js:82:25)
    at Socket.emit (events.js:310:20)
    at addChunk (_stream_readable.js:286:12)
    at readableAddChunk (_stream_readable.js:268:9)
(node:6) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:6) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
(node:6) PromiseRejectionHandledWarning: Promise rejection was handled asynchronously (rejection id: 1)
(node:6) UnhandledPromiseRejectionWarning: Error: There was an error: Table 'n8n.workflow_entity' doesn't exist
    at Object.error (/usr/local/lib/node_modules/n8n/node_modules/@oclif/errors/lib/index.js:22:17)
    at Start.error (/usr/local/lib/node_modules/n8n/node_modules/@oclif/command/lib/command.js:59:23)
    at /usr/local/lib/node_modules/n8n/dist/commands/start.js:110:22
(node:6) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)

jan · April 23, 2020, 6:46am

So actually it is exactly the same problem as before. I am actually not surprised, like written above do I actually think that nothing changed anyway and it uses the same code as for MySQL (we can now even see that in the error message above). Which actually makes sense as they are supposed to be compatible with each other so anything else would be strange.

The only thing I am wondering about is why MariaDB worked perfectly fine when I test it but seems to make problems for you. Do you use a recent version? I tested for example with 10.4.12.

ajayjohn · April 29, 2020, 4:26am

Sorry, got a little busy at work in the last few days.
I am using MariaDB 10.3.21, which isn’t much older. I trying digging into the error a little bit and it seems like TypeORM doesn’t really realize that the tables within the DB do not exist. I checked and noticed that you have correctly included the synchronize = true flag for TypeORM.

So I am clueless as to what might be going wrong Unfortunately, since I have a lot of other stuff running on my MariaDB instance, I can’t replace it easily either.

If possible, could you please export and share your MariaDB database schema. My last resort is to try and create the table structure manually and attempt to run n8n again using the same DB.

ajayjohn · May 17, 2020, 6:13pm

@jan,
Just checking in about the database schema request.

jan · May 17, 2020, 9:49pm

Ah sorry sure. You can find them here:

github.com

n8n-io/n8n/blob/master/packages/cli/src/databases/mysqldb/migrations/1588157391238-InitialMigration.ts

import { MigrationInterface, QueryRunner } from 'typeorm';

import * as config from '../../../../config';

export class InitialMigration1588157391238 implements MigrationInterface {
	name = 'InitialMigration1588157391238';

	async up(queryRunner: QueryRunner): Promise<void> {
		const tablePrefix = config.get('database.tablePrefix');

		await queryRunner.query('CREATE TABLE IF NOT EXISTS `' + tablePrefix + 'credentials_entity` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(128) NOT NULL, `data` text NOT NULL, `type` varchar(32) NOT NULL, `nodesAccess` json NOT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, INDEX `IDX_' + tablePrefix + '07fde106c0b471d8cc80a64fc8` (`type`), PRIMARY KEY (`id`)) ENGINE=InnoDB', undefined);
		await queryRunner.query('CREATE TABLE IF NOT EXISTS `' + tablePrefix + 'execution_entity` (`id` int NOT NULL AUTO_INCREMENT, `data` text NOT NULL, `finished` tinyint NOT NULL, `mode` varchar(255) NOT NULL, `retryOf` varchar(255) NULL, `retrySuccessId` varchar(255) NULL, `startedAt` datetime NOT NULL, `stoppedAt` datetime NOT NULL, `workflowData` json NOT NULL, `workflowId` varchar(255) NULL, INDEX `IDX_' + tablePrefix + 'c4d999a5e90784e8caccf5589d` (`workflowId`), PRIMARY KEY (`id`)) ENGINE=InnoDB', undefined);
		await queryRunner.query('CREATE TABLE IF NOT EXISTS`' + tablePrefix + 'workflow_entity` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(128) NOT NULL, `active` tinyint NOT NULL, `nodes` json NOT NULL, `connections` json NOT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `settings` json NULL, `staticData` json NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB', undefined);
	}

	async down(queryRunner: QueryRunner): Promise<void> {
		const tablePrefix = config.get('database.tablePrefix');

		await queryRunner.query('DROP TABLE `' + tablePrefix + 'workflow_entity`', undefined);
		await queryRunner.query('DROP INDEX `IDX_' + tablePrefix + 'c4d999a5e90784e8caccf5589d` ON `' + tablePrefix + 'execution_entity`', undefined);

This file has been truncated. show original

ajayjohn · May 19, 2020, 3:15am

Thanks, @jan!
As I suspected, the issue was simply to do with creating the initial tables. I can confirm that MariaDB works perfectly after the database schema has been created manually