(oracle) error when rh inserts a record into its own scriptsrun table

[wijnanjo]

We have one .sql file that rh completely executes but right after rh fails to insert that script's record in the SCRIPTSRUN table (Error is below). However, all other scripts are inserted nicely in that same table and moreover.when I split that .sql file in two, they get both inserted into the table. What is going on here?

2011-11-30 09:32:19,943 [INFO ] - Running 0005_Indexes.sql on localhost - .
2011-11-30 09:32:20,033 [ERROR] - roundhouse.databases.oracle.OracleDatabase with provider System.Data.OracleClient does not provide a facility for recording scripts run at this time.
could not insert: [roundhouse.model.ScriptsRun#5][SQL: INSERT INTO PRODATA_ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)]
2011-11-30 09:32:20,055 [ERROR] - RoundhousE encountered an error.
NHibernate.Exceptions.GenericADOException: could not insert: [roundhouse.model.ScriptsRun#5][SQL: INSERT INTO PRODATA_ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)] ---> System.Data.OracleClient.OracleException: ORA-01461: can bind a LONG value only for insert into a LONG column

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Action.EntityInsertAction.Execute()
at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
at NHibernate.Engine.ActionQueue.ExecuteActions()
at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)
at NHibernate.Impl.SessionImpl.Flush()
at roundhouse.infrastructure.persistence.Repository.save_or_update[T](T item)
at roundhouse.databases.DefaultDatabase`1.insert_script_run(String script_name, String sql_to_run, String sql_to_run_hash, Boolean run_this_script_once, Int64 version_id)
at roundhouse.migrators.DefaultDatabaseMigrator.record_script_in_scripts_run_table(String script_name, String sql_to_run, Boolean run_this_script_once, Int64 version_id)
at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String sql_to_run, String script_name, Boolean run_this_script_once, Boolean run_this_script_every_time, Int64 version_id, Environment environment, String repository_version, String repository_path, ConnectionType connection_type)
at roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(String directory, Int64 version_id, MigrationsFolder migration_folder, Environment migrating_environment, String repository_version, ConnectionType connection_type)
at roundhouse.runners.RoundhouseMigrationRunner.log_and_traverse(MigrationsFolder folder, Int64 version_id, String new_version, ConnectionType connection_type)
at roundhouse.runners.RoundhouseMigrationRunner.run()

[ferventcoder]

size of the script text?

[wijnanjo]

Hi,

It's just about 50-60 lines of 'CREATE INDEX ...' statements like the ones
below:

--table people
CREATE INDEX my_index1 ...
CREATE INDEX my_index ...
;
--table addresses
CREATE INDEX...
;
--etc.

The problem is indeed related with the number of lines or statements in the
script because splitting that script decreases the number of lines/script.
On the other hand our 'create tables' script has a lot more lines (less
create statements though) and just works fine.

hope that helps you, greets,

Jo

2011/11/30 Rob Reynolds <
reply@reply.github.com

size of the script text?

---

Reply to this email directly or view it on GitHub:
#37 (comment)

[ferventcoder]

While this shouldn't be considered a fix for the issue, have you thought about moving your index items to the new indexes folder?

[wijnanjo]

I have exactly the same problem if I put the script in the indexes folder:
one 'big' script fails, split into two smaller ones is fine.

2011/11/30 Rob Reynolds <
reply@reply.github.com

While this shouldn't be considered a fix for the issue, have you thought
about moving your index items to the new indexes folder?

---

Reply to this email directly or view it on GitHub:
#37 (comment)

[wimjans]

I've done some testing, and apparently the error occurs (on any of the folders) if the size of an entry being inserted into a CLOB column is between 2000 and 4000 characters.

This happen on all tables which have a CLOB column, be it in scriptruns or scriptrunerrors.

It seems like this is an issues with NHibernate and the default driver used.

I've found one website explaining this issue:
http://thebasilet.blogspot.com/2009/07/nhibernate-oracle-clobs.html

One solution is to use a different driver:
"NHibernate.Driver.OracleDataClientDriver instead of
NHibernate.Driver.OracleClientDriver"

Can RoundHousE be adapated so we can choose a different driver to be used with NHibernate?

Thanks,

Wim

[ferventcoder]

Interesting. I bet it could be adapted. This would likely solve some other issues as well.

[Buthrakaur]

Is there any special reason why this fix wasn't merged? I'm still facing the same ORA error on latest RoundhousE version grabbed from CodeBetter build server.

[mpareja]

@Buthrakaur It has been a while since the last RH release, so we're working to get a new release out soon. We've made some changes to our build infrastructure with the hope that we will be able to release more frequently in the future.

There are some mapping related changes in the release, so it might be a good idea to sneak your pull request #83 in.

[Buthrakaur]

@mpareja Thanks for quick response. I just tried to merge #83 into my own fork (https://github.com/Buthrakaur/roundhouse), but I ended with much more cryptic ORA error unfortunately:

INSERT INTO X__RH_SCRIPTS_RUN (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)] ---> System.Data.OracleClient.OracleException: ORA-01084: invalid argument in OCI call

There're quite a lot of issues with CLOB/BLOB columns and Microsoft Oracle driver AFAIK. :/

[mpareja]

Ouch, maybe we won't sneak this one in, yet!

[Buthrakaur]

I'm afraid there is just one right fix for Oracle - migrate from System.Data.OracleClient provider to Oracle's own Oracle.DataAccess (ODP.NET), which is much more mature according to my experience. I wonder if anybody really use RoundhousE with Oracle when I see such errors...

[ferventcoder]

We are getting rid of the microsoft driver

[ferventcoder]

There was another driver suggested that doesn't even require the oracle client tools and I would lean towards that.

[Buthrakaur]

You mean probably Oracle Managed Driver (https://nuget.org/packages/odp.net.managed/) - I think it doesn't work with NH yet (it's probably .NET 4 only), but I'll give it a try.

On the other side I easily managed to overcome the issues I mentioned here in my fork using ODP.NET driver (Oracle.DataAccess) instead of the MS one, but it requires Oracle client installation of course...

[Buthrakaur]

I just got the managed driver working and fixed some Oracle specific SQL syntax problems in my fork: Buthrakaur@b48e1f9

[ferventcoder]

the managed driver? With NH? That's pretty rockstar!

[mpareja]

Were you required to upgrade RH to .NET 4 in order to sort the driver issue
out?

On Thu, Jun 20, 2013 at 11:01 AM, Rob Reynolds notifications@github.comwrote:

the managed driver? With NH? That's pretty rockstar!

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/37#issuecomment-19758967
.

[Buthrakaur]

Yes - I had to migrate RH to .NET 4 and got NH working with the managed
Oracle driver (reimplemented RoundhousEOracleDriver class). It was quite straightforward. You can see the changes in the commit I mentioned in my previous comment.

[BiggerNoise]

@Buthrakaur I know it's been quite a long time, but would you consider opening a P/R with your changes to use the Managed Oracle driver?

[Buthrakaur]

@BiggerNoise the original PR is here: #191 , but I'm afraid it needs to clean up little bit :/

[BiggerNoise]

Are you able to do a new P/R with the needed cleanup? I'm not using Oracle, so there's no telling where we're going to end up if I do it.

[BiggerNoise]

I'm going to close this issue in favor of #171. It sounds like this will be fixed if we can update the Oracle driver and I'm trying to get the issue list down so I can fit it in my head.