CODEWITHSUNDEEP
mysqllaravelpartitioning
Roham Shojaei
Roham Shojaei

Reputation: 470

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

On MySQL 8 I have this table:

CREATE TABLE `float_values` (
  `id` bigint UNSIGNED NOT NULL,
  `attribute_id` bigint UNSIGNED NOT NULL,
  `value` double(8,2) NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `float_values`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `float_values_created_at_unique` (`created_at`);

ALTER TABLE `float_values`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

with this schema:

Schema::create('float_values', function (Blueprint $table) {
            $table->bigIncrements('id');

            $table->unsignedBigInteger('attribute_id');
            $table->float('value');

            $table->date('created_at');
            $table->date('updated_at');
        });

after table created I did this for partitioning on created_at column:

ALTER TABLE float_values 
PARTITION BY HASH( YEAR(created_at) )
PARTITIONS 4

but I get this error:

Static analysis:

1 errors were found during analysis.

Unrecognized alter operation. (near "" at position 0) SQL query:

ALTER TABLE float_values PARTITION BY HASH( YEAR(created_at) ) PARTITIONS 4

MySQL said: Documentation

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

I know that the question is duplicate but answers didn't help me!

the error says that we have to announce created_at as primary but we cant have two primary keys I changed created_at to unique but the problem still exists

Upvotes: 5

Views: 7216

Answers (3)

Rick James
Rick James

Reputation: 142208

PARTITION BY HASH is useless; don't bother figuring out how to make it work.

Furthermore, as a general rule, Partitioning is not useful unless you have at least a million rows. Since you are asking for a DATE to be UNIQUE, I suspect you will have not more than a few thousand rows.

If you would like to explain the purpose of the table, its eventual size, the goal for using Hash, and some SELECTs that you will use, I can probably explain how to achieve your goals without partitioning.

Scenarios:

For these queries:

... WHERE id = 111 AND created_at BETWEEN ... AND ... -- case 1
... WHERE attribute_id = 222                             -- case 2
... WHERE attribute_id = 333 AND created_at BETWEEN ... AND ... -- case 3

Then your table might be non-partitioned:

CREATE TABLE `float_values` (
  `id` bigint UNSIGNED NOT NULL  AUTO_INCREMENT,  -- debatable
  `attribute_id` bigint UNSIGNED NOT NULL,
  `value` double(8,2) NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` date NOT NULL
  PRIMARY KEY(id),                   -- for case 1
  INDEX(attribute_id, created_at)    -- for cases 2, 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Upvotes: 0

Roham Shojaei
Roham Shojaei

Reputation: 470

I found my answer.

when I was migrating the table it created a primary increment column on id so when I was doing the alter partition query it was telling me that partitioning column have to be a primary key! What I shall be doing was that create a table without primary keys and increments column in my schema and then after a simple table without any primary keys created I did create a primary key contains id, created_at columns like this:

First this:

CREATE TABLE `float_values` (
  `id` bigint UNSIGNED NOT NULL,
  `attribute_id` bigint UNSIGNED NOT NULL,
  `value` double(8,2) NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

With this schema:

Schema::create('float_values', function (Blueprint $table) {
            $table->unsignedBigInteger('id');

            $table->unsignedBigInteger('attribute_id');
            $table->float('value');

            $table->date('created_at');
            $table->primary(['id', 'created_at']);
            $table->date('updated_at');
        });

and then this query:

alter TABLE float_values add primary key (`id`, `created_at`)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562230

https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html says:

All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

In other words, every unique key on the table must use every column in the table's partitioning expression. This also includes the table's primary key, since it is by definition a unique key.

This is a blocker for many people who want to use table partitioning in MySQL. You can't always partition by the column you want to partition by, because it's either not in a unique key, or there is some other unique (or primary) key in the table.

Remember: the partitioning expression must be part of EVERY unique key of the table.

Even if you were to make created_at a unique key, there is still the fact that the primary key of this table is on the id column.

There is no way to use partitioning on a MySQL table if you have both a PRIMARY KEY and a UNIQUE KEY, and these two keys have no column in common.

Upvotes: 7

Related Questions