Skip to content

Instantly share code, notes, and snippets.

@greenlion
Created October 21, 2020 23:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save greenlion/5970c70aaa41829a8dae4d31dd5be0c7 to your computer and use it in GitHub Desktop.
Save greenlion/5970c70aaa41829a8dae4d31dd5be0c7 to your computer and use it in GitHub Desktop.
I had to make a few minor changes to FV and work around some CONNECT issues, but it does work.
The following queries are on my "slave" which does not actually replicate any data from the master.
The 'test.t1' table is a CONNECT table so all the data is actually read from the master.
The "mvlog" (changelog) tables ARE written to the "slave".
-- Note the 'test.t1' table is empty so the COUNT (cnt) is 0 (t1_v is equivalent to select count(*) as cnt from t1):
MariaDB [test]> select * from t1_v;
+----------+-----+
| mview$pk | cnt |
+----------+-----+
| 1 | 0 |
+----------+-----+
1 row in set (0.000 sec)
-- I INSERTED ONE ROW (with value 3) INTO THE MASTER AND THEN REFRESHED THE VIEW
MariaDB [test]> call flexviews.refresh(1, 'BOTH',NULL);
Query OK, 48 rows affected (0.033 sec)
-- HOORAH - COUNT WENT TO 1
MariaDB [test]> select * from t1_v;
+----------+-----+
| mview$pk | cnt |
+----------+-----+
| 1 | 1 |
+----------+-----+
1 row in set (0.000 sec)
-- I DELETED THE ROW ON THE MASTER SO THE TABLE IS NOW EMPTY AND COUNT SHOULD GO TO ZERO!
MariaDB [test]> select * from t1;
Empty set (0.002 sec)
-- HERE YOU CAN SEE THE DELETE AS THE LAST RECORD (and the insertion prior to it that sent the count to 1):
MariaDB [test]> select * from flexviews.mvlog_7a52a7837df7b90fa91d3c0c3c985048;
+----------+--------+--------------+--------+------+
| dml_type | uow_id | fv$server_id | fv$gsn | c1 |
+----------+--------+--------------+--------+------+
| 1 | 21 | 33 | 20 | 3 |
| -1 | 23 | 33 | 22 | 3 |
+----------+--------+--------------+--------+------+
10 rows in set (0.000 sec)
MariaDB [test]> call flexviews.refresh(1, 'BOTH',NULL);
Query OK, 50 rows affected (0.050 sec)
-- WOOHOO: COUNT (alias cnt) IS NOW ZERO (which reflects the empty set above)
MariaDB [test]> select * from t1_v;
+----------+-----+
| mview$pk | cnt |
+----------+-----+
| 3 | 0 |
+----------+-----+
1 row in set (0.000 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment