Announcement Announcement Module
Collapse
No announcement yet.
Problem with JDBC DAO and MySQL Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with JDBC DAO and MySQL

    I have been trying to adapt the 'simplest acegi example webapp' from http://www.fstxblog.com/completely-g...-possible.html
    to use a JDBC DAO, but have never succeeded. I'm using MySQL 4.1. I created the database according to the schema in the dbinit.txt file of the acegi distribution. In my applicationContext.xml I have the following entries:

    Code:
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
      <property name="url"><value>jdbc&#58;mysql//localhost&#58;3306/acegitest</value></property>
      <property name="username"><value>root</value></property>
      <property name="password"><value></value></property>
    </bean>
    
     <bean id="jdbcDaoImpl" class="net.sf.acegisecurity.providers.dao.jdbc.JdbcDaoImpl">
    <property name="dataSource"><ref bean="dataSource"/></property>
    <property name="usersByUsernameQuery">
    <value>SELECT username,password,'true' as enabled FROM users WHERE username = ?</value>
    </property>
    <property name="authoritiesByUsernameQuery">
    <value>SELECT username,authority FROM authorities WHERE username = ?</value>
    </property>
    </bean>
    
    <bean id="daoAuthenticationProvider" class="net.sf.acegisecurity.providers.dao.DaoAuthenticationProvider">
    <property name="authenticationDao">
    	         <ref local="jdbcDaoImpl"/>
    </property>
    </bean>
    The content of the tables is the same as in the dbinit.txt file (so, with marissa, scott etc as users). If I then run the application and try to log in, I can never be logged in, although I write the correct password for each user, the same as in the database. What is more, I don't get any errors in the tomcat logs, so that I don't know what can be wrong. The only problem I observed, that during the initialization of the acegitest1-app.context tomcat gives the following warnings in the log file:

    Code:
    INFO&#58; SQLErrorCodes loaded&#58; &#91;HSQL Database Engine, Oracle, Sybase SQL Server, Informix Dynamic Server, Microsoft SQL Server, PostgreSQL, MySQL, DB2*&#93;
    2005-02-16 23&#58;12&#58;15 org.springframework.jdbc.support.SQLErrorCodesFactory getErrorCodes
    WARNING&#58; Error while extracting database product name - falling back to empty error codes
    2005-02-16 23&#58;12&#58;15 org.springframework.jdbc.support.SQLErrorCodesFactory getErrorCodes
    WARNING&#58; Error while extracting database product name - falling back to empty error codes
    2005-02-16 23&#58;12&#58;15 org.springframework.jdbc.support.SQLErrorCodesFactory getErrorCodes
    WARNING&#58; Error while extracting database product name - falling back to empty error codes
    2005-02-16 23&#58;12&#58;15 org.springframework.beans.factory.support.AbstractBeanFactory getBean
    So, I assume that I simply don't get any database error messages at all as there are problems with loading these error codes. Does anyone have a good and simple working example of acegi with JDBC and MySQL or maybe can solve my problem?

  • #2
    I'll move this over to the data access forum, as JdbcDaoImpl is just a user of Spring's JDBC abstraction layer and perhaps someone has seen this behaviour with MySql.

    Comment


    • #3
      I tried this example with HSQL and get the same result. So it seems that it does not depend on MySQL.

      Comment


      • #4
        So, I assume that I simply don't get any database error messages at all as there are problems with loading these error codes.
        No, Spring should still throw exceptions, it just won't be able to categorize them properly.

        So I don't think Spring will be swallowing any exceptions, and doubt that's the problem. I would suggest writing a JUnit test doing your data access out of container (extend one of the test classes in org.springframework.test package in spring-mock.jar: look at latest PetClinic code for an example) to simplify digging further. Also, you might want to turn on DEBUG logging for the Spring JdbcTemplate.

        Comment


        • #5
          I think you have a problem logging in to the database - check that the username/password you are using can connect via JDBC. Even if you can login using root from the command line mysql, it doesn't mean that you can connect via JDBC using root. With JDBC you look like root@localhost to MySQL and that is a separate set of database privileges.

          Comment


          • #6
            Originally posted by trisberg
            I think you have a problem logging in to the database - check that the username/password you are using can connect via JDBC. Even if you can login using root from the command line mysql, it doesn't mean that you can connect via JDBC using root. With JDBC you look like root@localhost to MySQL and that is a separate set of database privileges.
            No, it's definitely not the problem. Generally, connecting to the database with JDBC works without problems, also with Spring. I have tried other simple examples with JDBC and Spring and everything works perfectly. Only this case with the Acegi authorisation fails.

            Comment


            • #7
              I found the problem, it was of course my fault I just mistyped the URL of the database in the datasource-configuration:
              Code:
              <property name="url"><value>jdbc&#58;mysql//localhost&#58;3306/acegitest</value></property>
              I forgot the ":" after 'mysql' But I the problem is still, that I didn't get any error messages because of it I simply used this datasource to another JDBC-Connection (not the one of the Acegi System) and I a Spring exception 'no suitable driver'.

              So it might be still a problem for me, if I want to work with Acegi, but without appropiate error messages it's very difficult

              Comment


              • #8
                it's a log4j configuration problem among other things...

                Hi johnny 2k5...
                I was in kind of the same predictment that you are, here are some comments:
                First the debug options in your log4j.xml you must have this package in debug option in order to see kindof the errors it's producing, not jus the INFOs and WARNINGs you were seeing

                Code:
                     <category name="org.springframework.jdbc">
                         <priority value="DEBUG" />
                     </category>
                And the seccond part it's a password encryption problem the one we had!, here's my post http://forum.springframework.org/showthread.php?t=15847 with all the configuration

                hope it helps, enjoy life
                Last edited by robyn; May 16th, 2006, 04:57 AM.

                Comment

                Working...
                X