MySQL database support #953

anondev-sudo · 2021-11-24T06:14:40Z

⚠️ Please verify that this feature request has NOT been suggested before.

I checked and didn't find similar feature request

🏷️ Feature Request Type

Other

🔖 Feature description

Add MySQL db support

✔️ Solution

Support MySQL databases

❓ Alternatives

No response

📝 Additional Context

No response

trogper · 2021-11-24T07:26:39Z

For alternatives, I can think of at least two

TCP port monitor to port 3306
push monitor with cron job running mysql client

anondev-sudo · 2021-11-24T15:04:05Z

I mean to support MySQL Databases for storing data of it, where are data stored now? I don't want to monitor it.

deefdragon · 2021-11-25T09:52:40Z

Going to cross reference #894. I don't know if its Louis saying he wont support other databases, or just that they aren't supported RN tho.

jbenguira · 2021-12-02T15:12:38Z

Please @louislam stay strong on this and do not accept anything related to supporting other DBs.
If we go that way everything will become an horrible mess and maintenance will become complex because we will have to handle database migrations for multiple RDBMS and things will break all the time when a new version of postgres or mysql will be out ... Let's keep things simple and easy to manage and upgrade automatically

chakflying · 2021-12-02T16:59:23Z

With easy to setup being one of the key advantages of this tool, adding a full database server could significantly complicate things. Even at the scale of GitLab, where they have cookbooks and Rails powering their database migrations, I still end up having issues during upgrade. I also don't think it's anywhere close to being possible with the current codebase, since a lot of things are closely coupled with SQLite.

louislam · 2021-12-02T17:26:49Z

maintenance will become complex because we will have to handle database migrations for multiple RDBMS

Even at the scale of GitLab, where they have cookbooks and Rails powering their database migrations, I still end up having issues during upgrade.

Yes, especially Uptime Kuma is being developed by my free time and contributors' free time. Manpower is not enough for supporting this feature, so it is likely to be a very low priority request.

The feature request itself isn't bad, just because the complexity of the task. It remains open.

mrspartak · 2021-12-10T08:36:42Z

I understand why you went the way with SQLite and why it is a pain in the butt to maintain other DBs. I just want to add one more use case where it is needed. I'm using the docker-swarm stack for fail-proof, and there is no way to use local storage because the app can be migrated to another machine. So the only way for the app to function is to have an external DB.
For sure, support for the Mysql or Postgres could be handy in this situation. Or is it possible to make Sqlite external?

trogper · 2021-12-18T22:34:46Z

So today I got bored and added support for MySQL on my fork. Connection options are provided via ENV variables. Support for another databases should be easily added in knexfile.js. You need to manually npm install mysql driver.
Knex currently has an issue with migrations, please patch table-creator.js according to this commit

However it is not backwards compatible with existing databases. I have removed the old patching functions, the database template file and implemented migrations using knex. Backwards compatibility could be done in two ways

rewrite all the patches to knex migrations
require users to upgrade the last version before knex and then upgrading to knex

@louislam please take a look at it, whether you agree to move to knex

I had to convert two complex queries to knex format/syntax. Other queries seem simple enough to be compatible with most (if not all) SQL databases. I'd recommend to convert them also, but did not want to waste time if it won't be accepted.

PS: I tried to make the "uptime formula" calculation/query/function more understandable. I spent 20 minutes trying to comprehend it 😅.

bogdal · 2022-01-06T22:59:45Z

@trogper I think opening a PR can help to push further this integration. Otherwise, it's hard to track all your changes and make some decisions.

heffneil · 2022-03-19T13:36:06Z

How does one pass credentials for testing that port? I am getting a lot of log entries for no credentials passed?

trogper · 2022-03-19T16:36:06Z

@heffneil if you are asking about my changes, you should post the question in #1139
To answer you, its DB_TYPE=mysql, DB_HOST, DB_NAME, DB_USER and DB_PASS

s4ke · 2022-03-26T18:12:40Z

Maybe using a more modular approach would suffice for people that need mysql support. It is pretty simple to write a healthcheck rest service that you let respond with HTTP 200 if the cluster is healthy, and with HTTP 500 if it isn't. As inspiration, you could take a look at the service we wrote for PostgreSQL:

https://github.com/neuroforgede/pg_auto_failover_ansible/tree/master/tools/health_monitor

trogper · 2022-04-03T16:50:42Z

@s4ke this is not about monitoring MySQL, but about using MySQL as the database.

s4ke · 2022-04-04T07:07:36Z

@trogper ouch, sorry. read it again.

elizabeth-wilona · 2022-04-06T07:31:11Z

I don't really know how to develop NodeJS, but I do need to use other databases because I have over 300 services to monitor and SQLite expands a lot and crashes almost every day.

Very good open source project, I hope someone can solve .

trogper · 2022-04-06T07:34:02Z

@elizabeth-wilona
I don't think the crashes are caused my SQLite. Can you provide some logs for us to look at it?

elizabeth-wilona · 2022-04-06T07:35:41Z

@elizabeth-wilona I don't think the crashes are caused my SQLite. Can you provide some logs for us to look at it?

