Heidisql: Can't connect to ProxySQL Admin interface

[bbluemel]

Steps to reproduce this issue

1. Step 1; Set up a proxysql instance.
2. Step 2; Attempt to connect to the admin interface using HeidiSQL
3. Get SQL Error (1045): no such function: CONNECTION_ID

Current behavior

Can't connect as the SELECT CONNECTION_ID() statement fails

Expected behavior

Able to connect and manage ProxySQL from Heidi.

Possible solution

I guess the connection should check for which server it is connected to before issuing CONNECTION_ID() or not getting a connection id shouldn't be a fatal issue?

Environment

• HeidiSQL version: 9.5.0.5293
  
• Database system and version: ProxySQL 1.4.8-1.1
  

[ansgarbecker]

What exactly is ProxySQL? Never heard of that. Is it a drop-in replacement for MySQL/MariaDB, or at least compatible to MySQL/MariaDB?

[bbluemel]

It's basically a high availability proxy for mysql, and allows you to route traffic to slaves or the master depending on queries etc.

To configure it, you can use a mysql client and connect on port 6032. where there are databases with tables in etc.

[ansgarbecker]

And there are other clients out there who can connect to that proxy?
It is very unlcear to me what Heidi has to do for a connection.

[chrisblech]

ProxySQL recommends in its documentation to use the default CLI mysql-client:

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

IMHO this issue could (/should?) be fixed, by teaching ProxySQL to understand Heidis SELECT CONNECTION_ID(); - see this issue

[ansgarbecker]

Ah, that makes sense now. I'll close this issue and mark it as "upstream".

[ansgarbecker]

Ok, so I'll close this issue and mark it as upstream.

[mjbnz]

I suspect getting HeidiSQL to talk to ProxySQL will take more than just dealing with CONNECTION_ID() - it's a very very cut down mysql interface, so probably needs some intelligence from HeidiSQL to treat it with some degree of special casing.

[ansgarbecker]

We'll see if I find some fixes here

[DuLerWeil]

some switch to skip SELECT CONNECTION_ID() will be nice

[ansgarbecker]

I can implement some compatibility logic if the server is detectable as proxysql. Is that somehow possible? Or, in other words: what does proxysql return on one of these queries:

SELECT VERSION();
SHOW VARIABLES LIKE 'version';

[mjbnz]

Can you use the server version and comment returned here by the mysql client? I confess, I'm not sure how that's generated, but I'm wondering if that comes from the last query in this transcript.

$ mysql -h 127.0.0.1 -P 6032 -u admin -padmin
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4094
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SELECT VERSION();
+---------------------+
| version()           |
+---------------------+
| 2.0.12-38-g58a909a0 |
+---------------------+
1 row in set (0.000 sec)

MySQL [(none)]> SHOW VARIABLES LIKE '%version%';
+----------------------+---------------------+
| Variable_name        | Value               |
+----------------------+---------------------+
| admin-version        | 2.0.12-38-g58a909a0 |
| mysql-server_version | 5.5.30              |
+----------------------+---------------------+
2 rows in set (0.001 sec)

MySQL [(none)]> select @@version_comment limit 1;
+---------------------------+
| '(ProxySQL Admin Module)' |
+---------------------------+
| (ProxySQL Admin Module)   |
+---------------------------+
1 row in set (0.000 sec)

MySQL [(none)]>

(Edit: to clarify, I was referring to the last line of this:

$ mysql -h 127.0.0.1 -P 6032 -u admin -padmin
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4094
Server version: 5.5.30 (ProxySQL Admin Module)

)

[mjbnz]

The content of This function tells you roughly what SQL the admin interface will respond to... there's some entries in there for mysqldump. The @@version_comment query result comes from here.

the version query you asked for doesn't get answered, but this version does:

MySQL [(none)]> SHOW GLOBAL VARIABLES LIKE 'version';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| version       | 2.0.12-38-g58a909a0 |
+---------------+---------------------+
1 row in set (0.001 sec)

MySQL [(none)]>

which comes from here

