[MDEV-2680] LP:923919 - error 1928 with 5.3.3 RC - or eats memory - JIRA

Details

Type: Bug
Status: Closed
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- Launchpad

Description

This is the same result as this KB report - error 1928 with 5.3.3 RC
http://kb.askmonty.org/en/error-1928-with-533-rc

Didnt see any more details or this bug being reported in bugs db so adding this with the info requested of original poster:

------1) Query that caused this problem:
explain
SELECT t1.language_actual, t1.CampaignType, t1.ad_group_name,t1.ad_name, t2.Impressions, t2.Clicks, t2.Cost, t1.Trials,(t1.Trials * 100)/t2.Clicks as 'Clicks/Trials %', t1.Trialsconfirmed, t1.Buyers,(t1.Buyers * 100)/t1.Trials as 'Trials/Buyers %', t2.Cost/t1.Buyers as '$Cost/Buyer', t1.sales_1_month,t1.sales_2_month,t1.sales_3_month, t1.Sales
FROM
(
SELECT
mp.language_id,
dl.language_actual,
t.CampaignType,
adg.ad_group_name,
adg.ad_name,
COUNT(DISTINCT t.member_id) as Trials,
COUNT(DISTINCT if(t.completed=1, t.member_id,null)) as Trialsconfirmed,
COUNT(DISTINCT if(mp.completed=1 and mp.amount>0, mp.member_id,null)) as Buyers,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 33 DAY),mp.amount, null)) AS sales_1_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 66 DAY),mp.amount, null)) AS sales_2_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 99 DAY),mp.amount, null)) AS sales_3_month,
SUM(IF(mp.completed=1 AND mp.amount >0,mp.amount,0)) as Sales
FROM
alldw.etl_all_mem_payments mp
INNER JOIN
(
SELECT member_id,
min(tm_added) as tm_added,
max(completed) as completed,
max(if(data like '%src=gc_Learn%Content%' or language_id=1, 'Content','Search')) as CampaignType,
if((LOCATE('_', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6) - (LOCATE('dilcg&',REVERSE(data)) + 6)) =0,
REVERSE(SUBSTRING(REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7,LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7) - (LOCATE('dilcg&',REVERSE(data)) + 7))),
REVERSE(SUBSTRING(REVERSE(data),LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 ) +1 , LOCATE('',REVERSE(data), LOCATE('',REVERSE(data), LOCATE('dilcg&',REVERSE(data))) + 1) - (LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 )) -1 ))) as adid
from alldw.etl_all_mem_payments where
Date(tm_added) BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
and product_id=1
and data like '%gc_Learn%'
group by member_id
) t
ON mp.member_id = t.member_id INNER JOIN alldw.etl_all_dim_languages dl ON mp.language_id = dl.language_id
LEFT JOIN
(SELECT ad_id,
MAX(ad_group_name ) as ad_group_name,
MAX(ad_name) as ad_name
FROM alldw.ad_daily
GROUP BY ad_id) adg
ON adg.ad_id = t.adid
GROUP BY mp.language_id, dl.language_actual, t.CampaignType, adg.ad_group_name, adg.ad_name
) t1
LEFT JOIN
(
SELECT ad.language_id,
dl.language_actual,
if(adc.campaign_type = 'Search', 'Search', 'Content') AS CampaignType,
ad.ad_group_name,
ad.ad_name,
SUM(ad.impressions) AS Impressions,
SUM(ad.clicks) AS Clicks,
SUM(ad.cost) AS Cost
FROM alldw.ad_daily ad
INNER JOIN alldw.etl_all_dim_languages dl ON ad.language_id = dl.language_id
INNER JOIN alldw.adwords_dictionary_campaigns adc ON adc.campaign_name = ad.campaign_name
WHERE ad.day BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
GROUP BY ad.language_id, CampaignType,ad.ad_group_name, ad.ad_name
) t2
ON t1.language_id = t2.language_id AND t1.CampaignType=t2.CampaignType AND t1.ad_group_name = t2.ad_group_name AND t1.ad_name = t2.ad_name

