dosubot[bot] commented on issue #7752:
URL:
https://github.com/apache/incubator-devlake/issues/7752#issuecomment-2231912075
<!-- Greeting -->
Hey @SiliconSatchel! I'm here to help you with your bug 🐞 Let's squash it
together!
<!-- Answer -->
The issue with the "file dimension" metrics not displaying properly in the
"Component and File-Level Metrics" dashboard in DevLake v1.0.1-beta2 is due to
an SQL query error related to the `file_path` column and character set issue.
The error message indicates a conflict between the `binary` and
`utf8mb4_unicode_ci` character sets.
To resolve this, you need to ensure that the `file_path` column is properly
encoded and modify the SQL queries to handle character set issues. Here are the
corrected SQL queries for the affected panels:
For the "files with maximum number of authors" panel:
```sql
SELECT CONVERT(file_path USING utf8) AS file_path,
COUNT(DISTINCT author_name) AS cnt
FROM commits
JOIN commit_files
JOIN repo_commits rc
ON commit_files.commit_sha = rc.commit_sha
AND commit_files.commit_sha = commits.sha
WHERE repo_id IN (${repo_id})
AND $__timeFilter(commits.authored_date)
AND file_path REGEXP '(${selected_path:regex})'
GROUP BY file_path
ORDER BY cnt DESC
LIMIT 10;
```
For the "files with most modifications" panel:
```sql
SELECT CONVERT(file_path USING utf8) AS file_path,
COUNT(DISTINCT sha) AS modified_num
FROM commits
JOIN commit_files
JOIN repo_commits rc
ON commit_files.commit_sha = rc.commit_sha
AND sha = commit_files.commit_sha
AND $__timeFilter(commits.authored_date)
WHERE repo_id IN (${repo_id})
AND file_path REGEXP '(${selected_path:regex})'
AND $__timeFilter(commits.authored_date)
GROUP BY file_path
ORDER BY modified_num DESC
LIMIT 15;
```
By converting the `file_path` column to use the `utf8` character set, the
character set issue should be resolved, and the metrics should display properly
in the dashboard
<sup>[[1]](https://github.com/apache/incubator-devlake/blob/main/grafana/dashboards/ComponentAndFileLevelMetrics.json)</sup>.
Additionally, ensure that the `commit_files` table contains the `file_path`
column. The schema of the `commit_files` table includes the `file_path` column,
which is defined as a `varchar(255)` field
<sup>[[2]](https://github.com/apache/incubator-devlake-website/blob/main/versioned_docs/version-v0.15/DataModels/DevLakeDomainLayerSchema.md)</sup>.
If you continue to experience issues, verify that the MySQL database used by
DevLake is configured with the correct character set and collation settings.
The default settings are `utf8mb4` for the character set and `utf8mb4_bin` for
the collation
<sup>[[3]](https://github.com/apache/incubator-devlake/blob/main/devops/releases/lake-v0.15.0/docker-compose.yml)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscr...@devlake.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
