~drizzle-trunk/drizzle/development

capabilities). It supports interactive and non\-interactive use. When used interactively, query results are presented in an ASCII\-table format. When used non\-interactively (for example, as a filter), the result is presented in tab\-separated format. The output format can be changed using command options.

.PP

If you have problems due to insufficient memory for large result sets, use the

\fB\-\-quick\fR

option. This forces

\fBdrizzle\fR

to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it.

.PP

Using

\fBdrizzle\fR

is very easy. Invoke it from the prompt of your command interpreter as follows:

.sp

.RS 3n

.nf

shell> \fBdrizzle \fR\fB\fIdb_name\fR\fR

.fi

.RE

.PP

Or:

.sp

.RS 3n

.nf

shell> \fBdrizzle \-\-user=\fR\fB\fIuser_name\fR\fR\fB \-\-password=\fR\fB\fIyour_password\fR\fR\fB \fR\fB\fIdb_name\fR\fR

.fi

.RE

.PP

Then type an SQL statement, end it with

\(lq;\(rq,

\\g, or

\\G

and press Enter.

.PP

Typing Control\-C causes

\fBdrizzle\fR

to attempt to kill the current statement. If this cannot be done, or Control\-C is typed again before the statement is killed,

\fBdrizzle\fR

exits.

.PP

You can execute SQL statements in a script file (batch file) like this:

.sp

.RS 3n

.nf

shell> \fBdrizzle \fR\fB\fIdb_name\fR\fR\fB < \fR\fB\fIscript.sql\fR\fR\fB > \fR\fB\fIoutput.tab\fR\fR

.fi

.RE

.SH "\fBDRIZZLE\fR OPTIONS"

.PP

\fBdrizzle\fR

supports the options in the following list.

.TP 3n

\(bu

\fB\-\-help\fR,

\fB\-?\fR

.sp

Display a help message and exit.

.TP 3n

\(bu

\fB\-\-auto\-rehash\fR

.sp

Enable automatic rehashing. This option is on by default, which enables database, table, and column name completion. Use

\fB\-\-disable\-auto\-rehash\fR

to disable rehashing. That causes

\fBdrizzle\fR

to start faster, but you must issue the

rehash

command if you want to use name completion.

.sp

To complete a name, enter the first part and press Tab. If the name is unambiguous,

\fBdrizzle\fR

completes it. Otherwise, you can press Tab again to see the possible names that begin with what you have typed so far. Completion does not occur if there is no default database.

.TP 3n

\(bu

\fB\-\-auto\-vertical\-output\fR

.sp

Causes result sets to be displayed vertically if they are too wide for the current window, and using normal tabular format otherwise. (This applies to statements terminated by

;

100

\\G.) This option was added in Drizzle 6.0.4.

101

.TP 3n

102

\(bu

103

\fB\-\-batch\fR,

104

\fB\-B\fR

105

.sp

106

Print results using tab as the column separator, with each row on a new line. With this option,

107

\fBdrizzle\fR

108

does not use the history file.

109

.sp

110

Batch mode results in non\-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the

111

\fB\-\-raw\fR

112

option.

113

.TP 3n

114

\(bu

115

\fB\-\-character\-sets\-dir=\fR\fB\fIpath\fR\fR

116

.sp

117

The directory where character sets are installed. See

118

Section\ 9.2, \(lqThe Character Set Used for Data and Sorting\(rq.

119

.TP 3n

120

\(bu

121

\fB\-\-column\-names\fR

122

.sp

123

Write column names in results.

124

.TP 3n

125

\(bu

126

\fB\-\-column\-type\-info\fR,

127

\fB\-m\fR

128

.sp

129

Display result set metadata.

130

.TP 3n

131

\(bu

132

\fB\-\-comments\fR,

133

\fB\-c\fR

134

.sp

135

Whether to preserve comments in statements sent to the server. The default is \-\-skip\-comments (discard comments), enable with \-\-comments (preserve comments). This option was added in Drizzle 6.0.4.

136

.TP 3n

137

\(bu

138

\fB\-\-compress\fR,

139

\fB\-C\fR

140

.sp

141

Compress all information sent between the client and the server if both support compression.

142

.TP 3n

143

\(bu

144

\fB\-\-database=\fR\fB\fIdb_name\fR\fR,

145

\fB\-D \fR\fB\fIdb_name\fR\fR

146

.sp

147

The database to use. This is useful primarily in an option file.

148

.TP 3n

149

\(bu

150

\fB\-\-debug\-check\fR

151

.sp

152

Print some debugging information when the program exits.

153

.TP 3n

154

\(bu

155

\fB\-\-debug\-info\fR,

156

\fB\-T\fR

157

.sp

158

Print debugging information and memory and CPU usage statistics when the program exits.

159

.TP 3n

160

\(bu

161

\fB\-\-delimiter=\fR\fB\fIstr\fR\fR

162

.sp

163

Set the statement delimiter. The default is the semicolon character (\(lq;\(rq).

164

.TP 3n

165

\(bu

166

\fB\-\-disable\-named\-commands\fR

167

.sp

168

Disable named commands. Use the

169

\\*

170

form only, or use named commands only at the beginning of a line ending with a semicolon (\(lq;\(rq).

171

\fBdrizzle\fR

172

starts with this option

173

\fIenabled\fR

174

by default. However, even with this option, long\-format commands still work from the first line. See

175

the section called \(lq\fBMYSQL\fR COMMANDS\(rq.

176

.TP 3n

177

\(bu

178

\fB\-\-execute=\fR\fB\fIstatement\fR\fR,

179

\fB\-e \fR\fB\fIstatement\fR\fR

180

.sp

181

Execute the statement and quit. The default output format is like that produced with

182

\fB\-\-batch\fR. See

183

Section\ 4.2.3.1, \(lqUsing Options on the Command Line\(rq, for some examples.

184

.TP 3n

185

\(bu

186

\fB\-\-force\fR,

187

\fB\-f\fR

188

.sp

189

Continue even if an SQL error occurs.

190

.TP 3n

191

\(bu

192

\fB\-\-host=\fR\fB\fIhost_name\fR\fR,

193

\fB\-h \fR\fB\fIhost_name\fR\fR

194

.sp

195

Connect to the Drizzle server on the given host.

196

.TP 3n

197

\(bu

198

\fB\-\-html\fR,

199

\fB\-H\fR

200

.sp

201

Produce HTML output.

202

.TP 3n

203

\(bu

204

\fB\-\-ignore\-spaces\fR,

205

\fB\-i\fR

206

.sp

207

Ignore spaces after function names. The effect of this is described in the discussion for the

208

IGNORE_SPACE

209

SQL mode (see

210

Section\ 5.1.7, \(lqServer SQL Modes\(rq).

211

.TP 3n

212

\(bu

213

\fB\-\-line\-numbers\fR

214

.sp

215

Write line numbers for errors. Disable this with

216

\fB\-\-skip\-line\-numbers\fR.

217

.TP 3n

218

\(bu

219

\fB\-\-local\-infile[={0|1}]\fR

220

.sp

221

Enable or disable

222

LOCAL

223

capability for

224

LOAD DATA INFILE. With no value, the option enables

225

LOCAL. The option may be given as

226

\fB\-\-local\-infile=0\fR

227

228

\fB\-\-local\-infile=1\fR

229

to explicitly disable or enable

230

LOCAL. Enabling

231

LOCAL

232

has no effect if the server does not also support it.

233

.TP 3n

234

\(bu

235

\fB\-\-named\-commands\fR,

236

\fB\-G\fR

237

.sp

238

Enable named

239

\fBdrizzle\fR

240

commands. Long\-format commands are allowed, not just short\-format commands. For example,

241

quit

242

and

243

\\q

244

both are recognized. Use

245

\fB\-\-skip\-named\-commands\fR

246

to disable named commands. See

247

the section called \(lq\fBMYSQL\fR COMMANDS\(rq.

248

.TP 3n

249

\(bu

250

\fB\-\-no\-auto\-rehash\fR,

251

\fB\-A\fR

252

.sp

253

Deprecated form of

254

\fB\-skip\-auto\-rehash\fR. Use

255

\fB\-\-disable\-auto\-rehash\fR

256

instead. See the description for

257

\fB\-\-auto\-rehash\fR.

258

.TP 3n

259

\(bu

260

\fB\-\-no\-beep\fR,

261

\fB\-b\fR

262

.sp

263

Do not beep when errors occur.

264

.TP 3n

265

\(bu

266

\fB\-\-no\-named\-commands\fR,

267

\fB\-g\fR

268

.sp

269

Deprecated, use

270

\fB\-\-disable\-named\-commands\fR

271

instead.

272

.TP 3n

273

\(bu

274

\fB\-\-no\-pager\fR

275

.sp

276

Deprecated form of

277

\fB\-\-skip\-pager\fR. See the

278

\fB\-\-pager\fR

279

option.

280

.TP 3n

281

\(bu

282

\fB\-\-no\-tee\fR

283

.sp

284

Do not copy output to a file.

285

the section called \(lq\fBMYSQL\fR COMMANDS\(rq, discusses tee files further.

286

.TP 3n

287

\(bu

288

\fB\-\-one\-database\fR,

289

\fB\-o\fR

290

.sp

291

Ignore statements except those for the default database named on the command line. This is useful for skipping updates to other databases in the binary log.

292

.TP 3n

293

\(bu

294

\fB\-\-pager[=\fR\fB\fIcommand\fR\fR\fB]\fR

295

.sp

296

Use the given command for paging query output. If the command is omitted, the default pager is the value of your

297

PAGER

298

environment variable. Valid pagers are

299

\fBless\fR,

300

\fBmore\fR,

301

\fBcat [> filename]\fR, and so forth. This option works only on Unix. It does not work in batch mode. To disable paging, use

302

\fB\-\-skip\-pager\fR.

303

the section called \(lq\fBMYSQL\fR COMMANDS\(rq, discusses output paging further.

304

.TP 3n

305

\(bu

306

\fB\-\-password[=\fR\fB\fIpassword\fR\fR\fB]\fR,

307

\fB\-P[\fR\fB\fIpassword\fR\fR\fB]\fR

308

.sp

309

The password to use when connecting to the server. If you use the short option form (\fB\-p\fR), you

310

\fIcannot\fR

311

have a space between the option and the password. If you omit the

312

\fIpassword\fR

313

value following the

314

\fB\-\-password\fR

315

316

\fB\-p\fR

317

option on the command line, you are prompted for one.

318

.sp

319

Specifying a password on the command line should be considered insecure. See

320

Section\ 5.5.6.2, \(lqEnd\-User Guidelines for Password Security\(rq.

321

.TP 3n

322

\(bu

323

\fB\-\-pipe\fR,

324

\fB\-W\fR

325

.sp

326

On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named\-pipe connections.

327

.TP 3n

328

\(bu

329

\fB\-\-port=\fR\fB\fIport_num\fR\fR,

330

\fB\-p \fR\fB\fIport_num\fR\fR

331

.sp

332

The TCP/IP port number to use for the connection.

333

.TP 3n

334

\(bu

335

\fB\-\-prompt=\fR\fB\fIformat_str\fR\fR

336

.sp

337

Set the prompt to the specified format. The default is

338

drizzle>. The special sequences that the prompt can contain are described in

339

the section called \(lq\fBMYSQL\fR COMMANDS\(rq.

340

.TP 3n

341

\(bu

342

\fB\-\-protocol={TCP|SOCKET|PIPE|MEMORY}\fR

343

.sp

344

The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the allowable values, see

345

Section\ 4.2.2, \(lqConnecting to the Drizzle Server\(rq.

346

.TP 3n

347

\(bu

348

\fB\-\-quick\fR,

349

\fB\-q\fR

350

.sp

351

Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option,

352

\fBdrizzle\fR

353

does not use the history file.

354

.TP 3n

355

\(bu

356

\fB\-\-raw\fR,

357

\fB\-r\fR

358

.sp

359

For tabular output, the

360

\(lqboxing\(rq

361

around columns enables one column value to be distinguished from another. For non\-tabular output (such as is produced in batch mode or when the

362

\fB\-\-batch\fR

363

364

\fB\-\-silent\fR

365

option is given), special characters are escaped in the output so they can be identified easily. Newline, tab,

366

NUL, and backslash are written as

367

\\n,

368

\\t,

369

\\0, and

370

\\\\. The

371

\fB\-\-raw\fR

372

option disables this character escaping.

373

.sp

374

The following example demonstrates tabular versus non\-tabular output and the use of raw mode to disable escaping:

375

.sp

376

.RS 3n

377

.nf

378

% \fBdrizzle\fR

379

drizzle> SELECT CHAR(92);

380

+\-\-\-\-\-\-\-\-\-\-+

381

| CHAR(92) |

382

+\-\-\-\-\-\-\-\-\-\-+

383

| \\ |

384

+\-\-\-\-\-\-\-\-\-\-+

385

% \fBdrizzle \-s\fR

386

drizzle> SELECT CHAR(92);

387

CHAR(92)

388

\\\\

389

% \fBdrizzle \-s \-r\fR

390

drizzle> SELECT CHAR(92);

391

CHAR(92)

392

393

.fi

394

.RE

395

.TP 3n

396

\(bu

397

\fB\-\-reconnect\fR

398

.sp

399

If the connection to the server is lost, automatically try to reconnect. A single reconnect attempt is made each time the connection is lost. To suppress reconnection behavior, use

400

\fB\-\-skip\-reconnect\fR.

401

.TP 3n

402

\(bu

403

\fB\-\-safe\-updates\fR,

404

\fB\-\-i\-am\-a\-dummy\fR,

405

\fB\-U\fR

406

.sp

407

Allow only those

408

UPDATE

409

and

410

DELETE

411

statements that specify which rows to modify by using key values. If you have set this option in an option file, you can override it by using

412

\fB\-\-safe\-updates\fR

413

on the command line. See

414

the section called \(lq\fBMYSQL\fR TIPS\(rq, for more information about this option.

415

.TP 3n

416

\(bu

417

\fB\-\-secure\-auth\fR

418

.sp

419

Do not send passwords to the server in old (pre\-4.1.1) format. This prevents connections except for servers that use the newer password format.

420

.TP 3n

421

\(bu

422

\fB\-\-show\-warnings\fR

423

.sp

424

Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

425

.TP 3n

426

\(bu

427

\fB\-\-sigint\-ignore\fR

428

.sp

429

Ignore

430

SIGINT

431

signals (typically the result of typing Control\-C).

432

.TP 3n

433

\(bu

434

\fB\-\-silent\fR,

435

\fB\-s\fR

436

.sp

437

Silent mode. Produce less output. This option can be given multiple times to produce less and less output.

438

.sp

439

This option results in non\-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the

440

\fB\-\-raw\fR

441

option.

442

.TP 3n

443

\(bu

444

\fB\-\-skip\-column\-names\fR,

445

\fB\-N\fR

446

.sp

447

Do not write column names in results. The short format,

448

\fB\-N\fR

449

is deprecated, use the long format instead.

450

.TP 3n

451

\(bu

452

\fB\-\-skip\-line\-numbers\fR,

453

\fB\-L\fR

454

.sp

455

Do not write line numbers for errors. Useful when you want to compare result files that include error messages. The short format,

456

\fB\-L\fR

457

is deprecated, use the long format instead.

458

.TP 3n

459

\(bu

460

\fB\-\-socket=\fR\fB\fIpath\fR\fR,

461

\fB\-S \fR\fB\fIpath\fR\fR

462

.sp

463

For connections to

464

localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

465

.TP 3n

466

\(bu

467

\fB\-\-ssl*\fR

468

.sp

469

Options that begin with

470

\fB\-\-ssl\fR

471

specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See

472

Section\ 5.5.7.3, \(lqSSL Command Options\(rq.

473

.TP 3n

474

\(bu

475

\fB\-\-table\fR,

476

\fB\-t\fR

477

.sp

478

Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

479

.TP 3n

480

\(bu

481

\fB\-\-tee=\fR\fB\fIfile_name\fR\fR

482

.sp

483

Append a copy of output to the given file. This option does not work in batch mode.

484

the section called \(lq\fBMYSQL\fR COMMANDS\(rq, discusses tee files further.

485

.TP 3n

486

\(bu

487

\fB\-\-unbuffered\fR,

488

\fB\-n\fR

489

.sp

490

Flush the buffer after each query.

491

.TP 3n

492

\(bu

493

\fB\-\-user=\fR\fB\fIuser_name\fR\fR,

494

\fB\-u \fR\fB\fIuser_name\fR\fR

495

.sp

496

The Drizzle user name to use when connecting to the server.

497

.TP 3n

498

\(bu

499

\fB\-\-verbose\fR,

500

\fB\-v\fR

501

.sp

502

Verbose mode. Produce more output about what the program does. This option can be given multiple times to produce more and more output. (For example,

503

\fB\-v \-v \-v\fR

504

produces table output format even in batch mode.)

505

.TP 3n

506

\(bu

507

\fB\-\-version\fR,

508

\fB\-V\fR

509

.sp

510

Display version information and exit.

511

.TP 3n

512

\(bu

513

\fB\-\-vertical\fR,

514

\fB\-E\fR

515

.sp

516

Print query output rows vertically (one line per column value). Without this option, you can specify vertical output for individual statements by terminating them with

517

\\G.

518

.TP 3n

519

\(bu

520

\fB\-\-wait\fR,

521

\fB\-w\fR

522

.sp

523

If the connection cannot be established, wait and retry instead of aborting.

524

.TP 3n

525

.PP

526

You can also set the following variables by using

527

\fB\-\-\fR\fB\fIvar_name\fR\fR\fB=\fR\fB\fIvalue\fR\fR. The

528

\fB\-\-set\-variable\fR

529

format is deprecated.

530

.TP 3n

531

\(bu

532

connect_timeout

533

.sp

534

The number of seconds before connection timeout. (Default value is

535

0.)

536

.TP 3n

537

\(bu

538

max_allowed_packet

539

.sp

540

The maximum packet length to send to or receive from the server. (Default value is 16MB.)

541

.TP 3n

542

\(bu

543

max_join_size

544

.sp

545

The automatic limit for rows in a join when using

546

\fB\-\-safe\-updates\fR. (Default value is 1,000,000.)

547

.TP 3n

548

\(bu

549

net_buffer_length

550

.sp

551

The buffer size for TCP/IP and socket communication. (Default value is 16KB.)

552

.TP 3n

553

\(bu

554

select_limit

555

.sp

556

The automatic limit for

557

SELECT

558

statements when using

559

\fB\-\-safe\-updates\fR. (Default value is 1,000.)

560

.sp

561

.RE

562

.PP

563

On Unix, the

564

\fBmysql\fR

565

client writes a record of executed statements to a history file. By default, this file is named

566

\fI.mysql_history\fR

567

and is created in your home directory. To specify a different file, set the value of the

568

MYSQL_HISTFILE

569

environment variable.

570

.PP

571

The

572

\fI.mysql_history\fR

573

should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords. See

574

Section\ 5.5.6.2, \(lqEnd\-User Guidelines for Password Security\(rq.

575

.PP

576

If you do not want to maintain a history file, first remove

577

\fI.mysql_history\fR

578

if it exists, and then use either of the following techniques:

579

.TP 3n

580

\(bu

581

Set the

582

MYSQL_HISTFILE

583

variable to

584

\fI/dev/null\fR. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files.

585

.TP 3n

586

\(bu

587

Create

588

\fI.mysql_history\fR

589

as a symbolic link to

590

\fI/dev/null\fR:

591

.sp

592

.RS 3n

593

.nf

594

shell> \fBln \-s /dev/null $HOME/.mysql_history\fR

595

.fi

596

.RE

597

You need do this only once.

598

.SH "\fBMYSQL\fR COMMANDS"

599

.PP

600

\fBmysql\fR

601

sends each SQL statement that you issue to the server to be executed. There is also a set of commands that

602

\fBmysql\fR

603

itself interprets. For a list of these commands, type

604

help

605

606

\\h

607

at the

608

mysql>

609

prompt:

610

.sp

611

.RS 3n

612

.nf

613

mysql> \fBhelp\fR

614

List of all Drizzle commands:

615

Note that all text commands must be first on line and end with ';'

616

? (\\?) Synonym for `help'.

617

clear (\\c) Clear command.

618

connect (\\r) Reconnect to the server. Optional arguments are db and host.

619

delimiter (\\d) Set statement delimiter.

620

edit (\\e) Edit command with $EDITOR.

621

ego (\\G) Send command to mysql server, display result vertically.

622

exit (\\q) Exit mysql. Same as quit.

623

go (\\g) Send command to mysql server.

624

help (\\h) Display this help.

625

nopager (\\n) Disable pager, print to stdout.

626

notee (\\t) Don't write into outfile.

627

pager (\\P) Set PAGER [to_pager]. Print the query results via PAGER.

628

print (\\p) Print current command.

629

prompt (\\R) Change your mysql prompt.

630

quit (\\q) Quit mysql.

631

rehash (\\#) Rebuild completion hash.

632

source (\\.) Execute an SQL script file. Takes a file name as an argument.

633

status (\\s) Get status information from the server.

634

system (\\!) Execute a system shell command.

635

tee (\\T) Set outfile [to_outfile]. Append everything into given

636

outfile.

637

use (\\u) Use another database. Takes database name as argument.

638

charset (\\C) Switch to another charset. Might be needed for processing

639

binlog with multi\-byte charsets.

640

warnings (\\W) Show warnings after every statement.

641

nowarning (\\w) Don't show warnings after every statement.

642

For server side help, type 'help contents'

643

.fi

644

.RE

645

.PP

646

Each command has both a long and short form. The long form is not case sensitive; the short form is. The long form can be followed by an optional semicolon terminator, but the short form should not.

647

.PP

648

The use of short\-form commands within multi\-line

649

/* ... */

650

comments is not supported.

651

.TP 3n

652

\(bu

653

\fBhelp [\fR\fB\fIarg\fR\fR\fB]\fR,

654

\fB\\h [\fR\fB\fIarg\fR\fR\fB]\fR,

655

\fB\\? [\fR\fB\fIarg\fR\fR\fB]\fR,

656

\fB? [\fR\fB\fIarg\fR\fR\fB]\fR

657

.sp

658

Displays a help message listing the available

659

\fBmysql\fR

660

commands.

661

.sp

662

If you provide an argument to the

663

help

664

command,

665

\fBmysql\fR

666

uses it as a search string to access server\-side help from the contents of the Drizzle Reference Manual. For more information, see

667

the section called \(lq\fBMYSQL\fR SERVER\-SIDE HELP\(rq.

668

.TP 3n

669

\(bu

670

\fBcharset \fR\fB\fIcharset_name\fR\fR,

671

\fB\\C \fR\fB\fIcharset_name\fR\fR

672

.sp

673

The

674

charset

675

command changes the default character set and issues a

676

SET NAMES

677

statement. This enables the character set to remain synchronized on the client and server if

678

\fBmysql\fR

679

is run with auto\-reconnect enabled (which is not recommended), because the specified character set is used for reconnects.

680

.TP 3n

681

\(bu

682

\fBclear\fR,

683

\fB\\c\fR

684

.sp

685

Clears the current input. Use this if you change your mind about executing the statement that you are entering.

686

.TP 3n

687

\(bu

688

\fBconnect [\fR\fB\fIdb_name\fR\fR\fB \fR\fB\fIhost_name\fR\fR\fB]]\fR,

689

\fB\\r [\fR\fB\fIdb_name\fR\fR\fB \fR\fB\fIhost_name\fR\fR\fB]]\fR

690

.sp

691

Reconnects to the server. The optional database name and host name arguments may be given to specify the default database or the host where the server is running. If omitted, the current values are used.

692

.TP 3n

693

\(bu

694

\fBdelimiter \fR\fB\fIstr\fR\fR,

695

\fB\\d \fR\fB\fIstr\fR\fR

696

.sp

697

The

698

delimiter

699

command changes the string that

700

\fBmysql\fR

701

interprets as the separator between SQL statements. The default is the semicolon character (\(lq;\(rq).

702

.sp

703

The delimiter can be specified as an unquoted or quoted argument. Quoting can be done with either single quote (') or douple quote (") characters. To include a quote within a quoted string, either quote the string with the other quote character or escape the quote with a backslash (\(lq\\\(rq) character. Backslash should be avoided outside of quoted strings because it is the escape character for Drizzle. For an unquoted argument, the delmiter is read up to the first space or end of line. For a quoted argument, the delimiter is read up to the matching quote on the line.

704

.sp

705

When the delimiter recognized by

706

\fBmysql\fR

707

is set to something other than the default of

708

\(lq;\(rq, instances of that character are sent to the server without interpretation. However, the server itself still interprets

709

\(lq;\(rq

710

as a statement delimiter and processes statements accordingly. This behavior on the server side comes into play for multiple\-statement execution (see

711

Section\ 20.10.12, \(lqC API Support for Multiple Statement Execution\(rq), and for parsing the body of stored procedures and functions, triggers, and events (see

712

Section\ 18.1, \(lqDefining Stored Programs\(rq).

713

.TP 3n

714

\(bu

715

\fBedit\fR,

716

\fB\\e\fR

717

.sp

718

Edits the current input statement.

719

\fBmysql\fR

720

checks the values of the

721

EDITOR

722

and

723

VISUAL

724

environment variables to determine which editor to use. The default editor is

725

\fBvi\fR

726

if neither variable is set.

727

.sp

728

The

729

\fBedit\fR

730

command works only in Unix.

731

.TP 3n

732

\(bu

733

\fBego\fR,

734

\fB\\G\fR

735

.sp

736

Sends the current statement to the server to be executed and displays the result using vertical format.

737

.TP 3n

738

\(bu

739

\fBexit\fR,

740

\fB\\q\fR

741

.sp

742

Exits

743

\fBmysql\fR.

744

.TP 3n

745

\(bu

746

\fBgo\fR,

747

\fB\\g\fR

748

.sp

749

Sends the current statement to the server to be executed.

750

.TP 3n

751

\(bu

752

\fBnopager\fR,

753

\fB\\n\fR

754

.sp

755

Disables output paging. See the description for

756

\fBpager\fR.

757

.sp

758

The

759

\fBnopager\fR

760

command works only in Unix.

761

.TP 3n

762

\(bu

763

\fBnotee\fR,

764

\fB\\t\fR

765

.sp

766

Disables output copying to the tee file. See the description for

767

\fBtee\fR.

768

.TP 3n

769

\(bu

770

\fBnowarning\fR,

771

\fB\\w\fR

772

.sp

773

Enables display of warnings after each statement.

774

.TP 3n

775

\(bu

776

\fBpager [\fR\fB\fIcommand\fR\fR\fB]\fR,

777

\fB\\P [\fR\fB\fIcommand\fR\fR\fB]\fR

778

.sp

779

By using the

780

\fB\-\-pager\fR

781

option when you invoke

782

\fBmysql\fR, it is possible to browse or search query results in interactive mode with Unix programs such as

783

\fBless\fR,

784

\fBmore\fR, or any other similar program. If you specify no value for the option,

785

\fBmysql\fR

786

checks the value of the

787

PAGER

788

environment variable and sets the pager to that.

789

.sp

790

Output paging can be enabled interactively with the

791

\fBpager\fR

792

command and disabled with

793

\fBnopager\fR. The command takes an optional argument; if given, the paging program is set to that. With no argument, the pager is set to the pager that was set on the command line, or

794

stdout

795

if no pager was specified.

796

.sp

797

Output paging works only in Unix because it uses the

798

popen()

799

function, which does not exist on Windows. For Windows, the

800

\fBtee\fR

801

option can be used instead to save query output, although it is not as convenient as

802

\fBpager\fR

803

for browsing output in some situations.

804

.TP 3n

805

\(bu

806

\fBprint\fR,

807

\fB\\p\fR

808

.sp

809

Prints the current input statement without executing it.

810

.TP 3n

811

\(bu

812

\fBprompt [\fR\fB\fIstr\fR\fR\fB]\fR,

813

\fB\\R [\fR\fB\fIstr\fR\fR\fB]\fR

814

.sp

815

Reconfigures the

816

\fBmysql\fR

817

prompt to the given string. The special character sequences that can be used in the prompt are described later in this section.

818

.sp

819

If you specify the

820

prompt

821

command with no argument,

822

\fBmysql\fR

823

resets the prompt to the default of

824

mysql>.

825

.TP 3n

826

\(bu

827

\fBquit\fR,

828

\fB\\q\fR

829

.sp

830

Exits

831

\fBmysql\fR.

832

.TP 3n

833

\(bu

834

\fBrehash\fR,

835

\fB\\#\fR

836

.sp

837

Rebuilds the completion hash that enables database, table, and column name completion while you are entering statements. (See the description for the

838

\fB\-\-auto\-rehash\fR

839

option.)

840

.TP 3n

841

\(bu

842

\fBsource \fR\fB\fIfile_name\fR\fR,

843

\fB\\. \fR\fB\fIfile_name\fR\fR

844

.sp

845

Reads the named file and executes the statements contained therein. On Windows, you can specify path name separators as

846

847

848

\\\\.

849

.TP 3n

850

\(bu

851

\fBstatus\fR,

852

\fB\\s\fR

853

.sp

854

The

855

status

856

command provides some information about the connection and the server you are using. If you are running in

857

\fB\-\-safe\-updates\fR

858

mode,

859

status

860

also prints the values for the

861

\fBmysql\fR

862

variables that affect your queries.

863

.TP 3n

864

\(bu

865

\fBsystem \fR\fB\fIcommand\fR\fR,

866

\fB\\! \fR\fB\fIcommand\fR\fR

867

.sp

868

Executes the given command using your default command interpreter.

869

.sp

870

The

871

\fBsystem\fR

872

command works only in Unix.

873

.TP 3n

874

\(bu

875

\fBtee [\fR\fB\fIfile_name\fR\fR\fB]\fR,

876

\fB\\T [\fR\fB\fIfile_name\fR\fR\fB]\fR

877

.sp

878

By using the

879

\fB\-\-tee\fR

880

option when you invoke

881

\fBmysql\fR, you can log statements and their output. All the data displayed on the screen is appended into a given file. This can be very useful for debugging purposes also.

882

\fBmysql\fR

883

flushes results to the file after each statement, just before it prints its next prompt.

884

.sp

885

You can enable this feature interactively with the

886

\fBtee\fR

887

command. Without a parameter, the previous file is used. The

888

\fBtee\fR

889

file can be disabled with the

890

\fBnotee\fR

891

command. Executing

892

\fBtee\fR

893

again re\-enables logging.

894

.TP 3n

895

\(bu

896

\fBuse \fR\fB\fIdb_name\fR\fR,

897

\fB\\u \fR\fB\fIdb_name\fR\fR

898

.sp

899

Uses

900

\fIdb_name\fR

901

as the default database.

902

.TP 3n

903

\(bu

904

\fBwarnings\fR,

905

\fB\\W\fR

906

.sp

907

Enables display of warnings after each statement (if there are any).

908

.sp

909

.RE

910

.PP

911

Here are a few tips about the

912

\fBpager\fR

913

command:

914

.TP 3n

915

\(bu

916

You can use it to write to a file and the results go only to the file:

917

.sp

918

.RS 3n

919

.nf

920

mysql> \fBpager cat > /tmp/log.txt\fR

921

.fi

922

.RE

923

You can also pass any options for the program that you want to use as your pager:

924

.sp

925

.RS 3n

926

.nf

927

mysql> \fBpager less \-n \-i \-S\fR

928

.fi

929

.RE

930

.TP 3n

931

\(bu

932

In the preceding example, note the

933

\fB\-S\fR

934

option. You may find it very useful for browsing wide query results. Sometimes a very wide result set is difficult to read on the screen. The

935

\fB\-S\fR

936

option to

937

\fBless\fR

938

can make the result set much more readable because you can scroll it horizontally using the left\-arrow and right\-arrow keys. You can also use

939

\fB\-S\fR

940

interactively within

941

\fBless\fR

942

to switch the horizontal\-browse mode on and off. For more information, read the

943

\fBless\fR

944

manual page:

945

.sp

946

.RS 3n

947

.nf

948

shell> \fBman less\fR

949

.fi

950

.RE

951

.TP 3n

952

\(bu

953

The

954

\fB\-F\fR

955

and

956

\fB\-X\fR

957

options may be used with

958

\fBless\fR

959

to cause it to exit if output fits on one screen, which is convenient when no scrolling is necessary:

960

.sp

961

.RS 3n

962

.nf

963

mysql> \fBpager less \-n \-i \-S \-F \-X\fR

964

.fi

965

.RE

966

.TP 3n

967

\(bu

968

You can specify very complex pager commands for handling query output:

969

.sp

970

.RS 3n

971

.nf

972

mysql> \fBpager cat | tee /dr1/tmp/res.txt \\\fR

973

\fB| tee /dr2/tmp/res2.txt | less \-n \-i \-S\fR

974

.fi

975

.RE

976

In this example, the command would send query results to two files in two different directories on two different file systems mounted on

977

\fI/dr1\fR

978

and

979

\fI/dr2\fR, yet still display the results onscreen via

980

\fBless\fR.

981

.sp

982

.RE

983

.PP

984

You can also combine the

985

\fBtee\fR

986

and

987

\fBpager\fR

988

functions. Have a

989

\fBtee\fR

990

file enabled and

991

\fBpager\fR

992

set to

993

\fBless\fR, and you are able to browse the results using the

994

\fBless\fR

995

program and still have everything appended into a file the same time. The difference between the Unix

996

\fBtee\fR

997

used with the

998

\fBpager\fR

999

command and the

1000

\fBmysql\fR

1001

built\-in

1002

\fBtee\fR

1003

command is that the built\-in

1004

\fBtee\fR

1005

works even if you do not have the Unix

1006

\fBtee\fR

1007

available. The built\-in

1008

\fBtee\fR

1009

also logs everything that is printed on the screen, whereas the Unix

1010

\fBtee\fR

1011

used with

1012

\fBpager\fR

1013

does not log quite that much. Additionally,

1014

\fBtee\fR

1015

file logging can be turned on and off interactively from within

1016

\fBmysql\fR. This is useful when you want to log some queries to a file, but not others.

1017

.PP

1018

The

1019

\fBprompt\fR

1020

command reconfigures the default

1021

mysql>

1022

prompt. The string for defining the prompt can contain the following special sequences.

1023

.TS

1024

allbox tab(:);

1025

l l

1026

l l

1027

l l

1028

l l

1029

l l

1030

l l

1031

l l

1032

l l

1033

l l

1034

l l

1035

l l

1036

l l

1037

l l

1038

l l

1039

l l

1040

l l

1041

l l

1042

l l

1043

l l

1044

l l

1045

l l

1046

l l

1047

l l

1048

l l

1049

l l

1050

l l

1051

l l

1052

l l

1053

l l.

1054

1055

\fBOption\fR

1056

T}:T{

1057

\fBDescription\fR

1058

1059

1060

\\o

1061

T}:T{

1062

The current month in numeric format

1063

1064

1065

\\P

1066

T}:T{

1067

am/pm

1068

1069

1070

\\p

1071

T}:T{

1072

The current TCP/IP port or socket file

1073

1074

1075

\\R

1076

T}:T{

1077

The current time, in 24\-hour military time (0\-23)

1078

1079

1080

\\r

1081

T}:T{

1082

The current time, standard 12\-hour time (1\-12)

1083

1084

1085

\\S

1086

T}:T{

1087

Semicolon

1088

1089

1090

\\s

1091

T}:T{

1092

Seconds of the current time

1093

1094

1095

\\t

1096

T}:T{

1097

A tab character

1098

1099

1100

\\U

1101

T}:T{

1102

Your full

1103

\fIuser_name\fR@\fIhost_name\fR

1104

account name

1105

1106

1107

\\u

1108

T}:T{

1109

Your user name

1110

1111

1112

\\c

1113

T}:T{

1114

A counter that increments for each statement you issue

1115

1116

1117

\\v

1118

T}:T{

1119

The server version

1120

1121

1122

\\w

1123

T}:T{

1124

The current day of the week in three\-letter format (Mon, Tue, \&...)

1125

1126

1127

\\Y

1128

T}:T{

1129

The current year, four digits

1130

1131

1132

\\y

1133

T}:T{

1134

The current year, two digits

1135

1136

1137

\\_

1138

T}:T{

1139

A space

1140

1141

1142

\\\

1143

T}:T{

1144

A space (a space follows the backslash)

1145

1146

1147

\\'

1148

T}:T{

1149

Single quote

1150

1151

1152

\\"

1153

T}:T{

1154

Double quote

1155

1156

1157

\\\\

1158

T}:T{

1159

A literal \(lq\\\(rq backslash character

1160

1161

1162

\\\fIx\fR

1163

T}:T{

1164

\fIx\fR, for any

1165

\(lq\fIx\fR\(rq not listed

1166

above

1167

1168

1169

\\D

1170

T}:T{

1171

The full current date

1172

1173

1174

\\d

1175

T}:T{

1176

The default database

1177

1178

1179

\\h

1180

T}:T{

1181

The server host

1182

1183

1184

\\l

1185

T}:T{

1186

The current delimiter

1187

1188

1189

\\m

1190

T}:T{

1191

Minutes of the current time

1192

1193

1194

\\n

1195

T}:T{

1196

A newline character

1197

1198

1199

\\O

1200

T}:T{

1201

The current month in three\-letter format (Jan, Feb, \&...)

1202

1203

.TE

1204

.sp

1205

.PP

1206

You can set the prompt in several ways:

1207

.TP 3n

1208

\(bu

1209

\fIUse an environment variable.\fR

1210

You can set the

1211

MYSQL_PS1

1212

environment variable to a prompt string. For example:

1213

.sp

1214

.RS 3n

1215

.nf

1216

shell> \fBexport MYSQL_PS1="(\\u@\\h) [\\d]> "\fR

1217

.fi

1218

.RE

1219

.TP 3n

1220

\(bu

1221

\fIUse a command\-line option.\fR

1222

You can set the

1223

\fB\-\-prompt\fR

1224

option on the command line to

1225

\fBmysql\fR. For example:

1226

.sp

1227

.RS 3n

1228

.nf

1229

shell> \fBmysql \-\-prompt="(\\u@\\h) [\\d]> "\fR

1230

(user@host) [database]>

1231

.fi

1232

.RE

1233

.TP 3n

1234

\(bu

1235

\fIUse an option file.\fR

1236

You can set the

1237

prompt

1238

option in the

1239

[mysql]

1240

group of any Drizzle option file, such as

1241

\fI/etc/my.cnf\fR

1242

or the

1243

\fI.my.cnf\fR

1244

file in your home directory. For example:

1245

.sp

1246

.RS 3n

1247

.nf

1248

[mysql]

1249

prompt=(\\\\u@\\\\h) [\\\\d]>\\\\_

1250

.fi

1251

.RE

1252

In this example, note that the backslashes are doubled. If you set the prompt using the

1253

prompt

1254

option in an option file, it is advisable to double the backslashes when using the special prompt options. There is some overlap in the set of allowable prompt options and the set of special escape sequences that are recognized in option files. (These sequences are listed in

1255

Section\ 4.2.3.2, \(lqUsing Option Files\(rq.) The overlap may cause you problems if you use single backslashes. For example,

1256

\\s

1257

is interpreted as a space rather than as the current seconds value. The following example shows how to define a prompt within an option file to include the current time in

1258

HH:MM:SS>

1259

format:

1260

.sp

1261

.RS 3n

1262

.nf

1263

[mysql]

1264

prompt="\\\\r:\\\\m:\\\\s> "

1265

.fi

1266

.RE

1267

.TP 3n

1268

\(bu

1269

\fISet the prompt interactively.\fR

1270

You can change your prompt interactively by using the

1271

prompt

1272

(or

1273

\\R) command. For example:

1274

.sp

1275

.RS 3n

1276

.nf

1277

mysql> \fBprompt (\\u@\\h) [\\d]>\\_\fR

1278

PROMPT set to '(\\u@\\h) [\\d]>\\_'

1279

(\fIuser\fR@\fIhost\fR) [\fIdatabase\fR]>

1280

(\fIuser\fR@\fIhost\fR) [\fIdatabase\fR]> prompt

1281

Returning to default PROMPT of mysql>

1282

mysql>

1283

.fi

1284

.RE

1285

.SH "\fBMYSQL\fR SERVER\-SIDE HELP"

1286

.sp

1287

.RS 3n

1288

.nf

1289

mysql> \fBhelp \fR\fB\fIsearch_string\fR\fR

1290

.fi

1291

.RE

1292

.PP

1293

If you provide an argument to the

1294

help

1295

command,

1296

\fBmysql\fR

1297

uses it as a search string to access server\-side help from the contents of the Drizzle Reference Manual. The proper operation of this command requires that the help tables in the

1298

mysql

1299

database be initialized with help topic information (see

1300

Section\ 5.1.8, \(lqServer\-Side Help\(rq).

1301

.PP

1302

If there is no match for the search string, the search fails:

1303

.sp

1304

.RS 3n

1305

.nf

1306

mysql> \fBhelp me\fR

1307

Nothing found

1308

Please try to run 'help contents' for a list of all accessible topics

1309

.fi

1310

.RE

1311

.PP

1312

Use

1313

\fBhelp contents\fR

1314

to see a list of the help categories:

1315

.sp

1316

.RS 3n

1317

.nf

1318

mysql> \fBhelp contents\fR

1319

You asked for help about help category: "Contents"

1320

For more information, type 'help <item>', where <item> is one of the

1321

following categories:

1322

Account Management

1323

Administration

1324

Data Definition

1325

Data Manipulation

1326

Data Types

1327

Functions

1328

Functions and Modifiers for Use with GROUP BY

1329

Geographic Features

1330

Language Structure

1331

Plugins

1332

Storage Engines

1333

Stored Routines

1334

Table Maintenance

1335

Transactions

1336

Triggers

1337

.fi

1338

.RE

1339

.PP

1340

If the search string matches multiple items,

1341

\fBmysql\fR

1342

shows a list of matching topics:

1343

.sp

1344

.RS 3n

1345

.nf

1346

mysql> \fBhelp logs\fR

1347

Many help items for your request exist.

1348

To make a more specific request, please type 'help <item>',

1349

where <item> is one of the following topics:

1350

SHOW

1351

SHOW BINARY LOGS

1352

SHOW ENGINE

1353

SHOW LOGS

1354

.fi

1355

.RE

1356

.PP

1357

Use a topic as the search string to see the help entry for that topic:

1358

.sp

1359

.RS 3n

1360

.nf

1361

mysql> \fBhelp show binary logs\fR

1362

Name: 'SHOW BINARY LOGS'

1363

Description:

1364

Syntax:

1365

SHOW BINARY LOGS

1366

SHOW MASTER LOGS

1367

Lists the binary log files on the server. This statement is used as

1368

part of the procedure described in [purge\-binary\-logs], that shows how

1369

to determine which logs can be purged.

1370

mysql> SHOW BINARY LOGS;

1371

+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-+

1372

| Log_name | File_size |

1373

+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-+

1374

| binlog.000015 | 724935 |

1375

| binlog.000016 | 733481 |

1376

+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-+

1377

.fi

1378

.RE

1379

.SH "EXECUTING SQL STATEMENTS FROM A TEXT FILE"

1380

.PP

1381

The

1382

\fBmysql\fR

1383

client typically is used interactively, like this:

1384

.sp

1385

.RS 3n

1386

.nf

1387

shell> \fBmysql \fR\fB\fIdb_name\fR\fR

1388

.fi

1389

.RE

1390

.PP

1391

However, it is also possible to put your SQL statements in a file and then tell

1392

\fBmysql\fR

1393

to read its input from that file. To do so, create a text file

1394

\fItext_file\fR

1395

that contains the statements you wish to execute. Then invoke

1396

\fBmysql\fR

1397

as shown here:

1398

.sp

1399

.RS 3n

1400

.nf

1401

shell> \fBmysql \fR\fB\fIdb_name\fR\fR\fB < \fR\fB\fItext_file\fR\fR

1402

.fi

1403

.RE

1404

.PP

1405

If you place a

1406

USE \fIdb_name\fR

1407

statement as the first statement in the file, it is unnecessary to specify the database name on the command line:

1408

.sp

1409

.RS 3n

1410

.nf

1411

shell> \fBmysql < text_file\fR

1412

.fi

1413

.RE

1414

.PP

1415

If you are already running

1416

\fBmysql\fR, you can execute an SQL script file using the

1417

source

1418

command or

1419

\\.

1420

command:

1421

.sp

1422

.RS 3n

1423

.nf

1424

mysql> \fBsource \fR\fB\fIfile_name\fR\fR

1425

mysql> \fB\\. \fR\fB\fIfile_name\fR\fR

1426

.fi

1427

.RE

1428

.PP

1429

Sometimes you may want your script to display progress information to the user. For this you can insert statements like this:

1430

.sp

1431

.RS 3n

1432

.nf

1433

SELECT '<info_to_display>' AS ' ';

1434

.fi

1435

.RE

1436

.PP

1437

The statement shown outputs

1438

<info_to_display>.

1439

.PP

1440

As of Drizzle 6.0.4,

1441

\fBmysql\fR

1442

ignores Unicode byte order mark (BOM) characters at the beginning of input files. Previously, it read them and sent them to the server, resulting in a syntax error. Presence of a BOM does not cause

1443

\fBmysql\fR

1444

to change its default character set. To do that, invoke

1445

\fBmysql\fR

1446

with an option such as

1447

\fB\-\-default\-character\-set=utf8\fR.

1448

.PP

1449

For more information about batch mode, see

1450

Section\ 3.5, \(lqUsing \fBmysql\fR in Batch Mode\(rq.

1451

.SH "\fBMYSQL\fR TIPS"

1452

.PP

1453

This section describes some techniques that can help you use

1454

\fBmysql\fR

1455

more effectively.

1456

.SS "Displaying Query Results Vertically"

1457

.PP

1458

Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \\G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output:

1459

.sp

1460

.RS 3n

1461

.nf

1462

mysql> \fBSELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\\G\fR

1463

*************************** 1. row ***************************

1464

msg_nro: 3068

1465

date: 2000\-03\-01 23:29:50

1466

time_zone: +0200

1467

mail_from: Monty

1468

reply: monty@no.spam.com

1469

mail_to: "Thimble Smith" <tim@no.spam.com>

1470

sbj: UTF\-8

1471

txt: >>>>> "Thimble" == Thimble Smith writes:

1472

Thimble> Hi. I think this is a good idea. Is anyone familiar

1473

Thimble> with UTF\-8 or Unicode? Otherwise, I'll put this on my

1474

Thimble> TODO list and see what happens.

1475

Yes, please do that.

1476

Regards,

1477

Monty

1478

file: inbox\-jani\-1

1479

hash: 190402944

1480

1 row in set (0.09 sec)

1481

.fi

1482

.RE

1483

.\" end of SS subsection "Displaying Query Results Vertically"

1484

.SS "Using the \-\-safe\-updates Option"

1485

.PP

1486

For beginners, a useful startup option is

1487

\fB\-\-safe\-updates\fR

1488

(or

1489

\fB\-\-i\-am\-a\-dummy\fR, which has the same effect). It is helpful for cases when you might have issued a

1490

DELETE FROM \fItbl_name\fR

1491

statement but forgotten the

1492

WHERE

1493

clause. Normally, such a statement deletes all rows from the table. With

1494

\fB\-\-safe\-updates\fR, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

1495

.PP

1496

When you use the

1497

\fB\-\-safe\-updates\fR

1498

option,

1499

\fBmysql\fR

1500

issues the following statement when it connects to the Drizzle server:

1501

.sp

1502

.RS 3n

1503

.nf

1504

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

1505

.fi

1506

.RE

1507

.PP

1508

See

1509

Section\ 5.1.4, \(lqSession System Variables\(rq.

1510

.PP

1511

The

1512

SET

1513

statement has the following effects:

1514

.TP 3n

1515

\(bu

1516

You are not allowed to execute an

1517

UPDATE

1518

1519

DELETE

1520

statement unless you specify a key constraint in the

1521

WHERE

1522

clause or provide a

1523

LIMIT

1524

clause (or both). For example:

1525

.sp

1526

.RS 3n

1527

.nf

1528

UPDATE \fItbl_name\fR SET \fInot_key_column\fR=\fIval\fR WHERE \fIkey_column\fR=\fIval\fR;

1529

UPDATE \fItbl_name\fR SET \fInot_key_column\fR=\fIval\fR LIMIT 1;

1530

.fi

1531

.RE

1532

.TP 3n

1533

\(bu

1534

The server limits all large

1535

SELECT

1536

results to 1,000 rows unless the statement includes a

1537

LIMIT

1538

clause.

1539

.TP 3n

1540

\(bu

1541

The server aborts multiple\-table

1542

SELECT

1543

statements that probably need to examine more than 1,000,000 row combinations.

1544

.sp

1545

.RE

1546

.PP

1547

To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the

1548

\fB\-\-select_limit\fR

1549

and

1550

\fB\-\-max_join_size\fR

1551

options:

1552

.sp

1553

.RS 3n

1554

.nf

1555

shell> \fBmysql \-\-safe\-updates \-\-select_limit=500 \-\-max_join_size=10000\fR

1556

.fi

1557

.RE

1558

.\" end of SS subsection "Using the \-\-safe\-updates Option"

1559

.SS "Disabling mysql Auto\-Reconnect"

1560

.PP

1561

If the

1562

\fBmysql\fR

1563

client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even if

1564

\fBmysql\fR

1565

succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user\-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements without you knowing it:

1566

.sp

1567

.RS 3n

1568

.nf

1569

mysql> \fBSET @a=1;\fR

1570

Query OK, 0 rows affected (0.05 sec)

1571

mysql> \fBINSERT INTO t VALUES(@a);\fR

1572

ERROR 2006: Drizzle server has gone away

1573

No connection. Trying to reconnect...

1574

Connection id: 1

1575

Current database: test

1576

Query OK, 1 row affected (1.30 sec)

1577

mysql> \fBSELECT * FROM t;\fR

1578

+\-\-\-\-\-\-+

1579

| a |

1580

+\-\-\-\-\-\-+

1581

| NULL |

1582

+\-\-\-\-\-\-+

1583

1 row in set (0.05 sec)

1584

.fi

1585

.RE

1586

.PP

1587

The

1588

1589

user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have

1590

\fBmysql\fR

1591

terminate with an error if the connection has been lost, you can start the

1592

\fBmysql\fR

1593

client with the

1594

\fB\-\-skip\-reconnect\fR

1595

option.

1596

.PP

1597

For more information about auto\-reconnect and its effect on state information when a reconnection occurs, see

1598

Section\ 20.10.11, \(lqControlling Automatic Reconnection Behavior\(rq.

1599

.\" end of SS subsection "Disabling mysql Auto\-Reconnect"

1600

.SH "COPYRIGHT"

1601

.PP

1602

1603

.PP

1604

This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

1605

.PP

1606

This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

1607

.PP

1608

You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110\-1301 USA or see http://www.gnu.org/licenses/.

1609

.SH "REFERENCES"

1610

.TP 3

1611

1.\ Bug#25946

1612

\%http://bugs.mysql.com/25946

1613

.SH "SEE ALSO"

1614

For more information, please refer to the Drizzle Reference Manual,

1615

which may already be installed locally and which is also available

1616

online at http://dev.mysql.com/doc/.

1617

.SH AUTHOR

1618

Sun Microsystems, Inc. (http://www.mysql.com/).

Older »