When SugarCRM Case Closed

Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed.
Thus, the query below will return the date the case was transitioned to ‘Closed’ provided the cases.status field is being audited.

select
  CONVERT_TZ(cases_audit.date_created,'+00:00','+10:00') as 'date_closed',
  accounts.name as 'account',
  cases.case_number,
  cases.name as 'case_id'
from
  cases_audit
  join cases on cases.id = cases_audit.parent_id
  join accounts on accounts.id = cases.account_id
where
  cases_audit.field_name = "status"
  and cases_audit.after_value_string = "Closed"
order by
  accounts.name asc,
  cases_audit.date_created desc;

Note, you might get more then one row returned per case if the case was re-opened, also my code above assumes a timezone of UTC+10

I happen to be in the UTC+10 timezone :).

Tracking down long running queries in SQL

I’ve had a client whose Linux Server (CentOS) gradually gets slower and slower, then falls over.

By logging into the MySQL command line, we were able to see that there were some long running queries that never end.

show processlist;

returns


| Id | User | Host | db | Command | Time | State | Info |
+———+———-+——————-+————–+———+——+————–+——————————————————————————————————+
| 2322386 | ssgadmin | 10.21.1.149:58526 | sugarcrm_ssg | Query | 2202 | Sending data | SELECT IFNULL(hr_humanresources.last_name,’’) hr_humanresources_last_name ,l3_cstm.verification_c l3 |

The key part here is that hr_humanresources_last_name has an underscore between the table name when it should be a period i.e. hr_humanresources.last_name. Knowing the table name was enough to tell me it was caused by something to do with SugarCRM that’s running on that server. But we don’t yet know what action caused these queries.

Knowing it was 2202 seconds ago from the time we ran the query we are able to pinpoint a time the action occurred. Looking through the SugarCRM log files did turn up that the error was often caused by a single user, but didn’t show up anything to help us figure out exactly what was the causal issue. Talking with that user, getting them to do what they’d done at that time didn’t turn up anything, the error wasn’t reproducing on demand.

So I turned to looking in the tracker table in the SugarCRM MySQL database for entries around the time of the error. Turns out that there is a Dashlet being loaded, that Dashlet uses a Report.

Each time I load the Dashlet or run the Report I get a corresponding long running query turn up. We’ve found our culprit, and we’re now able to recreate the report.

How long running are those queries?

At the MySQL commandline, you can enter
show processlist;
and see what processes are running.
If you’re tracking down some problematic code and you’ve got a query (or set of queries) that don’t end, consume CPU cycles and generally bog everything down to a crawl, it can be helpful to know what time you’ve run the command above. Thusly, I now run this:

show processlist; select now as current_date_time;

and it rather nicely tells me the current date/time on the MySQL server, using the Time column from the processlist, you can work out a tad more accurately when a query started, giving you a starting point to look in other logs.
Now those long running sql queries are easier to track down.

How to backup SugarCRM from the command line

From the linux command line, these commands let you backup and restore a SugarCRM database.

Update 2016-04-26: Just saw this SugarCRM KB Providing a Backup Without Sensitive Data which is really useful. Thanks Jared.

Firstly, for a proper back up of SugarCRM you’ll need two files, one containing the application files, one containing the SQL database.

First up, the backup…

Backup files:

Change to the folder you want to backup, then…

tar -zcvf CRM-BACKUP-FILES.tar.gz .

Edit: if you get an error ‘Permission Denied’ you may be trying to write to a folder you don’t have permission for, instead try writing to ~/CRM-BACKUP-FILES.tar.gz and it will likely work.
Edit: see also this StackOverflow article.

Backup sql (empty copy of the database):

mysqldump -u USERNAME -p -–no-data DATABASENAME > CRM-BACKUP-SQL.sql

Backup sql (with the data) :

mysqldump -u USERNAME -p DATABASENAME > CRM-BACKUP-SQL.sql

Then you can ‘tar’ the .sql file with

