Help creating rules.

[Fredrik Erlandsson]

Hi,

I have a "quite complex" query that might get some help from some kind om materialized view.

This is my query: 

SELECT ROUND(COUNT(CASE WHEN status = 'done' THEN 1 END)*100.0/COUNT(*),4), name, FROM_UNIXTIME(MAX(time_stamp)), ROUND(SUM(time), 4),CONCAT(COUNT(CASE WHEN status = 'done' THEN 1 END),'/', COUNT(*)) ,CONCAT(COUNT(CASE WHEN status = 'pulled' THEN 1 END) , '/',COUNT(*))FROM post JOIN page WHERE post.page_id=page.id GROUP BY page_id;

And this is my tables I'm SELECTing from:

mysql> show create table post\G show create table page \G

*************************** 1. row ***************************

       Table: post

Create Table: CREATE TABLE `post` (

  `id` int(32) NOT NULL AUTO_INCREMENT,

  `post_id` varchar(48) DEFAULT NULL,

  `page_id` int(11) DEFAULT NULL,

  `seq` int(11) DEFAULT NULL,

  `date` varchar(13) DEFAULT NULL,

  `data` longblob,

  `status` varchar(40) DEFAULT NULL,

  `time_stamp` int(11) DEFAULT NULL,

  `who` varchar(60) DEFAULT NULL,

  `time` float DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `post_id` (`post_id`),

  KEY `page_id` (`page_id`),

  KEY `IDX_FIELD2` (`post_id`),

  KEY `status_time_stamp_id` (`status`,`time_stamp`,`id`),

  KEY `status_post_id_time_stamp_id` (`status`,`post_id`,`time_stamp`,`id`),

  KEY `id_status_timestamp` (`id`,`status`,`time_stamp`)

) ENGINE=InnoDB AUTO_INCREMENT=3472802 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

*************************** 1. row ***************************

       Table: page

Create Table: CREATE TABLE `page` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=745 DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

Can you help me create the appropriate configuration for flexview to access my results from the query above, please?

[Justin Swanhart]

Hi Fredrik,

I just wanted to let you know that I've been busy and have not had a
chance to review this query yet. I'll reply further in the next day
or so.

--Justin

[Fredrik Erlandsson]

Ok, this is what I did (perhaps someone can use it as a tutorial or I can get back to it)..

Used the tool found at: http://flexviews.sourceforge.net/convert.php

With the following input: 

database: flexviews

table: controller_stat_helper

Enter SQL (there is a sample query there for you):

SELECT COUNT(CASE WHEN status = 'done' THEN 1 END) as done, 

name as name, 

page_id as page_id,

MAX(time_stamp) as update_time, 

SUM(time) as exec_time,

COUNT(status) as total_num,

COUNT(CASE WHEN status = 'pulled' THEN 1 END) as pulled) FROM crawling.post JOIN crawling.page WHERE post.page_id=page.id GROUP BY page_id;

Resulted in the following output:

# REFRESH_TYPE: INCREMENTAL

CALL flexviews.create('flexviews', 'controller_stat_helper', 'INCREMENTAL');
SET @mvid := LAST_INSERT_ID();
CALL flexviews.add_table(@mvid,'crawling', 'post','post', NULL);
CALL flexviews.add_table(@mvid,'crawling', 'page','page', ' ');
CALL flexviews.add_expr(@mvid,'COUNT', 'CASE WHEN status = ''done'' THEN 1 END', 'done');
CALL flexviews.add_expr(@mvid,'GROUP', 'name', 'name');
CALL flexviews.add_expr(@mvid,'GROUP', 'page_id', 'page_id');
CALL flexviews.add_expr(@mvid,'MAX', 'time_stamp', 'update_time');
CALL flexviews.add_expr(@mvid,'SUM', 'time', 'exec_time');
CALL flexviews.add_expr(@mvid,'COUNT', 'status', 'total_num');
CALL flexviews.add_expr(@mvid,'COUNT', 'CASE WHEN status = ''pulled'' THEN 1 END', 'pulled');
CALL flexviews.add_expr(@mvid,'WHERE','post.page_id=page.id' , 'where_clause');
CALL flexviews.enable(@mvid);

Added that information to a file I called: controller_stat_helper.sql

mysql < controller_stat_helper.sql 

After a long execution time (36 min 52.16 sec) I did have a new view.

Selecting it as:

