I want to try get Rails and MSSQL talking within our CI tool, CircleCI. I work on a mac, so setting this up was fairly pain free. I installed FreeTDS locally using Homebrew. Then I spawned up a MSSQL docker image and set my config in my rails app to point to it and that all worked.
CircleCI is a little different.
I have done the following.
In my gemfile added:
# mssql database gems
gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'
Modified our database.yml file with:
default: &default
adapter: sqlserver
pool: 5
encoding: utf8
mode: dblib
development:
<<: *default
host: localhost
database: collections_development
username: sa
password: P@55w0rd
test:
<<: *default
host: localhost
database: collections_test
username: sa
password: P@55w0rd
I built a docker image with:
FROM circleci/ruby:2.4.1-node-browsers
RUN set -ex \
&& sudo apt-get install build-essential \
&& sudo apt-get install libc6-dev \
&& sudo wget -O freetds-1.00.54.tar.gz "ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.00.54.tar.gz" \
&& sudo tar -xzvf freetds-1.00.54.tar.gz \
&& cd freetds-1.00.54 \
&& sudo ./configure \
&& sudo make \
&& sudo make install
This image is hosted on docker hub here.
Next I built the CircleCI config:
version: 2
jobs:
build:
working_directory: ~/collections-api
docker:
- image: legendaryrob/ruby:2.4.1-node-browser-freetds54-lib6c
environment:
RAILS_ENV: test
- image: microsoft/mssql-server-linux
environment:
ACCEPT_EULA: Y
SA_PASSWORD: P@55w0rd
environment:
RAILS_ENV: test
steps:
- checkout
- restore_cache:
name: Restore bundle cache
keys:
- collections-api-bundle-{{ checksum "Gemfile.lock" }}
- run:
name: Allow CI to read from pdg gemserver
command: bundle config https://gems.pdg.io/ $GEMS_USERNAME:$GEMS_PASSWORD
- run:
name: Bundle Install
command: bundle install --path vendor/bundle --jobs 4 --retry 3
- save_cache:
name: Store bundle cache
key: collections-api-bundle-{{ checksum "Gemfile.lock" }}
paths:
- vendor/bundle
- run:
name: Wait for DB
command: dockerize -wait tcp://localhost:1433 -timeout 1m
- run:
name: test tds connection
command: tsql -C
# Run setup
- run: ./spec/setup/circle_setup.sh
- run:
name: Create the database
command: bundle exec rake -v --trace db:create
- run:
name: Migrate the database
command: bundle exec rake -v --trace db:migrate
- run:
name: Check environment variables are present
command: bundle exec rake env_variables:check
- run:
name: Run tests
command: bundle exec rspec
The MSSQL docker image I am getting from Microsoft's docker hub account directly -> https://hub.docker.com/r/microsoft/mssql-server-linux/
Here you can see a couple things. I set ruby box I built with TDS installed and all the dependancies needed. I ensured that we are using the same version of TDS that we are using locally, because it is working locally, I have also tried a few of the other versions just incase I was dealing with a flakey version.
I also ensure that we can connect to the MSSQL docker box and ensure that we have the right tds config:
- run:
name: Wait for DB
command: dockerize -wait tcp://localhost:1433 -timeout 1m
- run:
name: test tds connection
command: tsql -C
This will result in the following:
#!/bin/bash -eo pipefail
dockerize -wait tcp://localhost:1433 -timeout 1m
enter code here
2017/09/01 09:25:40 Waiting for: tcp://localhost:1433
2017/09/01 09:25:40 Problem with dial: dial tcp 127.0.0.1:1433:
getsockopt: connection refused. Sleeping 1s
2017/09/01 09:25:41 Problem with dial: dial tcp 127.0.0.1:1433:
getsockopt: connection refused. Sleeping 1s
2017/09/01 09:25:42 Problem with dial: dial tcp 127.0.0.1:1433:
getsockopt: connection refused. Sleeping 1s
2017/09/01 09:25:48 Connected to tcp://localhost:1433
and
#!/bin/bash -eo pipefail
tsql -C
[TinyTds][v2.0.0][tsql]: /usr/local/bin/tsql
Compile-time settings (established with the "configure" script)
Version: freetds v1.00.54
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: auto
iODBC: no
unixodbc: no
SSPI "trusted" logins: no
Kerberos: no
OpenSSL: yes
GnuTLS: no
MARS: no
But then I get timeouts. These timeouts are intermittent, they will sometimes occur when we are trying to connect to localhost:1433
But mostly it occurs when we get to the rake tasks.
Below is the stack trace I am getting, I have tried a few different versions of MSSQL docker as well with no effect. If anyone has tried doing this please let me know I would really appreciate some help around doing this.
#!/bin/bash -eo pipefail
bundle exec rake -v --trace db:create
** Invoke db:create (first_time)
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:create
TinyTds::Error: Adaptive Server connection timed out: CREATE DATABASE [collections_test]
Couldn't create database for {"adapter"=>"sqlserver", "pool"=>5, "encoding"=>"utf8", "mode"=>"dblib", "host"=>"localhost", "database"=>"collections_test", "username"=>"sa", "password"=>"P@55w0rd"}
rake aborted!
ActiveRecord::StatementInvalid: TinyTds::Error: Adaptive Server connection timed out: CREATE DATABASE [collections_test]
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:274:in `do'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:274:in `raw_connection_do'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:215:in `block in do_execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:612:in `block (2 levels) in log'
/usr/local/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:611:in `block in log'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activesupport-5.1.3/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:603:in `log'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:215:in `do_execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_tasks.rb:10:in `create_database'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/tasks/sqlserver_database_tasks.rb:22:in `create'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:117:in `create'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:137:in `block in create_current'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:304:in `block in each_current_configuration'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:303:in `each'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:303:in `each_current_configuration'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:136:in `create_current'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/railties/databases.rake:27:in `block (2 levels) in <top (required)>'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:250:in `block in execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:250:in `each'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:250:in `execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:194:in `block in invoke_with_call_chain'
/usr/local/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:187:in `invoke_with_call_chain'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:180:in `invoke'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:152:in `invoke_task'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:108:in `block (2 levels) in top_level'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:108:in `each'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:108:in `block in top_level'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:117:in `run_with_threads'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:102:in `top_level'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:80:in `block in run'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:178:in `standard_exception_handling'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:77:in `run'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/exe/rake:27:in `<top (required)>'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/bin/rake:23:in `load'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/bin/rake:23:in `<top (required)>'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli/exec.rb:74:in `load'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli/exec.rb:74:in `kernel_load'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli/exec.rb:27:in `run'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli.rb:362:in `exec'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor/invocation.rb:126:in `invoke_command'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor.rb:387:in `dispatch'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli.rb:22:in `dispatch'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor/base.rb:466:in `start'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli.rb:13:in `start'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/exe/bundle:30:in `block in <top (required)>'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/friendly_errors.rb:121:in `with_friendly_errors'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/exe/bundle:22:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'
TinyTds::Error: Adaptive Server connection timed out
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:274:in `do'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:274:in `raw_connection_do'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:215:in `block in do_execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:612:in `block (2 levels) in log'
/usr/local/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:611:in `block in log'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activesupport-5.1.3/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:603:in `log'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:215:in `do_execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/connection_adapters/sqlserver/database_tasks.rb:10:in `create_database'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-sqlserver-adapter-5.1.1/lib/active_record/tasks/sqlserver_database_tasks.rb:22:in `create'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:117:in `create'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:137:in `block in create_current'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:304:in `block in each_current_configuration'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:303:in `each'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:303:in `each_current_configuration'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/tasks/database_tasks.rb:136:in `create_current'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.3/lib/active_record/railties/databases.rake:27:in `block (2 levels) in <top (required)>'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:250:in `block in execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:250:in `each'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:250:in `execute'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:194:in `block in invoke_with_call_chain'
/usr/local/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:187:in `invoke_with_call_chain'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/task.rb:180:in `invoke'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:152:in `invoke_task'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:108:in `block (2 levels) in top_level'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:108:in `each'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:108:in `block in top_level'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:117:in `run_with_threads'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:102:in `top_level'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:80:in `block in run'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:178:in `standard_exception_handling'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/lib/rake/application.rb:77:in `run'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/gems/rake-12.0.0/exe/rake:27:in `<top (required)>'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/bin/rake:23:in `load'
/home/circleci/collections-api/vendor/bundle/ruby/2.4.0/bin/rake:23:in `<top (required)>'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli/exec.rb:74:in `load'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli/exec.rb:74:in `kernel_load'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli/exec.rb:27:in `run'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli.rb:362:in `exec'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor/invocation.rb:126:in `invoke_command'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor.rb:387:in `dispatch'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli.rb:22:in `dispatch'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/vendor/thor/lib/thor/base.rb:466:in `start'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/cli.rb:13:in `start'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/exe/bundle:30:in `block in <top (required)>'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/lib/bundler/friendly_errors.rb:121:in `with_friendly_errors'
/usr/local/lib/ruby/gems/2.4.0/gems/bundler-1.15.4/exe/bundle:22:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'
Tasks: TOP => db:create
Exited with code 1
Note: The reason we are needing to connect to MSSQL is because we are going to be writing to the db for a short time, we have another team who relies on Access that is using MSSQL. I am just hoping there is someone else out there who has fought this beast and won.
EDIT: freetds.conf
# $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".
# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
tds version = auto
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512
# If you experience TLS handshake errors and are using openssl,
# try adjusting the cipher list (don't surround in double or single quotes)
# openssl ciphers = HIGH:!SSLv2:!aNULL:-DH
# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0
# A typical Microsoft server
[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 7.0