teratail
回答率
85.33%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問古いmysqldumpでダンプしたSQLファイルをMySQL5.7.18でimportしたい

編集履歴

質問編集履歴

3

回答者の要望に応えた。

2017/07/05 03:19

投稿

thisprogrammer
thisprogrammer

スコア27

test CHANGED
File without changes
test CHANGED
@@ -30,294 +30,320 @@
30
30
 
31
31
  # 回答者の要望
32
32
 
33
+ ## `SHOW GLOBAL VARIABLES LIKE 'innodb_file_%';` の結果
34
+
35
+ 古いサーバー
36
+
37
+ |Variable_name|Value|
38
+
39
+ |-------------|-----|
40
+
41
+ |innodb_file_format|Antelope|
42
+
43
+ |innodb_file_format_check|ON|
44
+
45
+ |innodb_file_format_max|Antelope|
46
+
47
+ |innodb_file_per_table|ON|
48
+
49
+
50
+
51
+ 新しいサーバー（MYSQL5.7.18）
52
+
53
+ | Variable_name | Value |
54
+
55
+ |--------------------------|-----------|
56
+
57
+ | innodb_file_format | Barracuda |
58
+
59
+ | innodb_file_format_check | ON |
60
+
61
+ | innodb_file_format_max | Barracuda |
62
+
63
+ | innodb_file_per_table | ON |
64
+
65
+
66
+
67
+ ## エラーの出るテーブル定義
68
+
33
- ```ここに言語を入力
69
+ ```sql
70
+
34
-
71
+ CREATE TABLE `bk_table` (
72
+
73
+ `a_bk_id` int(11) NOT NULL AUTO_INCREMENT,
74
+
75
+ `a_id` int(11) NOT NULL,
76
+
77
+ `a_id2` text COLLATE utf8_unicode_ci NOT NULL,
78
+
79
+ `a_id3` int(11) NOT NULL,
80
+
81
+ `a_id4` int(11) NOT NULL DEFAULT '1',
82
+
83
+ `a_id5` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
84
+
85
+ `a_id6` int(11) NOT NULL,
86
+
87
+ `a_id7` tinyint(4) NOT NULL DEFAULT '1',
88
+
89
+ `a_id8` tinyint(4) NOT NULL DEFAULT '0',
90
+
91
+ `a_id9` tinyint(4) NOT NULL DEFAULT '0',
92
+
93
+ `a_id10` int(11) NOT NULL DEFAULT '0',
94
+
95
+ `a_id11` text COLLATE utf8_unicode_ci NOT NULL,
96
+
97
+ `a_id12` text COLLATE utf8_unicode_ci NOT NULL,
98
+
99
+ `a_id13` tinyint(4) NOT NULL DEFAULT '0',
100
+
101
+ `a_id14` text COLLATE utf8_unicode_ci NOT NULL,
102
+
103
+ `a_id15` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
104
+
105
+ `a_id16` text COLLATE utf8_unicode_ci NOT NULL,
106
+
107
+ `a_id17` text COLLATE utf8_unicode_ci NOT NULL,
108
+
109
+ `a_id18` int(11) NOT NULL DEFAULT '0',
110
+
111
+ `a_id19` tinyint(4) NOT NULL,
112
+
113
+ `a_id20` text COLLATE utf8_unicode_ci NOT NULL,
114
+
115
+ `a_id21` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
116
+
117
+ `a_id22` int(11) NOT NULL DEFAULT '17',
118
+
119
+ `a_id23` text COLLATE utf8_unicode_ci NOT NULL,
120
+
121
+ `a_id24` text COLLATE utf8_unicode_ci NOT NULL,
122
+
123
+ `a_id25` int(11) NOT NULL,
124
+
125
+ `a_id26` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
126
+
127
+ `a_id27` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
128
+
129
+ `a_id28` text COLLATE utf8_unicode_ci NOT NULL,
130
+
131
+ `a_id29` text COLLATE utf8_unicode_ci NOT NULL,
132
+
133
+ `a_id30` int(11) NOT NULL DEFAULT '0',
134
+
135
+ `a_id31` int(11) NOT NULL,
136
+
137
+ `a_id32` int(11) NOT NULL,
138
+
139
+ `a_id33` int(11) NOT NULL,
140
+
141
+ `a_id34` datetime NOT NULL,
142
+
143
+ `a_id35` text COLLATE utf8_unicode_ci NOT NULL,
144
+
145
+ `a_id36` int(11) NOT NULL,
146
+
147
+ `a_id37` int(11) NOT NULL,
148
+
149
+ `a_id38` tinyint(4) NOT NULL DEFAULT '1',
150
+
151
+ `a_id39` text COLLATE utf8_unicode_ci NOT NULL,
152
+
153
+ `a_id40` text COLLATE utf8_unicode_ci NOT NULL,
154
+
155
+ `a_id41` text COLLATE utf8_unicode_ci NOT NULL,
156
+
157
+ `a_id42` int(11) NOT NULL DEFAULT '0',
158
+
159
+ `a_id43` int(11) NOT NULL DEFAULT '0',
160
+
161
+ `a_id44` int(11) NOT NULL DEFAULT '0',
162
+
163
+ `a_id45` int(11) NOT NULL DEFAULT '0',
164
+
165
+ `a_id46` int(11) NOT NULL DEFAULT '0',
166
+
167
+ `a_id47` text COLLATE utf8_unicode_ci NOT NULL,
168
+
169
+ `a_id48` text COLLATE utf8_unicode_ci NOT NULL,
170
+
171
+ `a_id49` text COLLATE utf8_unicode_ci NOT NULL,
172
+
173
+ `a_id50` text COLLATE utf8_unicode_ci NOT NULL,
174
+
175
+ `a_id51` text COLLATE utf8_unicode_ci NOT NULL,
176
+
177
+ `a_id52` text COLLATE utf8_unicode_ci NOT NULL,
178
+
179
+ `a_id53` text COLLATE utf8_unicode_ci NOT NULL,
180
+
181
+ `a_id54` text COLLATE utf8_unicode_ci NOT NULL,
182
+
183
+ `a_id55` text COLLATE utf8_unicode_ci NOT NULL,
184
+
185
+ `a_id56` text COLLATE utf8_unicode_ci NOT NULL,
186
+
187
+ `a_id57` text COLLATE utf8_unicode_ci NOT NULL,
188
+
189
+ `a_id58` text COLLATE utf8_unicode_ci NOT NULL,
190
+
191
+ `a_id59` text COLLATE utf8_unicode_ci NOT NULL,
192
+
193
+ `a_id60` text COLLATE utf8_unicode_ci NOT NULL,
194
+
195
+ `a_id61` text COLLATE utf8_unicode_ci NOT NULL,
196
+
197
+ `a_id62` text COLLATE utf8_unicode_ci NOT NULL,
198
+
199
+ `a_id63` text COLLATE utf8_unicode_ci NOT NULL,
200
+
201
+ `a_id64` text COLLATE utf8_unicode_ci NOT NULL,
202
+
203
+ `a_id65` text COLLATE utf8_unicode_ci NOT NULL,
204
+
205
+ `a_id66` text COLLATE utf8_unicode_ci NOT NULL,
206
+
207
+ `a_id67` text COLLATE utf8_unicode_ci NOT NULL,
208
+
209
+ `a_id68` text COLLATE utf8_unicode_ci NOT NULL,
210
+
211
+ `a_id69` text COLLATE utf8_unicode_ci NOT NULL,
212
+
213
+ `a_id70` text COLLATE utf8_unicode_ci NOT NULL,
214
+
215
+ `a_id71` text COLLATE utf8_unicode_ci NOT NULL,
216
+
217
+ `a_id72` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
218
+
219
+ `a_id73` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
220
+
221
+ `a_id74` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
222
+
223
+ `a_img_1` text COLLATE utf8_unicode_ci NOT NULL,
224
+
225
+ `a_img_2` text COLLATE utf8_unicode_ci NOT NULL,
226
+
227
+ `a_img_3` text COLLATE utf8_unicode_ci NOT NULL,
228
+
229
+ `a_img_4` text COLLATE utf8_unicode_ci NOT NULL,
230
+
231
+ `a_img_5` text COLLATE utf8_unicode_ci NOT NULL,
232
+
233
+ `a_img_6` text COLLATE utf8_unicode_ci NOT NULL,
234
+
235
+ `a_img_7` text COLLATE utf8_unicode_ci NOT NULL,
236
+
237
+ `a_img_8` text COLLATE utf8_unicode_ci NOT NULL,
238
+
239
+ `a_img_9` text COLLATE utf8_unicode_ci NOT NULL,
240
+
241
+ `a_img_10` text COLLATE utf8_unicode_ci NOT NULL,
242
+
243
+ `a_img_11` text COLLATE utf8_unicode_ci NOT NULL,
244
+
245
+ `a_img_12` text COLLATE utf8_unicode_ci NOT NULL,
246
+
247
+ `a_img_13` text COLLATE utf8_unicode_ci NOT NULL,
248
+
249
+ `a_img_14` text COLLATE utf8_unicode_ci NOT NULL,
250
+
251
+ `a_img_15` text COLLATE utf8_unicode_ci NOT NULL,
252
+
253
+ `a_img_16` text COLLATE utf8_unicode_ci NOT NULL,
254
+
255
+ `a_img_17` text COLLATE utf8_unicode_ci NOT NULL,
256
+
257
+ `a_img_18` text COLLATE utf8_unicode_ci NOT NULL,
258
+
259
+ `a_img_19` text COLLATE utf8_unicode_ci NOT NULL,
260
+
261
+ `a_img_20` text COLLATE utf8_unicode_ci NOT NULL,
262
+
263
+ `a_img_21` text COLLATE utf8_unicode_ci NOT NULL,
264
+
265
+ `a_img_22` text COLLATE utf8_unicode_ci NOT NULL,
266
+
267
+ `a_img_23` text COLLATE utf8_unicode_ci NOT NULL,
268
+
269
+ `a_img_24` text COLLATE utf8_unicode_ci NOT NULL,
270
+
271
+ `a_img_25` text COLLATE utf8_unicode_ci NOT NULL,
272
+
273
+ `a_img_26` text COLLATE utf8_unicode_ci NOT NULL,
274
+
275
+ `a_img_27` text COLLATE utf8_unicode_ci NOT NULL,
276
+
277
+ `a_img_28` text COLLATE utf8_unicode_ci NOT NULL,
278
+
279
+ `a_img_29` text COLLATE utf8_unicode_ci NOT NULL,
280
+
281
+ `a_img_30` text COLLATE utf8_unicode_ci NOT NULL,
282
+
283
+ `a_img_31` text COLLATE utf8_unicode_ci NOT NULL,
284
+
285
+ `a_img_32` text COLLATE utf8_unicode_ci NOT NULL,
286
+
287
+ `a_img_33` text COLLATE utf8_unicode_ci NOT NULL,
288
+
289
+ `a_img_34` text COLLATE utf8_unicode_ci NOT NULL,
290
+
291
+ `a_img_35` text COLLATE utf8_unicode_ci NOT NULL,
292
+
293
+ `a_img_36` text COLLATE utf8_unicode_ci NOT NULL,
294
+
295
+ `a_img_37` text COLLATE utf8_unicode_ci NOT NULL,
296
+
297
+ `a_img_38` text COLLATE utf8_unicode_ci NOT NULL,
298
+
299
+ `a_img_39` text COLLATE utf8_unicode_ci NOT NULL,
300
+
301
+ `a_img_40` text COLLATE utf8_unicode_ci NOT NULL,
302
+
303
+ -- もっともっと続く 文字数制限で全部掲載できない
304
+
305
+ `a_id94` datetime NOT NULL,
306
+
307
+ `a_id95` datetime NOT NULL,
308
+
309
+ `a_id96` datetime NOT NULL,
310
+
311
+ `a_id97` tinyint(4) NOT NULL,
312
+
313
+ `a_id98` text COLLATE utf8_unicode_ci NOT NULL,
314
+
315
+ `a_id99` text COLLATE utf8_unicode_ci NOT NULL,
316
+
317
+ `a_id100` int(11) NOT NULL,
318
+
319
+ `a_id101` text COLLATE utf8_unicode_ci,
320
+
321
+ `a_id102` text COLLATE utf8_unicode_ci,
322
+
323
+ `a_extra_option_1` text COLLATE utf8_unicode_ci NOT NULL,
324
+
325
+ `a_extra_option_2` text COLLATE utf8_unicode_ci NOT NULL,
326
+
327
+ `a_extra_option_3` text COLLATE utf8_unicode_ci NOT NULL,
328
+
329
+ `a_extra_option_4` text COLLATE utf8_unicode_ci NOT NULL,
330
+
331
+ `a_extra_option_5` text COLLATE utf8_unicode_ci NOT NULL,
332
+
333
+ `a_id103` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
334
+
335
+ `a_id104` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
336
+
337
+ `a_id105` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
338
+
339
+ `a_id106` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
340
+
341
+ `a_check_flag` int(11) NOT NULL DEFAULT '0',
342
+
343
+ PRIMARY KEY (`a_bk_id`),
344
+
35
- コード
345
+ KEY `a_id3` (`a_id3`)
346
+
347
+ ) ENGINE=InnoDB AUTO_INCREMENT=1852156 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
36
348
 