UNION

SELECT t2.language_actual, t2.CampaignType, t2.ad_group_name, t2.ad_name, t2.Impressions, t2.Clicks, t2.Cost, t1.Trials,(t1.Trials * 100)/t2.Clicks as 'Clicks/Trials %', t1.Trialsconfirmed, t1.Buyers,(t1.Buyers * 100)/t1.Trials as 'Trials/Buyers %',t2.Cost/t1.Buyers as '$Cost/Buyer',t1.sales_1_month,t1.sales_2_month,t1.sales_3_month, t1.Sales
FROM
(
SELECT
mp.language_id,
dl.language_actual,
t.CampaignType,
adg.ad_group_name,
adg.ad_name,
COUNT(DISTINCT t.member_id) as Trials,
COUNT(DISTINCT if(t.completed=1, t.member_id,null)) as Trialsconfirmed,
COUNT(DISTINCT if(mp.completed=1 and mp.amount>0, mp.member_id,null)) as Buyers,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 33 DAY),mp.amount, null)) AS sales_1_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 66 DAY),mp.amount, null)) AS sales_2_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 99 DAY),mp.amount, null)) AS sales_3_month,
SUM(if(mp.completed=1 and mp.amount >0,mp.amount,0)) as Sales
FROM
alldw.etl_all_mem_payments mp
INNER JOIN
(
SELECT member_id,
min(tm_added) as tm_added,
max(completed) as completed,
max(if(data like '%src=gc_Learn%Content%' or language_id=1, 'Content','Search')) as CampaignType,
if((LOCATE('_', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6) - (LOCATE('dilcg&',REVERSE(data)) + 6)) =0,
REVERSE(SUBSTRING(REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7,LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7) - (LOCATE('dilcg&',REVERSE(data)) + 7))),
REVERSE(SUBSTRING(REVERSE(data),LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 ) +1 , LOCATE('',REVERSE(data), LOCATE('',REVERSE(data), LOCATE('dilcg&',REVERSE(data))) + 1) - (LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 )) -1 ))) as adid
from alldw.etl_all_mem_payments where
Date(tm_added) BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
and product_id=1
and data like '%gc_Learn%'
group by member_id
) t
ON mp.member_id = t.member_id INNER JOIN alldw.etl_all_dim_languages dl ON mp.language_id = dl.language_id
LEFT JOIN
(SELECT ad_id,
MAX(ad_group_name ) as ad_group_name,
MAX(ad_name) as ad_name
FROM alldw.ad_daily
GROUP BY ad_id) adg
ON adg.ad_id = t.adid
GROUP BY mp.language_id, dl.language_actual, t.CampaignType, adg.ad_group_name, adg.ad_name
) t1
Right JOIN
(
SELECT ad.language_id,
dl.language_actual,
if(adc.campaign_type = 'Search', 'Search', 'Content') AS CampaignType,
ad.ad_group_name,
ad.ad_name,
SUM(ad.impressions) AS Impressions,
SUM(ad.clicks) AS Clicks,
SUM(ad.cost) AS Cost
FROM alldw.ad_daily ad
INNER JOIN alldw.etl_all_dim_languages dl ON ad.language_id = dl.language_id
INNER JOIN alldw.adwords_dictionary_campaigns adc ON adc.campaign_name = ad.campaign_name
WHERE ad.day BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
GROUP BY ad.language_id, CampaignType,ad.ad_group_name, ad.ad_name
) t2
ON t1.language_id = t2.language_id and t1.CampaignType=t2.CampaignType AND t1.ad_group_name = t2.ad_group_name AND t1.ad_name = t2.ad_name
order by language_actual, CampaignType, ad_group_name, ad_name

------ 2) Explain output:

