greenlion / swanhart-tools Goto Github PK

This was the input query:

    SELECT   work_share_round_ref.field_share_round_nid                                     AS `round_nid`,
             work_share_submitter.field_share_submitter_uid                                 AS `user_uid`,
             MIN(work_share_difficulty.field_share_difficulty_value)                        AS `user_round_min_difficulty`,
             COUNT(1)                                                                       AS `user_round_share_count`

    FROM     node                                       AS `node_work_share`

    JOIN     field_data_field_share_round               AS `work_share_round_ref`
    ON       node_work_share.nid               = work_share_round_ref.entity_id
       AND   work_share_round_ref.entity_type  = 'node'
       AND   work_share_round_ref.deleted      = 0

    JOIN     field_data_field_share_difficulty          AS `work_share_difficulty`
    ON       node_work_share.nid               = work_share_difficulty.entity_id
       AND   work_share_difficulty.entity_type = 'node'
       AND   work_share_difficulty.deleted     = 0

    JOIN     field_data_field_share_submitter           AS `work_share_submitter`
    ON       node_work_share.nid               = work_share_submitter.entity_id
       AND   work_share_difficulty.entity_type = 'node'
       AND   work_share_difficulty.deleted     = 0

    JOIN     field_data_field_share_verified_by_pool    AS `work_share_verified`
    ON       node_work_share.nid               = work_share_verified.entity_id
       AND   work_share_difficulty.entity_type = 'node'
       AND   work_share_difficulty.deleted     = 0

    WHERE    node_work_share.type                                   = 'share'
       AND   node_work_share.status                                 = 1
       AND   work_share_verified.field_share_verified_by_pool_value = 1

    GROUP BY round_nid, user_uid

This resulted in the following output from convert:

CALL flexviews.create('theredpool_drupal', '', 'INCREMENTAL');
SET @mvid := LAST_INSERT_ID();
CALL flexviews.add_expr(@mvid,'GROUP','work_share_round_ref.field_share_round_nid','round_nid');
CALL flexviews.add_expr(@mvid,'GROUP','work_share_submitter.field_share_submitter_uid','user_uid');
CALL flexviews.add_expr(@mvid,'MIN','work_share_difficulty.field_share_difficulty_value','user_round_min_difficulty');
CALL flexviews.add_expr(@mvid,'COUNT','1','user_round_share_count');

CALL flexviews.add_table(@mvid,'theredpool_drupal','node','node_work_share',NULL);
CALL flexviews.add_table(@mvid,'theredpool_drupal','field_data_field_share_round','work_share_round_ref','ON node_work_share.nid = work_share_round_ref.entity_id AND work_share_round_ref.entity_type = \'\'node\'\' AND work_share_round_ref.deleted = 0');
CALL flexviews.add_table(@mvid,'theredpool_drupal','field_data_field_share_difficulty','work_share_difficulty','ON node_work_share.nid = work_share_difficulty.entity_id AND work_share_difficulty.entity_type = \'\'node\'\' AND work_share_difficulty.deleted = 0');
CALL flexviews.add_table(@mvid,'theredpool_drupal','field_data_field_share_submitter','work_share_submitter','ON node_work_share.nid = work_share_submitter.entity_id AND work_share_difficulty.entity_type = \'\'node\'\' AND work_share_difficulty.deleted = 0');
CALL flexviews.add_table(@mvid,'theredpool_drupal','field_data_field_share_verified_by_pool','work_share_verified','ON node_work_share.nid = work_share_verified.entity_id AND work_share_difficulty.entity_type = \'\'node\'\' AND work_share_difficulty.deleted = 0');
CALL flexviews.add_expr(@mvid,'WHERE','node_work_share.type = \'\' \'\' AND node_work_share.status = 1 AND work_share_verified.field_share_verified_by_pool_value = 1','where_clause');
CALL flexviews.enable(@mvid);

Three issues:

1. Why does the convert script not take an MV name parameter on the command line? I always forget to fill in the name on the first line of the output.
2. Each single-quoted string is over-quoted (i.e. ' turns into '' instead of ').
3. The first single-quoted string in the WHERE clause is completely dropped.