SELECT ROUND(done*100.0/total_num,4), name, FROM_UNIXTIME(update_time), ROUND(exec_time, 4),CONCAT(done,' / ', total_num) ,CONCAT(pulled, ' / ', total_num)FROM flexviews.controller_stat_helper;

Is of course much faster than before (6 min 6.93 sec before, now  0.09 sec).

But before selection one must update via:

mysql "CALL flexview.refresh(ID HERE,'BOTH', NULL);"

This actually currently fails with the error:

ERROR 1054 (42S22): Unknown column 'ERROR: ERROR: SIGNAL ID NOT FOUND' in 'field list'

 

Any ideas?

[Justin Swanhart]

Hi,

You have to make sure that FlexCDC is running in the background. When
a view is created, the flexviews.mview_signal table is used to
identify the transaction id at which the view was created at, so that
it can be incrementally refreshed from that point forward. The first
time you refresh a view, Flexviews searches the changelogs of the
signal table to detect at which transaction id it was created, and
modify the Flexviews data dictionary appropriately.

Also, it is likely that your version of FlexCDC includes a regression
that breaks views with aggregation. You should download the latest
SVN version. I'll be releasing version 8.0.1 beta 2 this week which
fixes all known bugs.

--Justin

[Fredrik Erlandsson]

Ok, so with the following modifications the status is now..

mysql -e 'drop database flexviews;'

svn checkout http://flexviews.googlecode.com/svn/trunk/ flexviews-read-only

cd flexviews-read-only/consumer

## fix consumer.ini

php setup_flexcdc.php

## verification of flexcdc: mysql -u root -p -e 'select * from flexviews.binlog_consumer_status\G'

sh consumer_safe.sh --ini=consumer.ini &

php add_table.php --schema=crawling --table=post

php add_table.php --schema=crawling --table=page

(cd ..;mysql < install.sql) 
mysql < controller_stat_helper.sql

## modification of the tables (both a few updates on the post table and an forced insert on the page table)

## The command mysql -e 'select * from flexviews.crawling_post;' &

## mysql -e 'select * from flexviews.crawling_page;'

## outputs nothing..

It seems like the run_consumer don't get the changes, even if the changes show up in my bin-log files..

[Justin Swanhart]

Hi,

Do you see your tables in here?
select * from flexviews.mvlogs

Is there anything in flexcdc.err?

--Justin

[Fredrik Erlandsson]

# mysql -e 'select * from flexviews.mvlogs'
+--------------+--------------+------------------------+-------------+
| table_schema | table_name | mvlog_name | active_flag |
+--------------+--------------+------------------------+-------------+
| crawling | page | crawling_page | 1 |
| crawling | post | crawling_post | 1 |
| flexviews | mview_signal | flexviews_mview_signal | 1 |
+--------------+--------------+------------------------+-------------+

So it should work. But I had some other problems with my MySQL server
so let me get back when I have verified my installation.

Ah, the err file shows just mysqlbinlog info, like:

-- /usr/local/bin/mysqlbinlog
--user=root --host=localhost --port=3306 --password= --socket=/tmp/my
sql.sock -R --start-position=4 --stop-position=94011233 mysql-bin.0001
17 2>&1

Is that right?

And yes, the table post is updated like two-three times a sec.

MVH

Fredrik Erlandsson

--
Sent from my iPhone

[Fredrik Erlandsson]

Hi Justin,

Do you have any more ideas what might be wrong with my installation/configuration of flexview?

BTW, I do get this message:

[root@web ~/flexview/flexviews-read-only/consumer]# php add_table.php --schema=crawling --table=post

Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/local/share/pear/Console/Getopt.php on line 87

Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/local/share/pear/Console/Getopt.php on line 128

Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/local/share/pear/Console/Getopt.php on line 128

Strict Standards: Non-static method PEAR::isError() should not be called statically in /root/flexview/flexviews-read-only/consumer/add_table.php on line 14

success

[root@web ~/flexview/flexviews-read-only/consumer]# php add_table.php --schema=crawling --table=page

Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/local/share/pear/Console/Getopt.php on line 87

Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/local/share/pear/Console/Getopt.php on line 128

Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/local/share/pear/Console/Getopt.php on line 128

Strict Standards: Non-static method PEAR::isError() should not be called statically in /root/flexview/flexviews-read-only/consumer/add_table.php on line 14

success

This is the content from my controller_stat_helper.sql:

CALL flexviews.create('flexviews', 'controller_stat_helper', 'INCREMENTAL');

