sub partitioning

a marqué ce sujet comme résolu.

salut

J'ai un table access contenant des logs apaches avec des sous partitions par année, J'aimerai sous partitionner l'années courante par mois, savez vous comment faire ? 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE TABLE `access`
(
    `ip`            INT             UNSIGNED    NOT NULL        COMMENT "%a : Remote IP-address",
    `user`          VARCHAR(80)                 NOT NULL        COMMENT "%u : Remote user (from auth; may be bogus if return status (%s) is 401)",
    `date`          DATETIME                    DEFAULT NULL    COMMENT "%{%F %H:%M:%S}t : The time, in the form given by format, which should be in strftime(3) format. (potentially localized)",
    `protocol`      CHAR(16)                    NOT NULL        COMMENT "%H : The request protocol",
    `port`          SMALLINT        UNSIGNED    NOT NULL        COMMENT "%p : The canonical port of the server serving the request",
    `method`        CHAR(14)                    NOT NULL        COMMENT "%m : The request method",
    `file`          VARCHAR(240)                NOT NULL        COMMENT "%U : The URL path requested, not including any query string.",
    `query`         VARCHAR(7177)               NOT NULL        COMMENT "%q : The query string (prepended with a ? if a query string exists, otherwise an empty string)",
    `code`          SMALLINT        UNSIGNED    NOT NULL        COMMENT "%s : Status. For requests that got internally redirected, this is the status of the *original* request --- %>s for the last.",
    `sent`          INT             UNSIGNED    NOT NULL        COMMENT "%0 : Bytes sent, including headers, cannot be zero. You need to enable mod_logio to use this.",
    `seconds`       BIGINT          UNSIGNED    NOT NULL        COMMENT "%T : The time taken to serve the request, in seconds",
    `musec`         BIGINT          UNSIGNED    NOT NULL        COMMENT "%D : The time taken to serve the request, in microseconds",
    `referer`       VARCHAR(491)                DEFAULT NULL    COMMENT "%{Referer}i : Header Referer value",
    `user_agent`    VARCHAR(363)                DEFAULT NULL    COMMENT "%{User-Agent}i : Header User-agent value",

    INDEX(file),
    INDEX(code),
    INDEX(user),
    INDEX(seconds)
)

    PARTITION BY RANGE (YEAR(`date`))
    (
        PARTITION p2010 VALUES LESS THAN(2010),
        PARTITION p2011 VALUES LESS THAN(2011),
        PARTITION p2012 VALUES LESS THAN(2012),
        PARTITION p2013 VALUES LESS THAN(2013),
        PARTITION p2014 VALUES LESS THAN(2014),
        PARTITION p2015 VALUES LESS THAN(2015),
        PARTITION p2016 VALUES LESS THAN(2016),

        PARTITION pmax  VALUES LESS THAN MAXVALUE
    )
;
+0 -0

j'ai essayé : 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE TABLE `access`
(
    `ip`            INT             UNSIGNED    NOT NULL        COMMENT "%a : Remote IP-address",
    `user`          VARCHAR(80)                 NOT NULL        COMMENT "%u : Remote user (from auth; may be bogus if return status (%s) is 401)",
    `date`          DATETIME                    DEFAULT NULL    COMMENT "%{%F %H:%M:%S}t : The time, in the form given by format, which should be in strftime(3) format. (potentially localized)",
    `protocol`      CHAR(16)                    NOT NULL        COMMENT "%H : The request protocol",
    `port`          SMALLINT        UNSIGNED    NOT NULL        COMMENT "%p : The canonical port of the server serving the request",
    `method`        CHAR(14)                    NOT NULL        COMMENT "%m : The request method",
    `file`          VARCHAR(240)                NOT NULL        COMMENT "%U : The URL path requested, not including any query string.",
    `query`         VARCHAR(7177)               NOT NULL        COMMENT "%q : The query string (prepended with a ? if a query string exists, otherwise an empty string)",
    `code`          SMALLINT        UNSIGNED    NOT NULL        COMMENT "%s : Status. For requests that got internally redirected, this is the status of the *original* request --- %>s for the last.",
    `sent`          INT             UNSIGNED    NOT NULL        COMMENT "%0 : Bytes sent, including headers, cannot be zero. You need to enable mod_logio to use this.",
    `seconds`       BIGINT          UNSIGNED    NOT NULL        COMMENT "%T : The time taken to serve the request, in seconds",
    `musec`         BIGINT          UNSIGNED    NOT NULL        COMMENT "%D : The time taken to serve the request, in microseconds",
    `referer`       VARCHAR(491)                DEFAULT NULL    COMMENT "%{Referer}i : Header Referer value",
    `user_agent`    VARCHAR(363)                DEFAULT NULL    COMMENT "%{User-Agent}i : Header User-agent value",

    INDEX(file),
    INDEX(code),
    INDEX(user),
    INDEX(seconds)
)

    PARTITION BY RANGE (YEAR(`date`))
    (
        PARTITION p2010 VALUES LESS THAN(2010),
        PARTITION p2011 VALUES LESS THAN(2011),
        PARTITION p2012 VALUES LESS THAN(2012),
        PARTITION p2013 VALUES LESS THAN(2013),
        PARTITION p2014 VALUES LESS THAN(2014),
        PARTITION p2015 VALUES LESS THAN(2015),
        PARTITION p2016 VALUES LESS THAN(2016)
        (
            SUBPARTITION BY RANGE(MONTH(`date`))
            (
                PARTITION month_1   VALUES LESS THAN 1,
                PARTITION month_2   VALUES LESS THAN 2,
                PARTITION month_3   VALUES LESS THAN 3,
                PARTITION month_4   VALUES LESS THAN 4,
                PARTITION month_5   VALUES LESS THAN 5,
                PARTITION month_6   VALUES LESS THAN 6,
                PARTITION month_7   VALUES LESS THAN 7,
                PARTITION month_8   VALUES LESS THAN 8,
                PARTITION month_9   VALUES LESS THAN 9,
                PARTITION month_10  VALUES LESS THAN 10,
                PARTITION month_11  VALUES LESS THAN 11,
                PARTITION month_12  VALUES LESS THAN 12,
                PARTITION month_max VALUES LESS THAN MAXVALUE
            )

        ),

        ,

        PARTITION pmax  VALUES LESS THAN MAXVALUE
    )
