Error Code: 1214. The used table type doesn't support FULLTEXT indexes - when using UNION in Mysql 8.0

150 views Asked by At

I upgraded my Ubuntu server from 18.04 to 20.04 and - although I don't remember asking it to - MySql has also upgraded from 5.7 to 8.0.

My FULLTEXT queries that contain a UNION now fail with Error Code: 1214. The used table type doesn't support FULLTEXT indexes.

Example code:

SET @search = '+londo*';

SELECT
    id,
    city_name,
    region_name,
    country_name
FROM places
WHERE `nearby_city_name` IS NULL
AND MATCH (`city_name`, `region_name`, `country_name`) AGAINST (@search IN BOOLEAN MODE)

UNION

SELECT
    id,
    city_name,
    region_name,
    country_name
FROM places
WHERE `nearby_city_name` IS NOT NULL
AND MATCH (`nearby_city_name`) AGAINST (@search IN BOOLEAN MODE)
ORDER BY
    MATCH (`city_name`) AGAINST (@search IN BOOLEAN MODE) DESC,
    MATCH (`region_name`) AGAINST (@search IN BOOLEAN MODE) DESC,
    MATCH (`country_name`) AGAINST (@search IN BOOLEAN MODE) DESC,
    MATCH (`city_name`, `region_name`, `country_name`) AGAINST (@search IN BOOLEAN MODE) DESC
LIMIT 10;

This works fine with Mysql 5.7.

Mysql 8.0 is able to run each query separately without any trouble, however it fails when I try to UNION the two queries.

Is this a bug, or am I missing something? Thanks

0

There are 0 answers