Use powercfg to set Windows power states from a script or command line.
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.
| 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.
By default it’s in the same base folder as the SugarCRM application files. But on occasion we’ll move it. Quickly find it’s location by looking in the
config.php file. The two relevant lines are:
‘log_dir’ => ‘/var/log/sugarcrm/’,
‘log_file’ => ‘sugarcrm.log’,
If you’re at the command line, then this makes it easy:
grep 'log_dir' config.php
From the command line
find / -name 'index.html' 2>/dev/null
This will find and display all occurances of
index.html and all the errors will not display.
Testing 1, 2, 3
Nothing to see, move along.
At the MySQL commandline, you can enter
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.
I use Day One to journal. There is a service that creates a new Day One entry from the selection, but it didn’t have a hotkey associated with it. So I’ve set one up.
First (optional) step is to use to make your CAPS LOCK key useful. Big thanks to Brett Terpstra for that, I use that for loads of other shortcuts.
Next step is to add a shortcut for the New Day One Entry With Selection using the instructions to create keyboard shortcuts in the Apple knowledge base.
I used CAPSLOCK D as the hotkey to send whatever I’ve got currently selected to Day One as a new entry, works a treat.
Note: this is ^⇧⌥⌘–D using the optional first step, so it is the same as pressing Control, Shift, Option, Command, D all at the same time.
I just needed to compress and encrypt some files on Mac OS X. The following command does that:
zip -ejr TARGET SOURCEFOLDER
This will ask for a password (twice to confirm it), encrypt and compress the sourcefolder and put it into a file named target (and auto add the .zip extension).
To unzip it do this:
unzip SOURCE -d TARGETFOLDER
This will ask for the password, decrypt and uncompress it into the targetfolder.
This is a time limited deal, if you go to Cloudatcost.com and sign up, use the coupon code ‘benhamilton’ to get 20% off your order.
I have a VPS hosted with Cloudatcost myself and I know a few others that do as well, so I can vouch for their service.
This article here describes how to get Hazel to run rules in sub folders, very handy. I’m using this to move any screenshots taken on the iPhone out of the photo folder into a separate folder.
Advanced Bash-Scripting Guide. The name says it all.
Here’s how to enable a checkbox to be updated enmasse.
First create a new extvardef.php file containing the following:
Then put that file into at the following location
Of course, you’d want to check any existing files in the folder to make sure none of them already deal with that field.
From the linux command line, these commands let you backup and restore a SugarCRM database.
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…
tar -zcvf CRM-BACKUP-FILES.tar.gz FOLDER-TO-BACKUP/
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.
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:
- Diagnostic Tool
- db schema
- Download that file
Then for the restoration…
Restore ﬁles (to current folder):
tar -zxvf CRM-BACKUP-FILES.tar.gz
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:
For when you’re trying to explain what the load average figure means (which you get from running either top or w from the linux command line) this link will help explain it easily. Easier than spending 10 minutes talking about it.
I wrote up how I’m numbering tasks in OmniFocus, but I’ve now modified the Keyboard Maestro recipe so it’s just a touch faster.
Here’s a screenshot:
I’ve also since created a separate recipe in Keyboard Maestro for each key Project I have in OmniFocus, each with it’s own .txt file that it refers to. This lets me do
and it’ll spit out “(Admin #7)” or if I type
it’ll spit out “(CRM #15)”.
I’m using OmniFocus 2 on my MacBook Pro and wanting to ‘number’ tasks because at the end of each week, I print a PDF of the tasks completed and outstanding for a particular client. Task numbers make it much easier for the client to reconcile the work I’ve done with the lists they use.
The problem is that OmniFocus doesn’t have a ‘task number’ function.
Keyboard Maestro and a shell script to the rescue. This allows me to get the next sequential task number. Now all I do is type
and it expands out to the next available task number.
In a nutshell I use the shell script to get a variable from a text file (which is the last task number I used), and increment it. Keyboard Maestro is wrapped around this to both trigger it and make it look pretty.
The shell script itself is quite basic, any improvements you can suggest, please do.
Here is a screenshot of the Keyboard Maestro recipe (edit: I’ve since modified this recipe to be a little faster)
This relies on you having a text file named
in your home folder.
The format of this file is just a single line of text as follows:
Find duplicate contact records, by contact name, in SugarCRM using MySQL.
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.
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_two appear in
field_name in the table
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 (
Hat tip to this stackoverflow answer by eisberg.
Edit: 2014-05-26-21h53m put the final code snippet into a ‘Gist’ on GitHub.