- - - - 1. row ***************************
        id: 1
        select_type: PRIMARY
        table: <derived2>
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 16490420
        filtered: 100.00
        Extra:
        
        2. row ***************************
        id: 1
        select_type: PRIMARY
        table: <derived5>
        type: ref
        possible_keys: key0
        key: key0
        key_len: 530
        ref: t1.language_id,t1.CampaignType,t1.ad_group_name,t1.ad_name
        rows: 10
        filtered: 100.00
        Extra: Using where
        
        3. row ***************************
        id: 5
        select_type: DERIVED
        table: adc
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 34
        filtered: 100.00
        Extra: Using temporary; Using filesort
        
        4. row ***************************
        id: 5
        select_type: DERIVED
        table: ad
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 59981
        filtered: 100.00
        Extra: Using where; Using join buffer (flat, BNL join)
        
        5. row ***************************
        id: 5
        select_type: DERIVED
        table: dl
        type: eq_ref
        possible_keys: PRIMARY
        key: PRIMARY
        key_len: 1
        ref: alldw.ad.language_id
        rows: 1
        filtered: 100.00
        Extra: Using index condition
        
        6. row ***************************
        id: 2
        select_type: DERIVED
        table: <derived3>
        type: ALL
        possible_keys: key0
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 1649042
        filtered: 100.00
        Extra: Using temporary; Using filesort
        
        7. row ***************************
        id: 2
        select_type: DERIVED
        table: mp
        type: ref
        possible_keys: language_id,language_id_2
        key: language_id
        key_len: 4
        ref: t.member_id
        rows: 1
        filtered: 100.00
        Extra:
        
        8. row ***************************
        id: 2
        select_type: DERIVED
        table: <derived4>
        type: ref
        possible_keys: key0
        key: key0
        key_len: 8
        ref: t.adid
        rows: 10
        filtered: 100.00
        Extra: Using where
        
        9. row ***************************
        id: 2
        select_type: DERIVED
        table: dl
        type: eq_ref
        possible_keys: PRIMARY
        key: PRIMARY
        key_len: 1
        ref: alldw.mp.language_id
        rows: 1
        filtered: 100.00
        Extra: Using index condition
        
        10. row ***************************
        id: 4
        select_type: DERIVED
        table: ad_daily
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 59981
        filtered: 100.00
        Extra: Using temporary; Using filesort
        
        11. row ***************************
        id: 3
        select_type: DERIVED
        table: etl_all_mem_payments
        type: ref
        possible_keys: language_id_3
        key: language_id_3
        key_len: 4
        ref: const
        rows: 1649042
        filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
        
        12. row ***************************
        id: 6
        select_type: UNION
        table: <derived10>
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 2039354
        filtered: 100.00
        Extra:
        
        13. row ***************************
        id: 6
        select_type: UNION
        table: <derived7>
        type: ref
        possible_keys: key0
        key: key0
        key_len: 533
        ref: t2.language_id,t2.CampaignType,t2.ad_group_name,t2.ad_name
        rows: 10
        filtered: 100.00
        Extra:
        
        14. row ***************************
        id: 10
        select_type: DERIVED
        table: adc
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 34
        filtered: 100.00
        Extra: Using temporary; Using filesort
        
        15. row ***************************
        id: 10
        select_type: DERIVED
        table: ad
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 59981
        filtered: 100.00
        Extra: Using where; Using join buffer (flat, BNL join)
        
        16. row ***************************
        id: 10
        select_type: DERIVED
        table: dl
        type: eq_ref
        possible_keys: PRIMARY
        key: PRIMARY
        key_len: 1
        ref: alldw.ad.language_id
        rows: 1
        filtered: 100.00
        Extra: Using index condition
        
        17. row ***************************
        id: 7
        select_type: DERIVED
        table: <derived8>
        type: ALL
        possible_keys: key0
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 1649042
        filtered: 100.00
        Extra: Using temporary; Using filesort
        
        18. row ***************************
        id: 7
        select_type: DERIVED
        table: mp
        type: ref
        possible_keys: language_id,language_id_2
        key: language_id
        key_len: 4
        ref: t.member_id
        rows: 1
        filtered: 100.00
        Extra:
        
        19. row ***************************
        id: 7
        select_type: DERIVED
        table: <derived9>
        type: ref
        possible_keys: key0
        key: key0
        key_len: 8
        ref: t.adid
        rows: 10
        filtered: 100.00
        Extra: Using where
        
        20. row ***************************
        id: 7
        select_type: DERIVED
        table: dl
        type: eq_ref
        possible_keys: PRIMARY
        key: PRIMARY
        key_len: 1
        ref: alldw.mp.language_id
        rows: 1
        filtered: 100.00
        Extra: Using index condition
        
        21. row ***************************
        id: 9
        select_type: DERIVED
        table: ad_daily
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 59981
        filtered: 100.00
        Extra: Using temporary; Using filesort
        
        22. row ***************************
        id: 8
        select_type: DERIVED
        table: etl_all_mem_payments
        type: ref
        possible_keys: language_id_3
        key: language_id_3
        key_len: 4
        ref: const
        rows: 1649042
        filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
        
        23. row ***************************
        id: NULL
        select_type: UNION RESULT
        table: <union1,6>
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: NULL
        filtered: NULL
        Extra: Using filesort
        
        1. row ***************************
        Level: Note
        Code: 1003
        Message: select `t1`.`language_actual` AS `language_actual`,`t1`.`CampaignType` AS `CampaignType`,`t1`.`ad_group_name` AS `ad_group_name`,`t1`.`ad_name` AS `ad_name`,`t2`.`Impressions` AS `Impressions`,`t2`.`Clicks` AS `Clicks`,`t2`.`Cost` AS `Cost`,`t1`.`Trials` AS `Trials`,((`t1`.`Trials` * 100) / `t2`.`Clicks`) AS `Clicks/Trials %`,`t1`.`Trialsconfirmed` AS `Trialsconfirmed`,`t1`.`Buyers` AS `Buyers`,((`t1`.`Buyers` * 100) / `t1`.`Trials`) AS `Trials/Buyers %`,(`t2`.`Cost` / `t1`.`Buyers`) AS `$Cost/Buyer`,`t1`.`sales_1_month` AS `sales_1_month`,`t1`.`sales_2_month` AS `sales_2_month`,`t1`.`sales_3_month` AS `sales_3_month`,`t1`.`Sales` AS `Sales` from (select `alldw`.`mp`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,`t`.`CampaignType` AS `CampaignType`,`adg`.`ad_group_name` AS `ad_group_name`,`adg`.`ad_name` AS `ad_name`,count(distinct `t`.`member_id`) AS `Trials`,count(distinct if((`t`.`completed` = 1),`t`.`member_id`,NULL)) AS `Trialsconfirmed`,count(distinct if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`member_id`,NULL)) AS `Buyers`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 33 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_1_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 66 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_2_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 99 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_3_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`amount`,0)) AS `Sales` from `alldw`.`etl_all_mem_payments` `mp` join (select `alldw`.`etl_all_mem_payments`.`member_id` AS `member_id`,min(`alldw`.`etl_all_mem_payments`.`tm_added`) AS `tm_added`,max(`alldw`.`etl_all_mem_payments`.`completed`) AS `completed`,max(if(((`alldw`.`etl_all_mem_payments`.`data` like '%src=gc_Learn%Content%') or (`alldw`.`etl_all_mem_payments`.`language_id` = 1)),'Content','Search')) AS `CampaignType`,if(((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) = 0),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)))),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) + 1),((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`))) + 1)) - locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6))) - 1)))) AS `adid` from `alldw`.`etl_all_mem_payments` where ((`alldw`.`etl_all_mem_payments`.`product_id` = 1) and (cast(`alldw`.`etl_all_mem_payments`.`tm_added` as date) between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`etl_all_mem_payments`.`data` like '%gc_Learn%')) group by `alldw`.`etl_all_mem_payments`.`member_id`) `t` join `alldw`.`etl_all_dim_languages` `dl` left join (select `alldw`.`ad_daily`.`ad_id` AS `ad_id`,max(`alldw`.`ad_daily`.`ad_group_name`) AS `ad_group_name`,max(`alldw`.`ad_daily`.`ad_name`) AS `ad_name` from `alldw`.`ad_daily` group by `alldw`.`ad_daily`.`ad_id`) `adg` on(((`adg`.`ad_id` = `t`.`adid`) and (`t`.`adid` is not null))) where ((`alldw`.`mp`.`member_id` = `t`.`member_id`) and (`alldw`.`mp`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`mp`.`language_id`,`t`.`CampaignType`,`adg`.`ad_group_name`,`adg`.`ad_name`) `t1` left join (select `alldw`.`ad`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content') AS `CampaignType`,`alldw`.`ad`.`ad_group_name` AS `ad_group_name`,`alldw`.`ad`.`ad_name` AS `ad_name`,sum(`alldw`.`ad`.`impressions`) AS `Impressions`,sum(`alldw`.`ad`.`clicks`) AS `Clicks`,sum(`alldw`.`ad`.`cost`) AS `Cost` from `alldw`.`ad_daily` `ad` join `alldw`.`etl_all_dim_languages` `dl` join `alldw`.`adwords_dictionary_campaigns` `adc` where ((`alldw`.`ad`.`day` between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`adc`.`campaign_name` = convert(`alldw`.`ad`.`campaign_name` using utf8)) and (`alldw`.`ad`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`ad`.`language_id`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content'),`alldw`.`ad`.`ad_group_name`,`alldw`.`ad`.`ad_name`) `t2` on(((`t2`.`language_id` = `t1`.`language_id`) and (`t2`.`CampaignType` = `t1`.`CampaignType`) and (`t2`.`ad_group_name` = `t1`.`ad_group_name`) and (`t2`.`ad_name` = `t1`.`ad_name`) and (`t1`.`CampaignType` is not null) and (`t1`.`ad_group_name` is not null) and (`t1`.`ad_name` is not null))) where 1 union select `t2`.`language_actual` AS `language_actual`,`t2`.`CampaignType` AS `CampaignType`,`t2`.`ad_group_name` AS `ad_group_name`,`t2`.`ad_name` AS `ad_name`,`t2`.`Impressions` AS `Impressions`,`t2`.`Clicks` AS `Clicks`,`t2`.`Cost` AS `Cost`,`t1`.`Trials` AS `Trials`,((`t1`.`Trials` * 100) / `t2`.`Clicks`) AS `Clicks/Trials %`,`t1`.`Trialsconfirmed` AS `Trialsconfirmed`,`t1`.`Buyers` AS `Buyers`,((`t1`.`Buyers` * 100) / `t1`.`Trials`) AS `Trials/Buyers %`,(`t2`.`Cost` / `t1`.`Buyers`) AS `$Cost/Buyer`,`t1`.`sales_1_month` AS `sales_1_month`,`t1`.`sales_2_month` AS `sales_2_month`,`t1`.`sales_3_month` AS `sales_3_month`,`t1`.`Sales` AS `Sales` from (select `alldw`.`ad`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content') AS `CampaignType`,`alldw`.`ad`.`ad_group_name` AS `ad_group_name`,`alldw`.`ad`.`ad_name` AS `ad_name`,sum(`alldw`.`ad`.`impressions`) AS `Impressions`,sum(`alldw`.`ad`.`clicks`) AS `Clicks`,sum(`alldw`.`ad`.`cost`) AS `Cost` from `alldw`.`ad_daily` `ad` join `alldw`.`etl_all_dim_languages` `dl` join `alldw`.`adwords_dictionary_campaigns` `adc` where ((`alldw`.`ad`.`day` between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`adc`.`campaign_name` = convert(`alldw`.`ad`.`campaign_name` using utf8)) and (`alldw`.`ad`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`ad`.`language_id`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content'),`alldw`.`ad`.`ad_group_name`,`alldw`.`ad`.`ad_name`) `t2` left join (select `alldw`.`mp`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,`t`.`CampaignType` AS `CampaignType`,`adg`.`ad_group_name` AS `ad_group_name`,`adg`.`ad_name` AS `ad_name`,count(distinct `t`.`member_id`) AS `Trials`,count(distinct if((`t`.`completed` = 1),`t`.`member_id`,NULL)) AS `Trialsconfirmed`,count(distinct if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`member_id`,NULL)) AS `Buyers`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 33 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_1_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 66 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_2_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 99 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_3_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`amount`,0)) AS `Sales` from `alldw`.`etl_all_mem_payments` `mp` join (select `alldw`.`etl_all_mem_payments`.`member_id` AS `member_id`,min(`alldw`.`etl_all_mem_payments`.`tm_added`) AS `tm_added`,max(`alldw`.`etl_all_mem_payments`.`completed`) AS `completed`,max(if(((`alldw`.`etl_all_mem_payments`.`data` like '%src=gc_Learn%_Content%') or (`alldw`.`etl_all_mem_payments`.`language_id` = 1)),'Content','Search')) AS `CampaignType`,if(((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) = 0),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)))),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) + 1),((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`))) + 1)) - locate('_',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6))) - 1)))) AS `adid` from `alldw`.`etl_all_mem_payments` where ((`alldw`.`etl_all_mem_payments`.`product_id` = 1) and (cast(`alldw`.`etl_all_mem_payments`.`tm_added` as date) between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`etl_all_mem_payments`.`data` like '%gc_Learn%')) group by `alldw`.`etl_all_mem_payments`.`member_id`) `t` join `alldw`.`etl_all_dim_languages` `dl` left join (select `alldw`.`ad_daily`.`ad_id` AS `ad_id`,max(`alldw`.`ad_daily`.`ad_group_name`) AS `ad_group_name`,max(`alldw`.`ad_daily`.`ad_name`) AS `ad_name` from `alldw`.`ad_daily` group by `alldw`.`ad_daily`.`ad_id`) `adg` on(((`adg`.`ad_id` = `t`.`adid`) and (`t`.`adid` is not null))) where ((`alldw`.`mp`.`member_id` = `t`.`member_id`) and (`alldw`.`mp`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`mp`.`language_id`,`t`.`CampaignType`,`adg`.`ad_group_name`,`adg`.`ad_name`) `t1` on(((`t1`.`language_id` = `t2`.`language_id`) and (`t1`.`CampaignType` = `t2`.`CampaignType`) and (`t1`.`ad_group_name` = `t2`.`ad_group_name`) and (`t1`.`ad_name` = `t2`.`ad_name`))) where 1 order by `language_actual`,`CampaignType`,`ad_group_name`,`ad_name`

------- 3) DDL of tables:

