search

jayvynl / django-clickhouse-backend

Django clickhouse database backend.
MIT License
130 stars 21 forks source link

[BUG] DB Error 386 when updating JSONField #62

Closed ernestasga closed 1 year ago

ernestasga commented 1 year ago

Describe the bug A database error is thrown during update of jsonfield.

backend     | Internal Server Error: /
backend     | Traceback (most recent call last):
backend     |   File "/usr/local/lib/python3.8/site-packages/clickhouse_driver/dbapi/cursor.py", line 111, in execute
backend     |     response = execute(
backend     |   File "/usr/local/lib/python3.8/site-packages/clickhouse_backend/driver/client.py", line 53, in execute
backend     |     rv = self.process_ordinary_query(
backend     |   File "/usr/local/lib/python3.8/site-packages/clickhouse_driver/client.py", line 571, in process_ordinary_query
backend     |     return self.receive_result(with_column_types=with_column_types,
backend     |   File "/usr/local/lib/python3.8/site-packages/clickhouse_driver/client.py", line 204, in receive_result
backend     |     return result.get_result()
backend     |   File "/usr/local/lib/python3.8/site-packages/clickhouse_driver/result.py", line 50, in get_result
backend     |     for packet in self.packet_generator:
backend     |   File "/usr/local/lib/python3.8/site-packages/clickhouse_driver/client.py", line 220, in packet_generator
backend     |     packet = self.receive_packet()
backend     |   File "/usr/local/lib/python3.8/site-packages/clickhouse_driver/client.py", line 237, in receive_packet
backend     |     raise packet.exception
backend     | clickhouse_driver.errors.ServerException: Code: 386.
backend     | DB::Exception: There is no supertype for types Object('json'), Tuple(key String) because some of them are Tuple and some of them are not: While processing _CAST(if(id = 1729526377540485120, _CAST(map('key', 'new_value'), 'Object(\'json\')'), content), 'Object(\'json\')'). Stack trace:
backend     | 
backend     | 0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c2f4404 in /usr/bin/clickhouse
backend     | 1. ? @ 0x000000000acbdf98 in /usr/bin/clickhouse
backend     | 2. ? @ 0x00000000103ee170 in /usr/bin/clickhouse
backend     | 3. std::shared_ptr<DB::IDataType const> DB::getLeastSupertype<(DB::LeastSupertypeOnError)0>(std::vector<std::shared_ptr<DB::IDataType const>, std::allocator<std::shared_ptr<DB::IDataType const>>> const&) @ 0x00000000103eddb0 in /usr/bin/clickhouse
backend     | 4. ? @ 0x00000000099c48e0 in /usr/bin/clickhouse
backend     | 5. ? @ 0x0000000008078d64 in /usr/bin/clickhouse
backend     | 6. DB::IFunctionOverloadResolver::getReturnTypeWithoutLowCardinality(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&) const @ 0x000000000fe90280 in /usr/bin/clickhouse
backend     | 7. DB::IFunctionOverloadResolver::getReturnType(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&) const @ 0x000000000fe8fee0 in /usr/bin/clickhouse
backend     | 8. DB::IFunctionOverloadResolver::build(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&) const @ 0x000000000fe90b04 in /usr/bin/clickhouse
backend     | 9. DB::ActionsDAG::addFunction(std::shared_ptr<DB::IFunctionOverloadResolver> const&, std::vector<DB::ActionsDAG::Node const*, std::allocator<DB::ActionsDAG::Node const*>>, String) @ 0x000000001055500c in /usr/bin/clickhouse
backend     | 10. DB::ScopeStack::addFunction(std::shared_ptr<DB::IFunctionOverloadResolver> const&, std::vector<String, std::allocator<String>> const&, String) @ 0x000000001071dc2c in /usr/bin/clickhouse
backend     | 11. ? @ 0x0000000010726c14 in /usr/bin/clickhouse
backend     | 12. DB::ActionsMatcher::visit(DB::ASTFunction const&, std::shared_ptr<DB::IAST> const&, DB::ActionsMatcher::Data&) @ 0x0000000010720194 in /usr/bin/clickhouse
backend     | 13. DB::ActionsMatcher::visit(DB::ASTFunction const&, std::shared_ptr<DB::IAST> const&, DB::ActionsMatcher::Data&) @ 0x0000000010720cdc in /usr/bin/clickhouse
backend     | 14. ? @ 0x0000000010716ba0 in /usr/bin/clickhouse
backend     | 15. DB::ExpressionAnalyzer::getRootActions(std::shared_ptr<DB::IAST> const&, bool, std::shared_ptr<DB::ActionsDAG>&, bool) @ 0x00000000106f9b14 in /usr/bin/clickhouse
backend     | 16. DB::MutationsInterpreter::prepareMutationStages(std::vector<DB::MutationsInterpreter::Stage, std::allocator<DB::MutationsInterpreter::Stage>>&, bool) @ 0x0000000010f2d6c0 in /usr/bin/clickhouse
backend     | 17. DB::MutationsInterpreter::prepare(bool) @ 0x0000000010f29828 in /usr/bin/clickhouse
backend     | 18. DB::MutationsInterpreter::MutationsInterpreter(DB::MutationsInterpreter::Source, std::shared_ptr<DB::StorageInMemoryMetadata const>, DB::MutationCommands, std::vector<String, std::allocator<String>>, std::shared_ptr<DB::Context const>, DB::MutationsInterpreter::Settings) @ 0x0000000010f23a60 in /usr/bin/clickhouse
backend     | 19. DB::MutationsInterpreter::MutationsInterpreter(std::shared_ptr<DB::IStorage>, std::shared_ptr<DB::StorageInMemoryMetadata const>, DB::MutationCommands, std::shared_ptr<DB::Context const>, DB::MutationsInterpreter::Settings) @ 0x0000000010f234f0 in /usr/bin/clickhouse
backend     | 20. DB::InterpreterAlterQuery::executeToTable(DB::ASTAlterQuery const&) @ 0x0000000010d33d14 in /usr/bin/clickhouse
backend     | 21. ? @ 0x0000000011179854 in /usr/bin/clickhouse
backend     | 22. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x00000000111764e4 in /usr/bin/clickhouse
backend     | 23. DB::TCPHandler::runImpl() @ 0x0000000011d5ddd8 in /usr/bin/clickhouse
backend     | 24. DB::TCPHandler::run() @ 0x0000000011d70ae4 in /usr/bin/clickhouse
backend     | 25. Poco::Net::TCPServerConnection::start() @ 0x0000000012a275e4 in /usr/bin/clickhouse
backend     | 26. Poco::Net::TCPServerDispatcher::run() @ 0x0000000012a28b00 in /usr/bin/clickhouse
backend     | 27. Poco::PooledThread::run() @ 0x0000000012be98bc in /usr/bin/clickhouse
backend     | 28. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000012be7184 in /usr/bin/clickhouse
backend     | 29. start_thread @ 0x0000000000007624 in /usr/lib/aarch64-linux-gnu/libpthread-2.31.so
backend     | 30. ? @ 0x00000000000d149c in /usr/lib/aarch64-linux-gnu/libc-2.31.so
backend     | 
backend     | 
backend     | During handling of the above exception, another exception occurred:
backend     | 
backend     | Traceback (most recent call last):

To Reproduce Updating jsonfield of a model and save it.

Created a simple docker-compose project to demonstrate.

Check app/event/views.py for code.

Expected behavior Should update the model successfully

Versions

jayvynl commented 1 year ago

I have searched and tested. It's a bug of clickhouse. The JSON data type is an experimental feature.

https://github.com/ClickHouse/ClickHouse/issues/46575 https://stackoverflow.com/questions/76555036/clickhouse-how-to-update-json-field

create table test(id Int64, json JSON) engine MergeTree order by ();
insert into table test values (1, '{"a":"b"}');
-- no exception, but data not updated --
alter table test update json='{"a":"c"}' where 1;
-- Code: 341. DB::Exception: --
alter table test update json='{"a":"c"}' where 1 settings mutations_sync=1;
-- Code: 386. DB::Exception: --
alter table test update json='{"a":"c"}' where id=1;
ernestasga commented 1 year ago

Such a shame. Will need to look for an alternative solution. Closing the issue.