Bug Report: MariaDB support broken in vdiff2

[mikehamrick]

Overview of the Issue

When attempting to perform an import from an importkeyspace of 10.3.36-MariaDB-log we see repeated errors in the Vitess logs in the form of:

Error retrying vdiffs: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>>'$.core_options.auto_retry' = 'true'' at line 1 (errno 1064) (sqlstate 42000) during query: select * from _vt.vdiff where state = 'error' and options->>'$.core_options.auto_retry' = 'true'

It appears that the column->>path JSON operator used in the SQL here is not supported by MariaDB.

Reproduction Steps

I don't have a complete list of reproduction steps here sadly. It's from an import of an external MariaDB keyspace. Hopefully the error message alone will be enough to find the issue!

Binary Version

2023-02-07.e775d05

Operating System and Environment details

N/A

Log Fragments

No response

[mattlord]

We do not support MariaDB in Vitess v16 (where VDiff2 is GA and the default) and later, BUT this is a pretty easy fix — as col1->>'$.field1' is equivalent to JSON_UNQUOTE(JSON_EXTRACT(col1, '$.field1')) — and doing so allows us to fully support VDiff2 with MariaDB imports, even when running the diffs against the reverse workflows.

[deepthi]

@mattlord to me this looks like

• there's an external primary vttablet against MariaDB
• because it is a primary we create all the engines (including VDiff)
• that engine periodically checks for failed VDiff attempts
• the query for fetching those fails

So even before anyone tries to actually run a VDiff this will continuously error.

[mattlord]

@deepthi correct, you would see an error log message about this every 30 seconds on that tablet. It wouldn't cause anything to fail, but you would see that in the logs. This could, however, cause VDiffs to fail permanently, after the first error for any reason, when doing a VDiff on the _reverse workflow after cutting over traffic (perhaps to confirm both sides are in sync before completing the MoveTables).

[mattlord]

Fixed by: #12420