Heidisql: Empty user list and crash on add user on MariaDB 10.4

Created on 4 Apr 2019 · 9Comments · Source: HeidiSQL/HeidiSQL

Steps to reproduce this issue

Install MariaDB 10.4 on Linux (I used Ubuntu Bionic)
Set a password for MariaDB user root and allow remote connection.
Connect using HeidiSQL.
Open the User Manager. Note error 'Column "user" not available.' and empty user list.
Click add user. Observe crash.

Current behavior

Error opening the user list in the User Manager. Crash when trying to perform changes.

Expected behavior

No error and and crashes when using the User Manager.

Environment

heidi bugreport.txt

HeidiSQL version:
10.1.0.5464
Database system and version:
MariaDB 10.4.3
Operating system:
MariaDB running on Ubuntu Bionic, HeidiSQL on Windows 10.

bug nettype-mysql

Source

zrav

All 9 comments

I suppose that "Column "user" not available." error is the crux.

HeidiSQL fires these queries when opening the user manager:

SHOW COLUMNS FROM `mysql`.`user`;
SELECT `user`, `host`, IF(LENGTH(password)>0, password, authentication_string) AS `password` FROM `mysql`.`user`;

Could you please verify if your mysql.user table has a column named "User" (or "user")?
The error looks as if it does not.

ansgarbecker on 4 Apr 2019

MariaDB [(none)]> SHOW COLUMNS FROM `mysql`.`user`;
+------------------------+---------------------+------+-----+----------+-------+
| Field                  | Type                | Null | Key | Default  | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host                   | char(60)            | NO   |     |          |       |
| User                   | char(80)            | NO   |     |          |       |
| Password               | longtext            | YES  |     | NULL     |       |
| Select_priv            | varchar(1)          | YES  |     | NULL     |       |
| Insert_priv            | varchar(1)          | YES  |     | NULL     |       |
| Update_priv            | varchar(1)          | YES  |     | NULL     |       |
| Delete_priv            | varchar(1)          | YES  |     | NULL     |       |
| Create_priv            | varchar(1)          | YES  |     | NULL     |       |
| Drop_priv              | varchar(1)          | YES  |     | NULL     |       |
| Reload_priv            | varchar(1)          | YES  |     | NULL     |       |
| Shutdown_priv          | varchar(1)          | YES  |     | NULL     |       |
| Process_priv           | varchar(1)          | YES  |     | NULL     |       |
| File_priv              | varchar(1)          | YES  |     | NULL     |       |
| Grant_priv             | varchar(1)          | YES  |     | NULL     |       |
| References_priv        | varchar(1)          | YES  |     | NULL     |       |
| Index_priv             | varchar(1)          | YES  |     | NULL     |       |
| Alter_priv             | varchar(1)          | YES  |     | NULL     |       |
| Show_db_priv           | varchar(1)          | YES  |     | NULL     |       |
| Super_priv             | varchar(1)          | YES  |     | NULL     |       |
| Create_tmp_table_priv  | varchar(1)          | YES  |     | NULL     |       |
| Lock_tables_priv       | varchar(1)          | YES  |     | NULL     |       |
| Execute_priv           | varchar(1)          | YES  |     | NULL     |       |
| Repl_slave_priv        | varchar(1)          | YES  |     | NULL     |       |
| Repl_client_priv       | varchar(1)          | YES  |     | NULL     |       |
| Create_view_priv       | varchar(1)          | YES  |     | NULL     |       |
| Show_view_priv         | varchar(1)          | YES  |     | NULL     |       |
| Create_routine_priv    | varchar(1)          | YES  |     | NULL     |       |
| Alter_routine_priv     | varchar(1)          | YES  |     | NULL     |       |
| Create_user_priv       | varchar(1)          | YES  |     | NULL     |       |
| Event_priv             | varchar(1)          | YES  |     | NULL     |       |
| Trigger_priv           | varchar(1)          | YES  |     | NULL     |       |
| Create_tablespace_priv | varchar(1)          | YES  |     | NULL     |       |
| Delete_history_priv    | varchar(1)          | YES  |     | NULL     |       |
| ssl_type               | varchar(9)          | YES  |     | NULL     |       |
| ssl_cipher             | longtext            | NO   |     |          |       |
| x509_issuer            | longtext            | NO   |     |          |       |
| x509_subject           | longtext            | NO   |     |          |       |
| max_questions          | bigint(20) unsigned | NO   |     | 0        |       |
| max_updates            | bigint(20) unsigned | NO   |     | 0        |       |
| max_connections        | bigint(20) unsigned | NO   |     | 0        |       |
| max_user_connections   | bigint(21)          | NO   |     | 0        |       |
| plugin                 | longtext            | NO   |     |          |       |
| authentication_string  | longtext            | NO   |     |          |       |
| password_expired       | varchar(1)          | NO   |     |          |       |
| is_role                | varchar(1)          | YES  |     | NULL     |       |
| default_role           | longtext            | NO   |     |          |       |
| max_statement_time     | decimal(12,6)       | NO   |     | 0.000000 |       |
+------------------------+---------------------+------+-----+----------+-------+
47 rows in set (0.052 sec)
SELECT `user`, `host`, IF(LENGTH(password)>0, password, authentication_string) AS `password` FROM `mysql`.`user`;
+----------+-----------+-------------------------------------------+
| User     | Host      | password                                  |
+----------+-----------+-------------------------------------------+
| root     | %         | *DDXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| yyyyy    | localhost | *4BXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
+----------+-----------+-------------------------------------------+
2 rows in set (0.001 sec)

zrav on 4 Apr 2019

I suppose MariaDB 10.4 returns column names with their case as defined in the table rather than as the user selects it. E.g. in 10.3 SELECT user FROM mysql.user returned a column "user", where 10.4 returns "User" instead. HeidiSQL expected the same case as written in the query up to now. I changed this to case insensitive so that should work again in the next build. Please test.

ansgarbecker on 4 Apr 2019

👍1

Yes, problem solved. Thank you.

zrav on 5 Apr 2019

Also had same issue, found it fixed in the nightly build. New colour interface also.. someone likes pink.

tommylux on 19 Apr 2019

Just installed MDB v10.4.6, Heidi v9.5.0.5196 from the .msi and I get the same "column user not available" error, note that mysql.user contains a 'user' column, and that adding a user from the query tab succeeds.