Why you don't use autoincrement in databases?

[tsx]

Do you have any real reasons for using a separate table for ids and not using autoincrement?
I'm trying to write my own frontend and updating `ids` table and assigning new ids is someting that database engine should do, not my application.

[exkg]

... mainly to work with nodes I think ....

So .... are you writting your own frontend ?

A good start/idea is learn about ZABBIX API:

http://www.zabbix.com/documentation/1.8/api


[]s,
Luciano

[tsx]

I did not tried using distributed monitoring, but adding new hosts and items is centralized anyway, isn't it?
I know about api, but i write my frontend using Ruby On Rails, so i get api like this in couple of hours for free by just writing 30-50 lines of code describing classes and their relations.

[richlv]

in a distributed setup you can make changes at master or child node, and they are later synchronised.

as for api, there's currently a ruby lib for accessing it - not sure whether that helps any. it is also used by the console tool, zabcon (http://trac.red-tux.net/wiki/zbx_api/interactive)

[tsx]

Oh, I see...
Does zabbix server (not frontend) try to insert something into those tables if not using autodiscovery?
I could modify db schema to use autoincrement if it won't break server. That would make default php frontend not usable and distributed setup impossible, of course. However I think it's possible, but not easy, to patch php frontend to use autoincrement instead, so I can use both.

[richlv]

network discovery, auto-registration would make zabbix server insert.
not sure whether other cases exist as well.
in general, making such invasive modifications is quite risky

[tsx]

Ok, thanks for help.

[nelsonab]

Did I hear someone mention Ruby and Zabbix... :-D Let me know if I can be of assistance.

Auto increment will break the Distributed Setup. When you move to a DM setup the IDs change with the left most digits being the node and the rest being the ID number within the node. There are some limited cases where auto increment may be useful, but that use case is too small to be effective.

[tsx]

I'll post an announce in this forum when my frontend will be in at least useful state. It will be released as open source, I think.
Yes, I understand that autoincrement breaks DM, but managing ids by hand is so painful, especially when activerecord already does most of dirty work for you automatically. But imho most of zabbix installations are just single servers and don't use even proxies, so my decision is to simplify code a lot by using autoincrement and sacrificing possibility of using distributed setup with my frontend.
Of course some migrations will be created to convert database from default setup to my frontend's schema and back to normal without losing data.

[nelsonab]

Oooh... You might be in for some pain with Active Record and the Zabbix DB, I've been down that road. Granted what I was trying to do was a little more advanced and crazy than AR was designed for (working with views and stored procedures). I still think making any major modifications to the DB schema are not the best idea in the long run. You also run the risk of the Zabbix server process having an undesired side effect by making a DB schema change. True it's unlikely but very possible.

[tsx]

The only thing I'm going to change in the schema for now is moving the id assigning task to database engine(btw, I use mysql, didn't tried postgresql). Other problems like non-conventional column/table names are solved by explicit statements in class definitions. Just a few more lines per class and no magic.
As for side effects, I'll try to test server throughly on modified database.
At last, that's just an experiment, and nothing would blow up if something goes wrong :-)
I've been successfully running and restarting server on autoincrement database for about a day already (single server without autodiscovery or distributing) and even php frontend works fine if I don't forget to update `ids` table after using activerecord api for adding something.

[richlv]

But imho most of zabbix installations are just single servers and don't use even proxies, so my decision is to simplify code a lot by using autoincrement and sacrificing possibility of using distributed setup with my frontend.

...and users of your frontend get a high chance of being screwed when they want to move to the next version but you have decided to abandon this project.
nelsonab also has a very good point - you would also have to change server a lot, and that not only can, but will get tricky. you would have to track every fix, every change, figure out how to port it, then port and maintain that.
i'll make a wild guess that implementing id handling will be way, way easier. it actually doesn't seem that hard to me, as a non-coder (i've scripted a bit around them )

[tsx]

My goal is to provide an alternative frontend, not to fork the server. I want to use default server without modifications more than database schema. My frontend just wouldn't allow configuration of DM or autodiscovery.
All that users will have to do to switch from rails frontend to php is to apply backward migrations(that's one command from shell) so their database will be restored to normal state. Uninstalling frontend and reverting schema will be as easy as installing it.

[nelsonab]

I think it would be great to see the project and perhaps help out a little. I just think going with auto incrementing ID's is a bad idea. Especially if you're going it to the history table.

Another heads up is you might run into some performance issues with Active Record and the Zabbix db. The Zabbix data sets can be huge and often involve several joins. Often this is faster if you do this with a Select statement than from data joins at a higher level.

An idea might be to impliment your own root class which describes things like items and so forth and encapsulates grabbing those items from the database using MySQL select statements (including joins etc). Yes this might be heavy on memory but I think that's a fair trade off for speed.

[tsx]

I'm using autoincrement only on tables that can be modified in the frontend and cannot be modified by the server. You don't edit history in the frontend, do you? ;-)

"Premature optimization is the root of all evil" © Donald Knuth
At first i should just make it work. Also, I can use raw sql in activerecord when I need it.