37
349
  ```
38
-
39
-
40
-
41
- ## エラーの出るテーブル定義
42
-
43
- ```sql
44
-
45
- CREATE TABLE `bk_table` (
46
-
47
- `a_bk_id` int(11) NOT NULL AUTO_INCREMENT,
48
-
49
- `a_id` int(11) NOT NULL,
50
-
51
- `a_id2` text COLLATE utf8_unicode_ci NOT NULL,
52
-
53
- `a_id3` int(11) NOT NULL,
54
-
55
- `a_id4` int(11) NOT NULL DEFAULT '1',
56
-
57
- `a_id5` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
58
-
59
- `a_id6` int(11) NOT NULL,
60
-
61
- `a_id7` tinyint(4) NOT NULL DEFAULT '1',
62
-
63
- `a_id8` tinyint(4) NOT NULL DEFAULT '0',
64
-
65
- `a_id9` tinyint(4) NOT NULL DEFAULT '0',
66
-
67
- `a_id10` int(11) NOT NULL DEFAULT '0',
68
-
69
- `a_id11` text COLLATE utf8_unicode_ci NOT NULL,
70
-
71
- `a_id12` text COLLATE utf8_unicode_ci NOT NULL,
72
-
73
- `a_id13` tinyint(4) NOT NULL DEFAULT '0',
74
-
75
- `a_id14` text COLLATE utf8_unicode_ci NOT NULL,
76
-
77
- `a_id15` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
78
-
79
- `a_id16` text COLLATE utf8_unicode_ci NOT NULL,
80
-
81
- `a_id17` text COLLATE utf8_unicode_ci NOT NULL,
82
-
83
- `a_id18` int(11) NOT NULL DEFAULT '0',
84
-
85
- `a_id19` tinyint(4) NOT NULL,
86
-
87
- `a_id20` text COLLATE utf8_unicode_ci NOT NULL,
88
-
89
- `a_id21` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
90
-
91
- `a_id22` int(11) NOT NULL DEFAULT '17',
92
-
93
- `a_id23` text COLLATE utf8_unicode_ci NOT NULL,
94
-
95
- `a_id24` text COLLATE utf8_unicode_ci NOT NULL,
96
-
97
- `a_id25` int(11) NOT NULL,
98
-
99
- `a_id26` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
100
-
101
- `a_id27` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
102
-
103
- `a_id28` text COLLATE utf8_unicode_ci NOT NULL,
104
-
105
- `a_id29` text COLLATE utf8_unicode_ci NOT NULL,
106
-
107
- `a_id30` int(11) NOT NULL DEFAULT '0',
108
-
109
- `a_id31` int(11) NOT NULL,
110
-
111
- `a_id32` int(11) NOT NULL,
112
-
113
- `a_id33` int(11) NOT NULL,
114
-
115
- `a_id34` datetime NOT NULL,
116
-
117
- `a_id35` text COLLATE utf8_unicode_ci NOT NULL,
118
-
119
- `a_id36` int(11) NOT NULL,
120
-
121
- `a_id37` int(11) NOT NULL,
122
-
123
- `a_id38` tinyint(4) NOT NULL DEFAULT '1',
124
-
125
- `a_id39` text COLLATE utf8_unicode_ci NOT NULL,
126
-
127
- `a_id40` text COLLATE utf8_unicode_ci NOT NULL,
128
-
129
- `a_id41` text COLLATE utf8_unicode_ci NOT NULL,
130
-
131
- `a_id42` int(11) NOT NULL DEFAULT '0',
132
-
133
- `a_id43` int(11) NOT NULL DEFAULT '0',
134
-
135
- `a_id44` int(11) NOT NULL DEFAULT '0',
136
-
137
- `a_id45` int(11) NOT NULL DEFAULT '0',
138
-
139
- `a_id46` int(11) NOT NULL DEFAULT '0',
140
-
141
- `a_id47` text COLLATE utf8_unicode_ci NOT NULL,
142
-
143
- `a_id48` text COLLATE utf8_unicode_ci NOT NULL,
144
-
145
- `a_id49` text COLLATE utf8_unicode_ci NOT NULL,
146
-
147
- `a_id50` text COLLATE utf8_unicode_ci NOT NULL,
148
-
149
- `a_id51` text COLLATE utf8_unicode_ci NOT NULL,
150
-
151
- `a_id52` text COLLATE utf8_unicode_ci NOT NULL,
152
-
153
- `a_id53` text COLLATE utf8_unicode_ci NOT NULL,
154
-
155
- `a_id54` text COLLATE utf8_unicode_ci NOT NULL,
156
-
157
- `a_id55` text COLLATE utf8_unicode_ci NOT NULL,
158
-
159
- `a_id56` text COLLATE utf8_unicode_ci NOT NULL,
160
-
161
- `a_id57` text COLLATE utf8_unicode_ci NOT NULL,
162
-
163
- `a_id58` text COLLATE utf8_unicode_ci NOT NULL,
164
-
165
- `a_id59` text COLLATE utf8_unicode_ci NOT NULL,
166
-
167
- `a_id60` text COLLATE utf8_unicode_ci NOT NULL,
168
-
169
- `a_id61` text COLLATE utf8_unicode_ci NOT NULL,
170
-
171
- `a_id62` text COLLATE utf8_unicode_ci NOT NULL,
172
-
173
- `a_id63` text COLLATE utf8_unicode_ci NOT NULL,
174
-
175
- `a_id64` text COLLATE utf8_unicode_ci NOT NULL,
176
-
177
- `a_id65` text COLLATE utf8_unicode_ci NOT NULL,
178
-
179
- `a_id66` text COLLATE utf8_unicode_ci NOT NULL,
180
-
181
- `a_id67` text COLLATE utf8_unicode_ci NOT NULL,
182
-
183
- `a_id68` text COLLATE utf8_unicode_ci NOT NULL,
184
-
185
- `a_id69` text COLLATE utf8_unicode_ci NOT NULL,
186
-
187
- `a_id70` text COLLATE utf8_unicode_ci NOT NULL,
188
-
189
- `a_id71` text COLLATE utf8_unicode_ci NOT NULL,
190
-
191
- `a_id72` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
192
-
193
- `a_id73` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
194
-
195
- `a_id74` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
196
-
197
- `a_img_1` text COLLATE utf8_unicode_ci NOT NULL,
198
-
199
- `a_img_2` text COLLATE utf8_unicode_ci NOT NULL,
200
-
201
- `a_img_3` text COLLATE utf8_unicode_ci NOT NULL,
202
-
203
- `a_img_4` text COLLATE utf8_unicode_ci NOT NULL,
204
-
205
- `a_img_5` text COLLATE utf8_unicode_ci NOT NULL,
206
-
207
- `a_img_6` text COLLATE utf8_unicode_ci NOT NULL,
208
-
209
- `a_img_7` text COLLATE utf8_unicode_ci NOT NULL,
210
-
211
- `a_img_8` text COLLATE utf8_unicode_ci NOT NULL,
212
-
213
- `a_img_9` text COLLATE utf8_unicode_ci NOT NULL,
214
-
215
- `a_img_10` text COLLATE utf8_unicode_ci NOT NULL,
216
-
217
- `a_img_11` text COLLATE utf8_unicode_ci NOT NULL,
218
-
219
- `a_img_12` text COLLATE utf8_unicode_ci NOT NULL,
220
-
221
- `a_img_13` text COLLATE utf8_unicode_ci NOT NULL,
222
-
223
- `a_img_14` text COLLATE utf8_unicode_ci NOT NULL,
224
-
225
- `a_img_15` text COLLATE utf8_unicode_ci NOT NULL,
226
-
227
- `a_img_16` text COLLATE utf8_unicode_ci NOT NULL,
228
-
229
- `a_img_17` text COLLATE utf8_unicode_ci NOT NULL,
230
-
231
- `a_img_18` text COLLATE utf8_unicode_ci NOT NULL,
232
-
233
- `a_img_19` text COLLATE utf8_unicode_ci NOT NULL,
234
-
235
- `a_img_20` text COLLATE utf8_unicode_ci NOT NULL,
236
-
237
- `a_img_21` text COLLATE utf8_unicode_ci NOT NULL,
238
-
239
- `a_img_22` text COLLATE utf8_unicode_ci NOT NULL,
240
-
241
- `a_img_23` text COLLATE utf8_unicode_ci NOT NULL,
242
-
243
- `a_img_24` text COLLATE utf8_unicode_ci NOT NULL,
244
-
245
- `a_img_25` text COLLATE utf8_unicode_ci NOT NULL,
246
-
247
- `a_img_26` text COLLATE utf8_unicode_ci NOT NULL,
248
-
249
- `a_img_27` text COLLATE utf8_unicode_ci NOT NULL,
250
-
251
- `a_img_28` text COLLATE utf8_unicode_ci NOT NULL,
252
-
253
- `a_img_29` text COLLATE utf8_unicode_ci NOT NULL,
254
-
255
- `a_img_30` text COLLATE utf8_unicode_ci NOT NULL,
256
-
257
- `a_img_31` text COLLATE utf8_unicode_ci NOT NULL,
258
-
259
- `a_img_32` text COLLATE utf8_unicode_ci NOT NULL,
260
-
261
- `a_img_33` text COLLATE utf8_unicode_ci NOT NULL,
262
-
263
- `a_img_34` text COLLATE utf8_unicode_ci NOT NULL,
264
-
265
- `a_img_35` text COLLATE utf8_unicode_ci NOT NULL,
266
-
267
- `a_img_36` text COLLATE utf8_unicode_ci NOT NULL,
268
-
269
- `a_img_37` text COLLATE utf8_unicode_ci NOT NULL,
270
-
271
- `a_img_38` text COLLATE utf8_unicode_ci NOT NULL,
272
-
273
- `a_img_39` text COLLATE utf8_unicode_ci NOT NULL,
274
-
275
- `a_img_40` text COLLATE utf8_unicode_ci NOT NULL,
276
-
277
- -- もっともっと続く 文字数制限で全部掲載できない
278
-
279
- `a_id94` datetime NOT NULL,
280
-
281
- `a_id95` datetime NOT NULL,
282
-
283
- `a_id96` datetime NOT NULL,
284
-
285
- `a_id97` tinyint(4) NOT NULL,
286
-
287
- `a_id98` text COLLATE utf8_unicode_ci NOT NULL,
288
-
289
- `a_id99` text COLLATE utf8_unicode_ci NOT NULL,
290
-
291
- `a_id100` int(11) NOT NULL,
292
-
293
- `a_id101` text COLLATE utf8_unicode_ci,
294
-
295
- `a_id102` text COLLATE utf8_unicode_ci,
296
-
297
- `a_extra_option_1` text COLLATE utf8_unicode_ci NOT NULL,
298
-
299
- `a_extra_option_2` text COLLATE utf8_unicode_ci NOT NULL,
300
-
301
- `a_extra_option_3` text COLLATE utf8_unicode_ci NOT NULL,
302
-
303
- `a_extra_option_4` text COLLATE utf8_unicode_ci NOT NULL,
304
-
305
- `a_extra_option_5` text COLLATE utf8_unicode_ci NOT NULL,
306
-
307
- `a_id103` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
308
-
309
- `a_id104` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
310
-
311
- `a_id105` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
312
-
313
- `a_id106` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
314
-
315
- `a_check_flag` int(11) NOT NULL DEFAULT '0',
316
-
317
- PRIMARY KEY (`a_bk_id`),
318
-
319
- KEY `a_id3` (`a_id3`)
320
-
321
- ) ENGINE=InnoDB AUTO_INCREMENT=1852156 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
322
-
323
- ```

2

回答者の要望に応えた。

2017/07/05 03:19

投稿

thisprogrammer
thisprogrammer

スコア27

test CHANGED
File without changes
test CHANGED
@@ -1,4 +1,4 @@
1
- ###わからない事、知りたいこと
1
+ #わからない事、知りたいこと
2
2
 
3
3
  古いmysqldumpでダンプしたSQLファイルをMYSQL5.7.18でimportすると次のようなエラーになる。
4
4
 
@@ -12,11 +12,11 @@
12
12
 
13
13
 
14
14
 
15
- ###やってみたこと
15
+ # やってみたこと
16
-
17
-
18
-
16
+
17
+
18
+
19
- 古いmysqldumpでダンプしたSQLファイルの先頭に次のように記述した。
19
+ ###### 古いmysqldumpでダンプしたSQLファイルの先頭に次のように記述した。
20
20
 
21
21
 
22
22
 
@@ -25,3 +25,299 @@
25
25
 
26
26
 
27
27
  しかし、エラーメッセージは変わらなかった。
28
+
29
+
30
+
31
+ # 回答者の要望
32
+
33
+ ```ここに言語を入力
34
+
35
+ コード
36
+
37
+ ```
38
+
39
+
40
+
41
+ ## エラーの出るテーブル定義
42
+
43
+ ```sql
44
+
45
+ CREATE TABLE `bk_table` (
46
+
47
+ `a_bk_id` int(11) NOT NULL AUTO_INCREMENT,
48
+
49
+ `a_id` int(11) NOT NULL,
50
+
51
+ `a_id2` text COLLATE utf8_unicode_ci NOT NULL,
52
+
53
+ `a_id3` int(11) NOT NULL,
54
+
55
+ `a_id4` int(11) NOT NULL DEFAULT '1',
56
+
57
+ `a_id5` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
58
+
59
+ `a_id6` int(11) NOT NULL,
60
+
61
+ `a_id7` tinyint(4) NOT NULL DEFAULT '1',
62
+
63
+ `a_id8` tinyint(4) NOT NULL DEFAULT '0',
64
+
65
+ `a_id9` tinyint(4) NOT NULL DEFAULT '0',
66
+
67
+ `a_id10` int(11) NOT NULL DEFAULT '0',
68
+
69
+ `a_id11` text COLLATE utf8_unicode_ci NOT NULL,
70
+
71
+ `a_id12` text COLLATE utf8_unicode_ci NOT NULL,
72
+
73
+ `a_id13` tinyint(4) NOT NULL DEFAULT '0',
74
+
75
+ `a_id14` text COLLATE utf8_unicode_ci NOT NULL,
76
+
77
+ `a_id15` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
78
+
79
+ `a_id16` text COLLATE utf8_unicode_ci NOT NULL,
80
+
81
+ `a_id17` text COLLATE utf8_unicode_ci NOT NULL,
82
+
83
+ `a_id18` int(11) NOT NULL DEFAULT '0',
84
+
85
+ `a_id19` tinyint(4) NOT NULL,
86
+
87
+ `a_id20` text COLLATE utf8_unicode_ci NOT NULL,
88
+
89
+ `a_id21` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
90
+
91
+ `a_id22` int(11) NOT NULL DEFAULT '17',
92
+
93
+ `a_id23` text COLLATE utf8_unicode_ci NOT NULL,
94
+
95
+ `a_id24` text COLLATE utf8_unicode_ci NOT NULL,
96
+
97
+ `a_id25` int(11) NOT NULL,
98
+
99
+ `a_id26` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
100
+
101
+ `a_id27` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
102
+
103
+ `a_id28` text COLLATE utf8_unicode_ci NOT NULL,
104
+
105
+ `a_id29` text COLLATE utf8_unicode_ci NOT NULL,
106
+
107
+ `a_id30` int(11) NOT NULL DEFAULT '0',
108
+
109
+ `a_id31` int(11) NOT NULL,
110
+
111
+ `a_id32` int(11) NOT NULL,
112
+
113
+ `a_id33` int(11) NOT NULL,
114
+
115
+ `a_id34` datetime NOT NULL,
116
+
117
+ `a_id35` text COLLATE utf8_unicode_ci NOT NULL,
118
+
119
+ `a_id36` int(11) NOT NULL,
120
+
121
+ `a_id37` int(11) NOT NULL,
122
+
123
+ `a_id38` tinyint(4) NOT NULL DEFAULT '1',
124
+
125
+ `a_id39` text COLLATE utf8_unicode_ci NOT NULL,
126
+
127
+ `a_id40` text COLLATE utf8_unicode_ci NOT NULL,
128
+
129
+ `a_id41` text COLLATE utf8_unicode_ci NOT NULL,
130
+
131
+ `a_id42` int(11) NOT NULL DEFAULT '0',
132
+
133
+ `a_id43` int(11) NOT NULL DEFAULT '0',
134
+
135
+ `a_id44` int(11) NOT NULL DEFAULT '0',
136
+
137
+ `a_id45` int(11) NOT NULL DEFAULT '0',
138
+
139
+ `a_id46` int(11) NOT NULL DEFAULT '0',
140
+
141
+ `a_id47` text COLLATE utf8_unicode_ci NOT NULL,
142
+
143
+ `a_id48` text COLLATE utf8_unicode_ci NOT NULL,
144
+
145
+ `a_id49` text COLLATE utf8_unicode_ci NOT NULL,
146
+
147
+ `a_id50` text COLLATE utf8_unicode_ci NOT NULL,
148
+
149
+ `a_id51` text COLLATE utf8_unicode_ci NOT NULL,
150
+
151
+ `a_id52` text COLLATE utf8_unicode_ci NOT NULL,
152
+
153
+ `a_id53` text COLLATE utf8_unicode_ci NOT NULL,
154
+
155
+ `a_id54` text COLLATE utf8_unicode_ci NOT NULL,
156
+
157
+ `a_id55` text COLLATE utf8_unicode_ci NOT NULL,
158
+
159
+ `a_id56` text COLLATE utf8_unicode_ci NOT NULL,
160
+
161
+ `a_id57` text COLLATE utf8_unicode_ci NOT NULL,
162
+
163
+ `a_id58` text COLLATE utf8_unicode_ci NOT NULL,
164
+
165
+ `a_id59` text COLLATE utf8_unicode_ci NOT NULL,
166
+
167
+ `a_id60` text COLLATE utf8_unicode_ci NOT NULL,
168
+
169
+ `a_id61` text COLLATE utf8_unicode_ci NOT NULL,
170
+
171
+ `a_id62` text COLLATE utf8_unicode_ci NOT NULL,
172
+
173
+ `a_id63` text COLLATE utf8_unicode_ci NOT NULL,
174
+
175
+ `a_id64` text COLLATE utf8_unicode_ci NOT NULL,
176
+
177
+ `a_id65` text COLLATE utf8_unicode_ci NOT NULL,
178
+
179
+ `a_id66` text COLLATE utf8_unicode_ci NOT NULL,
180
+
181
+ `a_id67` text COLLATE utf8_unicode_ci NOT NULL,
182
+
183
+ `a_id68` text COLLATE utf8_unicode_ci NOT NULL,
184
+
185
+ `a_id69` text COLLATE utf8_unicode_ci NOT NULL,
186
+
187
+ `a_id70` text COLLATE utf8_unicode_ci NOT NULL,
188
+
189
+ `a_id71` text COLLATE utf8_unicode_ci NOT NULL,
190
+
191
+ `a_id72` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
192
+
193
+ `a_id73` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
194
+
195
+ `a_id74` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
196
+
197
+ `a_img_1` text COLLATE utf8_unicode_ci NOT NULL,
198
+
199
+ `a_img_2` text COLLATE utf8_unicode_ci NOT NULL,
200
+
201
+ `a_img_3` text COLLATE utf8_unicode_ci NOT NULL,
202
+
203
+ `a_img_4` text COLLATE utf8_unicode_ci NOT NULL,
204
+
205
+ `a_img_5` text COLLATE utf8_unicode_ci NOT NULL,
206
+
207
+ `a_img_6` text COLLATE utf8_unicode_ci NOT NULL,
208
+
209
+ `a_img_7` text COLLATE utf8_unicode_ci NOT NULL,
210
+
211
+ `a_img_8` text COLLATE utf8_unicode_ci NOT NULL,
212
+
213
+ `a_img_9` text COLLATE utf8_unicode_ci NOT NULL,
214
+
215
+ `a_img_10` text COLLATE utf8_unicode_ci NOT NULL,
216
+
217
+ `a_img_11` text COLLATE utf8_unicode_ci NOT NULL,
218
+
219
+ `a_img_12` text COLLATE utf8_unicode_ci NOT NULL,
220
+
221
+ `a_img_13` text COLLATE utf8_unicode_ci NOT NULL,
222
+
223
+ `a_img_14` text COLLATE utf8_unicode_ci NOT NULL,
224
+
225
+ `a_img_15` text COLLATE utf8_unicode_ci NOT NULL,
226
+
227
+ `a_img_16` text COLLATE utf8_unicode_ci NOT NULL,
228
+
229
+ `a_img_17` text COLLATE utf8_unicode_ci NOT NULL,
230
+
231
+ `a_img_18` text COLLATE utf8_unicode_ci NOT NULL,
232
+
233
+ `a_img_19` text COLLATE utf8_unicode_ci NOT NULL,
234
+
235
+ `a_img_20` text COLLATE utf8_unicode_ci NOT NULL,
236
+
237
+ `a_img_21` text COLLATE utf8_unicode_ci NOT NULL,
238
+
239
+ `a_img_22` text COLLATE utf8_unicode_ci NOT NULL,
240
+
241
+ `a_img_23` text COLLATE utf8_unicode_ci NOT NULL,
242
+
243
+ `a_img_24` text COLLATE utf8_unicode_ci NOT NULL,
244
+
245
+ `a_img_25` text COLLATE utf8_unicode_ci NOT NULL,
246
+
247
+ `a_img_26` text COLLATE utf8_unicode_ci NOT NULL,
248
+
249
+ `a_img_27` text COLLATE utf8_unicode_ci NOT NULL,
250
+
251
+ `a_img_28` text COLLATE utf8_unicode_ci NOT NULL,
252
+
253
+ `a_img_29` text COLLATE utf8_unicode_ci NOT NULL,
254
+
255
+ `a_img_30` text COLLATE utf8_unicode_ci NOT NULL,
256
+
257
+ `a_img_31` text COLLATE utf8_unicode_ci NOT NULL,
258
+
259
+ `a_img_32` text COLLATE utf8_unicode_ci NOT NULL,
260
+
261
+ `a_img_33` text COLLATE utf8_unicode_ci NOT NULL,
262
+
263
+ `a_img_34` text COLLATE utf8_unicode_ci NOT NULL,
264
+
265
+ `a_img_35` text COLLATE utf8_unicode_ci NOT NULL,
266
+
267
+ `a_img_36` text COLLATE utf8_unicode_ci NOT NULL,
268
+
269
+ `a_img_37` text COLLATE utf8_unicode_ci NOT NULL,
270
+
271
+ `a_img_38` text COLLATE utf8_unicode_ci NOT NULL,
272
+
273
+ `a_img_39` text COLLATE utf8_unicode_ci NOT NULL,
274
+
275
+ `a_img_40` text COLLATE utf8_unicode_ci NOT NULL,
276
+
277
+ -- もっともっと続く 文字数制限で全部掲載できない
278
+
279
+ `a_id94` datetime NOT NULL,
280
+
281
+ `a_id95` datetime NOT NULL,
282
+
283
+ `a_id96` datetime NOT NULL,
284
+
285
+ `a_id97` tinyint(4) NOT NULL,
286
+
287
+ `a_id98` text COLLATE utf8_unicode_ci NOT NULL,
288
+
289
+ `a_id99` text COLLATE utf8_unicode_ci NOT NULL,
290
+
291
+ `a_id100` int(11) NOT NULL,
292
+
293
+ `a_id101` text COLLATE utf8_unicode_ci,
294
+
295
+ `a_id102` text COLLATE utf8_unicode_ci,
296
+
297
+ `a_extra_option_1` text COLLATE utf8_unicode_ci NOT NULL,
298
+
299
+ `a_extra_option_2` text COLLATE utf8_unicode_ci NOT NULL,
300
+
301
+ `a_extra_option_3` text COLLATE utf8_unicode_ci NOT NULL,
302
+
303
+ `a_extra_option_4` text COLLATE utf8_unicode_ci NOT NULL,
304
+
305
+ `a_extra_option_5` text COLLATE utf8_unicode_ci NOT NULL,
306
+
307
+ `a_id103` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
308
+
309
+ `a_id104` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
310
+
311
+ `a_id105` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
312
+
313
+ `a_id106` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
314
+
315
+ `a_check_flag` int(11) NOT NULL DEFAULT '0',
316
+
317
+ PRIMARY KEY (`a_bk_id`),
318
+
319
+ KEY `a_id3` (`a_id3`)
320
+
321
+ ) ENGINE=InnoDB AUTO_INCREMENT=1852156 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
322
+
323
+ ```

1

やってみたこと　追記

2017/07/05 03:17

投稿

thisprogrammer
thisprogrammer

スコア27

test CHANGED
File without changes
test CHANGED
@@ -9,3 +9,19 @@
9
9
 
10
10
 
11
11
  よって、インポートできない。このエラーを解決してSQLファイルを読み込み、データベースを復元したい。
12
+
13
+
14
+
15
+ ###やってみたこと
16
+
17
+
18
+
19
+ 古いmysqldumpでダンプしたSQLファイルの先頭に次のように記述した。
20
+
21
+
22
+
23
+ SET GLOBAL innodb_default_row_format=DYNAMIC;
24
+
25
+
26
+
27
+ しかし、エラーメッセージは変わらなかった。