Feature request: Connection from common GUI (eg MySql Workbench, Devart dbForge,...)

[gootchick]

It would be nice to support connections from a common GUI like MySql Workbench or Devart dbForge. At this moment these connections failed mostly because of some SQL queries for the detection configuration of the MySql server.

Here is the error message from MySql Workbench

and here is from Devart dbForge

[tomatolog]

it could be better to provide full SphinxQL command as from error messages you provided it is not clear what statements cause this.

You could look at the searchd.log or query.log for any traces of bad statements.

You could also enable to log all incoming SphinxQL statements by set searchd.query_log_commands = 1 at the daemon config along with searchd.query_log_format = sphinxql . In this mode all incoming SphinxQL statements will be logged and you could post the full events flow.

I'd also try to use legacy version string searchd.mysql_version_string = 5.0.37 as described at our documentation to disable complex queries for your client

[gootchick]

Thanks for the explanation. Here is the result:

Official GUI for Mysql - MySQL Workbench failed here:

/ Thu Jan 26 08:32:49.357 2023 conn 1 real 0.000 / show character set where charset = 'utf8mb4'; / Thu Jan 26 08:32:49.357 2023 conn 1 / show character set where charset = 'utf8mb4' # error=sphinxql: syntax error, unexpected WHERE, expecting end of file near 'where charset = 'utf8mb4''

Devart dbForge failed twice here:

/ Thu Jan 26 08:32:52.722 2023 conn 2 real 0.000 / SELECT NULL FROM information_schema.tables LIMIT 0; / Thu Jan 26 08:32:52.722 2023 conn 2 / SELECT NULL FROM information_schema.tables LIMIT 0 # error=sphinxql: syntax error, unexpected NULL near 'NULL FROM information_schema.tables LIMIT 0'

/ Thu Jan 26 08:32:52.736 2023 conn 2 real 0.000 / SHOW VARIABLES WHERE Variable_name IN( 'version_comment', 'log_bin', 'have_geometry', 'default_storage_engine', 'table_type', 'have_partitioning', 'sql_mode', 'collation_database', 'default_authentication_plugin', 'old_passwords', 'character_set_server', 'character_set', 'character_set_connection', 'collation_server', 'lower_case_table_names', 'lower_case_file_system', 'profiling', 'autocommit', 'query_cache_type', 'default_password_lifetime', 'ndb_version_string', 'innodb_default_row_format', 'ndb_default_column_format'); / Thu Jan 26 08:32:52.736 2023 conn 2 / SHOW VARIABLES WHERE Variable_name IN( 'version_comment', 'log_bin', 'have_geometry', 'default_storage_engine', 'table_type', 'have_partitioning', 'sql_mode', 'collation_database', 'default_authentication_plugin', 'old_passwords', 'character_set_server', 'character_set', 'character_set_connection', 'collation_server', 'lower_case_table_names', 'lower_case_file_system', 'profiling', 'autocommit', 'query_cache_type', 'default_password_lifetime', 'ndb_version_string', 'innodb_default_row_format', 'ndb_default_column_format') # error=sphinxql: syntax error, unexpected IN, expecting '=' near 'IN( 'version_comment', 'log_bin', 'have_geometry', 'default_storage_engine', 'table_type', 'have_partitioning', 'sql_mode', 'collation_database', 'default_authentication_plugin', 'old_passwords', 'character_set_server', 'character_set', 'character_set_connection', 'collation_server', 'lower_case_table_names', 'lower_case_file_system', 'profiling', 'autocommit', 'query_cache_type', 'default_password_lifetime', 'ndb_version_string', 'innodb_default_row_format', 'ndb_default_column_format')'

[githubmanticore]

➤ Nick Sergeev commented:

We've opened another issue about the subject so we close this one.

[sanikolaev]

@Nick-S-2018 what has been done within this one? Does Manticore work well with Devart dbForge now?

[githubmanticore]

➤ Nick Sergeev commented:

Yes, it works with dbForge now, but the issue about MySQL Workbench still remains.

[sanikolaev]

From TG:

The latest version of manticore (6.3.2), the latest version of docker (4.31.1) and the latest version of dbForge (10.0.225, but the same problem even on an older version at a colleague). windows 10 64b

Reopening to check it.

[sanikolaev]

@Nick-S-2018 I see you've assigned this issue to @donhardman. What's expected from him?

[Nick-S-2018]

A review is needed here, for this related PR: https://github.com/manticoresoftware/manticoresearch-buddy/pull/349

[donhardman]

I reviewed the pull request. Looks ok, just some minor points to fix before we merge.

[Nick-S-2018]

I've done code refinement and reassisigned the issue to @donhardman to confirm all is ok now.

[donhardman]

It looks good for me now

[Nick-S-2018]

Done in https://github.com/manticoresoftware/manticoresearch-buddy/pull/349

[sanikolaev]

@Nick-S-2018 pls update the changelog https://docs.google.com/spreadsheets/d/1mz_3dRWKs86FjRF7EIZUziUDK_2Hvhd97G0pLpxo05s/edit?pli=1&gid=1102439133#gid=1102439133

[Nick-S-2018]

Done