One Master node, one subordinate node, 7 proxies.
Ubuntu 12.04
MySQL 5.6

you're not running 2.1 there accidentally - or maybe you have run 2.1 against some of those databases at some point ?

No, i'm really sure that i never downloaded/installed a 2.1 release.
Probably some code artifacts are merged to 2.0.9 from 2.1.x?

Found another strange message:

cannot find table [hosts_profiles]

This table also does not exist on zabbix 2.0 installations.
The zabbix 2.0.9 migration script deletes this table upgrades/dbpatches/2.0/mysql/patch.sql (DROP TABLE hosts_profiles)

It seems that there is 1.8.x code in this release.

I compared the process ids of the messages with the startup message. The problems are caused by the following process:

5021:20131104:163024.841 server #33 started node watcher #1

See also http://pastebin.com/pFGamEa7 (available for 24 hours)

I compiled zabbix with debug info and inspected the situation using gdb.

break db.c:1161
commands 1
print sql
info locals
backtrace
continue
end

break db.c:921
commands 2
print sql
info locals
backtrace
continue
end

break db.c:955
print sql
info locals
commands 3
backtrace
continue
end

I got the follwing results:

Problem: [Z3005] query failed: [1054] Unknown column 'macro' in 'field list' [select macro from graphs_items where gitemid=200200000002550]

Breakpoint 1, zbx_db_vselect (fmt=<optimized out>, args=<optimized out>) at db.c:1161
1161 zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
$423 = 0x133cfb0 "select macro from graphs_items where gitemid=200100000000001"
sql = 0x133cfb0 "select macro from graphs_items where gitemid=200100000000001"
result = 0x0
sec = 0
#0 zbx_db_vselect (fmt=<optimized out>, args=<optimized out>) at db.c:1161
#1 0x000000000046f800 in __zbx_DBselect (fmt=0x482c73 "%s") at db.c:381
#2 0x0000000000427750 in DMcollect_table_data (data_offset=0x7fff41991d18, data_alloc=0x7fff41991d10, data=0x7fff41991d08, table=0x4c5ee0, dest_nodetype=1 '\001', nodeid=<optimized out>) at ../nodewatcher/nodesender.c:343
#3 DMget_table_data (nodeid=<optimized out>, dest_nodetype=1 '\001', table=0x4c5ee0, data=0x7fff41991d08, data_alloc=0x7fff41991d10, data_offset=0x7fff41991d18, ptbls=0x7fff41991cf0, ptbls_alloc=0x7fff41991cf8, ptbls_offset=0x7fff41991d00) at ../nodewatcher/nodesender.c:487
#4 0x0000000000428169 in DMget_config_data (nodeid=2, dest_nodetype=1 '\001') at ../nodewatcher/nodesender.c:536
#5 0x00000000004289ce in process_nodes () at ../nodewatcher/nodesender.c:856
#6 0x000000000042b533 in main_nodewatcher_loop () at nodewatcher.c:185
#7 0x0000000000413b8a in MAIN_ZABBIX_ENTRY () at server.c:717
#8 0x0000000000449a2c in daemon_start (allow_root=<optimized out>) at daemon.c:286
#9 0x00007fc53a9d276d in __libc_start_main () from /lib/x86_64-linux-gnu/libc.so.6
#10 0x000000000040f6a9 in _start ()

Problem: [Z3005] query failed: [1054] Unknown column 'internal' in 'field list' [select name,internal,name,hsize,vsize,templateid from hosts where hostid=200200000010246]