If I change to double-quoted strings, 2 and 3 are no longer issues.

Otherwise dropping and recreating the table with different structure could break flexcdc.

Getting a 404: http://flexviews.googlecode.com/svn/trunk/manual.html

Is there an alternative source?

• Populating/Updating shard list
• Populating gearman servers
  INSERT INTO gearman_job_servers (schema_id, hostname, port, local) VALUES(5, 'sol.home.lan',4370, '')SETUP FAILED1366Incorrect integer value: '' for column 'local' at row 1

[config]
gearman=localhost:4370

swanhart-tools / flexviews / consumer / include / flexcdc.php
line 1271 $mvLogname undefined

A possible enhancement:

• When adding a COLUMN expr, add_expr() will raise an error if there are GROUP or aggregate exprs.
• When adding a GROUP or MAX-like expr, add_expr() will raise an error if there are COLUMN exprs.
• If a GROUP expr is identical to a COLUMN expr, the latter will be ignored (no error is needed)

The line that reads:

FAILURE_EMAIL_BODY=$(tail flexcdc.err)

Should read:

FAILURE_EMAIL_BODY=$(tail $FAILURE_ERROR_LOG)

The install_config_repo.php script advises to use a non root user:

NOTE: If using the local server as the repository, then for best results use 127.0.0.1 instead of 'localhost'.
Enter shard query repository hostname: [127.0.0.1] >
Enter shard query repository port: [3306] >
Enter shard query repository database/schema name: [shard_query] >
Please username (a non-root user): [shard_query] >

However, the INSTALL or the script itself don't contain (or I could not find) a list of privileges needed by this user. This means that to complete the setup I'll probably create a user with 'grant all ...', which would be the same as using root, minus the grant option.

I think since users will follow the INSTALL file for installation, an example CREATE USER / GRANT statement should be included on it.

Flexviews installs and is set up successfully with no errors but once a table is added using the php script, nothing happens. None of the steps on the following blog fail: http://www.mysqlperformanceblog.com/2011/03/25/using-flexviews-part-two-change-data-capture/.

I'm at a loss as to what should be done next to debug this as the error log shows nothing and at no point during the setup or the subsequent inserts is an error thrown.

MySQL Server version: 5.5.34
Ubuntu 12.04

Probably need an add_shard.php script which populates the new shard with the tables.

Hello,

I installed flexview without errors, configured .ini file appropriately. Flex creates mvlogs table with mapping:
cdc foo mvlog_cf2334a9995f95d9d66580fc7176b113 1
cdc mvlogs mvlog_39a8778cab4301203fc502eaef13b402 1
It auto_changelog is set true and works. ( as I add new tables, they instantly appear in mvlogs)
But actual changes in tables are in inserted in mvlogs_xxxx tables.
No error is returned.
Any advice there I should look for answer?

MySQL 5.1.73
PHP 5.3.3

Edit: when I run "run_consumer.php" I get
-- /usr/bin/mysqlbinlog --port=3306 --user=vv_cdc --password=cdc --host=192.168.200.160 --base64-output=decode-rows -v -R --start-position=3401 --stop-position=4176 mysql-bin.000006 2>&1
Does it stuck those positions?

CALL flexviews.invalid(flexviews.get_id('testing','deneme_summary'));
CALL flexviews.create('testing','deneme_summary','COMPLETE');
SET @mvid := last_insert_id();
CALL flexviews.set_definition(@mvid,'SELECT userId, SUM(value) AS summary FROM testing.deneme GROUP BY userId;');
CALL flexviews.enable(flexviews.get_id('testing','deneme_summary'));

Result: Error Code: 1072. Key column 'mview$hash' doesn't exist in table

Error happens in enable procedure's if block starting from line 104 and ending in line 111, when I comment the if block out. There seems to be no problems.

I couldn't quite understand the function of that block, so I wanted to ask can this affect mi in the long term (or which type of views won't work with this modification)?

If this is a bug, I would be glad to submit a PR but first I have to understand the problem.

Note: I'm only using the complete refresh mode.

