is it normal ? count column behavior on incremental view

[mdu]

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]

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]

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]

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]

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>