SugarCRM DateTime bug

I recently ran into a Sugar datetime bug that was annoying, and at first, I thought it would be hard to fix, but later found it to be relatively easy. I'm posting the solution here in hopes that Sugar will fix this bug in their core code in a future release, and to post a fix until they do, and also, because I had an interesting journey to bug resolution, and created some interesting queries across the Sugar database, I thought my readers might be interested. I present to you a quick trip through a SugarCRM bug.

The problem: Whenever I loaded a SugarCRM page, anywhere in the application, a slew of php_warning messages populated my php_error.log. All the warning messages were exactly the same, and since there were about 20 of these warning per every page load, the php_error.log quickly became full of these warning messages. So many of the warnings were put in the error log with each page load that it slowed down the application, quickly filled up the error log, and effectively made the php_error.log useless. Here's the PHP warning message that was overwhelming the error log:

[28-Jan-2015 09:31:49] PHP Warning: DateTime::modify(): Failed to parse time string (now&12:00am) at position 3 (&): Unexpected character in /data/cpms_R6/include/SugarDateTime.php on line 647

And here's what was happening:

Sugar, when it loads a page, creates a Sugar datetime object for each date and datetime field on the page.  Sugar then uses the default value in Studio, or in the page form itself, to create a datetime object for that field, that it then uses the object to modify the date and time in Sugar.  This is done in the include/SugarDateTime.php file around line 647 in the modify() method.

The Sugar modify($modify) method uses the $modify parameter, which is the amount of the offset. The Sugar modify method then calls PHP's modify($modify) method in PHP core code.  PHP returns the new date, modified by the $modify parameter, and Sugar creates its datetime object. Simple.

Let's take a look at some $modify parameters, and see what the Sugar modify($modify) method creates after the call to the PHP modify($modify) method.

now valid Sugar modify() returns a sugar date time object

+1 dayvalid Sugar modify() returns a sugar date time object

+1 monthvalid Sugar modify() returns a sugar date time object

+90000 secondsvalid Sugar modify() returns a sugar date time object

+1 weekvalid Sugar modify() returns a sugar date time object

-1 dayvalid Sugar modify() returns a sugar date time object

now&12:00am” returns “false

The last entry is what’s throwing the error, because Sugar does not create its datetime object. Thus every time a $modify parameter is set to a syntax like "now&12:00am" an error is thrown, and an entry is made in the PHP_error.log. PHP modify() throws the warning error as soon as it encounters the “&

The Sugar modify() method works well on fields with the "date" data type, but not on fields set to the "datetime" or "datetimecombo" data type. The "datetime" data type is assigned when you create a custom field in Studio, and set the new field to a datetime data type. When you do that, if you enter a data into the field, Sugar requires that you also put in a time before it will save the field, and this sets the $modify parameter to something with an "&" in it, like “now&12:00am,” and you'll end up with PHP Warning messages.

======

OK, we understand the problem, how do we fix it? Well, we don't want to modify the core code, because a new release might over write your fixed code. And this fix is not ideal for the custom folder, because at this stage, it looks like the problem is in the database, not the application. What we want to do is change the offending "datetime" fields to a "date" data type without losing the date data already in the database. hmmm....

Possible Solutions:

1. Modify the core code to take out everything after the "&" in the incoming $modify parameter. This involves modifying the core code, which I want to shy away from for this solution.

2. Set the default field value initially set in Studio to null. This works until a user enters a date in the field and is required to fill in a time.

3. Modify the database and change the date type in the data base to "date", instead of "datetime"

Since I had to preserve the dates already in the database for the fields in question, my initial thought was to write an sql query on the table and fields to truncate the "datetime" entry from "2014-08-01 18:37:04" to "2014-08-01".

But there was another problem. I couldn't just change every "datetime" field in the database to "date", because some system fields like "date_created", "date_entered", "date_modified", and other system dates need to be in the datetime format, not date.

First, I needed to query the database to find all the datetime data type fields in the database. Here it is:

"SELECT table_schema,table_name,column_name,ordinal_position,
FROM information_schema.columns
WHERE table_schema = '[ your db name ]' and data_type in ('datetime') and column_name not in ('date_entered','date_modified','date_created')
ORDER BY 2, 4;"

You can use this query across the database to find any particular data type, or for that matter any value in the database with some modifications. The ordinal number is the position in the returned database columns. This produces a list of all the datetime fields in the database.  I came up with 41 fields in all the tables in the database that had a datetime data type.

I then culled through the fields manually, and looked for custom fields that ended with a "_c", and targeted those fields for changing.

Once I had a list of fields I wanted to change, I needed a query to truncate the field data and preserve the date value. This turned out to be one query per field and it looked like this:

"ALTER TABLE `[ your database name ]`.[ the table with the field in it ]
MODIFY COLUMN [the field you want to change] date;"

The above query alters the field you want to change to a "date" data type by truncating the datetime field to date. And we're done.

======

Not quite!  If we go into Studio, after we change the database, the field is still labeled datetime, and if you save a value in the field in Studio, Sugar still requires a time. Although when saved to the database the time will be truncated off.

Which brought me to my relatively easy final solution.  In order to change the datetime date type in Studio, I needed to modify my modules vardef.php file located in modules/[the module your working with]/vardef.php to change that fields "type" and "dbType" to "date".

You can not do this in Studio, because once you create your custom field in Studio, you can't change the data type of the field in Studio without creating a new field. If you create a new field, you lose the data that was already in the database in the original field.

However, if I edit the vardef.php file directly, wonders of wonders, the datatype of the field changes in Studio, and the database is truncated while still preserving the original date. The best of both worlds, and a solution, as you can see, that I came across, while working through the bug.

Since the fields I needed to change were custom fields, those changes were relatively immune from Sugar code change updates, and the errors go away in the php_error.log, which solves my problem.

======

I learned three new things from this bug chase.

1. How to create a query to search the entire database for records that match a certain criteria.

2. A query to alter fields in the database while preserving the data.

3. The power of the vardef.php file as a bridge between Sugar and the Sugar database.