September 1, 2014

Using Flexviews – part two, change data capture

In my previous post I introduced materialized view concepts. This post begins with an introduction to change data capture technology and describes some of the ways in which it can be leveraged for your benefit. This is followed by a description of FlexCDC, the change data capture tool included with Flexviews. It continues with an overview of how to install and run FlexCDC, and concludes with a demonstration of the utility.

As a reminder, the first post covered the following topics:

  1. What is a materialized view(MV)?
  2. It explained that an MV can pre-compute joins and may aggregate and summarize data.
  3. Using the aggregated data can significantly improve query response times compared to accessing the non-aggregated data.
  4. Keeping MVs up-to-date (refreshing) is usually expensive.
  5. A change data capture tool can be used to implement an efficient way of refreshing them.

 

What is Change Data Capture (CDC)?

As the name implies, CDC software captures the changes made to database rows and makes those changes available in a convenient form which can be accessed by other programs. CDC applications exist for many commercial databases but until recently this type of software was not available for MySQL.

Change Data Capture can be used to:

  1. Monitor a database table, or tables for changes.
  2. Improve ETL performance by identifying the data which has changed.
  3. Maintain materialized views with Flexviews (the primary purpose of FlexCDC).
  4. Feed search engines like Sphinx or Solr only the rows that change.
  5. Feed third party replication systems.
  6. Provide data to “external triggers” such as Gearman jobs.

CDC tools usually operate in one of the following ways:

  1. Timestamps (usually more than one) to identify rows that have changed
  2. Triggers to capture changes synchronously
  3. Database log reading to capture the changes asynchronously

The first method has serious drawbacks, such that it can’t identify deleted rows and MySQL timestamps may not be flexible enough.

The trigger method has a lot of problems. Triggers add a significant overhead. When the structure of a table is changed, the triggers must be changed. The work in the trigger is immediate and affects every transaction. Finally, MySQL has limited trigger support, some of which is the cause of the aforementioned problems. The biggest problem, at least from standpoint of how Flexviews works, is that triggers can not, under normal conditions, detect the commit order of transactions. This above all makes triggers an unacceptable CDC method.

This leaves the third method, log based capture as the best option because it imposes less overhead than triggers and change data capture may be done remotely and asynchronously.

 

Binary log based CDC

The CDC tool included with Flexviews is called FlexCDC. It seemed like an appropriate name. The Binary Log is the MySQL log which records changes to tables in the database. FlexCDC reads the binary log to determine what rows have changed. Because of this, FlexCDC requires that you use row-based binary logs (RBR). If you don’t have MySQL 5.1 or aren’t using RBR, then it is possible to set up a dedicated MySQL slave which has log_slave_updates=1 and binlog_format=row to process SBR changes from a MySQL master. I’ll talk more about that in another blog post.

FlexCDC does not implement a full binary log parser. Instead, it invokes the ‘mysqlbinlog’ utility and it processes the predictable output of this program. mysqlbinlog will always be able to read the binary logs of the version of mysql it ships with (and previous versions) so there is no worry about binary log format changes. FlexCDC is written in PHP so it is portable.

 

Setting up FlexCDC

FlexCDC has some basic requirements:

  • MySQL 5.1+
  • row based logging (binlog_format=1)
  • unique server_id in the my.cnf
  • log_slave_updates=1, if this is a MySQL slave
  • transaction_isolation=READ-COMMITTED

You can get FlexCDC directly out of the Flexviews SVN. I suggest that you just grab all of Flexviews:

Next you have to customize the example ini file. FlexCDC is located in the flexviews/consumer/ subdirectory.

Create the settings file:

Change to the flexviews/consumer directory and copy the consumer.ini.example file to consumer.ini and edit it, making appropriate changes. The file is well commented. The example settings file should work for most MySQL installations which allow connections as root with no password from localhost. It is possible to read from and/or write to remote servers, but this example uses the local machine which is the usual configuration for Flexviews since it requires local access to the tables and the changelogs in order to maintain materialized views. Most database servers have some spare CPU for binary log parsing.

Run the setup script:

This will create the metadata tables and capture the initial binary log position.

If the setup detects any problems (such as binary logging not being enabled) it will exit with an error. It will exit with a message “setup completed” otherwise.

 

Verify installation

The binary log stores it progress in a metadata table:

If you select from that table you won’t see anything changing, even if you are writing into your database. This isn’t anything to worry about, since the background process isn’t running yet.

