Reaching max connections on MySQL #15

New Issue

Govindas · 2021-09-03T19:26:13Z

nathanpaulyoung commented

2021-09-03 19:26:13 +00:00

(Migrated from github.com)

My skript is reaching max connections on our MySQL instance. How does one manually manage connections to the DB? clearing the object does not seem to solve the issue.

#
# Check if the user has linked their Minecraft account
#

# Initialize the global database object and other global variables
on skript start:
	if {sql} exists:
		clear {sql}
	set {sql} to database "mysql://connection_string"
	updateCache()
	set {enabled} to false
	

on join:
	if {enabled} is true:
		if lookupPlayerByUUID(UUID of player) is not true:
			updateCache()
			if lookupPlayerByUUID(UUID of player) is not true:
				send "You must link your account to play."
				send "Once you have done this, log out and back in to start playing!"
				set player's walk speed to 0
				set player's fly speed to 0
				add player to {frozenPlayerList::*}

on quit:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			set player's walk speed to 0.2
			set player's fly speed to 0.1
			remove player from {frozenPlayerList::*}
			exit all sections

on block damage:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections
			
on build:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on break:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on jump:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on flight toggle:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			wait 1 tick
			push player downward at speed 1
			wait 10 ticks
			push player downward at speed 1

on chat:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on command:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on drop:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on inventory pickup:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on damage of a player:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on death:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on mount:
	loop {frozenPlayerList::*}:
		if loop-value is player:
			cancel event
			exit all sections

on inventory open:
	close player's inventory

command link [<text>] [<text>]:
	permission: skript.integration.link.admin
	description: /link ( check | init | on | off | self )
	trigger:
		arg 1 is "check":
			updateCache()
			if lookupPlayerByUUID(UUID of player) is not true:
				send "User has not linked their account."
			else:
				send "User has linked their account."
			exit
		arg 1 is "init":
			if {sql} exists:
				clear {sql}
			set {sql} to database "mysql://connection_string"
			updateCache()
			exit
		arg 1 is "on":
			set {enabled} to true
			exit
		arg 1 is "off":
			set {enabled} to false
			exit
		arg 1 is "self":
			set player's walk speed to 0
			add player to {frozenPlayerList::*}
			exit
		arg 1 is "help":
			arg 2 is "check":
				send "Checks if your UUID is in the database."
				exit
			arg 2 is "init":
				send "Reinitializes the database connection. Rarely needed."
				exit
			arg 2 is "on":
				send "Enables account linking enforcement."
				exit
			arg 2 is "off":
				send "Disabled account linking enforcement."
				exit
			arg 2 is "self":
				send "Activates enforcement for the user. Log out and back in to remove."
				exit
			arg 2 is "help":
				send "Wait, no, you can't--"
				exit
		arg 1 is set:
			send "Usage: /link ( check | init | on | off | self | help <arg> )"
			exit
		arg 1 is not set:
			send "Usage: /link ( check | init | on | off | self | help <arg> )"
			exit

# Iterate through the playerCache object and return true is UUID is found
function lookupPlayerByUUID(uuid: string) :: boolean:
	loop {playerCache::mc_id::*}:
		if loop-value is {_uuid}:
			return true
	return false

# Grab all UUIDs from db and store in the playerCache object
function updateCache():
	execute "SELECT mc_id FROM users" in {sql} and store the result in {playerCache::*}

