PHP Queries Not Working After MySQL to Postgres Conversion + Odd Errors

Question

Setup - PHP 7 app. - Postgres is on Heroku Hobby v11.x - MySQL version was v5.5.56 and protocol 10 - I do not have a great understanding of PHP - I do not have a great knowledge of lower-level Postgres conversions.

I migrated from MySQL to Postgres with pgloader.

pgloader --dry-run --with 'create indexes' --with 'create indexes' --with 'foreign keys' --with 'downcase identifiers'  --with 'create tables' --with 'include drop' mysql://<<hidden>>@us-cdbr-iron-east-03.cleardb.net/heroku_509e1e230baf4be postgres://<<hidden>>@ec2-23-21-177-102.compute-1.amazonaws.com:5432/dflfjbhhq18cav?sslmode=require

I know that the connection is working. Some of the queries are working after swapping the MySQL functions out with the Postgres - I know this as I can see data populating. I've also had to make some tweaks to get the queries to work.

The query for duplicating a field has a valid SQL statement, but I'm getting the following error. I know the query to be good as I've echoed it out and pasted it into my Navicat SQL window and it yields the proper results.

pg_query(): Query failed: ERROR:  permission denied for table campaigns

The DB user seems to have all the proper permissions to access the campaigns table and get what it needs.

I moved on to find yet another very odd issue.

No matter what I use to try to run a query with PHP with a valid SQL statement, I'm getting no data back.
-pg_pquery -pg_fetch_array -pg_query_params -pg_fetch_array

See code example below.

//Get the existing number of quota_deducted
    $q = 'SELECT app, quota_deducted FROM campaigns WHERE id = '.$campaign_id;
    $r = pg_query($mysqli, $q);
    if ($r) 
    {
        while($row = pg_fetch_array($r)) 
        {
            $app = $row['app'];
            $current_quota_deducted = $row['quota_deducted']=='' ? 0 : $row['quota_deducted'];
        }

        //Check if monthly quota needs to be updated
        $q2 = 'SELECT allocated_quota, current_quota FROM apps WHERE id = '.$app;
        $r2 = pg_query($mysqli, $q2);
        if($r2) 
        {
            while($row2 = pg_fetch_array($r2)) 
            {
                $allocated_quota = $row2['allocated_quota'];
                $current_quota = $row2['current_quota'];
            }
        }
        $updated_quota = $current_quota - $current_quota_deducted;

        //Update quota if a monthly limit was set
        if($allocated_quota!=-1)
        {           
            //if so, update quota
            $q3 = 'UPDATE apps SET current_quota = '.$updated_quota.' WHERE id = '.$app;
            pg_query($mysqli, $q3);
        }
    }

I see errors in the trailing logs for the apache2 terminal window, but I don't see any real connections or activity on the database pgAdmin.

Logs on the Heroku DB also don't seem to be coming in? Not sure if this is due to it being only a Hobby teir.

I've checked all the obvious stuff like making sure we're connected to the right DB and so on.

When using Navicate, I'm getting an error I've never seen before when trying to call the table DESIGN function. I get the error below. Not sure if this is having an effect on the above issue. I was able to test other same versions of Postgres to find that this error below was not present. So perhaps pgloader is creating some issues at a low level that can't be resolved.

ERROR: Column "proisagg" doe snot exist
LINE 1: ...e JOIN pg_language lng ON lng.oid=p.prolang WHERE proisagg = .. 
HINT: Perhaps you meant to reference the column "p.prolang".

Here is the query we are trying to run. This does work in Navicat.


INSERT INTO campaigns (userid, app, from_name, from_email, reply_to, title, label, plain_text, html_text, query_string, bounce_setup, complaint_setup, wysiwyg, opens_tracking, links_tracking) VALUES (2, 152, 'Person Name', 'campaignsupport@company.com', 'campaignsupport@company.com', 'Test', '', 'Test plain text.', 'asdf', '', 0, 0, 1, 1, 1)

OR


INSERT INTO public.campaigns (userid, app, from_name, from_email, reply_to, title, label, plain_text, html_text, query_string, bounce_setup, complaint_setup, wysiwyg, opens_tracking, links_tracking) VALUES (2, 152, 'Person Name', 'campaignsupport@company.com', 'campaignsupport@company.com', 'Test', '', 'Test plain text.', 'asdf', '', 0, 0, 1, 1, 1)

