search

LucidDB / luciddb

DEFUNCT: See README
https://github.com/LucidDB/luciddb
Apache License 2.0
53 stars 24 forks source link

[LDB-186] Error on foreign schema import from MySQL #136

Closed dynamobi-build closed 12 years ago

dynamobi-build commented 12 years ago

[reporter="dmurray3", created="Mon, 27 Oct 2008 12:31:17 -0500 (GMT-05:00)"] Following the LucidDbETLTutorial - Defines Sources Step, cannot execute:


import foreign schema "DEFAULT"
from server MYSQL_COOP_PRESENTA
into extraction_schema;


The following error is displayed:

Error: Support for foreign schema import not available in data
server "SYS_BOOT"
."MYSQL_COOP_PRESENTA" (state=,code=0)


Trace log follows:
* Trace log starts *
27/10/2008 03:09:35 PM net.sf.farrago.db.FarragoDbSingleton pinReference
INFO: connect
27/10/2008 03:09:35 PM net.sf.farrago.db.FarragoDatabase dumpTraceConfig
CONFIG: # Tracing configuration
handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.append=true
java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter
java.util.logging.FileHandler.pattern=C:\LUCIDDB\luciddb-0.7.4\install..\trace\Trace.log
.level=CONFIG
net.sf.farrago.jdbc.level=FINER


