[Bug][Sonarqube] SonarQube Plugin Data Collection Fails Due to Field Length Exceeding Limit

[narrowizard]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

When collecting data using the SonarQube plugin, an error occurred indicating that the id field in the cq_file_metrics table (which belongs to the domain layer) exceeded its current length limit of 255 characters. This prevented successful data ingestion.

Upon investigation and review of both the SonarQube tool layer and the DevLake CQ (Code Quality) domain layer models, we identified several fields that might frequently exceed their current length limits based on real-world SonarQube data. These fields and their proposed increased lengths are:

• cq_projects.name: Current 255, proposed 2000
• cq_issues.component: Current 255, proposed TEXT (or equivalent long text type)
• cq_file_metrics.file_name: Current 255, proposed 2000
• cq_file_metrics.id: Current 255, proposed 3000
• _tool_sonarqube_file_metrics.file_name: Current 255, proposed 2000
• _tool_sonarqube_file_metrics.file_metrics_key: Current 500, proposed 3000

The specific error encountered points to cq_file_metrics.id as the immediate blocker, but the others are likely to cause similar issues with varied SonarQube project data.

What do you expect to happen

We expect the SonarQube plugin to successfully collect and ingest all relevant data without encountering field length constraint errors. The DevLake database schema should be robust enough to accommodate the typical lengths of data originating from SonarQube, ensuring seamless data synchronization and analysis.

How to reproduce

1. Set up an Apache DevLake instance.
2. Configure the SonarQube plugin by adding a SonarQube connection.
3. Ensure your SonarQube instance contains projects with components, file names, or metrics keys that generate IDs or names longer than the currently defined field lengths in DevLake's schema (e.g., a file path or an internal metric key that exceeds 255 characters for cq_file_metrics.id or file_name).
4. Create and run a DevLake blueprint/pipeline to collect data from the configured SonarQube project.
5. Observe the pipeline execution logs; an error related to Data too long for column 'id' at row 1 (or similar, depending on the database backend) should appear for the cq_file_metrics table during the data transformation or saving phase.

Anything else

No response

Version

main

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[narrowizard]

Due to MySQL's InnoDB limitation where index keys can be a maximum of 3072 bytes, the cq_file_metrics.id field, when using the utf8mb4 character set, can only be set to VARCHAR(767) at its longest. To ensure SonarQube data can be collected correctly without hitting this field length limit, we may need to modify the format or handling of cq_file_metrics.id. This particular aspect of the issue will be tracked in a separate follow-up issue.

[narrowizard]

Due to MySQL's InnoDB limitation where index keys can be a maximum of 3072 bytes, the cq_file_metrics.id field, when using the utf8mb4 character set, can only be set to VARCHAR(767) at its longest. To ensure SonarQube data can be collected correctly without hitting this field length limit, we may need to modify the format or handling of cq_file_metrics.id. This particular aspect of the issue will be tracked in a separate follow-up issue.

Regarding the index key length limitation, we plan to address this for cq_file_metrics.id (which is derived from _tool_sonarqube_file_metrics.file_metrics_key) by hashing the file path portion of _tool_sonarqube_file_metrics.file_metrics_key to restrict its length.

This approach will necessitate considering data migration. The simplest and most straightforward method would be to clear the existing data from both the cq_file_metrics and _tool_sonarqube_file_metrics tables. This way, subsequent data collection will use the new ID format, avoiding any conflicts between old and new data representations.

[narrowizard]

After further consideration regarding the field length limitations, we've decided to keep the lengths of cq_file_metrics.id and _tool_sonarqube_file_metrics.file_metrics_key unchanged. Instead, we will focus on modifying the format of the file_metrics_key content itself (which contributes to cq_file_metrics.id) to ensure it fits within the existing column constraints. This approach aims to address the data overflow issue without requiring a schema change to increase column lengths.