;

mais je me mange : 

1
2
3
ERROR 1064 (42000) at line 4: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION BY RANGE(MONTH(`date`))
            (
                PARTITION mon' at line 34
+0 -0

Tu ne précises pas quel SGBD tu utilises, mais je suppose MySQL au vu des `nom` …?
De mémoire, quand tu sous-partitionnes, c’est par HASH ou par KEY… Et si j’ai bien compris ce que tu veux faire, la requête pour être simplifiée ainsi :

CREATE TABLE `access`
(
    `ip`            INT             UNSIGNED    NOT NULL        COMMENT "%a : Remote IP-address",
    `user`          VARCHAR(80)                 NOT NULL        COMMENT "%u : Remote user (from auth; may be bogus if return status (%s) is 401)",
    `date`          DATETIME                    DEFAULT NULL    COMMENT "%{%F %H:%M:%S}t : The time, in the form given by format, which should be in strftime(3) format. (potentially localized)",
    `protocol`      CHAR(16)                    NOT NULL        COMMENT "%H : The request protocol",
    `port`          SMALLINT        UNSIGNED    NOT NULL        COMMENT "%p : The canonical port of the server serving the request",
    `method`        CHAR(14)                    NOT NULL        COMMENT "%m : The request method",
    `file`          VARCHAR(240)                NOT NULL        COMMENT "%U : The URL path requested, not including any query string.",
    `query`         TEXT(7177)                  NOT NULL        COMMENT "%q : The query string (prepended with a ? if a query string exists, otherwise an empty string)",
    `code`          SMALLINT        UNSIGNED    NOT NULL        COMMENT "%s : Status. For requests that got internally redirected, this is the status of the *original* request --- %>s for the last.",
    `sent`          INT             UNSIGNED    NOT NULL        COMMENT "%0 : Bytes sent, including headers, cannot be zero. You need to enable mod_logio to use this.",
    `seconds`       BIGINT          UNSIGNED    NOT NULL        COMMENT "%T : The time taken to serve the request, in seconds",
    `musec`         BIGINT          UNSIGNED    NOT NULL        COMMENT "%D : The time taken to serve the request, in microseconds",
    `referer`       TEXT(491)                   DEFAULT NULL    COMMENT "%{Referer}i : Header Referer value",
    `user_agent`    TEXT(363)                   DEFAULT NULL    COMMENT "%{User-Agent}i : Header User-agent value",

    INDEX(file),
    INDEX(code),
    INDEX(user),
    INDEX(seconds)
)

    PARTITION BY RANGE( YEAR(`date`) )
    SUBPARTITION BY HASH( MONTH(`date`) )
    SUBPARTITIONS 12 (
        PARTITION p2010 VALUES LESS THAN(2010),
        PARTITION p2011 VALUES LESS THAN(2011),
        PARTITION p2012 VALUES LESS THAN(2012),
        PARTITION p2013 VALUES LESS THAN(2013),
        PARTITION p2014 VALUES LESS THAN(2014),
        PARTITION p2015 VALUES LESS THAN(2015),
        PARTITION p2016 VALUES LESS THAN(2016),
        PARTITION pmax  VALUES LESS THAN MAXVALUE
    )
;

Au passage, ce n’est jamais une bonne idée d’utiliser des noms réservés …comme date, user et method

+0 -0
Connectez-vous pour pouvoir poster un message.
Connexion

Pas encore membre ?

Créez un compte en une minute pour profiter pleinement de toutes les fonctionnalités de Zeste de Savoir. Ici, tout est gratuit et sans publicité.
Créer un compte