Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Getting this error when users try to log in, running 1.8 7ca #778

Open
samoy2002 opened this issue Mar 14, 2022 · 29 comments · Fixed by #833
Open

Getting this error when users try to log in, running 1.8 7ca #778

samoy2002 opened this issue Mar 14, 2022 · 29 comments · Fixed by #833
Assignees
@Patriboom
Labels
bug Fixed If OK -> Close it Please close this issue if your tests demonstrate that the issue is fixed.

Comments

@samoy2002
Copy link

Unhandled Exception
Message:
SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'mbbizca_bugs207.USR.id' which is not in SELECT list; this is incompatible with DISTINCT

SQL: SELECT DISTINCT 0 AS project, 0 AS attached, 0 AS tages, USR.email, USR.firstname AS first, USR.lastname as last, CONCAT(USR.firstname, ' ', USR.lastname) AS user, USR.language, 'Robot of BUGS system' AS name, 'A user just connected to BUGS' AS title FROM users AS USR WHERE USR.role_id = 4 ORDER BY USR.id ASC LIMIT 0, 1

Bindings: array (
)
Location:
/home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
Stack Trace:
#0 /home/mbbizca/public_html/bugs/app/laravel/database/connection.php(171): Laravel\Database\Connection->execute('SELECT DISTINCT...', Array)
#1 [internal function]: Laravel\Database\Connection->query('SELECT DISTINCT...')
#2 /home/mbbizca/public_html/bugs/app/laravel/database.php(179): call_user_func_array(Array, Array)
#3 /home/mbbizca/public_html/bugs/app/application/libraries/mail.php(119): Laravel\Database::__callStatic('query', Array)
#4 /home/mbbizca/public_html/bugs/app/application/controllers/login.php(49): Mail::letMailIt(Array, 10, 'en')
#5 [internal function]: Login_Controller->post_index()
#6 /home/mbbizca/public_html/bugs/app/laravel/routing/controller.php(303): call_user_func_array(Array, Array)
#7 /home/mbbizca/public_html/bugs/app/laravel/routing/controller.php(266): Laravel\Routing\Controller->response('index', Array)
#8 /home/mbbizca/public_html/bugs/app/laravel/routing/controller.php(157): Laravel\Routing\Controller->execute('index', Array)
#9 /home/mbbizca/public_html/bugs/app/laravel/routing/route.php(153): Laravel\Routing\Controller::call('login@index', Array)
#10 /home/mbbizca/public_html/bugs/app/laravel/routing/route.php(124): Laravel\Routing\Route->response()
#11 /home/mbbizca/public_html/bugs/app/laravel/laravel.php(158): Laravel\Routing\Route->call()
#12 /home/mbbizca/public_html/bugs/index.php(18): require('/home/mbbizca/p...')
#13 {main}
@Patriboom Patriboom added the bug label Mar 14, 2022
@Patriboom Patriboom self-assigned this Mar 14, 2022
@Patriboom
Copy link
Collaborator

Is there any circumstance when it happens ?
Is that for all users ?
How many users in this BUGS system ?
Did it happen right away after install ?
.... after an update ? If so (after update) did it work good before ?
Is the blocked user have special rights ( is he admin, or observer ... ) ?

I'll try - first - to replicate the problem.

Thanks for this message and for more informations.

@samoy2002
Copy link
Author

samoy2002 commented Mar 14, 2022 via email

@Patriboom
Copy link
Collaborator

Ok, thanks for your feedback.
You wrote When I go to the admin page I see the following. but provided no screenshot nor description.

My test will go like this:
in existing DB, I'll create a new user with admin permissions on all projects. Then, I'll logoff from my personnal admin and try to login as the newly created admin user. Hopefully, I'll encounter the problem you describ.

@samoy2002
Copy link
Author

samoy2002 commented Mar 15, 2022 via email

@Patriboom
Copy link
Collaborator

On github, you can drag & drop your file into reply square, it will attached the dropped images.

@Patriboom
Copy link
Collaborator

Ok, I think I've figured out.
For now, you'll be able to give them access following these steps:

  1. Login yourself as admin
  2. Go to the Users admin page
  3. Edit a user
  4. Define a new password to the user
  5. Apply the changes
  6. Repeat 3-5 for each user.

I'll fix that later this week.

@Patriboom Patriboom added Fixed For next release What is planed for next release or next version Fix available in pre-release Please follow the link to all releases, you'll find there the latest pre-release with all the fixes If OK -> Close it Please close this issue if your tests demonstrate that the issue is fixed. labels Mar 15, 2022
@Patriboom
Copy link
Collaborator

Patriboom commented Mar 15, 2022 •
edited
Loading

The patch is available, but will not alter the existing users. I'll release a new pre-release ... give you the like after its creation.

