MySQL Forums
Forum List  »  Knowledge Base

create temporary table Lock wait timeout exceeded
Posted by: Martin Stoewer
Date: August 02, 2007 03:25PM

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, 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, as mainBoxCategoryName, o.subBoxCategoryId, as subBoxCategoryName, o.productURLId, pu.URL as productURLURL, o.campaignId, as campaignName, c.state, cr.creativeId, as creativeName, cr.format, cr.URL as creativeURL, cr.type as creativeType, cr.width, cr.height, cr.category,, cr.expirationDate, cr.textContent, cr.textContentLength, cr.altText, cr.altImage, cr.adImpressionCount, cr.clickCount, cr.productId, ctg.customCategoryId, 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( 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(;

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.

Options: ReplyQuote

Written By
create temporary table Lock wait timeout exceeded
August 02, 2007 03:25PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.