1 2 Previous Next 17 Replies Latest reply on May 30, 2004 8:22 AM by erik777

    MySQL 4/JBoss 3.2.3 and CMP 2.0

    rberehoudougou

      Has anyone got the configuration working ?
      I think this might be a bug in JBoss . I tried the confuration for BMP and it is fine. But when it is a CMP , I get lots of SQL Errors, even on create method which SQL Querry is implemented by the container !

        • 1. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
          ironbird

          The simple "IT DOES NOT WORK" let you alone against your problem !

          • 2. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
            rberehoudougou

            Well,
            I don't have the exact exception at hand to post it , but basically the exception is about a SQL Exception ( check the my SQL manual etc..) that is thrown when the create method is called on the bean !
            I have configured and deplyed mysql-ds.xml and modified the standard jbosscmp.xml file.

            • 3. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
              ironbird

              My first post means :
              Post your descriptors (ejb-jar.xml and jbosscmp-jdbc.xml) and the full error log as possible.

              • 4. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                rberehoudougou

                Ok, here we go :
                1) ejb-jar.xml
                <?xml version="1.0" encoding="UTF-8"?>
                <!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">
                <ejb-jar>
                <display-name>Product</display-name>
                <enterprise-beans>

                <display-name>Product</display-name>
                <ejb-name>Product</ejb-name>
                cmptest.ProductHome
                cmptest.Product
                <local-home>cmptest.ProductLocalHome</local-home>
                cmptest.ProductLocal
                <ejb-class>cmptest.ProductBean</ejb-class>
                <persistence-type>Container</persistence-type>
                <prim-key-class>cmptest.ProductPK</prim-key-class>
                False
                <cmp-version>2.x</cmp-version>
                <abstract-schema-name>ProductBean</abstract-schema-name>
                <cmp-field>
                <field-name>name</field-name>
                </cmp-field>
                <cmp-field>
                <field-name>description</field-name>
                </cmp-field>
                <cmp-field>
                <field-name>basePrice</field-name>
                </cmp-field>
                <cmp-field>
                <field-name>productId</field-name>
                </cmp-field>

                <query-method>
                <method-name>findByName</method-name>
                <method-params>
                <method-param>java.lang.String</method-param>
                </method-params>
                </query-method>
                <ejb-ql>SELECT OBJECT (a) FROM ProductBean AS a WHERE a.name = ?1</ejb-ql>


                <query-method>
                <method-name>findByDescription</method-name>
                <method-params>
                <method-param>java.lang.String</method-param>
                </method-params>
                </query-method>
                <ejb-ql>SELECT OBJECT (a) FROM ProductBean AS a WHERE a.description = ?1</ejb-ql>


                <query-method>
                <method-name>findByBasePrice</method-name>
                <method-params>
                <method-param>double</method-param>
                </method-params>
                </query-method>
                <ejb-ql>SELECT OBJECT (a) FROM ProductBean AS a WHERE a.basePrice = ?1</ejb-ql>


                <query-method>
                <method-name>findExpensiveProducts</method-name>
                <method-params>
                <method-param>double</method-param>
                </method-params>
                </query-method>
                <ejb-ql>SELECT OBJECT (a) FROM ProductBean AS a WHERE a.basePrice &gt; ?1</ejb-ql>


                <query-method>
                <method-name>findCheapProducts</method-name>
                <method-params>
                <method-param>double</method-param>
                </method-params>
                </query-method>
                <ejb-ql>
                <![CDATA[SELECT OBJECT (a) FROM ProductBean AS a WHERE a.basePrice < ?1]]>
                </ejb-ql>


                <query-method>
                <method-name>findAllProducts</method-name>
                <method-params />
                </query-method>
                <ejb-ql>SELECT OBJECT (a) FROM ProductBean AS a WHERE a.productId IS NOT NULL</ejb-ql>


                </enterprise-beans>
                <assembly-descriptor>
                <container-transaction>

                <ejb-name>Product</ejb-name>
                <method-name>*</method-name>

                <trans-attribute>Required</trans-attribute>
                </container-transaction>
                </assembly-descriptor>
                </ejb-jar>



                2) jbosscmp-jdbc.xml

                <?xml version="1.0" encoding="UTF-8"?>
                <!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 3.2//EN" "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_2.dtd">
                <jbosscmp-jdbc>

                java:/DefaultDS
                <datasource-mapping>mySQL</datasource-mapping>

                <enterprise-beans>

                <ejb-name>Product</ejb-name>
                <table-name>products</table-name>
                <cmp-field>
                <field-name>name</field-name>
                <column-name>name</column-name>
                </cmp-field>
                <cmp-field>
                <field-name>description</field-name>
                <column-name>description</column-name>
                </cmp-field>
                <cmp-field>
                <field-name>basePrice</field-name>
                <column-name>basePrice</column-name>
                </cmp-field>
                <cmp-field>
                <field-name>productId</field-name>
                <column-name>productId</column-name>
                </cmp-field>

                </enterprise-beans>
                </jbosscmp-jdbc>

                3) The stack trace of the exception :
                javax.ejb.CreateException: Error checking if entity exists:java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertPKCreateCommand.beforeInsert(JDBCInsertPKCreateCommand.java:96)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:137)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
                at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)
                at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:269)
                at org.jboss.ejb.EntityContainer.createHome(EntityContainer.java:737)
                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:324)
                at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1043)
                at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
                at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:197)
                at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:214)
                at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
                at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:89)
                at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
                at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
                at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
                at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
                at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:98)
                at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
                at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
                at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
                at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:483)
                at org.jboss.ejb.Container.invoke(Container.java:720)
                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:324)
                at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:367)
                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:324)
                at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
                at sun.rmi.transport.Transport$1.run(Transport.java:148)
                at java.security.AccessController.doPrivileged(Native Method)
                at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
                at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
                at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
                at java.lang.Thread.run(Thread.java:534)
                at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:247)
                at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:223)
                at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:133)
                at org.jboss.invocation.jrmp.server.JRMPInvoker_Stub.invoke(Unknown Source)
                at org.jboss.invocation.jrmp.interfaces.JRMPInvokerProxy.invoke(JRMPInvokerProxy.java:135)
                at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:96)
                at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
                at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:45)
                at org.jboss.proxy.ejb.HomeInterceptor.invoke(HomeInterceptor.java:173)
                at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
                at $Proxy0.create(Unknown Source)
                at cmptest.ProductClient.main(ProductClient.java:65)
                Destroying products..
                javax.ejb.FinderException: Find failed: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM products t0_a WHERE (t0_a.productId IS NOT NULL)' at line"
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:238)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:111)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:38)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:579)
                at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:311)
                at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntities(CachedConnectionInterceptor.java:322)
                at org.jboss.ejb.EntityContainer.find(EntityContainer.java:672)
                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:324)
                at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1043)
                at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
                at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:197)
                at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:214)
                at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
                at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:89)
                at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
                at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
                at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
                at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
                at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:98)
                at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
                at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
                at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
                at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:483)
                at org.jboss.ejb.Container.invoke(Container.java:720)
                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:324)
                at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:367)
                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:324)
                at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
                at sun.rmi.transport.Transport$1.run(Transport.java:148)
                at java.security.AccessController.doPrivileged(Native Method)
                at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
                at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
                at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
                at java.lang.Thread.run(Thread.java:534)
                at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:247)
                at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:223)
                at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:133)
                at org.jboss.invocation.jrmp.server.JRMPInvoker_Stub.invoke(Unknown Source)
                at org.jboss.invocation.jrmp.interfaces.JRMPInvokerProxy.invoke(JRMPInvokerProxy.java:135)
                at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:96)
                at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
                at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:45)
                at org.jboss.proxy.ejb.HomeInterceptor.invoke(HomeInterceptor.java:173)
                at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
                at $Proxy0.findAllProducts(Unknown Source)
                at cmptest.ProductClient.main(ProductClient.java:141)
                Exception in thread "main"

                4) the client code :

                public class ProductClient
                {



                public static void main(String[] args) throws Exception {



                ProductHome home = null;



                try {

                /*

                * Get a reference to the Product Home Object - the

                * factory for Product EJB Objects

                */

                Context ctx = getContextInfo();
                //new InitialContext(System.getProperties());

                home = (ProductHome) javax.rmi.PortableRemoteObject.narrow(ctx.lookup("Product"), ProductHome.class);



                /*

                * Use the factory to create the Product EJB Object

                */


                home.create("P5-350", "350 Mhz Pentium", 200,"123-456-7890");

                home.create("P5-400", "400 Mhz Pentium", 300,"123-456-7891");

                home.create("P5-450", "450 Mhz Pentium", 400,"123-456-7892");

                home.create("SD-64", "64 MB SDRAM", 50,"123-456-7893");

                home.create("SD-128", "128 MB SDRAM", 100,"123-456-7894");

                home.create("SD-256", "256 MB SDRAM", 200,"123-456-7895");



                /*

                * Find a Product, and print out it's description

                */

                Iterator i = home.findByName(/*"SD-64"*/"Pentium III").iterator();

                System.out.println("The following product descriptions match the product name SD-64:");

                while (i.hasNext()) {

                Product prod = (Product) javax.rmi.PortableRemoteObject.narrow(i.next(), Product.class);

                System.out.println(prod.getDescription());

                }



                /*

                * Find all products that cost $200

                */

                System.out.println("Calling finder to find all products that cost $200");

                i = home.findByBasePrice(200).iterator();



                while (i.hasNext()) {

                Product prod = (Product) javax.rmi.PortableRemoteObject.narrow(i.next(), Product.class);

                System.out.println(prod.getDescription());

                }

                }

                catch (Exception e) {

                e.printStackTrace();

                }

                finally {

                if (home != null) {

                System.out.println("Destroying products..");



                /*

                * Find all the products

                */

                Iterator i = home.findAllProducts().iterator();

                while (i.hasNext()) {

                try {

                Product prod = (Product) javax.rmi.PortableRemoteObject.narrow(i.next(), Product.class);

                if (prod.getProductId().startsWith("123")) {

                prod.remove();

                }

                }

                catch (Exception e) {

                e.printStackTrace();

                }

                }

                }

                }

                }

                private static Context getJBossContextInfo()
                {
                Context ctx = null;
                try
                {
                Properties prop = new Properties();
                prop.put(Context.INITIAL_CONTEXT_FACTORY,
                "org.jnp.interfaces.NamingContextFactory");

                prop.put(Context.PROVIDER_URL, "Pendjari:1099");
                prop.put("java.naming.factory.url.pkgs",
                "org.jboss.naming:org.jnp.interfaces");

                ctx = new InitialContext(prop);

                }
                catch (Exception ex)
                {

                }
                return ctx;
                }

                private static Context getContextInfo() throws NamingException
                {
                return getJBossContextInfo /*getWebSphereContextInfo*/();
                }


                }

                • 5. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                  ironbird

                  Hi,

                  I think your have a problem with the primary key of the Product entity, not the database.
                  Do you have a compound primary key ? (Why the primary key class is declared as cmptest.ProductPK ?)
                  The error says that probably there is no field between SELECT and FROM:the SQL query is like this
                  "SELECT FROM products t0_a WHERE (t0_a.productId IS NOT NULL)"

                  The container mismatch with your primary key definition.

                  PS : I use myself JBoss 3.2.3 and MySQL 4.0.17 and it works fine.


                  • 6. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                    rberehoudougou

                    Hi,
                    Here is my primary key class :

                    public class ProductPK implements java.io.Serializable {



                    /*

                    * Note that the primary key fields must be a

                    * subset of the the container-managed Bean fields.

                    * The fields we are marking as container-managed in

                    * our Bean are productID, name, desc, and basePrice.

                    * Therefore our PK fields need to be from that set.

                    */

                    public String productID;



                    public ProductPK(String productID) {

                    this.productID = productID;

                    }



                    public ProductPK() {

                    }



                    public String toString() {

                    return productID.toString();

                    }



                    public int hashCode()

                    {

                    return productID.hashCode();

                    }



                    public boolean equals(Object prod)

                    {

                    return ((ProductPK)prod).productID.equals(productID);

                    }

                    }

                    As you can see , it is a valid compound PK class.

                    For the querry, the only querry I could think of that would match the one in the exception is the following from the ejb-jar.xml file :

                    <query-method>
                    <method-name>findAllProducts</method-name>
                    <method-params />
                    </query-method>
                    <ejb-ql>SELECT OBJECT (a) FROM ProductBean AS a WHERE a.productId IS NOT NULL</ejb-ql>


                    The select clause does select Object (a)

                    • 7. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                      erik777

                      Whenever your exception is being caused by a database error, the best bet is to see the exact SQL JBoss is sending to the database. There are two options for this.

                      One is to enable logging in MySQL. In your my.cnf, you can add the line:

                      log=/var/log/general.log

                      This can get huge, and is not recommended in production, but is suitable for development. You can always comment it out and restart MySQL when it's no longer needed.

                      Another tip to view recent transactions when the log is large is to stop MySQL, rename (mv) general.log, then restart MySQL, so you start from a new log.

                      The other method is to enable logging of CMP in server/default/conf/log4j.xml, which should show the SQL being generated:

                      <!-- View CMP queries -->
                       <category name="org.jboss.ejb.plugins.cmp">
                       <priority value="DEBUG"/>
                       </category>
                      


                      When you see the potentially offending SQL, you can often copy and paste it into a command line MySQL client to determine the exact problem, if it isn't already clear.



                      • 8. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                        ironbird

                        It should be

                        <category name="org.jboss.ejb.plugins">
                         <priority value="TRACE" class="org.jboss.logging.XLevel"/>
                        </category>
                        



                        • 9. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                          ironbird

                          I think I got it !

                          Your primary key class have only one field, and unfortunatly for you, JBoss 3.2.3 is bugged : where the primary key class has only one field, the generated SQL for testing a entity existence before its creation is incomplete (the formet depends for each database : with MSSQL, the WHERE clause is empty).

                          So you have two solutions:
                          1) Migrate to another version of JBoss (I don't know the one which solve this)
                          2) As your primary key class contains a single field, you don't need it. Just declare the productID field as the primary key of the bean.

                          • 10. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                            erik777

                            ironbird:

                            LOL! I noticed someone said "it should be" to you before when you posted a TRACE log4j property. I believe what he was saying is that for TRACE, class="org.jboss.logging.XLevel" is required. It isn't required for DEBUG, though, since DEBUG is built into log4j.

                            DEBUG worked for me for the purposes of seeing the SQL CMP outputs, so that's what I recommend. TRACE can output a LOT of information, so I only recommend it when DEBUG doesn't suffice. I don't mean to nit pick, but I can see we're both frequently recommending DEBUG and TRACE to people, so at least now you'll understand future posts why I recommend DEBUG. :)

                            • 11. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                              aloubyansky

                              IRONBIRD, your last two comments are absolutely misleading and wrong!

                              DEBUG for org.jboss.ejb.plugins.cmp is enough to see each executed SQL and DDL statement.

                              In 3.2.3 (as well as other versions), the SQL generated to check for key duplication is the same for each database and is correct for compound and simple primary keys.

                              Be careful in your comments otherwise they will be removed.

                              • 12. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                                ironbird

                                Erik,
                                Sorry, you teach me something, but I try both and cannot see a valuable difference.

                                Alex,
                                I apologize if you don't like my comments byt I can proove what I say. The only error I make is that (you are right) the SQL generated for testing the entity existence is the same for the databases). I make this mistake because I anticipate with the error in this topic.
                                But here is it:

                                I have an entity bean:

                                 <entity >
                                 <description><![CDATA[PharmaML Client]]></description>
                                 <display-name>PhML_ClientBean</display-name>
                                
                                 <ejb-name>PhML_Client</ejb-name>
                                
                                 <home>PharmaML_EJB.interfaces.PhML_ClientHome</home>
                                 <remote>PharmaML_EJB.interfaces.PhML_Client</remote>
                                 <local-home>PharmaML_EJB.interfaces.PhML_ClientLocalHome</local-home>
                                 <local>PharmaML_EJB.interfaces.PhML_ClientLocal</local>
                                
                                 <ejb-class>PharmaML_EJB.pkg.PhML_ClientBean</ejb-class>
                                 <persistence-type>Container</persistence-type>
                                 <prim-key-class>PharmaML_EJB.pkg.ClientPK</prim-key-class>
                                 <reentrant>False</reentrant>
                                 <cmp-version>2.x</cmp-version>
                                 <abstract-schema-name>PhML_Client</abstract-schema-name>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliId</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliAddr</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliCode</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliCp</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliFax</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliFlagsup</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliMail</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliRsnscl</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliSiret</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliTel</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>cliVille</field-name>
                                 </cmp-field>
                                 <cmp-field >
                                 <description><![CDATA[]]></description>
                                 <field-name>etabCode</field-name>
                                 </cmp-field>
                                
                                 <query>
                                 <query-method>
                                 <method-name>findByClientCode</method-name>
                                 <method-params>
                                 <method-param>java.lang.String</method-param>
                                 </method-params>
                                 </query-method>
                                 <ejb-ql><![CDATA[SELECT OBJECT(a) FROM PhML_Client AS a WHERE a.cliCode = ?1]]></ejb-ql>
                                 </query>
                                 <!-- Write a file named ejb-finders-PhML_ClientBean.xml if you want to define extra finders. -->
                                 </entity>
                                


                                My primary key class is
                                public class ClientPK implements java.io.Serializable {
                                
                                 public Long cliID;
                                
                                 public ClientPK() {
                                 }
                                
                                 public ClientPK(Long id) {
                                 cliID = id;
                                 //cliCode = code;
                                 }
                                
                                 public int hashCode()
                                 {
                                 return String.valueOf(cliID).hashCode();
                                 }
                                
                                
                                
                                 public boolean equals(Object prod)
                                 {
                                 return ((ClientPK)prod).cliID.equals(cliID);
                                 }
                                 /**
                                 * @return
                                 */
                                 public Long getCliID() {
                                 return cliID;
                                 }
                                
                                 /**
                                 * @param long1
                                 */
                                 public void setCliID(Long long1) {
                                 cliID = long1;
                                 }
                                
                                }
                                


                                As you see, only one field in the primary key class.

                                here is the log with MSSQL2000 (trying to create a new entity, table is already created) : the WHERE clause is empty
                                2004-05-30 11:16:58,821 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=create
                                2004-05-30 11:16:58,821 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is null
                                2004-05-30 11:16:58,821 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for create
                                2004-05-30 11:16:58,821 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx null
                                2004-05-30 11:16:58,821 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Starting new tx TransactionImpl:XidImpl [FormatId=257, GlobalId=bureau//27, BranchQual=]
                                2004-05-30 11:16:58,821 TRACE [org.jboss.ejb.plugins.EntityInstancePool] Get instance org.jboss.ejb.plugins.EntityInstancePool@884142#0#class PharmaML_EJB.pkg.PhML_ClientBean
                                2004-05-30 11:16:58,831 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PhML_Client] Executing SQL: SELECT COUNT(*) FROM dbo.TD_CLIENT WHERE
                                2004-05-30 11:16:58,981 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PhML_Client] Error checking if entity exists
                                java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Ligne 1 : syntaxe incorrecte vers 'WHERE'.
                                 at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
                                 at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
                                 at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
                                 at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
                                 at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown Source)
                                 at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
                                 at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
                                 at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
                                 at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
                                 at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
                                 at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
                                 at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
                                 at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
                                 at com.microsoft.jdbcx.base.BasePreparedStatementWrapper.executeQuery(Unknown Source)
                                 at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:304)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertPKCreateCommand.beforeInsert(JDBCInsertPKCreateCommand.java:83)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:137)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
                                 at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)
                                 at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:269)
                                 at org.jboss.ejb.EntityContainer.createHome(EntityContainer.java:737)
                                 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:324)
                                 at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1043)
                                 at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
                                 at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:197)
                                 at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:214)
                                 at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
                                 at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:89)
                                 at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
                                 at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
                                 at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
                                 at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
                                 at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:98)
                                 at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
                                 at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
                                 at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
                                 at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:483)
                                 at org.jboss.ejb.Container.invoke(Container.java:720)
                                 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:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:101)
                                 at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:90)
                                 at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
                                 at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:45)
                                 at org.jboss.proxy.ejb.HomeInterceptor.invoke(HomeInterceptor.java:173)
                                 at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
                                 at $Proxy63.create(Unknown Source)
                                 at tutorial.web.ComputeServlet.doPost(ComputeServlet.java:99)
                                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
                                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                                 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
                                 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
                                 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                                 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                                 at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                                 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                                 at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                                 at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                                 at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                                 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                                 at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                                 at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
                                 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                                 at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                                 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                                 at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                                 at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                                 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                                 at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                                 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
                                 at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                                 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                                 at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                                 at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
                                 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
                                 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
                                 at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
                                 at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
                                 at java.lang.Thread.run(Thread.java:536)
                                2004-05-30 11:16:59,111 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TxInterceptorCMT: In finally
                                2004-05-30 11:16:59,181 TRACE [org.jboss.ejb.plugins.LogInterceptor] End method=create
                                


                                and the trace at deployment with mySQL (create table is true) : both the WHERE clause is empty and the table creation has a SQL string with a PRIMARY KEY instruction empty.
                                2004-05-30 11:25:02,176 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PhML_Client] Insert Entity SQL: INSERT INTO TD_CLIENT (CLI_ID, CLI_ADDR, CLI_CODE, CLI_CP, CLI_FAX, CLI_FLAGSUP, CLI_MAIL, CLI_RSNSCL, CLI_SIRET, CLI_TEL, CLI_VILLE, ETAB_CODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                                2004-05-30 11:25:02,176 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PhML_Client] Entity Exists SQL: SELECT COUNT(*) FROM TD_CLIENT WHERE
                                2004-05-30 11:25:02,176 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.PhML_Client] entity-command: [commandName=default,commandClass=class org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand,attributes={}]
                                2004-05-30 11:25:02,226 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.PhML_Client] Remove SQL: DELETE FROM TD_CLIENT WHERE
                                2004-05-30 11:25:02,296 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.PhML_Client] Executing SQL: CREATE TABLE TD_CLIENT (CLI_ID BIGINT, CLI_ADDR VARCHAR(250) BINARY, CLI_CODE VARCHAR(250) BINARY, CLI_CP VARCHAR(250) BINARY, CLI_FAX VARCHAR(250) BINARY, CLI_FLAGSUP TINYINT NOT NULL, CLI_MAIL VARCHAR(250) BINARY, CLI_RSNSCL VARCHAR(250) BINARY, CLI_SIRET VARCHAR(250) BINARY, CLI_TEL VARCHAR(250) BINARY, CLI_VILLE VARCHAR(250) BINARY, ETAB_CODE VARCHAR(250) BINARY, CONSTRAINT pk_TD_CLIENT PRIMARY KEY ())
                                2004-05-30 11:25:02,336 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.PhML_Client] Could not create table TD_CLIENT
                                2004-05-30 11:25:02,346 ERROR [org.jboss.ejb.EntityContainer] Starting failed
                                org.jboss.deployment.DeploymentException: Error while creating table TD_CLIENT; - nested throwable: (java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1")
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:232)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:93)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:484)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:388)
                                 at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:152)
                                 at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:342)
                                 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
                                 at sun.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
                                 at $Proxy14.start(Unknown Source)
                                 at org.jboss.system.ServiceController.start(ServiceController.java:394)
                                 at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                                 at $Proxy31.start(Unknown Source)
                                 at org.jboss.ejb.EjbModule.startService(EjbModule.java:331)
                                 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
                                 at sun.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
                                 at $Proxy14.start(Unknown Source)
                                 at org.jboss.system.ServiceController.start(ServiceController.java:394)
                                 at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                                 at $Proxy12.start(Unknown Source)
                                 at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:544)
                                 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
                                 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
                                 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
                                 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
                                 at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                                 at $Proxy6.deploy(Unknown Source)
                                 at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
                                 at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
                                 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
                                 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:212)
                                 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:191)
                                Caused by: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1"
                                 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1876)
                                 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1098)
                                 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1192)
                                 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1153)
                                 at com.mysql.jdbc.Connection.execSQL(Connection.java:2048)
                                 at com.mysql.jdbc.Connection.execSQL(Connection.java:2005)
                                 at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1252)
                                 at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1198)
                                 at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:262)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:219)
                                 ... 55 more
                                2004-05-30 11:25:02,356 WARN [org.jboss.system.ServiceController] Problem starting service jboss.j2ee:jndiName=PhML_Individu,service=EJB
                                org.jboss.deployment.DeploymentException: Error while creating table TD_CLIENT; - nested throwable: (java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1")
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:232)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:93)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:484)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:388)
                                 at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:152)
                                 at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:342)
                                 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
                                 at sun.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
                                 at $Proxy14.start(Unknown Source)
                                 at org.jboss.system.ServiceController.start(ServiceController.java:394)
                                 at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                                 at $Proxy31.start(Unknown Source)
                                 at org.jboss.ejb.EjbModule.startService(EjbModule.java:331)
                                 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
                                 at sun.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
                                 at $Proxy14.start(Unknown Source)
                                 at org.jboss.system.ServiceController.start(ServiceController.java:394)
                                 at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                                 at $Proxy12.start(Unknown Source)
                                 at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:544)
                                 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
                                 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
                                 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
                                 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
                                 at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)
                                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                                 at java.lang.reflect.Method.invoke(Method.java:324)
                                 at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                                 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                                 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                                 at $Proxy6.deploy(Unknown Source)
                                 at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
                                 at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
                                 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
                                 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:212)
                                 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:191)
                                Caused by: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1"
                                 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1876)
                                 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1098)
                                 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1192)
                                 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1153)
                                 at com.mysql.jdbc.Connection.execSQL(Connection.java:2048)
                                 at com.mysql.jdbc.Connection.execSQL(Connection.java:2005)
                                 at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1252)
                                 at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1198)
                                 at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:262)
                                 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:219)
                                 ... 55 more
                                2004-05-30 11:25:02,376 DEBUG [org.jboss.ejb.plugins.local.BaseLocalProxyFactory] Fibo cannot be Bound, doesn't have local and local home interfaces
                                2004-05-30 11:25:02,446 DEBUG [org.jboss.ejb.plugins.StatelessSessionInstancePool] Starting
                                2004-05-30 11:25:02,446 INFO [org.jboss.ejb.plugins.StatelessSessionInstancePool] Started jboss.j2ee:jndiName=ejb/tutorial/Fibo,plugin=pool,service=EJB
                                2004-05-30 11:25:02,446 INFO [org.jboss.ejb.StatelessSessionContainer] Started jboss.j2ee:jndiName=ejb/tutorial/Fibo,service=EJB
                                2004-05-30 11:25:02,446 INFO [org.jboss.ejb.EjbModule] Started jboss.j2ee:module=FiboEJB.jar,service=EjbModule
                                2004-05-30 11:25:02,446 INFO [org.jboss.ejb.EJBDeployer] Deployed: file:/D:/JBoss/jboss-3.2.3/server/default/tmp/deploy/tmp10701FiboApp.ear-contents/FiboEJB.jar
                                2004-05-30 11:25:02,857 INFO [org.jboss.web.tomcat.tc4.EmbeddedTomcatService] deploy, ctxPath=/fibo, warUrl=file:/D:/JBoss/jboss-3.2.3/server/default/tmp/deploy/tmp10701FiboApp.ear-contents/FiboWeb.war/
                                2004-05-30 11:25:02,907 INFO [org.jboss.web.tomcat.tc4.EmbeddedTomcatService] ClusteredHTTPSessionService not found
                                2004-05-30 11:25:02,917 ERROR [org.jboss.web.tomcat.tc4.EmbeddedTomcatService] Failed to setup clustering, clustering disabled
                                2004-05-30 11:25:03,348 INFO [org.jboss.web.localhost.Engine] SingleSignOnContextConfig[/fibo]: Ajout de certificats -> requête Attribut de Valve (attribute Valve)
                                2004-05-30 11:25:03,928 WARN [org.jboss.web.tomcat.tc4.EmbeddedTomcatService] Unable to invoke setDelegate on class loader:org.jboss.web.tomcat.tc4.WebCtxLoader$ENCLoader@477b4e
                                2004-05-30 11:25:03,928 INFO [org.jboss.web.localhost.Engine] StandardManager[/fibo]: Alimentation de la classe du générateur de nombre aléatoire java.security.SecureRandom
                                2004-05-30 11:25:04,059 INFO [org.jboss.web.localhost.Engine] StandardManager[/fibo]: L'alimentation du générateur de nombre aléatoire est terminé
                                2004-05-30 11:25:04,129 INFO [org.jboss.web.localhost.Engine] StandardWrapper[/fibo:default]: Chargement du conteneur (container) de servlet default
                                2004-05-30 11:25:04,139 INFO [org.jboss.web.localhost.Engine] StandardWrapper[/fibo:invoker]: Chargement du conteneur (container) de servlet invoker
                                2004-05-30 11:25:05,471 INFO [org.jboss.deployment.EARDeployer] Started J2EE application: file:/D:/JBoss/jboss-3.2.3/server/default/deploy/FiboApp.ear
                                2004-05-30 11:25:05,481 INFO [org.jboss.deployment.MainDeployer] Deployed package: file:/D:/JBoss/jboss-3.2.3/server/default/deploy/FiboApp.ear
                                2004-05-30 11:25:33,681 TRACE [org.jboss.ejb.plugins.LRUEnterpriseContextCachePolicy] Resized cache for bean PhML_Client: old capacity = 1000000, new capacity = 50
                                


                                When I modify the primary key class with 2 fields, it works fine with both databases.
                                You can remove my comment, but i have already seen a topic in this forum where somebody talks about hte same error and had never had a response.
                                So, I apologize for the debug level of cmp plugin.
                                BUT WHAT ABOUT THE PRIMARY KEY ERROR !
                                Thanks


                                • 13. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                                  ironbird

                                  And I continue about compound primary key errors:
                                  To be clear, I never use it myself (I always use normalized schemas). But I try this:

                                  The same example as above with
                                  public String cliCode;
                                  in the primary key class (so 2 fields).

                                  The database creation in mySQL:

                                  2004-05-30 12:23:47,844 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.PhML_Client] Executing SQL: CREATE TABLE TD_CLIENT (CLI_ID BIGINT, CLI_ADDR VARCHAR(250) BINARY, CLI_CODE VARCHAR(250) BINARY NOT NULL, CLI_CP VARCHAR(250) BINARY, CLI_FAX VARCHAR(250) BINARY, CLI_FLAGSUP TINYINT NOT NULL, CLI_MAIL VARCHAR(250) BINARY, CLI_RSNSCL VARCHAR(250) BINARY, CLI_SIRET VARCHAR(250) BINARY, CLI_TEL VARCHAR(250) BINARY, CLI_VILLE VARCHAR(250) BINARY, ETAB_CODE VARCHAR(250) BINARY, CONSTRAINT pk_TD_CLIENT PRIMARY KEY (CLI_CODE))
                                  2004-05-30 12:23:47,924 INFO [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.PhML_Client] Created table 'TD_CLIENT' successfully.
                                  

                                  is false (only 1 primary key).
                                  Then I delete the table and create another manually with:
                                  CREATE TABLE TD_CLIENT (CLI_ID BIGINT, CLI_ADDR VARCHAR(250) BINARY, CLI_CODE VARCHAR(250) BINARY NOT NULL, CLI_CP VARCHAR(250) BINARY, CLI_FAX VARCHAR(250) BINARY, CLI_FLAGSUP TINYINT NOT NULL, CLI_MAIL VARCHAR(250) BINARY, CLI_RSNSCL VARCHAR(250) BINARY, CLI_SIRET VARCHAR(250) BINARY, CLI_TEL VARCHAR(250) BINARY, CLI_VILLE VARCHAR(250) BINARY, ETAB_CODE VARCHAR(250) BINARY, CONSTRAINT pk_TD_CLIENT PRIMARY KEY (CLI_ID,CLI_CODE))
                                  

                                  I have then my table with 2 primary keys (cliId and cliCode).

                                  Then I create the first entity (id=25000, code="55555555")
                                  2004-05-30 13:07:52,026 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PhML_Client] Executing SQL: SELECT COUNT(*) FROM TD_CLIENT WHERE CLI_CODE=?
                                  2004-05-30 13:07:52,176 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.PhML_Client#cliCode] Set parameter: index=1, jdbcType=VARCHAR, value=55555555
                                  


                                  and the second one (id=25001,code="55555555")
                                  The entity exist SQL is always the same:
                                  2004-05-30 13:11:21,757 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=create
                                  2004-05-30 13:11:21,757 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is null
                                  2004-05-30 13:11:21,757 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for create
                                  2004-05-30 13:11:21,757 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx null
                                  2004-05-30 13:11:21,757 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Starting new tx TransactionImpl:XidImpl [FormatId=257, GlobalId=bureau//23, BranchQual=]
                                  2004-05-30 13:11:21,757 TRACE [org.jboss.ejb.plugins.EntityInstancePool] Get instance org.jboss.ejb.plugins.EntityInstancePool@1c63791#0#class PharmaML_EJB.pkg.PhML_ClientBean
                                  2004-05-30 13:11:21,767 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PhML_Client] Executing SQL: SELECT COUNT(*) FROM TD_CLIENT WHERE CLI_CODE=?
                                  2004-05-30 13:11:21,767 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.PhML_Client#cliCode] Set parameter: index=1, jdbcType=VARCHAR, value=55555555
                                  2004-05-30 13:11:21,788 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TxInterceptorCMT: In finally
                                  2004-05-30 13:11:21,798 TRACE [org.jboss.ejb.plugins.LogInterceptor] End method=create
                                  

                                  And I have the error : Entity with primary key PharmaML_EJB.pkg.ClientPK@33c587 already exists.
                                  Which is false of course because my primary key is a compound one !

                                  This is not a bug ?





                                  • 14. Re: MySQL 4/JBoss 3.2.3 and CMP 2.0
                                    erik777

                                    You used a lower case 'd' when you defined the CMP field:

                                    <cmp-field >
                                     <description><![CDATA[]]></description>
                                     <field-name>cliId</field-name>
                                    </cmp-field>
                                    


                                    and an upper case 'D' when you defined your accessors in your primary key class:

                                    public Long getCliID() {
                                     return cliID;
                                    }
                                    


                                    Alex, Ironbird seems thus far to be a good person and is trying to help people. I understand your concern about misinformation on the JBoss forums, yet you can't fault him for intention, as he really does try to be correct. Over time, I think he'll become much more on target and, if he continues to be as eager to help people, could become an indispensable asset on the forum. I'd urge patience. while continuing to help him become a JBoss expert.


                                    1 2 Previous Next