{"status":"fail","msg":"Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?"}

[2022-04-06 15:34:34] KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
at Client_SQLite3.acquireConnection (/root/uptime-kuma/node_modules/knex/lib/client.js:305:26)
at async Runner.ensureConnection (/root/uptime-kuma/node_modules/knex/lib/execution/runner.js:259:28)
at async Runner.run (/root/uptime-kuma/node_modules/knex/lib/execution/runner.js:30:19)
at async RedBeanNode.storeCore (/root/uptime-kuma/node_modules/redbean-node/dist/redbean-node.js:166:26)
at async RedBeanNode.store (/root/uptime-kuma/node_modules/redbean-node/dist/redbean-node.js:126:20) at async beat (/root/uptime-kuma/server/model/monitor.js:417:13)
at async Timeout.safeBeat [as _onTimeout] (/root/uptime-kuma/server/model/monitor.js:443:17) {
sql: undefined,
bindings: undefined
}
[2022-04-06 15:34:34] KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
at Client_SQLite3.acquireConnection (/root/uptime-kuma/node_modules/knex/lib/client.js:305:26)
at runMicrotasks ()
at runNextTicks (node:internal/process/task_queues:61:5)
at listOnTimeout (node:internal/timers:526:9)
at processTimers (node:internal/timers:500:7)
at async Runner.ensureConnection (/root/uptime-kuma/node_modules/knex/lib/execution/runner.js:259:28)
at async Runner.run (/root/uptime-kuma/node_modules/knex/lib/execution/runner.js:30:19)
at async RedBeanNode.normalizeRaw (/root/uptime-kuma/node_modules/redbean-node/dist/redbean-node.js:570:22)
at async RedBeanNode.getRow (/root/uptime-kuma/node_modules/redbean-node/dist/redbean-node.js:556:22)
at async RedBeanNode.getCell (/root/uptime-kuma/node_modules/redbean-node/dist/redbean-node.js:591:19) {
sql: '\n' +
' SELECT AVG(ping)\n' +
' FROM heartbeat\n' +
" WHERE time > DATETIME('now', ? || ' hours')\n" +
' AND ping IS NOT NULL\n' +
' AND monitor_id = ? limit ?',
bindings: [ -24, 66, 1 ]
}
[2022-04-06 15:34:34] KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
at Client_SQLite3.acquireConnection (/root/uptime-kuma/node_modules/knex/lib/client.js:305:26)
at async Runner.ensureConnection (/root/uptime-kuma/node_modules/knex/lib/execution/runner.js:259:28)
at async Runner.run (/root/uptime-kuma/node_modules/knex/lib/execution/runner.js:30:19)
at async RedBeanNode.storeCore (/root/uptime-kuma/node_modules/redbean-node/dist/redbean-node.js:166:26)
at async RedBeanNode.store (/root/uptime-kuma/node_modules/redbean-node/dist/redbean-node.js:126:20) at async beat (/root/uptime-kuma/server/model/monitor.js:417:13)
at async Timeout.safeBeat [as _onTimeout] (/root/uptime-kuma/server/model/monitor.js:443:17) {
sql: undefined,
bindings: undefined
}

It happens randomly when my database file has about 1GB, and it happens more and more frequently as the volume gets bigger and bigger.

christianoviedo · 2022-08-31T23:31:01Z

You can use this script in /etc/crontab (at least it works in Debian, I suppose Ubuntu also)

 * *     * * *   root    if service mysql status; then /usr/bin/curl -s 'https://YOUR_KUMA_URL/api/push/ID?status=up&msg=OK&ping='; fi; > /dev/null 2>&1

It just checks if Mysql running.

dvdlevanon · 2022-09-05T15:25:33Z

For me the motivation to use a DB other than Sqlite is to ease the deployment of Uptime Kuma.

Currently I deployed it as a StaefulSet in EKS, and used a Persisted Volume backed by AWS EBS.
While it's not that hard, the real downside of this setup is luck of High Availability because:

The POD must run on a single availability zone because of EBS limitation
Can't run more than one replica of Uptime Kuma because of Sqlite limitation

That's mean Uptime Kuma can't run in an HA mode.
Its hard to trust Uptime Kuma to monitor other HA servers if it doesn't deployed in an HA mode.

I'm thinking of workaround this issue by running another separate instance of Uptime Kuma, in a different zone, which monitor the other instance of Uptime Kuma. But of course it complicate the situation.

I understand how hard is to support multiple databases, especially when the core code contains the logic for all those database. Maybe it would be better to introduce a plugable storage engine, the default and the only official one would be Sqlite, but anyone can implement a new storage engine by implementing a core interface.

trogper · 2022-09-05T21:41:20Z

@dvdlevanon
What is your environment, that you need HA deployment? If you are in corporate and you need HA and have SLAs to be met, then kuma is not for you. Kuma is a free-time open source uptime monitoring. If you need anything robust and proven, then you should look elsewhere.

We already have something like pluggabe storage engine - an ORM. Kuma uses knox.js, which supports many different SQL databases, but kuma in current implementation is fixed to SQLite only. I have experimented with removing the explicit SQLite dependency and adding support for MySQL in my fork and PR #1139.