Starting the background process:

FlexCDC includes a consumer_safe.sh script that will start up a copy of FlexCDC and restart it if it exits with error. You can shut down FlexCDC by sending it a HUP signal. The script will drop a .pid file so you know what process to HUP.

 

Adding a changelog to a table

FlexCDC copies the contents of database rows which change into special tables called changelogs. Each changelog is located in the flexviews database and is named $SCHEMA_$TABLE where $SCHEMA is the schema in which the source table is located and $TABLE the name of the source table. If that is confusing it should be clear in a moment.

Lets create a table, insert some rows, add a change log, delete rows and then insert some more of them:

Even though FlexCDC is running in the background, it didn’t capture any changes from that insert. We need to add the table to the list of tables to changelog. There is a utility included with FlexCDC called ‘add_table.php’. This script automates the process of adding a table to the list of tables to changelog. It does this by adding an entry to the flexviews.mvlogs metadata table, and it creates the changelog table itself.

Note that you can enable auto_changelog=true in the config file to automatically record changes for any table, starting from the first time a change is seen for that table. This is generally only useful if you have a small number of tables, and you want to track changes on all of them.

You may have also noted that I did not include –ini=consumer.ini. This is because this is the default config filename to search for. I included it in the earlier examples for illustration purposes.

 

Examine the changes

Now that the changelog has been added, any changes to test.demo will automatically be captured.

Insert data in one transaction (two rows):

And delete data in a second transaction:

The changelog is flexviews.test_demo. This because the source table is test.demo.

As you can see, there are three rows in the changelog, each representing one of the changes we made.

You will notice that the source table only has two columns, but the changelog contains five. All change logs contain three additional metadata columns: dml_type, uow_id and fv$server_id. These columns represent the type of change (insert is 1, delete -1), the transaction order and the source of the changes, respectively.

Finally, note that the two insertions happened inside of the same transaction, and that the insertions happened before the deletion. Though they are none shown here, updates would be represented by a deletion followed by an insertion.

About Justin Swanhart

Justin is a Principal Support Engineer on the support team. In the past, he was a trainer at Percona and a consultant. Justin also created and maintains Shard-Query, a middleware tool for sharding and parallel query execution and Flexviews, a tool for materialized views for MySQL. Prior to working at Percona Justin consulted for Proven Scaling, was a backend engineer at Yahoo! and a database administrator at Smule and Gazillion games.

