pixeline/bugs

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

samoy2002 opened this issue · 29 comments

samoy2002 commented

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 commented

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 commented
Patriboom commented

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 commented
Patriboom commented

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

Patriboom commented

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 commented

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 commented
Patriboom commented
Patriboom commented

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 commented
Patriboom commented

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 commented

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 commented
Patriboom commented

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 commented
Patriboom commented

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.

Patriboom commented

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 commented

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 commented

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 commented

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 commented

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

samoy2002 commented

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 commented
samoy2002 commented
Patriboom commented

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 commented

Patch is available on master branch.

Patriboom commented

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