My skript is reaching max connections on our MySQL instance. How does one manually manage connections to the DB? `clear`ing the object does not seem to solve the issue. ``` # # Check if the user has linked their Minecraft account # # Initialize the global database object and other global variables on skript start: if {sql} exists: clear {sql} set {sql} to database "mysql://connection_string" updateCache() set {enabled} to false on join: if {enabled} is true: if lookupPlayerByUUID(UUID of player) is not true: updateCache() if lookupPlayerByUUID(UUID of player) is not true: send "You must link your account to play." send "Once you have done this, log out and back in to start playing!" set player's walk speed to 0 set player's fly speed to 0 add player to {frozenPlayerList::*} on quit: loop {frozenPlayerList::*}: if loop-value is player: set player's walk speed to 0.2 set player's fly speed to 0.1 remove player from {frozenPlayerList::*} exit all sections on block damage: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on build: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on break: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on jump: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on flight toggle: loop {frozenPlayerList::*}: if loop-value is player: wait 1 tick push player downward at speed 1 wait 10 ticks push player downward at speed 1 on chat: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on command: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on drop: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on inventory pickup: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on damage of a player: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on death: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on mount: loop {frozenPlayerList::*}: if loop-value is player: cancel event exit all sections on inventory open: close player's inventory command link [<text>] [<text>]: permission: skript.integration.link.admin description: /link ( check | init | on | off | self ) trigger: arg 1 is "check": updateCache() if lookupPlayerByUUID(UUID of player) is not true: send "User has not linked their account." else: send "User has linked their account." exit arg 1 is "init": if {sql} exists: clear {sql} set {sql} to database "mysql://connection_string" updateCache() exit arg 1 is "on": set {enabled} to true exit arg 1 is "off": set {enabled} to false exit arg 1 is "self": set player's walk speed to 0 add player to {frozenPlayerList::*} exit arg 1 is "help": arg 2 is "check": send "Checks if your UUID is in the database." exit arg 2 is "init": send "Reinitializes the database connection. Rarely needed." exit arg 2 is "on": send "Enables account linking enforcement." exit arg 2 is "off": send "Disabled account linking enforcement." exit arg 2 is "self": send "Activates enforcement for the user. Log out and back in to remove." exit arg 2 is "help": send "Wait, no, you can't--" exit arg 1 is set: send "Usage: /link ( check | init | on | off | self | help <arg> )" exit arg 1 is not set: send "Usage: /link ( check | init | on | off | self | help <arg> )" exit # Iterate through the playerCache object and return true is UUID is found function lookupPlayerByUUID(uuid: string) :: boolean: loop {playerCache::mc_id::*}: if loop-value is {_uuid}: return true return false # Grab all UUIDs from db and store in the playerCache object function updateCache(): execute "SELECT mc_id FROM users" in {sql} and store the result in {playerCache::*} ```

nathanpaulyoung commented

2021-09-03 19:34:32 +00:00

(Migrated from github.com)

Looks like the plugin is hardcoded to use 10 connections exactly in the connection pool? If I am correct, could this be a config option?

GovindasOM commented

2021-09-03 20:27:31 +00:00

(Migrated from github.com)

it is not hardcoded, it automatically increases connections on demand

GovindasOM commented

2021-09-03 20:28:30 +00:00

(Migrated from github.com)

connections automatically get closed when they're no longer used

nathanpaulyoung commented

2021-09-07 07:19:51 +00:00

(Migrated from github.com)

Is there anyway to set the maxLifetime to a different duration? We're running into issues with connections living far, far too long, as the Hikari default appears to be 30 mins, while MySQL appears to be 10 mins. We're attempting to increase it on the MySQL side of things for now, but we'd like to be able to change this in a config somewhere, if possible.

GovindasOM commented

2021-09-19 08:53:25 +00:00

(Migrated from github.com)

maxLifetime is your sql server's option I believe, not skript-db

Govindas added the

help wanted

label 2022-03-22 10:21:09 +00:00

Govindas commented

2022-03-22 10:22:50 +00:00

Having thought more about this, it may be a good idea to make it configurable on skript-db side, however I do not have environment in which this could be tested as in my environment I do not get this issue.

Govindas commented

2022-12-08 19:42:18 +00:00

should be fixed in skript-db 1.3.9 (Release soon)

added max-connection-lifetime to config, default 30min
587f303ba5

should be fixed in skript-db 1.3.9 (Release soon) added max-connection-lifetime to config, default 30min https://git.limework.net/Limework/skript-db/commit/587f303ba5d110d9d738bcaa4ed307a63e71b22f

Govindas closed this issue

2022-12-08 19:42:18 +00:00

Sign in to join this conversation.