database different host/ip (not localhost)

[Hari Plaikoil]

Dear all,
 
I'm trying to connect to a database on different server (different ip than localhost), but I always get error messages. I change the entityengine.xml :
 
<delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" distributed-cache-clear-enabled="false"> 
<group-map group-name="org.ofbiz" datasource-name="externalmysql"/> 
</delegator> 
 
<datasource name="externalmysql"
helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
field-type-name="mysql"
check-on-start="true"
add-missing-on-start="true"
check-pks-on-start="false"
use-foreign-keys="true"
join-style="ansi-no-parenthesis"
alias-view-columns="false"
drop-fk-use-foreign-key-keyword="true"
table-type="InnoDB"
character-set="latin1"
collate="latin1_general_cs"> 
<read-data reader-name="seed"/> 
<read-data reader-name="demo"/> 
<read-data reader-name="ext"/> 
<inline-jdbc
jdbc-driver="com.mysql.jdbc.Driver"
jdbc-uri="jdbc:mysql://<ip_address>/<database_name>?autoReconnect=true"
jdbc-username="<username>"
jdbc-password="<password>"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="20"/>  
</datasource> 

when the database was on localhost, I could access it.  
 
jdbc-driver="com.mysql.jdbc.Driver"  
jdbc-uri="jdbc:mysql://127.0.0.1/<database_name>?autoReconnect=true"  
jdbc-username="<username>"  
jdbc-password="<password>"  

however, I want to access the database on different ip (change 127.0.0.1 with another ip), but system runs on my computer (ip: 127.0.0.1). it means, it doesn't install in the same server where the database lies on.

After I checked on the log, it seems system can access the database:
 
WARNING: Column [PRODUCT_ID] of table [ACCTG_TRANS_ENTRY] of entity [AcctgTransEntry] has a column size of [40] in the database, but is defined to have a column size of [20] in the entity definition.
Column [CARRIER_PARTY_ID] of table [ORDER_HEADER] of entity [OrderHeader] exists in the database but has no corresponding field
Column [ATTN_PARTY_ID] of table [ORDER_HEADER] of entity [OrderHeader] exists in the database but has no corresponding field
Entity [OrderHeader] has 31 fields but table [ORDER_HEADER] has 33 columns.
 
 
However, the other log message makes me confuse :
 
Failure in findListIteratorByCondition operation for entity [DynamicView]: org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT COUNT(*) FROM ENTITY_KEY_STORE (Base table or view not found message from server: "Table database_name.ENTITY_KEY_STORE' doesn't exist"). Rolling back transaction.
Exception: org.ofbiz.entity.GenericDataSourceException
Message: SQL Exception while executing the following:SELECT COUNT(*) FROM ENTITY_KEY_STORE (Base table or view not found message from server: "Table 'database_name.ENTITY_KEY_STORE' doesn't exist")
 
those messages give me an assumption that system cannot access the database.
 
 
FYI, table's name is typed in lowercase font, EntityKeyStore becomes entity_key_store. and field's name is typed in uppercase font, keyName becomes KEY_NAME.
 
after I changed entitty_key_store to ENTITY_KEY_STORE, ofbiz didn't give any message for that table, but for another table, another 'doesn't exist' messages.
 
would you like to help me, which one of the files should I modify to change the font case of the table's name from uppercase to lowercase font to make the query becomes like this : 'SELECT COUNT(*) FROM entity_key_store' ?  
 
I guess the problem is about uppercase and lowercase, but I'm not sure. My computer/system is running on windows. but the database is running on linux.
 
Please help me.
 
Thank in advance.

[BJ Freeman]

if you using a version of ofbiz that is past ver 4.0 there are two
databases open. one for ofbiz proper and one for olap.
it is not clear if your seeing log files for ofbiz or olap.
if you go further back up the log you will see where ofbiz has connected
to each database. it will tell you which one.


Hari Plaikoil sent the following on 5/26/2009 3:06 AM:

> Dear all,
>  
> I'm trying to connect to a database on different server (different ip than
> localhost), but I always get error messages. I change the entityengine.xml :
>  
> <delegator name="default" entity-model-reader="main"
> entity-group-reader="main" entity-eca-reader="main"
> distributed-cache-clear-enabled="false">
> <group-map group-name="org.ofbiz" datasource-name="externalmysql"/>  
> </delegator>
>  
> <datasource name="externalmysql"
> helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
> field-type-name="mysql"
> check-on-start="true"
> add-missing-on-start="true"
> check-pks-on-start="false"
> use-foreign-keys="true"
> join-style="ansi-no-parenthesis"
> alias-view-columns="false"
> drop-fk-use-foreign-key-keyword="true"
> table-type="InnoDB"
> character-set="latin1"
> collate="latin1_general_cs">
> <read-data reader-name="seed"/>
> <read-data reader-name="demo"/>
> <read-data reader-name="ext"/>
> <inline-jdbc
> jdbc-driver="com.mysql.jdbc.Driver"
> jdbc-uri="jdbc:mysql://<ip_address>/<database_name>?autoReconnect=true"
> jdbc-username="<username>"
> jdbc-password="<password>"
> isolation-level="ReadCommitted"
> pool-minsize="2"
> pool-maxsize="20"/>  
> </datasource>
>
> when the database was on localhost, I could access it.  
>  
> jdbc-driver="com.mysql.jdbc.Driver"  
> jdbc-uri="jdbc:mysql://127.0.0.1/<database_name>?autoReconnect=true"  
> jdbc-username="<username>"  
> jdbc-password="<password>"  
>
> however, I want to access the database on different ip (change 127.0.0.1
> with another ip), but system runs on my computer (ip: 127.0.0.1). it means,
> it doesn't install in the same server where the database lies on.
>
> After I checked on the log, it seems system can access the database:
>  
> WARNING: Column [PRODUCT_ID] of table [ACCTG_TRANS_ENTRY] of entity
> [AcctgTransEntry] has a column size of [40] in the database, but is defined
> to have a column size of [20] in the entity definition.
> Column [CARRIER_PARTY_ID] of table [ORDER_HEADER] of entity [OrderHeader]
> exists in the database but has no corresponding field
> Column [ATTN_PARTY_ID] of table [ORDER_HEADER] of entity [OrderHeader]
> exists in the database but has no corresponding field
> Entity [OrderHeader] has 31 fields but table [ORDER_HEADER] has 33 columns.
>  
>  
> However, the other log message makes me confuse :
>  
> Failure in findListIteratorByCondition operation for entity [DynamicView]:
> org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing
> the following:SELECT COUNT(*) FROM ENTITY_KEY_STORE (Base table or view not
> found message from server: "Table database_name.ENTITY_KEY_STORE' doesn't
> exist"). Rolling back transaction.
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: SQL Exception while executing the following:SELECT COUNT(*) FROM
> ENTITY_KEY_STORE (Base table or view not found message from server: "Table
> 'database_name.ENTITY_KEY_STORE' doesn't exist")
>  
> those messages give me an assumption that system cannot access the database.
>  
>  
> FYI, table's name is typed in lowercase font, EntityKeyStore becomes
> entity_key_store. and field's name is typed in uppercase font, keyName
> becomes KEY_NAME.
>  
> after I changed entitty_key_store to ENTITY_KEY_STORE, ofbiz didn't give any
> message for that table, but for another table, another 'doesn't exist'
> messages.
>  
> would you like to help me, which one of the files should I modify to change
> the font case of the table's name from uppercase to lowercase font to make
> the query becomes like this : 'SELECT COUNT(*) FROM entity_key_store' ?  
>  
> I guess the problem is about uppercase and lowercase, but I'm not sure. My
> computer/system is running on windows. but the database is running on linux.
>  
> Please help me.
>  
> Thank in advance.

--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.

[Ashish Vijaywargiya-5]

Hello Mr. Plaikoil,

It looks like that I can help you here.
As I also faced the same problem before two years or so.
To me it looks like you are saying correct, the problem is related case
sensitivity IMO.

You should open my.cnf file on your database server machine.
And then add the following line first in "[mysqld]" block and if it
don't work then add it in "[mysql.server]":
lower_case_table_names=1

For more details please read:
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

FYI after doing this settings change you may need to drop the database
and then recreate all the entities.
Now you are ready to go ... Give it another try, I am sure you will see
it working.

Please let us know how it goes for you.
Thanks !

--
Ashish

Hari Plaikoil wrote:

> Dear all,
>  
> I'm trying to connect to a database on different server (different ip than
> localhost), but I always get error messages. I change the entityengine.xml :
>  
> <delegator name="default" entity-model-reader="main"
> entity-group-reader="main" entity-eca-reader="main"
> distributed-cache-clear-enabled="false">
> <group-map group-name="org.ofbiz" datasource-name="externalmysql"/>  
> </delegator>
>  
> <datasource name="externalmysql"
> helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
> field-type-name="mysql"
> check-on-start="true"
> add-missing-on-start="true"
> check-pks-on-start="false"
> use-foreign-keys="true"
> join-style="ansi-no-parenthesis"
> alias-view-columns="false"
> drop-fk-use-foreign-key-keyword="true"
> table-type="InnoDB"
> character-set="latin1"
> collate="latin1_general_cs">
> <read-data reader-name="seed"/>
> <read-data reader-name="demo"/>
> <read-data reader-name="ext"/>
> <inline-jdbc
> jdbc-driver="com.mysql.jdbc.Driver"
> jdbc-uri="jdbc:mysql://<ip_address>/<database_name>?autoReconnect=true"
> jdbc-username="<username>"
> jdbc-password="<password>"
> isolation-level="ReadCommitted"
> pool-minsize="2"
> pool-maxsize="20"/>  
> </datasource>
>
> when the database was on localhost, I could access it.  
>  
> jdbc-driver="com.mysql.jdbc.Driver"  
> jdbc-uri="jdbc:mysql://127.0.0.1/<database_name>?autoReconnect=true"  
> jdbc-username="<username>"  
> jdbc-password="<password>"  
>
> however, I want to access the database on different ip (change 127.0.0.1
> with another ip), but system runs on my computer (ip: 127.0.0.1). it means,
> it doesn't install in the same server where the database lies on.
>
> After I checked on the log, it seems system can access the database:
>  
> WARNING: Column [PRODUCT_ID] of table [ACCTG_TRANS_ENTRY] of entity
> [AcctgTransEntry] has a column size of [40] in the database, but is defined
> to have a column size of [20] in the entity definition.
> Column [CARRIER_PARTY_ID] of table [ORDER_HEADER] of entity [OrderHeader]
> exists in the database but has no corresponding field
> Column [ATTN_PARTY_ID] of table [ORDER_HEADER] of entity [OrderHeader]
> exists in the database but has no corresponding field
> Entity [OrderHeader] has 31 fields but table [ORDER_HEADER] has 33 columns.
>  
>  
> However, the other log message makes me confuse :
>  
> Failure in findListIteratorByCondition operation for entity [DynamicView]:
> org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing
> the following:SELECT COUNT(*) FROM ENTITY_KEY_STORE (Base table or view not
> found message from server: "Table database_name.ENTITY_KEY_STORE' doesn't
> exist"). Rolling back transaction.
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: SQL Exception while executing the following:SELECT COUNT(*) FROM
> ENTITY_KEY_STORE (Base table or view not found message from server: "Table
> 'database_name.ENTITY_KEY_STORE' doesn't exist")
>  
> those messages give me an assumption that system cannot access the database.
>  
>  
> FYI, table's name is typed in lowercase font, EntityKeyStore becomes
> entity_key_store. and field's name is typed in uppercase font, keyName
> becomes KEY_NAME.
>  
> after I changed entitty_key_store to ENTITY_KEY_STORE, ofbiz didn't give any
> message for that table, but for another table, another 'doesn't exist'
> messages.
>  
> would you like to help me, which one of the files should I modify to change
> the font case of the table's name from uppercase to lowercase font to make
> the query becomes like this : 'SELECT COUNT(*) FROM entity_key_store' ?  
>  
> I guess the problem is about uppercase and lowercase, but I'm not sure. My
> computer/system is running on windows. but the database is running on linux.
>  
> Please help me.
>  
> Thank in advance.
>