Comments

  1. aivan says:

    Hi justin I just need a little help get “Could not read ini file: consumer.ini” Please check this pastebin http://pastebin.com/GkQdaKHD

  2. aivan says:

    Hi Justin. I already solved my problem I have an equal “=” character on my password field which I need to enclose in quotations. I have another problem though. I followed the steps provided from 1 to 3. But when I enable the MV using the SQL_API. I get this error ” PROCEDURE flexviews.enable does not exist”

  3. Americo says:

    Hey Justin,

    I tried to run the setup script and I got an error “Getopt.php” is missing. Do you have any clue where I can find this file?

    Thanks

    Americo

  4. Americo says:

    Hey Justin,

    I was able to install FlexView. Anyway, because I am completely blind on PHP usage (I am a J2EE developer), I would suggest you to add as requirements, otherwise your php setup will not run:

    - php-pear
    - php-mysql

    Thanks

    Awesome work!!!!

    Americo

  5. Matt says:

    I can get through this guide successfully up to “Examine the changes” section. At that point I can INSERT and DELETE from test.demo all I want and it never shows up in the flexviews.test_demo.

    I can verify that consumer_safe.sh is running and that the flexviews.test_demo table does exist. Any ideas on what could be happening? I’m on MySQL 5.1.48 and PHP 5.3.3. There are no errors in the flexviews error log. Any idea?

  6. Americo says:

    I tried Flexview once, last October without any luck. Like Matt’s previous post, the log table is not populated at all. I was thinking that probably it was cause any versioning issue. I mean, mysql was upgraded to the newest version.

    Recently, due some servers migration, I was able to install a pristine mysql instance. Unfortunately again, no luck.

    The project sounds interesting, but it seems that it has some issues.

  7. Leena says:

    Hi Justin,

    Great information! I plan to use CDC to pick up changed data in our mySql database and populate our Vertica DB for reporting. Do you know of a readily available API that could help us read the CDC logs and quickly update the Vertica Tables ? Any pointers would be very helpful!

    Thanks,
    Leena

  8. Chuksy says:

    i am currently trying FLEXCDC on 5.1. I have followed all the steps outlined above but still have the same experience as Americo and Matt, the mvlogs table and binlog_consumer_status get populated but the test_demo never gets anything into it. i have used both ROW and STATEMENT binlog formats on the destination server to see if it has anything to do with that and neither changes the situation.

  9. Chuksy,

    Did you set binlog_format=ROW on the SOURCE server?

    What is the exact version of MySQL that you are running? I have not found a version where FlexCDC does not capture ROW based changes properly.

  10. Fredrik E says:

    Two questions;

    1. are we tracking only insert/delete updates or are we tracking even UPDATE modifications?
    2. I get the following error when trying to update my view:
    mysql -e “CALL flexviews.refresh(14, ‘COMPLETE’, NULL);”
    ERROR 1054 (42S22) at line 1: Unknown column ‘ERROR: ERROR: SIGNAL ID NOT FOUND’ in ‘field list’
    any ideas what might be wrong?

  11. Dan H says:

    Justin,

    I have installed and had version 1.7.1 working however after installing 1.8.0 I am having some issues and wanted to see if you had any suggestions. My Mysql version is 5.1.41-3ubuntu12.9-log

    My issues is that when running the consumer_safe.sh script the flexcdc.err file contains
    AFTER EVENT PARSE REMAINING_DATA_LENGTH: 0
    EVENT OVERREAD AT ACTUAL_READ_POS: 183 EXPECTED_READ_POS: 104

    I unquoted some of your echo statements in binlo_parser.php and it echoes this when it runs:
    HEADER: MIw2UA/nAwAAZgAAAAAAAAAAAAQA LEN: 28
    DECODED LEN: 21
    EXPECT BODY SIZE(after decoding): 83, BODY SIZE(encoded): 112, BYTES IN STREAM: 108
    EXPECTED BODY SIZE: 83, GOT BODY SIZE: 83
    AT ACTUAL_READ_POS: 102 EXPECTED_READ_POS: 102
    HEADER: ZpE2UBPnAwAARAAAABsCAAAAAIwB LEN: 28
    DECODED LEN: 21
    EXPECT BODY SIZE(after decoding): 49, BODY SIZE(encoded): 68, BYTES IN STREAM: 64
    EXPECTED BODY SIZE: 49, GOT BODY SIZE: 49
    AT ACTUAL_READ_POS: 68 EXPECTED_READ_POS: 68
    HEADER: ZpE2UBjnAwAAaAAAAIMCAAAQAIwB LEN: 28
    DECODED LEN: 21
    EXPECT BODY SIZE(after decoding): 85, BODY SIZE(encoded): 116, BYTES IN STREAM: 112
    EXPECTED BODY SIZE: 85, GOT BODY SIZE: 85
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined property: binlog_event_consumer::$read_varint in /vol/flexviews/consumer/include/binlog_parser.php on line 477
    PHP Notice: Undefined offset: 8 in /vol/flexviews/consumer/include/binlog_parser.php on line 373
    AT ACTUAL_READ_POS: 183 EXPECTED_READ_POS: 104

    Any help would be appreciated,
    Dan

  12. Firoz Ahmad says:

    Hi Justin,

    Flexview/FlexCDC work’s just great !!! . But their is a major performanace issue when working with more then certain number of records/columns on a table which is supposed to be published.
    With a table with large number of columns say 100+ and when the source table is inserted with more then 1 row , there is a massive delay in seeing the impact or none if 100K plus records are inserted in one go in the parent table.

    Can we have this performance issue addressed ? I understand possibly why this performance issue could be there, but would like to know if you are aware and if so, when this can be addressed.

    Would appreciate

  13. Kairen.Huang says:

    Hi Justin,

    How to config the Flexviews in windows 2003 service. Thanks

  14. Bill John says:

    Hi Justin,

    I have problem when install, I step “./consumer_safe.sh –ini=consumer.ini &”
    After I run it, screen show “Restarting FlexCDC” repeat manytime. When I enter ps. It show “consumer_safe.s”. Don’t have “h” at the end. Can you help me?

    Best Regards
    Bill

  15. Frank Zhou says:

    Finally figure out the error in code, which can not handle “`” very well.

    Near 1110 line

    Should change the code to

    $this->db = preg_replace(‘/[^a-zA-Z0-9]+/’, ”, $matches[2]);
    $this->base_table = preg_replace(‘/[^a-zA-Z0-9]+/’, ”, $matches[3]);

    Otherwise, it will never call write operation.

    Frank

  16. Frank Zhou says:

    Hi, Firoz
    Did you find the way to improve the performance? I got same problem.
    In my case,

    insert into r select null, table_name,action,now() from r;

    Test it with continue try, until

    select * from binlog_consumer_status;
    +———–+—————–+—————–+———————+
    | server_id | master_log_file | master_log_size | exec_master_log_pos |
    +———–+—————–+—————–+———————+
    | 11111 | binlog.000006 | 262934935 | 16450550 |
    +———–+—————–+—————–+———————+

    Then, it takes “forever” to wait for CDC catch up although the master completed within 30 seconds.

    :-(

  17. Justin Swanhart says:

    Firoz/Frank,

    You can try the experimental consumer which is found in the SVN repository for Flexviews. This consumer uses less memory, copies less data and reads binary logs directly, instead of decoding them with mysqlbinlog, but it is /experimental/ because it is significantly different from the old consumer.

    Please test the speed and reliability and report any bugs to the bug tracker at code.google.com/p/flexviews

  18. Frank Zhou says:

    process_rows() always check table_ordinal_is_unsigned, why continue check it in bulk insert mode?

  19. Frank Zhou says:

    Hi, Justin
    I already test experimental version, and get the error in read_varstring/read_varint function. I already file bug in http://code.google.com/p/flexviews/issues/detail?id=23 , can you help take a look? Thanks!

  20. Frank Zhou says:

    Hi, Justin

    The problem seems like in encoding and decoding part, is there any doc you can share about mysql RAW format binlog encoding and decoding? Is there still someone can help debug the code? If I can not make it work in next week, suppose we should give up this flexcdc solution, although I like it and really hope to use it as serious solution instead of just a toy demo….

    Thank you very much
    Frank

  21. Frank Zhou says:

    Here is the statement
    insert into t values(null,’firstrow’);

    Here is the original BINLOG statement, I did test it, it works fine
    BINLOG ‘
    uExTURNnKwAAKwAAAAwHAAAAACEAAAAAAAEABHRlc3QAAXQAAgMPAv8AAg==
    uExTURdnKwAAKwAAADcHAAAAACEAAAAAAAEAAv/8AQAAAAhmaXJzdHJvdw==
    ‘/*!*/;

    Here is the statement from binlog_parser.php, which try to put changed data into flexcdc.test_t table, it does not work

    BINLOG ‘
    +m1UURNnKwAAOAAAAAAAAAAAACEAAAAAAAEAB2ZsZXhjZGMALgZ0ZXN0X3QABgMIAwgDDwL/ACA=
    +m1UURdnKwAAQgAAAHEAAAAAACEAAAAAAAAABj8AAQAAAB0AAAAAAAAAZysAAAIAAAAAAAAAAQAA
    AGZpcnN0cm93
    ‘/*!*/;

  22. Giuliano says:

    Hi, Justin

    Consider the following table:

    test.demo

    c1 | c2 | c3
    —————————
    3 | 6 | 1960
    —————————
    8 | 8 | 1958
    —————————
    13 | 4 | 1960
    —————————
    5 | 2 | 1960
    —————————
    4 | 7 | 1954

    if you try to write the query:

    DELETE FROM test.demo WHERE c3=1960;

    Flexviews produces this query:

    INSERT INTO flexviews.test_demo VALUES (-1, @fv_uow_id, 999,242,3,6,1960)(-1, @fv_uow_id, 999,243,13,4,1960)(-1, @fv_uow_id, 999,244,5,2,1960)

    in this way flexviews doesn’t work, because there’s no comma between the brackets ! You receive the message: “Restarting FlexCDC!”

    In function “process_rows” you should replace :

    unset($the_row['fv$gsn']);

    with:

    unset($the_row['fv$gsn']);
    if ($valList!=”) {
    $valList=$valList.”,”;
    }

    Now, Flexviews will produce a new query:

    INSERT INTO flexviews.test_demo VALUES (-1, @fv_uow_id, 999,242,3,6,1960),(-1, @fv_uow_id, 999,243,13,4,1960),(-1, @fv_uow_id, 999,244,5,2,1960)

    in this way flexviews correctly writes on the db.

    Wonderful work! :)
    Giuliano

  23. Hello,

    I had a problem in running flexviews, seems that solution that Frank mentioned works but I made small amendment of regular expression:

    $this->db = preg_replace(‘/[^a-zA-Z0-9\_]+/’,”, $matches[2]);
    $this->base_table = preg_replace(‘/[^a-zA-Z0-9\_]+/’,”, $matches[3]); // in order to work with tables that include underscore character in it (_)

    also according to Giuliano comment, I amended the lines 759-761:
    if($valList){
    $valList = rtrim($valList, ‘,’); //I added this because some times, there were two consequent commas (,,) resulting in the query
    $valList .= “,\n”;
    }

    now everything works fine, Thanks Guys :)

    very interesting project :)

  24. Justin Swanhart says:

    Hi,

    Thanks Zaid, Giuliano and others. I’ll incorporate the fix into a new beta ASAP.

    –Justin (aka greenlion on google code)

  25. Arash says:

    Hi Justin,

    please answer this question about flexview setup failer.

    http://stackoverflow.com/questions/17986069/flexview-illegal-mix-of-collations-error

  26. Arash says:

    Hi again ,

    Can you please migrate from google code to github? because google code ban Iranian users, and we have much problem to access it.

    thanks

  27. Hi,

    I’ve the problem with flexview revision 245.

    I used Incremental method and i followed all step but when i check the log table into flexviews database
    i don’t see nothing.

    I tried also to call the refresh method but i receive an error:

    mysql> call flexviews.refresh(flexviews.get_id(‘crm_test’,’projectView1′),’BOTH’,NULL);
    ERROR 1054 (42S22): Unknown column ‘ERROR: ERROR: SIGNAL ID NOT FOUND (FlexCDC consumer is likely behind)’ in ‘field list’

    mysql version: mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1

    consumer_safe.sh process activated

    can you help me please?

    thanks

    Stefano Dolce

  28. Justin Swanhart says:

    Stefano:
    Are you using the latest source from the git repository?

  29. Justin Swanhart says:

    Arash:
    github.com/greenlion/swanhart-tools

  30. Hi Justin,

    No, i used flexviews.1.7.1.tar.gz from http://code.google.com/p/flexviews/downloads/list

    Now i tried to use github.com/greenlion/swanhart-tools

    - I copied the consumer.ini.example in consumer.ini, created a new mysql user for flexview and setup consume.ini with new user and password
    - I modified the my.cnf file and added these parameters:
    binlog_format=ROW
    server_id=999
    log_slave_updates=1
    transaction_isolation=READ-COMMITTED
    log-bin=mysql-bin

    Then i tried to create and setup flexviews database from php ./setup_flexcdc.php –ini consumer.ini but when i execute this comand i receive the following error:

    [stefanod@morpheus consumer]$ php ./setup_flexcdc.php –ini consumer.ini
    PHP Notice: Undefined property: FlexCDC::$settings in /var/www/html/flexview-new/flexviews/consumer/include/flexcdc.php on line 133
    PHP Notice: Undefined property: FlexCDC::$settings in /var/www/html/flexview-new/flexviews/consumer/include/flexcdc.php on line 134
    PHP Notice: Undefined property: FlexCDC::$settings in /var/www/html/flexview-new/flexviews/consumer/include/flexcdc.php on line 133
    PHP Notice: Undefined property: FlexCDC::$settings in /var/www/html/flexview-new/flexviews/consumer/include/flexcdc.php on line 134
    setup starting
    SQL_ERROR IN STATEMENT:
    CREATE DATABASE flexviews
    Could not CREATE DATABASE flexviews

    Can you help me please?

    Best regards

    Stefano Dolce

  31. Justin Swanhart says:

    I will take a look at the setup and see if I can reproduce the issue.

  32. Justin Swanhart says:

    Stefano: Bug fixed. Keep in mind that Federico and I are making a lot of other concurrent changes to the github repository right now in preparation for a new release.

  33. Hi Justin,

    Thanks for reply.

    now i done a git pull in my local branch and i see many contributions from 29-09-2013 to today

    in this days i will try the new version

    thanks a lot

    Best regards

    Stefano Dolce

  34. Hi Justin,

    Now i tried the new version of flexviews (sha1 id: 4af1ca7a6312d30b701867ea1333a29c1db43e65) but i found 2 problem:

    when i try to execute: [root@morpheus consumer]# sh consumer_safe.sh –ini=consumer.ini &
    [1] 31945
    [root@morpheus consumer]# Restarting FlexCDC!
    Restarting FlexCDC!
    Restarting FlexCDC!

    when i try to execute: mysql> call flexviews.refresh(flexviews.get_id(‘www_youpuppy’,’youpuppy_users’),’BOTH’,NULL);
    ERROR 1644 (45000): ERROR: SIGNAL ID NOT FOUND (FlexCDC consumer is likely behind)

    - i setted my.cnf:
    binlog_format=ROW
    server_id=999
    log_slave_updates=1
    transaction_isolation=READ-COMMITTED
    log-bin=mysql-bin
    - i copied consumer.ini.example in consumer.ini
    - i executed [root@morpheus consumer]# php ./setup_flexcdc.php –ini consumer.ini
    setup starting
    setup completed
    - i executed mysql> \. install.sql (all ok without error)
    - i created the log table with:
    [root@morpheus consumer]# php add_table.php –schema=www_youpuppy –table=youpuppy_users
    - i created the MV-youpuppyusers.sql with: create table mv_youpuppyusers AS select userid,firstname,lastname from youpuppy_users; (simple without JOIN but it’s a test :-)
    - i executed [root@morpheus flexviews]# php convert.php www_youpuppy < MV-youpuppyusers.sql
    this is output:
    CALL flexviews.create('www_youpuppy', 'mv_youpuppyusers', 'INCREMENTAL');
    SET @mvid := LAST_INSERT_ID();
    CALL flexviews.add_expr(@mvid,'COLUMN','userid','userid');
    CALL flexviews.add_expr(@mvid,'COLUMN','firstname','firstname');
    CALL flexviews.add_expr(@mvid,'COLUMN','lastname','lastname');

    CALL flexviews.add_table(@mvid,'www_youpuppy','youpuppy_users','youpuppy_users',NULL);
    CALL flexviews.enable(@mvid);
    - i executed all of this procedure into mysql and i created a mv with data but i can't refresh it

    can you help me please?

    Thanks

    Best Regards

    Stefano Dolce

  35. Justin Swanhart says:

    [root@morpheus consumer]# Restarting FlexCDC!
    Restarting FlexCDC!
    Restarting FlexCDC!

    Look at flexcdc.err to see why FlexCDC is not working. It needs to run in the background. If you see “Restarting FlexCDC!” then the tool crashed and restarted and if that keeps happening that means the tool is not processing any changes, just repeating errors again and again.

  36. Hi Justin,

    Thanks for reply.

    I saw flexcdc.err and the error was inside the consumer.ini about mysqlbinlog.

    Now i changed the mysqlbinlog from mysqlbinlog=/usr/local/mysql/bin/mysqlbinlog to mysqlbinlog=/usr/bin/mysqlbinlog and the sh consumer_safe.sh –ini=consumer.ini & work

    I tried to reintall flexview and setup the new mv but i find new error:
    mysql> call flexviews.refresh(flexviews.get_id(‘www_youpuppy’,’youpuppy_users’),’BOTH’,NULL);
    ERROR 1644 (45000): XYZ UNSUPPORTED REFRESH METHOD

    Into flexviews.mvlog_38573c6756a3f2c7f854012cc180dcd7 there is a modify of my record dml_type 1 and -1 but into mv_youpuppyusers the data is old

    Can you help me?

    Thanks

    Best Regards

    Stefano Dolce

  37. Hi Justin,

    Sorry i sleeping :-) in Italy are 2.40 a.m.

    It works, i writed wrong the mv name into refresh method

    mysql> call flexviews.refresh(flexviews.get_id(‘www_youpuppy’,’mv_youpuppyusers’),’BOTH’,NULL);
    Restarting FlexCDC!
    Query OK, 0 rows affected (7.05 sec)

    i don’t understand the “Restarting FlexCDC!” after call refresh method but it work

    Now in my mv_youpuppyusers i have refreshed data

    Thanks a lot

    Best Regards :-)

    Stefano Dolce

  38. Zhoutall says:

    It seems that “select * from flexviews.test_demo” don’t work now.
    “_” has been changed to “md5()” + “md5()”

  39. Zhoutall says:

    It seems that “select * from flexviews.test_demo” don’t work now.
    “schema_table” has been changed to “md5(schema)” + “md5(table)”

  40. Ruixing says:

    Does flexviews must work in linux? If i use winodows,how can i config consumer.ini?Thanks

Speak Your Mind

*