B4J Question Cannot connect to 2 Databases from the same application.

Discussion in 'B4J Questions' started by vfafou, Mar 25, 2015.

  1. vfafou

    vfafou Active Member Licensed User

    Hello!
    I have 2 database instances:
    1 MariaDB 10.0
    1 MySQL 4.0

    I need to connect to these 2 databases.

    I have added those 2 jars:

    #AdditionalJar: mysql-connector-java-3.1.12-bin
    #AdditionalJar: mariadb-java-client-1.1.7

    If I try to connect separately with

    #AdditionalJar: mysql-connector-java-3.1.12-bin

    I can connect to MySQL 4.0

    If I try to connect separately with

    #AdditionalJar: mariadb-java-client-1.1.7

    I can connect to MariaDB 10.0

    But if I try to connect to both with

    #AdditionalJar: mysql-connector-java-3.1.12-bin
    #AdditionalJar: mariadb-java-client-1.1.7

    I take exceptions like:

    WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1d085c24 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:

    java.sql.SQLException: Host 'VF_PC' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
    at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:149)
    at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
    at org.mariadb.jdbc.Driver.connect(Driver.java:114)

    It seems that the one driver confuses the other!
    Is there any way to workaround this error?

    Thank you in advance!
     
  2. giga

    giga Well-Known Member Licensed User

    Do you have access to the MySQL logs on the server to see how many connections are logged?

    Because of this:
    Host 'VF_PC' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
     
  3. vfafou

    vfafou Active Member Licensed User

    Hello giga,
    Thank you for your reply!
    The error Host 'VF_PC' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
    isn't actually an error.
    MySQL 4.0 is an old version and the login protocol is not the same as the one of the newest versions.
    When I include both drivers, something is being confused and the MariaDB driver tries to connect to the new database (it's OK) and to the old database (wrong) that MySQL driver have to.
    When I include one driver at a time, I get the connection to the proper database, but error for the other (it's reasonable).
    It seems that I have to use only one jdbc driver...

    My settings are correct:
    com.mysql.jdbc.Driver class for MySQL 4.0
    org.mariadb.jdbc.Driver class for MariaDB 10.0

    When I try to connect to MySQL 4.0 with both drivers, it seems that only one driver tries to connect to both databases.
    That's why org.mariadb.jdbc.Driver causes the error for MySQL 4.0!
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Try to connect to the servers without a connection pool. The error message may be more correct.

    It is possible that there are classes with the exact same name (package name + class name) in these two jars. In this case it will indeed cause problems.
     
  5. vfafou

    vfafou Active Member Licensed User

    Hello Erel!
    I've already searched the 2 jars for the same class name and I've not found anything.
    I'm not sure if I'm right because I've not opened the org.mariadb.jdbc.Driver to see if its internal name is com.mysql.jdbc.Driver
    I've tried to connect without a connection pool to the old database only, but at the same time I was trying to connect to the new database with a connection pool.
    The result was exactly the same.
    I'll try to connect to both databases without a pool and I'll post the result.
    If the result be the same, is there any way to connect to both DBs?
    It's not impossible to be needed to find the source, change the internal class name and recompile the driver! :)
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    You only need to open the jars with 7zip and look on the folder names. If they are different than the package name is different.
     
    giga likes this.
  7. vfafou

    vfafou Active Member Licensed User

    If it's enough to see the folders, then I've done it!
    There are no identical class names!
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    In that case one jar file shouldn't affect the other one. Try to remove the connection pool in order to test it.
     
  9. vfafou

    vfafou Active Member Licensed User

    Hi again, Erel!
    I've tried without pool and:
    java.nio.BufferUnderflowException
    at java.nio.HeapByteBuffer.get(HeapByteBuffer.java:145)
    at org.mariadb.jdbc.internal.common.packet.buffer.Reader.readRawBytes(Reader.java:126)
    at org.mariadb.jdbc.internal.mysql.packet.MySQLGreetingReadPacket.<init>(MySQLGreetingReadPacket.java:87)
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:400)
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:673)
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.<init>(MySQLProtocol.java:266)
    at org.mariadb.jdbc.Driver.connect(Driver.java:110)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at anywheresoftware.b4j.objects.SQL.Initialize2(SQL.java:55)
    at rm.rmtunnel.db._init(db.java:58)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:563)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:221)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:156)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:82)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
    at rm.rmtunnel.main.main(main.java:29)

    My settings:
    JdbcUrl=jdbc:mariadb://localhost:3306/rdbms1?characterEncoding=Greek
    DriverClass=org.mariadb.jdbc.Driver
    DBUser=root
    DBPassword=password1
    JdbcUrlR=jdbc:mysql://192.168.2.30:3306/rdbms2?characterEncoding=Greek
    DriverClassR=com.mysql.jdbc.Driver
    DBUserR=root
    DBPasswordR=password2
    RecaptchaPrivateKey=
    Port=34009
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    You aren't reading the settings from a settings file, right?
     
  11. vfafou

    vfafou Active Member Licensed User

    I'm reading the settings from a txt file!
    The content of this file is:

    JdbcUrl=jdbc:mariadb://localhost:3306/rdbms1?characterEncoding=Greek
    DriverClass=org.mariadb.jdbc.Driver
    DBUser=root
    DBPassword=password1

    JdbcUrlR=jdbc:mysql://192.168.2.30:3306/rdbms2?characterEncoding=Greek
    DriverClassR=com.mysql.jdbc.Driver
    DBUserR=root
    DBPasswordR=password2
     
  12. vfafou

    vfafou Active Member Licensed User

    I've tried with newer jdbc for MySQL but still the same result. The class names of the two drivers are surely different. Still MariaDB driver tries to connect to old MySQL.
     
  13. Daestrum

    Daestrum Well-Known Member Licensed User

    Not sure if this is 100% relevant, but on the MariaDB site it states

    Which makes me wonder if this is why you cannot connect to mariaDB and mySQL at the same time.
     
  14. Erel

    Erel Administrator Staff Member Licensed User

    This is probably the reason. Both drivers register the same scheme.
     
  15. vfafou

    vfafou Active Member Licensed User

    I don't understand why the two drivers register the same scheme! The settings are:

    JdbcUrl=jdbc:mariadb://localhost:3306/rdbms1?characterEncoding=Greek
    DriverClass=org.mariadb.jdbc.Driver
    DBUser=root
    DBPassword=password1

    JdbcUrlR=jdbc:mysql://192.168.2.30:3306/rdbms2?characterEncoding=Greek
    DriverClassR=com.mysql.jdbc.Driver
    DBUserR=root
    DBPasswordR=password2

    The hosts are different, the keys are not the same!
    When I have
    #AdditionalJar: mariadb-java-client-1.1.7

    and
    DriverClass=com.mysql.jdbc.Driver

    I take error: No proper driver found, when trying to connect to both schemes!
     
    Last edited: Mar 29, 2015
  16. Erel

    Erel Administrator Staff Member Licensed User

    It is not related to your settings. The problem is that the maria db driver registers the mysql scheme.

    You can try to first connect to the MySQL database and only then to the maria db.
     
  17. vfafou

    vfafou Active Member Licensed User

    Wow! I haven't noticed that the MariaDB driver can use jdbc:mysql.
    I'm sure this is the reason!
    I think it is not possible to connect to both databases, because the two jars are needed but only the one is activated!
    Is there any way to tell the application which jar to use for every case?
     
  18. vfafou

    vfafou Active Member Licensed User

    Thank you all for the replies!
    I've finally changed and recompiled the MariaDB Driver to not parse MySQL URLs.
    :)
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice