Re: Switching to MySQL

[Kurt T Stam]

No there only needs to be an empty database, and a user with table 
create rights. You might be able to learn more by looking in the 
juddi.log on tomcat.

Jeremi Thebeau wrote:
Hi Kurt,
yes I followed the instructions for switching to mysql in chapter 3 
except that the URL used to describe the jdbc/JuddiDS resource in 
context.xml:

url="jdbc:mysql://localhost:3306/JUDDI

since my DB is called "JUDDI" and not "juddiv3".

I can log into  mysql with the juddi user and create tables. When I 
startup juddi the JUDDI DB is empty and after start up I still get:

mysql> show tables;
Empty set (0.00 sec)

Does there need to be a table in there before startup?

Jeremi

Kurt T Stam wrote:
Hi Jeremi,

I think you followed the exact instructions mention in Chapter 3?

http://svn.apache.org/viewvc/webservices/juddi/branches/v3_trunk/docs/jUDDI-UserGuide.pdf?view=co 


Does the mysql user have table create permissions and did the mysql 
tables get created?<property name="hibernate.hbm2ddl.auto" 
value="update"/>
should take autocreate it, which you have.

--Kurt


Jeremi Thebeau wrote:
Hi,

I'm running the following SNAPSHOT release of juddi:

http://people.apache.org/repo/m2-snapshot-repository/org/apache/juddi/juddi-portal-bundle/3.0.0.SNAPSHOT/juddi-portal-bundle-3.0.0.20090723.201427-7.zip* 


*on the following system:

Processor: Intel(R) Core(TM)2 CPU     6400  @ 2.13GHz
RAM: 2011 MB
OS: Ubuntu 9.04, linux kernel 2.6.28-13-generic

Also installed is MySQL Ver 14.12 Distrib 5.0.75, for 
debian-linux-gnu which has an empty DB called JUDDI for which user 
ju...@localhost has permissions.

As per the v3 User's Guide I modified the 
tomcat/webapps/juddi/META-INF/context.xml and 
tomcat/webapps/juddi/WEB-INF/classes/META-INF/persistence.xml files 
so they now look like this:

context.xml

<?xml version="1.0" encoding="UTF-8"?>

<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <!--  derby out-of-the-box
    <Resource name="jdbc/JuddiDS" auth="Container"
            type="javax.sql.DataSource" username="" password=""
            driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
            url="jdbc:derby:juddi-derby-test-db;create=true"
            maxActive="8"
            />
    -->
       <!-- mysql -->
    <Resource name="jdbc/JuddiDS" auth="Container"
            type="javax.sql.DataSource" username="juddi" 
password="MyPassword"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/JUDDI"
            maxActive="8"
            />
           </Context>


persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"; 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
             
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence 
http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"; 
             version="1.0">
  <persistence-unit name="juddiDatabase" 
transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>java:comp/env/jdbc/JuddiDS</jta-data-source>
        <!-- entity classes -->
    <class>org.apache.juddi.model.Address</class>
    <class>org.apache.juddi.model.AddressLine</class>
    <class>org.apache.juddi.model.AuthToken</class>
    <class>org.apache.juddi.model.BindingCategoryBag</class>
    <class>org.apache.juddi.model.BindingDescr</class>
    <class>org.apache.juddi.model.BindingTemplate</class>
    <class>org.apache.juddi.model.BusinessCategoryBag</class>
    <class>org.apache.juddi.model.BusinessDescr</class>
    <class>org.apache.juddi.model.BusinessEntity</class>
    <class>org.apache.juddi.model.BusinessIdentifier</class>
    <class>org.apache.juddi.model.BusinessName</class>
    <class>org.apache.juddi.model.BusinessService</class>
    <class>org.apache.juddi.model.CategoryBag</class>
    <class>org.apache.juddi.model.Contact</class>
    <class>org.apache.juddi.model.ContactDescr</class>
    <class>org.apache.juddi.model.DiscoveryUrl</class>
    <class>org.apache.juddi.model.Email</class>
    <class>org.apache.juddi.model.InstanceDetailsDescr</class>
    <class>org.apache.juddi.model.InstanceDetailsDocDescr</class>
    <class>org.apache.juddi.model.KeyedReference</class>
    <class>org.apache.juddi.model.KeyedReferenceGroup</class>
    <class>org.apache.juddi.model.OverviewDoc</class>
    <class>org.apache.juddi.model.OverviewDocDescr</class>
    <class>org.apache.juddi.model.PersonName</class>
    <class>org.apache.juddi.model.Phone</class>
    <class>org.apache.juddi.model.Publisher</class>
    <class>org.apache.juddi.model.PublisherAssertion</class>
    <class>org.apache.juddi.model.PublisherAssertionId</class>
    <class>org.apache.juddi.model.ServiceCategoryBag</class>
    <class>org.apache.juddi.model.ServiceDescr</class>
    <class>org.apache.juddi.model.ServiceName</class>
    <class>org.apache.juddi.model.ServiceProjection</class>
    <class>org.apache.juddi.model.Subscription</class>
    <class>org.apache.juddi.model.SubscriptionChunkToken</class>
    <class>org.apache.juddi.model.SubscriptionMatch</class>
    <class>org.apache.juddi.model.Tmodel</class>
    <class>org.apache.juddi.model.TmodelCategoryBag</class>
    <class>org.apache.juddi.model.TmodelDescr</class>
    <class>org.apache.juddi.model.TmodelIdentifier</class>
    <class>org.apache.juddi.model.TmodelInstanceInfo</class>
    <class>org.apache.juddi.model.TmodelInstanceInfoDescr</class>
    <class>org.apache.juddi.model.TransferToken</class>
    <class>org.apache.juddi.model.TransferTokenKey</class>
    <class>org.apache.juddi.model.UddiEntity</class>
    <class>org.apache.juddi.model.UddiEntityPublisher</class>
 
    <properties>
      <property name="hibernate.archive.autodetection" value="class"/>
      <property name="hibernate.hbm2ddl.auto" value="update"/>
      <property name="hibernate.show_sql" value="false"/>

      <property name="hibernate.dialect" 
value="org.hibernate.dialect.MySQLDialect"/> <!-- for MySQL DB -->
<!--  <property name="hibernate.dialect" 
value="org.hibernate.dialect.DerbyDialect"/>  for derby DB -->
          </properties>
  </persistence-unit>
</persistence>

However, when I try to get an AuthToken I get the following 
org.hibernate.exception.SQLGrammarException.

[14:38:33,045] DEBUG [main] - *Outbound Message*:
HTTP Headers:

Soap Message:
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/";>
    <S:Body>
        <get_authToken cred="root" userID="root" 
xmlns="urn:uddi-org:api_v3" 
xmlns:ns10="urn:uddi-org:policy_v3_instanceParms" 
xmlns:ns2="urn:uddi-org:custody_v3" xmlns:ns3="urn:uddi-org:repl_v3" 
xmlns:ns4="http://www.w3.org/2000/09/xmldsig#"; 
xmlns:ns5="urn:uddi-org:sub_v3" xmlns:ns6="urn:uddi-org:subr_v3" 
xmlns:ns7="urn:uddi-org:vscache_v3" 
xmlns:ns8="urn:uddi-org:policy_v3" xmlns:ns9="urn:uddi-org:vs_v3" />
    </S:Body>
</S:Envelope>

Message Context Properties:
- com.sun.xml.internal.ws.api.message.HeaderList = []
- com.sun.xml.internal.ws.api.server.WSEndpoint = null
- com.sun.xml.internal.ws.client.ContentNegotiation = none
- com.sun.xml.internal.ws.client.handle = JAX-WS RI 2.1.6 in JDK 6: 
Stub for http://192.168.178.200:8080/juddi/services/security
- com.sun.xml.internal.ws.handler.config = 
com.sun.xml.internal.ws.client.handlerconfigurat...@a16605
- com.sun.xml.internal.ws.server.OneWayOperation = true
- javax.xml.ws.handler.message.outbound = true
- javax.xml.ws.reference.parameters = []
- javax.xml.ws.service.endpoint.address = 
http://192.168.178.200:8080/juddi/services/security
- javax.xml.ws.soap.http.soapaction.uri = get_authToken
- javax.xml.ws.wsdl.interface = 
{urn:uddi-org:api_v3_portType}UDDI_Security_PortType
- javax.xml.ws.wsdl.port = 
{urn:uddi-org:api_v3_portType}UDDISecurityServicePort
- javax.xml.ws.wsdl.service = 
{urn:uddi-org:api_v3_portType}UDDISecurityService

[14:38:34,254] DEBUG [main] - *Inbound Message*:
HTTP Headers:
- null = [HTTP/1.1 200 OK]
- Content-type = [text/xml;charset=UTF-8]
- Content-length = [289]
- Server = [Apache-Coyote/1.1]
- Date = [Wed, 05 Aug 2009 12:38:56 GMT]

Soap Message:
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/";>
    <soap:Header />
    <soap:Body>
        <soap:Fault>
            <faultcode>
                soap:Server
            </faultcode>
            <faultstring>
                *org.hibernate.exception.SQLGrammarException: could 
not insert: [org.apache.juddi.model.Tmodel]*
            </faultstring>
        </soap:Fault>
    </soap:Body>
</soap:Envelope>

Message Context Properties:
- com.sun.xml.internal.ws.api.message.HeaderList = []
- com.sun.xml.internal.ws.api.server.WSEndpoint = null
- com.sun.xml.internal.ws.client.ContentNegotiation = none
- com.sun.xml.internal.ws.client.handle = null
- com.sun.xml.internal.ws.handler.config = 
com.sun.xml.internal.ws.client.handlerconfigurat...@a16605
- com.sun.xml.internal.ws.server.OneWayOperation = null
- com.xceptance.xlt.ws.handler.requestData = 
com.xceptance.xlt.api.engine.requestd...@c71c00
- javax.xml.ws.handler.message.outbound = false
- javax.xml.ws.http.response.code = 200
- javax.xml.ws.http.response.headers = {null=[HTTP/1.1 200 OK], 
Content-type=[text/xml;charset=UTF-8], Content-length=[289], 
Server=[Apache-Coyote/1.1], Date=[Wed, 05 Aug 2009 12:38:56 GMT]}
- javax.xml.ws.reference.parameters = []
- javax.xml.ws.service.endpoint.address = 
http://192.168.178.200:8080/juddi/services/security
- javax.xml.ws.soap.http.soapaction.uri = null
- javax.xml.ws.wsdl.interface = 
{urn:uddi-org:api_v3_portType}UDDI_Security_PortType
- javax.xml.ws.wsdl.port = 
{urn:uddi-org:api_v3_portType}UDDISecurityServicePort
- javax.xml.ws.wsdl.service = 
{urn:uddi-org:api_v3_portType}UDDISecurityService

[14:38:34,332] ERROR [main] - Execution step failed (user: 
'TRegisterBusiness-0', output: '1249475907636'):
javax.xml.ws.soap.SOAPFaultException: 
org.hibernate.exception.SQLGrammarException: could not insert: 
[org.apache.juddi.model.Tmodel]
    at 
com.sun.xml.internal.ws.fault.SOAP11Fault.getProtocolException(SOAP11Fault.java:178) 

    at 
com.sun.xml.internal.ws.fault.SOAPFaultBuilder.createException(SOAPFaultBuilder.java:111) 

    at 
com.sun.xml.internal.ws.client.sei.SyncMethodHandler.invoke(SyncMethodHandler.java:108) 

    at 
com.sun.xml.internal.ws.client.sei.SyncMethodHandler.invoke(SyncMethodHandler.java:78) 

    at 
com.sun.xml.internal.ws.client.sei.SEIStub.invoke(SEIStub.java:107)
    at $Proxy42.getAuthToken(Unknown Source)
    at 
org.apache.juddi.xlt.action.SOAP.GetAuthenticationToken.execute(GetAuthenticationToken.java:48) 

    at 
com.xceptance.xlt.api.actions.AbstractAction.run(AbstractAction.java:315) 

    at 
org.apache.juddi.xlt.test.TRegisterBusiness.registerBusiness(TRegisterBusiness.java:37) 

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 

    at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 

    at java.lang.reflect.Method.invoke(Method.java:597)
    at 
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44) 

    at 
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) 

    at 
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41) 

    at 
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) 

    at 
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) 

    at 
org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31) 

    at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:73) 

    at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:46) 

    at 
org.junit.runners.ParentRunner.runChildren(ParentRunner.java:180)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:41)
    at org.junit.runners.ParentRunner$1.evaluate(ParentRunner.java:173)
    at 
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) 

    at 
org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31) 

    at org.junit.runners.ParentRunner.run(ParentRunner.java:220)
    at 
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45) 

    at 
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) 

    at 
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) 

    at 
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) 

    at 
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) 

    at 
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) 


When using juddi with the Derby DB, out-of-the-box, the 
tomcat/bin/juddi-derby-test-db/ directory is automatically created 
when running ./startup.sh. I think I am missing the steps that 
create tables and populate them with the juddi services in my JUDDI 
database in MySQL. If this should be done manually, what tables are 
necessary?

Also worth mentioning, the tomcat/bin/juddi-derby-test-db/ directory 
is still being created and populated even after the modification.

Please help!

Thanks

Jeremi