Error when accessing privileges with phpmyadmin: unknown column references_priv

[pirate]

Please answer these questions before submitting your issue. Thanks!

Steps to reproduce:

1. docker run --name tidb-server -d -p 4000:4000 pingcap/tidb:latest
2. Connect using phpmyadmin root@127.0.0.1:4000
3. Go database "test", click on "Privileges" tab
4. Fails, shows error:

   unknown column references_priv

What did you expect to see? The privileges tab.

What version of TiDB are you using (tidb-server -V)?

Git Commit Hash: 31579bb3f0135e9be2c4756ef977c330957b4881
UTC Build Time:  2017-05-22 08:54:01

[shenli]

@pirate Thanks! We will fix this.

[pirate]

A couple more hints for reproducing this, I'm sure you already know most of this, but just in case here's my setup:

• make sure you have php fpm and nginx set up, if you're on mac brew install php71 nginx; brew services start php; brew services start nginx, and put this in /usr/local/etc/nginx.conf

  location ~ \.php$ {
  try_files      $uri = 404;
  fastcgi_pass   127.0.0.1:9000;
  fastcgi_index  index.php;
  fastcgi_param  SCRIPT_FILENAME $document_root$fastcgi_script_name;
  include        fastcgi_params;
  }

• Download this version of phpmyadmin: https://files.phpmyadmin.net/phpMyAdmin/4.7.0/phpMyAdmin-4.7.0-all-languages.zip and unzip to your nginx root
• set up a second localhost address so that phpmyadmin doesn't try to use a unix socket (which doesn't work with docker)

  sudo ifconfig lo0 alias 127.0.0.2/32

• copy config.sample.inc.php to config.inc.php and set:

  $cfg['Servers'][$i]['host'] = '127.0.0.2';
  $cfg['Servers'][$i]['port'] = '4000';
  $cfg['Servers'][$i]['AllowNoPassword'] = true;

[shenli]

@pirate This is fix in this PR. Please try the latest master.

[pirate]

Wow, thanks @shenli that was fast. I just pulled master and tested it, getting a different error when I click on the privileges tab now:

#1105 - unknown column create_tmp_table_priv

Failing query details:

( SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `References_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`, '*' AS `Db`, 'g' AS `Type` FROM `mysql`.`user` WHERE NOT (`Select_priv` = 'N' AND `Insert_priv` = 'N' AND `Update_priv` = 'N' AND `Delete_priv` = 'N' AND `Create_priv` = 'N' AND `Drop_priv` = 'N' AND `Grant_priv` = 'N' AND `References_priv` = 'N' AND `Create_tmp_table_priv` = 'N' AND `Lock_tables_priv` = 'N' AND `Create_view_priv` = 'N' AND `Show_view_priv` = 'N' AND `Create_routine_priv` = 'N' AND `Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND `Event_priv` = 'N' AND `Trigger_priv` = 'N')) UNION ( SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `References_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`, `Db`, 'd' AS `Type` FROM `mysql`.`db` WHERE 'test' LIKE `Db`     AND NOT (`Select_priv` = 'N' AND `Insert_priv` = 'N' AND `Update_priv` = 'N' AND `Delete_priv` = 'N' AND `Create_priv` = 'N' AND `Drop_priv` = 'N' AND `Grant_priv` = 'N' AND `References_priv` = 'N' AND `Create_tmp_table_priv` = 'N' AND `Lock_tables_priv` = 'N' AND `Create_view_priv` = 'N' AND `Show_view_priv` = 'N' AND `Create_routine_priv` = 'N' AND `Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND `Event_priv` = 'N' AND `Trigger_priv` = 'N')) ORDER BY `User` ASC, `Host` ASC, `Db` ASC;

Want me to open a new issue? I also understand if phpmyadmin support is not critical, feel free to focus your efforts elsewhere. I'm mainly using it to set up a new django db and user with proper permissions so I can test django on TIDB.

[shenli]

@pirate Thanks! We will work on this. @tiancaiamao PTAL

[tiancaiamao]

Wow, there are many columns missing...

Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Event_priv

[pirate]

Since https://github.com/pingcap/tidb/pull/3445 got merged, want me to pull master and try running this again?

[tiancaiamao]

Well, @pirate , I found that I forget to add those column to to mysql.db, so it still fail now. I'll fix it soon.

[morgo]

This issue appears to be fixed. Using a recent version of TiDB:

mysql> ( SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `References_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`, '*' AS `Db`, 'g' AS `Type` FROM `mysql`.`user` WHERE NOT (`Select_priv` = 'N' AND `Insert_priv` = 'N' AND `Update_priv` = 'N' AND `Delete_priv` = 'N' AND `Create_priv` = 'N' AND `Drop_priv` = 'N' AND `Grant_priv` = 'N' AND `References_priv` = 'N' AND `Create_tmp_table_priv` = 'N' AND `Lock_tables_priv` = 'N' AND `Create_view_priv` = 'N' AND `Show_view_priv` = 'N' AND `Create_routine_priv` = 'N' AND `Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND `Event_priv` = 'N' AND `Trigger_priv` = 'N')) UNION ( SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `References_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`, `Db`, 'd' AS `Type` FROM `mysql`.`db` WHERE 'test' LIKE `Db`     AND NOT (`Select_priv` = 'N' AND `Insert_priv` = 'N' AND `Update_priv` = 'N' AND `Delete_priv` = 'N' AND `Create_priv` = 'N' AND `Drop_priv` = 'N' AND `Grant_priv` = 'N' AND `References_priv` = 'N' AND `Create_tmp_table_priv` = 'N' AND `Lock_tables_priv` = 'N' AND `Create_view_priv` = 'N' AND `Show_view_priv` = 'N' AND `Create_routine_priv` = 'N' AND `Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND `Event_priv` = 'N' AND `Trigger_priv` = 'N')) ORDER BY `User` ASC, `Host` ASC, `Db` ASC;
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------+-----------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+------+------+
| User | Host | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | Index_priv | Alter_priv | References_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | Db   | Type |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------+-----------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+------+------+
| root | %    | Y           | Y           | Y           | Y           | Y           | Y         | Y          | Y          | Y          | Y               | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            | *    | g    |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------+-----------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+------+------+
1 row in set (0.01 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-rc.3-219-g1e0876fe8-dirty
Git Commit Hash: 1e0876fe810a832721aac52275dd2b7792fd2892
Git Branch: flush
UTC Build Time: 2018-11-24 01:12:47
GoVersion: go version go1.11 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

I am going to close this issue now, but please feel free to re-open if you have any questions. Thanks!