I installed XRMS 1.99.2 today for testing purposes and had a problem with file uploads which seems to be related to sql-strict mode of mysql. The files where transferred to the server and stored in the configured storage-directory but no entries in the files-table of the database where made.
After some testing I added debug output in files/new.php and the reason for failure of adding the needed information seems quite clear:
<hr />
(mysql): SHOW COLUMNS FROM files
<hr />
<hr />
(mysql): INSERT INTO files ( FILE_NAME, FILE_PRETTY_NAME, FILE_DESCRIPTION, FILE_SIZE, FILE_TYPE, ON_WHAT_TABLE, ON_WHAT_ID, ENTERED_AT, ENTERED_BY ) VALUES ( 'headers.txt', 'test', 'test', 551, 'text/plain', 'contacts', 3, '2007-06-05 15:20:28', 1 )
<hr />
1364: Field 'last_modified_by' doesn't have a default value<br>
<hr />
(mysql): SELECT LAST_INSERT_ID()
<hr />
I know this behaviour from some versions of Bugzilla which had problems with sql-strict in MySQL 5.037+, too, deactivating this mode made the file upload function properly. I didn't find any information regarding this problem but at least one person whith exactly the same report without any helpful answer, so maybe this information helps others.
If it's possible there should be some error handling after "$con->execute($ins);" in files/new.php.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Mr. Schoening,
you are absolutly right - and thanks a lot for your post (it's a long time ago - but nevertheless).
For me it is now Server 2008 R2 x64, IIS 7.5, Mysql 5.7, PHP 5.6.21. In Mysql 5.7 it is a very strange thing that if you change the my.ini (windows) the mysql workbench recognizes the option file and indicates the right (non strict mode) sql-mode= NO_ENGINE_SUBSTITUTION. But if you look below Workbench->Server Variables->SystemVariables there is still the strict-mode. So you have to reset the global mode manually like this (windows):
mysql -u root -p
mysql> SET @@SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
mysql> SET @@GLOBAL.SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
If you check that:
mysql> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
It's OK and after that the settings are OK too in Workbench->Server Variables->SystemVariables. .
After that the file-Upload via new file is working fine and without any error.
Naturally if you restart Mysql it's all gone for the setting is not permanent (though - as I mentioned before - the my.ini is recognized and the value is accepted in the mysql option file).
The option my.ini file resides in C:\Program Files\MySQL\MySQL Server 5.7\my.ini - and this one is not used for the globals.
The my.ini file, that is used for global server variables resides in the hidden directory -> C:\ProgramData\MySQL\MySQL Server 5.7\my.ini. So if you change that file, all is permanent.
I then debugged the new.php code while running the automatically reset sql strict mode:
$rec = array();
$rec['file_pretty_name'] = $file_pretty_name;
$rec['file_description'] = $_POST['file_description'];
$rec['file_name'] = $file_name;
$rec['file_size'] = $file_size;
$rec['file_type'] = $file_type;
$rec['on_what_table'] = $_POST['on_what_table'];
$rec['on_what_id'] = $_POST['on_what_id'];
$rec['entered_at'] = time();
$rec['entered_by'] = $session_user_id;
$rec['modified_on'] = $rec['entered_at'];
$rec['modified_by'] = $rec['entered_by'];
// files plugin hook allows external storage of files. see plugins/owl/README for example
// params: (file_field_name, record associative array)
$file_plugin_params = array('file_field_name' => 'file1', 'file_info' => $rec);
do_hook_function('file_add_file', $file_plugin_params);
// external_id gets set by the hook
$rec = $file_plugin_params['file_info'];
if($file_plugin_params['error_status']) {
$msg .= $file_plugin_params['error_text'];
$error = true;
} else {
// Make DB connection
$con = get_xrms_dbconnection();
$con->debug = 1;
and got these debug (error) outputs:
( ! ) Strict standards: Non-static method ADOConnection::outp() should not be called statically in C:\inetpub\wwwroot\CRM-Portal\include\adodb\adodb-lib.inc.php on line 933
Call Stack
1 0.1690 167144 {main}( ) ...\new.php:0
2 19.5051 2274880 ADOConnection->GetInsertSQL( ???, ???, ???, ??? ) ...\new.php:74
3 19.5081 2387296 _adodb_getinsertsql( ???, ???, ???, ???, ??? ) ...\adodb.inc.php:1757
4 19.5091 2390584 ADODB_mysqli->MetaColumns( ??? ) ...\adodb-lib.inc.php:671
5 19.5091 2390928 ADOConnection->Execute( ???, ??? ) ...\adodb-mysqli.inc.php:486
6 19.5091 2390992 ADOConnection->_Execute( ???, ??? ) ...\adodb.inc.php:855
7 19.5091 2391192 _adodb_debug_execute( ???, ???, ??? ) ...\adodb.inc.php:867
(mysqli): SHOW COLUMNS FROM files
( ! ) Strict standards: Declaration of file_upload::getFileSize() should be compatible with File::getFileSize($_path = false) in C:\inetpub\wwwroot\CRM-Portal\include\classes\File\file_upload.php on line 146
Call Stack
( ! ) Strict standards: Declaration of file_upload::getFileMimeType() should be compatible with File::getFileMimeType($_path = false) in C:\inetpub\wwwroot\CRM-Portal\include\classes\File\file_upload.php on line 146
Call Stack
Hello everybody,
I installed XRMS 1.99.2 today for testing purposes and had a problem with file uploads which seems to be related to sql-strict mode of mysql. The files where transferred to the server and stored in the configured storage-directory but no entries in the files-table of the database where made.
After some testing I added debug output in files/new.php and the reason for failure of adding the needed information seems quite clear:
<hr />
(mysql): SHOW COLUMNS FROM files
<hr />
<hr />
(mysql): INSERT INTO files ( FILE_NAME, FILE_PRETTY_NAME, FILE_DESCRIPTION, FILE_SIZE, FILE_TYPE, ON_WHAT_TABLE, ON_WHAT_ID, ENTERED_AT, ENTERED_BY ) VALUES ( 'headers.txt', 'test', 'test', 551, 'text/plain', 'contacts', 3, '2007-06-05 15:20:28', 1 )
<hr />
1364: Field 'last_modified_by' doesn't have a default value<br>
<hr />
(mysql): SELECT LAST_INSERT_ID()
<hr />
I know this behaviour from some versions of Bugzilla which had problems with sql-strict in MySQL 5.037+, too, deactivating this mode made the file upload function properly. I didn't find any information regarding this problem but at least one person whith exactly the same report without any helpful answer, so maybe this information helps others.
If it's possible there should be some error handling after "$con->execute($ins);" in files/new.php.
just for clearance:
Windows Server 2003 R2 SP2
MySQL 5.0.41
PHP 5.2.1
Apache 2.2.4
Hi Mr. Schoening,
you are absolutly right - and thanks a lot for your post (it's a long time ago - but nevertheless).
For me it is now Server 2008 R2 x64, IIS 7.5, Mysql 5.7, PHP 5.6.21. In Mysql 5.7 it is a very strange thing that if you change the my.ini (windows) the mysql workbench recognizes the option file and indicates the right (non strict mode) sql-mode= NO_ENGINE_SUBSTITUTION. But if you look below Workbench->Server Variables->SystemVariables there is still the strict-mode. So you have to reset the global mode manually like this (windows):
mysql -u root -p
mysql> SET @@SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
mysql> SET @@GLOBAL.SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
If you check that:
mysql> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
It's OK and after that the settings are OK too in Workbench->Server Variables->SystemVariables. .
After that the file-Upload via new file is working fine and without any error.
Naturally if you restart Mysql it's all gone for the setting is not permanent (though - as I mentioned before - the my.ini is recognized and the value is accepted in the mysql option file).
The option my.ini file resides in C:\Program Files\MySQL\MySQL Server 5.7\my.ini - and this one is not used for the globals.
The my.ini file, that is used for global server variables resides in the hidden directory -> C:\ProgramData\MySQL\MySQL Server 5.7\my.ini. So if you change that file, all is permanent.
I then debugged the new.php code while running the automatically reset sql strict mode:
$rec = array(); $rec['file_pretty_name'] = $file_pretty_name; $rec['file_description'] = $_POST['file_description']; $rec['file_name'] = $file_name; $rec['file_size'] = $file_size; $rec['file_type'] = $file_type; $rec['on_what_table'] = $_POST['on_what_table']; $rec['on_what_id'] = $_POST['on_what_id']; $rec['entered_at'] = time(); $rec['entered_by'] = $session_user_id; $rec['modified_on'] = $rec['entered_at']; $rec['modified_by'] = $rec['entered_by']; // files plugin hook allows external storage of files. see plugins/owl/README for example // params: (file_field_name, record associative array) $file_plugin_params = array('file_field_name' => 'file1', 'file_info' => $rec); do_hook_function('file_add_file', $file_plugin_params); // external_id gets set by the hook $rec = $file_plugin_params['file_info']; if($file_plugin_params['error_status']) { $msg .= $file_plugin_params['error_text']; $error = true; } else { // Make DB connection $con = get_xrms_dbconnection(); $con->debug = 1;and got these debug (error) outputs:
( ! ) Strict standards: Non-static method ADOConnection::outp() should not be called statically in C:\inetpub\wwwroot\CRM-Portal\include\adodb\adodb-lib.inc.php on line 933
Call Stack
1 0.1690 167144 {main}( ) ...\new.php:0
2 19.5051 2274880 ADOConnection->GetInsertSQL( ???, ???, ???, ??? ) ...\new.php:74
3 19.5081 2387296 _adodb_getinsertsql( ???, ???, ???, ???, ??? ) ...\adodb.inc.php:1757
4 19.5091 2390584 ADODB_mysqli->MetaColumns( ??? ) ...\adodb-lib.inc.php:671
5 19.5091 2390928 ADOConnection->Execute( ???, ??? ) ...\adodb-mysqli.inc.php:486
6 19.5091 2390992 ADOConnection->_Execute( ???, ??? ) ...\adodb.inc.php:855
7 19.5091 2391192 _adodb_debug_execute( ???, ???, ??? ) ...\adodb.inc.php:867
(mysqli): SHOW COLUMNS FROM files
( ! ) Strict standards: Declaration of file_upload::getFileSize() should be compatible with File::getFileSize($_path = false) in C:\inetpub\wwwroot\CRM-Portal\include\classes\File\file_upload.php on line 146
Call Stack
1 0.1690 167144 {main}( ) ...\new.php:0
2 19.5211 2405296 getFileUpLoad( ??? ) ...\new.php:84
3 19.5221 2423632 require_once( 'C:\inetpub\wwwroot\CRM-Portal\include\classes\File\file_upload.php' ) ...\utils-files.php:105
( ! ) Strict standards: Declaration of file_upload::getFileMimeType() should be compatible with File::getFileMimeType($_path = false) in C:\inetpub\wwwroot\CRM-Portal\include\classes\File\file_upload.php on line 146
Call Stack
1 0.1690 167144 {main}( ) ...\new.php:0
2 19.5211 2405296 getFileUpLoad( ??? ) ...\new.php:84
3 19.5221 2423632 require_once( 'C:\inetpub\wwwroot\CRM-Portal\include\classes\File\file_upload.php' ) ...\utils-files.php:105
So I have to analyze that ... we'll see
Best regards and thanks
Axel Arnold Bangert - Herzogenrath 2016
Last edit: Axel Bangert 2016-06-01