ntimo · 2022-12-31T14:46:22Z

Having mysql support would be super important too me, because my uptime kuma is super slow I have about 68 monitors and loading the dashboard takes about half a minute with a sqlite size of about 720mb

vadikcoma · 2023-03-12T18:32:52Z

We have 100+ monitors and kuma is becoming super slow. Adding or deleting a monitor can easily take minutes(!)

ljcbaby · 2023-11-15T05:46:02Z

We have 100+ monitors and kuma is becoming super slow. Adding or deleting a monitor can easily take minutes(!)

50~ monitors, it's very slow.

CommanderStorm · 2023-11-15T10:03:45Z

@vadikcoma
If adding or deleting takes several minutes, this means that sqlite is deleting a lot of data.
Consider reducing the retention time.

@ljcbaby
v2 is going to come with embedded/external mariadb or sqlite support.
⇒ the anticipated performance will be way better because of this and because of a few performance improvements.
See our roadmap for further details

CommanderStorm · 2023-12-07T14:06:10Z

v2 will include the ability to connect to a mysql
=> closing as resolved

anondev-sudo added the feature-request Request for new features to be added label Nov 24, 2021

deefdragon mentioned this issue Nov 25, 2021

Postgres support #959

Open

1 task

maxirus mentioned this issue Dec 20, 2021

Statefulset vs. Deployment + PVC? dirsigler/uptime-kuma-helm#10

Closed

trogper mentioned this issue Jan 7, 2022

Add support for MySQL and other DBs #1139

Closed

11 tasks

koen20 mentioned this issue Mar 13, 2022

Statistics Database Option #1361

Closed

1 task

JacksonChen666 mentioned this issue Jan 1, 2023

Shrinking databse/Blocking database opreations Give False Downtime #2470

Open

2 tasks

CommanderStorm mentioned this issue Aug 24, 2023

Does adding MySQL database support? #3637

Closed

1 task

CommanderStorm closed this as completed Dec 7, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL database support #953

MySQL database support #953

anondev-sudo commented Nov 24, 2021

trogper commented Nov 24, 2021

anondev-sudo commented Nov 24, 2021

deefdragon commented Nov 25, 2021

jbenguira commented Dec 2, 2021 •

edited

Loading

chakflying commented Dec 2, 2021

louislam commented Dec 2, 2021

mrspartak commented Dec 10, 2021

trogper commented Dec 18, 2021 •

edited

Loading

bogdal commented Jan 6, 2022 •

edited

Loading

heffneil commented Mar 19, 2022

trogper commented Mar 19, 2022

s4ke commented Mar 26, 2022

trogper commented Apr 3, 2022

s4ke commented Apr 4, 2022

elizabeth-wilona commented Apr 6, 2022

trogper commented Apr 6, 2022

elizabeth-wilona commented Apr 6, 2022 •

edited

Loading

christianoviedo commented Aug 31, 2022 •

edited

Loading

dvdlevanon commented Sep 5, 2022 •

edited

Loading

trogper commented Sep 5, 2022

ntimo commented Dec 31, 2022

vadikcoma commented Mar 12, 2023

ljcbaby commented Nov 15, 2023

CommanderStorm commented Nov 15, 2023

CommanderStorm commented Dec 7, 2023

MySQL database support #953

MySQL database support #953

Comments

anondev-sudo commented Nov 24, 2021

⚠️ Please verify that this feature request has NOT been suggested before.

🏷️ Feature Request Type

🔖 Feature description

✔️ Solution

❓ Alternatives

📝 Additional Context

trogper commented Nov 24, 2021

anondev-sudo commented Nov 24, 2021

deefdragon commented Nov 25, 2021

jbenguira commented Dec 2, 2021 • edited Loading

chakflying commented Dec 2, 2021

louislam commented Dec 2, 2021

mrspartak commented Dec 10, 2021

trogper commented Dec 18, 2021 • edited Loading

bogdal commented Jan 6, 2022 • edited Loading

heffneil commented Mar 19, 2022

trogper commented Mar 19, 2022

s4ke commented Mar 26, 2022

trogper commented Apr 3, 2022

s4ke commented Apr 4, 2022

elizabeth-wilona commented Apr 6, 2022

trogper commented Apr 6, 2022

elizabeth-wilona commented Apr 6, 2022 • edited Loading

christianoviedo commented Aug 31, 2022 • edited Loading

dvdlevanon commented Sep 5, 2022 • edited Loading

trogper commented Sep 5, 2022

ntimo commented Dec 31, 2022

vadikcoma commented Mar 12, 2023

ljcbaby commented Nov 15, 2023

CommanderStorm commented Nov 15, 2023

CommanderStorm commented Dec 7, 2023

jbenguira commented Dec 2, 2021 •

edited

Loading

trogper commented Dec 18, 2021 •

edited

Loading

bogdal commented Jan 6, 2022 •

edited

Loading

elizabeth-wilona commented Apr 6, 2022 •

edited

Loading

christianoviedo commented Aug 31, 2022 •

edited

Loading

dvdlevanon commented Sep 5, 2022 •

edited

Loading