[MDEV-31662] plugin ha_connect, file_type=DBF DELETE fails Created: 2023-07-11  Updated: 2023-07-13

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Storage Engine - Connect
Affects Version/s: 10.4.30, 11.0, 10.11.4
Fix Version/s: 10.4, 10.11

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Anel Husakovic
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows 11


Issue Links:
Relates
relates to MDEV-31625 plugin ha_connect, file_type=DBF inse... Closed

 Description   

@@version: 11.0.2-MariaDB

like MDEV-31625 but different:

There's a problem still in the deletes aren't affecting the file format correctly (though SQL seems to cope).

MariaDB [test]>  insert into t1 values (4),(3),(2),(1);
 
 
 
$ od --endian=big -x /tmp/build-mariadb-server-10.4-datadir/test/t1.dbf 
0000000 037b 070b 0400 0000 4200 0c00 0000 0000
 
0000020 0000 0000 0000 0000 0000 0000 0000 0000
 
0000040 6100 0000 0000 0000 0000 004e 0000 0000
 
0000060 0b00 0000 0000 0000 0000 0000 0000 0000
 
0000100 0d00 2020 2020 2020 2020 2020 2034 2020
 
0000120 2020 2020 2020 2020 2033 2020 2020 2020
 
0000140 2020 2020 2032 2020 2020 2020 2020 2020
 
0000160 2031 1a00
 
0000163


MariaDB [test]> delete from t1 where a=1;
 
 
 
$ od --endian=big -x /tmp/build-mariadb-server-10.4-datadir/test/t1.dbf 
0000000 037b 070b 0400 0000 4200 0c00 0000 0000
 
0000020 0000 0000 0000 0000 0000 0000 0000 0000
 
0000040 6100 0000 0000 0000 0000 004e 0000 0000
 
0000060 0b00 0000 0000 0000 0000 0000 0000 0000
 
0000100 0d00 2020 2020 2020 2020 2020 2034 2020
 
0000120 2020 2020 2020 2020 2033 2020 2020 2020
 
0000140 2020 2020 2032 2a20 2020 2020 2020 2020
 
0000160 2031 1a00
 
0000163

offset 0146 is different.

The record isn't deleted, its still there. The Number of records in the header isn't updated.

libreoffice thinks the file is now corrupted (though does a decent recovery job).

 Comments   
Comment by Daniel Black [ 2023-07-11 ]

DUNAZOLTAN, how important is the DELETE use case to you? Just wondering how to prioritise it.

Comment by Mr. Zoltan Duna [ 2023-07-12 ]

DELETE use case is not important to me. I mainly use SELECT and INSERT statements.
But, what about the UPDATE use case? Can I use it safely?

Comment by Daniel Black [ 2023-07-12 ]

As far as I can see UPDATES behave correctly. They overwrite the fields of the table and it doesn't need to be shrunk or the table header updated with a change in rows. Table fields are also of a fixed size.

So DELETEs will be fixed eventually. If anyone is currently affected by the poor dbf format implementation of a DELETE please comment and watch this issue and we'll prioritize it more.

Comment by Daniel Black [ 2023-07-13 ]

notably but not critical is that UPDATE writes to file, two rows instead of 1, its just the second is same as it was.

MariaDB [test]>  insert into t1 values (4),(3),(2),(1);
 
Query OK, 4 rows affected (0.001 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
MariaDB [test]> update t1 set a=5 where a=4;
 
Query OK, 1 row affected (0.002 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0

trace extract of UPDATE query only

 
[pid 203959] openat(AT_FDCWD, "/tmp/build-mariadb-server-10.4-datadir/./test/t1.dbf", O_RDWR) = 45
 
[pid 203959] newfstatat(45, "", {st_mode=S_IFREG|0660, st_size=115, ...}, AT_EMPTY_PATH) = 0
 
[pid 203959] lseek(45, 0, SEEK_SET)     = 0
 
[pid 203959] read(45, "\3{\7\16\4\0\0\0B\0\f\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 66) = 66
 
[pid 203959] read(45, "           4           3        "..., 4096) = 49
 
[pid 203959] read(45, "", 4096)         = 0
 
[pid 203959] lseek(45, 0, SEEK_SET)     = 0
 
[pid 203959] read(45, "\3{\7\16\4\0\0\0B\0\f\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 66) = 66
 
[pid 203959] write(45, "           5           3        "..., 48) = 48
 
[pid 203959] close(45)                  = 0

Generated at Thu Feb 08 10:25:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.