T
T
tzn2021-09-03 16:44:18
MySQL
tzn, 2021-09-03 16:44:18

PARTITION for a ready database?

I apologize for such a stupid question, and perhaps obvious to someone, but still ...
Now I'm studying PARTITION for mysql database, and I ran into a problem - I have a ready-made database in which there is already quite a lot of data. I want to do partitioning by date, but I constantly get errors by type

A PRIMARY KEY must include all columns in the table's partitioning function


Partition column values of incorrect type

etc.

I know how to use Google, but there are no examples of partitioning for a finished table anywhere. To the mature question - is it even real? if so what am i doing wrong. Thank you.

here are all my attempts:

ALTER TABLE leads  PARTITION BY RANGE COLUMNS (date) (
    PARTITION p1 VALUES LESS THAN ('2021-08-01')

);


ALTER TABLE leads  PARTITION BY RANGE (Month (date))
    (
    PARTITION p_1 VALUES LESS THAN (TO_DAYS('2021-05-01')),
    PARTITION p_2 VALUES LESS THAN MAXVALUE );


ALTER TABLE leads  PARTITION BY RANGE (Month (date))
    (
    PARTITION p_1 VALUES LESS THAN (TO_DAYS('2021-05-01')),
    PARTITION p_2 VALUES LESS THAN MAXVALUE );


ALTER TABLE leads  PARTITION BY RANGE (Month (date))
    (
    PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2021-05-01'))
    );

Answer the question

In order to leave comments, you need to log in

1 answer(s)
I
Ilya, 2021-09-04
@tzn

I want to do partitioning by date, but I constantly get errors like
A PRIMARY KEY must include all columns in the table's partitioning function

This error explains to you what is wrong, it is not clear why you continue to ignore it.
If you want to partition on the date column, then it must be part of the primary key. That is, you need to recreate the primary key accordingly, and then make partitions

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question