This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

Delete failed, several errors

Hi, I installed MariaDB ColumnStore 1.0.8. I use 1 PM and the storage is internal. I'm dealing with bulk data. For instance, 'test_log' table has 100,000,000 rows.

It succeeded to delete 100,000 rows.

MariaDB [input_test]> delete from test_log where stime < '2017-03-31 00:00:00' limit 100000;
Query OK, 100000 rows affected (9.67 sec)

However, when I tried deleting 1,000,000 rows from test_log table, it shows an error as follows:

MariaDB [input_test]> delete from test_log where stime < '2017-03-31 00:00:00' limit 1000000;
ERROR 1815 (HY000): Internal error: ClientRotator caught exception: InetStreamSocket::write error: Broken pipe -- write from InetStreamSocket: sd: 19 inet: 127.0.0.1 port: 8601

I performed the same query again, it succeeded!

MariaDB [input_test]> delete from test_log where stime < '2017-03-31 00:00:00' limit 1000000;
Query OK, 1000000 rows affected (1 min 28.25 sec)

Once I deleted 10,000,000 rows, another error occurred.

MariaDB [input_test]> delete from test_log where stime < '2017-03-31 00:00:00' limit 10000000;
ERROR 2013 (HY000): Lost connection to MySQL server during query

After the error occurred, I couldn't perform other queries with the table because it's locked.

MariaDB [input_test]> delete from test_log where stime < '2017-03-31 00:00:00' limit 10000000;
ERROR 1815 (HY000): Internal error: IDB-2009: Unable to perform the delete operation because DMLProc with PID 21714 is currently holding the table lock for session 2.
MariaDB [input_test]> truncate table test_log;
ERROR 1815 (HY000): Internal error: CAL0009: Truncate table failed:  IDB-2009: Unable to perform the DMLProc operation because 21714 with PID 2 is currently holding the table lock for session .

Could you let me know what the problem is? Why do these errors occur? And how can I fix it?

Thank you.

Answer Answered by David Thompson in this comment.

Please review troubleshooting steps here: https://mariadb.com/kb/en/mariadb/system-troubleshooting-mariadb-columnstore/

You may be hitting a number of issues. You could also file a jira with the output of columnstoreSupport and we can review for issues if you are still reproducing these.

Depending on what you are doing you should look into partition based drops since you appear to have time based data: https://mariadb.com/kb/en/mariadb/columnstore-partition-management/ That is the best way to do large scale deletes and will reclaim disk space.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.