Hi

I get a strange column not found exceptionwhen trying to handle the MySQL system table mysql.user.
In a nutshell, Hibernate generate me a perfectly correct SQL query (tested it by console client), that throws me an unexpected columnn not found error.

(details below)

I think it might be a jdbc isuue rather than a hibernate one, but I'm quite confused with it. if someone can help...

Hibernate version: 3.0.5

Mapping documents:
I use a view on mysql.user table, defined as follow (thought it came from too long filed names)

CREATE OR REPLACE VIEW MySQLUser_mod AS SELECT
host AS host,
user AS user,
password AS pwd,
select_priv AS sel,
delete_priv AS del,
insert_priv AS ins,
update_priv AS upd,
create_priv AS cre,
create_tmp_table_priv AS tmp,
reload_priv AS rld,
drop_priv AS drp,
super_priv AS sup
FROM mysql.user;

the mapping file (shortened):

<hibernate-mapping package="c.gbd.affaires.data">
<class name="MySQLUser" table="MySQLUser_mod">


<composite-id class="MySQLUserPK" name="Id">
<key-property column="host" name="Host" type="string" />
<key-property column="user" name="User" type="string" />
</composite-id>
<!-- DONNEES DE LA TABLE -->

<property
column="pwd"
length="41"
name="Password"
not-null="true"
type="string"
/>
<property
column="sel"
length="2"
name="SelectPriv"
not-null="true"
type="java.lang.String"
/>
<property
column="del"
length="2"
name="DeletePriv"
not-null="true"
type="java.lang.String"
/>
<property
column="ins"
length="2"
name="InsertPriv"
not-null="true"
type="java.lang.String"
/>
<property
column="upd"
length="2"
name="UpdatePriv"
not-null="true"
type="java.lang.String"
/>
<property
column="cre"
length="2"
name="CreatePriv"
not-null="true"
type="java.lang.String"
/>
<property
column="tmp"
length="2"
name="CreateTmpTablePriv"
not-null="true"
type="java.lang.String"
/>
<property
column="rld"
length="2"
name="ReloadPriv"
not-null="true"
type="java.lang.String"
/>
<property
column="Drp"
length="2"
name="DropPriv"
not-null="true"
type="java.lang.String"
/>
<property
column="Sup"
length="2"
name="SuperPriv"
not-null="true"
type="java.lang.String"
/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
(using the HibernateUtil class defined at http://www.hibernate.org/hib_docs/v3/re ... ngwithcats )

## in this class ##

public static Session currentSession() throws HibernateException{
Session s = (Session) myThreadLocal.get();
if (s == null){
s = sessionFactory.openSession();
myThreadLocal.set(s);
}
return s;
}

## the querying method ##
public static Object RequeteUnResultat(String hql) throws HibernateUtilException{
Session session = HibernateUtil.currentSession();
Transaction tx = session.beginTransaction();
Query myQuery = session.createQuery(hql);
tx.commit();

Object tmp = myQuery.uniqueResult();
HibernateUtil.closeSession();
// .....
}

## also tried to list the query content inside the session open/close

## the calling location ##
MySQLUser resultat = null;
try{
resultat = (MySQLUser) HibernateUtil.RequeteUnResultat(
"from MySQLUser where user='" + super.getLogin() + "' and host='" + super.getHost() + "'" );
}
catch(Exception e){ HibernateUtil.TraiteException(e); }




Full stack trace of any exception that occurs:
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
at c.gbd.affaires.HibernateUtil.RequeteUnResultat(HibernateUtil.java:129)
at c.gbd.affaires.data.User.forceInitMySQLUser(User.java:47)
at c.gbd.affaires.data.User.initMySQLUser(User.java:71)
at c.gbd.affaires.DebugAffaires.main(DebugAffaires.java:77)
Caused by: java.sql.SQLException: Column 'pwd10_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:910)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4942)
at org.hibernate.type.StringType.get(StringType.java:16)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:80)
at org.hibernate.persister.entity.BasicEntityPersister.hydrate(BasicEntityPersister.java:1690)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:991)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:942)
at org.hibernate.loader.Loader.getRow(Loader.java:855)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:305)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 10 more

Name and version of the database you are using:
MySQL 5.0.7 (beta!)
mysqlconnector/J 3.1.10

The generated SQL (show_sql=true):
27610 [main] DEBUG org.hibernate.SQL - /* from MySQLUser where user='lg' and host='localhost' */ select mysqluser0_.host as host, mysqluser0_.user as user, mysqluser0_.pwd as pwd10_, mysqluser0_.sel as sel10_, mysqluser0_.del as del10_, mysqluser0_.ins as ins10_, mysqluser0_.upd as upd10_, mysqluser0_.cre as cre10_, mysqluser0_.tmp as tmp10_, mysqluser0_.rld as rld10_, mysqluser0_.Drp as Drp10_, mysqluser0_.Sup as Sup10_ from MySQLUser_mod mysqluser0_ where user='lg' and host='localhost'

