[#MDEV-22265] Connect string character limit too small for full 64 character InnoDB table-name limit when using ad-hoc Spider server definitions.

When creating spider tables with ad-hoc server parameters (ie. not a reference to mysql.servers but host, port, database, user, password in the table definition) the spider table creation process throws "ERROR 12502 (HY000): The connect info for table is too long" before the 64-character tabe-name length limit is reached, causing some tables on source servers to be inaccessible as spider connect tables to user who cannot declare spider servers.

The length limit of the connect string is a little bit complicated to calculate exactly because implicit table names are expanded by spider and hostnames can also be resolved. The limit appears to be 149 characters fully resolved.

For example:

MariaDB [test32738]> create table t234567891123456789212345678931234567894123456789512(id int) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='host "10.0.0.114", user "spider", password "spider", port "3306", database "test32738", table ""t234567891123456789212345678931234567894123456789512';

Query OK, 0 rows affected (0.029 sec)

MariaDB [test32738]> create table t2345678911234567892123456789312345678941234567895123(id int) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='host "10.0.0.114", user "spider", password "spider", port "3306", database "test32738", table "t2345678911234567892123456789312345678941234567895123"';

ERROR 12502 (HY000): The connect info 't2345678911234567892123456789312345678941234567895123' for table is too long

MariaDB [test32738]> create table t2345678911234567892123456789312345678941234567895123(id int) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='host "h114", user "spider", password "spider", port "3306", database "test32738"';

ERROR 12502 (HY000): The connect info 't2345678911234567892123456789312345678941234567895123' for table is too long

where

~# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.0.114	h114

and

~# echo 'host "10.0.0.114", user "spider", password "spider", port "3306", database "test32738", table "t234567891123456789212345678931234567894123456789512"' | wc

      1      12     149

~# echo 'host "10.0.0.114", user "spider", password "spider", port "3306", database "test32738", table "t2345678911234567892123456789312345678941234567895123"' | wc

      1      12     150

~# echo 'host "h114", user "spider", password "spider", port "3306", database "test32738"' | wc

      1      10      81

[MDEV-22265] Connect string character limit too small for full 64 character InnoDB table-name limit when using ad-hoc Spider server definitions. Created: 2020-04-16 Updated: 2021-12-16 Resolved: 2021-05-01
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - Spider
Affects Version/s:	10.4.10
Fix Version/s:	10.4.19, 10.5.10

[MDEV-22265] Connect string character limit too small for full 64 character InnoDB table-name limit when using ad-hoc Spider server definitions. Created: 2020-04-16 Updated: 2021-12-16 Resolved: 2021-05-01