example_sort.sql

More simpe example attach to comment

This example create database and one table (example_sort) where created 5 column, and after create connect engine to myself, and this created connect engine can't sort

Thanks for the report! Reproduced as described on 10.3.10, 10.0-10.3:

MariaDB [test]>  CREATE or replace TABLE  `t3` ENGINE=CONNECT CONNECTION='mysql://root@localhost/test/example_sort' `table_type`=MYSQL;

Query OK, 0 rows affected (0,041 sec)

MariaDB [test]> select * from t3;

+------------+------------+--------------+-------------+-------------+

| column_one | column_two | column_three | column_four | column_five |

+------------+------------+--------------+-------------+-------------+

|          1 | Word one   | Word two     | Word three  | Word four   |

+------------+------------+--------------+-------------+-------------+

1 row in set (0,002 sec)

MariaDB [test]> describe t3;

+--------------+--------------+------+-----+---------+-------+

| Field        | Type         | Null | Key | Default | Extra |

+--------------+--------------+------+-----+---------+-------+

| column_one   | int(10)      | YES  |     | NULL    |       |

| column_two   | varchar(255) | YES  |     | NULL    |       |

| column_three | varchar(255) | YES  |     | NULL    |       |

| column_four  | varchar(255) | YES  |     | NULL    |       |

| column_five  | varchar(255) | YES  |     | NULL    |       |

+--------------+--------------+------+-----+---------+-------+

5 rows in set (0,010 sec)

MariaDB [test]> select * from t3 order by 1;

ERROR 1031 (HY000): Not supported by this table type

Error (Code 1031): Not supported by this table type

Error (Code 1296): Got error 122 'Not supported by this table type' from CONNECT

MariaDB [test]> select * from t3 order by column_one;

ERROR 1031 (HY000): Not supported by this table type

Error (Code 1031): Not supported by this table type

Error (Code 1296): Got error 122 'Not supported by this table type' from CONNECT

MariaDB [test]> select version();

+-----------------+

| version()       |

+-----------------+

| 10.3.10-MariaDB |

+-----------------+

1 row in set (0,010 sec)

Indeed the MYSQL table type is currently missing the facilities to randomly access a remote table that are existing for the ODBC or JDBC table types. I'll be working on adding them the MYSQL type.

Meanwhile, there are several turnarounds to avoid this. MariaDB can often do the sorting itself without asking the handler to do it. The conditions are that the table be not too big and the column not too long. This can be handled by setting a higher value of the system variable max_length_for_sort_data. To avoid setting a too big value for large tables, some more tricks can be applied:

In some tables the column might be set too long. if a column declared as VARCHAR(255) actually contains values that are no longer than 50 characters, you can define the corresponding column in the CONNECT table as VARCHAR(50) and the sort size will be reduced.

For utf8 tables the internal size is larger than the VARCHAR number. This is because for MariaDB the number is the number of characters and can be multiplied by 3 for utf8 charset. Therefore, the sort size can be reduced by not declaring the CONNECT table as utf8. This can be done like this:

CREATE OR REPLACE TABLE `connect_example_sort` (

  `column_one` int(10) DEFAULT NULL,

  `column_two` varchar(250) NOT NULL,

  `column_three` varchar(255) DEFAULT NULL,

  `column_four` varchar(255) DEFAULT NULL,

  `column_five` varchar(255) DEFAULT NULL

) ENGINE=CONNECT `table_type`=MYSQL

CONNECTION='mysql://root@127.0.0.1:3306/example_sort/example_sort';

It is, by the way, how to have utf8 characters translated to your local charset when reading the table.

Thanks for reply! Sorry for my weak English, but do I understand that the main problem is that it is difficult to sort the fields of large length, and if the more they are in UTF8 encoding thus takes even more bytes character. I.e. there is at least a solution is to sort by fields less than the number of characters.
I also want to note that connect engine I in most cases use to connect ODBC to Oracle database, and there is the same problem, or rather it manifests itself in different ways, under one field sorting with an error will fall, on the other there (I tried on different fields, on INT, VARCHAR(255) and even with Russian text, but it is difficult to understand the pattern because of what falls).
I'll try again to play with the lengths of the lines in the fields and understand why the problem arises

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.

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!

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.