Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17461

SQL (1031): Not supported by this table type - when try sort field from table by Connect storage engine

Details

    Description

      Good day, I have installed MariaDB 10.3 on Windows OS, and on another port MySQL DB.

      In MySQL I create simple database from example https://github.com/datacharmer/test_db ( load_employees.dump - only 30 row insert)

      Next I use connect storage engine by next query:

       
      create table employees (
        last_name varchar(255) )
        
      engine=CONNECT table_type=MYSQL
      connection='mysql://mysql:mysql@127.0.0.1:3306/employees/employees';
      

      After this, I try to select data from created table, and everything nice

      SELECT * FROM `connect_engine`.`employees`;
      

      but when I try also SORT

      SELECT * FROM `connect_engine`.`employees` ORDER BY `last_name` ASC;
      

      I get error: SQL (1031): Not supported by this table type

      If I created this sample database and table in MariaDB (not in external database with connect engine) everything works and sorts.
      And also if I was not created a varchar *field and an *int - also everything works and sorts by connect engine...

      That is, what is the problem when sorting varchar fields in connect storage engine?

      Attachments

        Activity

          Did you try to set max_length_for_sort_data to a larger value (its default value is 1024 that is pretty small). It is the simpler solution if it works.

          About ODBC, look at the documentation in https://mariadb.com/kb/en/library/connect-odbc-table-type-accessing-tables-from-another-dbms/ the chapter "Random Access of ODBC Tables" describes the different ways to handle the sorting problem. They are what I intend to add to the MYSQL table type.

          bertrandop Olivier Bertrand added a comment - Did you try to set max_length_for_sort_data to a larger value (its default value is 1024 that is pretty small). It is the simpler solution if it works. About ODBC, look at the documentation in https://mariadb.com/kb/en/library/connect-odbc-table-type-accessing-tables-from-another-dbms/ the chapter "Random Access of ODBC Tables" describes the different ways to handle the sorting problem. They are what I intend to add to the MYSQL table type.
          bertrandop Olivier Bertrand added a comment - - edited

          Note also that the MYSQL table type is rather a type useful internally for CONNECT. In your case, using the FEDERATED engine also solve your problem because always reading the entire result in memory.

          bertrandop Olivier Bertrand added a comment - - edited Note also that the MYSQL table type is rather a type useful internally for CONNECT. In your case, using the FEDERATED engine also solve your problem because always reading the entire result in memory.

          Sorry for the long answer. Increased the parameter max_length_for_sort_data and the problem is gone! Thanks!

          strelkovandreyvalerievich Strelkov Andrey added a comment - Sorry for the long answer. Increased the parameter max_length_for_sort_data and the problem is gone! Thanks!
          luc.willems luc willems added a comment -

          we have encountered similar issues using connect to allow us to join tables from different external mariadb databases.

          we use connect engine because it pushes the WHERE clause to the remote servers , which is important because the data set
          is multi tenant and have > 1M records over multiple customers, using a filter on customer id is critical in this case.

          our current setup : using mysqld 10.3.12-MariaDB-1:10.3.12+maria~bionic in docker container.
          the only difference is the message we get following error message:

          ERROR 1031 (HY000): SetRecpos not implemented for this table type

          are there any plans to implement this on the mysql connect as federated doesn't seems to be a option in our case ?

          luc.willems luc willems added a comment - we have encountered similar issues using connect to allow us to join tables from different external mariadb databases. we use connect engine because it pushes the WHERE clause to the remote servers , which is important because the data set is multi tenant and have > 1M records over multiple customers, using a filter on customer id is critical in this case. our current setup : using mysqld 10.3.12-MariaDB-1:10.3.12+maria~bionic in docker container. the only difference is the message we get following error message: ERROR 1031 (HY000): SetRecpos not implemented for this table type are there any plans to implement this on the mysql connect as federated doesn't seems to be a option in our case ?

          The message can be different but this is the same limitation. To fix this you can increase the value of the variable max_length_for_sort_data as explained above or use the MEMORY option the same way it is done for the ODBC or JDBC type. Because the on line documentation on MEMORY is unclear and specifies wong values, look at my last comment of MDEV-17902 for an updated documentation.

          There are no plans to implement random access for MYSQL tables because it would be very bad performance wise.

          bertrandop Olivier Bertrand added a comment - The message can be different but this is the same limitation. To fix this you can increase the value of the variable max_length_for_sort_data as explained above or use the MEMORY option the same way it is done for the ODBC or JDBC type. Because the on line documentation on MEMORY is unclear and specifies wong values, look at my last comment of MDEV-17902 for an updated documentation. There are no plans to implement random access for MYSQL tables because it would be very bad performance wise.

          People

            bertrandop Olivier Bertrand
            strelkovandreyvalerievich Strelkov Andrey
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.