SQL Error (1005): Can't create table (errno: 1) Operation not permitted

Question

Good afternoon, I am quite sure that I am missing something here, but I am not sure what, my intent is to create an archive table for logs, partitioned by week so I can delete the partitions in the future.

I have seen this is possible according to: http://dev.mysql.com/doc/refman/5.1/en/partitioning-management-range-list.html I ran the same tests and everything worked fine, yet when I try to create the following table:

CREATE TABLE `tbDatabaseErrors` (
    `idError` INT(6) NOT NULL AUTO_INCREMENT,
    `database` VARCHAR(50) NULL DEFAULT NULL,
    `table` VARCHAR(50) NULL DEFAULT NULL,
    `errorMessage` VARCHAR(150) NULL DEFAULT NULL,
    `severity` TINYINT(4) NULL DEFAULT NULL,
    `DateTimeCreated` DATETIME NOT NULL,
    `WeekCreated` TINYINT(2) NOT NULL,
    PRIMARY KEY (`idError`, `WeekCreated`)
)
COLLATE='latin1_swedish_ci'
ENGINE=ARCHIVE
PARTITION BY RANGE(WeekCreated) (
    PARTITION p0 VALUES LESS THAN (4),
    PARTITION p1 VALUES LESS THAN (8),
    PARTITION p2 VALUES LESS THAN (12),
    PARTITION p3 VALUES LESS THAN (16),
    PARTITION p4 VALUES LESS THAN (20),
    PARTITION p5 VALUES LESS THAN (24),
    PARTITION p6 VALUES LESS THAN (28),
    PARTITION p7 VALUES LESS THAN (32),
    PARTITION p8 VALUES LESS THAN (36),
    PARTITION p9 VALUES LESS THAN (40),
    PARTITION p10 VALUES LESS THAN (44),
    PARTITION p11 VALUES LESS THAN (48),
    PARTITION p12 VALUES LESS THAN (53)
);

I keep getting:

SQL Error (1005): Can't create table 'support.tbDatabaseErrors' (errno: 1)
Operation not permitted

Any chance someone can help me debug and understand what is wrong here?

Answer 1

Archive Engine has so many limitations, it doesn't support replace, update and delete next it doesn't accept multi column primary or composite keys. You need to give single column as primary key or unique key. Which will not be helpful in your case.

https://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html

 CREATE TABLE `tbDatabaseErrors` (
     `idError` INT(6) NOT NULL AUTO_INCREMENT,
     `database` VARCHAR(50) DEFAULT NULL,
     `table` VARCHAR(50) DEFAULT NULL,
     `errorMessage` VARCHAR(150) DEFAULT NULL,
     `severity` INT(4) DEFAULT NULL,
     `DateTimeCreated` DATETIME DEFAULT current_timestamp,
     `WeekCreated` INT(2) NOT NULL,
  PRIMARY KEY(`idError`)
 )
 ENGINE=ARCHIVE
 COLLATE latin1_swedish_ci;
OK, 0 rows affected (0.01 sec)

ERROR:

 mysql> CREATE TABLE `tbDatabaseErrors` (
        ->     `idError` INT(6) NOT NULL AUTO_INCREMENT,
        ->     `database` VARCHAR(50) DEFAULT NULL,
        ->     `table` VARCHAR(50) DEFAULT NULL,
        ->     `errorMessage` VARCHAR(150) DEFAULT NULL,
        ->     `severity` INT(4) DEFAULT NULL,
        ->     `DateTimeCreated` DATETIME DEFAULT current_timestamp,
        ->     `WeekCreated` INT(2) NOT NULL,
        ->     PRIMARY KEY (`idError`, `WeekCreated`)
        -> )
        -> ENGINE=ARCHIVE
        -> COLLATE latin1_swedish_ci;

ERROR 1030 (HY000): Got error -1 from storage engine

Again here you can't add a parition on the non primary column which will obviously lead to an error. Please try out with other Engine which supports delete and update actions.