Nodir Nodir - 7 months ago 29
SQL Question

MySQL Partitioning. The partition function return wrong type

What is incorrect in the following query?

CREATE TABLE `tbl_user_geolocation` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор',
`user_id` VARCHAR(100) NOT NULL COMMENT 'ID пользователя',
`client_type` VARCHAR(38) NULL DEFAULT NULL COMMENT 'Тип клиента',
`lat` DECIMAL(28,8) NOT NULL COMMENT 'Широта',
`lon` DECIMAL(28,8) NOT NULL COMMENT 'Долгота',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Дата создания',
PRIMARY KEY (`id`, `user_id`)
)
PARTITION BY HASH(id+user_id) PARTITIONS 200;


Error: The partition function returns the wrong type. 1491.
This table using for save users geo-location data

Answer

You need to pass an integer to HASH function. But in your case user_id is of type VARCHAR.

18.2.3. HASH Partitioning

To partition a table using HASH partitioning, it is necessary to append to the CREATE TABLE statement a PARTITION BY HASH (expr) clause, where expr is an expression that returns an integer.