[ansgarbecker]

Next build should be aware of ProxySQL, and use a hardcoded -1 as a replacement for the SELECT CONNECTION_ID().
But I suppose that was not the only incompatible thing to fix.

[mjbnz]

It might be better to check for proxysql admin module, not just proxysql... connecting to 6033 for access to the actual database servers returns:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4691
Server version: 5.5.30 (ProxySQL)

[ansgarbecker]

Yes, definitely. Just done that. Next build will compile in 10 minutes or so.

[bbluemel]

Hi,
Thanks for looking into this, just tried the latest build and now get:

/* Connecting to 127.0.0.1 via MySQL (SSH tunnel), username proxysqlroot, using password: Yes ... */
/* Port #33070 in use. Checking if #33071 is available... */
/* Attempt to create plink.exe process, waiting 4s for response ... */
/* C:\apps\PuTTYSuitePortable\App\Putty\PLINK.EXE **redacted** */
SELECT 1;
/* Characterset: utf8mb4 */
SHOW STATUS;
/* SQL Error (1045): ProxySQL Admin Error: near "SHOW": syntax error */
/* Closing plink.exe process #11164 ... */
/* Connection to 127.0.0.1 closed at 2020-06-17 14:48:52 */

From connecting to another server it seems like SELECT NOW(); will also fail, but SHOW VARIABLES; AND SHOW DATABASES; will work ok.

[ansgarbecker]

Well, mühsam ernährt sich das Eichhörnchen, as we say in Germany.

Is there an alternative to SELECT NOW() then?

I am curious also, why the ProxySQL admin interface imitates the MySQL protocol but can't handle such simple queries.

[bbluemel]

SELECT CURRENT_TIMESTAMP; works as an alternative to SELECT NOW();

The admin interface is mainly used for managing servers, groups, rules etc, so NOW() and probably many functions outside aggregate ones likely aren't implemented.

[ansgarbecker]

Ok, next build does not block when SHOW STATUS does not work, and uses SELECT CURRENT_TIMESTAMP instead of SELECT NOW(). Please update in a few minutes.

[bbluemel]

For some reason, I'm still getting the request for CONNECTION_ID():

SELECT 1;
/* Characterset: utf8mb4 */
SHOW STATUS;
/* SQL Error (1045): ProxySQL Admin Error: near "SHOW": syntax error */
/* SQL Error (1045): ProxySQL Admin Error: near "SHOW": syntax error */
SELECT CURRENT_TIMESTAMP;
SHOW VARIABLES;
SELECT CONNECTION_ID();
/* SQL Error (1045): ProxySQL Admin Error: no such function: CONNECTION_ID */
/* Closing plink.exe process #33544 ... */
/* Connection to 127.0.0.1 closed at 2020-06-17 16:00:17 */

not sure if this is relevant:

Admin> select @@version_comment limit 1;
+---------------------------+
| '(ProxySQL Admin Module)' |
+---------------------------+
| (ProxySQL Admin Module)   |
+---------------------------+
1 row in set (0.00 sec)

Admin> select @@version_comment;
+--------------------+
| @@version          |
+--------------------+
| 2.0.12-38-g58a909a |
+--------------------+
1 row in set (0.00 sec)

Admin>

[ansgarbecker]

In that case I suppose that "version_comment" is not part of the result from SHOW VARIABLES?

[mjbnz]

In that case I suppose that "version_comment" is not part of the result from SHOW VARIABLES?

No - it's a very specific result, generated from this bit of code that I linked earlier in this issue

The actual SQL in the SELECT_VERSION_COMMENT constant is defined here

Edit: A suggestion for working out if this is proxysql from the variables, would be to look at the admin-mysql_ifaces variable - if that contains the port (:<port>) connected to, then it's likely proxysql, and could be confirmed with a query for the version comment.

MySQL [(none)]> show variables like 'admin-mysql_ifaces';
+--------------------+--------------+
| Variable_name      | Value        |
+--------------------+--------------+
| admin-mysql_ifaces | 0.0.0.0:6032 |
+--------------------+--------------+
1 row in set (0.001 sec)

