SharePoint to MySQL or MariaDB for a Grafana defects board

Which of Ashcroft Build Group’s five project sites has the most open defects right now? The compliance team can only answer by exporting the Snagging and Defects list, because the project on each defect is a SharePoint lookup and the list view has no way to group by it. This walkthrough replicates Projects, Snagging and Defects and Subcontractor Compliance into MySQL with SPSync and puts a Grafana board on top. MariaDB works the same way.

Grafana pointed straight at the list fares no better: refresh crawls, every lookup is an id, and once a list passes 5,000 items a query that filters or sorts on a non-indexed column can fail. Power Automate can copy rows across until the Project lookup or the multi-value Certifications column changes. A loader written in-house needs its schema edited each time a column is added.

A copy in MySQL, kept current

SPSync reads the three lists in full once and then follows them with delta queries, writing the changes to MySQL or MariaDB tables on a schedule. Grafana and the compliance app never call SharePoint. The schema comes from the lists: lookups and people become key columns, multi-value columns become child tables. The SharePoint to MySQL page covers the destination in detail, and MariaDB has a page of its own at SharePoint to MariaDB.

Connecting SharePoint and the MySQL server

Install the desktop app, or SPSync in your Azure for a tenant-wide setup, and sign in with the compliance team’s own account. Sign-in is delegated: SPSync sees the three lists because that account does. Before an admin grants Sites.Selected, the permissions wizard spells out what it allows, and SPSync never writes to SharePoint. On the command line, sign-in uses a device code and keeps the token in the OS keychain, so a scheduled job holds no password.

Next, tick the lists, name the replication and choose MySQL. The connection string carries a host, port and database name with a MySQL user and password; MariaDB accepts the same string through the same driver. The user needs rights to create tables in that database. Anything that speaks the MySQL protocol works, self-hosted, in a container or as a managed service, though a managed service may want its CA certificate in the connection string. The Azure Database for MySQL page has the Azure detail. Switch on reporting views to get a view per list without removed rows and with people shown by name.

Defects, projects and certifications as tables

Each row keeps its SharePoint item id as the key. The Project lookup on a defect becomes project_id, pointing at sp_projects. Certifications, a multi-value choice on Subcontractor Compliance, becomes sp_subcontractor_compliance__certifications, one row per certification per subcontractor. Defect assignees and subcontractor contacts both resolve to sp_users, so a person joins the same way wherever they appear.

A few type choices are specific to MySQL and MariaDB. A SharePoint GUID column becomes a VARCHAR(36) holding a plain UUID string on both engines. The two longest text sizes are stored as TEXT, which keeps a row with several long columns under MySQL’s per-row length limit; shorter text stays a bounded VARCHAR, so it can be indexed or made unique. A table or column name over MySQL’s 64-character limit is shortened and given a short hash suffix.

When the site team adds a Cost Impact column, the next run adds it to sp_snagging_and_defects. A column they delete stays in MySQL, and the Grafana panel built on it carries on. A change of type adds a second column and flags the first, so old values are never reinterpreted. Key columns carry no foreign key constraints, which means load order cannot fail a run.

Running the first sync

The first run reads everything: 5 projects, 260 defects and 40 subcontractor compliance rows. Each table commits in one transaction once its read is done, so a panel sees the old data or the new. If Graph signals throttling, SPSync backs off, and the rest of the tenant’s calls carry on. The events screen splits the totals by list. From then on, each run reads only what changed.

The question at the top of this post is now one query: sp_snagging_and_defects.project_id joins to sp_projects.sp_item_id, grouped by project title.

A Grafana board and an insurance check

Add Grafana’s MySQL data source and build a defects-by-project panel on sp_snagging_and_defects, grouped by site and severity. It refreshes on the same five-minute cycle as the replication, which retires the spreadsheet rebuilt before each site meeting. A small PHP or Node app reads sp_subcontractor_compliance and flags every subcontractor whose insurance expires in the next thirty days. More on dashboards is in KPI dashboards from SharePoint.

How often the data refreshes

With a five-minute interval, each run reads the changes since the last one, and MySQL is never more than a few minutes behind. A full reconcile, every seven days by default, re-checks every item; run one by hand whenever you want to confirm the two sides match, or after an outage such as your own server failing or a Microsoft 365 incident. Both the desktop app and SPSync in your Azure have a schedule picker. From the command line, spsync run does a single run, and cron, a scheduled task or a CI pipeline decides how often.

Closed, edited and deleted defects

An edited defect is updated by the next delta run. A deleted one is soft-deleted: the row stays, flagged, and the reporting view hides it. A subcontractor record removed by a compliance officer disappears from the view while the raw table keeps it for an auditor.

The destinations page lists every target SPSync can write to, from relational databases to search and vector stores. One replication writes to one of them, and the same lists can have as many replications as you need. If the destination you want is not there, tell us. To go through your own lists, or arrange a trial in your tenant, book a call.