USE alldw;
CREATE TABLE IF NOT EXISTS etl_all_dim_languages(
language_id TINYINT(4) UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
database_name VARCHAR(255) DEFAULT NULL,
language_actual VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (language_id)
) ENGINE = MYISAM AVG_ROW_LENGTH = 48 CHARACTER SET latin1 COLLATE latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS ad_daily(
id BIGINT(20) NOT NULL AUTO_INCREMENT,
ad_id BIGINT(20) NOT NULL,
ad_name VARCHAR(255) NOT NULL,
campaign_name VARCHAR(255) NOT NULL,
ad_type VARCHAR(255) NOT NULL,
ad_group_name VARCHAR(255) NOT NULL,
description1 VARCHAR(255) NOT NULL,
description2 VARCHAR(255) NOT NULL,
displayUrl VARCHAR(255) NOT NULL,
status VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
impressions INT(11) NOT NULL,
clicks INT(11) NOT NULL,
cost FLOAT NOT NULL,
day DATE NOT NULL,
language_id INT(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE = MYISAM AUTO_INCREMENT = 60349 AVG_ROW_LENGTH = 217 CHARACTER SET latin1 COLLATE latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS adwords_dictionary_campaigns(
id INT(11) NOT NULL AUTO_INCREMENT,
campaign_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
campaign_type VARCHAR(255) NOT NULL,
language_id INT(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE = MYISAM AUTO_INCREMENT = 36 AVG_ROW_LENGTH = 43 CHARACTER SET latin1 COLLATE latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS etl_all_mem_payments(
payment_id BIGINT(20) NOT NULL,
language_id INT(11) NOT NULL,
member_id INT(11) NOT NULL DEFAULT 0,
payment_id_orig INT(11) NOT NULL,
member_id_orig INT(11) NOT NULL,
product_id INT(11) NOT NULL DEFAULT 0,
begin_date DATE NOT NULL DEFAULT '0000-00-00',
expire_date DATE NOT NULL DEFAULT '0000-00-00',
paysys_id VARCHAR(32) NOT NULL DEFAULT '',
receipt_id VARCHAR(32) NOT NULL DEFAULT '',
amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
completed SMALLINT(6) DEFAULT 0,
remote_addr VARCHAR(15) NOT NULL DEFAULT '',
`data` TEXT DEFAULT NULL,
`time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
aff_id INT(11) NOT NULL DEFAULT 0,
payer_id VARCHAR(255) NOT NULL DEFAULT '',
coupon_id INT(11) NOT NULL DEFAULT 0,
tm_added DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
tm_completed DATETIME DEFAULT NULL,
tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
refund_reason VARCHAR(50) DEFAULT NULL,
refund_amount DECIMAL(12, 2) DEFAULT 0.00,
refund_date DATETIME DEFAULT '0000-00-00 00:00:00',
data_json LONGTEXT NOT NULL,
cancelled_at DATETIME NOT NULL,
PRIMARY KEY (payment_id),
INDEX language_id (member_id, tm_added),
INDEX language_id_2 (member_id, amount),
INDEX language_id_3 (product_id),
INDEX language_id_4 (coupon_id),
INDEX tm_added (tm_added)
) ENGINE = MYISAM AVG_ROW_LENGTH = 848 CHARACTER SET latin1 COLLATE latin1_swedish_ci;

4) Dump of Tables: The main transaction table is LARGE at ~2million rows. If needed I can provide a sample set of data.

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

LPexportBug923919.xml
31 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2012-03-15 00:02

Re: error 1928 with 5.3.3 RC - or eats memory
Hi,

If the problem is still reproducible on the current version 5.3.5, could you please upload the data to ftp://ftp.askmonty.org? You can use the 'private' section for sensitive data.

Thank you.

Show

Elena Stepanova added a comment - 2012-03-15 00:02 Re: error 1928 with 5.3.3 RC - or eats memory Hi, If the problem is still reproducible on the current version 5.3.5, could you please upload the data to ftp://ftp.askmonty.org? You can use the 'private' section for sensitive data. Thank you.

Hide

Permalink

Elena Stepanova added a comment - 2012-03-18 00:54

Re: error 1928 with 5.3.3 RC - or eats memory
A similar problem with a simpler test case was described in bug #957409 which, in turn, is another manifestation of bug #953649. A fix for the latter was committed in revno 3459, so transitively I am switching this one to 'Fix committed', too.

Show

Elena Stepanova added a comment - 2012-03-18 00:54 Re: error 1928 with 5.3.3 RC - or eats memory A similar problem with a simpler test case was described in bug #957409 which, in turn, is another manifestation of bug #953649. A fix for the latter was committed in revno 3459, so transitively I am switching this one to 'Fix committed', too.

Hide

Permalink

Elena Stepanova added a comment - 2012-04-27 19:47

Re: error 1928 with 5.3.3 RC - or eats memory
Fix for the other bugs released in 5.3.6

Show

Elena Stepanova added a comment - 2012-04-27 19:47 Re: error 1928 with 5.3.3 RC - or eats memory Fix for the other bugs released in 5.3.6

Hide

Permalink

Rasmus Johansson added a comment - 2012-04-27 19:47

Launchpad bug id: 923919

Show

Rasmus Johansson added a comment - 2012-04-27 19:47 Launchpad bug id: 923919

People

Assignee:

Unassigned

Reporter:

Darren P

Votes:

0 Vote for this issue

Watchers:

0 Start watching this issue

Dates

Created:

2012-01-30 19:40

Updated:

2012-10-04 13:22

Resolved:

2012-10-04 12:54

Agile

View on Board