The pre-release 18. rev 7cl is available and fixes this giving you the opportunity to define the new user's password.

@samoy2002
Copy link
Author

samoy2002 commented Mar 16, 2022 via email

@Patriboom
Copy link
Collaborator

Patriboom commented Mar 16, 2022 via email

@samoy2002
Copy link
Author

2022-03-16 12_39_30-Window

@Patriboom
Copy link
Collaborator

I have installed the patch, 1.8 rev 7ck and then tried what you suggested with changing the password of the user. However, that user is still not able to log in. I believe that reading through your thread, you will be putting out a patch to fix it, I hope I understood that correctly

Did you try yourself the user's username and user's password ?
It is supposed to work. If it work for you, then it is a user problem, not config.

Recap:

1 ) You logged as admin
2 ) changed the user's password
3) Logoff
4) Log in as user with his email address and his password (the one you've just changed)
5) if num 4 works ... it should ... make sure the user is using the proper credentials - case sensitives

Is that happen with only one user or all of the 8 others (thant you) ?

@samoy2002
Copy link
Author

samoy2002 commented Mar 16, 2022 via email

@Patriboom Patriboom removed the Fixed label Mar 17, 2022
@Patriboom
Copy link
Collaborator

Patriboom commented Mar 17, 2022 •
edited
Loading

Thanks for your patience samoy2020. The issue seams to has its source in the option « Notice me when someone login ».
I'll will work on it.
Meanwhile, if you want to give access to your users, you may check this option off.
I'll come back with more information and - hopefully - a patch for that.

I'll find this option in your personnal options page.
Click on you username on top of screen, last option line is the one you should set to « NO » for now.

@Patriboom
Copy link
Collaborator

I'm still unable to reproduce problem.
Can you give a list of users with passwords and roles.
I'll try to find if there some "prohibited" types in the names, the email or the password.

I tried four different emails, with "test" as password with every one its own role.
aaa@domain.com / test / user
bbb@domain.com / test / developer
ccc@domain.com / test / manager
ddd@domain.com / test / admin

with both option "Notice me on every connction" ON and OFF.

Are all your password built out of regular alphabet / numbers ? Is there any special type like !"·$%&/()='¡¿?\ºªñ´-_.;:

Thanks for your patience and comprehension.

@samoy2002
Copy link
Author

samoy2002 commented Mar 17, 2022 via email

@Patriboom
Copy link
Collaborator

Patriboom commented Mar 18, 2022 •
edited
Loading

Oh, wow! Thanks for such good information.
I'll analyze that and find a way to fix the problem.

I deleted the data given up, for security reason.

@samoy2002
Copy link
Author

samoy2002 commented Mar 18, 2022 via email

@Patriboom
Copy link
Collaborator

Thanks samoy2002 .... I've fixed the automatic set on "YES" It will be available soon : nothing on github for now.
Now, I'll work on the problem generated by the notice sent to admin.

Repository owner deleted a comment from samoy2002 Mar 18, 2022
@Patriboom
Copy link
Collaborator

Patriboom commented Mar 18, 2022 •
edited
Loading

I could found the exact problem, but I suspect that user with no email address is the source of such issue.
According to that, I've modified the mySQL query to exclude all pertinent data with no email address.

The pre-release 1.8 v7cm is supposed to fix the issue we discuss here.
Thanks for your patience.

@samoy2002
Copy link
Author

System updated to 1.8 v7cm and the problem still persists. I do notice that the accounts without an email are accounts that have been deleted in the past, they are marked as "1" in the deleted column. This did fix the problem with the "notice me" showing incorrectly. Would there be any negative to me deleting the users out of the database directly where the deleted column is set to "1"?

Here is the connection errors out of the log file. It does give a little more info.

2022-03-18 10:34:51 ERROR - SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'mbbizca_bugs207.USR.id' which is not in SELECT list; this is incompatible with DISTINCT

SQL: SELECT DISTINCT 0 AS project, 0 AS attached, 0 AS tages, USR.email, USR.firstname AS first, USR.lastname as last, CONCAT(USR.firstname, ' ', USR.lastname) AS user, USR.language, 'Robot of BUGS system' AS name, 'A user just connected to BUGS' AS title FROM users AS USR WHERE USR.role_id = 4 AND USR.preferences LIKE '%noticeOnLogIn=true%' AND USR.id != 10 AND TRIM(USR.email) != '' ORDER BY USR.id ASC

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-18 10:34:56 ERROR - SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'mbbizca_bugs207.USR.id' which is not in SELECT list; this is incompatible with DISTINCT