27/10/2008 03:09:43 PM net.sf.farrago.catalog.FarragoMdrReposImpl
INFO: Catalog successfully loaded
27/10/2008 03:09:43 PM net.sf.farrago.db.FarragoDatabase
CONFIG: java.class.path = C:\pentaho170\java\lib\tools.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\commons-dbcp-1.2.1.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\commons-digester-1.7.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\commons-logging-1.1.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\commons-pool-1.3.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\commons-transaction-1.1.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\csvjdbc.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\eigenbase-resgen.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\eigenbase-xom.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\farrago.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\hsqldb.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\janino.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\jgrapht-jdk1.5.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\jline.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\openjava.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\postgresql-8.1-406.jdbc2.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mysql-connector-java-5.0.7-bin.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\RmiJdbc-1.4.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\RmiJdbc.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\sqlline.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\vjdbc.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\vjdbc_server.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mdrlibs\jmi.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mdrlibs\jmiutils.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mdrlibs\mdrapi.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mdrlibs\mdrjdbc.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mdrlibs\mof.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mdrlibs\nbmdr.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\mdrlibs\openide-util.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\antlr-2.7.6rc1.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\asm.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\cglib-2.1.3.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\commons-collections-3.1.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\commons-logging-api.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\commons-logging.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\dom4j-1.6.1.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\ehcache-1.2.0_03.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\eigenbase-enki-0.1.0-findbugs.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\ejb3-persistence.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\enki.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\hibernate-annotations.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\hibernate3.jar;C:\LUCIDDB\luciddb-0.7.4\install..\lib\enki\jta.jar;C:\PentahoAggTableDesigner\aggregation-designer\lib\log4j-1.2.9.jar;C:\PentahoAggTableDesigner\aggregation-designer\lib\mondrian-3.0.4.jar;C:\PentahoAggTableDesigner\aggregation-designer\lib\commons-vfs-1.0.jar;C:\PentahoAggTableDesigner\aggregation-designer\lib\commons-math-1.0.jar;C:\PentahoAggTableDesigner\aggregation-designer\lib\javacup-10k.jar
27/10/2008 03:09:43 PM net.sf.farrago.db.FarragoDatabase
CONFIG: java.library.path = C:\pentaho170\java\bin;.;C:\WINDOWS\system32;C:\WINDOWS;C:\pentaho170\java\bin;C:\pentaho1-7-0\java\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Archivos de programa\ATI Technologies\ATI Control Panel;;C:\ARCHIV~1\ARCHIV~1\MUVEET~1\030625;C:\LUCIDDB\luciddb-0.7.4\bin..\lib\fennel
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter cachePageSize=32768
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter cachePagesInit=5000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter cachePagesMax=5000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter cacheReservePercentage=5
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseIncrementSize=1000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseInitSize=2000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseMaxSize=0
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseShadowLogIncrementSize=1000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseShadowLogInitSize=2000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseTxnLogIncrementSize=1000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseTxnLogInitSize=2000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter expectedConcurrentStatements=4
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter forceTxns=true
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter freshmenPageQueuePercentage=25
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter groupCommitInterval=0
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter jniHandleTraceFile=
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter pageHistoryQueuePercentage=100
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter prefetchPagesMax=12
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter prefetchThrottleRate=10
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter resourceDir=C:\LUCIDDB\luciddb-0.7.4\bin../catalog/fennel
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter tempIncrementSize=1000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter tempInitSize=3000
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter tempMaxSize=0
27/10/2008 03:09:46 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel parameter databaseDir=C:\LUCIDDB\luciddb-0.7.4\bin..\catalog
27/10/2008 03:09:46 PM net.sf.fennel.database
INFO: Fennel build: --with-optimization
27/10/2008 03:09:46 PM net.sf.fennel.database
INFO: opening database; process ID = 4036
27/10/2008 03:09:51 PM net.sf.fennel.database
INFO: online UUID = e739029d-5332-4d57-aa6c-6b0c76ef8b5d
27/10/2008 03:09:51 PM net.sf.fennel.database
INFO: database opened; page version = 231
27/10/2008 03:09:51 PM net.sf.farrago.db.FarragoDatabase loadFennel
CONFIG: Fennel successfully loaded
27/10/2008 03:13:13 PM net.sf.farrago.db.FarragoDbSingleton pinReference
INFO: connect
27/10/2008 03:13:13 PM net.sf.farrago.db.FarragoDbSession commitImpl
INFO: commit
27/10/2008 03:13:30 PM net.sf.farrago.db.FarragoDbStmtContext cancel
INFO: cancel
27/10/2008 03:13:30 PM net.sf.farrago.db.FarragoDbSession prepare
INFO: import foreign schema "DEFAULT"
from server MYSQL_COOP_PRESENTA
into extraction_schema
27/10/2008 03:13:30 PM net.sf.farrago.db.FarragoDbSession commitImpl
INFO: commit
27/10/2008 03:13:49 PM org.eigenbase.util.EigenbaseException
GRAVE: org.eigenbase.util.EigenbaseException: Support for foreign schema import not available in data server "SYS_BOOT"."MYSQL_COOP_PRESENTA"
27/10/2008 03:13:49 PM net.sf.farrago.db.FarragoDbSession rollbackImpl
INFO: rollback
27/10/2008 03:13:49 PM net.sf.farrago.jdbc.FarragoJdbcUtil newSqlException
GRAVE: Support for foreign schema import not available in data server "SYS_BOOT"."MYSQL_COOP_PRESENTA"
27/10/2008 03:13:49 PM FarragoJdbcUtil newSqlException(ex)
MÁS FINA: THROW
org.eigenbase.util.EigenbaseException: Support for foreign schema import not available in data server "SYS_BOOT"."MYSQL_COOP_PRESENTA"
at net.sf.farrago.resource.FarragoResource$Def0.ex(FarragoResource.java:1766)
at net.sf.farrago.ddl.DdlImportForeignSchemaStmt.preValidate(DdlImportForeignSchemaStmt.java:154)
at net.sf.farrago.ddl.DdlValidator.validate(DdlValidator.java:845)
at net.sf.farrago.db.FarragoDbSession.validateDdl(FarragoDbSession.java:1148)
at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:1107)
at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:997)
at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:129)
at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:109)
at de.simplicit.vjdbc.command.StatementExecuteCommand.execute(StatementExecuteCommand.java:34)
at de.simplicit.vjdbc.server.command.ConnectionEntry.executeCommand(ConnectionEntry.java:161)
at de.simplicit.vjdbc.server.command.CommandProcessor.process(CommandProcessor.java:158)
at de.simplicit.vjdbc.server.rmi.CommandSinkRmiImpl.process(CommandSinkRmiImpl.java:37)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
at sun.rmi.transport.Transport$1.run(Transport.java:153)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
at java.lang.Thread.run(Thread.java:595)
27/10/2008 03:13:49 PM net.sf.farrago.db.FarragoDbStmtContext cancel
INFO: cancel
27/10/2008 03:13:49 PM net.sf.farrago.db.FarragoDbStmtContext cancel
INFO: cancel
27/10/2008 03:14:33 PM net.sf.farrago.db.FarragoDbSingleton disconnectSession
INFO: disconnect
27/10/2008 03:14:38 PM net.sf.farrago.db.FarragoDbSingleton shutdown
INFO: shutdown
27/10/2008 03:14:38 PM net.sf.fennel.database
INFO: closing database
27/10/2008 03:14:38 PM net.sf.fennel.database
INFO: database closed; page version = 233
27/10/2008 03:14:39 PM net.sf.farrago.catalog.FarragoMdrReposImpl closeAllocation
INFO: Catalog successfully closed
*trace log ends _*