MySQL [(none)]> select @@version_comment limit 1;
+---------------------------+
| '(ProxySQL Admin Module)' |
+---------------------------+
| (ProxySQL Admin Module)   |
+---------------------------+
1 row in set (0.000 sec)

[mjbnz]

I am curious also, why the ProxySQL admin interface imitates the MySQL protocol but can't handle such simple queries.

Yes - it's definitely an interesting choice... However, I'm not hating it.

[mjbnz]

Admin> select @@version_comment;
+--------------------+
| @@version          |
+--------------------+
| 2.0.12-38-g58a909a |
+--------------------+
1 row in set (0.00 sec)

Admin>

Just for completeness - this isn't actually returning @@version_comment, it's only returning @@version (as hinted by the column title) - the code in ProxySQL that matched this is only looking at the start of the string, and ignores anything after the match

[mjbnz]

(sorry for the spamming of this issue)

After connection is established, the next problem is going to be the selection of databases. The existing MySQL queries posted won't work (there's no information_schema database, and no TABLE STATUS query to use).

I have a feeling that this is going to end up being too complicated to add special handing to HeidiSQL without needing to move having a special database type to select in the connection entry. This might be a level of work that you're uncomfortable with @ansgarbecker ?

Either way, here's an example of the output that would be available to you:

MySQL [(none)]> SHOW DATABASES;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.001 sec)

MySQL [(none)]> SHOW TABLES FROM `main`;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| global_variables                                   |
| mysql_aws_aurora_hostgroups                        |
| mysql_collations                                   |
| mysql_firewall_whitelist_rules                     |
| mysql_firewall_whitelist_sqli_fingerprints         |
| mysql_firewall_whitelist_users                     |
| mysql_galera_hostgroups                            |
| mysql_group_replication_hostgroups                 |
| mysql_query_rules                                  |
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
| proxysql_servers                                   |
| restapi_routes                                     |
| runtime_checksums_values                           |
| runtime_global_variables                           |
| runtime_mysql_aws_aurora_hostgroups                |
| runtime_mysql_firewall_whitelist_rules             |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users             |
| runtime_mysql_galera_hostgroups                    |
| runtime_mysql_group_replication_hostgroups         |
| runtime_mysql_query_rules                          |
| runtime_mysql_query_rules_fast_routing             |
| runtime_mysql_replication_hostgroups               |
| runtime_mysql_servers                              |
| runtime_mysql_users                                |
| runtime_proxysql_servers                           |
| runtime_restapi_routes                             |
| runtime_scheduler                                  |
| scheduler                                          |
+----------------------------------------------------+
32 rows in set (0.000 sec)

MySQL [(none)]> SHOW TABLES FROM `disk`;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_aws_aurora_hostgroups                |
| mysql_collations                           |
| mysql_firewall_whitelist_rules             |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users             |
| mysql_galera_hostgroups                    |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| restapi_routes                             |
| scheduler                                  |
+--------------------------------------------+
16 rows in set (0.001 sec)

MySQL [(none)]> SHOW CREATE TABLE main.mysql_users;
+-------------+------------------------------------------------------+
| table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------------+------------------------------------------------------+
| mysql_users | CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend)) |
+-------------+------------------------------------------------------+
1 row in set (0.000 sec)

... thought - if it helps, the files backing the databases are sqlite.

edit: further comment - USE isn't useful. It's accepted, but doesn't actually change the "database" you're interacting with... This is fine, as HeidiSQL already uses the SHOW CREATE TABLE <db>.<table> form of the query, but instead of using information_schema, SHOW TABLES FROM <db> would need to be used.

[ansgarbecker]

I'm beginning to think HeidiSQL would anyway not be useful for ProxySQL servers, as the only thing you might be able to do with it is firing user queries.

[mjbnz]

I'm beginning to think HeidiSQL would anyway not be useful for ProxySQL servers, as the only thing you might be able to do with it is firing user queries.

