search

jruby / activerecord-jdbc-adapter

JRuby's ActiveRecord adapter using JDBC.
BSD 2-Clause "Simplified" License
462 stars 387 forks source link

Issues with migrations under Oracle #479

Closed rjordan closed 10 years ago

rjordan commented 10 years ago

We are getting an invalid character exception on Oracle during migrating when it tries to update the SCHEMA_MIGRATIONS table.

ActiveRecord::JDBCError: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
: INSERT INTO "SCHEMA_MIGRATIONS" ("VERSION") VALUES ('20131008181040')arjdbc/jdbc/RubyJdbcConnection.java:597:in `execute_insert'
/home/svcjenkins/.rvm/gems/jruby-1.7.3/gems/activerecord-jdbc-adapter-1.3.1/lib/arjdbc/jdbc/adapter.rb:441:in `exec_insert'
rjordan commented 10 years ago

Forgot to mention. This is under Rails 4.

kares commented 10 years ago

thanks, is this easily reproducable with a new empty (no additional gems/code) rails 4 app ?

rjordan commented 10 years ago

Seems to be yes. Against 11g, ojdbc6.jar and using direct, non-jndi connection. (Don't know if those are relevant, just providing more details)

kares commented 10 years ago

please provide us a deterministic way to reproduce - for now rake db:migrate went fine details :

   (5.0ms)  SELECT "SCHEMA_MIGRATIONS"."VERSION" FROM "SCHEMA_MIGRATIONS" 
   (117.0ms)  CREATE TABLE "MY_POSTS" ("ID" NUMBER(38) NOT NULL PRIMARY KEY, "NAME" VARCHAR2(255), "DESCRIPTION" CLOB, "CREATED_AT" DATE NOT NULL, "UPDATED_AT" DATE NOT NULL) 
   (3.0ms)  CREATE SEQUENCE "MY_POSTS_SEQ" START WITH 10000
  SQL (7.0ms)  INSERT INTO "SCHEMA_MIGRATIONS" ("VERSION") VALUES ('20130928073815')
   (1.0ms)  SELECT "SCHEMA_MIGRATIONS"."VERSION" FROM "SCHEMA_MIGRATIONS" 

used configuration :

development:
  adapter: oracle
  host: 192.168.56.11
  username: SAMPLE
  password: sample

a full (all lines) back-trace might have been useful as well ... thx

tbuehlmann commented 10 years ago

I'm having the exact same problem. Using Rails 4.0.2 having this database.yml:

alpha:
  adapter: jdbc
  driver: oracle.jdbc.driver.OracleDriver
  username: <username>
  password: <password>
  url: "jdbc:oracle:thin:@<host>:<port>:<sid>"
  connection_alive_sql: "SELECT 1 FROM sysibm.tables FETCH FIRST 1 ROWS ONLY"

(forget about connection_alive_sql, that's not correct but was needed.)

We think the problem lies around here: https://github.com/jruby/activerecord-jdbc-adapter/blob/5f773a788a3548d498a9adbd2308051c2f335577/src/java/arjdbc/jdbc/RubyJdbcConnection.java#L665

statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS); don't work for inserts. But statement.executeUpdate(query); works.

Repro: https://github.com/tbuehlmann/oracle_repro

messinadm commented 10 years ago

reproduced this as well. We are new to activerecord and would be willing to provide further documentation to help reproduce, but would need directions on what to provide.

Creation of tables and sequences works for the initial model, but it fails when creating a record in the SCHEMA_MIGRATIONS table.

using rails 4.0.3 activerecord-jdbc-adapter 1.3.6

Here's the trace of rake db:migrate

bundle exec rake db:migrate --trace
** Invoke db:migrate (first_time)                                       
** Invoke environment (first_time)                                      
** Execute environment                                                  
** Invoke db:load_config (first_time)                                   
** Execute db:load_config                                               
** Execute db:migrate                                                   
==  CreateUsers: migrating ====================================================
-- create_table(:users)                                                        
   -> 0.0420s                                                                  
   -> 0 rows                                                                   
==  CreateUsers: migrated (0.0430s) ===========================================

rake aborted!
An error has occurred, all later migrations canceled:

ActiveRecord::JDBCError: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
: INSERT INTO "SCHEMA_MIGRATIONS" ("VERSION") VALUES ('20140311003046')arjdbc/jdbc/RubyJdbcConnection.java:597:in `execute_insert'
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-jdbc-adapter-1.3.6/lib/arjdbc/jdbc/adapter.rb:448:in `exec_insert'
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:435:in `log'
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activesupport-4.0.3/lib/active_support/notifications/instrumenter.rb:20:in `instrument'  
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:430:in `log'
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-jdbc-adapter-1.3.6/lib/arjdbc/jdbc/adapter.rb:448:in `exec_insert'          
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-jdbc-adapter-1.3.6/lib/arjdbc/oracle/adapter.rb:565:in `exec_insert'        
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-jdbc-adapter-1.3.6/lib/arjdbc/oracle/adapter.rb:555:in `insert'             
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/relation.rb:76:in `insert'                          
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/persistence.rb:509:in `create_record'               
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/attribute_methods/dirty.rb:78:in `create_record'    
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/callbacks.rb:306:in `create_record'                 
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activesupport-4.0.3/lib/active_support/callbacks.rb:376:in `_run__1399447804__create__callbacks'
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activesupport-4.0.3/lib/active_support/callbacks.rb:80:in `run_callbacks'                       
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/callbacks.rb:306:in `create_record'                        
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/timestamp.rb:57:in `create_record'                         
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/persistence.rb:477:in `create_or_update'                   
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/callbacks.rb:302:in `create_or_update'                     
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activesupport-4.0.3/lib/active_support/callbacks.rb:376:in `_run__1399447804__save__callbacks'  
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activesupport-4.0.3/lib/active_support/callbacks.rb:80:in `run_callbacks'                       
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/callbacks.rb:302:in `create_or_update'                     
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/persistence.rb:128:in `save!'                              
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/validations.rb:57:in `save!'                               
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/attribute_methods/dirty.rb:41:in `save!'                   
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/transactions.rb:275:in `save!'                             
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/transactions.rb:326:in `with_transaction_returning_status' 
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `transaction'
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/connection_adapters/abstract/database_statements.rb:210:in `within_new_transaction'
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `transaction'           
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/transactions.rb:209:in `transaction'                                               
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/transactions.rb:323:in `with_transaction_returning_status'                         
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/transactions.rb:275:in `save!'                                                     
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/validations.rb:41:in `create!'                                                     
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:990:in `record_version_state_after_migrating'                         
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:960:in `execute_migration_in_transaction'                             
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:1007:in `ddl_transaction'                                             
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:958:in `execute_migration_in_transaction'                             
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:920:in `migrate'                                                      
org/jruby/RubyArray.java:1613:in `each'                                                                                                                                                
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:916:in `migrate'                                                      
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:764:in `up'                                                           
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/migration.rb:742:in `migrate'                                                      
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/activerecord-4.0.3/lib/active_record/railties/databases.rake:42:in `(root)'                                             
org/jruby/RubyProc.java:271:in `call'                                                                                                                                                  
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/task.rb:236:in `execute'                                                                           
org/jruby/RubyArray.java:1613:in `each'                                                                                                                                                
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/task.rb:231:in `execute'                                                                           
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/task.rb:175:in `invoke_with_call_chain'                                                            
/home/eagles/.rbenv/versions/jruby-1.7.5/lib/ruby/1.9/monitor.rb:211:in `mon_synchronize'                                                                                              
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/task.rb:168:in `invoke_with_call_chain'                                                            
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/task.rb:161:in `invoke'                                                                            
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:149:in `invoke_task'                                                                
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:106:in `top_level'                                                                  
org/jruby/RubyArray.java:1613:in `each'                                                                                                                                                
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:106:in `top_level'                                                                  
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:115:in `run_with_threads'                                                           
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:100:in `top_level'                                                                  
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:78:in `run'                                                                         
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:165:in `standard_exception_handling'                                                
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/lib/rake/application.rb:75:in `run'                                                                         
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/gems/rake-10.1.1/bin/rake:33:in `(root)'                                                                                     
org/jruby/RubyKernel.java:1101:in `load'                                                                                                                                               
/home/eagles/.workspace/cmsreporting.git/vendor/jruby/1.9/bin/rake:23:in `(root)'                                                                                                      
Tasks: TOP => db:migrate
kares commented 10 years ago

unfortunately I have failed reproducing twice (on my setup) ... I'm not sure but I would guess this relates to certain driver/oracle versions (would be great to have those mentioned here just in case). which seems like super "time gready" to fix if I need to install the beast once again ;( ... I'm sorry but it will likely take me a while to take a deep look into this again. so please anyone seeing this go for it ...

messinadm commented 10 years ago

I'd love to provide help get this fixed, just need some direction. Here's some info from my setup:

Java(TM) SE Runtime Environment (build 1.8.0-b132) Java HotSpot(TM) 64-Bit Server VM (build 25.0-b70, mixed mode) * * *had a 1.7 version before that also had the same issue

jruby 1.7.9 (1.9.3p392) 2013-12-06 87b108a on Java HotSpot(TM) 64-Bit Server VM 1.8.0-b132 +indy [linux-amd64] * * *confirmed same issue with 1.7.5

Oracle: 11.2.0.3 54c41acb9df6465f45a931fbe9734e1a /home/eagles/.rbenv/versions/jruby-1.7.9/lib/ojdbc6.jar

_Unit Tests_

[eagles@localhost activerecord-jdbc-adapter]$ rake appraisal:rails41 test_oracle 
>> BUNDLE_GEMFILE=/home/eagles/.workspace/jruby/activerecord-jdbc-adapter/gemfiles/rails41.gemfile bundle exec /home/eagles/.rbenv/versions/jruby-1.7.9/bin/rake test_oracle
Using ActiveRecord::VERSION = 4.1.0.rc1                                                                                                                                     
Loaded suite /home/eagles/.rbenv/versions/jruby-1.7.9/lib/ruby/gems/shared/gems/rake-10.1.1/lib/rake/rake_test_loader                                                       
Started                                                                                                                                                                     

[cut details]

Finished in 138.758 seconds.

165 tests, 245 assertions, 4 failures, 47 errors, 0 pendings, 2 omissions, 0 notifications
70.5521% passed

1.19 tests/s, 1.77 assertions/s

Exception: java.lang.ThreadDeath thrown from the UncaughtExceptionHandler in thread "Thread-7"
rake aborted!
1
org/jruby/RubyProc.java:271:in `call'
org/jruby/RubyProc.java:271:in `call'
org/jruby/RubyArray.java:1613:in `each'
org/jruby/RubyArray.java:1613:in `each'
org/jruby/RubyKernel.java:1099:in `load'
Tasks: TOP => test_oracle

Application Bundle

[eagles@localhost cmsreporting.git]$ bundle show
Gems included by the bundle:
  * actionmailer (4.0.3)
  * actionpack (4.0.3)
  * activemodel (4.0.3)
  * activerecord (4.0.3)
  * activerecord-deprecated_finders (1.0.3)
  * activerecord-jdbc-adapter (1.3.8.dev b93e786)
  * activesupport (4.0.3)
  * arel (4.0.2)
  * builder (3.1.4)
  * bundler (1.6.2)
  * coffee-rails (4.0.1)
  * coffee-script (2.2.0)
  * coffee-script-source (1.7.0)
  * diff-lcs (1.2.5)
  * erubis (2.7.0)
  * execjs (2.0.2)
  * hike (1.2.3)
  * i18n (0.6.9)
  * jbuilder (1.5.3)
  * jquery-rails (3.1.0)
  * json (1.8.1)
  * mail (2.5.4)
  * mime-types (1.25.1)
  * minitest (4.7.5)
  * multi_json (1.9.2)
  * polyglot (0.3.4)
  * rack (1.5.2)
  * rack-test (0.6.2)
  * rails (4.0.3)
  * railties (4.0.3)
  * rake (10.2.2)
  * rdoc (4.1.1)
  * rspec-core (2.13.1)
  * rspec-expectations (2.13.0)
  * rspec-mocks (2.13.1)
  * rspec-rails (2.13.1)
  * sass (3.2.19)
  * sass-rails (4.0.3)
  * sdoc (0.4.0)
  * sprockets (2.11.0)
  * sprockets-rails (2.0.1)
  * therubyrhino (2.0.3)
  * therubyrhino_jar (1.7.4)
  * thor (0.19.1)
  * thread_safe (0.3.3)
  * tilt (1.4.1)
  * treetop (1.4.15)
  * turbolinks (2.2.2)
  * tzinfo (0.3.39)
  * uglifier (2.5.0)
messinadm commented 10 years ago

Found the error occurring at the same place as tbuehlmann above. I've captured the packet data sent across the wire below. The part i can read says "INSERT INTO "SCHEMA_MIGRATIONS" ("VERSION") VALUES ('20140310003046') RETURNING ROWID INTO ?"

00:b5:00:00:06:00:00:00:00:00:11:69:00:01:01:01:01:02:03:5e:00:02:04:29:00:01:01:5c:01:01:0d:00:00:00:00:04:7f:ff:ff:ff:01:01:01:00:00:00:00:00:00:00:00:00:01:00:00:00:00:00:49:4e:53:45:52:54:20:49:4e:54:4f:20:22:53:43:48:45:4d:41:5f:4d:49:47:52:41:54:49:4f:4e:53:22:20:28:22:56:45:52:53:49:4f:4e:22:29:20:56:41:4c:55:45:53:20:28:27:32:30:31:34:30:33:31:31:30:30:33:30:34:36:27:29:20:52:45:54:55:52:4e:49:4e:47:20:52:4f:57:49:44:20:49:4e:54:4f:20:3f:01:01:01:01:00:00:00:00:00:00:00:00:00:00:00:0b:03:00:00:01:82:00:00:00:00:01:b2:01:00:07:00

There's a bit of back and fourth after the initial request followed by the ORA-009. 87 -> Request 88 <- 89 <- 90 -> 91 -> 92 -> ORA-009

yahonda commented 10 years ago

Hi, Oracle JDBC thin driver handles every character set as Unicode, If your database character set is not compatible with them, it may cause this kind of ORA-00904 error.

Would you provide this query output?, It requires DBA or select any dictionary privileges to execute.

SQL> select * from v$nls_parameters
  2  where parameter = 'NLS_CHARACTERSET'
  3  ;

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
NLS_CHARACTERSET
AL32UTF8                                                                  0

SQL>
messinadm commented 10 years ago

Our result: NLS_CHARACTERSET WE8MSWIN1252

We'll check to see if another characterset solves the issue. Thanks.

chrismoos commented 10 years ago

The fix for this is to add the following into your database config (where you put your url, adapter, etc,.):

statement_escape_processing: true

I believe the problem is because without the escape processing enabled, returning the generated key fails (probably due to the bind var):

RETURNING ROWID INTO ?

A permanent fix might be to enable this config option by default for Oracle, so you don't need to set it explicitly (assuming there are no ill effects). Possibly its a bug with the Oracle driver handling RETURN_GENERATED_KEYS with escape processing disabled.

messinadm commented 10 years ago

statement_escape_processing: true Adding the line above does resolve the issue.

Here's my database.yml

development:
  adapter: jdbc
  driver: oracle.jdbc.OracleDriver
  url: "jdbc:oracle:thin:@<host>:<port>:<sid>"
  username: <username>
  password: <password>
  statement_escape_processing: true