TinyTds - Adaptive Server connection timed out

[yozart]

Hello Guys,

I'm facing issues with search features that throws 500 Internal error quiet often. I'm connected to a SQL server installed on an external machine.

I, [2024-09-19T12:09:29.762648 #129] INFO -- : Started GET "/projects/it14/search?utf8=%E2%9C%93&scope=&issues=1&q=pr%C3%A9l%C3%A8vement" for 10.148.5.14 at 2024-09-19 12:09:29 +0000
I, [2024-09-19T12:09:29.764138 #129] INFO -- : Processing by SearchController#index as HTML
I, [2024-09-19T12:09:29.764305 #129] INFO -- : Parameters: {"utf8"=>"✓", "scope"=>"", "issues"=>"1", "q"=>"prélèvement", "id"=>"it14"}
I, [2024-09-19T12:09:29.777268 #129] INFO -- : Current user: yozart (id=6)
I, [2024-09-19T12:09:35.608093 #129] INFO -- : Completed 500 Internal Server Error in 5844ms (ActiveRecord: 5821.0ms | Allocations: 6746)
F, [2024-09-19T12:09:35.610621 #129] FATAL -- : ActiveRecord::StatementTimeout (TinyTds::Error: Adaptive Server connection timed out):

I tried to add REDMINE_DB_TIMEOUT: 60 in my docker compose without success.

Any idea what I shall do to avoid such issue ?

my docker compose :

  redmine:
    image: redmine:5.1.3
    container_name: redmine
    restart: always
    environment:
      REDMINE_DB_ADAPTER: "sqlserver"
      REDMINE_DB_DATABASE: "REDMINE"
      REDMINE_DB_SQLSERVER: "my sql server ip"
      REDMINE_DB_PORT: "1433"
      REDMINE_DB_USERNAME: "user"
      REDMINE_DB_PASSWORD: "password"
      REDMINE_PLUGINS_MIGRATE: true
      REDMINE_DB_TIMEOUT: 60
    ports:
      - "3000:3000"
    volumes:
      - REDMINE_DATA:/usr/src/redmine/files
      - REDMINE_PLUGINS:/usr/src/redmine/plugins
      - /var/lib/docker/volumes/REDMINE_CONF/configuration.yml:/usr/src/redmine/config/configuration.yml
    networks:
      - common_network
    entrypoint: ["sh", "-c", "chmod -R o-w \"$$GEM_HOME/gems\" && chmod -R o-w \"$$GEM_HOME/extensions\" && apt-get update && apt-get install -y shared-mime-info && /docker-entrypoint.sh rails server -b 0.0.0.0"]

PS: SQL Server version is 2019

[tianon]

There's nothing useful in your SQL Server logs? The SQL Server connection is definitely otherwise working?

I'm not super familiar with either SQL Server or Redmine's usage of SQL TBH, but search functionality is typically very taxing on the database. Do you perhaps have a lot of data it's trying to search through? Maybe the search query doesn't have a good enough database index?

[yozart]

Yes, the connection is working great for everything else. Only issue is with the search. But the timeout seems to be 1 or 2 seconds maxi.
I'm having now 50000+ issues in my database with a lot of custom fields, a lot of messages in each issue. It can take some time and I'd like to increase this timeout to 30 seconds.

Is it feasible somehow ?

Adding indexes can be a solution but not sure it will fix my problem. Moreover, adding indexes deteriorates insert/update/delete operations. Search is not very common but I need to get an answer even if it's longer than just a second.

[tianon]

Oh, I see what's up -- REDMINE_DB_TIMEOUT isn't a variable we actually support/use! So for this use case, you'll probably want to stop using our code to create config/database.yml and create/mount it in yourself:

redmine/docker-entrypoint.sh

Lines 59 to 132 in 23cb85e

	if [ ! -f './config/database.yml' ]; then
	file_env 'REDMINE_DB_MYSQL'
	file_env 'REDMINE_DB_POSTGRES'
	file_env 'REDMINE_DB_SQLSERVER'
	if [ "$MYSQL_PORT_3306_TCP" ] && [ -z "$REDMINE_DB_MYSQL" ]; then
	export REDMINE_DB_MYSQL='mysql'
	elif [ "$POSTGRES_PORT_5432_TCP" ] && [ -z "$REDMINE_DB_POSTGRES" ]; then
	export REDMINE_DB_POSTGRES='postgres'
	fi
	if [ "$REDMINE_DB_MYSQL" ]; then
	adapter='mysql2'
	host="$REDMINE_DB_MYSQL"
	file_env 'REDMINE_DB_PORT' '3306'
	file_env 'REDMINE_DB_USERNAME' "${MYSQL_ENV_MYSQL_USER:-root}"
	file_env 'REDMINE_DB_PASSWORD' "${MYSQL_ENV_MYSQL_PASSWORD:-${MYSQL_ENV_MYSQL_ROOT_PASSWORD:-}}"
	file_env 'REDMINE_DB_DATABASE' "${MYSQL_ENV_MYSQL_DATABASE:-${MYSQL_ENV_MYSQL_USER:-redmine}}"
	file_env 'REDMINE_DB_ENCODING' ''
	elif [ "$REDMINE_DB_POSTGRES" ]; then
	adapter='postgresql'
	host="$REDMINE_DB_POSTGRES"
	file_env 'REDMINE_DB_PORT' '5432'
	file_env 'REDMINE_DB_USERNAME' "${POSTGRES_ENV_POSTGRES_USER:-postgres}"
	file_env 'REDMINE_DB_PASSWORD' "${POSTGRES_ENV_POSTGRES_PASSWORD}"
	file_env 'REDMINE_DB_DATABASE' "${POSTGRES_ENV_POSTGRES_DB:-${REDMINE_DB_USERNAME:-}}"
	file_env 'REDMINE_DB_ENCODING' 'utf8'
	elif [ "$REDMINE_DB_SQLSERVER" ]; then
	adapter='sqlserver'
	host="$REDMINE_DB_SQLSERVER"
	file_env 'REDMINE_DB_PORT' '1433'
	file_env 'REDMINE_DB_USERNAME' ''
	file_env 'REDMINE_DB_PASSWORD' ''
	file_env 'REDMINE_DB_DATABASE' ''
	file_env 'REDMINE_DB_ENCODING' ''
	else
	echo >&2
	echo >&2 'warning: missing REDMINE_DB_MYSQL, REDMINE_DB_POSTGRES, or REDMINE_DB_SQLSERVER environment variables'
	echo >&2
	echo >&2 '*** Using sqlite3 as fallback. ***'
	echo >&2
	adapter='sqlite3'
	host='localhost'
	file_env 'REDMINE_DB_PORT' ''
	file_env 'REDMINE_DB_USERNAME' 'redmine'
	file_env 'REDMINE_DB_PASSWORD' ''
	file_env 'REDMINE_DB_DATABASE' 'sqlite/redmine.db'
	file_env 'REDMINE_DB_ENCODING' 'utf8'
	mkdir -p "$(dirname "$REDMINE_DB_DATABASE")"
	if [ "$(id -u)" = '0' ]; then
	find "$(dirname "$REDMINE_DB_DATABASE")" \! -user redmine -exec chown redmine '{}' +
	fi
	fi
	REDMINE_DB_ADAPTER="$adapter"
	REDMINE_DB_HOST="$host"
	echo "$RAILS_ENV:" > config/database.yml
	for var in \
	adapter \
	host \
	port \
	username \
	password \
	database \
	encoding \
	; do
	env="REDMINE_DB_${var^^}"
	val="${!env}"
	[ -n "$val" ] || continue
	echo " $var: \"$val\"" >> config/database.yml
	done
	fi

If you docker exec redmine-container cat config/database.yml, that should give you a good starting point -- you'll then be able to add timeout: 60 to it, and mount it into the container at that same location (and the if in the entrypoint script will make sure it's no longer generated). That would also mean you can drop the related environment variables, but they should be harmless either way.

[yozart]

I've just tried it and indeed it worked great with
timeout: 60

I had to change my docker compose file, here what it looks like for people having same issue :
I removed the environment variables to declare the DB and created a database.yml file manually

redmine:
image: redmine:5.1.3
container_name: redmine_qa
restart: always
environment:
REDMINE_PLUGINS_MIGRATE: true
ports:
- "3300:3000"
volumes:
- REDMINE_QA_DATA:/usr/src/redmine/files
- REDMINE_QA_PLUGINS:/usr/src/redmine/plugins
- /var/lib/docker/volumes/REDMINE_QA_CONF/database.yml:/usr/src/redmine/config/database.yml
networks:
- common_network
entrypoint: ["sh", "-c", "chmod -R o-w "$$GEM_HOME/gems" && chmod -R o-w "$$GEM_HOME/extensions" && apt-get update && apt-get install -y shared-mime-info && /docker-entrypoint.sh rails server -b 0.0.0.0"]
volumes:
REDMINE_QA_DATA:
external: true
REDMINE_QA_PLUGINS:
external: true