Thank you in advance for your assistance. Regards, Daniel Murray (dmurray3)

dynamobi-build commented 12 years ago

[author="jvs", created="Tue, 28 Oct 2008 00:18:05 -0500 (GMT-05:00)"] I was able to reproduce your problem by using this:


CREATE SERVER MYSQL_PRESENTA
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
driver_class 'com.mysql.jdbc.Driver',
url 'jdbc:mysql://localhost/LE_OLTP?nullCatalogMeansCurrent=false',
user_name 'root',
login_timeout '10',
validation_query 'select 1',
fetch_size '-2147483648',
table_types 'TABLE',
schema_name 'DEFAULT');


Apparently your default value for the nullCatalogMeansCurrent is set to the opposite of mine. If you use this, the problem should go away:


CREATE SERVER MYSQL_PRESENTA
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
driver_class 'com.mysql.jdbc.Driver',
url 'jdbc:mysql://localhost/coop_presenta?nullCatalogMeansCurrent=true',
user_name 'root',
password 'root',
login_timeout '10',
validation_query 'select 1',
fetch_size '-2147483648',
table_types 'TABLE',
schema_name 'DEFAULT');

dynamobi-build commented 12 years ago

[author="dmurray3", created="Tue, 28 Oct 2008 13:34:00 -0500 (GMT-05:00)"] Sorry to advise, but I still can't the "import schema" working. This is what I have done so far:


1) I dropped the server MYSQL_COOP_PRESENTA in order to start from scratch:
2) I reviewed your comments. According to this, I have tried:


a) First following your suggestion textually:


0: jdbc:luciddb:rmi://localhost> drop server MYSQL_COOP_PRESENTA CASCADE;
No rows affected (0.172 seconds)


0: jdbc:luciddb:rmi://localhost> CREATE SERVER MYSQL_COOP_PRESENTA
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
   driver_class 'com.mysql.jdbc.Driver',
   url 'jdbc:mysql://localhost/coop_presenta?nullCatalogMeansCurrent=true',


   user_name 'root',
   password 'root',
   login_timeout '10',
   validation_query 'select 1',
   fetch_size '1000',


   table_types 'TABLE',
   schema_name 'DEFAULT');
No rows affected (0.125 seconds)


0: jdbc:luciddb:rmi://localhost>



I test with a simple select count() against one of the tables thereon:
0: jdbc:luciddb:rmi://localhost> SELECT COUNT() FROM MYSQL_COOP_PRESENTA."DEFAU
LT"."dim_time06_12";
+---------+
| EXPR$0 |
+---------+
| 2557 |
+---------+
1 row selected (0.437 seconds)


0: jdbc:luciddb:rmi://localhost>


I then try to import the schema:


0: jdbc:luciddb:rmi://localhost> import foreign schema "DEFAULT"
from server MYSQL_COOP_PRESENTA
into extraction_schema;
Error: Support for foreign schema import not available in data server "SYS_BOOT"
."MYSQL_COOP_PRESENTA" (state=,code=0)
0: jdbc:luciddb:rmi://localhost>


