create temporary table Lock wait timeout exceeded
I'm working with mysql 5.0.22 and I use innoDB tables. We just set up a new instance of the database we use in production, but wheras it worked in production with the same mysql configuration, the following creation of a temporary table faills with "create temporary table Lock wait timeout exceeded" after app. 50 seconds (the configured timeout):
create temporary table temporary1
select o.campaignCreativeId, o.name as campaignCreativeName, o.type as campaignCreativeType, o.measurementType, o.hasCustomPartnerCharging, o.isChargedByPartnerActions, o.clientActionPercent, o.createdOn, o.adImpressionCount as o_adImpressionCount, o.clickCount as o_clickCount, o.mainBoxCategoryId, mbc.name as mainBoxCategoryName, o.subBoxCategoryId, sbc.name as subBoxCategoryName, o.productURLId, pu.URL as productURLURL, o.campaignId, c.name as campaignName, c.state, cr.creativeId, cr.name as creativeName, cr.format, cr.URL as creativeURL, cr.type as creativeType, cr.width, cr.height, cr.category, cr.active, cr.expirationDate, cr.textContent, cr.textContentLength, cr.altText, cr.altImage, cr.adImpressionCount, cr.clickCount, cr.productId, ctg.customCategoryId, ctg.name as customCategoryName, ctg.count1 as customCategoryCount1
from CampaignCreativeEJB o
left join (CustomCategoryEJB ctg) on (o.categoryId = ctg.customCategoryId)
left join (BoxCategoryEJB mbc) on (o.mainBoxCategoryId = mbc.boxCategoryId)
left join (BoxCategoryEJB sbc) on (o.subBoxCategoryId = sbc.boxCategoryId)
left join (AOCampaignCreativeEJB aocc) on (o.campaignCreativeId = aocc.campaignCreativeId)
left join (AdvertOfferEJB adverto ) on (aocc.advertOfferId = adverto.advertOfferId)
left join (AOPartnerEJB aop) on (adverto.advertOfferId = aop.advertOfferId)
left join (PartnerEJB prtnr) on (aop.partnerId = prtnr.partnerId),
CampaignEJB c,
ProductURLEJB pu,
CampaignCreative_Creative ccc,
CreativeEJB cr,
ProductEJB p
where 1 = 1 AND o.lifecycleState != 1 AND c.productId = p.productId AND o.campaignId = c.campaignId AND o.productURLId = pu.productURLId AND o.campaignCreativeId = ccc.campaignCreativeId AND ccc.creativeId = cr.creativeId AND cr.mainCreativeId IS NULL AND cr.partnerId IS NULL AND prtnr.partnerId = 111 AND (1574 = -3 OR o.campaignId = 1574) AND UPPER(o.name) LIKE upper('%') AND (-3 = -3 OR cr.type = -3) AND (-3 = -3 OR cr.format = -3) AND (-3 = -3 OR o.categoryId = -3) AND cr.mainCreativeId IS NULL AND cr.partnerId IS NULL ORDER BY upper(o.name);
The query on its own returns within 2 seconds (only 1 row as result), a create temporary table that uses a simple select as input also works. And as I said, this statement makes no trouble in the production database, same configuration. There is also hardly anything going on in the testdatabase.
I'd really appreciate any insight as to what might be the problem.