New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"Choose column to display" does not work #13533

Closed
svivian opened this Issue Jul 28, 2017 · 6 comments

Comments

Projects
None yet
5 participants
@svivian

svivian commented Jul 28, 2017

Steps to reproduce

  1. Go to a table view.
  2. Go to the Relations tab.
  3. Next to "Choose columns to display", pick another column.

Expected behaviour

The column to display should change.

Actual behaviour

Firstly, the dropdown started with a field selected that I don't remember selecting in the first place, and didn't want. So I tried to change it to something useful. Clicking save appears to do something (it pops up the "Loading" banner) but it doesn't change the field. If I click "Preview SQL" it always says "No change".

If I'm not mistaken the preferences should be stored in the table phpmyadmin.pma__table_info which I have set up correctly (other functions like query history are working fine). That table did have some config for other databases in there that I added a while back, but nothing was getting added to it. Tried emptying the table but still no joy.

Server configuration

Operating system: Mac

Web server: Apache

Database: Mysql 5.6.13

PHP version: 5.5.38

phpMyAdmin version: 4.7.3

Client configuration

Browser: Chrome

Operating system: Mac

@ibennetch ibennetch added this to the 4.7.4 milestone Jul 29, 2017

@ibennetch ibennetch added the bug label Jul 29, 2017

@ibennetch

This comment has been minimized.

Show comment
Hide comment
@ibennetch

ibennetch Jul 29, 2017

Member

Indeed, this also seems broken on master.

Member

ibennetch commented Jul 29, 2017

Indeed, this also seems broken on master.

@godzillante

This comment has been minimized.

Show comment
Hide comment
@godzillante

godzillante Aug 17, 2017

I can confirm it happens on 4.7.3 with other browsers on Mac as well (Firefox 54 and Safari 10). PHP version is 5.6.31, MySQL server 5.5.57, libmysqlclient 5.1.73
Also, my phpmyadmin.pma__table_info table appears to be empty while other tables (i.e. phpmyadmin.pma__favorite) are correctly updated.

godzillante commented Aug 17, 2017

I can confirm it happens on 4.7.3 with other browsers on Mac as well (Firefox 54 and Safari 10). PHP version is 5.6.31, MySQL server 5.5.57, libmysqlclient 5.1.73
Also, my phpmyadmin.pma__table_info table appears to be empty while other tables (i.e. phpmyadmin.pma__favorite) are correctly updated.

@jebsolutions

This comment has been minimized.

Show comment
Hide comment
@jebsolutions

jebsolutions Aug 23, 2017

