POUWIEL|COM

After several mysterious data disappearances in MySQL, I finally had enough and went on to configure RoR to use a remote Oracle db installation.
Here’s what’s what:

Versions:
Rails 4.0.5
ruby 2.0.0p481 (2014-05-08 revision 45883) [x86_64-linux]

Gemfile:
gem 'ruby-oci8', '~> 2.1.5'
gem "activerecord-oracle_enhanced-adapter", "~> 1.5.0"

database.yml
development:
  adapter: oracle_enhanced
  encoding: utf8
  database: 
  username: 
  password: 

.bashrc
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export TNS_ADMIN=/usr/lib/oracle/12.1/client64/tns
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
export PATH="$PATH:$HOME/.rvm/bin:$LD_LIBRARY_PATH:$ORACLE_HOME/bin:$TNS_ADMIN" # Add RVM to PATH for scripting
[[ -s "$HOME/.rvm/scripts/rvm" ]] && source "$HOME/.rvm/scripts/rvm"

After several tries to catch the culprit, I was not able to figure out what was going on, it even got to the point where I created a fresh user after a

rake db:refresh

and after a quick bite to eat my user was gone!.

Troubleshooting:
First things first: Can you connect to the db via sqlplus!?
Can Ruby connect to the database?:

ruby -rubygems -e "require 'oci8'; OCI8.new('','','').exec('select * from dual') do |r| puts r.join(','); end"

When you have set the NLS_LANG parameter, you should just get ‘X’ as output.

Working through the excellent rails tutorial by Michael Hartl, somewhere around the end chapter 9 I came across some unusual behavior of (what I suspect) Ruby/Rails. My data, which is committed to a MySQL database, kept disappearing.
Whenever a change was made to any of the spec or controller files, all my users would be gone 🙁
To get a grip on who the culprit is, I went off to create some triggers who would log what happened and when:

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, logger_outstring  VARCHAR(100)
, creation_date     DATETIME
, last_update_date  DATETIME) ENGINE=MyISAM;

DELIMITER $
CREATE TRIGGER trg_users_insert
AFTER INSERT ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'insert'
           ,'users'
           , new.email
           , null
           , new.created_at
           , null );
END$

CREATE TRIGGER trg_users_update
AFTER update ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'update'
           ,'users'
           , old.email
           , new.email
           , old.updated_at
           , new.updated_at );	
END$

CREATE TRIGGER trg_users_delete
before delete ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'delete'
           ,'users'
           , old.email
           , null
           , null
           , current_timestamp );	
END$

Not the most sophisticated piece of code ever, but let’s see if it does the trick…