SET @mvid := LAST_INSERT_ID();

CALL flexviews.add_table(@mvid,'crawling', 'post','post', NULL);

CALL flexviews.add_table(@mvid,'crawling', 'page','page', ' ');

CALL flexviews.add_expr(@mvid,'COUNT', 'CASE WHEN status = ''done'' THEN 1 END', 'done');

CALL flexviews.add_expr(@mvid,'GROUP', 'name', 'name');

CALL flexviews.add_expr(@mvid,'GROUP', 'page_id', 'page_id');

CALL flexviews.add_expr(@mvid,'MAX', 'time_stamp', 'update_time');

CALL flexviews.add_expr(@mvid,'SUM', 'time', 'exec_time');

CALL flexviews.add_expr(@mvid,'COUNT', 'status', 'total_num');

CALL flexviews.add_expr(@mvid,'COUNT', 'CASE WHEN status = ''pulled'' THEN 1 END', 'pulled');

CALL flexviews.add_expr(@mvid,'WHERE','post.page_id=page.id' , 'where_clause');

CALL flexviews.enable(@mvid);

Any ideas?

--
Fredrik Erlandsson
Sunna kanalväg 6b
SE-371 41 Karlskrona

Telefon : +46(0)455 - 82164
Mobil : +46(0)70 - 9798866
E-post : fred...@gmail.com
ICQ-UIN: 8292444

[Fredrik Erlandsson]

Yeah, I think I have found the error for you. 

To start with, this is my environment:

# uname -a

FreeBSD web 9.0-RELEASE FreeBSD 9.0-RELEASE #0: Tue Jan  3 07:46:30 UTC 2012     ro...@farrell.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC  amd64

# php --version

PHP 5.4.3 (cli) (built: Jul 18 2012 00:03:40) 

Copyright (c) 1997-2012 The PHP Group

Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies

For some reason the which returns a newline making the cmdLine a bit crippled here is the fix:

svn diff --diff-cmd diff -x -uw

Index: include/flexcdc.php

===================================================================

--- include/flexcdc.php (revision 282)

+++ include/flexcdc.php (working copy)

@@ -159,7 +159,7 @@

  $settings = $this->read_settings();

  $this->settings = $settings;

  }

- if(!$this->cmdLine) $this->cmdLine = `which mysqlbinlog`;

+ if(!$this->cmdLine) $this->cmdLine = trim(`which mysqlbinlog`);

  if(!$this->cmdLine) {

  die1("could not find mysqlbinlog!",2);

  }

That gave me the following error:

Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/local/share/pear/Console/Getopt.php on line 87

Strict Standards: Non-static method PEAR::isError() should not be called statically in /root/flexview/flexviews-read-only/consumer/run_consumer.php on line 38

-- /usr/local/bin/mysqlbinlog --user=root --host=localhost --port=3306 --password= --socket=/tmp/mysql.sock -R --start-position=256666 --stop-position=64205040 mysql-bin.000115 2>&1

Strict Standards: Only variables should be passed by reference in /root/flexview/flexviews-read-only/consumer/include/binlog_parser.php on line 500

Strict Standards: Only variables should be passed by reference in /root/flexview/flexviews-read-only/consumer/include/binlog_parser.php on line 502

Strict Standards: Only variables should be passed by reference in /root/flexview/flexviews-read-only/consumer/include/binlog_parser.php on line 503

Strict Standards: Only variables should be passed by reference in /root/flexview/flexviews-read-only/consumer/include/binlog_parser.php on line 401

Strict Standards: Only variables should be passed by reference in /root/flexview/flexviews-read-only/consumer/include/binlog_parser.php on line 408

Strict Standards: Only variables should be passed by reference in /root/flexview/flexviews-read-only/consumer/include/binlog_parser.php on line 514

Forcing me to apply the following changes:

svn diff --diff-cmd diff -x -uw

Index: include/flexcdc.php

===================================================================

--- include/flexcdc.php (revision 282)

+++ include/flexcdc.php (working copy)

@@ -18,7 +18,7 @@

     If not, see <http://www.gnu.org/licenses/>.

 */

 

-error_reporting(E_ALL);

+error_reporting(E_ALL ^ E_STRICT);

 ini_set('memory_limit', 1024 * 1024 * 1024);

 define('SOURCE', 'source');

 define('DEST', 'dest');

Now it seems to be working!

Try to apply my suggested fixes. Thanks.

/F