How to best fix this is left up to your imagination. My quick hack was to do an "insert...on duplicate key" sql instead of an "update":


    public function updateDisplayField($disp, $display_field, $cfgRelation)
    {
        $upd_query = false;
        if ($disp) {
            if ($display_field == '') {
                $upd_query = 'DELETE FROM '
                    . Util::backquote($GLOBALS['cfgRelation']['db'])
                    . '.' . Util::backquote($cfgRelation['table_info'])
                    . ' WHERE db_name  = \''
                    . $GLOBALS['dbi']->escapeString($this->_db_name) . '\''
                    . ' AND table_name = \''
                    . $GLOBALS['dbi']->escapeString($this->_name) . '\'';
            } elseif ($disp != $display_field) {
                $upd_query = 'INSERT INTO '
                    . Util::backquote($GLOBALS['cfgRelation']['db'])
                    . '.' . Util::backquote($cfgRelation['table_info'])
                    . '(db_name, table_name, display_field) VALUES('
                    . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name) . '\','
                    . '\'' . $GLOBALS['dbi']->escapeString($this->_name) . '\','
                    . '\'' . $GLOBALS['dbi']->escapeString($display_field) . '\')'
                    . ' ON DUPLICATE KEY UPDATE '
                    . ' display_field = \''
                    . $GLOBALS['dbi']->escapeString($display_field) . '\'';
            }

I disclaim all right to the above code. anybody can use it for any reason.

The problem is caused by this code:

https://github.com/phpmyadmin/phpmyadmin/blame/b2ad33ca474ba2c3355d8e118703331209d10f3d/libraries/relation.lib.php#L867

If there is no "display_field" set for this table it defaults to the first character field.

And in this function
https://github.com/phpmyadmin/phpmyadmin/blob/2ff392043820ce372a539ca2e1afdde5ad9f884c/libraries/classes/Table.php

public function updateDisplayField($disp, $display_field, $cfgRelation)

It compares $disp (which can be the defaulted value) with your changed value (whatever you set). Since those two do not match...it does an UPDATE instead of an INSERT.

And an update to a non-existent key "works" but does nothing since there is no record to overwrite....so thats why there is no sql error but the display field wasn't being updated.

anyway. that's all the patience I have for this issue, someone else can do a PR.

jebsolutions commented Aug 23, 2017

How to best fix this is left up to your imagination. My quick hack was to do an "insert...on duplicate key" sql instead of an "update":


    public function updateDisplayField($disp, $display_field, $cfgRelation)
    {
        $upd_query = false;
        if ($disp) {
            if ($display_field == '') {
                $upd_query = 'DELETE FROM '
                    . Util::backquote($GLOBALS['cfgRelation']['db'])
                    . '.' . Util::backquote($cfgRelation['table_info'])
                    . ' WHERE db_name  = \''
                    . $GLOBALS['dbi']->escapeString($this->_db_name) . '\''
                    . ' AND table_name = \''
                    . $GLOBALS['dbi']->escapeString($this->_name) . '\'';
            } elseif ($disp != $display_field) {
                $upd_query = 'INSERT INTO '
                    . Util::backquote($GLOBALS['cfgRelation']['db'])
                    . '.' . Util::backquote($cfgRelation['table_info'])
                    . '(db_name, table_name, display_field) VALUES('
                    . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name) . '\','
                    . '\'' . $GLOBALS['dbi']->escapeString($this->_name) . '\','
                    . '\'' . $GLOBALS['dbi']->escapeString($display_field) . '\')'
                    . ' ON DUPLICATE KEY UPDATE '
                    . ' display_field = \''
                    . $GLOBALS['dbi']->escapeString($display_field) . '\'';
            }

I disclaim all right to the above code. anybody can use it for any reason.

The problem is caused by this code:

https://github.com/phpmyadmin/phpmyadmin/blame/b2ad33ca474ba2c3355d8e118703331209d10f3d/libraries/relation.lib.php#L867

If there is no "display_field" set for this table it defaults to the first character field.

And in this function
https://github.com/phpmyadmin/phpmyadmin/blob/2ff392043820ce372a539ca2e1afdde5ad9f884c/libraries/classes/Table.php

public function updateDisplayField($disp, $display_field, $cfgRelation)

It compares $disp (which can be the defaulted value) with your changed value (whatever you set). Since those two do not match...it does an UPDATE instead of an INSERT.

And an update to a non-existent key "works" but does nothing since there is no record to overwrite....so thats why there is no sql error but the display field wasn't being updated.

anyway. that's all the patience I have for this issue, someone else can do a PR.

@ibennetch ibennetch modified the milestones: 4.7.5, 4.7.4 Aug 24, 2017

@nijel nijel self-assigned this Sep 4, 2017

nijel added a commit that referenced this issue Sep 4, 2017

Remove no longer needed attribute
Issue #13533

Signed-off-by: Michal Čihař <michal@cihar.com>
@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Sep 4, 2017

Member

@jebsolutions Thanks for analysis, I've just fixed that in ed91209.

Member

nijel commented Sep 4, 2017

@jebsolutions Thanks for analysis, I've just fixed that in ed91209.

@nijel nijel closed this in ed91209 Sep 4, 2017

@svivian

This comment has been minimized.

Show comment
Hide comment
@svivian

svivian Oct 23, 2017

The issue with selecting the 'column to display' is fixed, but I'm wondering where does PMA get said column from when displaying the dropdown in Relation View? It seems like it should come from pma__table_info - and when I save it a row is added to that table.

But I cleared out that table and it still shows a particular column selected on that dropdown. Does that selection come from somewhere other than pma__table_info?

svivian commented Oct 23, 2017

The issue with selecting the 'column to display' is fixed, but I'm wondering where does PMA get said column from when displaying the dropdown in Relation View? It seems like it should come from pma__table_info - and when I save it a row is added to that table.

But I cleared out that table and it still shows a particular column selected on that dropdown. Does that selection come from somewhere other than pma__table_info?

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 24, 2017

Member

It might be cached in the session as well (I'm not sure with this, but AFAIK there was something like this).

Member

nijel commented Oct 24, 2017

It might be cached in the session as well (I'm not sure with this, but AFAIK there was something like this).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment