11 Replies Latest reply on Jul 11, 2017 5:33 AM by ss_2000

    org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement

    ss_2000

      I am trying to build a simple application and through a Servlet I have managed to establish a Connection to MySQL database. I have configured DataSource in WildFly Server (ver 10 Final). I the Servlet however,

       

      PASS - DataSource access via JNDI

      PASS - Connection retrieval from the Datasource.

      FAIL - SQL Statement when executed with simple query (select * from test.user).

           I get error (SQLException) "org.h2.jdbc.JdbcSQLException: Schema TEST not found"

      FAIL- When SQL Statement provided without the Schema PREFIX of "test" (i.e select * from user)

           I get error (SQL Exception) "org.h2.jdbc.JdbcSQLException: Table USER not found"

       

      This id plain old JDBC work that I have done for donkey years, but I have never come across such a basic error.

       

      I have even shifted from Eclipse to NetBeans in vain.

       

      The only "suspicion" I have is that error log is talking of "h2" database and no where it is "MySQL".

       

      Please help.

        • 1. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
          mayerw01

          Did you link your datasource to the MySQL driver like:

           

          <datasource jta="true" jndi-name="java:jboss/datasources/test" pool-name="test" enabled="true" use-ccm="true">

                <connection-url>jdbc:mysql://{hostname}:3306/test</connection-url>

                <driver-class>com.mysql.jdbc.Driver</driver-class>

                <driver>mysql</driver>

          ...

          </datasource>

          • 2. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
            ss_2000

            Hi Thanks for the reply. Apologies I could not respond earlier> i have been without any Internet.

             

            Yes I have tried your prescribed solution but unfortunately, I keep getting the same result.

            • 3. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
              emag

              Please share your standalone*.xml you use and how to get DataSource. I suspect your app lookups predefined ExampleDS.

              • 4. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                ss_2000

                I have the following setup in standalone.xml:

                <datasources>

                                <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">

                                    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>

                                    <driver>h2</driver>

                                    <security>

                                        <user-name>sa</user-name>

                                        <password>sa</password>

                                    </security>

                                </datasource>

                                <datasource datasource jta="true" jndi-name="java:/MySqlDS" pool-name="MySqlDS" enabled="true" use-ccm="true">

                                    <connection-url>jdbc:mysql://localhost:3306/test?useSSL=false</connection-url>

                                    <driver-class>com.mysql.jdbc.Driver</driver-class>

                                    <driver>mysql</driver>

                                    <pool>

                                        <min-pool-size>2</min-pool-size>

                                        <max-pool-size>5</max-pool-size>

                                    </pool>

                                    <security>

                                        <user-name>root</user-name>

                                        <password>root</password>

                                    </security>

                                </datasource>

                 

                As per the Wildfly documentstion, I have placed the Driver (mysql-connector-java-5.1.42-bin.jar) at the following location along with module.xml:

                 

                /Users/satindersingh/servers/wildfly1010/modules/system/layers/base/com/mysql/main

                 

                The contents of module.xml are:

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

                <module xmlns="urn:jboss:module:1.1" name="com.mysql">

                    <resources>

                        <resource-root path="mysql-connector-java-5.1.42-bin.jar"/>             

                    </resources>

                    <dependencies>

                        <module name="javax.api"/>

                        <module name="javax.transaction.api"/>

                    </dependencies>

                </module>

                 

                The DataSource calling in the Servlet is:

                @WebServlet(name = "NewServlet", urlPatterns = {"/NewServlet"})

                public class NewServlet extends HttpServlet {

                   

                    private static final Logger LOGGER=Logger.getLogger("NewServlet");

                    @Resource(name = "java:/MySQLDS")

                    DataSource ds;

                 

                And the calling code within the same Servlet is:

                out.println("<h1>Servlet NewServlet at " + request.getContextPath() + "</h1>"); works well and displays

                Servlet NewServlet at /TestDS

                on the Browser.

                out.println("<h2>DataSource found: " + ds.toString() + "</h2>");works well and displays

                DataSource found: org.jboss.as.connector.subsystems.datasources.WildFlyDataSource@1e910448

                on the Browser.try{

                                    con=ds.getConnection(); works well and displays

                                      if (con!=null){

                                       

                                        out.println("<h2>Connection found: " + con.toString() + "</h2>"); works well and displays

                Connection found: org.jboss.jca.adapters.jdbc.jdk7.WrappedConnectionJDK7@4f4dd345

                                        out.println("<h2>Auto Commit is : " + con.getAutoCommit() + "</h2>");

                Display: Auto Commit is : true

                                        String catalog=con.getCatalog();

                                        if (catalog!=null){

                                           out.println("<h2>Catalog name is : " + catalog + "</h2>");

                Catalog name is : TEST

                                        }

                                        String query="select * from TEST.USER";

                                        Statement stm=con.createStatement();

                 

                ERROR IS HERE

                09:24:21,696 ERROR [stderr] (default task-1) org.h2.jdbc.JdbcSQLException: Schema "TEST" not found; SQL statement:

                09:24:21,705 ERROR [stderr] (default task-1) select * from TEST.USER [90079-173]

                • 5. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                  mayerw01

                  Try to use  @Resource(mappedName = "java:/MySQLDS") instead.

                  • 6. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                    ss_2000

                    @Wolfgang

                     

                    Thanks for your help, but it has not worked. I have just tried.

                     

                    Regards

                    • 7. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                      mayerw01

                      Is the message the same?

                      It looks like WildFly is using the default data source (ExampleDS) if mappedName is not specified.

                      • 8. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                        ss_2000

                        Yes message is indeed the same. Unfortunately, I can't check what DataSource is being used. I was hoping that DataSource.toString() method will reveal the name of the database being used. But that method does not return any 'plain english' value. It is just a gobbledygook

                         

                        org.jboss.as.connector.subsystems.datasources.WildFlyDataSource@7fb14721

                         

                        Unfortunately, even Connection.toString() method is not meaningful:

                        org.jboss.jca.adapters.jdbc.jdk7.WrappedConnectionJDK7@73e13d23

                         

                        But a method Connection.getCatalog() gives the expected value of the Schema name i.e "TEST"

                         

                        The following statements behave nicely as well:

                        Connection conn = ds.getConnection();

                        Statement stm=conn.createStatement();

                         

                        And finally the error is thrown when I perform -

                         

                        ResultSet rs=stm.executeQuery("select * from TEST.USER")

                        The exception is as per my original posting:

                        14:18:00,503 ERROR [stderr] (default task-1) org.h2.jdbc.JdbcSQLException: Schema "TEST" not found; SQL statement:

                        14:18:00,512 ERROR [stderr] (default task-1) select * from TEST.USER [90079-173]

                         

                        I shall keep trying but anu clue or help is greatly appreciated.

                         

                        Thanks

                        • 9. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                          mayerw01

                          That is really strange.

                          But you could try to figure out details regarding your connection via

                          con.getMetaData();

                          • 10. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                            jaikiran

                            I think it all comes down to using wrong datasource. In your standalone.xml your JNDI name for the datasource is java:/MySqlDS

                             

                            <datasource datasource jta="true" jndi-name="java:/MySqlDS"

                             

                            So you should be using:

                             

                            @Resource(lookup="java:/MySqlDS")

                             

                            to inject this datasource. The case of the string matters. In one of the replies, Wolfgang Mayer suggested this change, but there was an oversight with the case of the string which is why I think it didn't work (it's a different issue that it didn't throw an exception in that case).

                             

                            Do this specific change and get the latest logs (if it still fails).

                            • 11. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
                              ss_2000

                              Yes it works indeed. It was just a matter of getting the name of the resource in correct case.

                               

                              Thanks a lot everyone. Appreciate all your help.