Breakpoint 1, zbx_db_vselect (fmt=<optimized out>, args=<optimized out>) at db.c:1161
1161 zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
$91 = 0x133cfb0 "select name,internal,name,hsize,vsize,templateid from hosts where hostid=200100000000025"
sql = 0x133cfb0 "select name,internal,name,hsize,vsize,templateid from hosts where hostid=200100000000025"
result = 0x0
sec = 0
#0 zbx_db_vselect (fmt=<optimized out>, args=<optimized out>) at db.c:1161
#1 0x000000000046f800 in __zbx_DBselect (fmt=0x482c73 "%s") at db.c:381
#2 0x0000000000427750 in DMcollect_table_data (data_offset=0x7fff41991d18, data_alloc=0x7fff41991d10, data=0x7fff41991d08, table=0x4aff60, dest_nodetype=1 '\001', nodeid=<optimized out>) at ../nodewatcher/nodesender.c:343
#3 DMget_table_data (nodeid=<optimized out>, dest_nodetype=1 '\001', table=0x4aff60, data=0x7fff41991d08, data_alloc=0x7fff41991d10, data_offset=0x7fff41991d18, ptbls=0x7fff41991cf0, ptbls_alloc=0x7fff41991cf8, ptbls_offset=0x7fff41991d00) at ../nodewatcher/nodesender.c:487
#4 0x0000000000428169 in DMget_config_data (nodeid=2, dest_nodetype=1 '\001') at ../nodewatcher/nodesender.c:536
#5 0x00000000004289ce in process_nodes () at ../nodewatcher/nodesender.c:856
#6 0x000000000042b533 in main_nodewatcher_loop () at nodewatcher.c:185
#7 0x0000000000413b8a in MAIN_ZABBIX_ENTRY () at server.c:717
#8 0x0000000000449a2c in daemon_start (allow_root=<optimized out>) at daemon.c:286
#9 0x00007fc53a9d276d in __libc_start_main () from /lib/x86_64-linux-gnu/libc.so.6
#10 0x000000000040f6a9 in _start ()

DMget_config_data seems to be the source of the problem. Where is "tables" filled with data?

It seems that this problem is sourced in the contents of the database.

You can send me GDB or SQL commands to get deeper details.

Thank you for investigation.

Please attach dbschema.c file. It is located in src/libs/zbxdbhigh/. Seems it's broken.

These SQL statements are built on the basis of data from this file.

The requested file.

The files is part of the source distribution downloaded at:

RELEASE="2.0.9"
wget "http://freefr.dl.sourceforge.net/project/zabbix/ZABBIX%20Latest%20Stable/${RELEASE}/zabbix-${RELEASE}.tar.gz"

1. md5sum zabbix-2.0.9.tar.gz
   edf00241cce2302e0b65f620e83c7e41 zabbix-2.0.9.tar.gz

ZBX-7493 is somewhat similar

for the record, attached dbschema matches 2.0 dbschema.c

how was zabbix compiled and installed ? you don't have multiple source archives or binaries by accident ?

As described in comment of "2013 Nov 06 12:36" we ensured that the file is part of the distribution.
We compared the file several times with the downloaded release.

I had:

 11981:20131228:034110.868 NODE 10: Received configuration changes from slave node 30 for node 30 datalen 54
 11981:20131228:034208.226 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3001000000000001]
 11981:20131228:034208.227 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3001000000000002]
 11981:20131228:034208.227 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3001000000000003]
...
many lines here with increasing applicationid, totally 1540
...
 11981:20131228:034208.395 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3003000000000961]
 11981:20131228:034208.395 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3003000000000962]
 11981:20131228:034208.395 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3003000000000963]
 11981:20131228:034210.257 NODE 10: sending configuration changes to slave node 30 for node 30 datalen 10

errors and I was able to fix that.

These errors appeared every time when master node received configuration changes from child node.
They started to appear right after upgrade 2.0.9 -> 2.2.0
Unfortunately I don't know is the child has been upgraded together with master or not. It was not under my control.

As we can see above there is a problem when master nodeid10 receiving configuration changes from child nodeid30.
It's a two-way sync. And at some point master node perform incorrect query.

Of course we already sure that there are no so obvious errors in zabbix code or db schemas.
So what is it then ?
I decided to investigate a "node_cksum" table.
Actually I didn't want to investigate it because I investigated its data structures some time ago and I don't want to do it again now, I'm not crazy

I have a part of the table dump when the issue presents, you could investigate it, attached.

# echo 'select * from node_cksum where tablename like "applications" and recordid like "300%"' | mysql zabbix -uzabbix -p > applications_cksums_broken

Then I've cleared up related entries on master node database:

mysql> delete from node_cksum where tablename = "applications" and recordid like "300%";
Query OK, 3248 rows affected (44.19 sec)

(do it with stopped the child nodeid30 !!!)

And the issue is gone, there just was a bit increased resync:

