Can't login to GUI of SuiteCRM 7.7 after adding custom field to Users module in Studio

💬 General Discussion
1

First of all I am not good programmer and not very familier with administration of Suite CRM.

I added custom field called “test” in Users module via Studio just for test. After that I lost ability to login to web interfejs to the CRM :frowning:
After entering user+pass I receive message “You have been logged out because your session has expired.”. I cleared all content (cache, temporary file etc) in the browser but it didn’t help. I don’t have Sugar CRM installed along with Suite CRM (there is such bug). It happend just after adding this “test” field so I belive it is corselated.

There is no php files to define this custom field. Do you think can it be because of this?

How to remove this custom field from command line in Linux or how to make GUI available for login?

Arek

2

Hello,

I guess it should not be issue of new field, Can you try it on another browser ?
or try typing yourCRM URL without loginErrorMessage variable on url

3

I tried in Chrome, FF, MSIE and from two computers. All the same…

I normally connect to:
http://192.168.88.77/crm

then the server redirects to:
http://192.168.88.77/crm/index.php?action=Login&module=Users

I enter user+pass and see error “session expired” with url:
http://192.168.88.77/crm/index.php?module=Users&action=Login&loginErrorMessage=LBL_SESSION_EXPIRED

I know that it looks like not new field issue but it happend just after I enter new field to Users module. I firstly had also added new field to Product Line module and after that I was unable to enter any quotas (in this case I was able to remove this field and fix situation)… That’s why I wanted to test adding new field in another module and added to Users module (after that GUI stoped warking, I saw white web page and after clear cache I could enter user+pass again but without ability to login).

4

Can you check suitecrm.log at root of the SuiteCRM and also the apache error log ?

5

I suggest looking at the error you’re getting about the session expired, it’s a known issue, regardless of what you were doing when it broke.

Check:

  • if your disk is full

  • if your session.save_path is well defined, exists and is writable. You can search Google and these forums for more detailed answer on this (including some written by me)

  • if you have Varnish server-side caching turned on in your server

6

I found below errors in /var/log/httpd/error_log and suitecrm.log. It has been occuring from “Sat Feb 11 22:00” (the time when I added new field to Users module) untill now.

/var/log/httpd/error_log
####################################################################################
[Sat Feb 11 22:00:31.108413 2017] [:error] [pid 18755] [client 192.168.88.1:50930] PHP Fatal error: Uncaught exception ‘Exception’ with message ‘Database failure. Please refer to suitecrm.log for details.’ in /var/www/html/crm/include/utils.php:1623\nStack trace:\n#0 /var/www/html/crm/include/database/DBManager.php(345): sugar_die(‘Database failur…’)\n#1 /var/www/html/crm/include/database
/DBManager.php(321): DBManager->registerError(‘Retrieving reco…’, ‘MySQL error 126…’, true)\n#2 /var/www/html/crm/include/database/MysqliManager.php(159): DBManager->checkError(‘Retrieving reco …’, true)\n#3 /var/www/html/crm/include/database/MysqlManager.php(277): MysqliManager->query(‘SELECT users.,…’, true, ‘Retrieving reco…’)\n#4 /var/www/html/crm/data/SugarBean.php(3845): Mysq
lManager->limitQuery('SELECT users.,…’, 0, 1, true, ‘Retrieving reco…’)\n#5 /var/www/html/crm/include/SugarObjects/templates/person/Person.php(83): SugarBean->retrieve(‘1’, true, true)\n#6 /var/www/html/crm/modules/Users/User.php(620): Person->retrieve(‘1’, true, true)\n#7 /var/www/html/crm/modules/Users/authentication/SugarAuthenticate/Su in /var/www/html/crm/include/utils.php on line 1623, referer: http://192.168.88.77/crm/index.php?action=LogView&module=Configurator&doaction=all&filter=

/var/www/html/crm/suitecrm.log
####################################################################################
Sat Feb 11 22:00:01 2017 [18919][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:01:01 2017 [18926][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:02:01 2017 [18940][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:03:01 2017 [18944][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:04:02 2017 [18951][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:05:01 2017 [18955][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:06:01 2017 [18960][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:07:01 2017 [18964][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:08:01 2017 [18968][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:09:01 2017 [18972][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER
E users.id = ‘1’ AND users.deleted=0 LIMIT 0,1: MySQL error 1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’
Sat Feb 11 22:10:02 2017 [18993][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHER

7

Please tell us your versions of

  • SuiteCRM
  • MySQL
  • Apache
  • Linux
  • PHP

Was this the first time ever, that you were making changes in Studio?

8

SuiteCRM 7.7.9

  • MySQL
    [root@crm ~]# yum list installed | grep mysql
    Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
    mysql-community-client.x86_64 5.6.35-2.el7 @mysql56-community
    mysql-community-common.x86_64 5.6.35-2.el7 @mysql56-community
    mysql-community-devel.x86_64 5.6.35-2.el7 @mysql56-community
    mysql-community-libs.x86_64 5.6.35-2.el7 @mysql56-community
    mysql-community-release.noarch el7-5 installed
    mysql-community-server.x86_64 5.6.35-2.el7 @mysql56-community
    php-mysql.x86_64 5.4.16-42.el7 @base

  • Apache
    [root@crm ~]# yum list installed | grep httpd
    Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
    httpd.x86_64 2.4.6-45.el7.centos @base

  • Linux
    CENTOS 7
    [root@crm ~]# uname -a
    Linux crm 3.10.0-514.6.1.el7.x86_64 #1 SMP Wed Jan 18 13:06:36 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
    [root@crm ~]#

  • PHP
    [root@crm ~]# yum list installed | grep php
    Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
    php.x86_64 5.4.16-42.el7 @base
    php-cli.x86_64 5.4.16-42.el7 @base
    php-common.x86_64 5.4.16-42.el7 @base
    php-gd.x86_64 5.4.16-42.el7 @base
    php-imap.x86_64 5.4.16-7.el7 @epel
    php-mbstring.x86_64 5.4.16-42.el7 @base
    php-mysql.x86_64 5.4.16-42.el7 @base
    php-pdo.x86_64 5.4.16-42.el7 @base

Was this the first time ever, that you were making changes in Studio?
About 1-2 weeks ago before I did lost ability to login.

9

Maybe does someone just know how to export/import the accounts and contacts using linux command line and it would be ok for me to move this data to fresh installation of CRM? It woul be the quickest fixing the problem.

10

I fixed the problem :slight_smile:

When I added custom field to Users module table users_cstm change Collation to “utf8_polish_ci” whereas users table has collection of “utf8_general_ci”.
And some of php scripts issues:
SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHERE users.id = ‘1’ AND users.deleted=0 LIMIT 0,1
what makes an error i mysql:
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’

Thats why I couldn’t loged in.

mysql> show full columns from users;
+---------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field                     | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+---------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id                        | char(36)     | utf8_general_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| user_name                 | varchar(60)  | utf8_general_ci | YES  | MUL | NULL    |       | select,insert,update,references |         |
| user_hash                 | varchar(255) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| system_generated_password | tinyint(1)   | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
| pwd_last_changed          | datetime     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |


mysql> show full columns from users_cstm;
+-----------------+---------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field           | Type          | Collation      | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------+---------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id_c            | char(36)      | utf8_polish_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| test_c         | char(36)      | utf8_polish_ci| YES  |     | NULL    |       | select,insert,update,references |         |
+-----------------+---------------+----------------+------+-----+---------+-------+---------------------------------+---------+

After changeig:

ALTER TABLE users_cstm CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

I can log in to CRM :slight_smile:

Probably the same problem was with unavailable quotas after adding custom field to Product Line module… Probably encofing also changed to utf8_polish_ci.

1 Like