Oracle 5.0, Reference Manual

Page: 301 / 3232

mysql
— The MySQL Command-Line Tool
281
For beginners, a useful startup option is
--safe-updates
[269] (or
--i-am-a-dummy
[269] ,
which has the same effect). It is helpful for cases when you might have issued a
DELETE FROM
tbl_name
statement but forgotten the
WHERE
clause. Normally, such a statement deletes all rows from
the table. With
--safe-updates
[269] , you can delete rows only by specifying the key values that
identify them. This helps prevent accidents.
When you use the
--safe-updates
[269] option,
mysql
issues the following statement when it
connects to the MySQL server:
SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
See Section 5.1.4, “Server System Variables” .
The
SET
statement has the following effects:
• You are not permitted to execute an
UPDATE
or
DELETE
statement unless you specify a key
constraint in the
WHERE
clause or provide a
LIMIT
clause (or both). For example:
UPDATE tbl_name SET not_key_column = val WHERE key_column = val ;
UPDATE tbl_name SET not_key_column = val LIMIT 1;
• The server limits all large
SELECT
results to 1,000 rows unless the statement includes a
LIMIT
clause.
• The server aborts multiple-table
SELECT
statements that probably need to examine more than
1,000,000 row combinations.
To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the
--
select_limit
and
--max_join_size
options:
shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
4.5.1.6.4. Disabling
mysql
Auto-Reconnect
If the
mysql
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
mysql
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:
mysql> SET @a=1;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: test
Query OK, 1 row affected (1.30 sec)
mysql> SELECT * FROM t;
+------+
| a |
+------+
| NULL |
+------+
1 row in set (0.05 sec)
The
@a
user variable has been lost with the connection, and after the reconnection it is undefined. If it
is important to have
mysql
terminate with an error if the connection has been lost, you can start the
mysql
client with the
--skip-reconnect
[269] option.