# grep -E "configuration changes|Unknown column" zabbix_server.log | tail -n50
 12022:20131229:001048.278 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3003000000000962]
 12022:20131229:001048.278 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3003000000000963]
 12022:20131229:001050.138 NODE 10: sending configuration changes to slave node 30 for node 30 datalen 10
 11954:20131229:001102.985 NODE 10: Received configuration changes from slave node 30 for node 30 datalen 54
 11954:20131229:001318.859 NODE 10: sending configuration changes to slave node 30 for node 30 datalen 10
 12027:20131229:001332.303 NODE 10: Received configuration changes from slave node 30 for node 30 datalen 354
 12027:20131229:001510.907 NODE 10: sending configuration changes to slave node 30 for node 30 datalen 122748
 11957:20131229:001536.854 NODE 10: Received configuration changes from slave node 30 for node 30 datalen 10
 11957:20131229:001648.072 NODE 10: sending configuration changes to slave node 30 for node 30 datalen 10

Then I did similar working snap shot, attached:

# echo 'select * from node_cksum where tablename like "applications" and recordid like "300%"' | mysql zabbix -uzabbix -p > applications_cksums_working

If we compare them - we will see they have noticeably different data structures.
Zabbix server uses these data to construct SQLs. So incorrect data -> incorrect SQLs.

Probably it possible to try to reproduce it on correctly(together) and not correctly upgraded nodes, but I'm not really sure I need it.

Questions ?

same issue
query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=200200000002707]

Migration from 2.0.4 -> 2.2.1

First Master, then Node migrated
both are on 2.2.1

Same issue here.

Upgraded my zabbix servers and nodes to 2.2.1 and got messages

1628:20140203:155439.140 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000461]
1628:20140203:155439.141 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000462]
1628:20140203:155439.141 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000463]
1628:20140203:155439.141 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000464]
1628:20140203:155439.142 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000465]
1628:20140203:155439.142 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000466]
1628:20140203:155439.142 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000467]
1628:20140203:155439.142 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000468]
1628:20140203:155439.143 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000469]
1628:20140203:155439.143 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000470]
1628:20140203:155439.143 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000471]
1628:20140203:155439.143 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000472]
1628:20140203:155439.143 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000473]
1628:20140203:155439.144 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000474]
1628:20140203:155439.144 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000475]
1628:20140203:155439.144 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000476]
1628:20140203:155439.144 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000477]
1628:20140203:155439.145 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000478]
1628:20140203:155439.145 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000479]
1628:20140203:155439.145 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000480]
1628:20140203:155439.145 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000481]
1628:20140203:155439.146 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000482]
1628:20140203:155439.146 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000483]
1628:20140203:155439.146 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000484]
1628:20140203:155439.146 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000485]
1628:20140203:155439.147 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000486]
1628:20140203:155439.147 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000487]
1628:20140203:155439.147 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000488]
1628:20140203:155439.147 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000489]
1628:20140203:155439.147 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000490]
1628:20140203:155439.148 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000491]
1628:20140203:155439.148 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000492]
1628:20140203:155439.148 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000493]
1628:20140203:155439.148 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000494]
1628:20140203:155439.149 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000495]
1628:20140203:155439.149 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000496]
1628:20140203:155439.149 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000497]
1628:20140203:155439.150 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000498]
1628:20140203:155439.150 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000499]
1628:20140203:155439.150 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000500]
1628:20140203:155439.150 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000501]
1628:20140203:155439.150 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000502]
1628:20140203:155439.151 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=1501500000000503]

Every approx 5 min in the logs.

any solution ?

I had the same problem with "Unknown column 'type' in 'field list'".
I've found solution on https://www.zabbix.com/wiki/doc/troubleshooting/index
I've had to do:
stop child
truncate table node_cksum; on child
start child

remark: master database doesn't had any data from child earlier.

I have a 1 zabbix father and 7 child.
I upgraded from version 2.0.8 to 2.2.1 and I'm also with this error, since I performed a truncate in node_chksum unsuccessfully.
I upgraded from version 2.2.1 to 2.2.2 and continue with the problem.

My database is postgresql 9.2.4.

Same problem 2.0.8 to 2.2.2

Upgraded 2.0.8 ==> 2.2.2 Facing the same problem

First Upgrade Child Node then Upgraded Master Node.

29523:20140730:194542.347 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=200200000000355]

Best regards,
Md. Mansoor

Same problem here, zabbix 2.2.5 on centos 6.5.
22681:20140908:111857.097 [Z3005] query failed: [1054] Unknown column 'type' in 'field list' [select type from applications where applicationid=3001000000000001]
Got similar on every node...(just app id different)...

given that nodes have been removed since 2.4, this issue is unlikely to be looked in -> closing