Ben Hamilton

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 […]

Posted in SQL, SugarCRM | Tagged , | Comments Off on When SugarCRM Case Closed

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 […]

Posted in *nix, SQL, SugarCRM | Tagged , | Comments Off on Tracking down long running queries in SQL

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 […]

Posted in *nix, SQL | Tagged , | Comments Off on How long running are those queries?

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. […]

Posted in *nix, SQL | Tagged , , , , | Comments Off on How to backup SugarCRM from the command line

Find Duplicate Contacts in SugarCRM

Find duplicate contact records, by contact name, in SugarCRM using MySQL.

Posted in SQL | Tagged , , | Comments Off on Find Duplicate Contacts in SugarCRM

SQL Query ORDER BY last_name, then first_name

This snippet of MySQL code shows how to concatenate two fields into one column, also shows how to reverse the concatenation and order by last name then first name.

Posted in SQL | Tagged , | Comments Off on SQL Query ORDER BY last_name, then first_name

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 = […]

Posted in SugarCRM | Tagged , , , | Comments Off on MySQL – Counting how many rows have a particular value

What version of MySQL is running?

On a CentOS 6.x linux box, typing the following will show the MySQL version mysql -v or if not the root user, then mysql -u root -p -v (Which says user the root user, prompt for the password, then get the version).

Posted in SQL | Tagged , | Comments Off on What version of MySQL is running?

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 […]

Posted in SugarCRM | Tagged , , , | Comments Off on How to convert UTC time to current timezone in MySQL

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 […]

Posted in SugarCRM | Tagged , , | Comments Off on MySQL snippet for past and next 6 Months

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 […]

Posted in General | Tagged , , , | 1 Comment