need live tests for queries to 'information schema.tables' table

[klirichek]

Proposal:

Files in src/Plugin/Select implement responses to different tools speaking with mysql proto.
Since some funcs going to move to daemon's code, query tests are necessary.
That is all real kinds of 'select ... from information_schema.tables' - with correct answers, which budy generates.

It should be not just general queries, but concrete specific queries dumped from the tools in real sessions.

There are some comments in the code - like 'HeidiSQL tool doesn't recognize lowercase data types so we add this extra transformation', or 'Adding the character set columns with fake data since they're mandatory for HeidiSQL'. They explain some of details, but that is much better to have whole picture. Ideally - just written sessions from different apps with buddy's answers, what is happened 'underneath'.

For now only queries to INFORMATION_SCHEMA.TABLES is necessary

As an example you can see test/test_272/test.xml. It contains real sessions from mysqldump with different params. Here is request to make same one with the tools tested with 'information_schema.tables mentioned HeidiSQL, and also all the other which was used to create the plugin.

Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

• Implementation completed
• Tests developed
• Documentation updated
• Documentation reviewed
• Changelog updated
• OpenAPI YAML updated and issue created to rebuild clients

[sanikolaev]

Test plan:

1. Run searchd --logdebugv
2. Use the tools we support through the MySQL interface:
   • DBeaver
   • Apache Superset
   • Grafana
   • HeidiSQL
3. Check the searchd log to find all the MySQL queries these tools run, for example:

   [Tue Jun 24 09:53:11.885 2025] [29143303] DEBUG: LoopClientMySQL command 3, 'show tables'
   

   Make sure to include any queries that fail too, like:

   [Tue Jun 24 09:55:01.100 2025] [29143291] DEBUG: P03: syntax error, unexpected tablename near 'show tablesdsds'
   

4. Write down all the queries and remove any duplicates
5. Share the list here, along with which tool ran each query and whether it failed or not

[sanikolaev]

HeidiSQL

Decided to focus on DBeaver first because:

• it seems to be a lot more popular than HeidiSQL
• it looks easier to add plugins to (if we decide to go that route)

[PavelShilin89]

DBeaver
Successful queries:

SELECT * FROM information_schema.tables
-- ✅ Works, but returns only 1 table out of 3, empty TABLE_SCHEMA and TABLE_TYPE

SELECT COUNT(*) FROM information_schema.tables  
-- ✅ Works

SHOW FULL TABLES (without FROM)
-- ✅ Works (shows tables from current schema)

Failed queries:

SHOW ENGINES
-- ❌ P01: syntax error, unexpected identifier, expecting VARIABLES near 'ENGINES'

SHOW CHARSET
-- ❌ P01: syntax error, unexpected identifier, expecting VARIABLES near 'CHARSET'

SHOW COLLATION  
-- ❌ P01: syntax error, unexpected SHOW near 'SHOW COLLATION'

SHOW PLUGINS
-- ❌ P01: syntax error, unexpected identifier, expecting VARIABLES near 'PLUGINS'

SELECT @@GLOBAL.character_set_server,@@GLOBAL.collation_server
-- ❌ Failed to handle query

SELECT * FROM information_schema.TABLES t WHERE...
-- ❌ P01: syntax error, unexpected identifier, expecting $end near 't'

use Manticore
-- ❌ P01: syntax error, unexpected identifier near 'use Manticore'

SHOW FULL TABLES FROM information_schema
-- ❌ P01: syntax error, unexpected identifier, expecting VARIABLES

SELECT COUNT(*) as table_count, 'tables' as type
-- ❌ P01: syntax error, unexpected AS near 'as type'

information_schema issues:

SELECT * FROM information_schema.tables
-- ❌ Undefined array key TABLE_NAME
-- ❌ Returns only 1 table out of 3, empty TABLE_SCHEMA and TABLE_TYPE

[PavelShilin89]

Grafana - Failed Queries:

SELECT table_name FROM information_schema.tables WHERE table_schema = ‘Manticore’ - returns only 1 table out of 3 created
SELECT DISTINCT TABLE_SCHEMA from information_schema.TABLES where TABLE_TYPE != ‘SYSTEM VIEW’ - works, but depends on incomplete data in information_schema.tables
Query Builder shows only 1 table instead of 3 due to problems with information_schema.tables

[PavelShilin89]

Blocked by issues:

• DBeaver compatibility issues with MySQL protocol manticoresearch#3610
• Grafana latest Query Builder shows incomplete table list due to information_schema.tables issues manticoresearch#3611
• Apache Superset 3.x integration fails with "unknown sysvar @@transaction_isolation" manticoresearch#3584