Is it possible to shard by a datetime field? The source only seems to handle string or interger. We don't use the PL id field for initial SELECT's. We use a datetime field and then join other tables based on the id.

Also, is there anything that needs to be configured in the shard map tables for the directory mapper?

If I decide to only use COMPLETE refresh type, and do not perform CDC installation,

Lines 67-69 of install_procs.inc yield an error (since the tables used in that part are setup by CDC installation), if I comment them out, are there any corner cases that will impact the correctness of COMPLETE refresh mehod?

-- Flexviews requires a signal table to communicate with the external binlog consumer
#call flexviews.create_mvlog('flexviews', 'mview_signal');
#ALTER TABLE flexviews.mvlog_3b0cef8fb9788ab03163cf02b19918d1 add key(signal_id);
#CREATE OR REPLACE VIEW flexviews.flexviews_mview_signal as select * from flexviews.mvlog_3b0cef8fb9788ab03163cf02b19918d1;

add skip_old_update_image=true to flexcdc.ini

Push down the necessary components of the expression:
SUM
SUM(pow(expr,2))
COUNT(expr)

I am able to set-up an incremental MV from a query just fine, but when I try to refresh it using:

call flexviews.refresh(flexviews.get_id('theredpool_drupal', 'mv_user_round_summary')); 

I get:

ERROR 1054 (42S22): Unknown column 'ERROR: ERROR: SIGNAL ID NOT FOUND (FlexCDC consumer is likely behind)' in 'field list'

Even though I have verified that consumer_safe.sh is running.

The flexviews sample consumer.ini file shows an example of the only_databases option, with flexviews as the first database to include. Wouldn't this cause an infinite loop of CDC?

i got this problem in mysql-proxy log, and mysq-proxy not working correctly

2014-03-22 18:35:16: (critical) (read_query_result) [string "/usr/share/shard-query/proxy/mysqlproxy.lua"]:563: bad argument #1 to 'sub' (string expected, got nil)

how to solve this problem ?

delta sql :

INSERT INTO test.member_sales_delta SELECT (o.dml_type * 1) as dml_type,o.uow_id as uow_id,o.fv$gsn as fv$gsn ,(memberId) as `memberId`, (c.username) as `uname`, (FROM_UNIXTIME(addTime, "%Y-%m")) as `sale_when`, SUM((o.dml_type * 1) * ol.number) as `total_items`, SUM((o.dml_type * 1) * ol.salePrice * ol.number) as `total_price`, IFNULL(SUM((o.dml_type * 1)),0) as `total_lines`
FROM  flexviews.mvlog_e5c4135feaa1670712433c128e871e30 as o   JOIN  test.OrderProducts as ol ON o.id = ol.orderId  JOIN  test.Members as c ON o.memberId = c.id
WHERE o.uow_id >86 AND o.uow_id <=96 AND (o.dml_type * 1 IS NOT NULL)  GROUP BY (memberId), (c.username), (FROM_UNIXTIME(addTime, "%Y-%m"))

"SUM((o.dml_type * 1) * ol.number) as total_items" cause the error " BIGINT UNSIGNED value is out of range in"

the field ol.number type is "int unsigned"

in procs/delta.sql line 900
if express type is SUM , add CAST function to solve this issue

SET v_mview_expression = CONCAT('SUM(',v_dml_type, ' * ', 'CAST(', v_mview_expression, ' AS SIGNED))');

Upgraded to MySQL 5.6 with the mysqlbinlog program from 5.7, and now I'm getting this:

SQL_ERROR IN STATEMENT:
INSERT INTO `flexviews`.`mvlog_3b0cef8fb9788ab03163cf02b19918d1` VALUES (1, @fv_uow_id,1,27,26,1385616034)
COULD NOT EXEC SQL:
INSERT INTO `flexviews`.`mvlog_3b0cef8fb9788ab03163cf02b19918d1` VALUES
Incorrect datetime value: '1385616034' for column 'signal_time' at row 1

For 1.9GA

I have a utf8 table and flexviews work fine, but encoding for russian letters failed.
in consumer.ini i have set-charset=utf8 in section [source] and [dest]