I disagree - HeidiSQL would be very useful when connecting to the admin interface to administer the proxy. Being able to use the Data tab to edit configuration table rows directly, without needing to manually write queries would itself alone be amazing.

However, I do concede that it more than likely will be a a fair amount of work for a very small subset of users to add support for the very limited SQL the admin interface supports.

[ansgarbecker]

Yes I fear this might lead to many quirks in the HeidiSQL code, which may even cause new issues on normal MySQL connections.

The current way to change things for Heidi looks too vague to me, any may result in new issues.

• Is there some documentation which SQL functions the admin module supports? I don't want to look that all up in the cpp code files.
• And/or is there a ProxySQL Admin server which I may access from home here?

[ansgarbecker]

Now that @daniel-kadosh-gartner was so nice to create a publicly available Proxy Admin server for testing purposes, I finally could run tests, and see some more issues here:

• select @@version_comment limit 1; returns "(ProxySQL Admin Module)" while select @@version_comment returns "2.0.10-27-g5b319972"
• Expanding a database leaf results in "_no such table: information_schema.SCHEMATA_"
• SHOW TABLE STATUS and SHOW TABLE STATUS FROM x return "_ProxySQL Admin Error: near "SHOW": syntax error_"
• SHOW VARIABLES; and SHOW GLOBAL VARIABLES; work
• SHOW /*!50002 GLOBAL */ STATUS; returns "_ProxySQL Admin Error: near "SHOW": syntax error_"
• SHOW FULL PROCESSLIST; returns an empty result
• FLUSH PRIVILEGES; returns "_ProxySQL Admin Error: near "FLUSH": syntax error_"
• HELP contents; returns "_ProxySQL Admin Error: near "HELP": syntax error_"
• and so on

It really does not make sense to me to go further here - HeidiSQL relies on all of these queries in MySQL/MariaDB mode. I think managing that admin interface is a task for the command line only.

[daniel-kadosh-gartner]

