wAC44zNK

1Migrating: 2017_12_15_000033_create_ea_og_sp_calculate_product_benchmark_report_stored-procedure
2
3   Illuminate\Database\QueryException 
4
5  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rows;
6
7            WHILE _rows >= 0 DO
8
9            SET @timespent = 0;
10        ' at line 105 (SQL: 
11        CREATE PROCEDURE analytics.ea_og_sp_calculate_product_benchmark_report(_todate DATE)
12        BEGIN
13
14        DECLARE _initialdate, _finaldate DATE;
15        DECLARE _rows INTEGER DEFAULT 0;
16
17        SET _finaldate = DATE_SUB(DATE_ADD(DATE_FORMAT(_todate,'%Y-%m-01'), INTERVAL 1 MONTH), INTERVAL 1 DAY);  /* GET END OF MONTH */
18
19        IF (DATE_SUB(DATE_FORMAT(_finaldate, "%Y-%m-01"), INTERVAL 12 MONTH) < '2016-06-01') THEN
20            SET _initialdate = '2016-06-01';
21        ELSE
22            SET _initialdate = DATE_SUB(DATE_FORMAT(_finaldate, "%Y-%m-01"), INTERVAL 12 MONTH);
23        END IF;
24
25        DROP TABLE IF EXISTS product_benchmark_report_temp;
26        CREATE TEMPORARY TABLE product_benchmark_report_temp (
27            company_id integer unsigned NOT NULL DEFAULT 0,
28            product_id integer unsigned NOT NULL DEFAULT 0,
29            category_id integer unsigned NOT NULL DEFAULT 0,
30            brand_id integer unsigned NOT NULL DEFAULT 0,
31            load_impression int(10) unsigned NOT NULL DEFAULT 0,
32            served_impression int(10) unsigned NOT NULL DEFAULT 0,
33            requested_impression int(10) unsigned NOT NULL DEFAULT 0,
34            click int(10) unsigned NOT NULL DEFAULT 0,
35            interaction int(10) unsigned NOT NULL DEFAULT 0,
36            engagement int(10) unsigned NOT NULL DEFAULT 0,
37            video_start int(10) unsigned NOT NULL DEFAULT 0,
38            video_finish int(10) unsigned NOT NULL DEFAULT 0,
39            units int(10) unsigned NOT NULL DEFAULT 0,
40            timespent double(12,2) unsigned NOT NULL DEFAULT 0,
41            ads text,
42            PRIMARY KEY (company_id, product_id, category_id, brand_id),
43            INDEX prod_index (product_id)
44        ) ENGINE = MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
45
46        DROP TABLE IF EXISTS morethan_1000;
47        CREATE TEMPORARY TABLE morethan_1000 (
48            ad_id bigint(20) unsigned NOT NULL DEFAULT 0,
49            company_id integer unsigned NOT NULL DEFAULT 0,
50            product_id integer unsigned NOT NULL DEFAULT 0,
51            category_id integer unsigned NOT NULL DEFAULT 0,
52            brand_id integer unsigned NOT NULL DEFAULT 0,
53            1000_impression int(10) unsigned NOT NULL DEFAULT 0,
54            PRIMARY KEY (ad_id)
55        ) ENGINE = MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
56
57        WHILE _initialdate <= _finaldate DO
58
59            SET _rows = 0;
60            TRUNCATE TABLE morethan_1000;
61            TRUNCATE TABLE product_benchmark_report_temp;
62
63            INSERT INTO morethan_1000 (ad_id, company_id, product_id, category_id, brand_id)
64            SELECT ad_id, ad.company_id, ad.product_id, ad.category_id, ad.brand_id
65            FROM adunit_metrics
66            INNER JOIN origin.ad ad ON ad.id = adunit_metrics.ad_id
67            WHERE adunit_metrics.created_at BETWEEN UNIX_TIMESTAMP(_initialdate) AND UNIX_TIMESTAMP(_finaldate)
68            GROUP BY ad_id
69            HAVING SUM(load_impression) > 1000;
70
71            INSERT INTO product_benchmark_report_temp(company_id, product_id, category_id, brand_id, load_impression, served_impression, requested_impression, click, interaction, engagement, video_start, video_finish, units, ads, timespent)
72            SELECT ad.company_id, ad.product_id, 0 AS category_id, 0 AS brand_id,
73            SUM(load_impression) AS load_impression, SUM(served_impression) AS served_impression, SUM(requested_impression) AS requested_impression,
74            SUM(click) AS click, SUM(interaction) AS interaction, SUM(engagement) AS engagement, SUM(video_start) AS video_start, SUM(video_finish) AS video_finish,
75            COUNT(DISTINCT adunit_metrics.ad_id) AS units, GROUP_CONCAT(DISTINCT adunit_metrics.ad_id ORDER BY adunit_metrics.ad_id), 0 AS timespent
76            FROM adunit_metrics
77            INNER JOIN origin.ad ad ON ad.id = adunit_metrics.ad_id
78            INNER JOIN morethan_1000 ON morethan_1000.ad_id = adunit_metrics.ad_id
79            WHERE adunit_metrics.created_at BETWEEN UNIX_TIMESTAMP(_initialdate) AND UNIX_TIMESTAMP(_finaldate)
80            GROUP BY ad.company_id, ad.product_id;
81
82            INSERT INTO product_benchmark_report_temp(company_id, product_id, category_id, brand_id, load_impression, served_impression, requested_impression, click, interaction, engagement, video_start, video_finish, units, ads, timespent)
83            SELECT ad.company_id, ad.product_id, ad.category_id, 0 AS brand_id,
84            SUM(load_impression) AS load_impression, SUM(served_impression) AS served_impression, SUM(requested_impression) AS requested_impression,
85            SUM(click) AS click, SUM(interaction) AS interaction, SUM(engagement) AS engagement, SUM(video_start) AS video_start, SUM(video_finish) AS video_finish,
86            COUNT(DISTINCT adunit_metrics.ad_id) AS units, GROUP_CONCAT(DISTINCT adunit_metrics.ad_id ORDER BY adunit_metrics.ad_id), 0 AS timespent
87            FROM adunit_metrics
88            INNER JOIN origin.ad ad ON ad.id = adunit_metrics.ad_id
89            INNER JOIN morethan_1000 ON  morethan_1000.ad_id = adunit_metrics.ad_id AND morethan_1000.category_id = ad.category_id
90            WHERE adunit_metrics.created_at BETWEEN UNIX_TIMESTAMP(_initialdate) AND UNIX_TIMESTAMP(_finaldate)
91            GROUP BY ad.company_id, ad.product_id, ad.category_id;
92
93            INSERT INTO product_benchmark_report_temp(company_id, product_id, category_id, brand_id, load_impression, served_impression, requested_impression, click, interaction, engagement, video_start, video_finish, units, ads, timespent)
94            SELECT ad.company_id, ad.product_id, 0 AS category_id, ad.brand_id,
95            SUM(load_impression) AS load_impression, SUM(served_impression) AS served_impression, SUM(requested_impression) AS requested_impression,
96            SUM(click) AS click, SUM(interaction) AS interaction, SUM(engagement) AS engagement, SUM(video_start) AS video_start, SUM(video_finish) AS video_finish,
97            COUNT(DISTINCT adunit_metrics.ad_id) AS units, GROUP_CONCAT(DISTINCT adunit_metrics.ad_id ORDER BY adunit_metrics.ad_id), 0 AS timespent
98            FROM adunit_metrics
99            INNER JOIN origin.ad ad ON ad.id = adunit_metrics.ad_id
100            INNER JOIN morethan_1000 ON morethan_1000.ad_id = adunit_metrics.ad_id AND morethan_1000.brand_id = ad.brand_id
101            WHERE adunit_metrics.created_at BETWEEN UNIX_TIMESTAMP(_initialdate) AND UNIX_TIMESTAMP(_finaldate)
102            GROUP BY ad.company_id, ad.product_id, ad.brand_id;
103
104            INSERT INTO product_benchmark_report_temp(company_id, product_id, category_id, brand_id, load_impression, served_impression, requested_impression, click, interaction, engagement, video_start, video_finish, units, ads, timespent)
105            SELECT ad.company_id, ad.product_id, ad.category_id, ad.brand_id,
106            SUM(load_impression) AS load_impression, SUM(served_impression) AS served_impression, SUM(requested_impression) AS requested_impression,
107            SUM(click) AS click, SUM(interaction) AS interaction, SUM(engagement) AS engagement, SUM(video_start) AS video_start, SUM(video_finish) AS video_finish,
108            COUNT(DISTINCT adunit_metrics.ad_id) AS units, GROUP_CONCAT(DISTINCT adunit_metrics.ad_id ORDER BY adunit_metrics.ad_id), 0 AS timespent
109            FROM adunit_metrics
110            INNER JOIN origin.ad ad ON ad.id = adunit_metrics.ad_id
111            INNER JOIN morethan_1000 ON  morethan_1000.ad_id = adunit_metrics.ad_id AND morethan_1000.category_id = ad.category_id AND morethan_1000.brand_id = ad.brand_id
112            WHERE adunit_metrics.created_at BETWEEN UNIX_TIMESTAMP(_initialdate) AND UNIX_TIMESTAMP(_finaldate)
113            GROUP BY ad.company_id, ad.product_id, ad.category_id, ad.brand_id;
114
115            SELECT COUNT(0) - 1 INTO _rows FROM (SELECT product_id, brand_id, category_id, ads FROM product_benchmark_report_temp GROUP BY product_id, brand_id, category_id) AS rows;
116
117            WHILE _rows >= 0 DO
118
119            SET @timespent = 0;
120            SET @Query =  CONCAT("SELECT product_id, brand_id, category_id, ads INTO @product, @brand, @category, @ads FROM product_benchmark_report_temp GROUP BY product_id, brand_id, category_id LIMIT 1 OFFSET ", _rows);
121            PREPARE stmt FROM @Query;
122            EXECUTE stmt;
123            DEALLOCATE PREPARE stmt;
124
125            CALL ea_og_sp_select_product_median_timespent(@ads, UNIX_TIMESTAMP(_initialdate), UNIX_TIMESTAMP(_finaldate), @timespent);
126
127            IF @timespent IS NOT NULL THEN
128                UPDATE product_benchmark_report_temp SET timespent = @timespent WHERE product_id = @product AND brand_id = @brand AND category_id = @category;
129            END IF;
130
131            SET _rows = _rows - 1;
132
133            END WHILE;
134
135            INSERT INTO product_benchmark_report(initialdate, finaldate, company_id, product_id, category_id, brand_id, load_impression, served_impression, requested_impression, click, interaction, engagement, video_start, video_finish, units, ads, timespent)
136            SELECT _initialdate, _finaldate, company_id, product_id, category_id, brand_id, load_impression, served_impression, requested_impression, click, interaction, engagement, video_start, video_finish, units, ads, timespent
137            FROM product_benchmark_report_temp
138            ON DUPLICATE KEY UPDATE
139            load_impression = VALUES(load_impression),
140            served_impression = VALUES(served_impression),
141            requested_impression = VALUES(requested_impression),
142            click = VALUES(click),
143            interaction = VALUES(interaction),
144            engagement = VALUES(engagement),
145            video_start = VALUES(video_start),
146            video_finish = VALUES(video_finish),
147            units = VALUES(units),
148            timespent = VALUES(timespent),
149            ads = VALUES(ads);
150
151            SET _initialdate = DATE_ADD(_initialdate, INTERVAL 1 MONTH);
152
153        END WHILE;
154
155        DROP TABLE IF EXISTS morethan_1000;
156        DROP TABLE IF EXISTS product_benchmark_report_temp;
157
158        SELECT DISTINCT initialdate, finaldate FROM product_benchmark_report WHERE finaldate = DATE_SUB(DATE_ADD(CONCAT(YEAR(_todate), '-', MONTH(_todate), '-01'), INTERVAL 1 MONTH), INTERVAL 1 DAY);
159
160        END
161        )
162
163  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:678
164    674▕         // If an exception occurs when attempting to run a query, we'll format the error
165    675▕         // message to include the bindings with SQL, which will make this exception a
166    676▕         // lot more helpful to the developer instead of just the database's errors.
167    677▕         catch (Exception $e) {
168  ➜ 678▕             throw new QueryException(
169    679▕                 $query, $this->prepareBindings($bindings), $e
170    680▕             );
171    681▕         }
172    682▕
173
174      +5 vendor frames 
175  6   database/migrations/2017_12_15_000033_create_ea_og_sp_calculate_product_benchmark_report_stored-procedure.php:29
176      Illuminate\Database\Connection::unprepared("
177        CREATE PROCEDURE analytics.ea_og_sp_calculate_product_benchmark_report(_todate DATE)
178        BEGIN
179
180        DECLARE _initialdate, _finaldate DATE;
181        DECLARE _rows INTEGER DEFAULT 0;
182
183        SET _finaldate = DATE_SUB(DATE_ADD(DATE_FORMAT(_todate,'%Y-%m-01'), INTERVAL 1 MONTH), INTERVAL 1 DAY);  /* GET END OF MONTH */
184
185        IF (DATE_SUB(DATE_FORMAT(_finaldate, "%Y-%m-01"), INTERVAL 12 MONTH) < '2016-06-01') THEN
186            SET _initialdate = '2016-06-01';
187        ELSE
188            SET _initialdate = DATE_SUB(DATE_FORMAT(_finaldate, "%Y-%m-01"), INTERVAL 12 MONTH);
189        END IF;
190
191        DROP TABLE IF EXISTS product_benchmark_report_temp;
192        CREATE TEMPORARY TABLE product_benchmark_report_temp (
193            company_id integer unsigned NOT NULL DEFAULT 0,
194            product_id integer unsigned NOT NULL DEFAULT 0,
195            category_id integer unsigned NOT NULL DEFAULT 0,
196            brand_id integer unsigned NOT NULL DEFAULT 0,
197            load_impression int(1...")
198
199      +21 vendor frames 
200  28  artisan:37
201      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))