alter table engine=archive

**Fabien Celaia** · 07/03/2017, 10h13

j'utilise MySQL 5.7.17 sur RedHat7

J'essaie de convertir une table Innodb en archive et ai quelques soucis

1) des clés dupliquées... sans clé dupliquée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE `MYTABLE` ( 
  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT, 
  `NAME_FR` varchar(50) DEFAULT NULL, 
  `NAME_DE` varchar(50) DEFAULT NULL, 
  `NAME_IT` varchar(50) DEFAULT NULL, 
  `NAME_EN` varchar(50) DEFAULT NULL, 
  PRIMARY KEY (`ID`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 
 
ALTER TABLE MYTABLE engine=archive ;  => tout ok
ALTER TABLE MYTABLE engine=Innodb;  => tout ok
 
INSERT INTO `MYTABLE` VALUES 
(1,'identité','Identität','identità','identity'),
(2,'somme','Summe','somma','sum'),
(3,'moyenne','Mittelwert','media','average'),
(4,'moyenne mobile','gleitender Durchschnitt','media mobile','moving average'),
(5,'minimum','Minimum','minimo','minimum'),
(6,'maximum','Maximum','massimo','maximum');
 
ALTER TABLE MYTABLE engine=archive ; 
 
Lookup Error - MySQL Database Error: Can't write; duplicate key in table '#sql-950b_476'

2) Même table, mais sans AUTO_INCREMENT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE MYTABLE engine=archive ; 
 Lookup Error - MySQL Database Error: Got error -1 from storage engine

**Artemus24** · 07/03/2017, 20h24

Salut Fadace.

Si le moteur "archive" n'est pas activé ("skip-archive"), j'obtiens une erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
58
59
60
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SHOW engines
--------------
 
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | NO      | Archive storage engine                                         | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `mytable`
--------------
 
--------------
CREATE TABLE `MYTABLE` (
  `ID`      smallint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `NAME_FR` varchar(50)       DEFAULT NULL,
  `NAME_DE` varchar(50)       DEFAULT NULL,
  `NAME_IT` varchar(50)       DEFAULT NULL,
  `NAME_EN` varchar(50)       DEFAULT NULL
 ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
commit
--------------
 
--------------
ALTER TABLE MYTABLE engine=archive
--------------
 
ERROR 1286 (42000) at line 40: Unknown storage engine 'archive'

Inversement, si le moteur est actif, je n'ai pas de problème :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SHOW engines
--------------
 
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `mytable`
--------------
 
--------------
CREATE TABLE `MYTABLE` (
  `ID`      smallint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `NAME_FR` varchar(50)       DEFAULT NULL,
  `NAME_DE` varchar(50)       DEFAULT NULL,
  `NAME_IT` varchar(50)       DEFAULT NULL,
  `NAME_EN` varchar(50)       DEFAULT NULL
 ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
commit
--------------
 
--------------
ALTER TABLE MYTABLE engine=archive
--------------
 
--------------
commit
--------------
 
--------------
INSERT INTO `MYTABLE` VALUES
  (1,'identité',       'Identität',               'identità',     'identity'),
  (2,'somme',          'Summe',                   'somma',        'sum'),
  (3,'moyenne',        'Mittelwert',              'media',        'average'),
  (4,'moyenne mobile', 'gleitender Durchschnitt', 'media mobile', 'moving average'),
  (5,'minimum',        'Minimum',                 'minimo',       'minimum'),
  (6,'maximum',        'Maximum',                 'massimo',      'maximum')
--------------
 
--------------
select * from `mytable`
--------------
 
+----+----------------+-------------------------+--------------+----------------+
| ID | NAME_FR        | NAME_DE                 | NAME_IT      | NAME_EN        |
+----+----------------+-------------------------+--------------+----------------+
|  1 | identité       | Identität               | identità     | identity       |
|  2 | somme          | Summe                   | somma        | sum            |
|  3 | moyenne        | Mittelwert              | media        | average        |
|  4 | moyenne mobile | gleitender Durchschnitt | media mobile | moving average |
|  5 | minimum        | Minimum                 | minimo       | minimum        |
|  6 | maximum        | Maximum                 | massimo      | maximum        |
+----+----------------+-------------------------+--------------+----------------+
--------------
show create table mytable
--------------
 
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mytable | CREATE TABLE `mytable` (
  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `NAME_FR` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `NAME_DE` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `NAME_IT` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `NAME_EN` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=ARCHIVE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Est-ce que le moteur "archive" est activé dans votre SGBDR MySql ?

@+

alter table engine=archive

MySQL

Vue hybride

Discussions similaires

Partager

Partager