Ajax called script stops after PHQL query execution

Check php logs.

Maybe also try a simpler sql statement. I'm guessing it's blowing up in there somewhere and not erroring properly. Maybe wrap a try/catch around the $result = ... part of it & var_dump the error if one occurs.

There are no logs, nor exeption about this problem. But when I tried to simlify the SQL keeping only the one part of it without IF and CASE (I've tried that version earlier, too), it ran correctly. This is strange, because I has a similar UPDATE query with IF somewhere else and it works perefectly there, so it can't be said that PHQL doesn't support it. If I keep it simple, then I should run two queries, because I have two cases as you can see:

UPDATE [\Main\Models\Messages] AS messages SET messages.sender_deleted = IF(messages.sender_id = :sender_id:,:sender_deleted:,messages.sender_deleted), messages.receiver_deleted = IF(messages.receiver_id = :receiver_id:,:receiver_deleted:,messages.receiver_deleted) WHERE messages.conversation_id = :conv:

Do you have any suggestion how can I rewrite this SQL to be able to do it with one query?

Then maybe just try to put application->handle() into try catch block and display exception message ? Is there any ? Maybe you have seg fault ?

Yes, the application handle is already in a try/catch, but no error is thrown in this exact case. The server also logs, but no error in error logs, too. As I said, the script just stops after this query execution and nothing can be dumped or echoed after it, so ajax gets back nothing but an error, that's all. As I said, when I change the query, it works, but it should have been work the original way as it has no error in it, it can be executed in the database handler, but not from Phalcon. Earlier I've already run into situations when PHQL just couldn't handle certain kind of queries, but until now there was no problem with IF statements, they ran. I have no idea what would be the problem in this exact situation.

Check other errors like syslog or apache log or anything. There just must be some log anywhere, i never had a problem that nothing was done and no log.

There is nothing about it in PHP error log, nothing in MySQL error log, also nothing in Apache error log, and the request is successful by the Apache access log (everything is logged, nothing is turned off). It doesn't throw Phalcon, PDO or any other PHP exception, I've checked each. If you have any better idea, just let me know, I would be really happy to know what's going on.

By the way, just today I ran into a similar strange thing at another place with another query. It's also an AJAX call with a simple SELECT, it executes in default, but fails if I add some string search into it. The query has no error in syntax, executable in phpmyadmin. I use the same execute() function there, and when the error happens, it just fails and throws nothing just like the case above there. The script also stops in this case and nothing can be outputed after it, the AJAX receives only an empty result nothing more.

What the hell could cause the execution to stop like this?

Unfortunately I can't try it with Phalcon 3 on this server, it has still PHP 5.4 there.

Then create issue on github with whole script to reproduce(like copy paste) and database. Can't help otherwise.

Meanwhile I noticed something in the second case I mentioned in my previous post: when I output the SQL from the query object with getSql() , I see that the model names are replaced with the table names everywhere normally except in the bracets in WHERE, it leaves it out there even if I've put it there. It doesn't cause problem when there is no other requested field with the same name, but it does when there are. At that case that might be the problem.

Here is what I write as a query:

SELECT 
\Main\Models\MenuBanners.id AS id, 
\Main\Models\MenuBanners.img AS img, 
\Main\Models\MenuBanners.url AS url, 
t0.name AS category 
FROM \Main\Models\MenuBanners 
LEFT JOIN \Main\Models\BannerCategory AS t0 ON \Main\Models\MenuBanners.category_id=t0.id 
WHERE (\Main\Models\MenuBanners.id LIKE :bind_0: OR \Main\Models\MenuBanners.url LIKE :bind_1: ) AND \Main\Models\MenuBanners.deleted = :bind_deleted: 
ORDER BY \Main\Models\MenuBanners.id DESC 
LIMIT 0, 10

And this is what I get when I request the SQL from the query object:

SELECT 
`menu_banners`.`id` AS `id`, 
`menu_banners`.`img` AS `img`, 
`menu_banners`.`url` AS `url`, 
`t0`.`name` AS `category` 
FROM `menu_banners` 
LEFT JOIN `banner_category` AS `t0` ON `menu_banners`.`category_id` = `t0`.`id` 
WHERE (`id` LIKE :bind_0 OR `url` LIKE :bind_1 ) AND `menu_banners`.`deleted` = :bind_deleted 
ORDER BY `id` DESC 
LIMIT 10 OFFSET 0

I get the Column "id" in where clause is ambiguous error if try to execute the SQL in phpmyadmin.

Why does it leaves out the table name there?

Notice: There are aliases for the fields at the beginning (where actually no need), but this SQL is generated automatically in many places with many different cases where there have to be diffrent names.

I have no idea why, just use lias here:

FROM \Main\Models\MenuBanners t1

Also why you are not using query builder ?

That part was from an older project where Query Builder wasn't yet used, but here it is, I rewrote it now:

    $q = $manager->createBuilder();

    $q->columns($column_list);
    $q->from(array('t' => $modelname));

    if($joins != null){
        foreach($joins as $i=>$join){
            $q->leftJoin($join['model'], $join_on[$i], "t$i");
        }
    }

    $q->where($where);

    if($groupby != null){
        $q->groupBy("t.$groupby");
    }

    $q->orderBy($order);
    $q->limit($limit['limit'],$limit['offset']);

    $data = $q->getQuery()->execute($binding_params);

It works fine when there is no search in WHERE. When there is, it looks like this when I output the PHQL:

SELECT 
t.id AS id,
t.username AS username,
t.email AS email,
t.rights AS rights,
t.account AS account,
t.reg_timestamp AS reg_timestamp,
t.last_login AS last_login,
t0.name_en AS user_account_type,
t1.name_en AS user_referer,
t2.site_title AS site_title 
FROM [\Main\Models\Users] AS [t] 
LEFT JOIN [\Main\Models\UserType] AS [t0] ON t.type_id=t0.id 
LEFT JOIN [\Main\Models\Referer] AS [t1] ON t.referer_id=t1.id 
LEFT JOIN [\Main\Models\SiteConfig] AS [t2] ON t.site_id=t2.id 
WHERE (t.id LIKE :bind_0: OR t.username LIKE :bind_1: OR t.email LIKE :bind_2: OR t2.site_title LIKE :bind_3: OR t.rights LIKE :bind_4: OR t.account LIKE :bind_5: OR t0.name_en LIKE :bind_6: OR t.reg_timestamp LIKE :bind_7: OR t.last_login LIKE :bind_8: OR t1.name_en LIKE :bind_9:) 
ORDER BY t.id DESC 
LIMIT :APL0: OFFSET :APL1:
//10, 0

// Binds
array(10) { 
    ["bind_0"]=> string(6) "%test%" 
    ["bind_1"]=> string(6) "%test%" 
    ["bind_2"]=> string(6) "%test%" 
    ["bind_3"]=> string(6) "%test%" 
    ["bind_4"]=> string(6) "%test%" 
    ["bind_5"]=> string(6) "%test%" 
    ["bind_6"]=> string(6) "%test%" 
    ["bind_7"]=> string(6) "%test%" 
    ["bind_8"]=> string(6) "%test%" 
    ["bind_9"]=> string(6) "%test%" 
}

It has no error until getQuery(), it fails there and comes that no-error-or-log-showing case that drives me crazy. Of course, I've tried to put into a try/catch, but there was nothing outputed or logged again. The query should have run, it gives the desired results when I execute it out of PHP.

Actually it fails again in case of search like the other query I posted before.

Is there a way to get the SQL from this PHQL to see what might go wrong here?

As i wrote create issue on github with WHOLE script to reproduce issue(copy paste run) and table dump.

Yes there is, just add event listener to db adapter to beforeQuery.

When I investigate the produced query with beforeQuery event, I have found what causes the problem.

SELECT 
`t`.`id` AS `id`, 
`t`.`username` AS `username`, 
`t`.`email` AS `email`, 
`t`.`rights` AS `rights`, 
`t`.`account` AS `account`, 
`t`.`reg_timestamp` AS `reg_timestamp`, 
`t`.`last_login` AS `last_login`, 
`t0`.`name_en` AS `user_account_type`, 
`t1`.`name_en` AS `user_referer`, 
`t2`.`site_title` AS `site_title` 
FROM `users` AS `t`  
LEFT JOIN `user_type` AS `t0` ON `t`.`type_id` = `t0`.`id` 
LEFT JOIN `referer` AS `t1` ON `t`.`referer_id` = `t1`.`id` 
LEFT JOIN `site_config` AS `t2` ON `t`.`site_id` = `t2`.`id` 
WHERE (`id` LIKE :bind_0 OR `username` LIKE :bind_1 OR `email` LIKE :bind_2 OR `site_title` LIKE :bind_3 OR `rights` LIKE :bind_4 OR `account` LIKE :bind_5 OR `t0`.`name_en` LIKE :bind_6 OR `reg_timestamp` LIKE :bind_7 OR `last_login` LIKE :bind_8 OR `t1`.`name_en` LIKE :bind_9) 
ORDER BY `id` DESC 
LIMIT :APL0 OFFSET :APL1

array(12) {
  ["APL0"]=>
  int(10)
  ["APL1"]=>
  int(0)
  ["bind_0"]=>
  string(3) "%1%"
  ["bind_1"]=>
  string(3) "%1%"
  ["bind_2"]=>
  string(3) "%1%"
  ["bind_3"]=>
  string(3) "%1%"
  ["bind_4"]=>
  string(3) "%1%"
  ["bind_5"]=>
  string(3) "%1%"
  ["bind_6"]=>
  string(3) "%1%"
  ["bind_7"]=>
  string(3) "%1%"
  ["bind_8"]=>
  string(3) "%1%"
  ["bind_9"]=>
  string(3) "%1%"
}

Look at the WHERE conditions: there are the table names before the fields of joined tables, but there aren't before the main table even if it was there in the PHQL. That causes the Column "id" in where clause is ambiguous error which - it seems - is not caught, so produces neither error message nor log. So there might be a bug within the parser, that lefts out the main table name from WHERE.

If you wish, I can post it to issues, but first I wanted to show what might cause this problem.

In my case, it might work if I place the searching conditions to HAVING with alias field names, but I think that will slow down the query. Second option is to use raw SQL, that might work better in this case.

You just have to put: model.id in the ORDER BY

As you can see in PHQL, I've put it everywhere - or rather QueryBuilder put it -, but when it was translated to SQL during parsing, table name misses both from WHERE - where the main table alias "t" should be - and also from ORDER BY.

So this PHQL

SELECT 
t.id AS id,
t.username AS username,
t.email AS email,
t.rights AS rights,
t.account AS account,
t.reg_timestamp AS reg_timestamp,
t.last_login AS last_login,
t0.name_en AS user_account_type,
t1.name_en AS user_referer,
t2.site_title AS site_title 
FROM [\Main\Models\Users] AS [t] 
LEFT JOIN [\Main\Models\UserType] AS [t0] ON t.type_id=t0.id 
LEFT JOIN [\Main\Models\Referer] AS [t1] ON t.referer_id=t1.id 
LEFT JOIN [\Main\Models\SiteConfig] AS [t2] ON t.site_id=t2.id 
WHERE (t.id LIKE :bind_0: OR t.username LIKE :bind_1: OR t.email LIKE :bind_2: OR t2.site_title LIKE :bind_3: OR t.rights LIKE :bind_4: OR t.account LIKE :bind_5: OR t0.name_en LIKE :bind_6: OR t.reg_timestamp LIKE :bind_7: OR t.last_login LIKE :bind_8: OR t1.name_en LIKE :bind_9:) 
ORDER BY t.id DESC 
LIMIT :APL0: OFFSET :APL1:

was translated to this SQL

SELECT 
`t`.`id` AS `id`, 
`t`.`username` AS `username`, 
`t`.`email` AS `email`, 
`t`.`rights` AS `rights`, 
`t`.`account` AS `account`, 
`t`.`reg_timestamp` AS `reg_timestamp`, 
`t`.`last_login` AS `last_login`, 
`t0`.`name_en` AS `user_account_type`, 
`t1`.`name_en` AS `user_referer`, 
`t2`.`site_title` AS `site_title` 
FROM `users` AS `t`  
LEFT JOIN `user_type` AS `t0` ON `t`.`type_id` = `t0`.`id` 
LEFT JOIN `referer` AS `t1` ON `t`.`referer_id` = `t1`.`id` 
LEFT JOIN `site_config` AS `t2` ON `t`.`site_id` = `t2`.`id` 
WHERE (`id` LIKE :bind_0 OR `username` LIKE :bind_1 OR `email` LIKE :bind_2 OR `site_title` LIKE :bind_3 OR `rights` LIKE :bind_4 OR `account` LIKE :bind_5 OR `t0`.`name_en` LIKE :bind_6 OR `reg_timestamp` LIKE :bind_7 OR `last_login` LIKE :bind_8 OR `t1`.`name_en` LIKE :bind_9) 
ORDER BY `id` DESC 
LIMIT :APL0 OFFSET :APL1

what is not correct.