MySQL - sql_mode STRICT_TRANS_TABLE hiccups.
Activity
That maybe so, but it seems to me that new Linux distros are now setting this as standard. A very similar issue was raised in the closed issue #54 (closed).
Please see this article, and some of the comments, from January 2014 by one of the MySQL devs at Oracle: http://www.tocker.ca/2014/01/14/making-strict-sql_mode-the-default.html
I should think a review (grep) of the schema would be able to provide a list of where potential poor table/column definitions are and then we should all try to fix them - rather than sweeping it under the carpet and hoping it goes away... ;-)
-
I am sure that many inadvertent bugs and other issues could be prevented if there was a little focus on data integrity:
Here's a description of the difference between the two modes: STRICT_TRANS_TABLES and STRICT_ALL_TABLES (both of which I believe are now on by default in modern MySQL installations)
http://www.noelherrick.com/blog/mysql-strict_all_tables-vs-strict_trans_tables
-
@prasad Awesome :-)
There was also a reference to a similar issue mentioned in #105 (closed) which might need a review.
Fixed #105 (closed) with (053eba12)
-
There is also the situation where a column is set to NOT NULL but has no default value; irrespective of the data type. This was the issue in #54 (closed).
Issue #54 (closed) was fixed earlier.
@prasad - Adding $fdefault = ' CURRENT_TIMESTAMP'; /* STRICT_TRANS_TABLES fix */ is a "no go".
Fri May 13 12:18:16 2016,721 [35988] INFO VT - PearDatabase ->ADODB error Query Failed:select * from vtiger_users where id=?::->[1146]Table 'vtiger.vtiger_users' doesn't exist Fri May 13 12:18:16 2016,726 [35988] DEBUG VT - result is not an object:#0 /Applications/MAMP/htdocs/vtiger/modules/Users/Users.php(1024): PearDatabase->query_result(false, 0, 'user_name')@manuelgit - Can you clarify? Install succeeded with this fix - did it not work on yours?
Added cleanup and removed ~19 labels
Didn't work here, in two different systems using mamp
Database server
Server: Localhost via UNIX socket Server type: MySQL Server version: 5.5.42 - Source distribution Protocol version: 10 User: root@localhost Server charset: UTF-8 Unicode (utf8)Web server
Apache Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $ PHP extension: mysqli Documentation PHP version: 5.6.10phpMyAdmin
Version information: 4.4.10@manuelgit - any possibility to have access to the server?
ONLY_FULL_GROUP_BY is exposing a problem in data/CRMEntity,line 352,367,379 grouping by columnname isn't enough, changing to grouping by fieldid is more accurate and lets it save objects.
INFO VT - PearDatabase ->ADODB error Query Failed:select * from vtiger_field where tabid in (?) and tablename=? and displaytype in (1,3) and presence in (0,2) group by columnname::->[1055]Expression #2 (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'photos.vtiger_field.fieldid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mentioned in issue #217 (closed)
$fdefault = ' CURRENT_TIMESTAMP'; /* STRICT_TRANS_TABLES fix */This broke the installation process on a stable debian system.
I can not comprehend this... What is going on here? Why are there breaking changes in the master since weeks? I mean, the master branch...! Am I the only one around who thinks this is unacceptable?
Can we please revert this for the master branch and put it onto a different branch until it works?
mentioned in merge request !66 (closed)
mentioned in merge request !66 (closed)
-
On a new Ubuntu 16.04 server (MySQL 5.7) the default MySQL settings include ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES.
I was unable to edit and re-save a Lead due to the following error:
Wed Aug 10 14:42:40 2016,128 [25950] INFO VT - PearDatabase ->ADODB error Query Failed:select * from vtiger_field where tabid in (?) and tablename=? and displaytype in (1,3) and presence in (0,2) group by columnname::->[1055]Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vtigercrm_amber.vtiger_field.fieldid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byRemoving ONLY_FULL_GROUP_BY allowed me to edit and save a Lead.
I also had an issue trying to create a new Opportunity.
Thu Aug 11 07:41:52 2016,347 [14395] INFO VT - PearDatabase ->ADODB error Query Failed:insert into vtiger_potential(potentialid,potentialname,potential_no,amount,related_to,closingdate,potentialtype,nextstep,sales_stage,probability,campaignid,forecast_amount,contact_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?)::->[1366]Incorrect integer value: '' for column 'related_to' at row 1This was rectified by removing STRICT_TRANS_TABLES from the defaults.
STRICT_TRANS_TABLES - data formatting need handling.
Thu Aug 11 07:41:52 2016,347 [14395] INFO VT - PearDatabase ->ADODB error Query Failed:insert into vtiger_potential(potentialid,potentialname,potential_no,amount,related_to,closingdate,potentialtype,nextstep,sales_stage,probability,campaignid,forecast_amount,contact_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?)::->[1366]Incorrect integer value: '' for column 'related_to' at row 1
related_to, contact_id - should be converted to number instead of string
mentioned in issue #298 (closed)
I decided to work around this for now with the following code block.
index.php
... include_once 'includes/main/WebUI.php'; + // Remove ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES from sql_mode + global $adb; + $result = $adb->pquery('SELECT @@SESSION.sql_mode AS sql_mode'); + $sql_mode = $adb->query_result($result, 0, 'sql_mode'); + $sql_mode = trim(preg_replace('/,+/', ',', str_replace(array('ONLY_FULL_GROUP_BY', 'STRICT_TRANS_TABLES'), '', $sql_mode)), ','); + $adb->pquery('SET SESSION sql_mode = ?', array($sql_mode)); $webUI = new Vtiger_WebUI(); ...-
Well even before you restart, when you close your connection I believe. Which is done deliberately as Vtiger isn't the only database I have in MySQL and I don't want all of them to be effected, hence why I added it where I have so it's performed before every request.
It's worth mentioning that I agree with @lord_alan that it's best to fix this properly rather than ignoring it, I just needed something quick to get things working again, so thought I'd share what I've done in-case it's useful to someone else.
mentioned in commit 9b5c5338
-
getting these errors in vt7.0.0 creating calendar entries with default sql_mode. The primary key in vtiger_activity_reminder doesn't allow nulls, and getting the calendar data is incompatible with only_full_group_by
Tue Dec 13 16:14:16 2016,254 [25428] INFO VT - PearDatabase ->ADODB error Error in processing vtiger_table vtiger_activity_reminder Query Failed:INSERT INTO vtiger_activity_reminder VALUES (?,?,?,?)::->[1366]Incorrect integer value: '' for column 'recurringid' at row 1
Tue Dec 13 16:17:56 2016,916 [14725] INFO VT - PearDatabase ->ADODB error Query Failed:SELECT vtiger_activity.status, CASE WHEN (vtiger_activity.status not like '') THEN vtiger_activity.status ELSE vtiger_activity.eventstatus END AS status, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_seactivityrel.crmid, vtiger_activity.date_start, vtiger_activity.time_start, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_crmentity.smownerid, vtiger_activity.activityid, vtiger_calendar_user_field.starred, vtiger_activity.visibility FROM vtiger_activity INNER JOIN vtiger_crmentity ON vtiger_activity.activityid = vtiger_crmentity.crmid LEFT JOIN vtiger_seactivityrel ON vtiger_activity.activityid = vtiger_seactivityrel.activityid LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid LEFT JOIN vtiger_calendar_user_field ON vtiger_activity.activityid = vtiger_calendar_user_field.recordid AND vtiger_calendar_user_field.userid=1 WHERE vtiger_crmentity.deleted=0 AND ( vtiger_activity.activitytype <> 'Emails') AND vtiger_activity.activityid > 0 GROUP BY vtiger_activity.activityid ORDER BY str_to_date(concat(date_start,time_start),'%Y-%m-%d %H:%i:%s') DESC LIMIT 0, 35::->[1055]Expression #5 (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vt7.vtiger_seactivityrel.crmid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mentioned in issue #665 (closed)
Sending ('') instead of (0) on integer fields would fail (see #718 (closed))
mentioned in issue #841 (closed)
mentioned in issue #1154 (closed)
mentioned in commit b76f2808