You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Lucas Holt <lu...@foolishgames.com> on 2010/06/18 22:15:10 UTC
Strange Cayenne problem
Earlier today, I was writing some unit tests and stumbled onto a strange
problem with some existing code we have. A result is getting returned
for something that does not exist in the database.
I've included the method that's getting called below. Given a specific
input, we get a valid object even though there is nothing close to it.
We have a system with a "publications" table and a publication editions
table. The latter is an instance of a publication typically used when
we have a newsletter from a specific date. If we use today's date and a
publication name we'll call A, we get a PublicationEditions object for
publication B on a different date from last year. The primary key is
different as is all the other meta data for the PublicationEditions
object (a cayenne generated class).
The only possibility I've come up with is that there is some hash used
in the system and we happened to hit a collision. Is this a
possibility? We've been using Cayenne's LRU algorithm, but I've tried
switching to oscache today as well with the same results (also LRU).
Lucas Holt
public com.prime.pcd.db.PublicationEditions lookup(final String
publicationName, final Date date)
throws IllegalArgumentException, ModelException {
com.prime.pcd.db.PublicationEditions pe = null;
if (publicationName == null || publicationName.length() > 255
|| publicationName.length() < 1) {
throw new IllegalArgumentException("Invalid publication name");
}
if (date == null) {
throw new IllegalArgumentException("Invalid date");
}
try {
Expression qualifier =
Expression.fromString("publicationEditionsPublication.publicationName =
$pn and publicationEditionDate=$date");
final HashMap<String, Object> map = new HashMap<String,
Object>(2);
map.put("pn", publicationName);
map.put("date", date);
qualifier = qualifier.expWithParameters(map);
final SelectQuery query = new
SelectQuery(com.prime.pcd.db.PublicationEditions.class, qualifier);
final List<com.prime.pcd.db.PublicationEditions> editions =
dataContext.performQuery(query);
logger.debug( "Number of found items is " + editions.size());
if (!editions.isEmpty()) {
pe = editions.get(0);
logger.debug(pe.getPublicationEditionsId() + ", " +
pe.getPublicationEditionsPublication().getPublicationName());
}
} catch (CayenneRuntimeException ce) {
logger.error("Cayenne error looking up edition: " +
ce.getMessage());
throw new ModelException("Could not lookup edition");
}
return pe;
}
Re: Strange Cayenne problem
Posted by Lucas Holt <lu...@foolishgames.com>.
Disregard my last email. I found the problem. UTS error; I was using
the test database :)
Lucas
Re: Strange Cayenne problem
Posted by Lucas Holt <lu...@foolishgames.com>.
On 06/19/10 10:48, Andrus Adamchik wrote:
> This is odd since your query is not even using caching, so it hits the
> DB and returns the data from there. Do you have Cayenne SQL logging
> enabled and does the generated query look like what you expected?
>
> Andrus
>
This is the log data. It should not have found a row.
2010-06-18 12:30:10,021 [main] INFO
org.apache.cayenne.access.QueryLogger - SELECT
`t0`.`publication_edition_date`, `t0`.`publication_edition_id`,
`t0`.`publication_edition_arrived`, `t0`.`publication_id
` FROM `publication_editions` `t0` JOIN `publications` `t1` ON
(`t0`.`publication_id` = `t1`.`publication_id`) WHERE
(`t1`.`publication_name` = ?) AND (`t0`.`publication_edition_date` = ?)
[bind: 1->publ
ication_name:'Prime research', 2->publication_edition_date:'2010-06-18
12:30:10.012']
2010-06-18 12:30:10,023 [main] INFO
org.apache.cayenne.access.QueryLogger - === returned 1 row. - took 2 ms.
2010-06-18 12:30:10,023 [main] INFO
org.apache.cayenne.access.QueryLogger - +++ transaction committed.
Then later it does an inserting using the same item to another table.
2010-06-18 12:30:10,102 [main] INFO
org.apache.cayenne.access.QueryLogger - INSERT INTO `articles`
(`abstract_key_messages`, `abstract_lead`, `abstract_quotes`,
`article_status_id`, `creator_user_id`, `d
ate_created`, `date_published`, `date_updated`, `etext_id`, `headline`,
`journalist_id`, `project_id`, `publication_edition_id`, `subhead`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2010-06-18 12:30:10,103 [main] INFO
org.apache.cayenne.access.QueryLogger - [bind:
1->abstract_key_messages:NULL, 2->abstract_lead:NULL,
3->abstract_quotes:NULL, 4->article_status_id:01, 5->creator_user_
id:2000, 6->date_created:'2010-06-18 12:30:10.04',
7->date_published:'2010-06-18 12:30:10.028', 8->date_updated:'2010-06-18
12:30:10.04', 9->etext_id:NULL, 10->headline:'JUNIT Test Article',
11->journali
st_id:NULL, 12->project_id:6, 13->publication_edition_id:237,
14->subhead:NULL]
What doesn't make sense here is what's in the database versus what's
returned.
Publication editions contains the following for the pk 237
publication_edition_id publication_id publication_edition_date
publication_edition_arrived
237 41 2010-02-02 00:00:00 0
Publication 1 is "Prime research" whereas publication 41 is physorg.com.
Here is the MySQL schema for the two tables
CREATE TABLE IF NOT EXISTS `publications` (
`publication_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`publication_category_id` smallint(5) unsigned NOT NULL,
`publication_owner_id` tinyint(3) unsigned NOT NULL,
`country_id` smallint(5) unsigned NOT NULL,
`parent_id` int(11) DEFAULT NULL,
`publication_onsale_days_before` tinyint(3) unsigned NOT NULL,
`publication_name` varchar(255) NOT NULL,
`national_publication` tinyint(1) NOT NULL COMMENT 'national or
regional',
`primary_language` tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`publication_id`),
UNIQUE KEY `publication_country_name` (`country_id`,`publication_name`),
KEY `publication_country` (`country_id`),
KEY `publication_category` (`publication_category_id`),
KEY `publication_office` (`publication_owner_id`),
KEY `publication_parent` (`parent_id`),
KEY `publication_name` (`publication_name`) USING BTREE,
KEY `publication_language` (`primary_language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2001 ;
CREATE TABLE IF NOT EXISTS `publication_editions` (
`publication_edition_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`publication_id` int(10) unsigned NOT NULL,
`publication_edition_date` datetime NOT NULL,
`publication_edition_arrived` tinyint(1) NOT NULL,
PRIMARY KEY (`publication_edition_id`),
KEY `publication_edition_pub` (`publication_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5087 ;
And from the cayenne map:
<db-entity name="publication_editions" catalog="prime_pcd2009">
<db-attribute name="publication_edition_arrived" type="BOOLEAN"
isMandatory="true"/>
<db-attribute name="publication_edition_date" type="DATE"
isMandatory="true"/>
<db-attribute name="publication_edition_id" type="BIGINT"
isPrimaryKey="true" isGenerated="true" isMandatory="true" length="10"/>
<db-attribute name="publication_id" type="BIGINT" isMandatory="true"
length="10"/>
</db-entity>
...
<db-entity name="publications" catalog="prime_pcd2009">
<db-attribute name="country_id" type="BIGINT" isMandatory="true"
length="10"/>
<db-attribute name="national_publication" type="BOOLEAN"
isMandatory="true"/>
<db-attribute name="parent_id" type="BIGINT" length="10"/>
<db-attribute name="primary_language" type="TINYINT" isMandatory="true"/>
<db-attribute name="publication_category_id" type="BIGINT"
isMandatory="true" length="10"/>
<db-attribute name="publication_id" type="BIGINT" isPrimaryKey="true"
isGenerated="true" isMandatory="true" length="10"/>
<db-attribute name="publication_name" type="VARCHAR" isMandatory="true"
length="255"/>
<db-attribute name="publication_onsale_days_before" type="TINYINT"
isMandatory="true" length="2"/>
<db-attribute name="publication_owner_id" type="TINYINT"
isMandatory="true"/>
</db-entity>
It's quite possible I'm making some obvious mistake here, but there is
only one record for the publication name "Prime research" in the
publication_editions table which is:
<http://mail.primemediaanalysis.com/phpmyadmin/sql.php?db=prime_pcd2009&table=publication_editions&sql_query=DELETE+FROM+%60prime_pcd2009%60.%60publication_editions%60+WHERE+%60publication_editions%60.%60publication_edition_id%60+%3D+1&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dprime_pcd2009%26table%3Dpublication_editions%26sql_query%3DSELECT%2B%252A%2B%2BFROM%2B%2560publication_editions%2560%2BWHERE%2B%2560publication_id%2560%2B%253D%2B1%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dsql.php%253Fdb%253Dprime_pcd2009%2526amp%253Btable%253Dpublication_editions%2526amp%253Bserver%253D1%2526amp%253Btoken%253Df1870468188ec6208aa4dc611e33fbf1%2526amp%253Bsql_query%253DSELECT%252B%25252A%252B%252BFROM%252B%252560publication_editions%252560%252BWHERE%252B%252560publication_id%252560%252B%25253D%252B1%26server%3D1%26token%3Df1870468188ec6208aa4dc611e33fbf1&server=1&token=f1870468188ec6208aa4dc611e33fbf1>
1 1 2009-09-11 00:00:00 0
I would expect no match.
Re: Strange Cayenne problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
This is odd since your query is not even using caching, so it hits the
DB and returns the data from there. Do you have Cayenne SQL logging
enabled and does the generated query look like what you expected?
Andrus
On Jun 18, 2010, at 11:15 PM, Lucas Holt wrote:
> Earlier today, I was writing some unit tests and stumbled onto a
> strange problem with some existing code we have. A result is
> getting returned for something that does not exist in the database.
>
> I've included the method that's getting called below. Given a
> specific input, we get a valid object even though there is nothing
> close to it.
>
> We have a system with a "publications" table and a publication
> editions table. The latter is an instance of a publication
> typically used when we have a newsletter from a specific date. If
> we use today's date and a publication name we'll call A, we get a
> PublicationEditions object for publication B on a different date
> from last year. The primary key is different as is all the other
> meta data for the PublicationEditions object (a cayenne generated
> class).
>
> The only possibility I've come up with is that there is some hash
> used in the system and we happened to hit a collision. Is this a
> possibility? We've been using Cayenne's LRU algorithm, but I've
> tried switching to oscache today as well with the same results (also
> LRU).
>
> Lucas Holt
>
>
> public com.prime.pcd.db.PublicationEditions lookup(final String
> publicationName, final Date date)
> throws IllegalArgumentException, ModelException {
> com.prime.pcd.db.PublicationEditions pe = null;
>
> if (publicationName == null || publicationName.length() > 255
> || publicationName.length() < 1) {
> throw new IllegalArgumentException("Invalid publication
> name");
> }
>
> if (date == null) {
> throw new IllegalArgumentException("Invalid date");
> }
>
> try {
> Expression qualifier =
>
> Expression
> .fromString("publicationEditionsPublication.publicationName = $pn
> and publicationEditionDate=$date");
> final HashMap<String, Object> map = new HashMap<String,
> Object>(2);
> map.put("pn", publicationName);
> map.put("date", date);
> qualifier = qualifier.expWithParameters(map);
> final SelectQuery query = new
> SelectQuery(com.prime.pcd.db.PublicationEditions.class, qualifier);
>
> final List<com.prime.pcd.db.PublicationEditions> editions
> = dataContext.performQuery(query);
> logger.debug( "Number of found items is " +
> editions.size());
> if (!editions.isEmpty()) {
> pe = editions.get(0);
> logger.debug(pe.getPublicationEditionsId() + ", " +
> pe.getPublicationEditionsPublication().getPublicationName());
> }
> } catch (CayenneRuntimeException ce) {
> logger.error("Cayenne error looking up edition: " +
> ce.getMessage());
> throw new ModelException("Could not lookup edition");
> }
>
> return pe;
> }
>
>