SQL: SELECT DISTINCT 0 AS project, 0 AS attached, 0 AS tages, USR.email, USR.firstname AS first, USR.lastname as last, CONCAT(USR.firstname, ' ', USR.lastname) AS user, USR.language, 'Robot of BUGS system' AS name, 'A user just connected to BUGS' AS title FROM users AS USR WHERE USR.role_id = 4 AND USR.preferences LIKE '%noticeOnLogIn=true%' AND USR.id != 10 AND TRIM(USR.email) != '' ORDER BY USR.id ASC

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-18 10:38:30 ERROR - SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'mbbizca_bugs207.USR.id' which is not in SELECT list; this is incompatible with DISTINCT

SQL: SELECT DISTINCT 0 AS project, 0 AS attached, 0 AS tages, USR.email, USR.firstname AS first, USR.lastname as last, CONCAT(USR.firstname, ' ', USR.lastname) AS user, USR.language, 'Robot of BUGS system' AS name, 'A user just connected to BUGS' AS title FROM users AS USR WHERE USR.role_id = 4 AND USR.preferences LIKE '%noticeOnLogIn=true%' AND USR.id != 10 AND TRIM(USR.email) != '' ORDER BY USR.id ASC

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238

@Patriboom
Copy link
Collaborator

Patriboom commented Mar 18, 2022 •
edited
Loading

System updated to 1.8 v7cm and the problem still persists. I do notice that the accounts without an email are accounts that have been deleted in the past, they are marked as "1" in the deleted column. This did fix the problem with the "notice me" showing incorrectly. Would there be any negative to me deleting the users out of the database directly where the deleted column is set to "1"?

Yes, you should keep them otherwise, showing an ticket, the notices added by the deleted users will generate error. The system need to keep trace of old users, hoping to show there names beside every notice they could have added.

About the error under user connection ... I think I've found something: you're system runs database under SQL and not under MySQL. I'll check this option. Under MySQL the sorting list doesn't need to find fields in select list.

Thanks for those details, I'll have a look on this tonight or during the weekend.

@Patriboom
Copy link
Collaborator

I rebuilt a great chunk of code and tested. It seams to be ok.

Revesion 1.7_co will be available within few minutes.

Pop-up when adding or supressing tags on issue does now dispear properly
Message to admin on user's login is now working properly.

Is the message sent to followers work properly also when anyone comments ? Not sure. Please let me know

@samoy2002
Copy link
Author

It looks like that worked. My users can log in again. Thank you

@samoy2002
Copy link
Author

New error when creating a new issue on a project.

Unhandled Exception
Message:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'following.project.id' in 'on clause'

SQL: SELECT following.project_id AS project, following.attached AS attached, following.tags AS tages, users.email, users.firstname, users.lastname, users.language, projects.name, projects_issues.title FROM following INNER JOIN users ON users.id = following.user_id INNER JOIN projects ON projects.id = following.project_id INNER JOIN projects_issues ON projects_issues.id = following.project.id WHERE following.id = ? AND users.email <> ? AND users.email IS NOT NULL AND following.issue_id = ? AND following.user_id NOT IN (?) AND following.project = ? ORDER BY users.id ASC

Bindings: array (
0 => 38,
1 => '',
2 => 1429,
3 => 0,
4 => 1,
)
Location:
/home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
Stack Trace:
#0 /home/mbbizca/public_html/bugs/app/laravel/database/connection.php(171): Laravel\Database\Connection->execute('SELECT followi...', Array) #1 /home/mbbizca/public_html/bugs/app/laravel/database/query.php(668): Laravel\Database\Connection->query('SELECT followi...', Array)
#2 /home/mbbizca/public_html/bugs/app/application/libraries/mail.php(136): Laravel\Database\Query->get()
#3 /home/mbbizca/public_html/bugs/app/application/controllers/project/issue.php(63): Mail::letMailIt(Array, 1, 'en')
#4 [internal function]: Project_Issue_Controller->post_new('38')
#5 /home/mbbizca/public_html/bugs/app/laravel/routing/controller.php(303): call_user_func_array(Array, Array)
#6 /home/mbbizca/public_html/bugs/app/laravel/routing/controller.php(266): Laravel\Routing\Controller->response('new', Array)
#7 /home/mbbizca/public_html/bugs/app/laravel/routing/controller.php(157): Laravel\Routing\Controller->execute('new', Array)
#8 /home/mbbizca/public_html/bugs/app/laravel/routing/route.php(153): Laravel\Routing\Controller::call('project.issue@n...', Array)
#9 /home/mbbizca/public_html/bugs/app/laravel/routing/route.php(124): Laravel\Routing\Route->response()
#10 /home/mbbizca/public_html/bugs/app/laravel/laravel.php(158): Laravel\Routing\Route->call()
#11 /home/mbbizca/public_html/bugs/index.php(17): require('/home/mbbizca/p...')
#12 {main}

