~drizzle-trunk/drizzle/development

ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

131

CREATE TABLE t1(

132

c TEXT NOT NULL, d TEXT NOT NULL,

133

PRIMARY KEY (c(767),d(767)))

134

ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;

135

ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

136

CREATE TABLE t1(

137

c TEXT NOT NULL, d TEXT NOT NULL,

138

PRIMARY KEY (c(767),d(767)))

139

ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;

140

drop table t1;

141

CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))

142

ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;

143

ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

144

CREATE TABLE t1(c TEXT, PRIMARY KEY (c(439)))

145

ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;

146

INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));

147

DROP TABLE t1;

148

create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,

149

primary key(c1, c2(22), c3(22)))

150

engine = innodb row_format = dynamic;

151

begin;

152

insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),

153

repeat('C', 20000));

154

update t1 set c3 = repeat('D', 20000) where c1 = 1;

155

commit;

156

select count(*) from t1 where c2 = repeat('A', 20000);

157

count(*)

158

159

select count(*) from t1 where c3 = repeat('D', 20000);

160

count(*)

161

162

select count(*) from t1 where c4 = repeat('C', 20000);

163

count(*)

164

165

update t1 set c3 = repeat('E', 20000) where c1 = 1;

166

drop table t1;

167

set global innodb_file_format=`0`;

168

select @@innodb_file_format;

169

@@innodb_file_format

170

Antelope

171

set global innodb_file_format=`1`;

172

select @@innodb_file_format;

173

@@innodb_file_format

174

Barracuda

175

set global innodb_file_format=`2`;

176

ERROR HY000: Incorrect arguments to SET

177

set global innodb_file_format=`-1`;

178

ERROR HY000: Incorrect arguments to SET

179

set global innodb_file_format=`Antelope`;

180

set global innodb_file_format=`Barracuda`;

181

set global innodb_file_format=`Cheetah`;

182

ERROR HY000: Incorrect arguments to SET

183

set global innodb_file_format=`abc`;

184

ERROR HY000: Incorrect arguments to SET

185

set global innodb_file_format=`1a`;

186

ERROR HY000: Incorrect arguments to SET

187

set global innodb_file_format=``;

188

ERROR HY000: Incorrect arguments to SET

189

set global innodb_file_per_table = on;

190

set global innodb_file_format = `1`;

191

set innodb_strict_mode = off;

192

create table t1 (id int primary key) engine = innodb key_block_size = 0;

193

Warnings:

194

Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=0.

195

drop table t1;

196

set innodb_strict_mode = on;

197

create table t1 (id int primary key) engine = innodb key_block_size = 0;

198

ERROR HY000: Can't create table 'test.t1' (errno: 1478)

199

show errors;

200

Level Code Message

201

Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 0. Valid values are [1, 2, 4, 8, 16]

202

Error 1005 Can't create table 'test.t1' (errno: 1478)

203

create table t2 (id int primary key) engine = innodb key_block_size = 9;

204

ERROR HY000: Can't create table 'test.t2' (errno: 1478)

205

show errors;

206

Level Code Message

207

Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]

208

Error 1005 Can't create table 'test.t2' (errno: 1478)

209

create table t3 (id int primary key) engine = innodb key_block_size = 1;

210

create table t4 (id int primary key) engine = innodb key_block_size = 2;

211

create table t5 (id int primary key) engine = innodb key_block_size = 4;

212

create table t6 (id int primary key) engine = innodb key_block_size = 8;

213

create table t7 (id int primary key) engine = innodb key_block_size = 16;

214

create table t8 (id int primary key) engine = innodb row_format = compressed;

215

create table t9 (id int primary key) engine = innodb row_format = dynamic;

216

create table t10(id int primary key) engine = innodb row_format = compact;

217

create table t11(id int primary key) engine = innodb row_format = redundant;

218

SELECT table_schema, table_name, row_format

219

FROM information_schema.tables WHERE engine='innodb';

220

table_schema table_name row_format

221

test t10 Compact

222

test t11 Redundant

223

test t3 Compressed

224

test t4 Compressed

225

test t5 Compressed

226

test t6 Compressed

227

test t7 Compressed

228

test t8 Compressed

229

test t9 Dynamic

