Author: jstephens
Date: 2006-10-19 09:22:44 +0200 (Thu, 19 Oct 2006)
New Revision: 3675
Log:
Started correcting/marking up old 5.0 Tech FAQ
*This file does not currently validate*
Modified:
trunk/refman-5.0/old-faq.xml
Modified: trunk/refman-5.0/old-faq.xml
===================================================================
--- trunk/refman-5.0/old-faq.xml 2006-10-19 00:30:24 UTC (rev 3674)
+++ trunk/refman-5.0/old-faq.xml 2006-10-19 07:22:44 UTC (rev 3675)
Changed blocks: 4, Lines Added: 198, Lines Deleted: 86; 25314 bytes
@@ -9,7 +9,7 @@
<!ENTITY % versions.entities SYSTEM "versions.ent">
%versions.entities;
]>
-<section>
+<section id="faqs">
<title>MySQL 5.0 Technical FAQ</title>
<programlisting>
@@ -126,78 +126,205 @@
* Does MySQL 5.0 have built-in Authentication against LDAP directories?
* Does MySQL 5.0 have built-in Roles Based Access Control (RBAC)?
+ </programlisting>
-General
-GEN 1. When did MySQL 5.0 become Production/GA?
- MySQL 5.0 a Production release on 19th October 2005. We are now working on MySQL 5.1, which is in the beta stages.
-GEN 2. Can MySQL 5.0 do subqueries?
- Yes. MySQL supports subqueries since version 4.1 See http://dev.mysql.com/doc/mysql/en/subqueries.html
-GEN 3. Can MySQL 5.0 do multi-table inserts and updates?
- Yes. Multi-table UPDATE and DELETE were actually implemented in MySQL 4.0, with enhancements added in MySQL 4.1. See http://dev.mysql.com/doc/mysql/en/UPDATE.html and http://dev.mysql.com/doc/mysql/en/DELETE.html
-GEN 4. Does MySQL 5.0 have a Query Cache? Does it work on Server, Instance or Database?
- Yes. The Query Cache was introduced in MySQL 4.0, and operates on the server level. It caches complete result sets, matched with the original query string. If an exactly identical query is made (which often happens, particularly in web applications), no parsing or execution is necessary, the result is sent directly from the cache. Various tuning options are available. See http://dev.mysql.com/doc/mysql/en/query-cache.html
-GEN 5. Does MySQL 5.0 have Sequences?
- No. However, MySQL has an AUTO_INCREMENT system, which in MySQL 5.0 can also handle inserts in a multi-master replication setup. With the --auto-increment-increment and -auto-increment-offset startup options, you can set each server to generate auto-increment values that don't conflict with other servers. The auto-increment-increment should be >= the number of servers, and each server should have a unique offset.
-GEN 6. Does MySQL 5.0 have a NOW() with fractions of seconds?
- No. This is on the MySQL roadmap as a "rolling feature". This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling.
+<section id="faqs-general">
+ <title>MySQL 5.0 FAQ — General</title>
+<qandaset defaultlabel="qanda"> <qandaentry id="faqs-general-production">
+<question><para>When did MySQL 5.0 become Production/GA?</para></question>
+ <answer><para>MySQL 5.0 a Production release on 19th October 2005. We are now working on MySQL 5.1, which is in the beta stages.</para></answer></qandaentry>
+<qandaentry id="faqs-general-subqueries"><question><para>Can MySQL 5.0 do subqueries?</para></question>
+ <answer><para>Yes. MySQL supports subqueries since version 4.1 See
+ <xref linkend="subqueries"/>.</para></answer></qandaentry>
+<qandaentry id="faqs-general-multi-table"><question><para>Can MySQL 5.0 do multi-table inserts and updates?</para></question>
+ <answer><para>Yes. Multi-table <literal>UPDATE</literal> and
+ <literal>DELETE</literal> were actually implemented in MySQL 4.0,
+ with enhancements added in MySQL 4.1. See <xref
+ linkend="update"/>, and <xref linkend="delete"/>.</para></answer></qandaentry>
+<qandaentry id="faqs-general-query-cache"><question><para>Does MySQL 5.0 have a Query Cache? Does it work on Server, Instance or Database?</para></question>
+ <answer><para>Yes. The Query Cache was introduced in MySQL 4.0, and
+ operates on the server level. It caches complete result sets,
+ matched with the original query string. If an exactly identical
+ query is made (which often happens, particularly in web
+ applications), no parsing or execution is necessary, the result is
+ sent directly from the cache. Various tuning options are
+ available. See <xref linkend="query-cache"/>.</para></answer></qandaentry>
+<qandaentry id="faqs-general-sequences"><question><para>Does MySQL 5.0 have Sequences?</para></question>
+ <answer><para>No. However, MySQL has an
+ <literal>AUTO_INCREMENT</literal> system, which in MySQL 5.0 can
+ also handle inserts in a multi-master replication setup. With the
+ <option>--auto-increment-increment</option> and
+ <option>--auto-increment-offset</option> startup options, you can
+ set each server to generate auto-increment values that don't
+ conflict with other servers. The
+ <option>--auto-increment-increment</option> value should
+ be greater than the number of servers, and each server should have a unique offset.</para></answer></qandaentry>
+<qandaentry
+ id="faqs-general-now-fractional-seconds"><question><para>Does MySQL
+ 5.0 have a <literal>NOW()</literal> function with fractions of seconds?</para></question>
+ <answer><para>No. This is on the MySQL roadmap as a <quote>rolling feature</quote>. This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling.</para>
+ <para>However, MySQL does parse time strings with a fractional
+ component. See <xref linkend="time"/>.</para></answer></qandaentry>
+<qandaentry id="faqs-general-multi-cores"><question><para>Does MySQL 5.0
+ work with mutli-core processorsd?</para></question>
+<answer><para>Yes. MySQL is fully multi-threaded, and will make use of
+ multiple CPUs, provided that the operating system supports them.</para></answer></qandaentry>
+<qandaentry id="faqs-general-myisam-hot-backup"><question><para>Is there a hot
+ backup tool for MyISAM like InnoDB Hot Backup?</para></question>
+ <answer><para>This is currently under development for a future MySQL
+ release.</para></answer></qandaentry>
+<qandaentry id="faqs-general-fk-error-reporting"><question><para>Is there any improvement in error reporting when Foreign Key fails (i.e. currently does not report which column and reference failed)?</para></question>
+ <answer><para>The foreign key support in InnoDB has seen improvements in each major version of MySQL. However, global foreign key support (i.e., generically for all storage engines) is scheduled for MySQL 5.2 and should resolve any inadequacies in the current storage engine specific implementation.</para></answer></qandaentry>
+<qandaentry id="faqs-general-acid"><question><para>Can MySQL 5.0 perform ACID transactions?</para></question>
+ <answer><para>Yes. MySQL has supported transactions for years now, since 3.23-Max and all versions 4.0 and above. The InnoDB storage engine offers full ACID transactions with row-level locking, multi-versioning, non-locking repeatable reads, and all four SQL standard isolation levels.</para></answer></qandaentry></qandaset>
+</section>
- MySQL will however parse time strings with a fractional component. See http://dev.mysql.com/doc/mysql/en/time.html
-GEN 7. Does MySQL 5.0 work with Dual Core Opterons?
- Yes. MySQL is fully multi-threaded, and will make use of multiple CPUs provided the operating system supports it.
-GEN 8. Is there a "MyISAM Hot-Backup" like the "InnoDB Hot-Backup"?
- This is currently under development, and scheduled for MySQL 5.1.
-GEN 9. Is there any improvement in error reporting when Foreign Key fails (i.e. currently does not report which column and reference failed)?
- The foreign key support in InnoDB has seen improvements in each major version of MySQL. However, global foreign key support (i.e., generically for all storage engines) is scheduled for MySQL 5.2 and should resolve any inadequacies in the current storage engine specific implementation.
-GEN 10. Can MySQL 5.0 do ACID Transactions?
- Yes. MySQL has supported transactions for years now, since 3.23-Max and all versions 4.0 and above. The InnoDB storage engine offers full ACID transactions with row-level locking, multi-versioning, non-locking repeatable reads, and all four SQL standard isolation levels.
-Storage Engines
-ST 1. What are the new Storage Engines in MySQL 5.0?
- The FEDERATED storage engine, new in MySQL 5.0, allows the server to access tables in other (remote) servers. See http://dev.mysql.com/doc/mysql/en/federated-storage-engine.html
+<section id="faqs-storage-engines">
+<title>MySQL 5.0 FAQ — Storage Engines</title>
+<qandaset defaultlabel="qanda">
+ <qandaentry id="faqs-storage-engines-new-engines"><question><para>What are the new Storage Engines in MySQL 5.0?</para></question>
+ <answer><para>The <literal>FEDERATED</literal> storage engine, new
+ in MySQL 5.0, allows the server to access tables in other (remote)
+ servers. See <xref linkend="federated"/>.</para><para>
- MySQL 4.1 already added other new storage engines, such as ARCHIVE, CSV and NDB (Cluster).
-ST 2. What are the unique benefits of the ARCHIVE Storage Engine?
- The ARCHIVE Storage Engine, added in MySQL 4.1, is ideally suited for storing large amounts of data (without indexes) in a very small footprint, and selects using table scans. See http://dev.mysql.com/doc/mysql/en/archive-storage-engine.html for details.
-ST 3. Do the new features in MySQL 5.0 apply to all the 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.
-Server SQL Mode
-MODE 1. What is "Server SQL Mode"?
- The MySQL server can operate in different SQL modes, and (as of MySQL 4.1) can apply these modes separately for different clients. 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. For more information, see http://dev.mysql.com/doc/mysql/en/server-sql-mode.html
-MODE 2. How many modes are there in "Server SQL Mode"?
- Quite a few, as each mode can be individually switched on and off. See http://dev.mysql.com/doc/mysql/en/server-sql-mode.html for a full current list.
-MODE 3. How do you configure "Server SQL Mode"?
- You can set the default SQL mode (for mysqld startup) in the MySQL configuration with the --sql-mode option. With SET [SESSION|GLOBAL] sql_mode='modes' you can change the settings from within a connection; either local to the connection, or to take effect globally. You can retrieve the current mode by issuing a SELECT @@sql_mode statement.
-MODE 4. Is the mode dependent on the Database or the Connection?
- A mode is not 'linked' to a particular database. Modes can be set locally to the session (connection), or globally for the server With SET [SESSION|GLOBAL] sql_mode='modes' you can change the settings.
-MODE 5. Can "Strict Mode" rules be extended?
- When we refer to ``strict mode,'' we mean a mode where at least one of TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES is enabled. See also http://dev.mysql.com/doc/mysql/en/server-sql-mode.html Options can be combined, so you can indeed add additional restrictions to a mode.
-MODE 6. Does "Strict Mode" impact performance?
- Of course, a setting such as the requirement for detailed validation of input data requires some additional time. While the performance impact is not that great, if you do not require such validation (perhaps your application already handles all of this), MySQL gives you the option of leaving Strict Mode disabled. But if you do require it, Strict Mode delivers the validation you need.
-MODE 7. What is the default mode when MySQL 5.0 is installed?
- By default no special modes are enabled. See http://dev.mysql.com/doc/mysql/en/server-sql-mode.html for information about all available modes and the default behaviour in 5.0.
-Stored Procedures
-SP 1. Where can I find documentation for MySQL Stored Procedures?
- In the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/stored-procedures.html
-SP 2. Where is the discussion forum for MySQL Stored Procedures?
- http://forums.mysql.com/list.php?98
-SP 3. Where can I find the ANSI SQL 2003 specification for Stored Procedures?
- Unfortunately, the official specifications are not freely available, ANSI makes them available for purchase. However, there are books available, such as "SQL-99 Complete, Really" by Peter Gulutzan and Trudy Pelzer, which give a comprehensive overview of the standard including stored procedures.
-SP 4. How do you manage Stored Procedures?
- It is always good practice to use a clear naming scheme for your stored procedures. You can manage stored procedures with CREATE [FUNCTION|PROCEDURE], ALTER [FUNCTION|PROCEDURE], DROP [FUNCTION|PROCEDURE], and SHOW CREATE [FUNCTION|PROCEDURE], INFORMATION_SCHEMA (see http://dev.mysql.com/doc/mysql/en/routines-table.html).
-SP 5. Is there a way to view all Stored Procedures in a database?
- Yes, using the INFORMATION_SCHEMA.ROUTINES table: SELECT ROUTINE_TYPE,ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbname'; See also http://dev.mysql.com/doc/mysql/en/routines-table.html
+ MySQL 4.1 already added other new storage engines, such as ARCHIVE, CSV and NDB (Cluster).</para></answer></qandaentry>
+<qandaentry id="faqs-storage-engines-archive"><question><para>What are
+ the unique benefits of the <literal>ARCHIVE</literal> storage engine?</para></question>
+ <answer><para>The <literal>ARCHIVE</literal> Storage Engine, added
+ in MySQL 4.1, is ideally suited for storing large amounts of data
+ (without indexes) in a very small footprint, and selects using
+ table scans. See <xref linkend="archive-storage-engine"/>, for details.</para></answer></qandaentry>
+<qandaentry id="faqs-storage-engines-new-features"><question><para>Do
+ the new features in MySQL 5.0 apply to all storage engines?</para></question>
+ <answer><para>The general new features such as views, stored procedures, triggers, <literal>INFORMATION_SCHEMA</literal>, precision math (<literal>DECIMAL</literal> column type), and the <literal>BIT</literal> column type, apply to all storage engines. There are also additions and changes for specific storage engines.</para></answer></qandaentry></qandaset></section>
- The body of individual stored procedures can be seen through SHOW CREATE [FUNCTION|PROCEDURE] procname; See also http://dev.mysql.com/doc/mysql/en/show-create-procedure.html
-SP 6. Where are Stored Procedures stored?
- In the proc table in the mysql system database. However, you should not access the tables in the system database directly. Instead, use SHOW CREATE [FUNCTION|PROCEDURE] (see http://dev.mysql.com/doc/mysql/en/show-create-procedure.html) or INFORMATION_SCHEMA (see http://dev.mysql.com/doc/mysql/en/routines-table.html)
-SP 7. Is it possible to group SPs into a package (e.g. like Oracle)?
- No, not at present.
-SP 8. Can a SP call another SP?
- Yes.
-SP 9. Can a SP call a Trigger?
- A stored procedure could execute an SQL statement (such as an UPDATE) that causes a trigger to fire.
-SP 10. Can SPs access a single Table? Multiple Tables?
- Yes and Yes.
+ <section id="faqs-sql-modes">
+ <title>MySQL 5.0 FAQ — Server SQL Mode</title>
+ <qandaset defaultlabel="qanda">
+<qandaentry id="faqs-sql-modes-what"><question><para>What are server SQL modes?</para></question>
+ <answer><para>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. For more
+ information, see <xref linkend="server-sql-mode"/>.</para></answer></qandaentry>
+<qandaentry id="faq-sql-modes-how-many"><question><para>How many server SQL modes are there?</para></question>
+ <answer><para>Quite a few, as each mode can be individually switched
+ on and off. See <xref linkend="server-sql-mode"/>, for a complete
+ list of available modes.</para></answer></qandaentry>
+<qandaentry id="faqs-sql-modes-determining"><question><para>How do you determine the server SQL mode?</para></question>
+ <answer><para>You can set the default SQL mode (for
+ <command>mysqld</command> startup) with the
+ <option>--sql-mode</option> option. Using the statement
+ <literal>SET [SESSION|GLOBAL]
+ sql_mode='<replaceable>modes</replaceable>'</literal>, 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 <literal>SELECT @@sql_mode</literal> statement.</para></answer></qandaentry>
+<qandaentry id="faqs-sql-modes-scope">
+ <question><para>Is the mode dependent on the database or connection?</para></question>
+ <answer><para>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 <literal>SET [SESSION|GLOBAL] sql_mode='<replaceable>modes</replaceable>'</literal>.</para></answer></qandaentry>
+<qandaentry id="faqs-sql-modes-extending"><question><para>Can the rules for strict mode be extended?</para></question>
+ <answer><para>When we refer to <emphasis>strict mode</emphasis>, we
+ mean a mode where at least one of the modes
+ <literal>TRADITIONAL</literal>,
+ <literal>STRICT_TRANS_TABLES</literal>, or
+ <literal>STRICT_ALL_TABLES</literal> is enabled. Options can be
+ combined, so you can add additional restrictions to a mode. See
+ <xref linkend="server-sql-mode"/>, for more information.</para></answer></qandaentry>
+<qandaentry id="faqs-sql-modes-performance"><question><para>Does strict mode impact performance?</para></question>
+ <answer><para>Of course, a setting that requires intensive
+ validation of input data requires some additional time. 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.</para></answer></qandaentry>
+<qandaentry id="faqs-sql-modes-default">
+ <question><para>What is the default server SQL mode when MySQL 5.0 is installed?</para></question>
+ <answer><para>By default, no special modes are enabled. See <xref
+ linkend="server-sql-mode"/>, for information about all available modes and the default behaviour.</para></answer></qandaentry>
+ </qandaset>
+ </section>
+
+ <section id="faqs-stored-procedures">
+<title>MySQL 5.0 FAQ — Stored Procedures</title>
+<qandaset>
+ <qandaentry>
+ <question>
+ <para>Does MySQL 5.0 support stored procedures?</para>
+ </question>
+ <answer>
+ <para>Yes. MySQL 5.0 supports two types of stored routines —
+ stored procedures and stored functions.</para>
+ </answer>
+ </qandaentry>
+
+ <qandaentry><question><para>Where can I find documentation for MySQL
+ stored procedures and stored functions?</para></question>
+ <answer><para>See <xref linkend="stored-procedures"/>.</para></answer></qandaentry>
+<qandaentry><question><para>Is there a discussion forum for MySQL stored
+ procedures?</para></question>
+ <answer><para>Yes. See <ulink url="http://forums.mysql.com/list.php?98">http://forums.mysql.com/list.php?98</ulink>.</para></answer></qandaentry>
+<qandaentry><question><para>Where can I find the ANSI SQL 2003
+ specification for stored procedures?</para></question>
+ <answer><para>Unfortunately, the official specifications are not
+ freely available (ANSI makes them available for purchase).
+ However, there are books — such as <citetitle>SQL-99
+ Complete, Really</citetitle> by Peter Gulutzan and Trudy Pelzer
+ — which give a comprehensive overview of the standard,
+ including coverage of stored procedures.</para></answer></qandaentry>
+<qandaentry><question><para>How do you manage stored routines?</para></question>
+ <answer><para>It is always good practice to use a clear naming
+ scheme for your stored routines. You can manage stored
+ procedures with <literal>CREATE [FUNCTION|PROCEDURE]</literal>,
+ <literal>ALTER [FUNCTION|PROCEDURE]</literal>, <literal>DROP
+ [FUNCTION|PROCEDURE]</literal>, and <literal>SHOW CREATE
+ [FUNCTION|PROCEDURE]</literal>. You can obtain information
+ about existing stored procedures using the
+ <literal>ROUTINES</literal> table in the
+ <literal>INFORMATION_SCHEMA</literal> database (see
+ <xref linkend="routines-table"/>).</para></answer>.</qandaentry>
+<qandaentry><question><para>Is there a way to view all stored procedures
+ and stored functions in a given database?</para></question>
+ <answer><para>Yes. For a database named <replaceable>dbname</replaceable>, use this query on the
+ <literal>INFORMATION_SCHEMA.ROUTINES</literal> table:</para>
+<programlisting>
+SELECT ROUTINE_TYPE, ROUTINE_NAME
+ FROM INFORMATION_SCHEMA.ROUTINES
+ WHERE ROUTINE_SCHEMA='<replaceable>dbname</replaceable>';
+</programlisting>
+ <para>For more information, see <xref linkend="routine-table."/>.</para>
+ <para>The body of a stored routine can be viewed using
+ <literal>SHOW CREATE FUNCTION</literal> (for a stored function)
+ or <literal>SHOW CREATE PROCEDURE</literal> (for a stored
+ procedure). See <xref linkend="show-create-function"/>, and
+ <xref linkend="show-create-procedure"/>, for more information.</para></answer></qandaentry>
+
+
+<qandaentry><question><para>Where are stored procedures stored?</para></question>
+ <answer><para>In the <literal>proc</literal> table of the
+ <literal>mysql</literal> system database. However, you should not
+ access the tables in the system database directly. Instead, use
+ <literal>SHOW CREATE FUNCTION</literal> to obtain information
+ about stored functions, and <literal>SHOW CREATE
+ PROCEDURE</literal> to obtain information about stored
+ procedures. See <xref linkend="show-create-function"/>, and <xref
+ linkend="show-create-procedure"/>, for more information about
+ these statements. </para><para>You can also query the
+ <literal>ROUTINES</literal> table in the
+ <literal>INFORMATION_SCHEMA</literal> database — see
+ <xref linkend="routines-table"/>, for information about this table.</para></answer></qandaentry>
+<qandaentry><question><para>Is it possible to group stored procedures or stored functions into packages?</para></question>
+ <answer><para>No. This is not supported in MySQL 5.0.</para></answer></qandaentry>
+<qandaentry><question><para>Can a stored procedure call another stored procedure?</para></question>
+ <answer><para>Yes.</para></answer></qandaentry>
+<qandaentry><question><para>Can a stored procedure call a trigger?</para></question>
+ <answer><para>A stored procedure can execute an SQL statement, such
+ as an <literal>UPDATE</literal>, that causes a trigger to fire.</para></answer></qandaentry>
+<qandaentry><question><para>Can a stored procedure access tables?</para></question>
+ <answer><para>Yes. A stored procedure can access one or more tables as required.</para></answer></qandaentry>
SP 11. Do SPs have a "raise" statement to "raise application errors"?
Sorry, not at present. The SQL standard SIGNAL and RESIGNAL statements are on the TODO.
SP 12. Do SPs have Exception Handling?
@@ -217,7 +344,9 @@
SP 19. Can I print out a variable value in a SP for debugging?
Yes, if you do an ordinary SELECT inside a stored procedure or function, the result set is returned directly to the client. You will need to use the MySQL 4.1 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.
SP 20. Can I do a Transaction inside a SP (i.e. committing or rolling back a set of SQL statements in a batch)?
- No.
+ No.</qandaset>
+</section>
+
Triggers
TRG 1. Where can I find documentation for MySQL Triggers?
In the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/show-create-procedure.html
@@ -343,21 +472,4 @@
No. Support for external authentication methods is on the MySQL roadmap as a "rolling feature". This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling.
SEC 4. Does MySQL 5.0 have built-in Roles Based Access Control (RBAC)?
No. Roles are on the MySQL roadmap as a "rolling feature". This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling.
-
-
-
-Back to Top
-
-
-
-
-© 1995-2006 MySQL AB. All rights reserved.
-
- * Online Shop
- * Site Map
- * Legal
- * Contact Us
- * No Software Patents!
-
- </programlisting>
</section>
\ No newline at end of file
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r3675 - trunk/refman-5.0 | jon | 19 Oct |