tar -zcvf DATABASENAME-mysql.tar.gz DATABASENAME-mysql.sql

Backup just a single table

mysqldump db_name table_name | gzip > table_name.sql.gz

If you only want the database schema, then in the SugarCRM web application you can do the following:

  • Admin
  • Diagnostic Tool
  • db schema
  • Download that file

Then for the restoration…

Restore files (to current folder):

tar -zxvf CRM-BACKUP-FILES.tar.gz

Restore sql:

mysql -u USERNAME -p DATABASENAME < CRM-BACKUP-SQL.sql

Restore just a single table

gunzip < table_name.sql.gz | mysql -u username -p db_name

These SugarCRM Knowledge Base articles may also be of use:

MySQL – Counting how many rows have a particular value

Working on a SugarCRM dashboard today, and I needed to count how many records had one value and how many records had a second value. I then wanted to know what percentage the first value was of the total. So there’s a bit of MySQL code that helps make this easy to do.


SELECT
  SUM(IF(field_name = 'value_one',1,0)) AS 'Value One',
  SUM(IF(field_name = 'value_two',1,0)) AS 'Value Two'
FROM table_name

What this does is counts up how many times value_one and value_two appear in field_name in the table table_name.

You can then take this a step further, if you wanted to, and I did…

you can use a line like this within your query:

This gives you the percentage that value_one is of (value_one + value_two).

Hat tip to this stackoverflow answer by eisberg.

Edit: 2014-05-26-21h53m put the final code snippet into a ‘Gist’ on GitHub.

How to convert UTC time to current timezone in MySQL

Using SugarCRM (this client is using Enterprise 6.5.15) and we want the last two hundred items in the tracker table. The problem is that the `tracker`.`date_modified` field is in UTC time, not the local time zone, which is +10:00.

The solution is to use the CONVERT_TZ MySQL command, as shown in the example below. It will convert between timezones for you.


SELECT `users`.`user_name` AS "User",
`tracker`.`module_name` AS "Module",
`tracker`.`item_summary` AS "Summary",
`tracker`.`action` AS "Action",
CONVERT_TZ(`tracker`.`date_modified`,'+00:00','+10:00') AS "Modified"
FROM `users`
INNER JOIN `tracker` ON
`users`.`id` = `tracker`.`user_id`
WHERE `tracker`.`deleted` = 0
ORDER BY `tracker`.`date_modified` DESC
LIMIT 200

MySQL snippet for past and next 6 Months

Spent some time today working with DATEDIFF, DATE_ADD and all kinds of horrid looking code before realising that to do a MySQL query to get records where the date is in the past or the next x months all I need is the following.


WHERE t_position_cstm.review_date_c <= DATE_ADD(DATE(now()), INTERVAL 6 MONTH)

You can change the 6 to whatever appropriate value you have, and you can change MONTH to a variety of other values, for me the two I used today was MONTH and DAY.

I’m using this on a SugarCRM MySQL database with a custom module of Position (the actual table name is t_position).

Crises conceal opportunities

Each problem has hidden in it an opportunity so powerful that it literally dwarfs the problem. The greatest success stories were created by people who recognized a problem and turned it into an opportunity.” – Joe Sugarman

I was sent this quote Tuesday this week. It is appropriate on many levels, but particularly so with regard to this web site.

You see, my database, which contains all my blog posts since I can’t remember when was miraculously destroyed. Along with it’s backups. That to me is a CRISIS.

Note to self: paranoia is healthy.

I first began blogging shortly after Chris Pirillo recommended using ‘BLOG’, a windows app that would do scheduled ftp uploads to a web site. I became a Textpattern fan, then switched to WordPress. My database contained all my posts from Textpattern and WordPress, and a whole bunch more.

But as the quote (and title) suggest, crises conceal opportunities. The opportunity here is a fresh start. No ties. A new commitment.

So, here begins not a new chapter, but a new book, the old one is no longer in print.