Error Running Sync Step Oracle 0RA-01424

geraldodev · September 8, 2020, 10:27pm

I’ve tried to add a oracle 11g xe docker database with success with latest version of metabase.

When I try the company database I’m getting “missing or illegal charactar follogin the escape character”

It’s a 11g database.
I’m running metabase with /usr/bin/java -Xmx512m -Duser.timezone="-03:00" -jar metabase0_36_4.jar

Thanks in advance,

Geraldo Lopes de Souza

{
  "browser-info": {
    "language": "pt-BR",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:80.0) Gecko/20100101 Firefox/80.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "1.8.0_265-8u265-b01-0ubuntu2~18.04-b01",
    "java.vendor": "Private Build",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_265",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "25.265-b01",
    "os.name": "Linux",
    "os.version": "4.15.0-111-generic",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "oracle"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.3 (Ubuntu 12.3-1.pgdg18.04+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.36.4",
      "date": "2020-08-17",
      "branch": "release-0.36.x",
      "hash": "196c1f6"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

java.sql.SQLDataException: ORA-01424: missing or illegal character following the escape character

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
        at oracle.jdbc.OracleDatabaseMetaData.getTables(OracleDatabaseMetaData.java:2721)
        at com.mchange.v2.c3p0.impl.NewProxyDatabaseMetaData.getTables(NewProxyDatabaseMetaData.java:3010)
        at metabase.driver.sql_jdbc.sync$get_tables.invokeStatic(sync.clj:103)
        at metabase.driver.sql_jdbc.sync$get_tables.invoke(sync.clj:99)
        at metabase.driver.sql_jdbc.sync$fast_active_tables$iter__72128__72134$fn__72135.invoke(sync.clj:119)
        at clojure.lang.LazySeq.sval(LazySeq.java:42)
        at clojure.lang.LazySeq.seq(LazySeq.java:58)
        at clojure.lang.ChunkedCons.chunkedNext(ChunkedCons.java:59)
        at clojure.core$chunk_next.invokeStatic(core.clj:708)
        at clojure.core$reduce1.invokeStatic(core.clj:942)
        at clojure.core$set.invokeStatic(core.clj:4113)
        at clojure.core$set.invoke(core.clj:4105)
        at metabase.driver.sql_jdbc.sync$fast_active_tables.invokeStatic(sync.clj:118)
        at metabase.driver.sql_jdbc.sync$fast_active_tables.doInvoke(sync.clj:108)
        at clojure.lang.RestFn.invoke(RestFn.java:425)
        at metabase.driver.sql_jdbc.sync$fn__72079.invokeStatic(sync.clj:38)
        at metabase.driver.sql_jdbc.sync$fn__72079.invoke(sync.clj:37)
        at clojure.lang.MultiFn.invoke(MultiFn.java:234)
        at metabase.driver.sql_jdbc.sync$describe_database.invokeStatic(sync.clj:225)
        at metabase.driver.sql_jdbc.sync$describe_database.invoke(sync.clj:221)
        at metabase.driver.sql_jdbc$fn__73838.invokeStatic(sql_jdbc.clj:57)
        at metabase.driver.sql_jdbc$fn__73838.invoke(sql_jdbc.clj:55)
        at clojure.lang.MultiFn.invoke(MultiFn.java:234)
        at metabase.sync.fetch_metadata$fn__52935$db_metadata__52940$fn__52941.invoke(fetch_metadata.clj:13)
        at metabase.sync.fetch_metadata$fn__52935$db_metadata__52940.invoke(fetch_metadata.clj:10)
        at metabase.sync.sync_metadata.tables$fn__54361$db_metadata__54366$fn__54367.invoke(tables.clj:146)
        at metabase.sync.sync_metadata.tables$fn__54361$db_metadata__54366.invoke(tables.clj:143)
        at metabase.sync.sync_metadata.tables$fn__54422$sync_tables_BANG___54427$fn__54428.invoke(tables.clj:163)
        at metabase.sync.sync_metadata.tables$fn__54422$sync_tables_BANG___54427.invoke(tables.clj:158)
        at metabase.sync.util$fn__42218$run_step_with_metadata__42223$fn__42227$fn__42229.invoke(util.clj:345)
        at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
        at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
        at metabase.sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:119)
        at metabase.sync.util$with_start_and_finish_debug_logging.invoke(util.clj:116)
        at metabase.sync.util$fn__42218$run_step_with_metadata__42223$fn__42227.invoke(util.clj:342)
        at metabase.sync.util$fn__42218$run_step_with_metadata__42223.invoke(util.clj:337)
        at metabase.sync.util$fn__42410$run_sync_operation__42415$fn__42416$fn__42417.invoke(util.clj:430)
        at clojure.core$mapv$fn__8445.invoke(core.clj:6912)
        at clojure.lang.PersistentVector.reduce(PersistentVector.java:343)
        at clojure.core$reduce.invokeStatic(core.clj:6827)
        at clojure.core$mapv.invokeStatic(core.clj:6903)
        at clojure.core$mapv.invoke(core.clj:6903)
        at metabase.sync.util$fn__42410$run_sync_operation__42415$fn__42416.invoke(util.clj:430)
        at metabase.sync.util$fn__42410$run_sync_operation__42415.invoke(util.clj:424)
        at metabase.sync.sync_metadata$fn__54475$sync_db_metadata_BANG___54480$fn__54481$fn__54482.invoke(sync_metadata.clj:51)
        at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:149)
        at metabase.sync.util$do_with_error_handling.invoke(util.clj:142)
        at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:145)
        at metabase.sync.util$do_with_error_handling.invoke(util.clj:142)
        at clojure.core$partial$fn__5839.invoke(core.clj:2623)
        at metabase.driver$fn__22445.invokeStatic(driver.clj:540)
        at metabase.driver$fn__22445.invoke(driver.clj:540)
        at clojure.lang.MultiFn.invoke(MultiFn.java:239)
        at metabase.sync.util$sync_in_context$fn__42130.invoke(util.clj:138)
        at metabase.sync.util$with_db_logging_disabled$fn__42127.invoke(util.clj:129)
        at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
        at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
        at metabase.sync.util$with_start_and_finish_logging$fn__42116.invoke(util.clj:114)
        at metabase.sync.util$with_sync_events$fn__42111.invoke(util.clj:88)
        at metabase.sync.util$with_duplicate_ops_prevented$fn__42102.invoke(util.clj:67)
        at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:164)
        at metabase.sync.util$do_sync_operation.invoke(util.clj:161)
        at metabase.sync.sync_metadata$fn__54475$sync_db_metadata_BANG___54480$fn__54481.invoke(sync_metadata.clj:50)
        at metabase.sync.sync_metadata$fn__54475$sync_db_metadata_BANG___54480.invoke(sync_metadata.clj:47)
        at metabase.sync$fn__54525$sync_database_BANG___54530$fn__54531$fn__54532.invoke(sync.clj:29)
        at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:149)
        at metabase.sync.util$do_with_error_handling.invoke(util.clj:142)
        at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:145)
        at metabase.sync.util$do_with_error_handling.invoke(util.clj:142)
        at clojure.core$partial$fn__5839.invoke(core.clj:2623)
        at metabase.driver$fn__22445.invokeStatic(driver.clj:540)
        at metabase.driver$fn__22445.invoke(driver.clj:540)
        at clojure.lang.MultiFn.invoke(MultiFn.java:239)
        at metabase.sync.util$sync_in_context$fn__42130.invoke(util.clj:138)
        at metabase.sync.util$with_db_logging_disabled$fn__42127.invoke(util.clj:129)
        at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
        at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
        at metabase.sync.util$with_start_and_finish_logging$fn__42116.invoke(util.clj:114)
        at metabase.sync.util$with_sync_events$fn__42111.invoke(util.clj:88)
        at metabase.sync.util$with_duplicate_ops_prevented$fn__42102.invoke(util.clj:67)
        at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:164)
        at metabase.sync.util$do_sync_operation.invoke(util.clj:161)
        at metabase.sync$fn__54525$sync_database_BANG___54530$fn__54531.invoke(sync.clj:27)
        at metabase.sync$fn__54525$sync_database_BANG___54530.invoke(sync.clj:19)
        at metabase.events.sync_database$process_sync_database_event$fn__74152.invoke(sync_database.clj:38)
        at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)
        at clojure.lang.AFn.call(AFn.java:18)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: Error : 1424, Position : 195, Sql = SELECT NULL AS table_cat,
       o.owner AS table_schem,
       o.object_name AS table_name,
       o.object_type AS table_type,
       NULL AS remarks
  FROM all_objects o
  WHERE o.owner LIKE :1 ESCAPE '/'
    AND o.object_name LIKE :2 ESCAPE '/'
    AND o.object_type IN ('xxx', 'TABLE', 'VIEW')
  ORDER BY table_type, table_schem, table_name
