[MDEV-29337] CONNECT BIN unsigned fails on DISTRIB=sorted Created: 2022-08-19  Updated: 2023-11-13

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.8.4, 10.9.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: catam Assignee: Andrew Hutchings
Resolution: Unresolved Votes: 0
Labels: None


 Description   

create a CONNECT BIN table with a unsigned field is not creating the .bop block_index file,
also fails on optimize table as well.:

create table tm (
 
 time INT4 unsigned not null DEFAULT 0 field_format='X' DISTRIB=sorted,
 
 data INT4 unsigned not null DEFAULT 0 field_format='X'
 
) engine=CONNECT table_type=BIN block_size=128  file_name='tm.dat';

then INSERT 1000 rows,

MariaDB [test]> select * from tm limit 10;
 
 
 
| time       | data    |
 
 
 
| 1651378127 | 7447060 |
 
| 1651378187 | 6197231 |
 
| 1651378288 | 9090561 |
 
| 1651378390 | 9525601 |
 
| 1651378451 | 4827450 |
 
| 1651378511 | 4905942 |
 
| 1651378579 | 5260349 |

no tm.bop file shows up:

-rw-rw---- 1 mysql mysql 8000 Aug 19 14:31 tm.dat
 
-rw-rw---- 1 mysql mysql  565 Aug 19 14:31 tm.frm

Optimize fails as well:

MariaDB [test]> optimize table tm;
 
 
 
| Table   | Op       | Msg_type | Msg_text                                      |
 
 
 
| test.tm | optimize | Error    | Non matching Value types                      |
 
| test.tm | optimize | error    | Unknown - internal error 122 during operation |
 
 
 
2 rows in set (0.000 sec)

Recreate table with signed 

drop table tm;
 
create table tm (
 
 time INT4 not null DEFAULT 0 field_format='X' DISTRIB=sorted,
 
 data INT4 unsigned not null DEFAULT 0 field_format='X'
 
) engine=CONNECT table_type=BIN block_size=128  file_name='tm.dat';
 

update tm set time=time-1 limit 1;
 
update tm set time=time+1 limit 1

Now we have tm.bop file:

-rw-rw---- 1 mysql mysql   80 Aug 19 14:33 tm.bop
 
-rw-rw---- 1 mysql mysql 8000 Aug 19 14:33 tm.dat
 
-rw-rw---- 1 mysql mysql  565 Aug 19 14:33 tm.frm

And also optimize works

MariaDB [test]> optimize table tm;
 
 
 
| Table   | Op       | Msg_type | Msg_text |
 
 
 
| test.tm | optimize | status   | OK       |
 
 
 
1 row in set (0.000 sec)

So there seems to be an issue with unsigned types (I tried short as well) and this DISTRIB sorted



 Comments   
Comment by Alice Sherepa [ 2022-08-19 ]

Thanks for the report!
I repeated as described.
To repeat the bug it needs unsigned + DISTRIB=sorted + block_size.
With signed int and no update manipulations - I get "internal error 145 during operation " after optimize table.

MariaDB [test]> create table t8 ( a INT unsigned DISTRIB=sorted) engine=CONNECT table_type=BIN block_size=100  file_name='t8.dat';
 
Query OK, 0 rows affected (0.016 sec)
 
 
 
MariaDB [test]> insert into t8 select seq from seq_1_to_1000;
 
Query OK, 1000 rows affected, 1 warning (0.001 sec)
 
Records: 1000  Duplicates: 0  Warnings: 0
 
 
 
Warning (Code 1105): Non matching Value types
 
MariaDB [test]> optimize table t8;
 
+---------+----------+----------+-----------------------------------------------+
 
| Table   | Op       | Msg_type | Msg_text                                      |
 
+---------+----------+----------+-----------------------------------------------+
 
| test.t8 | optimize | Error    | Non matching Value types                      |
 
| test.t8 | optimize | error    | Unknown - internal error 122 during operation |
 
+---------+----------+----------+-----------------------------------------------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]> create table t9 ( a INT unsigned DISTRIB=sorted) engine=CONNECT table_type=BIN   file_name='t9.dat';
 
Query OK, 0 rows affected (0.016 sec)
 
 
 
MariaDB [test]> insert into t9 select seq from seq_1_to_1000;
 
Query OK, 1000 rows affected (0.001 sec)
 
Records: 1000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> optimize table t9;
 
+---------+----------+----------+--------------------------+
 
| Table   | Op       | Msg_type | Msg_text                 |
 
+---------+----------+----------+--------------------------+
 
| test.t9 | optimize | Warning  | Not an optimizable table |
 
| test.t9 | optimize | status   | OK                       |
 
+---------+----------+----------+--------------------------+
 
2 rows in set (0.000 sec)


MariaDB [test]> create table tm ( a int not null  DISTRIB=sorted ) engine=CONNECT table_type=BIN block_size=128 file_name='a.dat';
 
Query OK, 0 rows affected (0.017 sec)
 
 
 
MariaDB [test]> insert into tm select seq from seq_1_to_1000;
 
Query OK, 1000 rows affected, 1 warning (0.002 sec)
 
Records: 1000  Duplicates: 0  Warnings: 0
 
 
 
Warning (Code 1105): Column a of table tm is not sorted
 
MariaDB [test]> optimize table tm;
 
+---------+----------+----------+-----------------------------------------------+
 
| Table   | Op       | Msg_type | Msg_text                                      |
 
+---------+----------+----------+-----------------------------------------------+
 
| test.tm | optimize | Error    | Column a of table tm is not sorted            |
 
| test.tm | optimize | error    | Unknown - internal error 145 during operation |
 
+---------+----------+----------+-----------------------------------------------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]> create table tm2 ( a int not null  DISTRIB=sorted ) engine=CONNECT table_type=BIN block_size=128 file_name='b.dat';
 
Query OK, 0 rows affected (0.022 sec)
 
 
 
MariaDB [test]> insert into tm2 select seq from seq_1_to_1000;
 
Query OK, 1000 rows affected (0.001 sec)
 
Records: 1000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> update tm2 set a=a-1 limit 1;
 
Query OK, 1 row affected (0.001 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [test]> update tm2 set a=a+1 limit 1;
 
Query OK, 1 row affected (0.001 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [test]> optimize table tm2;
 
+----------+----------+----------+----------+
 
| Table    | Op       | Msg_type | Msg_text |
 
+----------+----------+----------+----------+
 
| test.tm2 | optimize | status   | OK       |
 
+----------+----------+----------+----------+
 
1 row in set (0.001 sec)

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