migratus icon indicating copy to clipboard operation
migratus copied to clipboard

Published 20 hours ago • verified publisher icon yogthos

Migration table failure against Oracle

Open dpassen opened this issue 9 years ago • 11 comments

https://github.com/yogthos/migratus/blob/master/src/migratus/database.clj#L213 uses 'BIGINT' for the Migrations table. This is not a valid datatype in Oracle land.

dpassen avatar Oct 08 '15 20:10 dpassen

Yeah, looks like you would either need to detect the database type and use the correct type or allow passing custom types here.

yogthos avatar Oct 08 '15 20:10 yogthos

I guess in the meantime, a workaround would be to manually create the table before running migrations.

yogthos avatar Oct 08 '15 22:10 yogthos

I can confirm that this works.

dpassen avatar Oct 09 '15 02:10 dpassen

There's a secondary problem here even when manually creating the table, namely there no JDBC mappings to a java.lang.Long using the Oracle driver. Best mapping for the Postgres BIGINT in Oracle would be a NUMBER, but that's returned by JDBC as a java.math.BigDecimal. This causes migratus.core/uncompleted-migrations to fail its check because migratus.protocols/id returns a java.lang.Long; a java.math.BigDecimal does not equal a java.lang.Long even with the same value.

Perhaps it would be better to keep ids as some kind of string type column (e.g. VARCHAR)? I'd assume JDBC always maps those to java.lang.String. Either that or add coercion in migratus.database/completed-ids*to ensure the ids returned are longs.

alvinfrancis avatar Jun 05 '17 16:06 alvinfrancis

Using strings for ids should be fine, although that would break backwards compatibility. This looks like an existing issue as well. Perhaps it might be better to just explicitly cast to long when comparing?

yogthos avatar Jun 05 '17 16:06 yogthos

Yeah. Considering backwards compatibility, explicit coercion seems to be the easy fix. It still leaves the bug in Oracle where the migration table cannot be created automatically when it does not exist. However, since that can be alleviated by manually creating the table, it's less of an issue.

alvinfrancis avatar Jun 05 '17 18:06 alvinfrancis

Two options would be to either try to infer the database type from the driver and use the type supported by it, or find a numeric type that most databases support. If the latter is possible, I think that would be ideal.

yogthos avatar Jun 05 '17 21:06 yogthos

It looks like ANSI/ISO type should be double precision. The PlatformUtils class from the ApacheDdlUtils library could be used to infer the database type otherwise.

yogthos avatar Jun 05 '17 22:06 yogthos

Hi i am having trouble migrating oracle..

mondaldebanjan avatar Apr 04 '18 20:04 mondaldebanjan

Did you try creating the migrations table by hand?

(sql/create-table-ddl :schema_migrations
  [[:id "NUMBER" "UNIQUE" "NOT NULL"]
   [:applied "TIMESTAMP" "" ""]
   [:description "VARCHAR(1024)" "" ""]])

yogthos avatar Apr 04 '18 21:04 yogthos

This worked :) hi thanks for the help from the begining, and the quick response yogthos,

mondaldebanjan avatar Apr 05 '18 04:04 mondaldebanjan