title: MySQL - Primary key needed for partition
body: <p>I need to partition my table every day based on UTC timestamp, I am getting error as <code>Fatal error: A PRIMARY KEY must include all columns in the table's partitioning function</code></p>
  
  <p>I am doing this way, column<code>id</code> is INT PRIMARY KEY column <code>date</code> is INT storing UTC timestamp</p>
  
  <p>My table structure <code>DESCRIBE stats;</code></p>
  
  <pre><code>Field   Type    Null    Key     Default     Extra   
  id      int(11) NO      PRI     NULL    auto_increment
  date    int(11) YES             NULL    
  .
  .
  .
  .
  </code></pre>
  
  <p>Create table structure <code>SHOW CREATE TABLE stats</code></p>
  
  <pre><code>CREATE TABLE `stats` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `date` int(11) DEFAULT NULL,
   .
   .
   .
   .
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=371 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  </code></pre>
  
  <p>I execute script via php</p>
  
  <pre><code>$today = strtotime("today midnight");
  $partition = date('d_m_y',$today); 
  
  ALTER TABLE `stats`
  PARTITION BY RANGE (date) (
  PARTITION p_$partition VALUES LESS THAN ($today)
  )
  </code></pre>
  
  <p>I tried this but getting syntax error</p>
  
  <pre><code>ALTER TABLE `stats`
  UNIQUE KEY (`id`)
  PARTITION BY RANGE (date) (
  PARTITION p_$partition VALUES LESS THAN ($today)
  )
  
  ALTER TABLE `stats`
  PRIMARY KEY (`id`)
  PARTITION BY RANGE (date) (
  PARTITION p_$partition VALUES LESS THAN ($today)
  )
  
  ALTER TABLE `stats`
  PRIMARY KEY (`id`,`date`)
  PARTITION BY RANGE (date) (
  PARTITION p_$partition VALUES LESS THAN ($today)
  )
  </code></pre>
  
  <p>Also tried with <code>HASH</code> but same error <code>A PRIMARY KEY must include all columns in the table's partitioning function in</code></p>
  
  <pre><code>ALTER TABLE stats
  PARTITION BY HASH(date)
  PARTITIONS 1
  </code></pre>
  
  <p>Also tried this way but got error <code>Fatal error: Partition management on a not partitioned table is not possible</code></p>
  
  <pre><code>ALTER TABLE `stats` 
  ADD PARTITION (
  PARTITION p_$partition VALUES LESS THAN ($today)
  )
  </code></pre>
  
  <p>Please see and suggest any possible way to do this.</p>
  
  <p>Thanks</p>