b) I have also tried adjusting the foreign data wrapper as inidcated on http://pub.eigenbase.org/wiki/LucidDbCreateForeignServer to get the correct syntax for the Connector/J 5.x I am using to access MySQL:


0: jdbc:luciddb:rmi://localhost> drop server MYSQL_COOP_PRESENTA CASCADE;
No rows affected (0.156 seconds)


0: jdbc:luciddb:rmi://localhost> CREATE SERVER MYSQL_COOP_PRESENTA
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
    driver_class 'com.mysql.jdbc.Driver',
    url 'jdbc:mysql://localhost/coop_presenta?useCursorFetch=true&nullCatalogMea
nsCurrent=true',


    user_name 'root',
    password 'root',
    login_timeout '10',
    fetch_size '1000',
    validation_query 'select 1',


    table_types 'TABLE',
    schema_name 'DEFAULT');
No rows affected (0.14 seconds)


0: jdbc:luciddb:rmi://localhost>



I then test the wrapper with my simple select count():
0: jdbc:luciddb:rmi://localhost> select count() from mysql_coop_presenta."DEFAU
LT"."dim_time06_12";
+---------+
| EXPR$0 |
+---------+
| 2557 |
+---------+
1 row selected (0.391 seconds)


0: jdbc:luciddb:rmi://localhost>



Then I again try to import the schema:


0: jdbc:luciddb:rmi://localhost> import foreign schema "DEFAULT"
from server MYSQL_COOP_PRESENTA
into extraction_schema;
Error: Support for foreign schema import not available in data server "SYS_BOOT"
."MYSQL_COOP_PRESENTA" (state=,code=0)
0: jdbc:luciddb:rmi://localhost>


Still no luck.... I will enclose the new trace log to the JIRA LBD-168 case.

dynamobi-build commented 12 years ago

[author="dmurray3", created="Tue, 28 Oct 2008 13:35:18 -0500 (GMT-05:00)"] This trace log corresponds to the trials described in Dmurray3's comments dated 2008/10/28

dynamobi-build commented 12 years ago

[author="jvs", created="Wed, 29 Oct 2008 01:16:12 -0500 (GMT-05:00)"] Darn. Let's try some more things, in this order.


  1. Add the qualifying_catalog_name option to your server definition as follows (using the database name for the value):


CREATE SERVER MYSQL_COOP_PRESENTA
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
    driver_class 'com.mysql.jdbc.Driver',
    url 'jdbc:mysql://localhost/coop_presenta?useCursorFetch=true',
    user_name 'root',
    password 'root',
    login_timeout '10',
    fetch_size '1000',
    validation_query 'select 1',
    qualifying_catalog_name 'coop_presenta',
    table_types 'TABLE',
    schema_name 'DEFAULT');


  1. If that doesn't work, try upgrading to the latest MySQL Connector/J (5.1.7)


  1. If that doesn't work, try using the LIMIT clause in the IMPORT statement to bring in just one or two tables:


import foreign schema "DEFAULT"
limit to ("table1", "table2")
from server MYSQL_COOP_PRESENTA
into extraction_schema;


If that works, see if you can narrow down the problem table.


  1. If that doesn't work, turn on mysqld logging in my.cnf and attach the tail of that log from just after the failure and we'll continue diagnostics from there.
dynamobi-build commented 12 years ago

[author="jvs", created="Wed, 29 Oct 2008 11:30:50 -0500 (GMT-05:00)"] Update from Daniel on luciddb-users:


Hi John...


I followed your suggestion (1) and used the "qualifying_catalog_name", then tried the LIMIT in the IMPORT statement with 2 tables... and it worked....


My MySQL database has sme 30+ tables... so I'll start importing these until I hit the one that apparently is causing my problems.


I´ll keep you posted on how I do.


Thanks again for your support. Kind regards, DMurray3

dynamobi-build commented 12 years ago

[author="jvs", created="Tue, 4 Nov 2008 19:37:06 -0500 (GMT-05:00)"] Documentation updated here based on Daniel's findings:


http://pub.eigenbase.org/wiki/LucidDbCreateForeignServer#MySQL