Welcome Guest, Not a member yet? Register   Sign In
Inconsistent query results in CI, consistent in raw PHP and MySQL Workbench

(This post was last modified: 07-21-2020, 08:41 AM by whatweknowisadrop.)

First, let me say that I'm not blaming the framework, I'm sure this is some detail I'm supposed to know and am overlooking. Nonetheless, I'm completely lost as to how this strange behavior could happen. 

I have a query that seems to rotate through three results throughout the day:

1.) The vast majority of the time, the query gives expected results. Results match what I get from running the query in a raw mysqli connection in a test PHP file and in MySQL workbench.

2.) Two of the 27 rows are invalid (always the same two). Raw PHP and Workbench return the correct result.

3.) Eight of the of 27 rows are invalid (always the same eight). Raw PHP and Workbench return the correct result.

Whatever state it decides to be in, it stays in for a period of time (generally a few minutes in the case of the erroneous states). Scenario 2 seems to be far more common than scenario 3. At any particular time, I get the same result from multiple devices. 

My mentor's first suspicion was that it was an issue with some members of a db connection pool. I'm not sure if CI even maintains a connection pool, but we tried restarting the application to see. No dice. 

Anyways.. that's all I've got. If anybody has ideas I'd greatly appreciate your input. Thank you for taking the time to read this.

Hi, are you logging all queries, so you know they don't change?

And how are you generating said queries? We need some code.

What version of CodeIgniter?

As far as I know, we don't have any DB pools. As long as you have pconnect (talking about CI 3 here) disabled. We close everything at script end.

(This post was last modified: 07-21-2020, 10:12 AM by whatweknowisadrop.)

I am logging the query and it's consistently the same.

$db['school_db']['dbdriver'] = 'mysqli';
$db['school_db']['dbprefix'] = '';
$db['school_db']['pconnect'] = FALSE;
$db['school_db']['db_debug'] = FALSE;
$db['school_db']['cache_on'] = FALSE;
$db['school_db']['cachedir'] = '';
$db['school_db']['char_set'] = 'utf8';
$db['school_db']['dbcollat'] = 'utf8_general_ci';
$db['school_db']['swap_pre'] = '';
$db['school_db']['autoinit'] = TRUE;
$db['school_db']['stricton'] = FALSE;

These are my connection options. This project was migrated from CI2 to CI3 (not sure the exact release, I can try and find out if relevant).

The exact query is:

select schedules.start_time as date, student_lesson_progress.lesson_status, largest_package_details.lesson_order
    from (
        select package_details.lesson_order, package_details.lesson_id, lessons.lesson_name, package_id from package_details
        join lessons on lessons.lesson_id = package_details.lesson_id
        where package_details.package_id in (select package_id from (
        select package_id, max(package_size)
        from (SELECT package_id, count(*) package_size FROM package_details group by package_id order by package_size desc, package_id asc) package_size) largest_package))
    left join student_lesson_progress on largest_package_details.lesson_id = student_lesson_progress.lesson_id
                        and student_lesson_progress.student_id = ? -- this id is the only argument, but for all of my testing I'm using the same value
    left join schedules on schedules.schedule_id = student_lesson_progress.schedule_id
    where student_lesson_progress.product_id > 0 or student_lesson_progress.product_id is null
    order by largest_package_details.lesson_order;

Wrapped in PHP:

PHP Code:
            $query "the query in the code block above";
            $args = array($student_id);

            $query $this->db->query($query,$args);
            return $query->result_array(); 

I'm not particularly proud of this query, but essentially:

Lessons are templates for schedules that dictate what content will be covered. All schedules for a particular lesson will cover the same material.
A package is a named collection of lessons. The details of what lessons are included in a package are stored in package_details alongside what order the lessons should be in.
Schedules set a start and end time for lesson and can be assigned to one or more students curriculum (student_lesson_progress). 

Line 2-8 find the package with the largest collection of lessons. The lesson_order of the lessons in the largest package is important to the results of the query.

For a particular student, for each lesson in the largest package, find the lesson status (completed, invited, not scheduled) in the student_lesson_progress that corresponds to that lesson. If a student_lesson_progress is found, grab the start time from the schedule in student lesson progress. 

In the rows that differ, lesson_order is maintained while date and lesson_status are null/0.

{"date":"2019-04-03 16:30:00","lesson_status":"3","lesson_order":"0","package_id":"1"}
{"date":"2019-04-10 16:30:00","lesson_status":"3","lesson_order":"1","package_id":"1"}
{"date":"2019-04-17 16:30:00","lesson_status":"3","lesson_order":"2","package_id":"1"}
{"date":"2019-04-24 16:30:00","lesson_status":"3","lesson_order":"3","package_id":"1"}
{"date":"2019-05-01 16:30:00","lesson_status":"3","lesson_order":"4","package_id":"1"}
{"date":"2019-05-15 19:00:00","lesson_status":"3","lesson_order":"5","package_id":"1"}
{"date":"2019-09-04 16:00:00","lesson_status":"3","lesson_order":"6","package_id":"1"}
{"date":"2019-09-18 16:00:00","lesson_status":"3","lesson_order":"7","package_id":"1"}
{"date":"2019-08-13 19:00:00","lesson_status":"3","lesson_order":"8","package_id":"1"}
{"date":"2019-10-02 18:00:00","lesson_status":"3","lesson_order":"9","package_id":"1"}
{"date":"2019-10-02 19:00:00","lesson_status":"3","lesson_order":"10","package_id":"1"}
{"date":"2019-10-16 19:00:00","lesson_status":"3","lesson_order":"11","package_id":"1"}
{"date":"2019-11-01 11:00:00","lesson_status":"3","lesson_order":"12","package_id":"1"}
{"date":"2019-11-01 12:00:00","lesson_status":"3","lesson_order":"13","package_id":"1"}
{"date":"2019-10-23 19:00:00","lesson_status":"3","lesson_order":"14","package_id":"1"}
{"date":"2019-12-04 18:00:00","lesson_status":"3","lesson_order":"15","package_id":"1"}
{"date":"2019-12-04 19:00:00","lesson_status":"3","lesson_order":"16","package_id":"1"}
{"date":"2020-02-17 19:00:00","lesson_status":"3","lesson_order":"17","package_id":"1"}
{"date":"2020-01-22 15:45:00","lesson_status":"3","lesson_order":"18","package_id":"1"}
{"date":"2020-01-22 17:00:00","lesson_status":"3","lesson_order":"19","package_id":"1"}
{"date":"2020-04-20 13:30:00","lesson_status":"3","lesson_order":"20","package_id":"1"}
{"date":"2020-07-17 09:00:00","lesson_status":"3","lesson_order":"21","package_id":"1"}
{"date":"2020-06-24 16:30:00","lesson_status":"3","lesson_order":"22","package_id":"1"}
{"date":"2020-06-24 17:30:00","lesson_status":"3","lesson_order":"23","package_id":"1"}
{"date":"2020-05-27 14:30:00","lesson_status":"3","lesson_order":"24","package_id":"1"}
{"date":"2020-07-17 10:00:00","lesson_status":"3","lesson_order":"25","package_id":"1"}
{"date":"2020-07-20 10:00:00","lesson_status":"3","lesson_order":"26","package_id":"1"}

For the time being, I'm going to replace this particular query with mysqli prepared statements and see if the issue goes away.

If it's always the same, it's not a query builder failure (or prepared statement) as it's what you are doing but with CodeIgniter at the moment.

I'm afraid I don't have tips for you.


Have you tried running the queries directly in the database to see if it has any issues? Also, what does the generated query look like after it places the student id into the query? Also, what version of PHP? MySQL?

Theme © iAndrew 2016 - Forum software by © MyBB