An SQL error occurred: SQLSTATE[42000]:

Hosting Support
1

Website URL

https://gradingsystem.great-site.net/

Sample Account
username: Sample123
password: Sample@123

Error Message

DataTables warning: table id=uploadGradeTable - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Other Information

In my machine its working and this is the expected Return
{“draw”:1,“recordsTotal”:7,“recordsFiltered”:7,“data”:[[3,“Chicky Bracchi”,“[{"component_value_id": 97, "student_grade": 95, "student_id": 8, "component_value": 100},{"component_value_id": 98, "student_grade": 10, "student_id": 8, "component_value": 10},{"component_value_id": 99, "student_grade": 0, "student_id": 8, "component_value": 0},{"component_value_id": 100, "student_grade": 0, "student_id": 8, "component_value": 0},{"component_value_id": 101, "student_grade": 0, "student_id": 8, "component_value": 0},{"component_value_id": 102, "student_grade": 70, "student_id": 8, "component_value": 100},{"component_value_id": 103, "student_grade": 95, "student_id": 8, "component_value": 100},{"component_value_id": 104, "student_grade": 0, "student_id": 8, "component_value": 0},{"component_value_id": 105, "student_grade": 0, "student_id": 8, "component_value": 0},{"component_value_id": 106, "student_grade": 0, "student_id": 8, "component_value": 0},{"component_value_id": 107, "student_grade": 8, "student_id": 8, "component_value": 10},{"component_value_id": 108, "student_grade": 45, "student_id": 8, "component_value": 75}]”,“1230”,“M”],[3,“Fourth S. Student4”,“[{"component_value_id": 97, "student_grade": 98, "student_id": 7, "component_value": 100},{"component_value_id": 98, "student_grade": 9, "student_id": 7, "component_value": 10},{"component_value_id": 99, "student_grade": 0, "student_id": 7, "component_value": 0},{"component_value_id": 100, "student_grade": 0, "student_id": 7, "component_value": 0},{"component_value_id": 101, "student_grade": 0, "student_id": 7, "component_value": 0},{"component_value_id": 102, "student_grade": 70, "student_id": 7, "component_value": 100},{"component_value_id": 103, "student_grade": 70, "student_id": 7, "component_value": 100},{"component_value_id": 104, "student_grade": 0, "student_id": 7, "component_value": 0},{"component_value_id": 105, "student_grade": 0, "student_id": 7, "component_value": 0},{"component_value_id": 106, "student_grade": 0, "student_id": 7, "component_value": 0},{"component_value_id": 107, "student_grade": 9, "student_id": 7, "component_value": 10},{"component_value_id": 108, "student_grade": 69, "student_id": 7, "component_value": 75}]”,“1230”,“M”],[3,“New R. Student”,“[{"component_value_id": 97, "student_grade": 92, "student_id": 9, "component_value": 100},{"component_value_id": 98, "student_grade": 10, "student_id": 9, "component_value": 10},{"component_value_id": 99, "student_grade": 0, "student_id": 9, "component_value": 0},{"component_value_id": 100, "student_grade": 0, "student_id": 9, "component_value": 0},{"component_value_id": 101, "student_grade": 0, "student_id": 9, "component_value": 0},{"component_value_id": 102, "student_grade": 70, "student_id": 9, "component_value": 100},{"component_value_id": 103, "student_grade": 70, "student_id": 9, "component_value": 100},{"component_value_id": 104, "student_grade": 0, "student_id": 9, "component_value": 0},{"component_value_id": 105, "student_grade": 0, "student_id": 9, "component_value": 0},{"component_value_id": 106, "student_grade": 0, "student_id": 9, "component_value": 0},{"component_value_id": 107, "student_grade": 10, "student_id": 9, "component_value": 10},{"component_value_id": 108, "student_grade": 44, "student_id": 9, "component_value": 75}]”,“1230”,“M”],[3,“Eduardo Tacorda”,“[{"component_value_id": 97, "student_grade": 1, "student_id": 6, "component_value": 100},{"component_value_id": 98, "student_grade": 5, "student_id": 6, "component_value": 10},{"component_value_id": 99, "student_grade": 0, "student_id": 6, "component_value": 0},{"component_value_id": 100, "student_grade": 0, "student_id": 6, "component_value": 0},{"component_value_id": 101, "student_grade": 0, "student_id": 6, "component_value": 0},{"component_value_id": 102, "student_grade": 70, "student_id": 6, "component_value": 100},{"component_value_id": 103, "student_grade": 70, "student_id": 6, "component_value": 100},{"component_value_id": 104, "student_grade": 0, "student_id": 6, "component_value": 0},{"component_value_id": 105, "student_grade": 0, "student_id": 6, "component_value": 0},{"component_value_id": 106, "student_grade": 0, "student_id": 6, "component_value": 0},{"component_value_id": 107, "student_grade": 5, "student_id": 6, "component_value": 10},{"component_value_id": 108, "student_grade": 0, "student_id": 6, "component_value": 75}]”,“1230”,“F”],[3,“Student F. Gender”,“[{"component_value_id": 97, "student_grade": 85, "student_id": 11, "component_value": 100},{"component_value_id": 98, "student_grade": 9, "student_id": 11, "component_value": 10},{"component_value_id": 99, "student_grade": 0, "student_id": 11, "component_value": 0},{"component_value_id": 100, "student_grade": 0, "student_id": 11, "component_value": 0},{"component_value_id": 101, "student_grade": 0, "student_id": 11, "component_value": 0},{"component_value_id": 102, "student_grade": 95, "student_id": 11, "component_value": 100},{"component_value_id": 103, "student_grade": 70, "student_id": 11, "component_value": 100},{"component_value_id": 104, "student_grade": 0, "student_id": 11, "component_value": 0},{"component_value_id": 105, "student_grade": 0, "student_id": 11, "component_value": 0},{"component_value_id": 106, "student_grade": 0, "student_id": 11, "component_value": 0},{"component_value_id": 107, "student_grade": 9, "student_id": 11, "component_value": 10},{"component_value_id": 108, "student_grade": 36, "student_id": 11, "component_value": 75}]”,“1230”,“F”],[3,“Student S. Gender”,“[{"component_value_id": 97, "student_grade": 94, "student_id": 10, "component_value": 100},{"component_value_id": 98, "student_grade": 10, "student_id": 10, "component_value": 10},{"component_value_id": 99, "student_grade": 0, "student_id": 10, "component_value": 0},{"component_value_id": 100, "student_grade": 0, "student_id": 10, "component_value": 0},{"component_value_id": 101, "student_grade": 0, "student_id": 10, "component_value": 0},{"component_value_id": 102, "student_grade": 70, "student_id": 10, "component_value": 100},{"component_value_id": 103, "student_grade": 70, "student_id": 10, "component_value": 100},{"component_value_id": 104, "student_grade": 0, "student_id": 10, "component_value": 0},{"component_value_id": 105, "student_grade": 0, "student_id": 10, "component_value": 0},{"component_value_id": 106, "student_grade": 0, "student_id": 10, "component_value": 0},{"component_value_id": 107, "student_grade": 10, "student_id": 10, "component_value": 10},{"component_value_id": 108, "student_grade": 57, "student_id": 10, "component_value": 75}]”,“1230”,“F”],[3,“Student S. Inside”,“[{"component_value_id": 97, "student_grade": 0, "student_id": 12, "component_value": 100},{"component_value_id": 98, "student_grade": 9, "student_id": 12, "component_value": 10},{"component_value_id": 99, "student_grade": 0, "student_id": 12, "component_value": 0},{"component_value_id": 100, "student_grade": 0, "student_id": 12, "component_value": 0},{"component_value_id": 101, "student_grade": 0, "student_id": 12, "component_value": 0},{"component_value_id": 102, "student_grade": 70, "student_id": 12, "component_value": 100},{"component_value_id": 103, "student_grade": 70, "student_id": 12, "component_value": 100},{"component_value_id": 104, "student_grade": 0, "student_id": 12, "component_value": 0},{"component_value_id": 105, "student_grade": 0, "student_id": 12, "component_value": 0},{"component_value_id": 106, "student_grade": 0, "student_id": 12, "component_value": 0},{"component_value_id": 107, "student_grade": 5, "student_id": 12, "component_value": 10},{"component_value_id": 108, "student_grade": 62, "student_id": 12, "component_value": 75}]”,“1230”,“F”]]}

The error is under of the List of course/subject

2

The error suggest that our server don’t accept such big queries, while your local machine might do.

The solution is right in the error message, though there’s no guarantee whether it would work on our servers or not:

SET SQL_BIG_SELECTS=1;

or

SET MAX_JOIN_SIZE=<Some big number>;

You have to execute these before the real SELECT query. If these didn’t work, however, you might want to optimize your code. (I suppose this is custom coded.)

7 Likes
3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.