230

drop table t3, t4, t5, t6, t7, t8, t9, t10, t11;

231

create table t1 (id int primary key) engine = innodb

232

key_block_size = 8 row_format = compressed;

233

create table t2 (id int primary key) engine = innodb

234

key_block_size = 8 row_format = redundant;

235

ERROR HY000: Can't create table 'test.t2' (errno: 1478)

236

show errors;

237

Level Code Message

238

Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.

239

Error 1005 Can't create table 'test.t2' (errno: 1478)

240

create table t3 (id int primary key) engine = innodb

241

key_block_size = 8 row_format = compact;

242

ERROR HY000: Can't create table 'test.t3' (errno: 1478)

243

show errors;

244

Level Code Message

245

Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.

246

Error 1005 Can't create table 'test.t3' (errno: 1478)

247

create table t4 (id int primary key) engine = innodb

248

key_block_size = 8 row_format = dynamic;

249

ERROR HY000: Can't create table 'test.t4' (errno: 1478)

250

show errors;

251

Level Code Message

252

Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.

253

Error 1005 Can't create table 'test.t4' (errno: 1478)

254

create table t5 (id int primary key) engine = innodb

255

key_block_size = 8 row_format = default;

256

ERROR HY000: Can't create table 'test.t5' (errno: 1478)

257

show errors;

258

Level Code Message

259

Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.

260

Error 1005 Can't create table 'test.t5' (errno: 1478)

261

SELECT table_schema, table_name, row_format

262

FROM information_schema.tables WHERE engine='innodb';

263

table_schema table_name row_format

264

test t1 Compressed

265

drop table t1;

266

create table t1 (id int primary key) engine = innodb

267

key_block_size = 9 row_format = redundant;

268

ERROR HY000: Can't create table 'test.t1' (errno: 1478)

269

show errors;

270

Level Code Message

271

Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]

272

Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE.

273

Error 1005 Can't create table 'test.t1' (errno: 1478)

274

create table t2 (id int primary key) engine = innodb

275

key_block_size = 9 row_format = compact;

276

ERROR HY000: Can't create table 'test.t2' (errno: 1478)

277

show errors;

278

Level Code Message

279

Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]

280

Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE.

281

Error 1005 Can't create table 'test.t2' (errno: 1478)

282

create table t2 (id int primary key) engine = innodb

283

key_block_size = 9 row_format = dynamic;

284

ERROR HY000: Can't create table 'test.t2' (errno: 1478)

285

show errors;

286

Level Code Message

287

Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]

288

Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.

289

Error 1005 Can't create table 'test.t2' (errno: 1478)

290

SELECT table_schema, table_name, row_format

291

FROM information_schema.tables WHERE engine='innodb';

292

table_schema table_name row_format

293

set global innodb_file_per_table = off;

294

create table t1 (id int primary key) engine = innodb key_block_size = 1;

295

ERROR HY000: Can't create table 'test.t1' (errno: 1478)

296

show errors;

297

Level Code Message

298

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

299

Error 1005 Can't create table 'test.t1' (errno: 1478)

300

create table t2 (id int primary key) engine = innodb key_block_size = 2;

301

ERROR HY000: Can't create table 'test.t2' (errno: 1478)

302

show errors;

303

Level Code Message

304

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

305

Error 1005 Can't create table 'test.t2' (errno: 1478)

306

create table t3 (id int primary key) engine = innodb key_block_size = 4;

307

ERROR HY000: Can't create table 'test.t3' (errno: 1478)

308

show errors;

309

Level Code Message

310

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

311

Error 1005 Can't create table 'test.t3' (errno: 1478)

312

create table t4 (id int primary key) engine = innodb key_block_size = 8;

313

ERROR HY000: Can't create table 'test.t4' (errno: 1478)

314

show errors;

315

Level Code Message

316

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

317

Error 1005 Can't create table 'test.t4' (errno: 1478)

318

create table t5 (id int primary key) engine = innodb key_block_size = 16;

319

ERROR HY000: Can't create table 'test.t5' (errno: 1478)

320

show errors;

321

Level Code Message

322

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

323

Error 1005 Can't create table 'test.t5' (errno: 1478)

324

create table t6 (id int primary key) engine = innodb row_format = compressed;

