0

On an Ubuntu 16.04.4 LTS machine running MySQL (Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper) we are experiencing some very peculiar behavior.

There is a schema, mydb, and two users, root@% and mydb_admin@%. The permissions for the two users are as follows:

GRANT ALL PRIVILEGES ON `mydb`.* TO 'mydb_admin'@'%'
GRANT ALL PRIVILEGES ON *.* TO ‘root'@'%'

Included in the schema is a stored procedure which both creates and drops tables – not temporary tables or table variables, but real tables.

Using MySQL Workbench, and logged into the server as root, we opened a query window and called the stored procedure. The resulting table is correct (where correct means that the values returned are as they should be). We then logged out as root and logged in as mydb_admin, and the resulting table was incorrect (the values returned in certain columns were not as they should be). Another time, we logged in as root, ran the stored procedure and the results were incorrect. We then made some changes to the stored procedure (which had nothing to do with the incorrect results), ran it again, and the results were correct.

We also have a php script that executes the stored procedure. When we run it, accessing the database as either user, the results are completely incorrect. By completely incorrect, we mean that the columns returned reflect an older version of the stored procedure.

We have been unable to find any repeatable pattern to this behavior and, obviously, we are very concerned as we don’t know if other stored procedure calls are similarly failing. What should we do to resolve this problem?

  • I think you need to provide more details: the create procedure statement for the procedure, how you're calling it (parameters, which user, which database), and the results you get. – dbdemon Mar 30 '18 at 14:56
0

(This should be a reply to dbdemon, but I cannot post it as such)

One of the table is rdefined as follows:

DROP TABLE IF EXISTS hss;
CREATE TABLE hss (
    high_school VARCHAR(255),
    cntStudents INT,
    cntOppsViewed INT,
    cntActiveStudents INT,
    cntApplications INT,
    aryRaceDemo VARCHAR(255),
    aryTopInterests VARCHAR(255),
    aryTopOppTypes VARCHAR(255),
    aryGradYears VARCHAR(255)
);

After it is defined, a query is run that fills in (among other things) the high_school column.

The problem observed is occurring with the values stored in the aryTopInterests and aryTopOppTypes. The data for both is generated in a similar manner:

DROP TABLE IF EXISTS hssInterest;
CREATE TABLE hssInterest (
    high_school VARCHAR(255),
    interest_id INT,
    interest_name VARCHAR(255),
    cntInterest INT);

INSERT INTO hssInterest (high_school, interest_id, interest_name, cntInterest)
SELECT hss.high_school, interest_areas.id, interest_areas.title, count(*) AS cntInterest
FROM hss, nonp, opportunities, interest_areas, opportunity_interest, views
WHERE nonp.id = views.non_provider_id AND
    views.opportunity_id = opportunities.id AND
    opportunities.id  = opportunity_interest.opportunity_id AND
    opportunity_interest.interest_area_id = interest_areas.id AND
    nonp.high_school = hss.high_school AND
    views.created_at >= sDate AND 
    views.created_at <= eDate
GROUP BY hss.high_school, interest_areas.id
ORDER BY cntInterest DESC;

DROP TABLE IF EXISTS hssInterestTop;
CREATE TABLE hssInterestTop (
    high_school VARCHAR(255),
    interest_name VARCHAR(255),
    cntInterest INT);

INSERT INTO hssInterestTop (high_school, interest_name, cntInterest)
SELECT high_school, interest_name, cntInterest
FROM
    (SELECT high_school, interest_name, cntInterest,
          @int_rank := IF(@current_hs = high_school, @int_rank + 1, 1) AS int_rank,
          @current_hs := high_school 
    FROM hssInterest
    ORDER BY high_school, cntInterest DESC
    ) ranked
WHERE int_rank <= 3;

UPDATE hss AS T1, (
SELECT high_school, GROUP_CONCAT(CONCAT_WS(', ', interest_name) ORDER BY cntInterest DESC SEPARATOR ';') AS topInterests
FROM hssInterestTop
GROUP BY high_school) AS T2
SET T1.aryTopInterests = T2.topInterests
WHERE T1.high_school = T2.high_school;

The problem observed is this: The result stored in the column aryTopInterests should have exactly three semicolon separated items. Sometimes, this is the result returned. In other cases, there are more than three semicolon separated items. With respect to aryTopOppTypes, again, there should always be exactly three semicolon separated items; but sometimes there is only one item. No - this is not an issue of the underlying data; in all cases, three items exist to be returned.

  • One more comment: This issue is not related to the instance of MySQL on which it is run. I just copied the database to another machine and got the same weird behavior - works sometimes and not others. – GRoston Apr 5 '18 at 22:36
  • The answer can be found here: xaprb.com/blog/2006/12/15/…. I rewrote my code based on what I learned and it now works properly every time. – GRoston Apr 8 '18 at 16:18
  • No indexes?? No PRIMARY KEY?? – Rick James Apr 17 '18 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.