Recording a Row's Last Modification Time

[subhra78]

Hi I am trying to autp capture the last modification time of a row but not sure how, tried to create the trigger but I get error when try to run this the error is - query error: must be in workspace context to run mutate queries

This is the query

CREATE TRIGGER `table_update_trigger`
BEFORE UPDATE ON `table_name`
FOR EACH ROW
SET NEW.updated_at = CURRENT_TIMESTAMP();

[timsehn]

Interesting. This should work. We'll dig in today.

[timsehn]

Not a simple repro:

$ dolt init --fun
Successfully initialized dolt data repository.
$ dolt sql -q "create table t (c1 int primary key, t1 timestamp)";
$ dolt sql -q "CREATE TRIGGER trigger1              
BEFORE UPDATE ON t
FOR EACH ROW
SET NEW.t1 = CURRENT_TIMESTAMP();"
$ dolt sql -q "insert into t(c1) values (0)"
Query OK, 1 row affected (0.00 sec)
$ dolt sql -q "select * from t"
+----+------+
| c1 | t1   |
+----+------+
| 0  | NULL |
+----+------+

$ dolt sql -q "update t set c1=1 where c1=0"
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
$ dolt sql -q "select * from t"             
+----+---------------------+
| c1 | t1                  |
+----+---------------------+
| 1  | 2023-05-10 16:18:01 |
+----+---------------------+

Will try server context.

[timsehn]

This also works in the server context in a simple repro:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess 

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test_write_trigger;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t;
+------+---------------------+
| c1   | t1                  |
+------+---------------------+
|    1 | 2023-05-10 16:18:01 |
+------+---------------------+
1 row in set (0.01 sec)

mysql> update t set c1=2 where c1=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+---------------------+
| c1   | t1                  |
+------+---------------------+
|    2 | 2023-05-10 16:20:33 |
+------+---------------------+
1 row in set (0.00 sec)

mysql>

Can you provide a bit more information?

[timsehn]

The plot thickens. I can't find that error anywhere in Dolt or go-mysql-server:

$ pwd
/Users/timsehn/dolthub/git/dolt
$ grep -r "workspace context" .
$ cd ../go-mysql-server 
$ grep -r "workspace context" .
$

Are you using the latest version of Dolt?

[timsehn]

This error seems like it might be happening on DoltHub. A workspace is a DoltHub concept. @liuliu-dev or @tbantle22 do update triggers work on DoltHub?

[timsehn]

Was able to reproduce here:

https://www.dolthub.com/repositories/timsehn/test_write_triggers

Triggers don't seem to play well with DoltHub workspaces. I'm sending this bug to the dolthub-issues repository.

[tbantle22]

Deploying a fix for this now. It was an issue with the sql parser we use on the web

[tbantle22]

This should be fixed now. Thanks for the issue

[subhra78]

Thanks , it worked. I have noticed when the dataset is created and I try to modify the data using the spreadsheet editor on dolthub web browser platform, it doesn't capture the updated_time but same table capture the updated_time when I do the modification using the query. Could you please help? How can we solve this ? If the timestamp gets captured while doing modification from spread sheet editor then it would be very helpful for team to work. Thank you and Thanks again for fixing the issue

[timsehn]

Do you mind making a new issue for this?

It seems that dolt table import does not fire triggers. I will test.

[timsehn]

So this is the bug:

$ dolt init --fun
Successfully initialized dolt data repository.
$ dolt sql -q "create table t (pk int primary key, c1 int, ts timestamp)"
$ cat <<EOF > in.csv
pk,c1
0,0
1,1
EOF        
$ dolt sql -q "CREATE TRIGGER trigger1                                
BEFORE INSERT ON t
FOR EACH ROW
SET NEW.ts = CURRENT_TIMESTAMP();"
$ dolt sql -q "insert into t(pk,c1) values (5,5)";
Query OK, 1 row affected (0.00 sec)
$ dolt sql -q "select * from t"                   
+----+----+---------------------+
| pk | c1 | ts                  |
+----+----+---------------------+
| 5  | 5  | 2023-05-11 15:55:39 |
+----+----+---------------------+

$ dolt table import -u t in.csv
Warning: There are fewer columns in the import file's schema than the table's schema.
If unintentional, check for any typos in the import file's header.
Rows Processed: 2, Additions: 2, Modifications: 0, Had No Effect: 0
Import completed successfully.
$ dolt sql -q "select * from t"
+----+----+---------------------+
| pk | c1 | ts                  |
+----+----+---------------------+
| 0  | 0  | 2023-05-11 15:56:21 |
| 1  | 1  | 2023-05-11 15:56:21 |
| 5  | 5  | 2023-05-11 15:55:39 |
+----+----+---------------------+

$ 

As you can see, I made the trigger do it on insert, not update because the rows did not exist. It works.

Now if I add an additional update trigger and make a new CSV, the update trigger is not fired on import:

$ dolt sql -q "CREATE TRIGGER trigger2
BEFORE UPDATE ON t
FOR EACH ROW
SET NEW.ts = CURRENT_TIMESTAMP();"
$ dolt table import -u t in.csv
Warning: There are fewer columns in the import file's schema than the table's schema.
If unintentional, check for any typos in the import file's header.
Rows Processed: 2, Additions: 0, Modifications: 0, Had No Effect: 2
Import completed successfully.
$ dolt sql -q "select * from t"       
+----+----+---------------------+
| pk | c1 | ts                  |
+----+----+---------------------+
| 0  | 0  | 2023-05-11 15:56:21 |
| 1  | 1  | 2023-05-11 15:56:21 |
| 5  | 5  | 2023-05-11 15:55:39 |
+----+----+---------------------+

$ cat <<EOF > in.csv                              
pk,c1
0,1
1,2
EOF
$ dolt table import -u t in.csv
Warning: There are fewer columns in the import file's schema than the table's schema.
If unintentional, check for any typos in the import file's header.
Rows Processed: 2, Additions: 0, Modifications: 2, Had No Effect: 0
Import completed successfully.
$ dolt sql -q "select * from t"
+----+----+---------------------+
| pk | c1 | ts                  |
+----+----+---------------------+
| 0  | 1  | 2023-05-11 15:56:21 |
| 1  | 2  | 2023-05-11 15:56:21 |
| 5  | 5  | 2023-05-11 15:55:39 |
+----+----+---------------------+

I'll make a new Dolt bug.

[timsehn]

https://github.com/dolthub/dolt/issues/5925