After having gotten a servlet with a JDBC datasource working fine, I am now trying to wire up a JPA configuration. In doing so I have paged through hundreds of posts and manuals, and tried different things, and every time it all comes back to the same exception mentioned in the subject:
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MUSICIAN
As mentioned I have tried multiple different configurations, but here is what I have currently. I have two different servlets. I will describe the first because I get the same error on the second so same issue.
Oh - TomEE Plume 7.0.2, Oracle JDK 1.8.0_181, MariaDB 10.3.8.
First, my Musician class:
package gym;
...imports omitted for brevity...
@Entity
@Table(name="musician")
public class Musician {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="musician_id")
private int musician_id;
@Column(name="namelast")
private String namelast;
@Column(name="namefirst")
private String namefirst;
@Column(name="biography")
private String biography;
@Column(name="nickname")
private String nickname;
public Musician() {
}
... getters/setters omitted for brevity...
}
Here is the servlet code:
package gym;
... imports left out for brevity...
public class GymJPAServlet extends HttpServlet {
@PersistenceContext(name="gymPU",unitName="gymPU")
EntityManager em;
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
PrintWriter out = response.getWriter();
StringBuilder es = new StringBuilder();
es.append("Entity Manager Stuff: " + em.toString() + "<br/><br/>");
es.append("Entity Manager Contains: " + em.contains(gym.Musician.class) + "<br/><br/>");
Set entities = em.getMetamodel().getEntities();
es.append("Entities: " + entities + "<br/><br/>");
ArrayList results = new ArrayList();
try {
Musician m = em.find(Musician.class, 1);
es.append("found it : " + m + "<br/><br/>");
} catch (Exception e) {
... nonessential exception handling code omitted for brevity
}
if (results != null) {
for (Iterator i = results.iterator(); i.hasNext(); ) {
Musician m = (Musician)(i.next());
es.append("musician: " + m.toString() + "<br/><br/>");
}
} else {
es.append(es.toString());
}
out.write("<html><head></head><body>\n");
out.write(es.toString());
out.write("</body></html>\n");
}
}
My persistence.xml:
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
<persistence-unit name="gymPU" transaction-type="JTA">
<jta-data-source>jdbc/GymDS</jta-data-source>
<non-jta-data-source>jdbc/Gym</non-jta-data-source>
<class>gym.Musician</class>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema(ForeignKeys=true)"/>
</properties>
</persistence-unit>
</persistence>
my WEB-INF/resources.xml:
<Resource id="jdbc/GymDS" type="javax.sql.DataSource">
accessToUnderlyingConnectionAllowed = false
alternateUsernameAllowed = false
defaultAutoCommit = true
ignoreDefaultValues = false
initialSize = 0
jdbcDriver = org.mariadb.jdcbc.Driver
jdbcUrl = jdbc:mariadb://localhost:3306/gym
jtaManaged = true
maxActive = 20
maxIdle = 20
maxOpenPreparedStatements = 0
maxWaitTime = 10000
minEvictableIdleTime = 30 minutes
minIdle = 0
numTestsPerEvictionRun = 3
password = mypassword
passwordCipher = PlainText
poolPreparedStatements = false
testOnBorrow = true
testOnReturn = false
testWhileIdle = false
timeBetweenEvictionRuns = -1 millisecond
userName = gymadmin
</Resource>
And finally, my context.xml for reference for the non-jta ds:
<Context>
<Resource
name="jdbc/Gym"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
username="gymadmin" password="mypassword"
driverClassName="org.mariadb.jdbc.Driver"
url="jdbc:mariadb://localhost:3306/gym"/>
</Context>
So on the other end is the MariaDB instance. I am able to connect to it just fine using straight JDBC with no JPA, from servlets in the same container. This is important because I feel this all adds up to the fact that something is not wired right in my JPA setup, and so the "object not found" is that it cannot find it in the internal JPA setup rather than in the db on the other end. This is reinforced to me by clues from the TomEE log.
The other servlet I tried creating a separate MusicianDAO stateless bean, but it gives me the same exception. So I will not belabor this post by showing the second servlet code unless necessary later on.
Here are the clues from my tomee log. When deploying the app, I get:
19-Mar-2019 15:42:15.727 INFO [localhost-startStop-13] org.apache.openejb.config.ConfigurationFactory.configureApplication Configuring enterprise application: /home/omadmin/tomeeapps/webapps/gym-0.1.1-dev 19-Mar-2019 15:42:15.917 INFO [localhost-startStop-13] org.apache.openejb.config.ConfigurationFactory.configureService Configuring Service(id=gym-0.1.1-dev/jdbc/Gym, type=Resource, provider-id=ProvidedByTomcat) 19-Mar-2019 15:42:15.919 INFO [localhost-startStop-13] org.apache.openejb.assembler.classic.Assembler.createRecipe Creating Resource(id=gym-0.1.1-dev/jdbc/Gym) 19-Mar-2019 15:42:15.924 INFO [localhost-startStop-13] org.apache.openejb.config.AutoConfig.processResourceRef Auto-linking resource-ref 'jdbc/Gym' in bean gym-0.1.1-dev.Comp1022583414 to Resource(id=jdbc/Gym) 19-Mar-2019 15:42:15.925 INFO [localhost-startStop-13] org.apache.openejb.config.AutoConfig.processResourceRef Auto-linking resource-ref 'openejb/Resource/gym-0.1.1-dev/jdbc/Gym' in bean gym-0.1.1-dev.Comp1022583414 to Resource(id=gym-0.1.1-dev/jdbc/Gym) 19-Mar-2019 15:42:15.925 INFO [localhost-startStop-13] org.apache.openejb.config.AutoConfig.processResourceRef Auto-linking resource-ref 'openejb/Resource/jdbc/Gym' in bean gym-0.1.1-dev.Comp1022583414 to Resource(id=gym-0.1.1-dev/jdbc/Gym) 19-Mar-2019 15:42:15.925 INFO [localhost-startStop-13] org.apache.openejb.config.AutoConfig.deploy Configuring PersistenceUnit(name=gymPU) 19-Mar-2019 15:42:15.926 WARNING [localhost-startStop-13] org.apache.openejb.config.AutoConfig.deploy Found matching datasource: gym-0.1.1-dev/jdbc/Gym but this one is a JTA datasource 19-Mar-2019 15:42:15.926 WARNING [localhost-startStop-13] org.apache.openejb.config.AutoConfig.deploy Found matching datasource: gym-0.1.1-dev/jdbc/Gym but this one is a JTA datasource 19-Mar-2019 15:42:15.926 WARNING [localhost-startStop-13] org.apache.openejb.config.AutoConfig.deploy Found matching datasource: gym-0.1.1-dev/jdbc/Gym but this one is a JTA datasource 19-Mar-2019 15:42:15.927 INFO [localhost-startStop-13] org.apache.openejb.config.AutoConfig.setJtaDataSource Adjusting PersistenceUnit gymPU to Resource ID 'Default JDBC Database' from 'jdbc/GymDS' 19-Mar-2019 15:42:15.931 INFO [localhost-startStop-13] org.apache.openejb.config.AutoConfig.setNonJtaDataSource Adjusting PersistenceUnit gymPU to Resource ID 'Default Unmanaged JDBC Database' from 'jdbc/Gym' 19-Mar-2019 15:42:15.936 INFO [localhost-startStop-13] org.apache.openejb.config.AppInfoBuilder.build Enterprise application "/home/omadmin/tomeeapps/webapps/gym-0.1.1-dev" loaded. 19-Mar-2019 15:42:15.937 INFO [localhost-startStop-13] org.apache.openejb.assembler.classic.Assembler.createApplication Assembling app: /home/omadmin/tomeeapps/webapps/gym-0.1.1-dev 19-Mar-2019 15:42:15.975 INFO [localhost-startStop-13] org.apache.openejb.assembler.classic.ReloadableEntityManagerFactory.createDelegate PersistenceUnit(name=gymPU, provider=org.eclipse.persistence.jpa.PersistenceProvider) - provider time 29ms 19-Mar-2019 15:42:16.043 INFO [localhost-startStop-13] org.apache.tomee.catalina.TomcatWebAppBuilder.deployWebApps using context file /home/omadmin/tomeeapps/webapps/gym-0.1.1-dev/META-INF/context.xml 19-Mar-2019 15:42:16.046 INFO [localhost-startStop-13] org.apache.openejb.assembler.classic.Assembler.createApplication Deployed Application(path=/home/omadmin/tomeeapps/webapps/gym-0.1.1-dev) 19-Mar-2019 15:42:16.308 INFO [localhost-startStop-13] sun.reflect.DelegatingMethodAccessorImpl.invoke Deployment of web application archive /home/omadmin/tomeeapps/webapps/gym-0.1.1-dev.war has finished in 839 ms [EL Info]: 2019-03-19 15:43:47.421--ServerSession(1356468468)--EclipseLink, version: Eclipse Persistence Services - 2.6.3.v20160428-59c81c5 [EL Info]: 2019-03-19 15:43:47.499--ServerSession(1356468468)--/file:/home/omadmin/tomeeapps/webapps/gym-0.1.1-dev/_gymPU login successful [EL Warning]: 2019-03-19 15:43:47.506--ServerSession(1356468468)--Problem while registering MBean: java.lang.NullPointerException [EL Warning]: 2019-03-19 15:43:47.51--UnitOfWork(570034438)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.v20160428-59c81c5): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MUSICIAN Error Code: -5501 Call: SELECT musician_id, biography, namefirst, namelast, nickname FROM musician WHERE (musician_id = ?) bind => [1 parameter bound] Query: ReadObjectQuery(name="readMusician" referenceClass=Musician sql="SELECT musician_id, biography, namefirst, namelast, nickname FROM musician WHERE (musician_id = ?)")
So.. being a complete JEE noob here, the best I can come up with is that it appears to me I still don't have something wired right in the config files, and so because of that, it does away with my gymDS data source and replaces it with some default internal db representation, and then when the database call is made, well of course it cant find the MUSICIAN object in this alternate reality version of a database "Default JDBC database" as seen in the log snippet above.
The "file:... login successful" thing is suspicious too but I suspect that's a side effect of the previously mentioned issue.
NOTE also I have not done anything with the global tomee.xml because I was trying to do this local to the app and not global. I mention this because some things I read seem to indicate I may need to touch that file anyway but I am not to that point yet.
Anyway I don't know what else to try. I have not tried a different container yet but maybe I need to spin all this up under a different JEE system such as OpenLiberty to see if the same thing happens.
In the mean time, can anyone shed any light? It's got to be something simple, a field I am missing here or there or perhaps my persistence.xml is in the wrong location, something...???