121. Why
do I see multiple processes for
mysqld?
When using
LinuxThreads, you should see a minimum of three mysqld
processes running. These are in fact threads. There is one thread for the
LinuxThreads manager, one thread to handle connections, and one thread to
handle alarms and signals.
122. Have there been there any improvements
in error reporting when foreign keys fail? Does MySQL now report which column
and reference failed?
The foreign key
support in
InnoDB has seen
improvements in each major version of MySQL. Foreign key support generic to all
storage engines is scheduled for MySQL 6.x; this should resolve any
inadequacies in the current storage engine specific implementation.
Yes. All current
MySQL versions support transactions. The
InnoDB
storage engine offers full ACID transactions with row-level locking,
multi-versioning, nonlocking repeatable reads, and all four SQL standard
isolation levels.
124. Are
there any new storage engines in MySQL 5.7?
The features
from the optional
InnoDB Plugin
from MySQL 5.1 are folded into the built-in InnoDB
storage engine, so you can take advantage of features such as the Barracuda
file format, InnoDB table
compression, and the new configuration options for performance. InnoDB also becomes the default storage
engine for new tables.
125.
Have any storage engines been removed in MySQL 5.7?
No.
The
ARCHIVE storage engine is ideally suited
for storing large amounts of data without indexes; it has a very small
footprint, and performs selects using table scans.
127. Do
the new features in MySQL 5.7 apply to all storage engines?
The general new
features such as views, stored procedures, triggers,
INFORMATION_SCHEMA, precision math (DECIMAL
column type), and the BIT
column type, apply to all storage engines. There are also additions and changes
for specific storage engines.
128.
What are server SQL modes?
Server SQL modes
define what SQL syntax MySQL should support and what kind of data validation
checks it should perform. This makes it easier to use MySQL in different
environments and to use MySQL together with other database servers. The MySQL
Server apply these modes individually to different clients.
129. How
many server SQL modes are there?
130. How
do you determine the server SQL mode?
You can set the
default SQL mode (for mysqld
startup) with the
--sql-mode
option. Using the statement SET [GLOBAL|SESSION] sql_mode='modes',
you can change the settings from within a connection, either locally to the
connection, or to take effect globally. You can retrieve the current mode by
issuing a SELECT @@sql_mode
statement.
A mode is not
linked to a particular database. Modes can be set locally to the session
(connection), or globally for the server. you can change these settings using
SET [GLOBAL|SESSION] sql_mode='modes'.
When we refer to
strict mode, we mean a mode where at least one of the modes
TRADITIONAL,
STRICT_TRANS_TABLES,
or STRICT_ALL_TABLES
is enabled. Options can be combined, so you can add restrictions to a mode.
The intensive
validation of input data that some settings requires more time than if the
validation is not done. While the performance impact is not that great, if you
do not require such validation (perhaps your application already handles all of
this), then MySQL gives you the option of leaving strict mode disabled.
However—if you do require it—strict mode can provide such validation.
The default SQL
mode is
NO_ENGINE_SUBSTITUTION.
135.
Does MySQL 5.7 support stored procedures and functions?
Yes. MySQL 5.7
supports two types of stored routines—stored procedures and stored functions.
It is always
good practice to use a clear naming scheme for your stored routines. You can
manage stored procedures with
CREATE
[FUNCTION|PROCEDURE], ALTER
[FUNCTION|PROCEDURE], DROP
[FUNCTION|PROCEDURE], and SHOW
CREATE [FUNCTION|PROCEDURE]. You can obtain information about existing
stored procedures using the ROUTINES
table in the INFORMATION_SCHEMA
database
137. Is
there a way to view all stored procedures and stored functions in a given
database?
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='dbname';
The body of a
stored routine can be viewed using
SHOW CREATE
FUNCTION (for a stored function) or SHOW
CREATE PROCEDURE (for a stored procedure).
138.
Where are stored procedures stored?
In the
proc table of the mysql system database. However, you
should not access the tables in the system database directly. Instead, use SHOW CREATE
FUNCTION to obtain information about stored functions, and SHOW
CREATE PROCEDURE to obtain information about stored
procedures
139. Is
it possible to group stored procedures or stored functions into packages?
No. This is not
supported in MySQL 5.7.
140. Can
a stored procedure call another stored procedure?
Yes.
141. Can
a stored procedure call a trigger?
A stored
procedure can execute an SQL statement, such as an
UPDATE,
that causes a trigger to activate.
Yes. A stored
procedure can access one or more tables as required.
143. Do
stored procedures have a statement for raising application errors?
144. Do
stored procedures provide exception handling?
145. Can
MySQL 5.7 stored routines return result sets?
Stored
procedures can, but stored functions cannot. If you perform an ordinary
SELECT
inside a stored procedure, the result set is returned directly to the client.
You need to use the MySQL 4.1 (or above) client/server protocol for this to
work. This means that—for instance—in PHP, you need to use the mysqli extension rather than the old mysql extension.
Not in MySQL
5.7.
147. Is there a MySQL equivalent to using
mod_plsql as a gateway on
Apache to talk directly to a stored procedure in the database?
There is no
equivalent in MySQL 5.7.
Not in MySQL
5.7.
149. Can
I pass a cursor as an
IN
parameter to a stored procedure?
In MySQL 5.7,
cursors are available inside stored procedures only.
150. Can
I return a cursor as an
OUT
parameter from a stored procedure?
In MySQL 5.7,
cursors are available inside stored procedures only. However, if you do not
open a cursor on a
SELECT,
the result will be sent directly to the client. You can also SELECT INTO variables.
Yes, you can do
this in a stored procedure, but not in a stored function. If you
perform an ordinary
SELECT
inside a stored procedure, the result set is returned directly to the client.
You will need to use the MySQL 4.1 (or above) client/server protocol for this
to work. This means that—for instance—in PHP, you need to use the mysqli extension rather than the old mysql extension.
Yes. However,
you cannot perform transactional operations within a stored function.
153. Do
MySQL 5.7 stored procedures and functions work with replication?
Yes, standard
actions carried out in stored procedures and functions are replicated from a
master MySQL server to a slave server.
Yes, creation of
stored procedures and functions carried out through normal DDL statements on a
master server are replicated to a slave, so the objects will exist on both
servers.
ALTER and DROP statements for stored procedures
and functions are also replicated.
155. How
are actions that take place inside stored procedures and functions replicated?
MySQL records
each DML event that occurs in a stored procedure and replicates those
individual actions to a slave server. The actual calls made to execute stored
procedures are not replicated.
Stored functions
that change data are logged as function invocations, not as the DML events that
occur inside each function.
156. Are
there special security requirements for using stored procedures and functions
together with replication?
Yes. Because a
slave server has authority to execute any statement read from a master's binary
log, special security constraints exist for using stored functions with
replication. If replication or binary logging in general (for the purpose of
point-in-time recovery) is active, then MySQL DBAs have two security options
open to them:
2. Alternatively,
a DBA can set the
log_bin_trust_function_creators
system variable to 1, which enables anyone with the standard CREATE
ROUTINE privilege to create stored functions.
Nondeterministic
(random) or time-based actions embedded in stored procedures may not replicate
properly. By their very nature, randomly produced results are not predictable
and cannot be exactly reproduced, and therefore, random actions replicated to a
slave will not mirror those performed on a master. Note that declaring stored
functions to be
DETERMINISTIC
or setting the log_bin_trust_function_creators
system variable to 0 will not allow random-valued operations to be invoked.
In addition,
time-based actions cannot be reproduced on a slave because the timing of such
actions in a stored procedure is not reproducible through the binary log used
for replication. It records only DML events and does not factor in timing
constraints.
Finally,
nontransactional tables for which errors occur during large DML actions (such
as bulk inserts) may experience replication issues in that a master may be
partially updated from DML activity, but no updates are done to the slave
because of the errors that occurred. A workaround is for a function's DML
actions to be carried out with the
IGNORE
keyword so that updates on the master that cause errors are ignored and updates
that do not cause errors are replicated to the slave.
The same
limitations that affect replication do affect point-in-time recovery.
You can choose
either statement-based replication or row-based replication. The original
replication implementation is based on statement-based binary logging.
Row-based binary logging resolves the limitations mentioned earlier.
Mixed
replication is also available (by starting the server with
--binlog-format=mixed).
This hybrid, “smart” form of replication “knows” whether statement-level replication can safely be
used, or row-level replication is required.
In MySQL 5.7,
all triggers are
FOR EACH ROW—that
is, the trigger is activated for each row that is inserted, updated, or
deleted. MySQL 5.7 does not support triggers using FOR EACH STATEMENT.
Not explicitly.
MySQL does have specific special behavior for some
TIMESTAMP
columns, as well as for columns which are defined using AUTO_INCREMENT.
In MySQL 5.7,
triggers can be created using the
CREATE TRIGGER
statement, and dropped using DROP TRIGGER.
Information about triggers can be obtained by querying the INFORMATION_SCHEMA.TRIGGERS
table
163. Is
there a way to view all triggers in a given database?
Yes. You can
obtain a listing of all triggers defined on database
dbname using a query on the INFORMATION_SCHEMA.TRIGGERS
table such as the one shown here: SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='dbname';
164.
Where are triggers stored?
Triggers for a
table are currently stored in
.TRG
files, with one such file one per table.
Yes.
A trigger can
access both old and new data in its own table. A trigger can also affect other
tables, but it is not permitted to modify a table that is already being used
(for reading or writing) by the statement that invoked the function or trigger.
As of MySQL
5.7.2, it is possible to define multiple triggers for a given table that have
the same trigger event and action time. For example, you can have two
BEFORE UPDATE triggers for a table. By
default, triggers that have the same trigger event and action time activate in
the order they were created. To affect trigger order, specify a clause after FOR EACH ROW that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also
has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing
trigger. With PRECEDES, the
new trigger activates before the existing trigger.
Yes. For
example, a trigger could invoke the
sys_exec()
UDF.
170. Do
triggers work with replication?
Yes. However,
the way in which they work depends whether you are using MySQL's “classic” statement-based replication available in all
versions of MySQL, or the row-based replication format introduced in MySQL 5.1.
When using
statement-based replication, triggers on the slave are executed by statements
that are executed on the master (and replicated to the slave).
When using
row-based replication, triggers are not executed on the slave due to statements
that were run on the master and then replicated to the slave. Instead, when
using row-based replication, the changes caused by executing the trigger on the
master are applied on the slave.
171. How
are actions carried out through triggers on a master replicated to a slave?
Again, this
depends on whether you are using statement-based or row-based replication.
Statement-based
replication. First, the triggers that exist on a master must be
re-created on the slave server. Once this is done, the replication flow works
as any other standard DML statement that participates in replication. For
example, consider a table
EMP
that has an AFTER insert
trigger, which exists on a master MySQL server. The same EMP table and AFTER insert trigger exist on the slave
server as well. The replication flow would be:
2. The
AFTER trigger on EMP activates.
5. The
AFTER trigger on EMP that exists on the slave activates.
Row-based
replication. When you use row-based replication, the changes caused
by executing the trigger on the master are applied on the slave. However, the
triggers themselves are not actually executed on the slave under row-based
replication. This is because, if both the master and the slave applied the
changes from the master and—in addition—the trigger causing these changes were
applied on the slave, the changes would in effect be applied twice on the
slave, leading to different data on the master and the slave.
In most cases,
the outcome is the same for both row-based and statement-based replication.
However, if you use different triggers on the master and slave, you cannot use
row-based replication. (This is because the row-based format replicates the
changes made by triggers executing on the master to the slaves, rather than the
statements that caused the triggers to execute, and the corresponding triggers
on the slave are not executed.) Instead, any statements causing such triggers
to be executed must be replicated using statement-based replication.
After a view has
been created, it is possible to drop or alter a table or view to which the
definition refers. To check a view definition for problems of this kind, use
the
CHECK TABLE
statement.
173.
Does MySQL 5.7 have table snapshots?
No.
No.
It is possible,
provided that your
INSERT
statement has a column list that makes it clear there is only one table
involved.
You cannot
insert into multiple tables with a single insert on a view.
176.
What is the difference between the Oracle Data Dictionary and MySQL's
INFORMATION_SCHEMA?
Both Oracle and
MySQL provide metadata in tables. However, Oracle and MySQL use different table
names and column names. MySQL's implementation is more similar to those found
in DB2 and SQL Server, which also support
INFORMATION_SCHEMA
as defined in the SQL standard.
No. Since
applications may rely on a certain standard structure, this should not be
modified.
178.
Where can I find information on how to migrate from MySQL 5.6 to MySQL 5.7?
Do not skip a
major version when upgrading, but rather complete the process in steps,
upgrading from one major version to the next in each step. This may seem more
complicated, but it will you save time and trouble—if you encounter problems
during the upgrade, their origin will be easier to identify, either by you
or—if you have a MySQL Enterprise subscription—by MySQL support.
Storage engine
support has changed as follows:
·
Support for
ISAM
tables was removed in MySQL 5.0 and you should now use the MyISAM storage engine in place of ISAM. To convert a table tblname from ISAM to MyISAM, simply issue a statement such as this one: ALTER TABLEtblnameENGINE=MYISAM;
·
Internal
RAID
for MyISAM tables was also
removed in MySQL 5.0. This was formerly used to allow large tables in file
systems that did not support file sizes greater than 2GB. All modern file
systems allow for larger tables; in addition, there are now other solutions
such as MERGE tables and
views.
180.
Does MySQL 5.7 have native support for SSL?
Most 5.7
binaries have support for SSL connections between the client and server. You
can also tunnel a connection using SSH, if (for example) the client application
does not support SSL connections.
181. Is SSL support be built into MySQL
binaries, or must I recompile the binary myself to enable it?
Most 5.7
binaries have SSL enabled for client-server connections that are secured,
authenticated, or both
182.
Does MySQL 5.7 have built-in authentication against LDAP directories?
Not at this
time.
Not at this
time.
184.
Which versions of the MySQL software support Cluster? Do I have to compile from
source?
MySQL Cluster is
not supported in MySQL Server 5.7 releases. Instead, MySQL Cluster is released
as a separate product, available as MySQL Cluster NDB 7.2 and MySQL Cluster NDB
7.3. You should use MySQL Cluster NDB 7.3 for new deployments, and plan to upgrade
if you are using a previous version of MySQL Cluster. The development version
of MySQL Cluster is based on version 7.4 of the
NDB
storage engine and MySQL Server 5.6, and is currently available for testing and
evaluation.
185.
Must the slave be connected to the master all the time?
No, it does not.
The slave can go down or stay disconnected for hours or even days, and then
reconnect and catch up on updates. For example, you can set up a master/slave
relationship over a dial-up link where the link is up only sporadically and for
short periods of time. The implication of this is that, at any given time, the
slave is not guaranteed to be in synchrony with the master unless you take some
special measures.
To ensure that
catchup can occur for a slave that has been disconnected, you must not remove
binary log files from the master that contain information that has not yet been
replicated to the slaves. Asynchronous replication can work only if the slave
is able to continue reading the binary log from the point where it last read
events.
Yes, networking
must be enabled on the master and slave. If networking is not enabled, the
slave cannot connect to the master and transfer the binary log. Check that the
skip-networking
option has not been enabled in the configuration file for either server.
187. How do I know how late a slave is
compared to the master? In other words, how do I know the date of the last
statement replicated by the slave?
Check the
Seconds_Behind_Master column in the
output from SHOW SLAVE STATUS.When
the slave SQL thread executes an event read from the master, it modifies its
own time to the event timestamp. (This is why TIMESTAMP
is well replicated.) In the Time
column in the output of SHOW PROCESSLIST,
the number of seconds displayed for the slave SQL thread is the number of
seconds between the timestamp of the last replicated event and the real time of
the slave machine. You can use this to determine the date of the last
replicated event. Note that if your slave has been disconnected from the master
for one hour, and then reconnects, you may immediately see large Time values such as 3600 for the slave
SQL thread in SHOW PROCESSLIST.
This is because the slave is executing statements that are one hour old.
188. How
do I force the master to block updates until the slave catches up?
Use the
following procedure:
1. On
the master, execute these statements:
2. mysql>FLUSH TABLES WITH READ LOCK;
3. mysql>SHOW MASTER STATUS;
Record the replication coordinates
(the current binary log file name and position) from the output of the
SHOW statement.
4. On
the slave, issue the following statement, where the arguments to the
MASTER_POS_WAIT()
function are the replication coordinate values obtained in the previous step: 5. mysql>SELECT MASTER_POS_WAIT('log_name', log_pos);
The
SELECT
statement blocks until the slave reaches the specified log file and position.
At that point, the slave is in synchrony with the master and the statement
returns.
6. On
the master, issue the following statement to enable the master to begin
processing updates again:
7. mysql>UNLOCK TABLES;
189.
What issues should I be aware of when setting up two-way replication?
MySQL
replication currently does not support any locking protocol between master and
slave to guarantee the atomicity of a distributed (cross-server) update. In
other words, it is possible for client A to make an update to co-master 1, and
in the meantime, before it propagates to co-master 2, client B could make an
update to co-master 2 that makes the update of client A work differently than
it did on co-master 1. Thus, when the update of client A makes it to co-master
2, it produces tables that are different from what you have on co-master 1,
even after all the updates from co-master 2 have also propagated. This means
that you should not chain two servers together in a two-way replication
relationship unless you are sure that your updates can safely happen in any
order, or unless you take care of mis-ordered updates somehow in the client
code.
You should also
realize that two-way replication actually does not improve performance very
much (if at all) as far as updates are concerned. Each server must do the same
number of updates, just as you would have a single server do. The only
difference is that there is a little less lock contention because the updates
originating on another server are serialized in one slave thread. Even this
benefit might be offset by network delays.
Set up one
server as the master and direct all writes to it. Then configure as many slaves
as you have the budget and rackspace for, and distribute the reads among the master
and the slaves. You can also start the slaves with the
--skip-innodb,
--low-priority-updates,
and --delay-key-write=ALL
options to get speed improvements on the slave end. In this case, the slave
uses nontransactional MyISAM
tables instead of InnoDB
tables to get more speed by eliminating transactional overhead. 
No comments:
Post a Comment