but it doesn't help. I can't find in code, where is bug.

Trying to create a materialized view that had an error in a join clause flexviews.enable errored out (obviously).

After fixing the error (via remove_table and add_table) and trying to run enable again I get:

mysql> call flexviews.enable(12);
ERROR 1644 (45000): An expression with this alias already exists: mview$hash in materialized view: 12

Calling flexviews.disable() and/or flexviews.drop() doesn't seem to have any effect

mysql> call flexviews.drop(12);
ERROR 1644 (45000): This materialized view is already disabled (NOTHING WAS DROPPED)
mysql> call flexviews.disable(12);
+-----------------------------------------------------------------------------------------------------------------------------+
| WARNING                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------+
| This procedure is deprecated.  Please use flexviews.DROP() to remove a view or flexviews.INVALIDATE() to mark it as invalid |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

ERROR 1644 (45000): This materialized view is already disabled (NOTHING WAS DROPPED)

I'm capable of manually deleting the view from flexviews.mviews and recreating it from scratch however.

Try to create a table with bad syntax.

should be:
d_yearmonthnum = 199401

drop()'s robodoc is the same as disable()'s :-)

large binary logs need larger values

ALTER needs to be fixed

    if(!empty($cache[$key])) {
        return $cache[$key][0];
    } 

should be:

    if(!empty($cache[$key])) {
        return $cache[$key];
    } 

Hello,

Are there any tools or is there support for splitting a shard?

Say I have a single MySQL server to start and I want to split that up into two smaller shards of data, how would I go about that?

when ru consumer_safe.sh with php version 5.5.3

Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/share/php/Console/Getopt.php on line 109
PHP Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context in /usr/share/php/Console/Getopt.php on line 154
PHP Strict Standards: Non-static method PEAR::isError() should not be called statically in swanhart-tools/flexviews/consumer/run_consumer.php on line 39
PHP Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in swanhart-tools/flexviews/consumer/include/flexcdc.php on line 140
PHP Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in swanhart-tools/flexviews/consumer/include/flexcdc.php on line 143

This error message seems to be obsolete:

if(!defined('HAVE_PHP_SQL_PARSER')) {
echo "You need to download the SQL parser component in order to use this tool. It is hosted separately here:\nhttp://php-sql-parser.googlecode.com/svn/trunk/php-sql-parser.php\n";
exit;
}

Probably the whole block could be removed?

This is a new issue for discussion on mpb.

Make the loader easier to use, and support S3

mysql> call flexviews.create_mvlog('test','bozo');
Query OK, 1 row affected (0.01 sec)

mysql> call flexviews.create('test','mv2','INCREMENTAL');
Query OK, 0 rows affected (0.00 sec)

mysql> call flexviews.add_table(1, 'test','bozo', 'bzo', null);
Query OK, 1 row affected (0.01 sec)

mysql> call flexviews.enable(1);
ERROR 1644 (45000): COULD NOT VALIDATE MATERIALIZED VIEW. CHECK @MV_DEBUG.

procs refresh_all first param v_mode error

old:

IN v_mode ENUM('COMPLETE', 'INCREMENTAL')

should

IN v_mode ENUM('COMPLETE', 'COMPUTE', 'APPLY', 'BOTH')

line 909

  SET v_mview_expression  = CONCAT('IF(',v_mview_expression,' IS NULL,0,', v_dml_type);

should be:

  SET v_mview_expression  = CONCAT('IF(',v_mview_expression,' IS NULL,0,', v_dml_type,')');

?

SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
SQL_ERROR IN STATEMENT:
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
SQL_ERROR IN STATEMENT:
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
SQL_ERROR IN STATEMENT:
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SQL_ERROR IN STATEMENT:
SET NAMES utf8 */;

table "mview_compute_schedule" only update, no insert,

php run_consumer.php --skip-trans=1

OVER / PARTITION clauses need to be support in SELECT clause
Custom aggregate functions need to be marked as supporting windows
Hashes will need to be generated for each window.

Separate issues will be filed for each window function that will be supported.