CODEWITHSUNDEEP
mysql
sudhakar reddy karukala
sudhakar reddy karukala

Reputation: 253

MySQL Database Error: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

I was tried to make partition by the timestamp in mysql table. But it is returning an error

CREATE TABLE tblemployeepunch (
  fld_id int(11) NOT NULL AUTO_INCREMENT,
  fld_date Varchar(15) DEFAULT NULL,
  fld_rawpunchdate varchar(25) DEFAULT NULL,
  fld_rawpunchtime varchar(25) DEFAULT NULL,
  fld_cardno varchar(50) DEFAULT NULL,
  fld_reasoncard varchar(20) DEFAULT NULL,
  fld_mode varchar(20) DEFAULT NULL,
  fld_punchdatetime varchar(50) DEFAULT NULL,
  fld_crtdate  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY fld_id (fld_id,fld_crtdate),
  KEY in_timesheet (fld_cardno,fld_punchdatetime,fld_mode,fld_rawpunchtime),
  KEY in_emppunch (fld_cardno,fld_rawpunchdate,fld_punchdatetime)
  )
  PARTITION BY HASH(DAYOFYEAR(fld_crtdate))
PARTITIONS 12;

Upvotes: 10

Views: 20099

Answers (1)

fancyPants
fancyPants

Reputation: 51868

Well, the error message is quite clear. Don't partition by values which are in a timezone-dependent column. Use a column which has datatype date, datetime or int (by calculating DAYOFYEAR(fld_crtdate) beforehand in a separate column) or any other.

Upvotes: 4

Related Questions