325

ERROR HY000: Can't create table 'test.t6' (errno: 1478)

326

show errors;

327

Level Code Message

328

Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.

329

Error 1005 Can't create table 'test.t6' (errno: 1478)

330

create table t7 (id int primary key) engine = innodb row_format = dynamic;

331

ERROR HY000: Can't create table 'test.t7' (errno: 1478)

332

show errors;

333

Level Code Message

334

Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.

335

Error 1005 Can't create table 'test.t7' (errno: 1478)

336

create table t8 (id int primary key) engine = innodb row_format = compact;

337

create table t9 (id int primary key) engine = innodb row_format = redundant;

338

SELECT table_schema, table_name, row_format

339

FROM information_schema.tables WHERE engine='innodb';

340

table_schema table_name row_format

341

test t8 Compact

342

test t9 Redundant

343

drop table t8, t9;

344

set global innodb_file_per_table = on;

345

set global innodb_file_format = `0`;

346

create table t1 (id int primary key) engine = innodb key_block_size = 1;

347

ERROR HY000: Can't create table 'test.t1' (errno: 1478)

348

show errors;

349

Level Code Message

350

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.

351

Error 1005 Can't create table 'test.t1' (errno: 1478)

352

create table t2 (id int primary key) engine = innodb key_block_size = 2;

353

ERROR HY000: Can't create table 'test.t2' (errno: 1478)

354

show errors;

355

Level Code Message

356

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.

357

Error 1005 Can't create table 'test.t2' (errno: 1478)

358

create table t3 (id int primary key) engine = innodb key_block_size = 4;

359

ERROR HY000: Can't create table 'test.t3' (errno: 1478)

360

show errors;

361

Level Code Message

362

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.

363

Error 1005 Can't create table 'test.t3' (errno: 1478)

364

create table t4 (id int primary key) engine = innodb key_block_size = 8;

365

ERROR HY000: Can't create table 'test.t4' (errno: 1478)

366

show errors;

367

Level Code Message

368

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.

369

Error 1005 Can't create table 'test.t4' (errno: 1478)

370

create table t5 (id int primary key) engine = innodb key_block_size = 16;

371

ERROR HY000: Can't create table 'test.t5' (errno: 1478)

372

show errors;

373

Level Code Message

374

Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.

375

Error 1005 Can't create table 'test.t5' (errno: 1478)

376

create table t6 (id int primary key) engine = innodb row_format = compressed;

377

ERROR HY000: Can't create table 'test.t6' (errno: 1478)

378

show errors;

379

Level Code Message

380

Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.

381

Error 1005 Can't create table 'test.t6' (errno: 1478)

382

create table t7 (id int primary key) engine = innodb row_format = dynamic;

383

ERROR HY000: Can't create table 'test.t7' (errno: 1478)

384

show errors;

385

Level Code Message

386

Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.

387

Error 1005 Can't create table 'test.t7' (errno: 1478)

388

create table t8 (id int primary key) engine = innodb row_format = compact;

389

create table t9 (id int primary key) engine = innodb row_format = redundant;

390

SELECT table_schema, table_name, row_format

391

FROM information_schema.tables WHERE engine='innodb';

392

table_schema table_name row_format

393

test t8 Compact

394

test t9 Redundant

395

drop table t8, t9;

396

set global innodb_file_per_table=0;

397

set global innodb_file_format=Antelope;

398

set global innodb_file_per_table=on;

399

set global innodb_file_format=`Barracuda`;

400

set global innodb_file_format_check=`Antelope`;

401

create table normal_table (

402

c1 int

403

) engine = innodb;

404

select @@innodb_file_format_check;

405

@@innodb_file_format_check

406

Antelope

407

create table zip_table (

408

c1 int

409

) engine = innodb key_block_size = 8;

410

select @@innodb_file_format_check;

411

@@innodb_file_format_check

412

Barracuda

413

set global innodb_file_format_check=`Antelope`;

414

select @@innodb_file_format_check;

415

@@innodb_file_format_check

416

Antelope

417

show table status;

418

select @@innodb_file_format_check;

419

@@innodb_file_format_check

420

Barracuda

421

drop table normal_table, zip_table;

Older »