Mysql Is Drunk.

Yii 1.1 General Discussion
1

Hi,

One of us is drunk, MySQL, Code, Me:

The following query, when executed, runs and does everything ok, but it seems to flip between two outcomes…

Run it once, you get say Showing rows 0 - 17 ( 18 total, Query took 0.0729 sec)

Run it a second time, you get say Showing rows 0 - 16 ( 17 total, Query took 0.0698 sec)

Run a third time Showing rows 0 - 18 ( 19 total, Query took 0.0699 sec)

It seems two of the users are often appeaing and disapearing…

Im guessing my query is ridiculos :blink: and where it’s doing realtime calculations, it’s causing the different results each time i run it…

Can someone take a look and streamline or do something clever? lol Im sure its very obviously to a DB pro B)




  $query = Yii::app()->db->createCommand(

  

  "SELECT 

    profiles.user_id AS 'adviser_id',

    profiles.firstname AS 'adviser_firstname',

    profiles.lastname AS 'adviser_lastname',

    users.email AS 'adviser_email',

    leads.id_lead AS 'lead_id',

    client_1_lastname AS 'client_last_name',

    visit_adviser_notified_date AS 'adviser_notified',

    DATE_FORMAT(NOW(), '%d/%m/%Y') AS now,

    DATEDIFF(CURDATE(),STR_TO_DATE(visit_adviser_notified_date, '%d/%m/%Y')) AS 'days_elapsed',

    

    group_concat(leads.client_1_lastname) AS outstanding_client_names,

    group_concat(leads.id_lead) AS outstanding_lead_ids,

    count(*) AS outstanding_amount


FROM profiles, users, leads


WHERE profiles.user_id = users.id

    AND profiles.user_id = leads.id_user

    AND visit_adviser_notified_date != ''

    #AND id_user = ".$adviserID."


GROUP BY profiles.user_id  


HAVING days_elapsed >=3


ORDER BY outstanding_amount DESC


")->queryAll();
2

First You should check what is the problem, I assume that you are not drunk, becouse you can type some letters :D so there are only two more options :P. Please try your query in some phpmyadmin or mysql workbench or something like that and than You will have only one option :P

3

haha, very true!

I have tried it in phpmyadmn and workbench when creating the code, and has the same effects.

The problem is that say user1 has an ‘outstanding_amount’ of 549 and user2 has 400 - and its sorted by ‘outstanding_amount’ one of those users disapears…

the rows return total changes… im wondering if im asking the server to do too much too quickly… and it just cant cope? theres only about 15-20 users, and each have from 500 to 1 child row assigned to them…

Its odd…

I need it to be stable as im going to run a MAIL() script to email these results to the respective user to remind them that they have stuff outstanding to do…

I think I should get drunk haha

4

Yes its odd, maybe changing mysql engine could help, but i dont think thats very good solution for your problem, but anyway always you can try change to innodb or myisam depends on what you are using now.

5

Hi,

I just removed the HAVING part from the query


HAVING days_elapsed >=3

so now each time i refresh the data is the same, its rock solid…

So, how can I achieve the same thing as having?

also, @mirunho im using MyISAM

6

OK, progress…

Im thinking that the query is having a hard time, because its trying to do calculation using column that dont exist yet… make sense?

So, I removed the HAVING bit, and added to my WHERE clause, but instead of refering to a ‘calculated column’ I the calculation inside the WHERE clause…

So now the query returns the same number of rows each time, and on the surface appears to be working!!

Query is messy though… :(


SELECT 

    profiles.user_id AS 'adviser_id',

    profiles.firstname AS 'adviser_firstname',

    profiles.lastname AS 'adviser_lastname',

    users.email AS 'adviser_email',

    #leads.id_lead AS 'lead_id',

    client_1_lastname AS 'client_last_name',

    visit_adviser_notified_date AS 'adviser_notified',

    DATE_FORMAT(NOW(), '%d/%m/%Y') AS now,

    #DATEDIFF(CURDATE(),STR_TO_DATE(visit_adviser_notified_date, '%d/%m/%Y')) AS 'days_elapsed',

    

    group_concat(leads.client_1_lastname) AS outstanding_client_names,

    group_concat(leads.id_lead) AS outstanding_lead_ids,

    count(*) AS outstanding_amount


FROM profiles, users, leads


WHERE profiles.user_id = users.id

    AND profiles.user_id = leads.id_user

    AND visit_adviser_notified_date != ''

    #AND id_user = ".$adviserID."

    AND DATEDIFF(CURDATE(),STR_TO_DATE(visit_adviser_notified_date, '%d/%m/%Y')) >= 3


GROUP BY profiles.user_id  


#HAVING days_elapsed >=3


ORDER BY outstanding_amount DESC

I also added ORDER BY to the GROUP_CONCAT so now the data doesnt jiggle around!


group_concat(leads.id_lead ORDER BY leads.id_lead ASC)