SugarCRM SDLC – Monitoring Database Changes

sugarcrmdevelopers —  August 29, 2012

When talking about the SugarCRM Software Development Life Cycle (SDLC), the bane of the experience is always the process of analyzing database changes and porting them to the new system. How do we know what tables changed in a relevant want for our new fields, or our configuration changes?

Different changes to the Sugar system affect different aspects of the database (naturally), and we can narrow-down core system changes to a few key tables:

  • fields_meta_data houses information about custom fields. Any custom field you create will be registered here, and changes made to these fields via vardefs will cause updates or inserts to this table during the Repair/Rebuild process. Watch this field for data, INSERT and UPDATE commands.
  • *_cstm tables keep the columns for custom fields. For custom fields on the Accounts module, you’ll find the table accuonts_cstm. Watch these tables by looking for CREATE and ALTER statements
  • config holds often-important system data; watch for INSERTs and UPDATEs to this table.
  • tables relating to users, teams, workflows, and ACL (security roles) should be watched for INSERTs and UPDATEs

There are several ways to monitor for such queries. I’ll outline two.

  1. Sugar Logger. By setting the SugarCRM Logger Level to INFO or DEBUG, Sugar will log all queries to the database. This includes the above important inquiries as well as any INSERT or UPDATE statements for processing the real data in your system. Additionally, most INSERT and UPDATE queries result in not one but three queries being sent to the log: one in preparation, one in save, and one by the query logger (from what I can tell). These queries will all be stored along side typical info/debug statements through the system code and will be kept in sugarcrm.log (rolling to sugarcrm1.log and so on).
    • Advantages: plain-text, easily accessible and searchable with text editors and parsing programs. 
    • Disadvantages: rooting through similar data that is repeated and through so many debug statements.
  2. mysqlbinlog. If you have binary logging enabled in your MySQL server, any and all queries to the server are recorded in your default data directory. Use mysqlbinlog to root through these log files, because they are themselves binary files (not plain-text). As far as I know, mysqlbinlog is the only tool around that will open and go through these files, and it will only do so one at a time (that can be good or bad).
    • Advantages: only stores SQL queries, so the only filtering is to remove the INSERT/UPDATE statements from non-desirable tables.
    • Disadvantages: these binary files are difficult to access and process without command-line mysqlbinlog tool

So what is better? What I would really like to see is an IDE-integrated tool that would open the binary MySQL log files and allow me to search through via regular expressions. But that doesn’t exist. I subside for now by rooting through the sugarcrm.log file. I find that I do well enough if I watch for changes during my development session, or at the end of it, and that my logs don’t roll within an hour or two’s work unless I’m doing something to incur tremendous data changes. I also have better success by limiting the size of the log files to 3MB instead of 10, so that my software can better handle and search through the data.

I have yet to develop a “master” regular expression to find all of my key fields and changes. I often simply create a field in studio then search the log for the name of that field, looking specifically for the insert statement into fields_meta_data and the alter table statement changing the module’s custom-fields table.

But here’s my first attempt. I’ll spell it out with some indentation and spacing, then paste it the minimized version below it. Note that I use this Regex with PHP Storm and that different editors sometimes have slightly different syntax for regex (this doesn’t seem to work correctly in Notepad++).

	((INSERT into )|(UPDATE )|(DELETE from ))
	(((alter)|(create)|(drop)) table)

(?<=QUERY:)(((INSERT into )|(UPDATE )|(DELETE from ))((fields_meta_data)|(config)|(acl_actions)|(acl_roles_actions)|(acl_fields)|(acl_roles)|(acl_roles_users)|(users)|(teams)|(team_memberships)|(email_templates)|(workflow)|(workflow_actions)|(workflow_actionshells)|(workflow_alerts)|(workflow_schedules)|(workflow_triggershells)|(scheduler)|(campaigns)).+)|((((alter)|(create)|(drop)) table)).+