Ebean - Dynamic Query - Prepared Statement's Mismatched Parameter Count Error

Question

I'm hoping someone with more Ebean expertise than myself can help me with an erratic issue I'm troubleshooting now.

The environment:

• Java 1.7.0_17
• MySQL 5.5
• Play Framework 2.1.1 (however I've upgraded to Play 2.2.1 and this problem still existed)

My main problem is that I'm getting alternating SQLExceptions when I restart Play, all having to do with either too many or too few parameters in the prepared statement that Ebean is creating. Data set A works half of the time when data set B fails, but when restarting Play, data set B works while data set A fails.

I'm building an Ebean Query object dynamically based on a Set of Filter objects I've written passed into a function. The Ebean Query is finding all objects that meet the given requirements of each Filter.

The function is similar to:

private List<Contact> contacts getContacts(Set<Filter> filters) {
    Query<Contact> query = Contact.find;
    for (Filter filter : filters) {
        filter.apply(query);
    }
    List<Contact> contacts = query.findList();
}

An example of a Filter:

public void apply(Query<Contact> query) {
    query.where().in("tags", getTags());
}

First off, is this type of query building supported in Ebean? I believe it is as this works the majority of the time, however, I get problems with running the "getContacts" function several hundred times with certain data, but only sometimes...

So stick with me, as this issue is extremely confusing. I'm also going to glaze over most of the details of what the data actually is, but if you want/need more information, please ask.

Data set A's query and exception:

select distinct t0.contact_id c0, t0.contact_uuid c1, t0.bounce c2 
from contact t0
join email_record u1 on u1.contact_id = t0.contact_id 
join contact_tag u2z_ on u2z_.contact_id = t0.contact_id 
join tag u2 on u2.tag_id = u2z_.tag_id  
where u1.status = ?  and t0.unit_id = ?  and u2.tag_id in (?,?,?)  and t0.unit_id in (? )  and t0.campaign_id in (?,?,?,?,?,?,?,?,?,?,?,?)

[PersistenceException: Error with property[19] dt[4]data[1464][java.lang.Integer]]
Caused by: java.sql.SQLException: Parameter index out of range (19 > number of parameters, which is 18).

Then after a Play restart, data set B's query and exception:

[PersistenceException: Query threw SQLException:No value specified for parameter 19 Bind values:[SENT, 1290, 8988, 13032, 13052, 1290, 96, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 222] Query was: select distinct t0.contact_id c0, t0.contact_uuid c1, t0.bounce c2 from contact t0 join email_record u1 on u1.contact_id = t0.contact_id join contact_tag u2z_ on u2z_.contact_id = t0.contact_id join tag u2 on u2.tag_id = u2z_.tag_id where u1.status = ? and t0.unit_id = ? and u2.tag_id in (?,?) and t0.unit_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) and t0.campaign_id in (?,?,?,?,?,?,?,?,?,?,?,?) ]

I feel that the most perplexing piece about this is how it switches, reliably 100% of the time back and forth on restart.

Has anyone ever seen anything like this before? Just to add to the confusion, it's additionally doing something very similar (on different data sets) on our production environment, but I cannot reproduce this on my development or testing machines even after dumping the database and loading it locally. The problem described above though is reproducible everywhere.

Answer 1

I've seen something similar (on DB2) using prepared statements. Querties only worked for the number of parameters the first statement executed had. Only the number of parameters it had the first time it was invoked or less would work. It probably set aside a buffer for the parameters - I believe it acted like the extra parameters did not exist.

If the first call had enough parameters then everything would be fine until you hit more than that number of parameters. But the next run might fail immediately if the first query only had 1 parameter and the next call had more than one.

Check if it works with a regular statement instead of a prepared statement. If so suspect an error in prepared statements handling for variable numbers of parameters.