is it normal ? count column behavior on incremental view

24 views
Skip to first unread message

mdu

unread,
Sep 2, 2012, 5:51:09 AM9/2/12
to flexview...@googlegroups.com
I have a strange behavior in a INCREMENTAL material view on a query which join 2 tables and group by column and count(*)

for example

Articles liked by people
I have a ARTICLE table join to PEOPLE_LIKE_ARTICLE table

My goal is to have a mat view with article id and number of likes so i have created it (and add tables in changelog tables of course)

each time a people like an article (for example article_id=5), the count is not updated for the line where article_id = 5 but instead of it, i have a new line with a count column equal to '1' (if people like it) or equal -1 (if people unlike it)

I dont understand the logic behind it or if it a bug

Justin Swanhart

unread,
Sep 3, 2012, 8:07:34 PM9/3/12
to flexview...@googlegroups.com
Hi,

It is a bug in the version of Flexviews that you are using.

There is a simple workaround:
a) disable the view
b) enable the view
c) before refreshing the view use ALTER TABLE to add a UNIQUE KEY on
all of the GROUP BY columns.
For example, if article_id is the only group by column then:
ALTER TABLE mview add UNIQUE(article_id);
d) refresh the view

The UNIQUE KEY is necessary to refresh the view properly, but there is
a regression bug in version 7 which prevents the unique key from being
added.

--Justin

mdu

unread,
Sep 4, 2012, 4:55:44 AM9/4/12
to flexview...@googlegroups.com
Thanks but it doesn't work... even with this workaround (and i do a very simple case, the minimal case in fact : an id + an count)
For information, i use the beta version 1.8 available on google code

But i think i will use another tools or method because i need reliability and it doesn't seem to be the case ; unfortunately, you are alone on this (smart) project and i dont think you have enough time to pass on it (few updates, few follow up)

Cheers

Justin Swanhart

unread,
Sep 5, 2012, 1:02:41 PM9/5/12
to flexview...@googlegroups.com
Hi,

The bug is still in the beta 8 version tarball. If you use the SVN
version it is fixed (SVN trunk will soon be version 8, beta 2)

Did you actually try adding the key manually after building the view?
Flexviews uses INSERT .. ON DUPLICATE KEY to update the materialized
view. If there is no UNIQUE KEY on the group by attributes, when the
delta is inserted into the table it will never find a duplicate key to
update and will instead insert the delta into the table.

--Justin

Michel Dupo

unread,
Sep 7, 2012, 3:08:09 AM9/7/12
to flexview...@googlegroups.com
thanks for trick but now flexdcd fails with this error (i restart from a clean configuration but always the same error)

SQL_ERROR IN STATEMENT:
BINLOG '
UmNIUBMBAAAAaAAAAAAAAAAAADQAAAAAAAEACWZsZXh2aWV3cwAPc2tfZXhwbG9yZV9zcG90AB4D
CAMIAw8DAwMDAw8PDwMDAwz8AQMM/PwPAwMBAQMNLAFYAlgCLAE0NDTuAgA49Sc=
UmNIUBcBAAAALQEAAEcCAAAAADQAAAAAAAAAHv///z8AAAEA/////wwAAAAAAAAAAQAAAAkAAAAA
AAAAEwAAAEZMRVhJQkxFIExPVkUgLSBmbGV4aWJsZSBsb3ZlIwAAAAMAAAABAAAAAwAAAAQAAABo
dHRwOi8vd3d3Lm15ZmFiMi5jb20vaHR0cDovL215ZGV2LnNwb290bmlrLmNvbTo4MDAwL21lZGlh
L3Nwb3QvdG1wLzEzY2VkYTZiLWM3OGItMTFlMS05MjJkLTAwMjMzMmI4MjNhYy5qcGVnAgAAAONz
MAAeZ4q3TBIAAAEBAAAAWmeKt0wSAAAyZTMzNjk0MC1jNzhiLTExZTEtYWU0Yy0wMDIzMzJiODIz
YWPXAAAAjQAAAAEB+gAAAA==
UmNIUBcBAAAAJAEAADUCAAAAADQAAAAAAAAAHv///z8AAAEAAQAAAAwAAAAAAAAAAQAAAAoAAAAA
AAAAEwAAAEZMRVggLSBmbGV4aWJsZSBsb3ZlIwAAAAMAAAABAAAAAwAAAAQAAABodHRwOi8vd3d3
Lm15ZmFiMi5jb20vaHR0cDovL215ZGV2LnNwb290bmlrLmNvbTo4MDAwL21lZGlhL3Nwb3QvdG1w
LzEzY2VkYTZiLWM3OGItMTFlMS05MjJkLTAwMjMzMmI4MjNhYy5qcGVnAgAAAONzMAAeZ4q3TBIA
AAEBAAAAWmeKt0wSAAAyZTMzNjk0MC1jNzhiLTExZTEtYWU0Yy0wMDIzMzJiODIzYWPXAAAAjQAA
AAEB+gAAAA==
'/*!*/;


2012/9/5 Justin Swanhart <gree...@gmail.com>
Reply all
Reply to author
Forward
0 new messages