2022-03-21 10:50:06 ERROR - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'following.project.id' in 'on clause'

SQL: SELECT following.project_id AS project, following.attached AS attached, following.tags AS tages, users.email, users.firstname, users.lastname, users.language, projects.name, projects_issues.title FROM following INNER JOIN users ON users.id = following.user_id INNER JOIN projects ON projects.id = following.project_id INNER JOIN projects_issues ON projects_issues.id = following.project.id WHERE following.id = ? AND users.email <> ? AND users.email IS NOT NULL AND following.issue_id = ? AND following.user_id NOT IN (?) AND following.project = ? ORDER BY users.id ASC

Bindings: array (
0 => 38,
1 => '',
2 => 1428,
3 => 0,
4 => 1,
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:50:43 ERROR - SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '11' for key 'PRIMARY'

SQL: INSERT INTO activity (id, description, activity) VALUES (11, 'Deleted a comment','delete_comment' ),(12, 'Edited a comment','edit_comment' );

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:50:46 ERROR - SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'ftcolor'

SQL: ALTER TABLE tags ADD COLUMN ftcolor VARCHAR(50) DEFAULT '#FFFFFF' AFTER bgcolor ;

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:50:48 ERROR - SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'temps_plan'

SQL: ALTER TABLE projects_issues ADD COLUMN temps_plan smallint(4) DEFAULT 30 AFTER duration;

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:50:49 ERROR - SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'ZH_TW'

SQL: ALTER TABLE tags ADD COLUMN ZH_TW VARCHAR(255) AFTER tag;

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:50:51 ERROR - SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'start_at'

SQL: ALTER TABLE projects_issues ADD COLUMN start_at datetime DEFAULT NULL AFTER created_at;

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:50:53 ERROR - SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'preferences'

SQL: ALTER TABLE users ADD COLUMN preferences text;

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:50:57 ERROR - SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'temps_fait'

SQL: ALTER TABLE projects_issues_comments ADD COLUMN temps_fait time DEFAULT NULL AFTER comment;

Bindings: array (
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238
2022-03-21 10:51:46 ERROR - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'following.project.id' in 'on clause'

SQL: SELECT following.project_id AS project, following.attached AS attached, following.tags AS tages, users.email, users.firstname, users.lastname, users.language, projects.name, projects_issues.title FROM following INNER JOIN users ON users.id = following.user_id INNER JOIN projects ON projects.id = following.project_id INNER JOIN projects_issues ON projects_issues.id = following.project.id WHERE following.id = ? AND users.email <> ? AND users.email IS NOT NULL AND following.issue_id = ? AND following.user_id NOT IN (?) AND following.project = ? ORDER BY users.id ASC

Bindings: array (
0 => 38,
1 => '',
2 => 1429,
3 => 0,
4 => 1,
) in /home/mbbizca/public_html/bugs/app/laravel/database/connection.php on line 238

@Patriboom
Copy link
Collaborator

Patriboom commented Mar 21, 2022 via email

@samoy2002
Copy link
Author

samoy2002 commented Oct 11, 2022 via email

@Patriboom Patriboom reopened this Oct 13, 2022
@Patriboom
Copy link
Collaborator

Hello Samoy2002,

I'll have a look on that.
Are saying that the issue is back ?
Are saying that the issue is still the same still a year long and has never been fixed ?

Recap:

  1. user can log
  2. You log as admin
  3. You change user's password
  4. This user can no longer login

Is that correct ?

Reading « No one of my 10 users can login » sounds like
0) All user can log

  1. Anyone of them change it's password
  2. No one can login anymore

I'm pretty sure to have analysed the way it occurs, while I'll try to fix it, may you please make clear the problem according to the above assumptions or correct them.

@Patriboom
Copy link
Collaborator

Patriboom commented Oct 13, 2022 •
edited
Loading

Patch is available on master branch.

@Patriboom Patriboom added Fixed and removed ¿ Fixed ? For next release What is planed for next release or next version If OK -> Close it Please close this issue if your tests demonstrate that the issue is fixed. Fix available in pre-release Please follow the link to all releases, you'll find there the latest pre-release with all the fixes labels Oct 13, 2022
@Patriboom Patriboom linked a pull request Nov 11, 2022 that will close this issue
Patch on SendMail when user logs in #833
Merged
@Patriboom Patriboom added the If OK -> Close it Please close this issue if your tests demonstrate that the issue is fixed. label Nov 11, 2022
@Patriboom
Copy link
Collaborator

I'll be back at work soon. Stay tuned.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Patriboom Patriboom

Labels
bug Fixed If OK -> Close it Please close this issue if your tests demonstrate that the issue is fixed.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Patch on SendMail when user logs in Patriboom/bugs
2 participants
@Patriboom @samoy2002