:-( :-( A lot of work, I understand. Thanks for looking into this.

We use command-line for all our scripted stuff, and this is an "admin" interface to ProxySQL where a nice UI like Heidi is exactly how I'd like to interact with it, not command-line.
Will have to find another Windows MySQL client that might work.

[mjbnz]

Ahh, my apologies... I had meant to set up an instance for you @ansgarbecker , but this managed to slip out of my firefighting list! Thank you to @daniel-kadosh-gartner for stepping up.

It really does not make sense to me to go further here - HeidiSQL relies on all of these queries in MySQL/MariaDB mode. I think managing that admin interface is a task for the command line only.

I absolutely agree. As an alternative, do you think it would be possible with the current code base to create a new database type/mode for ProxySQL (forking the mariadb/mysql mode, and heavily cutting it down)? or can you foresee compatibility issues where it's just not practical? I'm not suggesting that you do that, but if someone else were to take this route and provide a PR, would you accept it?

[ansgarbecker]

Well, that's an idea. The user is responsible then for chosing the right network type, and I don't have to watch out for the version string in the code, for disabling many things. I would prefer doing that myself. Perhaps I should set up ProxySQL on my virtual Ubuntu here.

[ansgarbecker]

I am on the way to add a new network type in HeidiSQL for ProxySQL Admin sessions. I only can't find a way to add another user, as the admin:admin user is restricted to local access. Adding one in /etc/proxysql.cnf does not help yet. Can someone help me here?

Edit: I get "Access denied for user 'anse'@'xyz'" here, even after restarting ProxySQL several times with that above cnf file.

[chrisblech]

ProxySQL has a quite uncommon behaviour dealing with configuration, see docs, section "Conf lifecycle":

If a database file is found, the /etc/proxysql.cnf config file is not parsed

Information about adding users (and how to apply changes to users table to live) is here: https://proxysql.com/documentation/Users-configuration/

[ansgarbecker]

• I managed to create a persistent user "anse" via locally connected mysql client
• saved it to disk via SAVE MYSQL USERS TO DISK;
• restarted the service via sudo systemctl restart proxysql-initial (plus a second time via sudo systemctl restart proxysql)

Still I get an "access denied" for that user when connecting from my host machine...

[bbluemel]

ah no that's the wrong way for admin.. those are mysql users for the mysql interface, as it needs to verify them.

So for what you want you need to do

UPDATE global_variables SET variable_value = 'admin:admin;anse:anse' WHERE variable_name = 'admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;

[ansgarbecker]

Thanks, that was it!

[ansgarbecker]

This is what the next build will show up now:

• SELECT CONNECTION_ID() in PSA (I'll abbreviate that "ProxySQL Admin") mode replaced by mysql_thread_id() C-API method call
• replaced SELECT NOW() with SELECT CURRENT_TIMESTAMP
• replaced SHOW STATUS with SELECT * FROM stats_mysql_global
• replaced SHOW /*!50002 GLOBAL */ STATUS LIKE 'Com\_%' with SELECT * FROM stats_mysql_commands_counters
• replaced SHOW TABLE STATUS FROM xyz with SHOW TABLES FROM xyz, plus ignore all non existent column names in the result
• looking for uptime status info extended with ProxySQL_Uptime (for "Uptime:" in the status bar)
• using column 1 instead of 0 from SHOW DATABASES
• added that "Stargate" icon

Just the new network type for PSA sits a bit separated from the other MySQL types:

[ansgarbecker]

I'm not sure, is it doable to get the columns and indexes of tables? SHOW [FULL] COLUMNS FROM mytable fails.

[daniel-kadosh-gartner]

@ansgarbecker Maybe use show create table

mysql> show create table mysql_users \G
*************************** 1. row ***************************
       table: mysql_users
Create Table: CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))
1 row in set (0.00 sec)

[ansgarbecker]

Ugh, that would again mean I have to parse that stuff. I just got rid of that a few versions ago. I suppose there is no way using information_schema.columns and friends?

[daniel-kadosh-gartner]

They have a very crude DB server setup, without anything resembling information_schema. It's using SQLite under-the-hood, plus support for a small subset of MySQL commands.
https://www.percona.com/blog/2017/06/07/proxysql-admin-interface-not-typical-mysql-server/

Even though you can SHOW DATABASES and USE <db-name>, there is only schema and these commands were added for showing some information and slight compatibility to some MySQL tools.

1) In ProxySQL we cannot create, alter or drop any of these tables anyways, so a "full UI" for table management is not needed.
2) It is using SQLite, and there's (experimental) support for that in Heidi already that maybe you can leverage.

Suggesting that when I double-click on a table name on the left, under the Table: <name> main/upper tab, the only minor tab you show is </> CREATE code with the output from SHOW CREATE TABLE. You can completely skip the other tabs: basic, options, indexes, FKs, partitions, alter code. If it's hard to not show those tabs, perhaps just show nothing or "blank data" in each of them. Even a message "ProxySQL does not support table management" in these tabs would make sense.

For the Data main/upper tab I suppose you do need to have the column names, but hopefully you get that already differently without needing anything like information_schema.columns.

[mjbnz]

Yeah, I'd suggest the same as @daniel-kadosh-gartner - a very very cut down interface in HeidiSQL would be sensible.

The sum total of features I'd use are the table data view, and an sql query window.

Given that it's a separate database type, it might even be nice to have some extra toolbar buttons to run the config save/commit SQL:

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
... etc etc...

But, that's far beyond the scope of just being able to connect and edit things I suspect.

[daniel-kadosh-gartner]

After re-reading Peter Zeitsev's Percona article and noting the "hidden yet always available tables" quirk, I have a few more thoughts.
A few starting points:
A) You can run SQLs against any table without using USE <dbname> at all, and using just the name, e.g., select * from system_cpu
B) Referencing a table as <dbname>.<tablename> works, e.g., select * from disk.mysql_servers;
* <tablename> MUST be in the output of SHOW TABLES FROM <dbname>, else you get an error like "no such table"
C) SHOW TABLES does not list all the possible tables, but using SHOW TABLES FROM <dbname> in a loop around the dbnames from SHOW DATABASES will get the full list of tables.

