Inserts not being saved (but no error displayed?)

[Ferran Gil Cuesta]

Hello everybody,

We are using the lastest community version which seems to perform much better for us (see https://groups.google.com/g/mariadb-columnstore/c/rnMcDpj6gEQ for context) but we are facing a new issue.

We have one table that seems to "skip" the inserts we perform to it.

We have two servers doing the same inserts (one is running columnstore 6.4.1 and the other is at 6.4.7). The recent one is the one facing this issue.

We have consolidation process that creates a temporary table, makes inserts grouping data from another table, and then swaps names and keeps only the new "compacted" table. This process creates a new table (create table like xxx) so even if there was a problem with a specific table, after running this process we are technically creating a brand new table, inserting data and replacing them.
On the old server we are not facing this issue (and we see that selecting data from the table does bring new values while the updating process is running). 

On the new server, is like the inserts are not really saved (but we are not notified, or are unable to see it).

Doing a reboot (sudo systemctl restart mariadb mariadb-columnstore) makes the table "writeable" (and really storing the inserted data), but we are affraid the non-storing inserts will happen again (it happened 2 days ago). 

At the moment the updating process can and is indeed storing new data into this table.

Columnstore logs don't reveal anything strange, as far as we can see.

The problematic table has 48 fields (mostly floats) and 8 milion rows (it's not that big, and we have much bigger tables with no updating issues).

Any hints would be appreciated.

Thanks,

Ferran

[drrtuy]

Hey Ferran,

Could you describe the effect in more details b/c I am not sure that I understand the case correctly?
Could you also share the output for  the sql statement
show variables like 'columnstore_cache%'
?

Regards,
Roman

среда, 1 марта 2023 г. в 19:48:12 UTC+3, Ferran Gil Cuesta:

[Ferran Gil Cuesta]

Hey Roman,

Variables are like:

We didn't tweak anything, and in the upgrade we kept the previous config (mostly defaults with VersionBufferFileSize set to 8GB).

Let me explain it again and try to be more clear:

- we have 10 tables that are updated every hour/two hours

- for all of them except one, after an insert, if you perform a select you do see the newly inserted data

- for one in particular, the select does not show the inserted rows, even if in our python script we get the same output as for the rest (i.e: number of rows inserted, etc).

It seems that a simple reboot (sudo systemctl restart mariadb mariadb-columnstore) seems to "fix" the issue. Yesterday morning we did a restart, and the following updates inserted data just fine, which we could then see via selects, ... but this morning we see again that data is not being stored (we can compare the with the old server, were we run the same processes, and there are more rows and more data there than in the faulty table).

At the same time, as our ETL is "incremental" by nature, we run a process every week to compact/consolidate data. This process does this:

- creates a new table tmp_table

- inserts data from the current table to tmp_table, with a combination of sum(col1), sum(col2)... groupby xx, yy... in order to reduce the fragmentation.

- it works fine for all tables and also works for this "faulty" one, but for most of the tmp_table we see ~25% less rows, but the faulty one says 1% or so (as all the inserts that apparently our ETL did are not really saved and are not being returned when doing selects).

Unfortunately, we are unable to reproduce it. We can just wait and check and compare between the old system and the new one, to discover that even if it says that it has been doing inserts, we cannot see them (but we see them on the old one).

What is also annoying is the fact that our consolidation process creates a completely new table, and the faulty table is dropped after the new table has all data. Then we rename the table and keep using the newly created one in our ETL. As far as we understand, creating a new table and dropping the one that has issues should be enough to avoid the issue (in case the table itself had an issue).

Another thing we cannot understand is that the non-upgraded MariaDB server (running 6.4.1) is updating his own table just fine (as well as the other tables), and we ETL is exactly the same (inserting 1000 records at a time, etc). The old server has now less resources (32GB RAM, 8 CPU), so it is slower in general but the inserts work just fine.

We are not sure if the updating process messed with some catalog data or similar (although doing the consolidation, that creates a brand new table, should be enough, I guess). One thing we could try is to build an empty server with the latest MariaDB + CS version, and then re-ingest all data from scratch (first we need to export them from the current live server, a process we did a year ago, and it is slow and quite manual...).

We are happy that the overall stability in MariaDB+Columnstore improved but not being able to rely on the data stored is obviously very worring.

Any ideas are welcome! Thanks

Ferran

[Ferran Gil Cuesta]

Hi all,

A new update on this issue.

It seems that the issue was already present in the old DB version (6.4.1), but as we were restarting every 3 nights, we didn't realize!

We have a way to replicate the issue, but it is probably not quick: just leave the database performing updates for  a few days and most likely we will start seeing that the insert records are not there when you select them.

Today we also tested by performing an insert from the MariaDB shell directly, to see if the issue was already present there, and it is. Take a look at the following select, which counts and sums some values grouped by day, and sorted by day. We insert two records for 2023-03-06 and it says OK, but we execute the select again and the new values are not visible at all.

Currently we are being forced to restart MariaDB every very few days, and in fact we are not really sure if we are missing some data that should be stored in DB at any given moment.

We are going to "count" how many records we have before and after an updating process, so we can know for sure if the inserts are being saved to DB or not, and alert us asap (instead of the current workflow, were we don't know if the inserts are working or not and we have to manually check and compare between systems).

If anybody experienced missing inserts after some hours, please shout.

Best regards,

Ferran

[drrtuy]

Hi Ferran and Zorion,

Thanks for the case! I will look into this shortly.

Regards,
Roman

понедельник, 6 марта 2023 г. в 13:53:12 UTC+3, Ferran Gil Cuesta:

[drrtuy]

So here is the thing.

The issue is reproducible in the current CS 6.4.7 version however it doesn't happen in the next major 22.08. The plan is to publish the most recent major that is 23.02 in April for community.

Regards,

Roman

пятница, 10 марта 2023 г. в 11:52:52 UTC+3, drrtuy:

[Ferran Gil Cuesta]

Hi Roman, 

Great news that the next release will fix the issue.

Is there any specific URL where we can check if the release 23.02 is available or not?

I am unable to find a clear "changelog" with versions and dates in the MariaDB website.

Thanks,

Ferran

[drrtuy]

Hi,

The release info will be available here.

Regards,

Roman

понедельник, 17 апреля 2023 г. в 11:51:58 UTC+3, Ferran Gil Cuesta:

[Ferran Gil Cuesta]

Hi all,

We tested the new MariaDB version (11.0.2) Stable and unfortunately the issue when renaming tables is still present.

It requires a restart for ColumnStore to be aware that the table with the new name exists.

The steps to reproduce are quite simple, just follow these 4 steps:

CREATE TABLE t1 (col1 float DEFAULT NULL, day date DEFAULT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3;

INSERT INTO t1 (col1, day) VALUES ('0.291','2023-03-02'),('0.062','2023-03-03'),('0.594','2023-03-03');

RENAME TABLE t1 TO t2;

INSERT INTO t2 (col1, day) VALUES ('0.499','2023-03-04'),('0.862','2023-03-04'),('0.785','2023-03-01');

As you can see on the image below, restarting MariaDB allows the second insert to perform successfully.

Is there are other information we can provide in order to fix this big issue?

Thank you for all your hard work.

Best regards,

Ferran

[Allen Herrera]

I don't see this happening in enterprise 23.02.3 btw.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/7060aa76-17ad-491f-9a0e-215237929309n%40googlegroups.com.

--

Allen Herrera

Customer Engineer | MariaDB Corporation

+1.360.888.3938 | calendly.com/allen-herrera | allen.herrera@mariadb.com

[Ferran Gil Cuesta]

I still see 6.4.7 when performing select calgetversion();
This 6.4.7 is the same I was seeing in the version we have been using for the last few months. (mariadb --version -> mariadb Ver 15.1 Distrib 10.10.3-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2)

Does this means the bug is not yet fixed in the Community version??

Thanks,

Ferran

[Todd Stoffel]

6.4.7 is an older version of ColumnStore. You'll need to upgrade to Community Server 11.1.1 to get the latest Columnstore release (which is 23.2.3).

[Ferran Gil Cuesta]

I've installed MariaDB 11.11.1 using the .deb packages (in our case mariadb-11.1.1-debian-buster-amd64-debs) but although everything looks fine there's no ColumnStore at all.

From a MariaDB prompt, I tried a few things:

MariaDB [client_cache]> select calgetversion();
ERROR 1305 (42000): FUNCTION client_cache.calgetversion does not exist

And also a simple count to one of the tables I see:

MariaDB [client_cache]> select count(*) from my_columnstore_table;
ERROR 1286 (42000): Unknown storage engine 'Columnstore'

I can see all the tables as usual by calling "show tables;" and I see MariaDB is indeed 11.11.1 (Server version: 11.1.1-MariaDB-1:11.1.1+maria~deb10 mariadb.org binary distribution) but we don't know how to troubleshoot this. The available documentation we could find on ColumnStore is outdated.

Any hints?

Thanks,

Ferran

[Ferran Gil Cuesta]

Sorry for the confusion; we are obviously on 11.1.1 (not 11.11.1 as I wrongly stated before).

I just want to add that a "show engines;" does not display any columnstore engine in this running MariaDB 11.1.1.
"show plugins" lists 70 of them, all active except FEEDBACK. However, none seems related to ColumnStore.

Thank you for any help you can provide.

Cheers.

[Roland Noland]

Hi!

MCS is shipped as a separate package. Do you see the package?

Regards,

Roman

пн, 12 июн. 2023 г., 12:50 Ferran Gil Cuesta <fg...@g-n.com>:

You received this message because you are subscribed to a topic in the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mariadb-columnstore/37LViZMXu8o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mariadb-columns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/a3ac6204-d1b1-4cfa-9c80-b816f5327ad5n%40googlegroups.com.

[Ferran Gil Cuesta]

I don't see any "mcs" package. Calling "show package status" gives Empty set.

The file I downloaded from https://mariadb.com/downloads/ (community server, URL https://dlm.mariadb.com/3240734/MariaDB/mariadb-11.1.1/repo/debian/mariadb-11.1.1-debian-buster-amd64-debs.tar) contains the following files:



I basically apt-get installed the mariadb-server, nothing else.

Thanks

[Roland Noland]

Right, there is a trade off. We upgraded mcs to the most recent version but we can support it for distributions that has relatively modern compiler, e.g. u22, rocky8/9. Lwt mw check what is the least possible Debian release 11.1.1 has MCS.

Regards,

Roman

пн, 12 июн. 2023 г., 14:13 Ferran Gil Cuesta <fg...@g-n.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/9e37230e-9155-4d8b-8e43-8ed126ca75can%40googlegroups.com.

[Ferran Gil Cuesta]

Thanks Roland for the info. We were not aware of that.

We would be able to test it in Debian 11, in case MCS is "included" there. Or Debian 12, or a different distribution.

In case we use a newer distribution, do we need to install MariaDB or MCS in a different way or what we did is more or less what is required?

Many thanks for your help on this issue. I hope more people will benefit later on.

Best regards,

Ferran

Ferran Gil (+34) 93-611-6048 fg...@g-n.com

[Roland Noland]

The procedure is the same but the list of the distributions supported was reduced with 11.1.1 mainly b/c there are certain promices to the community that MDB server team has to hold.

Regards,

Roman

пн, 12 июн. 2023 г., 15:13 Ferran Gil Cuesta <fg...@g-n.com>:

[Ferran Gil Cuesta]

Just a quick follow-up: We are running Columnstore in Debian 11, without problems.

We tried Debian 12 but couldn't make it work (maybe it is not yet possible).

[drrtuy]

Hey

JFYI The upcoming 23.10.0 release(the next MDB 11.1 will have it) supports debian 12.

Regards,
Roman

пятница, 20 октября 2023 г. в 14:38:48 UTC+3, Ferran Gil Cuesta: