Repository: sqlfluff/sqlfluff
Issue category: Bug Report

Applying rule CV05 to 'test.sql' threw an Exception: TypeGuard: Segment must exist
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

Linting a MySQL file generated by MySQLWorkbench with `sqlfluff` reported the following:

```console
❯ docker run --rm -v "$PWD:/sql:ro" sqlfluff/sqlfluff lint --dialect mysql test.sql
CRITICAL   [CV05] Applying rule CV05 to 'test.sql' threw an Exception: TypeGuard: Segment must exist
Traceback (most recent call last):
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/core/rules/base.py", line 507, in crawl
    res = self._eval(context=context)
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/rules/convention/CV05.py", line 88, in _eval
    assert sub_seg, "TypeGuard: Segment must exist"
AssertionError: TypeGuard: Segment must exist
== [test.sql] FAIL
L:   7 | P:  23 | CV05 | Unexpected exception: TypeGuard: Segment must
                       | exist;
Could you open an issue at
                       | https://github.com/sqlfluff/sqlfluff/issues ?
You can
                       | ignore this exception for now, by adding '-- noqa: CV05'
                       | at the end
of line 7
 [convention.is_null]
L:   7 | P:  23 | LT01 | Expected single whitespace between variable and raw
                       | comparison operator '='. [layout.spacing]
L:   7 | P:  24 |  PRS | Line 7, Position 24: Found unparsable section:
                       | '@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;\nSET @O...'
L:  10 | P:   1 | LT12 | Files must end with a single trailing newline.
                       | [layout.end_of_file]
WARNING: Parsing errors found and dialect is set to 'mysql'. Have you configured your dialect correctly?
All Finished!
```

### Expected Behaviour

I expect the `sqlfluff` MySQL dialect to be able to parse files generated by MySQL Workbench.

### Observed Behaviour

See the console output of "What Happened", in particular:

```console
CRITICAL   [CV05] Applying rule CV05 to 'test.sql' threw an Exception: TypeGuard: Segment must exist
```

and

```console
Could you open an issue at
                       | https://github.com/sqlfluff/sqlfluff/issues ?
```

### How to reproduce

To reproduce, use a `test.sql` file with the following content:

```sql
-- Database version 3.8.0
-- MySQL Script generated by MySQL Workbench
-- Thu 23 May 2024 11:25:21 AM UTC
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
```

and issue the following command:

```console
docker run --rm -v "$PWD:/sql:ro" sqlfluff/sqlfluff lint --dialect mysql test.sql
```

### Dialect

As specified in the previous command, the MySQL dialect is used.

### Version

```console
❯ docker run --rm -v "$PWD:/sql:ro" sqlfluff/sqlfluff --version
sqlfluff, version 3.0.7
```

### Configuration

Basic configuration, no additional configuration is needed.

### Are you willing to work on and submit a PR to address the issue?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)



Functions to edit:
src/sqlfluff/dialects/dialect_mysql.py:SetAssignmentStatementSegment

Patch:
diff --git a/src/sqlfluff/dialects/dialect_mysql.py b/src/sqlfluff/dialects/dialect_mysql.py
index a85db1a9f5e..a3747ca051c 100644
--- a/src/sqlfluff/dialects/dialect_mysql.py
+++ b/src/sqlfluff/dialects/dialect_mysql.py
@@ -318,7 +318,7 @@
         type="at_sign_literal",
     ),
     SystemVariableSegment=RegexParser(
-        r"@@(session|global)\.[A-Za-z0-9_]+",
+        r"@@((session|global)\.)?[A-Za-z0-9_]+",
         CodeSegment,
         type="system_variable",
     ),
@@ -1763,6 +1763,7 @@ class SetAssignmentStatementSegment(BaseSegment):
                     Ref("QuotedLiteralSegment"),
                     Ref("DoubleQuotedLiteralSegment"),
                     Ref("SessionVariableNameSegment"),
+                    Ref("SystemVariableSegment"),
                     # Match boolean keywords before local variables.
                     Ref("BooleanDynamicSystemVariablesGrammar"),
                     Ref("LocalVariableNameSegment"),

Test patch: diff --git a/test/fixtures/dialects/mysql/set_system_variable.sql b/test/fixtures/dialects/mysql/set_system_variable.sql
new file mode 100644
index 00000000000..30c74d8e082
--- /dev/null
+++ b/test/fixtures/dialects/mysql/set_system_variable.sql
@@ -0,0 +1,3 @@
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
diff --git a/test/fixtures/dialects/mysql/set_system_variable.yml b/test/fixtures/dialects/mysql/set_system_variable.yml
new file mode 100644
index 00000000000..768b07afb21
--- /dev/null
+++ b/test/fixtures/dialects/mysql/set_system_variable.yml
@@ -0,0 +1,46 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 2664ea25dc8f7322ca49054b9e069ad196504e1fb40df67d452f36b89df210d7
+file:
+- statement:
+    set_statement:
+    - keyword: SET
+    - variable: '@OLD_UNIQUE_CHECKS'
+    - comparison_operator:
+        raw_comparison_operator: '='
+    - system_variable: '@@UNIQUE_CHECKS'
+    - comma: ','
+    - variable: UNIQUE_CHECKS
+    - comparison_operator:
+        raw_comparison_operator: '='
+    - variable: '0'
+- statement_terminator: ;
+- statement:
+    set_statement:
+    - keyword: SET
+    - variable: '@OLD_FOREIGN_KEY_CHECKS'
+    - comparison_operator:
+        raw_comparison_operator: '='
+    - system_variable: '@@FOREIGN_KEY_CHECKS'
+    - comma: ','
+    - variable: FOREIGN_KEY_CHECKS
+    - comparison_operator:
+        raw_comparison_operator: '='
+    - variable: '0'
+- statement_terminator: ;
+- statement:
+    set_statement:
+    - keyword: SET
+    - variable: '@OLD_SQL_MODE'
+    - comparison_operator:
+        raw_comparison_operator: '='
+    - system_variable: '@@SQL_MODE'
+    - comma: ','
+    - variable: SQL_MODE
+    - comparison_operator:
+        raw_comparison_operator: '='
+    - quoted_literal: "'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"
+- statement_terminator: ;
diff --git a/test/fixtures/rules/std_rule_cases/CV05.yml b/test/fixtures/rules/std_rule_cases/CV05.yml
index 980c6f8a82a..f4a63ed541e 100644
--- a/test/fixtures/rules/std_rule_cases/CV05.yml
+++ b/test/fixtures/rules/std_rule_cases/CV05.yml
@@ -112,3 +112,10 @@ test_exclude_constraint:
   configs:
     core:
       dialect: postgres
+
+test_mysql_system_variable:
+  pass_str: |
+    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+  configs:
+    core:
+      dialect: mysql