Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MySQL database support #953

Closed
1 task done
anondev-sudo opened this issue Nov 24, 2021 · 25 comments
Closed
1 task done

MySQL database support #953

anondev-sudo opened this issue Nov 24, 2021 · 25 comments
Labels
feature-request Request for new features to be added

Comments

@anondev-sudo
Copy link

⚠️ 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

@anondev-sudo anondev-sudo added the feature-request Request for new features to be added label Nov 24, 2021
@trogper
Copy link
Contributor

trogper commented Nov 24, 2021

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
Copy link
Author

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

@deefdragon
Copy link
Contributor

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.

@deefdragon deefdragon mentioned this issue Nov 25, 2021
1 task
@jbenguira
Copy link
Contributor

jbenguira commented Dec 2, 2021

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
Copy link
Collaborator

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
Copy link
Owner

louislam commented Dec 2, 2021

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
Copy link

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
Copy link
Contributor

trogper commented Dec 18, 2021

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

  1. rewrite all the patches to knex migrations
  2. 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
Copy link

bogdal commented Jan 6, 2022

@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
Copy link

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

@trogper
Copy link
Contributor

trogper commented Mar 19, 2022

@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
Copy link

s4ke commented Mar 26, 2022

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
Copy link
Contributor

trogper commented Apr 3, 2022

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

@s4ke
Copy link

s4ke commented Apr 4, 2022

@trogper ouch, sorry. read it again.

@elizabeth-wilona
Copy link

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
Copy link
Contributor

trogper commented Apr 6, 2022

@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
Copy link

elizabeth-wilona commented Apr 6, 2022

@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
Copy link

christianoviedo commented Aug 31, 2022

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
Copy link

dvdlevanon commented Sep 5, 2022

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
Copy link
Contributor

trogper commented Sep 5, 2022

@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
Copy link

ntimo commented Dec 31, 2022

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
Copy link

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

@ljcbaby
Copy link

ljcbaby commented Nov 15, 2023

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
Copy link
Collaborator

@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
Copy link
Collaborator

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
feature-request Request for new features to be added
Projects
None yet
Development

No branches or pull requests