Debug level Hibernate log excerpt:

26428 [main] DEBUG org.hibernate.transaction.JDBCTransaction - begin
26428 [main] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
26438 [main] DEBUG org.hibernate.connection.DriverManagerConnectionProvider - total checked-out connections: 0
26438 [main] DEBUG org.hibernate.connection.DriverManagerConnectionProvider - using pooled JDBC connection, pool size: 0
26438 [main] DEBUG org.hibernate.transaction.JDBCTransaction - current autocommit status: false
26438 [main] DEBUG org.hibernate.transaction.JDBCTransaction - commit
26448 [main] DEBUG org.hibernate.impl.SessionImpl - automatically flushing session
26448 [main] DEBUG org.hibernate.jdbc.JDBCContext - before transaction completion
26448 [main] DEBUG org.hibernate.impl.SessionImpl - before transaction completion
26458 [main] DEBUG org.hibernate.transaction.JDBCTransaction - committed JDBC Connection
26468 [main] DEBUG org.hibernate.jdbc.JDBCContext - after transaction completion
26468 [main] DEBUG org.hibernate.impl.SessionImpl - after transaction completion
26468 [main] DEBUG org.hibernate.transaction.JDBCTransaction - begin
26468 [main] DEBUG org.hibernate.transaction.JDBCTransaction - current autocommit status: false
26508 [main] DEBUG org.hibernate.transaction.JDBCTransaction - commit
26508 [main] DEBUG org.hibernate.impl.SessionImpl - automatically flushing session
26508 [main] DEBUG org.hibernate.jdbc.JDBCContext - before transaction completion
26508 [main] DEBUG org.hibernate.impl.SessionImpl - before transaction completion
26518 [main] DEBUG org.hibernate.transaction.JDBCTransaction - committed JDBC Connection
26518 [main] DEBUG org.hibernate.jdbc.JDBCContext - after transaction completion
26518 [main] DEBUG org.hibernate.impl.SessionImpl - after transaction completion
26528 [main] DEBUG org.hibernate.impl.SessionImpl - find: from MySQLUser where user='lg' and host='localhost'
26538 [main] DEBUG org.hibernate.engine.QueryParameters - named parameters: {}
26929 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - parse() - HQL: from c.gbd.affaires.data.MySQLUser where user='lg' and host='localhost'
26989 [main] DEBUG org.hibernate.hql.ast.AST - --- HQL AST ---
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| \-[FROM] 'from'
| \-[RANGE] 'RANGE'
| \-[DOT] '.'
| +-[DOT] '.'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[IDENT] 'c'
| | | | \-[IDENT] 'gbd'
| | | \-[IDENT] 'affaires'
| | \-[IDENT] 'data'
| \-[IDENT] 'MySQLUser'
\-[WHERE] 'where'
\-[AND] 'and'
+-[EQ] '='
| +-[IDENT] 'user'
| \-[QUOTED_STRING] ''lg''
\-[EQ] '='
+-[IDENT] 'host'
\-[QUOTED_STRING] ''localhost''

26999 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
27179 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - query() << begin, level = 1
27300 [main] DEBUG org.hibernate.hql.ast.FromElement - FromClause{level=1} : c.gbd.affaires.data.MySQLUser (no alias) -> mysqluser0_
27390 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - query() : finishing up , level = 1
27400 [main] DEBUG org.hibernate.hql.ast.HqlSqlWalker - processQuery() : ( SELECT ( FromClause{level=1} MySQLUser_mod mysqluser0_ ) ( where ( and ( = user 'lg' ) ( = host 'localhost' ) ) ) )
27440 [main] DEBUG org.hibernate.hql.ast.HqlSqlWalker - Derived SELECT clause created.
27470 [main] DEBUG org.hibernate.hql.ast.JoinProcessor - Using FROM fragment [MySQLUser_mod mysqluser0_]
27470 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - query() >> end, level = 1
27490 [main] DEBUG org.hibernate.hql.ast.AST - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (MySQLUser_mod)
+-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
| +-[SELECT_EXPR] SelectExpressionImpl: 'mysqluser0_.host as host, mysqluser0_.user as user' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=MySQLUser_mod,tableAlias=mysqluser0_,colums={,className=c.gbd.affaires.data.MySQLUser}}}
| \-[SQL_TOKEN] SqlFragment: 'mysqluser0_.pwd as pwd10_, mysqluser0_.sel as sel10_, mysqluser0_.del as del10_, mysqluser0_.ins as ins10_, mysqluser0_.upd as upd10_, mysqluser0_.cre as cre10_, mysqluser0_.tmp as tmp10_, mysqluser0_.rld as rld10_, mysqluser0_.Drp as Drp10_, mysqluser0_.Sup as Sup10_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[], fromElementByTableAlias=[mysqluser0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'MySQLUser_mod mysqluser0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=MySQLUser_mod,tableAlias=mysqluser0_,colums={,className=c.gbd.affaires.data.MySQLUser}}
\-[WHERE] SqlNode: 'where'
\-[AND] SqlNode: 'and'
+-[EQ] SqlNode: '='
| +-[IDENT] IdentNode: 'user' {originalText=user}
| \-[QUOTED_STRING] LiteralNode: ''lg''
\-[EQ] SqlNode: '='
+-[IDENT] IdentNode: 'host' {originalText=host}
\-[QUOTED_STRING] LiteralNode: ''localhost''