So two approaches to consider, and both work for me -- whatever's easier.
For the left-side panel of HeidiSQL:
1) Ignore "databases" completely, but have a complete list of all distinct table names obtained from a loop as described in (C).
* As long as the tables are in alphabetical order, this is perfect to sort through them. not an option, see later comments.
2) Leverage the "databases" concept, and each one have the tables output from its own SHOW TABLES FROM <dbname>
* Quirk to note: would have some "duplicate" tables across some of these databases where they actually represent the same exact thing, e.g., main.mysql_servers === disk.mysql_servers (misunderstanding of ProxySQL's quirks on my part)

[mjbnz]

No, main and disk are not equivalent. disk is what's stored on disk for startup configuration, and main is what's currently in memory, but not committed to disk, or loaded into runtime:

disk.mysql_servers <-> main.mysql_servers <-> main.runtime_mysql_servers

[mjbnz]

Ugh, that would again mean I have to parse that stuff. I just got rid of that a few versions ago. I suppose there is no way using information_schema.columns and friends?

try:
PRAGMA table_info(mysql_servers)

I was reading through the admin_session_handler() function in ProxySQL_Admin.cpp from ProxySQL itself, looking at the methods of viewing table data. A couple of options for column info looked plausible, one was show fields from from here (seems to be a case of inconsistency in expected case... it needs lower case, where all other commands expect upper. I'll file a bug on that I think). However, the result has no column names, just indexes (which I think is a bug - I'll file one for that too). However, reading of the function that is called, led me to SQLite's PRAGMA table_info(<table>), which appears to work nicely:

This is because run_query is set to true at the start of the function, and if there's no code to do something special with a given query, it passes it to the SQLite backend, here.

[ansgarbecker]

Does it make sense to file a bug report in the proxysql tracker for each non-working query type? I guess they don't aim to support all of them, or?

[ansgarbecker]

The SQLite hint was helpful. I had to copy some code (ugh...) from my SQLite implementation, but also had to remove the database name from the columns and keys queries. Now the table and data tab should be working, basically. Not yet tested each and every button on them, so feel free to help out. Next HeidiSQL build will be ready in half an hour.

[mjbnz]

Does it make sense to file a bug report in the proxysql tracker for each non-working query type? I guess they don't aim to support all of them, or?

No, it doesn't - the bug reports are only for issues with queries that are currently implemented.

[daniel-kadosh-gartner]

@ansgarbecker that last build worked quite well for me -- THANK YOU!
It makes it a whole lot easier to quickly check out values of all ProxySQL's settings, and manage some of them.

Left-side panel works great. So do the Host: xxx and Database: xxx tabs.

Table: xxx tab:

• The sub-tabs are ok even if most aren't needed. Out of curiosity I tried adding an index to a table, and it failed just as I would expect (MySQL vs. SQLite syntax issue). Adding a VARCHAR column didn't work either, as SQLite didn't like the (50) field size parameter. Anyways, DDL is not needed nor expected for ProxySQL.
• The Create Code sub-tab tab doesn't show some of the nuances of SQLite columns such as max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000, which would be really nice to have. DEFAULT ... was also missing on several columns that I checked.
  While I can live without this being fixed, it's just very useful to have DEFAULTs and the CHECKs for columns easily visible for some ProxySQL management tasks and debugging.

[ansgarbecker]

Thanks for all feedback so far!

The missing DEFAULT and CHECK clauses are not even implemented for regular SQLite connections in HeidiSQL. Once that's done, I should remember to copy some lines of code into the code path for ProxySQL to get these here as well. But that's beyond the scope of this issue, so I'll close this now.

[mjbnz]

I can also add a comment that nightly build 11.0.0.6044 worked awesomely! Nice work @ansgarbecker , thank you so much - this is an excellent new ability for HeidiSQL. I've only had a quick cursory glance - I'm happy to provide more feedback once I get a chance to look at it in more detail (later today, as it happens).