4 Replies Latest reply on Oct 13, 2010 12:26 PM by Chris Williams

    Getting a "MySQLSyntaxErrorException" on deploy

    Chris Williams Newbie

      The specific error, when I deploy my WAR is:

       

      Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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

       

      The small black square made me think that the issue was some sort of encoding issue, so I tried setting the MySQL schema to ASCII. It didn't change anything. Here's the details of my entire project:

       

      - Packages ---

      jboss 5.1.0 GA

      MySQL 5.1.51

      JDK 1.5.0 R22

      MySQL Connector/J 5.1.13

       

      - mysql-ds.xml ---

      <datasources>
          <local-tx-datasource>
              <jndi-name>MySqlDS</jndi-name>
              <connection-url>jdbc:mysql://localhost:3306/jboss</connection-url>
              <driver-class>com.mysql.jdbc.Driver</driver-class>
              <user-name>jbossuser</user-name>
              <password>jbosspass</password>
             
              <min-pool-size>5</min-pool-size>

       

              <!-- Don't set this any higher than max_connections on your
              MySQL server, usually this should be a 10 or a few 10's
              of connections, not hundreds or thousands -->

       

              <max-pool-size>20</max-pool-size>

       

              <!-- Don't allow connections to hang out idle too long,
              never longer than what wait_timeout is set to on the
              server...A few minutes is usually okay here,
              it depends on your application
              and how much spikey load it will see -->

       

              <idle-timeout-minutes>5</idle-timeout-minutes>

       

              <!-- If you're using Connector/J 3.1.8 or newer, you can use
              our implementation of these to increase the robustness
              of the connection pool. -->

       

              <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
              <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
        
              <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
              <metadata>
                  <type-mapping>mySQL</type-mapping>
              </metadata>
          </local-tx-datasource>
      </datasources>

       

      - persistence.xml ---

      <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="RPersist">
              <jta-data-source>java:/MySqlDS</jta-data-source>
              <properties>
                  <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
                  <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jboss" />
                  <property name="javax.persistence.jdbc.user" value="jbossuser" />
                  <property name="javax.persistence.jdbc.password" value="jbosspass" />

       

                  <!-- JBoss Specific? -->
                  <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
                  <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/jboss"/>
                  <property name="hibernate.connection.username" value="jbossuser"/>
                  <property name="hibernate.connection.password" value="jbosspass"/>
                  <property name="hibernate.connection.password" value="jbosspass"/>
                  <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect"/>
                  <property name="hibernate.connection.datasource" value="java:/MySqlDS" />

       

                  <property name="hibernate.c3p0.min_size" value="5"/>
                  <property name="hibernate.c3p0.max_size" value="20"/>
                  <property name="hibernate.c3p0.timeout" value="300"/>
                  <property name="hibernate.c3p0.max_statements" value="50"/>
                  <property name="hibernate.c3p0.idle_test_period" value="3000"/>
              </properties>
          </persistence-unit>
      </persistence>

       

      - IRObject.java ---

      import javax.ejb.*;

       

      @Local
      interface IRObject {
          public boolean login(String uname);
      }

       

      - RObject.java ---

      import javax.persistence.*;
      import javax.ejb.*;

       

      @Stateful
      public class RObject implements IRObject {
          @PersistenceContext(unitName="RPersist",type=PersistenceContextType.EXTENDED)
          private EntityManager em;
         
          @Init
          public void onInit() {
             
          }
         
          public boolean login(String uname) {
              return true;
          }
      }

        • 1. Re: Getting a "MySQLSyntaxErrorException" on deploy
          Wolf-Dieter Fink Master

          First I don't understand why you double the connect information.

          I deploy my persistenceUnit without any information about connention and credentials (this is the main decouple feature with the DS files).

           

          Because the problem occour in deployment, do you have any Entity to deploy or use a startup class (servlet or EJB) with DB actions?

          Can you provide more informations (stacktrace, startup log)?

          • 2. Re: Getting a "MySQLSyntaxErrorException" on deploy
            Chris Williams Newbie

            Basically, I tried every possible combination of properties for what's in persistence.xml and the one you see is simply the one I stopped at because I didn't have anything else to try. I tried an entirely property-less version and when I deployed it stated that I needed to set at least "hibernate.dialect". When I did that, I got the error we see above. I tried it without the hibernate properties; I tried it without the javax.persistence properties; if you have any particular combination of properties that you recommend, I'll gladly try them. Obviously, my preference would be something which was portable to different Application Servers -- which goes out the door if I have to mention Hibernate.

             

            And no, I don't have any entities defined yet. The two .java files are literally all the code I've written. I didn't cut anything out, they're actually empty.

             

            I'm using a library called BlazeDS which has a servlet running inside of it that -- I presume -- creates instances of RObject for each new session, but I have no control over that. So far as I know it doesn't use a database (I can't think of why it would), but I really have no way of knowing.

             

            I'll dump the full trace when I get in to work in the morning, I guess. It gets cut off by all the Java runtime errors in the console or I would have posted it before.

            • 3. Re: Getting a "MySQLSyntaxErrorException" on deploy
              Wolf-Dieter Fink Master

              My persistence.xml is very simple and it works:

              <persistence-unit name="pUnit">
                  <jta-data-source>java:/myDS</jta-data-source>  <!-- a Oracle database connector -->
                  <jar-file>../persistence.jar</jar-file> <!-- here all entities are for auto analysis -->
                  <properties>
                    <property name="hibernate.archive.autodetection" value="class, hbm" />
                    <property name="hibernate.hbm2ddl.auto" value="validate" />
                  </properties>
                </persistence-unit>

               

              I suppose that your problem is not the persistence but a SQL from servlet.

              A problem that a persistence.xml without a entity dosn't work sounds unlikly.

              So you need to analyse the startup and find out what kind of SQL statement will be the problem.

              • 4. Re: Getting a "MySQLSyntaxErrorException" on deploy
                Chris Williams Newbie

                Ahah. When I dumped the output to a text file, the oddly coded characters showed themselves as "þÿ" instead of as a box. A google found that, and it was indeed a character code issue. I needed to reinstall MySQL with UTF-8 as the default encoding. Just changing the encoding on the schema wasn't enough.