dolthub / dolthub/dolt

Block invalid trigger syntax or fix doltdump parsing

Open
#11,684 0 comments 0 reactions 0 assignees View on GitHub
bug cli dumps import
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 6h
Merged PRs (30d)
101

Description

This is invalid syntax for creating a trigger
MySQL:
```sql
mysql> create trigger trig before insert on t for each row begin select 1; end;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 1
```

Dolt:
```sql
tmp/main> create table t (i int);
Query OK, 0 rows affected (0.01 sec)
tmp/main*> create trigger trig before insert on t for each row begin select 1; end;
Query OK, 0 rows affected (0.01 sec)
```

This is relevant because it allows us to create doltdumps that can't be imported.
```shell
james@Mac tmp % dolt dump --file-name bad_trigger.sql
Successfully exported data.

james@Mac tmp % cat bad_trigger.sql
CREATE DATABASE IF NOT EXISTS `tmp`; USE `tmp`;
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`i` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
create trigger trig before insert on t for each row begin select 1; end;

james@Mac tmp % rm -rf .dolt
james@Mac tmp % dolt init
Successfully initialized dolt data repository.
james@Mac tmp % dolt sql < bad_trigger.sql
error on line 8 for query create trigger trig before insert on t for each row begin select 1
Error parsing SQL:
syntax error at position 67 near '1'
create trigger trig before insert on t for each row begin select 1

```

Contributor guide

No contributing guide indexed for this repository

Research direction

Reproduce the trigger statement in Dolt, then export it with `dolt dump --file-name bad_trigger.sql` and import the dump with `dolt sql < bad_trigger.sql`. Compare the accepted syntax with MySQL's rejection and inspect the paths handling `CREATE TRIGGER` and dump output. Done means either the invalid trigger is rejected or the generated dump imports successfully.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.