Tag: mysql

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.

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.

At the MySQL commandline, you can entershow 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.

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

First up, the backup…

Backup ﬁles:

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:

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.

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.

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.

“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.