, OriginalSql = SELECT NULL AS table_cat,
       o.owner AS table_schem,
       o.object_name AS table_name,
       o.object_type AS table_type,
       NULL AS remarks
  FROM all_objects o
  WHERE o.owner LIKE :1 ESCAPE '/'
    AND o.object_name LIKE :2 ESCAPE '/'
    AND o.object_type IN ('xxx', 'TABLE', 'VIEW')
  ORDER BY table_type, table_schem, table_name
, Error Msg = ORA-01424: missing or illegal character following the escape character

geraldodev · September 9, 2020, 2:39am

I’ve used ojdbc8_19.3.0.0 and ojdbc8_12.2.0.1, both with the same outcome.

If I don’t use -Duser.timezone="-03:00" I get errors about the timezone, (recursive call)

flamber · September 9, 2020, 9:37am

Hi @geraldodev
By any chance, does your tables or columns have names that includes a forward-slash (/) ?

geraldodev · September 9, 2020, 10:49am

No, the select below returns no rows

SELECT COLUMN_NAME
FROM DBA_TAB_COLUMNS
WHERE column_name LIKE ‘%/%’

flamber · September 9, 2020, 10:52am

@geraldodev And tables?
https://stackoverflow.com/questions/38028916/java-sql-sqldataexception-ora-01424-missing-or-illegal-character-following-the

geraldodev · September 9, 2020, 10:56am

we have bunch of SYS_NThZ/ tables

flamber · September 9, 2020, 11:05am

@geraldodev Okay, so those are causing problems - please open an issue on it: https://github.com/metabase/metabase/issues/new/choose

geraldodev · September 9, 2020, 11:13am

Thank you flamber!

geraldodev · September 9, 2020, 8:36pm

adding a forward slash on the column name is harmless.

I did the folowing on xe HR schema and were able to finish the sync metadata information
ALTER TABLE HR.COUNTRIES ADD "COLUMN1_/_" VARCHAR2(100);
It's interesting that metabase is resilient to Caused by: java.lang.NoClassDefFoundError: oracle/xdb/XMLType caused by tables on apex schema.

On the other hand like you pointed out, adding a forward slash on the table name causes the ORA-01424, and keeps a new database (like ours) from being tracked by metabase.

CREATE TABLE HR."slash_table/" (
COLUMN1 VARCHAR2(100)
);

flamber · September 9, 2020, 8:52pm

For reference: https://github.com/metabase/metabase/issues/13225