I've been working on this issue for the last 20 hours and am stumped any help or thoughts would be greatly apricated.

Warmest regards,

Casey Havenor

Probably you just didn't rename the new connection variable, but having `pg_query($mysqli, $sql_query)` is really fishy to my opinion. — mrbm, Jul 24 '19 at 20:10
$mysqli comes from the pg_pconection result. It is a resource. — cph, Jul 25 '19 at 02:25
Hardly any of the SQL posted.. the error contains a JOIN with pg_language, the alias 'p' is used for another table containg a field name 'lang_id'... I would guess the alias is "pro" and the field "isagg", but the dot was accidentially removed. Search your complete code for the SQL fragment! — Honk der Hase, Jul 25 '19 at 21:29

score 1 · Accepted Answer · answered Jul 26 '19 at 06:45

Your question contains a whole load of issues, and not really enough information to fully solve any of them, so I will try to provide some troubleshooting steps which may help you to find the solutions.

Problem #1

The command:

INSERT INTO campaigns (userid, app, from_name, from_email, reply_to, title, label, plain_text, html_text, query_string, bounce_setup, complaint_setup, wysiwyg, opens_tracking, links_tracking) VALUES (2, 152, 'Person Name', 'campaignsupport@company.com', 'campaignsupport@company.com', 'Test', '', 'Test plain text.', 'asdf', '', 0, 0, 1, 1, 1)

fails with error:

pg_query(): Query failed: ERROR:  permission denied for table campaigns

However you are able to execute this SQL command when you run it in Navicat.

In the context you have described, there are only a few reasons why this error could occur:

You are not connected to the database with a user that has the required permissions (possibly not the user that you think you are connected as). To check which user you are connected with issue query:
```
SELECT current_user;
```
Issue this from within your application, immediately before the failing query, using the same connection as you use for the failed query. Log the output somewhere you can see it - eg. the apache error log.

Issue the same query from Navicat to see what user is used there. If not the same as the application is using, then reconnect with the same user and see if the query still fails.
The table does not have the permission grants that you think it has.

I am not familiar with Navicat: it may provide the means to check the permissions on a table. If not (or if it is not working, see below) then you could connect using the psql command line tool and use command \z campaigns to see the set of grants on the table.
You are not accessing the table that you think you are, perhaps one that requires different permissions to the one that you think you are accessing. This could happen if the schema path was resulting the a table of the same name in a different schema being selected. However I don't think this is happening because you mentioned you have tried specifying the fully qualified name public.campaigns.
You are not connected to the correct database. You state that you have checked this - if you have reached this point and still not found the cause I suggest checking again!

Problem #2

No matter what I use to try to run a query with PHP with a valid SQL statement, I'm getting no data back.

Simplified, your code looks like this:

$r = pg_query($mysqli, $q);
if ($r) 
{
    // do something
}

I'm no php expert, but checking the documentation for pg_query I found it says:

If an error occurs, and FALSE is returned, details of the error can be retrieved using the pg_last_error() function if the connection is valid.

You are not checking for any error conditions. I would guess that the queries are failing, maybe with the same permissions issues mentioned above. I suggest adding an else clause to the code which calls pg_last_error() and displays the result.

Problem #3

Navicat displays this error:

ERROR: Column "proisagg" doe snot exist
LINE 1: ...e JOIN pg_language lng ON lng.oid=p.prolang WHERE proisagg = .. 
HINT: Perhaps you meant to reference the column "p.prolang".

Up until postgresql 11, the internal postgresql table pg_proc contained a column proisagg which was true if the row represents an aggregate function.

From Postgresql 11 onwards this column is replaced by a column prokind which has different values to describe if the row represents an aggregate function, window function, normal function or procedure.

As mentioned above I am not familiar with Navicat, but I would guess that the version you are using has not been updated to take account of this change.

So the software re-creates an additional DB connection that was referenced outside of the repo. That part of the original source code that overwrites the working db resource with one that is out of date. Thanks! Problem 2 - Had logic in there before. I put it back. Problem 3 - I'm not sure if this error matters. — cph, Jul 30 '19 at 09:31

PHP Queries Not Working After MySQL to Postgres Conversion + Odd Errors

1 Answers1

Problem #1

Problem #2

Problem #3