物化视图，不更新数据，还影响了直接查询也不更新数据

[zymeli]

【简述】物化视图，数据源为远程mysql库里的视图，物化视图不更新数据，手动和异步都不更新，还影响了直接查询也不更新数据。附后详细说明。
【背景】做过哪些操作？开和关dataCache功能也一样的问题，附后详细说明，
【业务影响】
【是否存算分离】存算一体，用docker和手动部署，都是这样问题。
【StarRocks版本】3.2.3和3.3.0
【集群规模】1fe+1be
【机器信息】8C/16G/万兆
【联系方式】社区回复或者邮箱，谢谢

为了方便理解，以下关键字中，可能以中文代替相关数据信息。

一、我的远程数据库mysql，里面有一张msql视图，视图极简单：
select 字段1,字段2,字段8 from 物理库.物理表

二、我在StarRocks中，用jdbc，建了catalog为：jdbc_catalog

三、用 select 字段1,字段2,字段8 from jdbc_catalog.物理库.物理表，有数据，
而且数据随着 物理表 的更新，查询结果也有更新。正常。

四、创建物化视图，Create 物化视图 AS select 字段1,字段2 from (
select 字段1,字段2 from jdbc_catalog.物理库.物理表
) as t 成功，注意这里少个 “字段8”

五、解释一下，这里为什么要 select … from ( select … from ) as t ，
是因为要拼接多个子查询，这个不影响以下错误的呈现。

六、现在错误现象来了：在mysql物理表数据有更新删除插入等变化时：
1、手动更新物化视图数据，或自动异步更新数据，更新任务会成功，更新日志也显示更新成功，
2、但 select * from 物化视图 的查询结果，却没有更新，即没有预期的与mysql物理表数据有更新删除插入等变化。
3、而且如上“三”中，用 select 字段1,字段2 from jdbc_catalog.物理库.物理表，查询结果，一样的错误，没有更新。
4、但如上“三”中，用 select 字段1,字段2,字段8 from jdbc_catalog.物理库.物理表，查询结果，却更新了，注意这里多加了个 字段8 。
5、这里3和4，只差了一个 字段8，这个 字段8 在 “四” 创建物化视图时，没有此字段。
6、总结这里，就有了2个非预期的错误：物化视图无法同步数据，和，直接查询源也无法同步数据

七、我推断，是不是 “创建物化视图”时，缓存了schema，之后在 “六.3” 时，发现查询是缓存了的，直接取了本地物化视图数据？而在 “六.4” 时，又取了远程物理数据？

八、重建msql视图，数据会同步一次，没意义。重建"物化视图"，数据会同步一次，没意义。试了开关queryCache和dataCache，问题依旧。

新人第一问，麻烦大家了，谢谢。

[zhanghuaibei]

官方文档并没有说jdbc catalog支持创建物化视图

[zymeli]

大家好，有空的可以帮我测试一下，你的mysql+starRocks有类似问题没，谢谢。
以下是我的相关测试步骤。

一、1fe+1be上的 jdbc_catalog
StarRocks > show catalogs;
±------------------------------±---------±-----------------------------------------------------------------+
| Catalog | Type | Comment |
±------------------------------±---------±-----------------------------------------------------------------+
| default_catalog | Internal | An internal catalog contains this cluster’s self-managed tables. |
| jdbc_192_168_5_18_3306_mysql8 | Jdbc | NULL |
±------------------------------±---------±-----------------------------------------------------------------+
2 rows in set (0.00 sec)

StarRocks > show create catalog jdbc_192_168_5_18_3306_mysql8;
±------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Catalog | Create Catalog |
±------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| jdbc_192_168_5_18_3306_mysql8 | CREATE EXTERNAL CATALOG jdbc_192_168_5_18_3306_mysql8
PROPERTIES (“driver_class” = “com.mysql.cj.jdbc.Driver”,
“checksum” = “ad7ef879830dbf17c7a73bf03b34f6fc”,
“driver_url” = “https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.30/mysql-connector-java-8.0.30.jar”,
“type” = “jdbc”,
“user” = “root”,
“jdbc_uri” = “jdbc:mysql://192.168.5.18:3306”
) |
±------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

StarRocks >

二、mysql上的物理表和视图，最简单的视图
StarRocks > SELECT * FROM jdbc_192_168_5_18_3306_mysql8.test.some_info;
±------±-------±-----+
| sm_id | name | age |
±------±-------±-----+
| 1 | 张三 | 20 |
| 2 | 李四 | 30 |
±------±-------±-----+
2 rows in set (0.28 sec)

StarRocks > SELECT * FROM jdbc_192_168_5_18_3306_mysql8.test.view_some_info;
±------±-------±-----+
| sm_id | name | age |
±------±-------±-----+
| 1 | 张三 | 20 |
| 2 | 李四 | 30 |
±------±-------±-----+
2 rows in set (0.02 sec)

StarRocks >

三、在 default_catalog 里创建物化视图

StarRocks > set catalog default_catalog;
Query OK, 0 rows affected (0.00 sec)

StarRocks > create database for_test;
Query OK, 0 rows affected (0.01 sec)

