Bash script to upload logs to Google Cloud Storage

The following Bash script is using the Google Gsutil tool to upload hourly server logs (statistics) to Google Cloud. It support sed and grep the logs prior uploading to Google Cloud. The script rename the log file at every stage for better handling errors.

# Written by Ilan Hazan
# This bash script will upload your hourly created logs to Google Storage Cloud using gsutil (Python application that lets you access Google Cloud Storage from the command line).
# It assumes that your logs path has the following pattern: path + date (e.g. /the/directory/contains/logs/20140530/).
# Takes only the logs that their last modified time bigger than 61 minutes. 
# Support sedding and grepping the logs prior uploading to Google Cloud    
# It renames the files so several scripts can run simultaneously 
###### NOTES ######
# need to call it with ./ >> /tmp/googleCloud.log 2>&1 in order to redirect stderr to the log file 
# database credentials
# the subject of your log. Assuming there are many log types
# run the script for logs 
Date="2 hour ago"
if [ $# -eq 3 ]; then
# there is a possibility to SED the logs. If needed, fill in the SED string 
# there is a possibility to GREP the logs. If needed, fill in the GREP string (regular expression)
##### SCRIPT GLOBALS #####
# Google BigQuery not allow files bigger than that size
##### FUNCTIONS ##########
# Output logs to Stderr with date 
# $1 output string
# Return: None
function myLogger(){
	echo "$(date) $1 " >&2
function fail() {
    echo "exited with level $exitcode, error was '$@'" >&2
    exit $exitcode
# remove last extension of a file name
# $1 the file name
# Return: the file name without last extension
function removeLastExtension(){
	local fileName=$1
	echo ${fileName%.*}
# Making sed to the file. If needed creating file with extension *.sed
# $1 the file name
# $2 sed string
# Globals -
#      SedExtension: the extension given to a files that passed sed
# Return: the file name to continue working on. In case of empty Sed String the suffix will remain the same. Otherwise the the name will be *.sed.
function sedTheFile(){
	local fileName=$1
	local sedString=$2
	local fileNameWithoutExtension=$(removeLastExtension $fileName)
	local retValue=""
	if [ -n "$sedString" ]; then
		myLogger "sed for $fileName is not empty"
		sed "$sedString" $fileName > "$fileNameWithoutExtension.$SedExtension"
		myLogger "$fileName finished sed"
		myLogger "sed for $fileName is empty"
	echo "$retValue"
# Making grep to the file. If needed creating file with extension *.greped
# $1 the file name
# $2 grep string
# Globals -
#      GrepExtension: the extension given to a files that passed sed
# Return: the file name to continue working on. In case of empty Grep String the suffix will remain the same. Otherwise the the name will be *.greped.
function grepTheFile(){
	local fileName=$1
	local grepString=$2
	local fileNameWithoutExtension=$(removeLastExtension $fileName)
	local retValue;
	if [ -n "$grepString" ]; then
		myLogger "grep for $rawFile is not empty"
		LC_ALL=C grep "$grepString" $fileName > "$fileNameWithoutExtension.$GrepExtension"
		myLogger "$fileName finished grep "
		myLogger "grep for $fileName is empty "
	echo "$retValue"
# extract the hour from the file name. The hour located between the first two dots. 
# $1 the file name
# Return: hour
function extractHourFromFileName(){
   echo "$1" | cut -d'.' -f 2
# Validate that file size is smaller than max configured. Fails if not!
# $1 the file name
# $2 max file size  
# Return: None. Fails upon failure!
function validateFileSize(){
		local fileName=$1
		local maxFileSize=$2
		local compressedFileSize=$(stat -c%s "$fileName")
		myLogger "File "$fileName" size is: $compressedFileSize. max allowed: $maxFileSize"
		if [ $compressedFileSize -gt $maxFileSize ]; then
			myLogger "Error: File "$fileName" is larger than $maxFileSize: $compressedFileSize "
			fail 1 "File "$fileName" is larger than $maxFileSize, please investigate"
# get file details from Cloud. 
# $1 the file name including path
# Globals:
#     GSUTILPath - the path to gsutil (Google application for uploading files)
# Return: 0 upon suceess
function getFileDetailsFromCloud(){
	$GSUTILPath list -l $1
# Validate that file uploaded successfully. Fails if not!
# $1 the file name including path
# Return: None. Fails upon failure!
function validateFileLocatedInCloud(){
	local fileName=$1
	local loopTries=$2
	local sleepTime=$3
	myLogger "going to validate file exist in Cloud: $fileName "
	getFileDetailsFromCloud $fileName
	local ReturnedResponse=$?
	local var=0
	while [ $ReturnedResponse -ne 0 -a $var -lt $loopTries ]; do
			myLogger "Error: $fileName is not located in Google Cloud as it should be. Response code $ReturnedResponse for $var time" ; date
			sleep $sleepTime
			getFileDetailsFromCloud $fileName
	if [[ $ReturnedResponse -ne 0 ]] ; then
			myLogger "Error: file does not exist in Cloud: $fileName"
			fail 1 "file $fileName is not exist in cloud"
			myLogger "Validated file exist in Cloud: $fileName "
# Upload file to Google cloud
# $1 the file path
# $2 the file name
# $3 loop tries - number of tries before declaring failure
# $4 sleep time - sleep between tries
# Globals:
#     GSUTILPath - 
# Return: None. Fails upon failure!
function uploadFileToCloud(){
	local filePath=$1
	local fileName=$2
	local loopTries=$3
	local sleepTime=$4
	myLogger "going to run: $GSUTILPath cp $fileName $filePath "
	$GSUTILPath cp $fileName $filePath
	local ReturnedResponse=$?
	local var=0
	while [ $ReturnedResponse -ne 0 -a $var -lt $loopTries ]; do
			myLogger "Error: $fileName Fail uploading to Google Cloud $filePath with response code $ReturnedResponse for $var time" ; date
			sleep $sleepTime
			$GSUTILPath cp $fileName $filePath
	if [[ $ReturnedResponse -ne 0 ]] ; then
		myLogger "Error: $fileName Fail uploading to Google Cloud $filePath with response code $ReturnedResponse after $loopTries times"
		fail 1 "could not upload file $loopTries to cloud, please investigate"
######### DEFAULTS ###########
if [[ -z "$Destination" ]]; then
WorkingDir=$(date -d "$Date" +"%Y/%m/%d")
if [[ -z "$BQDate" ]]; then
	DateInTableName=$(date -d "$BQDate" +"%Y%m%d")
if [[ -z "$FileSizeBytesLimit" ]]; then
BaseDir="/the/directory/contains/logs/" # base directory
# note: CloudDir is used for creating BQ table name in other scripts (must be alphanumeric plus underscores )
for rawFile in $( find $BaseDir$WorkingDir -name "$FileName.*.log" -mmin +61 )
    if [ -f $rawFile ]
		mv $rawFile $rawFile.try
		myLogger ""
		myLogger "**Start working on $rawFile "
		# take row count orig
		rowCountOrig=$(wc -l < $TempFileName)
		myLogger "$TempFileName row count is: $rowCountOrig "
		# sed the file if needed
		TempFileName=$(sedTheFile $TempFileName "$SedString")
		# grep the file if needed 
		TempFileName=$(grepTheFile $TempFileName "$GrepString")
		myLogger "TempFileName is $TempFileName"
		# At this stage there can be orig file + sed? + grep?
		# take row count after grep (if was a grep)
		if [ -n "$GrepString" ]; then
			# will get wc without file name
			rowCountAfterGrep=$(wc -l < $TempFileName)
			myLogger "$TempFileName row count after grep is: $rowCountAfterGrep "
		# rename file to include server name + date + num rows
		myLogger "FileRowsDateAndServerName:$FileRowsDateAndServerName FileRowsDiffDateAndServerName:$FileRowsDiffDateAndServerName"
		mv $TempFileName $FileRowsDateAndServerName.csv
		# make gzip
		gzip $FileRowsDateAndServerName.csv
		mv $FileRowsDateAndServerName.csv.gz $FileRowsDateAndServerName.csv.$CloudExtension
		# Check if file is larger than 4G
		validateFileSize $TempFileName $MaxCompressedFileSize
		# extract the hour from file name
		FileHour=$(extractHourFromFileName $rawFile)
		# upload the file to Cloud
		uploadFileToCloud $UploadingDirectoryForThisFile $TempFileName $LoopTries $SleepTime
		# validating file exist in Cloud
		BaseTempFileName=$(basename $TempFileName)
		validateFileLocatedInCloud "$UploadingDirectoryForThisFile$BaseTempFileName" $LoopTries $SleepTime
		# Making cleanup
		# Need to save the original file with two numbers on its name: the orig rows and the final rows
		# Rename file so we know it is uploaded successfully to cloud
		if [ -n $rawFile.try ]; then
			rm -f "$rawFile.$SedExtension"
			rm $TempFileName
			mv $rawFile.try $FileRowsDiffDateAndServerName.$UploadedExtension.csv
			gzip $FileRowsDiffDateAndServerName.$UploadedExtension.csv
			# a case that there wasn't Sed and Grep
			mv $TempFileName $FileRowsDateAndServerName.csv.$UploadedExtension.gz

Google BigQuery API Client Example in PHP

Here is an example of how to use Google APIs Client Library for PHP in order for interact with Google BigQuery.
First you need to download Google BigQuery API client library for PHP (download from here).
Open it and use only the Google directory.
Before coding, please validate that you have a Google BigQuery account and that you have the credentials. In the following examples you need to supply:


PHP class to interact with Google BigQuery API

require_once 'Google/Client.php';
require_once 'Google/Auth/AssertionCredentials.php';
require_once 'Google/Service/Bigquery.php';
class MyBigQueryClass {
	var $project_id;
	var $application_name;
	var $client_id;
	var $service_account_name;
	var $service;
	function MyBigQueryClass($project_id, $client_id, $service_account_name) {
		$this->project_id = $project_id;
		$this->application_name = "BigQuery PHP Example";
		$this->client_id = $client_id;
		$this->service_account_name = $service_account_name;
	function getGoogleServiceBigquery(){
		return $this->service; // Google_Service_Bigquery
	/* @throws Exception Google_Service_Exception if operation fail
	function loadFileToBigQuery($uri_array, $schema_array, $dataset, $table_name){
		$loadConfig = new Google_Service_Bigquery_JobConfigurationLoad();
		$loadConfig->setDestinationTable($this->createDestinationTable($dataset, $table_name));
		$loadConfig->sourceFormat = 'CSV';
		$config = new Google_Service_Bigquery_JobConfiguration();
		$job = new Google_Service_Bigquery_Job();
		// this may throw exception: Google_Service_Exception
		$job = $this->service->jobs->insert($this->project_id, $job);
		return $job;
	function queryBigQuery($sql, $dataset, $table_name){
		$queryConfig = new Google_Service_Bigquery_JobConfigurationQuery();
		$queryConfig->setDestinationTable($this->createDestinationTable($dataset, $table_name));
		$config = new Google_Service_Bigquery_JobConfiguration();
		$job = new Google_Service_Bigquery_Job();
		$job = $this->service->jobs->insert($this->project_id, $job);
		return $job;
	function requestBigQuery($sql){
		$query = new Google_Service_Bigquery_QueryRequest();
		$response = $this->service->jobs->query($this->project_id, $query);
		$job_id = $response->getJobReference()->getJobId();
		$pageToken = null;
		do {
			$queryResults = $this->service->jobs->getQueryResults($this->project_id, $job_id);
		} while (!$queryResults->getJobComplete());
		return $queryResults;
	private function setGoogleServiceBigquery(){
		$client = new Google_Client();
		$key = file_get_contents('privatekey.p12');
		$cred = new Google_Auth_AssertionCredentials(
		if ($client->getAuth()->isAccessTokenExpired()) {
			$service_token = $client->getAccessToken();
		$this->service = new Google_Service_Bigquery($client); // Google_Service_Bigquery
	private function createSchema($schema_array){
		$fields = array();
		foreach ($schema_array as $field){
			$table_field_schema = new Google_Service_Bigquery_TableFieldSchema;
			$fields[] = $table_field_schema;
		// Create a tableschema
		$schema = new Google_Service_Bigquery_TableSchema();
		return $schema;
	private function createDestinationTable($dataset, $table_name){
		$destinationTable = new Google_Service_Bigquery_TableReference();
		return $destinationTable;

PHP example code for querying bigquery and get results

$PROJECT_ID = "0123456789";
$CLIENT_ID = "";
$myBigQueryObject = new MyBigQueryClass($PROJECT_ID, $CLIENT_ID, $SERVICE_ACCOUNT_NAME);
$sql = "select * from publicdata:samples.github_timeline limit 10";
	$queryResults = $myBigQueryObject->requestBigQuery($sql);
}catch (Google_Service_Exception $e) {
    echo "Google Service Exception: " . $e->getMessage();
	$fields = $queryResults->getSchema()->getFields();
	$fieldNames = array();
	foreach ($fields as $field) {
		$fieldNames[] = $field['name'];
	$row = $queryResults['rows'][0];

PHP example code for querying bigquery and insert result to new table

$PROJECT_ID = "0123456789";
$CLIENT_ID = "";
$myBigQueryObject = new MyBigQueryClass($PROJECT_ID, $CLIENT_ID, $SERVICE_ACCOUNT_NAME);
$dataset = "bigquery_dataset";
$table_name = "php_example";
$sql = "select * from publicdata:samples.github_timeline limit 10";
	$job = $myBigQueryObject->queryBigQuery($sql, $dataset, $table_name);
}catch (Google_Service_Exception $e) {
    echo "Google Service Exception: " . $e->getMessage();
    $status = new Google_Service_Bigquery_JobStatus();
    $status = $job->getStatus();
    //    print_r($status);
    if ($status->count() != 0) {
        $err_res = $status->getErrorResult();
$jr = $job->getJobReference();
$jobId = $jr['jobId'];
if ($status)
    $state = $status['state'];
echo 'JOBID:' . $jobId . " ";
echo 'STATUS:' . $state;

PHP example code for uploading a file from Google cloud to bigquery

$PROJECT_ID = "0123456789";
$CLIENT_ID = "";
$SCHEMA = array(array('name' => 'firstField', 'type' => 'string'),
				array('name' => 'secondField', 'type' => 'string')
$myBigQueryObject = new MyBigQueryClass($PROJECT_ID, $CLIENT_ID, $SERVICE_ACCOUNT_NAME);
$uri_array = array(
    // File paths here
$dataset = "bigquery_dataset";
$table_name = "php_example";
	$job = $myBigQueryObject->loadFileToBigQuery($uri_array, $SCHEMA, $dataset, $table_name);
}catch (Google_Service_Exception $e) {
    echo "Google Service Exception: " . $e->getMessage();
    $status = new Google_Service_Bigquery_JobStatus();
    $status = $job->getStatus();
//    print_r($status);
    if ($status->count() != 0) {
        $err_res = $status->getErrorResult();
$jr = $job->getJobReference();
$jobId = $jr['jobId'];
if ($status)
    $state = $status['state'];
echo 'JOBID:' . $jobId . " ";
echo 'STATUS:' . $state;

Useful links:

java.lang.NoClassDefFoundError when loading a class located in another project at Eclipse

It is useful to use classes that resides in another project in the Eclipse Workspace.
In order to avoid java.lang.NoClassDefFoundError error, perform the following steps:

Add the project that has the wanted classes to the Java Build Path

Properties-> java build path -> projects ->add your project that contain wanted classes
Check the added project in “order and export tab”

Deployment Assembly

Properties-> Deployment Assembly -> Add -> project -> choose your project that contain wanted classes
All left to do is Clean project and Start.

How to Build a RESTful Web Service with JAX-WS and Jersey on Eclipse

In order to build a RESTful API with Jersey on Eclipse, please use the following steps:

Create new Eclipse Dynamic Web Project

Open Eclipse, then File -> New -> Dynamic Web Project.
Name it “TestProject”.

Eclipse Dynamic Web Project

Eclipse Dynamic Web Project

Generating web.xml file

Generate a web.xml file IN WebContent/WEB_INF by:
Right click on the created project -> Java EE Tools -> Generate Deployment Descriptor Stub

How to generate wex.xml file

How to generate wex.xml file

Copy the following into the web.xml file

<!--?xml version="1.0" encoding="UTF-8"?-->
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="" xmlns="" xmlns:web="" xsi:schemaLocation="" id="WebApp_ID" version="3.0">
    <servlet-name>Jersey Web Application</servlet-name>
    <servlet-name>Jersey Web Application</servlet-name>

Download Jar files

Download the following three jar files and put them under WebContents\WEB-INF\lib folder.



Create Java Class file

Create java class file with name DefaultResource and with package

Java Class

Java Class

Put the following code in the new created class:

@Produces( {  MediaType.APPLICATION_XML } )
public class DefaultResource {
	public String get() {
        "<!--?xml version=\"1.0\" encoding=\"UTF-8\"?-->" +
        "" +
        	"Ilan Hazan" +
        	"Hello World" +

Run as Server

Right click on the created project -> run as -> server

Run On Server

Run On Server

Neme: Apache Tomcat v7.0
Instalation directory -> create new directory
Click download and install for apache-tomcat-7.0.12
Please wait several minutes and then finish.

Start the Server

In the server view you will see server instance. Right click -> clean. Then right click -> start.
Browse to: http://localhost:8080/TestProject/api

Deleting batch of rows from MS-SQL Server table

Deleting large number of rows from a (SQL Server) table can cause replication delay, create a very large transaction and a big performance impact, as well as escalating locks so that the table will be unavailable.
If it is possible use truncate:


Truncate will run much faster although it has no facility to filter rows, it does a table meta data change at the back (e.g. TRUNCATE will reset the IDENTITY value for the table if there is one).

The option of deleting small portions at a time will be slower, although it will generate less impact on the server performance.

    SET @COUNT = 0
    WHILE (@COUNT < 1000)
       DELETE TOP (10000) FROM [MyTable] -- WHERE [];
       WAITFOR DELAY '00:00:02';
       SET @COUNT = (@COUNT + 1)

Hibernate: How to insert OneToMany children by cascade

I needed a way to insert an entity with a new joined (children) entity together. One way to do it is by separate the actions (e.g. to save the joined entity first and then to save the main entity).
However, Hibernate support inserting a new entity with new joined entity.

I would like to present a way to insert new parent element together with its new OneToMany children by cascade:

The parent Hibernate entity:

@Table(name = "Parent")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Parent {
    @Id    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column( name="Parent_ID", unique = true, nullable = false, updatable = false )
    public Integer getId() { return id; }
    private Long id;
    @OneToMany(fetch = FetchType.LAZY, cascade = { CascadeType.ALL,CascadeType.PERSIST,CascadeType.MERGE }, mappedBy = "parent")
    @Column(nullable = false)
    public List<Children> getChildrens() { return childrens; }
    public void setChildrens( List<Children> childrens) { this.childrens = childrens; }
    private List<Childrens> childrens;

The children Hibernate entity:

@Table(name = "Children")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Children  {
   @ManyToOne( fetch = FetchType.EAGER )
   @JoinColumn(name = "parent_id", nullable = false )
   public Parent getParent() { return parent; }
   public void setTitle(Title title) {
       this.parent = parent;
   private Parent parent;

In the Java code:

Don’t forget to set the parent in the children entity.
For example:

    Parent parent;// getting the Hibernate entity of the parent
    List<Children> childrenList = new ArrayList<Children>();
    Children children;
    children = new Children();
    children.setParent( parent );

Solr spellcheck compound from several fields

The Solr’s SpellCheck component is designed to provide inline spell checking of queries (i.e. query suggestions or “Did You Mean”) in case it thinks the input query might have been misspelled. The words can be loaded from text files, a field in Solr, or even from several fields (more than one field).

In order for making the spellcheck loading the words from several fields you need to:

Declare a new field and copy all the fields, of which their words should be part of the spellcheck index, into the new field

The declaration of the new field and the copy should be configured in the the schema.xml file.

New field declaration

You should pay attension to the following properties:

  • Field type: It is important that you declared it as field type textSpell (Solr’s Spellcheck only works with field type textSpell).
  • MultiValued: because your index consist from several fields (not one) you must declare it as multi valued.
  • Stored: for space issue, declare the field as not stored (in my case it was a difference between increase of 13% vs 43% of space).
<!-- multiple field spell check -->
<field name="didYouMean" type="textSpell" indexed="true" stored="false" multiValued="true"/>

Copy the fields into the new field

Suppose the following are the fields you would like your spell check consist from:

<field name="q" type="text" indexed="true" stored="true" />
<field name="tn" type="text" indexed="true" stored="true" />
<field name="an" type="text" indexed="true" stored="true" />

The following statements copy all the fields, of which their words should be part of the spellcheck index, into the new field.

<copyField source="q" dest="didYouMean"/>
<copyField source="tn" dest="didYouMean"/>
<copyField source="an" dest="didYouMean"/>

Configure Solr to use the new field

The configuration of Solr to use the new field (to specify the field name on which the spell check will operate on) is done in the solrconfig.xml file.

<searchComponent name="spellcheck" class="solr.SpellCheckComponent">
    <str name="queryAnalyzerFieldType">textSpell</str>
    <lst name="spellchecker">
      <str name="name">default</str>
      <str name="field">didYouMean</str>
      <str name="spellcheckIndexDir">./spellchecker</str>
           <str name="buildOnCommit">true</str>
  <requestHandler name="/spell" class="solr.SearchHandler" lazy="true">
     <lst name="defaults">
      <str name="spellcheck.onlyMorePopular">false</str>
      <str name="spellcheck.extendedResults">false</str>
      <str name="spellcheck.count">5</str>
            <str name="spellcheck">on</str>
      <str name="spellcheck.collate">true</str>
    <arr name="last-components">

How to make configuration changes into effect

In order to make the spellcheck configurations into effect follow the following steps:

  1. Restart your server (e.g. sudo /etc/init.d/jetty restart)
  2. Reload config and full import with cleaning (http://localhost:8983/solr/test/admin/dataimport.jsp?handler=/dataimport)
  3. Test your regular index (http://localhost:8983/solr/test/select/?q=*:*&start=0&rows=10&indent=on)
  4. Test the spell check index (http://localhost:8983/solr/test/spell/?q=helllo&version=2.2&start=0&rows=10&indent=on&spellcheck=true&


  • If you query for a sentence that contain more than one word, the spellcheck response will contain alternatives for each word not found in the index. The spellcheck.collate=true causes a modified version of the original query (the sentence) to be returned with the most likely alternatives.
  • Note the which is needed only once to build the spellcheck index from the main Solr index. It takes time and should not be specified with each request. SpellCheckComponent can be configured to automatically (re)build indices based on fields in Solr index when a commit is done. In order to do so you must enable this feature by adding the following line in your SpellCheckComponent configuration for each spellchecker where you wish it to apply:
    <str name="buildOnCommit">true</str>

I will be happy to receive any comment from you.

How to create a copy of existing Solr index

If you have an already existing Solr index and you want to try new settings, it is best to work and test on a copy Solr’s index first.
Here are the simple steps to make a copy of your Solr’s index:
Suppose we have already an index named cast and the copy will be named castSpell.

  1. Make new directory under solr (e.g. castSpell).
  2. Copy the entire directories conf and lib from cast to castSpell.
  3. Make new directory called data under the new directory castspell.
  4. Add to solr.xml your new index name:   <core instanceDir=”castSpell” name=”castSpell” />
  5. Restart Jetty (e.g. sudo /etc/init.d/jetty restart)
  6. Go to http://localhost:8983/solr/castSpell/admin/dataimport.jsp?handler=/dataimport
    • click on Reload-config
    • click on Full-import
    • continue to next step only after the status became “idle” (by clicking on Status).
  7. Test your new index by: http://localhost:8983/solr/castSpell/select/?q=*:*&start=0&rows=10&indent=on

Drupal Sitemap does not show URL aliases

The Sitemap module

The Drupal Sitemap module generates a sitemap page and gives visitors a site overview. All categories and terms (i.e. vocabulary) are expanded optionally with node counts and RSS feeds.

The taxonomy URL paths on the sitemap page are not in the URL paths’ alias format

In Drupal 6, I encountered a problem: The taxonomy URL paths on the sitemap page are not in the URL paths’ alias format (which are generated by the Pathauto module). For example, the taxonomy URLs are in the format /taxonomy/term/28, instead of being displayed with the assigned URL alias, such as /category/projects.

Some of the solutions I found on the web are:

  1. Uninstalling and reinstalling the Sitemap and Pathauto modules;
  2. “Bulk update” in the Pathauto module settings;
  3. Changing modules weights.

However, none of the solutions listed above, nor any other solutions, worked for me. In order to fix this problem, I had to fix the Drupal Sitemap module code:

Code fix

Go to your sitemap module directory, back up and edit:

cd sites/all/modules/site_map
cp site_map.module site_map.module.backup
vi site_map.module

At function _site_map_taxonomy_tree, inside the elseif ($term->count) block, replace this line:

$term_item .= l($term->name, ($cat_depth < 0) ? taxonomy_term_path($term) : "taxonomy/term/$term->tid/$cat_depth", array('attributes' => array('title' => $term->description)));

with the following four lines:

/* show URL alias at sitemap */
$temp_term_path = taxonomy_term_path($term);
$temp_path_alias = drupal_get_path_alias($temp_term_path);
$term_item .= l($term->name, $temp_path_alias, array('attributes' => array('title' => $term->description)));

This fixed the sitemap page to show the URL aliases (clean URL) in the taxonomy links (see

If you have any comment or a suggestion, please share.

How to log all MySQL queries in Drupal

In order to benchmark a Drupal site performance you need to see all the database queries related to your Drupal site.

In case you don’t have access to the my.cnf file

If you don’t have access to the my.cnf file, you can log the queries from the Drupal code itself:

cp includes/ includes/
vim includes/

At function db_query, just before the return clause, write:

error_log("Query: $query");

This will write all the queries Drupal uses for calculating its pages.
Be careful and use it with wisdom, as it might blow your error_log file.
To stop the queries logging process, overwrite the includes/ file with the original.

cp includes/  includes/

In case you have access to the my.cnf file

If you have access to the my.cnf file, you need to set the log parameter (at the my.cnf file):

log = /var/log/mysql/mysql-queries.log

The output will be at


Please remember to shut the log off after you have finished, or it could hurt your site performance.
See MySQL queries log analyzing

Insert into multiple MySQL tables with one-to-one relationship

I have been asked if there is a way to insert data into two MyISAM tables that connected by a one-to-one relationship in one MySQL query.

For example:

mysql> CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
mysql> CREATE TABLE `user_details` (
  `id` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned default NULL,
  `height` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)

How can I insert at once a new product with its details?
My answer is that you can’t insert into multiple MySQL tables in one query (if you know a way, I will be happy to learn;).
However, there are several simple and easy ways to do it:

Using select in the insert value:

INSERT INTO user (name)
     VALUES ('John Smith');
INSERT INTO user_details (id, weight, height)
     VALUES (SELECT(id FROM user WHERE name='John Smith'), 83, 185);

Using LAST_INSERT_ID in the insert value:

INSERT INTO user (name)
     VALUES ('John Smith');
INSERT INTO user_details (id, weight, height)
     VALUES (LAST_INSERT_ID(),83, 185);

Note: The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

Using PHP script:

// Connecting to database
$link = mysql_connect($wgScriptsDBServerIP, $wgScriptsDBServerUsername, $wgScriptsDBServerPassword, true);
if(!$link || !@mysql_SELECT_db($wgScriptsDBName, $link)) {
echo("Cant connect to server");
// Values to insert
$name = 'John Smith';
$weight = 83;
$height = 185;
// insertion to user table
$sql = "INSERT INTO user (name) VALUES ('$name')";
$result = mysql_query( $sql,$conn );
// retrieve last id
$user_id = mysql_insert_id( $conn );
mysql_free_result( $result );
// insertion to user_details table
$sql = "INSERT INTO user_details (id, weight, height) VALUES ($user_id, $weight, $height)";
$result = mysql_query( $sql,$conn );
mysql_free_result( $result );

If you know of another good way please post a comment.

Note: This post was fixed according to the comments I have received from Singer Wang, Shlomi Noach and Chris Evers. Thanks guys!

Connection Pool: MySQL Communications link failure

The Problem And The Solution

While using a MySQL connection pool in Java, I received a MySQL Communications link failure Exception (see below).

In order to solve communication link failure exception:

  • I have removed JDBC property autoReconnect=true and put only the JDBC property autoReconnectForPools=true
  • I have added the connection properties:

See for example: How to set up a MySQL connection pool in Java

The MySQL CommunicationsException: Communications link failure Exception

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error
MESSAGE: Communications link failure
Last packet sent to the server was 0 ms ago.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(
        at java.lang.reflect.Constructor.newInstance(
        at com.mysql.jdbc.Util.handleNewInstance(
        at com.mysql.jdbc.SQLError.createCommunicationsException(
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(
        at com.mysql.jdbc.MysqlIO.sendCommand(
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
        at com.mysql.jdbc.ConnectionImpl.execSQL(
        at com.mysql.jdbc.StatementImpl.executeUpdate(
        at com.mysql.jdbc.StatementImpl.executeUpdate(
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(
        at java.util.concurrent.ThreadPoolExecutor$
Caused by: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(
        ... 14 more