Not null columns

CrateDB
1

Hi,

I’ve got several tables that have completely null records in ,even though the primary key field is a varchar with not null constraint. The only field that is actually populated is a generated field ( from a timestamp) to create a two week partitioning timestamp.

The primary keys is made up of the not null varchar and two timestamp fields

So, is the not null constraint affected by it being part of the primary key ?

( I should add the null records are amongst thousands of other valid records)

Many thanks
David

2

Hi David,

We had a bug in < 5.9.11 which wrongfully showed NULL values for columns added after the 5.5 version. You can check the bug description here New column in partitioned table wrongfully shows `NULL` in CrateDB console for not null values · Issue #17575 · crate/crate · GitHub
Can you check if that’s your case? You can do that by removing the LIMIT clause from the query you are running by commenting it out if you are using the Admin UI.

Thank you,
Karyn Azevedo

3

Thanks Karyn,

I don’t think this is the case as all the columns apart from the generated timestamp partition column are null, including the two other “not null” fields that make up the primary key.

I’ll try and recreate the table, but at the moment I’m having issues just straight duplicating the table i.e.
SHOW CREATE TABLE table_a and then use this to CREATE TABLE table_a_new, followed by INSERT INTO table_a_new … SELECT … FROM table_a.
even with the session timeout set to 0 on the console, it fails to complete ( its about 18.7GB)

Thanks
David

4

Hi

Actually I’ve managed to deal with the table duplication by using the partitioning column to do one partition at a time, which seems to help.
Also I noticed that in the web console (timeout set to 0) that once the insert statement returned a result, the insert was still actually inserting data in the background for a while.

I kept thinking it had failed as the data size was not the same for the partitions copied.

So the table is copying OK.

I’ll be able to update this post once a new table is created and I can see if NULLS are still appearing…
Many thanks
David

1 Like
5

Just to add to this, I’ve now worked out that partitions created SINCE we upgraded from 5.9.x to 5.10.3 are the ones containing these null records.
This is happening in all related tables. I can’t tell how frequently this is happening as even the timestamp column is null.

Thanks

6

Hi David,

Could you update to the latest version in the 5.10 series, i.e. 5.10.6? There is a bug in the early versions of the 5.10 series which affected the column name mapping to storage names.

You can check that by comparing the results from the following query, comparing two different partitions:

SELECT _doc
FROM <YOUR_TABLE> WHERE <PARTITION_COLUMN> = <BEFORE_UPGRADE_PARTITION>
LIMIT 100;

-- vs --

SELECT _doc
FROM <YOUR_TABLE> WHERE <PARTITION_COLUMN> = <AFTER_UPGRADE_PARTITION>
LIMIT 100;

If you check the _doc object, in the first query, it will return every column, but on the second, it only shows the partition column, because it is unable to map the other columns.

Thank you,
Karyn Azevedo