Clickhouse: altered column not found on table

[m7shapan]

I added the revenue column after creating the table, and this error message appears to me,
it's not stable as sometimes it inserts data and sometimes it gives me this error

how I added the column

ALTER TABLE  default.analytics_widget_boxes ADD COLUMN revenue Float64 AFTER ads_clicks
OPTIMIZE TABLE default.analytics_widget_boxes

2020.03.24 16:19:40.286153 [ 90 ] {} <Error> HTTPHandler: Code: 16, e.displayText() = DB::Exception: No such column revenue in table analytics_widget_boxes, Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x10278d1c in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x8f02989 in /usr/bin/clickhouse
2. ? @ 0xcee20ad in /usr/bin/clickhouse
3. DB::InterpreterInsertQuery::execute() @ 0xcedfb76 in /usr/bin/clickhouse
4. ? @ 0xd359078 in /usr/bin/clickhouse
5. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>) @ 0xd35bf05 in /usr/bin/clickhouse
6. DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) @ 0x8fa9922 in /usr/bin/clickhouse
7. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) @ 0x8facf83 in /usr/bin/clickhouse
8. Poco::Net::HTTPServerConnection::run() @ 0xdfffe2c in /usr/bin/clickhouse
9. Poco::Net::TCPServerConnection::start() @ 0xe02da47 in /usr/bin/clickhouse
10. Poco::Net::TCPServerDispatcher::run() @ 0xe02dead in /usr/bin/clickhouse
11. Poco::PooledThread::run() @ 0x10302f0f in /usr/bin/clickhouse
12. Poco::ThreadImpl::runnableEntry(void*) @ 0x102fef58 in /usr/bin/clickhouse
13. ? @ 0x103007f9 in /usr/bin/clickhouse
14. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
15. clone @ 0x12188f in /lib/x86_64-linux-gnu/libc-2.27.so
 (version 20.3.2.1 (official build))

[alesapin]

Maybe you can show full table schema?

[m7shapan]

@alesapin I ran describe command more than one time

DESCRIBE TABLE analytics_widget_boxes

at first

widget_id,String
box_id,UInt8
content_impressions,UInt64
content_clicks,UInt64
ads_impressions,UInt64
ads_clicks,UInt64
country_code,String
device_type,String
date,Date
hour,UInt8

then

widget_id,String
box_id,UInt8
content_impressions,UInt64
content_clicks,UInt64
ads_impressions,UInt64
ads_clicks,UInt64
revenue,Float64
country_code,String
device_type,String
date,Date
hour,UInt8

you can see that at the second result the column appear

[alesapin]

Also SHOW CREATE TABLE would be useful.

[m7shapan]

at first

CREATE TABLE default.analytics_widget_boxes (`widget_id` String, `box_id` UInt8, `content_impressions` UInt64, `content_clicks` UInt64, `ads_impressions` UInt64, `ads_clicks` UInt64, `country_code` String, `device_type` String, `date` Date, `hour` UInt8) ENGINE = MergeTree PARTITION BY toYYYYMM(date) ORDER BY (widget_id, box_id, country_code, device_type, date, hour) SETTINGS index_granularity = 8192

then

CREATE TABLE default.analytics_widget_boxes (`widget_id` String, `box_id` UInt8, `content_impressions` UInt64, `content_clicks` UInt64, `ads_impressions` UInt64, `ads_clicks` UInt64, `revenue` Float64, `country_code` String, `device_type` String, `date` Date, `hour` UInt8) ENGINE = MergeTree PARTITION BY toYYYYMM(date) ORDER BY (widget_id, box_id, country_code, device_type, date, hour) SETTINGS index_granularity = 8192

I want to mention that we have 2 clickhouse instance

[alesapin]

So, It seems like the column was added on the second instance only? Maybe you have different clickhouse versions on the first one?

[alesapin]

Try to update both instances to the latest 20.3 version. If the error persists, just reopen this issue.