MySQL - sql_mode STRICT_TRANS_TABLE hiccups.

We recommend removal of STRICT_TRANS_TABLE sql_mode which is defined in MySQL 5.5.x and above by default.

Achieving compatibility with this might need deep core changes.

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

ok - lets shoot for it and see how far we can reach.

Reference: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes

Noted below is the default sql mode on (MySQL 5.7.12)

SELECT @@GLOBAL.sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Pushed data-sanitization and schema creation fix (36b410f9)

Title changed from MySQL - sql_mode STRICT_TRANS_TABLE not supported. to MySQL - sql_mode STRICT_TRANS_TABLE hiccups.

Milestone changed to 6.5.0

@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)

Summary: Injecting ('') instead of 0 or NULL on numeric field is not acceptable with MySQL. PHP being type-less makes the cleanup bit more widespread.

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

phpMyAdmin

Version information: 4.4.10

@manuelgit - any possibility to have access to the server?