27490 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
27570 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - HQL: from c.gbd.affaires.data.MySQLUser where user='lg' and host='localhost'
27570 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select mysqluser0_.host as host, mysqluser0_.user as user, mysqluser0_.pwd as pwd10_, mysqluser0_.sel as sel10_, mysqluser0_.del as del10_, mysqluser0_.ins as ins10_, mysqluser0_.upd as upd10_, mysqluser0_.cre as cre10_, mysqluser0_.tmp as tmp10_, mysqluser0_.rld as rld10_, mysqluser0_.Drp as Drp10_, mysqluser0_.Sup as Sup10_ from MySQLUser_mod mysqluser0_ where user='lg' and host='localhost'
27580 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
27610 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
27610 [main] DEBUG org.hibernate.SQL - /* from MySQLUser where user='lg' and host='localhost' */ select mysqluser0_.host as host, mysqluser0_.user as user, mysqluser0_.pwd as pwd10_, mysqluser0_.sel as sel10_, mysqluser0_.del as del10_, mysqluser0_.ins as ins10_, mysqluser0_.upd as upd10_, mysqluser0_.cre as cre10_, mysqluser0_.tmp as tmp10_, mysqluser0_.rld as rld10_, mysqluser0_.Drp as Drp10_, mysqluser0_.Sup as Sup10_ from MySQLUser_mod mysqluser0_ where user='lg' and host='localhost'
Hibernate: /* from MySQLUser where user='lg' and host='localhost' */ select mysqluser0_.host as host, mysqluser0_.user as user, mysqluser0_.pwd as pwd10_, mysqluser0_.sel as sel10_, mysqluser0_.del as del10_, mysqluser0_.ins as ins10_, mysqluser0_.upd as upd10_, mysqluser0_.cre as cre10_, mysqluser0_.tmp as tmp10_, mysqluser0_.rld as rld10_, mysqluser0_.Drp as Drp10_, mysqluser0_.Sup as Sup10_ from MySQLUser_mod mysqluser0_ where user='lg' and host='localhost'
27620 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
27720 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
27730 [main] DEBUG org.hibernate.loader.Loader - processing result set
27730 [main] DEBUG org.hibernate.loader.Loader - result set row: 0
27730 [main] DEBUG org.hibernate.type.StringType - returning 'localhost' as column: host
27730 [main] DEBUG org.hibernate.type.StringType - returning 'lg' as column: user
27750 [main] DEBUG org.hibernate.loader.Loader - result row: EntityKey[c.gbd.affaires.data.MySQLUser#component[Host,User]{Host=localhost, User=lg}]
27750 [main] DEBUG org.hibernate.loader.Loader - Initializing object from ResultSet: [c.gbd.affaires.data.MySQLUser#component[Host,User]{Host=localhost, User=lg}]
27780 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister - Hydrating entity: [c.gbd.affaires.data.MySQLUser#component[Host,User]{Host=localhost, User=lg}]
27880 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close ResultSet (open ResultSets: 1, globally: 1)
27880 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
27880 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
27910 [main] DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query [select mysqluser0_.host as host, mysqluser0_.user as user, mysqluser0_.pwd as pwd10_, mysqluser0_.sel as sel10_, mysqluser0_.del as del10_, mysqluser0_.ins as ins10_, mysqluser0_.upd as upd10_, mysqluser0_.cre as cre10_, mysqluser0_.tmp as tmp10_, mysqluser0_.rld as rld10_, mysqluser0_.Drp as Drp10_, mysqluser0_.Sup as Sup10_ from MySQLUser_mod mysqluser0_ where user='lg' and host='localhost']
java.sql.SQLException: Column 'pwd10_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:910)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4942)
at org.hibernate.type.StringType.get(StringType.java:16)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:80)
at org.hibernate.persister.entity.BasicEntityPersister.hydrate(BasicEntityPersister.java:1690)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:991)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:942)
at org.hibernate.loader.Loader.getRow(Loader.java:855)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:305)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
at c.gbd.affaires.HibernateUtil.RequeteUnResultat(HibernateUtil.java:129)
at c.gbd.affaires.data.User.forceInitMySQLUser(User.java:47)
at c.gbd.affaires.data.User.initMySQLUser(User.java:71)
at c.gbd.affaires.DebugAffaires.main(DebugAffaires.java:77)
27920 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: S0022
27920 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Column 'pwd10_' not found.
27920 [main] DEBUG org.hibernate.jdbc.JDBCContext - after autocommit
<stack trace>

Hi,

Did you manage to solve this issue? I am getting the same error and I am unable to figure out what the problem must be!

Thanks,
Nat