StarRocks > use for_test;
Database changed
StarRocks > CREATE MATERIALIZED VIEW default_catalog.for_test.view_test REFRESH ASYNC EVERY(INTERVAL 2 MINUTE)
-> AS
-> SELECT sm_id, name FROM
-> ( SELECT sm_id, name FROM jdbc_192_168_5_18_3306_mysql8.test.view_some_info ) AS t;
Query OK, 0 rows affected (0.03 sec)

StarRocks >
StarRocks > show tables;
±-------------------+
| Tables_in_for_test |
±-------------------+
| view_test |
±-------------------+
1 row in set (0.00 sec)

StarRocks >
StarRocks > SELECT * FROM default_catalog.for_test.view_test;
±------±-------+
| sm_id | name |
±------±-------+
| 1 | 张三 |
| 2 | 李四 |
±------±-------+
2 rows in set (0.01 sec)

StarRocks >

四、测试出问题1，增加mysql物理表的数据行，再等2分钟自动同步后，在物化视图里的数据没有增加

StarRocks > SELECT * FROM jdbc_192_168_5_18_3306_mysql8.test.view_some_info;
±------±-------±-----+
| sm_id | name | age |
±------±-------±-----+
| 1 | 张三 | 20 |
| 2 | 李四 | 30 |
| 3 | 王五 | 40 |
±------±-------±-----+
3 rows in set (0.05 sec)

StarRocks >
StarRocks > SELECT * FROM view_test;
±------±-------+
| sm_id | name |
±------±-------+
| 2 | 李四 |
| 1 | 张三 |
±------±-------+
2 rows in set (0.00 sec)

StarRocks >
StarRocks > SHOW MATERIALIZED VIEWS FROM for_test WHERE NAME = ‘view_test’ \G
*************************** 1. row ***************************
id: 10526
database_name: for_test
name: view_test
refresh_type: ASYNC
is_active: true
inactive_reason:
partition_type: UNPARTITIONED
task_id: 10533
task_name: mv-10526
last_refresh_start_time: 2024-07-19 08:55:41
last_refresh_finished_time: 2024-07-19 08:55:41
last_refresh_duration: 0.033
last_refresh_state: SUCCESS
last_refresh_force_refresh: false
last_refresh_start_partition: NULL
last_refresh_end_partition: NULL
last_refresh_base_refresh_partitions: {}
last_refresh_mv_refresh_partitions: []
last_refresh_error_code: 0
last_refresh_error_message:
rows: 2
text: CREATE MATERIALIZED VIEW view_test (sm_id, name)
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 2 MINUTE)
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “1”,
“storage_medium” = “HDD”
)
AS SELECT t.sm_id, t.name
FROM (SELECT view_some_info.sm_id, view_some_info.name
FROM jdbc_192_168_5_18_3306_mysql8.test.view_some_info) t;
extra_message: {“queryIds”:[“ad69864a-45ac-11ef-9000-bc2411a2a73b”],“isManual”:false,“isSync”:false,“isReplay”:false,“priority”:0,“lastTaskRunState”:“SUCCESS”}
query_rewrite_status: VALID
1 row in set (0.02 sec)

StarRocks >
StarRocks > SELECT * FROM view_test;
±------±-------+
| sm_id | name |
±------±-------+
| 1 | 张三 |
| 2 | 李四 |
±------±-------+
2 rows in set (0.01 sec)

StarRocks >

五，在上面 SHOW MATERIALIZED VIEWS ... 即使等几个2分钟，它都有显示 ASYNC=SUCCESS，但是物化视图的查询数据，依旧没有预期的与mysql同步。

六、测试出问题2，居然此时，查询mysql的视图数据，也没有更新为新的数据！！
StarRocks > SELECT sm_id,name FROM jdbc_192_168_5_18_3306_mysql8.test.view_some_info;
±------±-------+
| sm_id | name |
±------±-------+
| 2 | 李四 |
| 1 | 张三 |
±------±-------+
2 rows in set (0.03 sec)

StarRocks >

七，但此时查询mysql视图如果多1个字段，却有更新为新的数据！！
StarRocks > SELECT sm_id,name,age FROM jdbc_192_168_5_18_3306_mysql8.test.view_some_info;
±------±-------±-----+
| sm_id | name | age |
±------±-------±-----+
| 1 | 张三 | 20 |
| 2 | 李四 | 30 |
| 3 | 王五 | 40 |
±------±-------±-----+
3 rows in set (0.04 sec)

StarRocks >

以上，麻烦有空的兄弟帮我，在你的系统里测试一下，有类似问题没，谢谢。

[zymeli]

@管理员 管理员
通过监视 mysql 查询日志 ，这个应该是starRocks应该完善的一个地方。
出现以上问题的关键点，我是用 mysql 的视图作为源数据，在starRocks里建的物化视图。
而starRocks在自动或手动同步数据时，判断的依据是：
SELECT TABLE_NAME AS NAME, IF(UPDATE_TIME IS NULL, CREATE_TIME, UPDATE_TIME) AS MODIFIED_TIME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = ‘test’ AND TABLE_NAME = ‘view_some_info’
注意这里，INFORMATION_SCHEMA.PARTITIONS 里， TABLE_NAME = ‘view_some_info’
这里 TABLE_NAME 是mysql视图，并不是 mysql物理表，所以得出的 MODIFIED_TIME 是不会变的。
建议，通过 EXPLAIN mysql视图 的创建语句，找到 mysql物理表 ，再测试出 正确的 MODIFIED_TIME ，
谢谢。