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