No-SQL for loggin my db system?

K

klabacita

Hi people.

I have some questions that would like share and receive inputs to help me chose the right path, I had learn a lot in this bsd forum.

We got a ERP system that have some issues that we want to attack, the current system use a db, it has users, acl, no issue here, the issue is that is not logging what the users are doing.

I mean if User1 access purchase, open a PO change some data, save done.

I cannot know what data he changes, this for us is very important.

We have access to the db, them are moving screens to the web with php and we can do a hack and start creating a logging system for the ERP.

I hear about no-sql systems that can handle a lot info and a lot of sites start using them to analise users data and learn better their type of users, this system are being use where a RDBMS is slow, managing a lot of info.

Something cross my mind them, can one of this system help me?

Now, latter I stop and watch what FreeBSD, Linux do with the logs, syslog handle this in text files:
Code: 
/oaknew/bin/modulos/datacollector/data/abc_workord.php HTTP/1.1" 200 29 
"http://192.168.2.48/new/bin/modulos/datacollector/datacollected.php" 
"Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.23) Gecko/20110921 
Ubuntu/10.04 (lucid) Firefox/3.6.23"

one line, we install a syslog analizer and we got reports for apache that tell me what the users are doing.

Now, lets go back to my issue, if user1 access the system, purchase module, open the PO screen, input a PO, make some changes, save done.

My app will apply the changes to the db, them for example, grab the whole fields, concatenate them create a string like this:

Code: 
ip, user1,2011-12-08 15:58,screen12,ponum,totalamount,4 items,delivery-date

The user change the delivery date, I don't care about indexes, I want to know the whole data, latter with a log analyzer I can get a report about what had happen to the screen12, I don't care about what tables, I care about the screen number data.

Now, in the db I can create a table called, LogPurchase, there some fields and one varchar to hold the whole string I want to log.

But a lot of users will be updating, deleting, adding data to the database at the same time.

If my idea is good, MySQL can do the trick and good performance or is better to use a no-sql type system? if this is my path which could be, I want to start working here.

Or how can I attack this log issues and not affect my system performance?

Any input will be appreciated, I hope to be clear and if u need more details about my idea let me know please, thanks :)
 
Build the logging functionality into the software you have.

You can turn on logging on MySQL but this will only log the full SQL query. Since all queries come from the web application you won't be able to see which user does what.
 
Hi SirDice.

Yes, MySQL have log full querys but is not recommended to turn on this feature in production and the DB for the ERP is not MySQL.

The idea is to use other software to help us with this problem.

We start using text files to log this, but exist some already system that could handle this?

Thanks!!!
 
I once built a system which logged everything by using triggers on the tables.

I don't understand why you would like to use a "NoSQL" (== vague buzzword) database for this specific task...
 
There is an endless debate about NOSQL versus so-called traditional databases (sometimes referred also as YESSQL). While you are free to choose the implementation you want, I would remain on the well know world of traditional-structured-reliable-transactional-sql databases.
The only reason for your specific case to use a NOSQL database is that you want to log different type of data for the same user at the same time without having to spend time designing how this data will be later used. And this is the point: what do you need? Statistical information? Data replication? On Time Recovery?
I guess you are in the first scenario, you need to know what your users are doing.
I would avoid any database logging facility, because it could generate too much data that you are unable to read. Even if a good database should be able to log each query attached to a process, and you should be able to know the user that has logged into such process, so you can even collect raw queries. But then you would have to deal with triggers, that can nullify or modify your queries...so it is going to become a nightmare very soon. Database query loggin has not meant to be an auditing tool.
So you have to build your logging facility into your application. Doing with triggers, doing with stored procedures, doing with code, it does not matter, that is the right place to use for logging since it is there the place you know what values has your data to you. And even if you go to NOSQL you have to implement it, so I don't get your doubt.

A lot of applications are happy just to see information about who and when did a record change. This can be easy, since it requires 4 fields (two automatic timestamps). If you need also to keep historical data, a very easy solution is to place a visible flag in each table, and on INSERT/UPDATE use a trigger to mark not visible all the past rows (for that user, order, article, ...).
Just consider also using a standard logging facility not tied to the database (text files). How many transasctions are you going to manage? It could be the filesystem can handle without too much problems.
 
You must log in or register to reply here.
Back
Top