diff options
Diffstat (limited to 'Docs/manual.texi')
| -rw-r--r-- | Docs/manual.texi | 6377 |
1 files changed, 3229 insertions, 3148 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index bb158c60f97..8b5052aa19c 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -111,10 +111,7 @@ distribution for that version. @menu * Introduction:: General Information about @strong{MySQL} -* Questions:: @strong{MySQL} mailing lists and how to ask questions or report errors (bugs) -* Licensing and Support:: @strong{MySQL} licensing and support * Installing:: Installing @strong{MySQL} -* Compatibility:: How standards-compatible is @strong{MySQL}? * Privilege system:: The @strong{MySQL} access privilege system * Reference:: @strong{MySQL} language reference * Table types:: @strong{MySQL} table types @@ -134,7 +131,6 @@ distribution for that version. * Common problems:: Solving some common problems with @strong{MySQL} * Log files:: * Clients:: @strong{MySQL} client tools and APIs -* Comparisons:: How does @strong{MySQL} compare with other databases? * MySQL internals:: @strong{MySQL} internals * Environment variables:: @strong{MySQL} environment variables * Users:: Some @strong{MySQL} users @@ -142,8 +138,6 @@ distribution for that version. * Contrib:: Contributed programs * Credits:: Contributors to @strong{MySQL} * News:: @strong{MySQL} change history -* Bugs:: Known errors and design deficiencies in @strong{MySQL} -* TODO:: List of things we want to add to @strong{MySQL} in the future (The TODO) * Porting:: Comments on porting to other systems * Regexp:: Description of @strong{MySQL} regular expression syntax * Unireg:: What is Unireg? @@ -157,20 +151,31 @@ distribution for that version. General Information About MySQL +* MySQL and MySQL AB:: +* MySQL Information Sources:: +* Licensing and Support:: +* Compatibility:: +* Comparisons:: +* TODO:: + +MySQL, MySQL AB, and Open Source + * What-is:: What is @strong{MySQL}? -* What is MySQL AB:: +* What is MySQL AB:: What is @strong{MySQL AB}? * Manual-info:: About this manual +* Manual conventions:: Conventions used in this manual * History:: History of @strong{MySQL} -* MySQL-Books:: Books about MySQL * Features:: The main features of @strong{MySQL} * Stability:: How stable is @strong{MySQL}? +* Table size:: * Year 2000 compliance:: Year 2000 compliance -* General-SQL:: General SQL information and tutorials -* Useful Links:: Useful @strong{MySQL}-related links -About This Manual +MySQL Information Sources -* Manual conventions:: Conventions used in this manual +* MySQL-Books:: +* General-SQL:: +* Useful Links:: +* Questions:: MySQL Mailing Lists @@ -211,6 +216,61 @@ Types of Commercial Support * Telephone support:: Telephone support * Table handler support:: Support for other table handlers +How Standards-compatible Is MySQL? + +* Extensions to ANSI:: @strong{MySQL} extensions to ANSI SQL92 +* Differences from ANSI:: @strong{MySQL} differences compared to ANSI SQL92 +* ANSI mode:: Running @strong{MySQL} in ANSI mode +* Missing functions:: Functionality missing from @strong{MySQL} +* Standards:: What standards does @strong{MySQL} follow? +* Commit-rollback:: How to cope without @code{COMMIT}-@code{ROLLBACK} +* Bugs:: + +Functionality Missing from MySQL + +* Missing Sub-selects:: Sub-selects +* Missing SELECT INTO TABLE:: @code{SELECT INTO TABLE} +* Missing Transactions:: Transactions +* Missing Triggers:: Triggers +* Missing Foreign Keys:: Foreign Keys +* Broken Foreign KEY:: +* Missing Views:: Views +* Missing comments:: @samp{--} as the start of a comment + +Foreign Keys + +* Broken Foreign KEY:: Reasons NOT to use foreign keys constraints + +How MySQL Compares to Other Databases + +* Compare mSQL:: How @strong{MySQL} compares to @code{mSQL} +* Protocol differences:: +* Compare PostgreSQL:: How @strong{MySQL} compares with PostgreSQL +* MySQL-PostgreSQL features:: + +How MySQL Compares to @code{mSQL} + +* Using mSQL tools:: How to convert @code{mSQL} tools for @strong{MySQL} + +How @code{mSQL} and MySQL Client/Server Communications Protocols Differ + +* Syntax differences:: + +How MySQL Compares to PostgreSQL + +* MySQL-PostgreSQL goals:: MySQL and PostgreSQL development strategies + +Featurevise Comparison of MySQL and PostgreSQL + +* MySQL-PostgreSQL benchmarks:: + +MySQL and the future (The TODO) + +* TODO MySQL 4.0:: Things that should be in Version 4.0 +* TODO future:: Things that must be done in the near future +* TODO sometime:: Things that have to be done sometime +* TODO unplanned:: Some things we don't have any plans to do + Installing MySQL * Getting MySQL:: How to get @strong{MySQL} @@ -331,29 +391,6 @@ Upgrading/Downgrading MySQL * Upgrading-from-3.20:: Upgrading from a 3.20 version to 3.21 * Upgrading-to-arch:: Upgrading to another architecture -How Standards-compatible Is MySQL? - -* Extensions to ANSI:: @strong{MySQL} extensions to ANSI SQL92 -* ANSI mode:: Running @strong{MySQL} in ANSI mode -* Differences from ANSI:: @strong{MySQL} differences compared to ANSI SQL92 -* Missing functions:: Functionality missing from @strong{MySQL} -* Standards:: What standards does @strong{MySQL} follow? -* Commit-rollback:: How to cope without @code{COMMIT}-@code{ROLLBACK} - -Functionality Missing from MySQL - -* Missing Sub-selects:: Sub-selects -* Missing SELECT INTO TABLE:: @code{SELECT INTO TABLE} -* Missing Transactions:: Transactions -* Missing Triggers:: Triggers -* Missing Foreign Keys:: Foreign Keys -* Missing Views:: Views -* Missing comments:: @samp{--} as the start of a comment - -Foreign Keys - -* Broken Foreign KEY:: Reasons NOT to use foreign keys constraints - The MySQL Access Privilege System * General security:: General security @@ -619,7 +656,6 @@ Queries from Twin Project MySQL Server Functions * Languages:: What languages are supported by @strong{MySQL}? -* Table size:: How big @strong{MySQL} tables can be What Languages Are Supported by MySQL? @@ -919,23 +955,6 @@ MySQL PHP API * PHP problems:: Common problems with MySQL and PHP -How MySQL Compares to Other Databases - -* Compare mSQL:: How @strong{MySQL} compares to @code{mSQL} -* Compare PostgreSQL:: How @strong{MySQL} compares with PostgreSQL - -How MySQL Compares to @code{mSQL} - -* Using mSQL tools:: How to convert @code{mSQL} tools for @strong{MySQL} -* Protocol differences:: How @code{mSQL} and @strong{MySQL} client/server communications protocols differ -* Syntax differences:: How @code{mSQL} 2.0 SQL syntax differs from @strong{MySQL} - -How MySQL Compares to PostgreSQL - -* MySQL-PostgreSQL goals:: MySQL and PostgreSQL development strategies -* MySQL-PostgreSQL features:: Featurevise Comparison of MySQL and PostgreSQL -* MySQL-PostgreSQL benchmarks:: Benchmarking MySQL and PostgreSQL - MySQL Internals * MySQL threads:: MySQL threads @@ -1111,13 +1130,6 @@ Changes in release 3.19.x * News-3.19.4:: Changes in release 3.19.4 * News-3.19.3:: Changes in release 3.19.3 -MySQL and the future (The TODO) - -* TODO MySQL 4.0:: Things that should be in Version 4.0 -* TODO future:: Things that must be done in the near future -* TODO sometime:: Things that have to be done sometime -* TODO unplanned:: Some things we don't have any plans to do - Comments on porting to other systems * Debugging server:: Debugging a @strong{MySQL} server @@ -1144,22 +1156,9 @@ Debugging a MySQL server @cindex online location of manual @cindex manual, online location -@node Introduction, Questions, Top, Top +@node Introduction, Installing, Top, Top @chapter General Information About MySQL -@menu -* What-is:: What is @strong{MySQL}? -* What is MySQL AB:: -* Manual-info:: About this manual -* History:: History of @strong{MySQL} -* MySQL-Books:: Books about MySQL -* Features:: The main features of @strong{MySQL} -* Stability:: How stable is @strong{MySQL}? -* Year 2000 compliance:: Year 2000 compliance -* General-SQL:: General SQL information and tutorials -* Useful Links:: Useful @strong{MySQL}-related links -@end menu - This is the @strong{MySQL} reference manual; it documents @strong{MySQL} Version @value{mysql_version}. As @strong{MySQL} is work in progress, the manual gets updated frequently. There is a very good chance that @@ -1246,10 +1245,36 @@ or relational database concepts. If you want general information about SQL, see @ref{General-SQL}. For books that focus more specifically on @strong{MySQL}, see @ref{MySQL-Books}. + +@menu +* MySQL and MySQL AB:: +* MySQL Information Sources:: +* Licensing and Support:: +* Compatibility:: +* Comparisons:: +* TODO:: +@end menu + +@node MySQL and MySQL AB, MySQL Information Sources, Introduction, Introduction +@section MySQL, MySQL AB, and Open Source + +@menu +* What-is:: What is @strong{MySQL}? +* What is MySQL AB:: What is @strong{MySQL AB}? +* Manual-info:: About this manual +* Manual conventions:: Conventions used in this manual +* History:: History of @strong{MySQL} +* Features:: The main features of @strong{MySQL} +* Stability:: How stable is @strong{MySQL}? +* Table size:: +* Year 2000 compliance:: Year 2000 compliance +@end menu + +@node What-is, What is MySQL AB, MySQL and MySQL AB, MySQL and MySQL AB +@subsection What Is MySQL + @cindex MySQL, defined @cindex MySQL, introduction -@node What-is, What is MySQL AB, Introduction, Introduction -@section What Is MySQL @strong{MySQL}, the most popular Open Source SQL database, is provided by @strong{MySQL AB}. @strong{MySQL AB} is a commercial company that @@ -1335,9 +1360,10 @@ language already supports @strong{MySQL}. The official way to pronounce @strong{MySQL} is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL. +@node What is MySQL AB, Manual-info, What-is, MySQL and MySQL AB +@subsection What Is MySQL AB + @cindex MySQL AB, defined -@node What is MySQL AB, Manual-info, What-is, Introduction -@section What Is MySQL AB @strong{MySQL AB} is the Swedish company owned and run by the @strong{MySQL} founders and main developers. We are dedicated to developing @strong{MySQL} @@ -1446,12 +1472,8 @@ Are a virtual company, networking with others. Work against software patents. @end itemize -@node Manual-info, History, What is MySQL AB, Introduction -@section About This Manual - -@menu -* Manual conventions:: Conventions used in this manual -@end menu +@node Manual-info, Manual conventions, What is MySQL AB, MySQL and MySQL AB +@subsection About This Manual This manual is currently available in Texinfo, plain text, Info, HTML, PostScript, and PDF versions. The primary document is the Texinfo file. @@ -1467,7 +1489,7 @@ This manual is written and maintained by David Axmark, Michael (Monty) Widenius, Jeremy Cole, and Paul DuBois. For other contributors, see @ref{Credits}. -@node Manual conventions, , Manual-info, Manual-info +@node Manual conventions, History, Manual-info, MySQL and MySQL AB @subsection Conventions Used in This Manual This manual uses certain typographical conventions: @@ -1591,12 +1613,13 @@ alternatives are listed within braces (@samp{@{} and @samp{@}}): @{DESCRIBE | DESC@} tbl_name @{col_name | wild@} @end example +@node History, Features, Manual conventions, MySQL and MySQL AB +@subsection History of MySQL + @cindex MySQL history @cindex history of MySQL @cindex MySQL name @cindex My, derivation -@node History, MySQL-Books, Manual-info, Introduction -@section History of MySQL We once started out with the intention of using @code{mSQL} to connect to our tables using our own fast low-level (ISAM) routines. However, after some @@ -1611,363 +1634,9 @@ directory and a large number of our libraries and tools have had the prefix is also named My. Which of the two gave its name to @strong{MySQL} is still a mystery, even for us. -@node MySQL-Books, Features, History, Introduction -@section Books About MySQL - -@cindex books, about MySQL -@cindex manuals, about MySQL - -While this manual is still the right place for up to date technical -information, its primary goal is to contain everything there is to know -about @strong{MySQL}. It is sometimes nice to have a bound book to read -in bed or while you travel. Here is a list of books about @strong{MySQL} and -related subjects (in English). - -By purchasing a book through these hyperlinks provided herein, you are -contributing to the development of @strong{MySQL}. - -@emph{MySQL} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0735709211&bfmtype=book, Barnes and Noble} -@item Publisher @tab New Riders -@item Author @tab Paul DuBois -@item Pub Date @tab 1st Edition December 1999 -@item ISBN @tab 0735709211 -@item Pages @tab 800 -@item Price @tab $49.99 US -@item Downloadable examples @tab - @uref{http://www.kitebird.com/mysql-book/, @code{samp_db} distribution} -@item Errata @tab -@uref{http://www.kitebird.com/mysql-book/errata.html, are available here} -@end multitable - -Foreword by Michael ``Monty'' Widenius, @strong{MySQL} Moderator. -@* - -In @emph{MySQL}, Paul DuBois provides you with a comprehensive guide to -one of the most popular relational database systems. Paul has -contributed to the online documentation for @strong{MySQL} and is an -active member of the @strong{MySQL} community. The principal @strong{MySQL} -developer, Monty Widenius, and a network of his fellow developers -reviewed the manuscript, and provided Paul with the kind of insight -no one else could supply. -@* - -Instead of merely giving you a general overview of @strong{MySQL}, Paul -teaches you how to make the most of its capabilities. Through two -sample database applications that run throughout the book, he -gives you solutions to problems you're sure to face. He helps you -integrate @strong{MySQL} efficiently with third-party tools, such as PHP -and Perl, enabling you to generate dynamic Web pages through -database queries. He teaches you to write programs that access -@strong{MySQL} databases, and also provides a comprehensive set of -references to column types, operators, functions, SQL syntax, -@strong{MySQL} programming, C API, Perl @code{DBI}, and PHP API. -@emph{MySQL} simply gives you the kind of information you won't find -anywhere else. -@* - -If you use @strong{MySQL}, this book provides you with: -@itemize @bullet -@item -An introduction to @strong{MySQL} and SQL. -@item -Coverage of @strong{MySQL}'s data types and how to use them. -@item -Thorough treatment of how to write client programs in C. -@item -A guide to using the Perl @code{DBI} and PHP APIs for developing -command-line and Web-based applications. -@item -Tips on administrative issues such as user accounts, backup, -crash recovery, and security. -@item -Help in choosing an ISP for @strong{MySQL} access. -@item -A comprehensive reference for @strong{MySQL}'s data types, operators, -functions, and SQL statements and utilities. -@item -Complete reference guides for @strong{MySQL}'s C API, the Perl @code{DBI} API, -and PHP's @strong{MySQL}-related functions. -@end itemize -@* - -@emph{MySQL & mSQL} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=1565924347&bfmtype=book, Barnes and Noble} -@item Publisher @tab O'Reilly -@item Authors @tab Randy Jay Yarger, George Reese & Tim King -@item Pub Date @tab 1st Edition July 1999 -@item ISBN @tab 1-56592-434-7, Order Number: 4347 -@item Pages @tab 506 -@item Price @tab $34.95 -@end multitable - -This book teaches you how to use @strong{MySQL} and @code{mSQL}, two popular -and robust database products that support key subsets of SQL on both Linux -and Unix systems. Anyone who knows basic C, Java, Perl, or Python can -write a program to interact with a database, either as a stand-alone -application or through a Web page. This book takes you through the -whole process, from installation and configuration to programming -interfaces and basic administration. Includes plenty of tutorial -material. -@* - -@emph{Sams' Teach Yourself MySQL in 21 Days} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0672319144&bfmtype=book, Barnes and Noble} -@item Publisher @tab Sams -@item Authors @tab Mark Maslakowski and Tony Butcher -@item Pub Date @tab June 2000 -@item ISBN @tab 0672319144 -@item Pages @tab 650 -@item Price @tab $39.99 -@end multitable - -Sams' @emph{Teach Yourself MySQL in 21 Days} is for intermediate Linux users -who want to move into databases. A large share of the audience is Web -developers who need a database to store large amounts of information that -can be retrieved via the Web. - -Sams' @emph{Teach Yourself MySQL in 21 Days} is a practical, step-by-step -tutorial. The reader will learn to design and employ this open source -database technology into his or her Web site using practical, hands-on -examples to follow. -@* - -@emph{E-Commerce Solutions with MySQL} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0761524452&bfmtype=book, Barnes and Noble} -@item Publisher @tab Prima Communications, Inc. -@item Authors @tab N/A -@item Pub Date @tab January 2000 -@item ISBN @tab 0761524452 -@item Pages @tab 500 -@item Price @tab $39.99 -@end multitable - -No description available. -@* - -@emph{MySQL and PHP from Scratch} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0789724405&bfmtype=book, Barnes and Noble} -@item Publisher @tab Que -@item Authors @tab N/A -@item Pub Date @tab September 2000 -@item ISBN @tab 0789724405 -@item Pages @tab 550 -@item Price @tab $34.99 -@end multitable - -This book puts together information on installing, setting up, and -troubleshooting Apache, @strong{MySQL}, PHP3, and IMP into one complete -volume. You also learn how each piece is part of a whole by learning, -step-by-step, how to create a web-based e-mail system. Learn to run -the equivalent of Active Server Pages (ASP) using PHP3, set up an -e-commerce site using a database and the Apache web server, and create -a data entry system (such as sales, product quality tracking, customer -preferences, etc) that no installation in the PC. -@* - -@emph{Professional MySQL Programming} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://shop.barnesandnoble.com/bookSearch/isbnInquiry.asp?isbn=1861005164, Barnes and Noble} -@item Publisher @tab Wrox Press, Inc. -@item Authors @tab N/A -@item Pub Date @tab Late 2001 -@item ISBN @tab 1861005164 -@item Pages @tab 1000 -@item Price @tab $49.99 -@end multitable - -No description available. -@* - -@emph{Professional Linux Programming} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=1861003013&bfmtype=book, Barnes and Noble} -@item Publisher @tab Wrox Press, Inc. -@item Authors @tab N/A -@item Pub Date @tab September 2000 -@item ISBN @tab 1861003013 -@item Pages @tab 1155 -@item Price @tab $47.99 -@end multitable - -In this follow-up to the best-selling @emph{Beginning Linux Programming}, -you will learn from the authors' real-world knowledge and experience of -developing software for Linux; you'll be taken through the development -of a sample 'DVD Store' application, with 'theme' chapters addressing -different aspects of its implementation. Meanwhile, individual -``take-a-break'' chapters cover important topics that go beyond the -bounds of the central theme. All focus on the practical aspects of -programming, showing how crucial it is to choose the right tools for -the job, use them as they should be used, and get things right first -time. -@* - -@emph{PHP and MySQL Web Development} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0672317842&bfmtype=book, Barnes and Noble} -@item Publisher @tab Sams -@item Authors @tab Luke Welling, Laura Thomson -@item Pub Date @tab March 2001 -@item ISBN @tab 0672317842 -@item Pages @tab 700 -@item Price @tab $49.99 -@end multitable - -@emph{PHP and MySQL Web Development} introduces you to the advantages -of implementing both @strong{MySQL} and PHP. These advantages are detailed -through the provision of both statistics and several case studies. A -practical web application is developed throughout the book, providing -you with the tools necessary to implement a functional online -database. Each function is developed separately, allowing you the -choice to incorporate only those parts that you would like to -implement. Programming concepts of the PHP language are highlighted, -including functions which tie @strong{MySQL} support into a PHP script and -advanced topics regarding table manipulation. -@* - -@strong{Books recommended by the MySQL Developers} - -@emph{SQL-99 Complete, Really} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0879305681&bfmtype=book, Barnes and Noble} -@item Publisher @tab CMP Books -@item Authors @tab Peter Gulutzan, Trudy Pelzer -@item Pub Date @tab April 1999 -@item ISBN @tab 0879305681 -@item Pages @tab 1104 -@item Price @tab $55.96 -@end multitable - -This book contains complete descriptions of the new standards for -syntax, data structures, and retrieval processes of SQL databases. As -an example-based reference manual, it includes all of the CLI -functions, information, schema tables, and status codes, as well as a -working SQL database provided on the companion disk. -@* - -@emph{C, A reference manual} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0133262243&bfmtype=book, Barnes and Noble} -@item Publisher @tab Prentice Hall -@item Authors @tab Samuel P. Harbison, Guy L. Steele -@item Pub Date @tab September 1994 -@item ISBN @tab 0133262243 -@item Pages @tab 480 -@item Price @tab $35.99 -@end multitable - -A new and improved revision of the bestselling C language -reference. This manual introduces the notion of "Clean C", writing C -code that can be compiled as a C++ program, C programming style that -emphasizes correctness, portability, maintainability, and -incorporates the ISO C Amendment 1 (1994) which specifies new -facilities for writing portable, international programs in C. -@* - -@emph{C++ for Real Programmers} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0120499428&bfmtype=book, Barnes and Noble} -@item Publisher @tab Academic Press, Incorporated -@item Authors @tab Jeff Alger, Jim Keogh -@item Pub Date @tab February 1998 -@item ISBN @tab 0120499428 -@item Pages @tab 388 -@item Price @tab $39.95 -@end multitable - -@emph{C++ For Real Programmers} bridges the gap between C++ as described -in beginner and intermediate-level books and C++ as it is practiced by -experts. Numerous valuable techniques are described, organized into -three simple themes: indirection, class hierarchies, and memory -management. It also provides in-depth coverage of template creation, -exception handling, pointers and optimization techniques. The focus of -the book is on ANSI C++ and, as such, is compiler independent. - -@emph{C++ For Real Programmers} is a revision of -@emph{Secrets of the C++ Masters} and includes a new appendix comparing C++ -with Java. The book comes with a 3.5" disk for Windows with source code. -@* - -@emph{Algorithms in C} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0201514257&bfmtype=book, Barnes and Noble} -@item Publisher @tab Addison Wesley Longman, Inc. -@item Authors @tab Robert Sedgewick -@item Pub Date @tab April 1990 -@item ISBN @tab 0201514257 -@item Pages @tab 648 -@item Price @tab $45.75 -@end multitable - -@emph{Algorithms in C} describes a variety of algorithms in a number of -areas of interest, including: sorting, searching, string-processing, and -geometric, graph and mathematical algorithms. The book emphasizes -fundamental techniques, providing readers with the tools to confidently -implement, run, and debug useful algorithms. -@* - -@emph{Multithreaded Programming with Pthreads} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0136807291&bfmtype=book, Barnes and Noble} -@item Publisher @tab Prentice Hall -@item Authors @tab Bil Lewis, Daniel J. Berg -@item Pub Date @tab October 1997 -@item ISBN @tab 0136807291 -@item Pages @tab 432 -@item Price @tab $34.95 -@end multitable - -Based on the best-selling @emph{Threads Primer}, -@emph{Multithreaded Programming with Pthreads} gives you a solid -understanding of Posix threads: what they are, how they work, when to use -them, and how to optimize them. It retains the clarity and humor of -@emph{Threads Primer}, but includes expanded comparisons to Win32 and OS/2 -implementations. Code examples tested on all of the major UNIX platforms -are featured along with detailed explanations of how and why they use threads. -@* - -@emph{Programming the PERL DBI: Database Programming with PERL} -@multitable @columnfractions .3 .7 -@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=1565926994&bfmtype=book, Barnes and Noble} -@item Publisher @tab O'Reilly & Associates, Incorporated -@item Authors @tab Alligator Descartes, Tim Bunce -@item Pub Date @tab February 2000 -@item ISBN @tab 1565926994 -@item Pages @tab 400 -@item Price @tab $27.96 -@end multitable - -@emph{Programming the Perl DBI} is coauthored by Alligator Descartes, one -of the most active members of the DBI community, and by Tim Bunce, the -inventor of DBI. For the uninitiated, the book explains the architecture -of DBI and shows you how to write DBI-based programs. For the experienced -DBI dabbler, this book explains DBI's nuances and the peculiarities of each -individual DBD. - -The book includes: -@itemize @bullet -@item -An introduction to DBI and its design. -@item -How to construct queries and bind parameters. -@item -Working with database, driver, and statement handles. -@item -Debugging techniques. -@item -Coverage of each existing DBD. -@item -A complete reference to DBI. -@end itemize -@* -@node Features, Stability, MySQL-Books, Introduction -@section The Main Features of MySQL +@node Features, Stability, History, MySQL and MySQL AB +@subsection The Main Features of MySQL @cindex main features of MySQL @cindex features of MySQL @@ -2130,8 +1799,8 @@ can be used to determine how the optimizer resolves a query. @end itemize @cindex stability -@node Stability, Year 2000 compliance, Features, Introduction -@section How Stable Is MySQL? +@node Stability, Table size, Features, MySQL and MySQL AB +@subsection How Stable Is MySQL? This section addresses the questions ``How stable is @strong{MySQL}?'' and ``Can I depend on @strong{MySQL} in this project?'' We will try to clarify @@ -2146,8 +1815,6 @@ new users who made queries in a manner different than our own. Each new release has had fewer portability problems than the previous one (even though each has had many new features). -@c FIX We've been stable for quite a while now. :) (jcole) - Each release of @strong{MySQL} has been usable, and there have been problems only when users start to use code from the ``gray zones.'' Naturally, outside users don't know what the gray zones are; this section attempts to indicate @@ -2290,11 +1957,68 @@ Text search seems to work, but is still not widely used. Bugs are usually fixed right away with a patch; for serious bugs, there is almost always a new release. +@node Table size, Year 2000 compliance, Stability, MySQL and MySQL AB +@subsection How Big Can MySQL Tables Be? + +@cindex tables, maximum size +@cindex size of tables +@cindex operating systems, file size limits +@cindex limits, file size +@cindex files, size limits + +@strong{MySQL} Version 3.22 has a 4G limit on table size. With the new +@code{MyISAM} in @strong{MySQL} Version 3.23 the maximum table size is +pushed up to 8 million terabytes (2 ^ 63 bytes). + +Note, however, that operating systems have their own file size +limits. Here are some examples: + +@multitable @columnfractions .5 .5 +@item @strong{Operating System} @tab @strong{File Size Limit} +@item Linux-Intel 32 bit @tab 2G, 4G or more, depends on Linux version +@item Linux-Alpha @tab 8T (?) +@item Solaris 2.5.1 @tab 2G (possible 4G with patch) +@item Solaris 2.6 @tab 4G +@item Solaris 2.7 Intel @tab 4G +@item Solaris 2.7 ULTRA-SPARC @tab 8T (?) +@end multitable + +On Linux 2.2 you can get bigger tables than 2G by using the LFS patch for +the ext2 file system. On Linux 2.4 there exists also patches for ReiserFS +to get support for big files. + +This means that the table size for @strong{MySQL} is normally limited by +the operating system. + +By default, @strong{MySQL} tables have a maximum size of about 4G. You can +check the maximum table size for a table with the @code{SHOW TABLE STATUS} +command or with the @code{myisamchk -dv table_name}. +@xref{SHOW}. + +If you need bigger tables than 4G (and your operating system supports +this), you should set the @code{AVG_ROW_LENGTH} and @code{MAX_ROWS} +parameter when you create your table. @xref{CREATE TABLE}. You can +also set these later with @code{ALTER TABLE}. @xref{ALTER TABLE}. + +If your big table is going to be read-only, you could use +@code{myisampack} to merge and compress many tables to one. +@code{myisampack} usually compresses a table by at least 50%, so you can +have, in effect, much bigger tables. @xref{myisampack, , +@code{myisampack}}. + +You can go around the operating system file limit for @code{MyISAM} data +files by using the @code{RAID} option. @xref{CREATE TABLE}. + +Another solution can be the included MERGE library, which allows you to +handle a collection of identical tables as one. @xref{MERGE, MERGE +tables}. + +@node Year 2000 compliance, , Table size, MySQL and MySQL AB +@subsection Year 2000 Compliance + @cindex Year 2000 compliance @cindex compliance, Y2K @cindex date functions, Y2K compliance -@node Year 2000 compliance, General-SQL, Stability, Introduction -@section Year 2000 Compliance @strong{MySQL} itself has no problems with Year 2000 (Y2K) compliance: @@ -2388,10 +2112,373 @@ provide unambiguous input. See @ref{Y2K issues} for @strong{MySQL}'s rules for dealing with ambiguous date input data (data containing 2-digit year values). +@node MySQL Information Sources, Licensing and Support, MySQL and MySQL AB, Introduction +@section MySQL Information Sources + @cindex manuals, about @strong{MySQL} @cindex books, about @strong{MySQL} -@node General-SQL, Useful Links, Year 2000 compliance, Introduction -@section General SQL Information and Tutorials + +@menu +* MySQL-Books:: +* General-SQL:: +* Useful Links:: +* Questions:: +@end menu + +@node MySQL-Books, General-SQL, MySQL Information Sources, MySQL Information Sources +@subsection Books About MySQL + +While this manual is still the right place for up to date technical +information, its primary goal is to contain everything there is to know +about @strong{MySQL}. It is sometimes nice to have a bound book to read +in bed or while you travel. Here is a list of books about @strong{MySQL} and +related subjects (in English). + +By purchasing a book through these hyperlinks provided herein, you are +contributing to the development of @strong{MySQL}. + +@emph{MySQL} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0735709211&bfmtype=book, Barnes and Noble} +@item Publisher @tab New Riders +@item Author @tab Paul DuBois +@item Pub Date @tab 1st Edition December 1999 +@item ISBN @tab 0735709211 +@item Pages @tab 800 +@item Price @tab $49.99 US +@item Downloadable examples @tab + @uref{http://www.kitebird.com/mysql-book/, @code{samp_db} distribution} +@item Errata @tab +@uref{http://www.kitebird.com/mysql-book/errata.html, are available here} +@end multitable + +Foreword by Michael ``Monty'' Widenius, @strong{MySQL} Moderator. +@* + +In @emph{MySQL}, Paul DuBois provides you with a comprehensive guide to +one of the most popular relational database systems. Paul has +contributed to the online documentation for @strong{MySQL} and is an +active member of the @strong{MySQL} community. The principal @strong{MySQL} +developer, Monty Widenius, and a network of his fellow developers +reviewed the manuscript, and provided Paul with the kind of insight +no one else could supply. +@* + +Instead of merely giving you a general overview of @strong{MySQL}, Paul +teaches you how to make the most of its capabilities. Through two +sample database applications that run throughout the book, he +gives you solutions to problems you're sure to face. He helps you +integrate @strong{MySQL} efficiently with third-party tools, such as PHP +and Perl, enabling you to generate dynamic Web pages through +database queries. He teaches you to write programs that access +@strong{MySQL} databases, and also provides a comprehensive set of +references to column types, operators, functions, SQL syntax, +@strong{MySQL} programming, C API, Perl @code{DBI}, and PHP API. +@emph{MySQL} simply gives you the kind of information you won't find +anywhere else. +@* + +If you use @strong{MySQL}, this book provides you with: +@itemize @bullet +@item +An introduction to @strong{MySQL} and SQL. +@item +Coverage of @strong{MySQL}'s data types and how to use them. +@item +Thorough treatment of how to write client programs in C. +@item +A guide to using the Perl @code{DBI} and PHP APIs for developing +command-line and Web-based applications. +@item +Tips on administrative issues such as user accounts, backup, +crash recovery, and security. +@item +Help in choosing an ISP for @strong{MySQL} access. +@item +A comprehensive reference for @strong{MySQL}'s data types, operators, +functions, and SQL statements and utilities. +@item +Complete reference guides for @strong{MySQL}'s C API, the Perl @code{DBI} API, +and PHP's @strong{MySQL}-related functions. +@end itemize +@* + +@emph{MySQL & mSQL} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=1565924347&bfmtype=book, Barnes and Noble} +@item Publisher @tab O'Reilly +@item Authors @tab Randy Jay Yarger, George Reese & Tim King +@item Pub Date @tab 1st Edition July 1999 +@item ISBN @tab 1-56592-434-7, Order Number: 4347 +@item Pages @tab 506 +@item Price @tab $34.95 +@end multitable + +This book teaches you how to use @strong{MySQL} and @code{mSQL}, two popular +and robust database products that support key subsets of SQL on both Linux +and Unix systems. Anyone who knows basic C, Java, Perl, or Python can +write a program to interact with a database, either as a stand-alone +application or through a Web page. This book takes you through the +whole process, from installation and configuration to programming +interfaces and basic administration. Includes plenty of tutorial +material. +@* + +@emph{Sams' Teach Yourself MySQL in 21 Days} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0672319144&bfmtype=book, Barnes and Noble} +@item Publisher @tab Sams +@item Authors @tab Mark Maslakowski and Tony Butcher +@item Pub Date @tab June 2000 +@item ISBN @tab 0672319144 +@item Pages @tab 650 +@item Price @tab $39.99 +@end multitable + +Sams' @emph{Teach Yourself MySQL in 21 Days} is for intermediate Linux users +who want to move into databases. A large share of the audience is Web +developers who need a database to store large amounts of information that +can be retrieved via the Web. + +Sams' @emph{Teach Yourself MySQL in 21 Days} is a practical, step-by-step +tutorial. The reader will learn to design and employ this open source +database technology into his or her Web site using practical, hands-on +examples to follow. +@* + +@emph{E-Commerce Solutions with MySQL} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0761524452&bfmtype=book, Barnes and Noble} +@item Publisher @tab Prima Communications, Inc. +@item Authors @tab N/A +@item Pub Date @tab January 2000 +@item ISBN @tab 0761524452 +@item Pages @tab 500 +@item Price @tab $39.99 +@end multitable + +No description available. +@* + +@emph{MySQL and PHP from Scratch} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0789724405&bfmtype=book, Barnes and Noble} +@item Publisher @tab Que +@item Authors @tab N/A +@item Pub Date @tab September 2000 +@item ISBN @tab 0789724405 +@item Pages @tab 550 +@item Price @tab $34.99 +@end multitable + +This book puts together information on installing, setting up, and +troubleshooting Apache, @strong{MySQL}, PHP3, and IMP into one complete +volume. You also learn how each piece is part of a whole by learning, +step-by-step, how to create a web-based e-mail system. Learn to run +the equivalent of Active Server Pages (ASP) using PHP3, set up an +e-commerce site using a database and the Apache web server, and create +a data entry system (such as sales, product quality tracking, customer +preferences, etc) that no installation in the PC. +@* + +@emph{Professional MySQL Programming} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://shop.barnesandnoble.com/bookSearch/isbnInquiry.asp?isbn=1861005164, Barnes and Noble} +@item Publisher @tab Wrox Press, Inc. +@item Authors @tab N/A +@item Pub Date @tab Late 2001 +@item ISBN @tab 1861005164 +@item Pages @tab 1000 +@item Price @tab $49.99 +@end multitable + +No description available. +@* + +@emph{Professional Linux Programming} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=1861003013&bfmtype=book, Barnes and Noble} +@item Publisher @tab Wrox Press, Inc. +@item Authors @tab N/A +@item Pub Date @tab September 2000 +@item ISBN @tab 1861003013 +@item Pages @tab 1155 +@item Price @tab $47.99 +@end multitable + +In this follow-up to the best-selling @emph{Beginning Linux Programming}, +you will learn from the authors' real-world knowledge and experience of +developing software for Linux; you'll be taken through the development +of a sample 'DVD Store' application, with 'theme' chapters addressing +different aspects of its implementation. Meanwhile, individual +``take-a-break'' chapters cover important topics that go beyond the +bounds of the central theme. All focus on the practical aspects of +programming, showing how crucial it is to choose the right tools for +the job, use them as they should be used, and get things right first +time. +@* + +@emph{PHP and MySQL Web Development} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0672317842&bfmtype=book, Barnes and Noble} +@item Publisher @tab Sams +@item Authors @tab Luke Welling, Laura Thomson +@item Pub Date @tab March 2001 +@item ISBN @tab 0672317842 +@item Pages @tab 700 +@item Price @tab $49.99 +@end multitable + +@emph{PHP and MySQL Web Development} introduces you to the advantages +of implementing both @strong{MySQL} and PHP. These advantages are detailed +through the provision of both statistics and several case studies. A +practical web application is developed throughout the book, providing +you with the tools necessary to implement a functional online +database. Each function is developed separately, allowing you the +choice to incorporate only those parts that you would like to +implement. Programming concepts of the PHP language are highlighted, +including functions which tie @strong{MySQL} support into a PHP script and +advanced topics regarding table manipulation. +@* + +@strong{Books recommended by the MySQL Developers} + +@emph{SQL-99 Complete, Really} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0879305681&bfmtype=book, Barnes and Noble} +@item Publisher @tab CMP Books +@item Authors @tab Peter Gulutzan, Trudy Pelzer +@item Pub Date @tab April 1999 +@item ISBN @tab 0879305681 +@item Pages @tab 1104 +@item Price @tab $55.96 +@end multitable + +This book contains complete descriptions of the new standards for +syntax, data structures, and retrieval processes of SQL databases. As +an example-based reference manual, it includes all of the CLI +functions, information, schema tables, and status codes, as well as a +working SQL database provided on the companion disk. +@* + +@emph{C, A reference manual} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0133262243&bfmtype=book, Barnes and Noble} +@item Publisher @tab Prentice Hall +@item Authors @tab Samuel P. Harbison, Guy L. Steele +@item Pub Date @tab September 1994 +@item ISBN @tab 0133262243 +@item Pages @tab 480 +@item Price @tab $35.99 +@end multitable + +A new and improved revision of the bestselling C language +reference. This manual introduces the notion of "Clean C", writing C +code that can be compiled as a C++ program, C programming style that +emphasizes correctness, portability, maintainability, and +incorporates the ISO C Amendment 1 (1994) which specifies new +facilities for writing portable, international programs in C. +@* + +@emph{C++ for Real Programmers} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0120499428&bfmtype=book, Barnes and Noble} +@item Publisher @tab Academic Press, Incorporated +@item Authors @tab Jeff Alger, Jim Keogh +@item Pub Date @tab February 1998 +@item ISBN @tab 0120499428 +@item Pages @tab 388 +@item Price @tab $39.95 +@end multitable + +@emph{C++ For Real Programmers} bridges the gap between C++ as described +in beginner and intermediate-level books and C++ as it is practiced by +experts. Numerous valuable techniques are described, organized into +three simple themes: indirection, class hierarchies, and memory +management. It also provides in-depth coverage of template creation, +exception handling, pointers and optimization techniques. The focus of +the book is on ANSI C++ and, as such, is compiler independent. + +@emph{C++ For Real Programmers} is a revision of +@emph{Secrets of the C++ Masters} and includes a new appendix comparing C++ +with Java. The book comes with a 3.5" disk for Windows with source code. +@* + +@emph{Algorithms in C} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0201514257&bfmtype=book, Barnes and Noble} +@item Publisher @tab Addison Wesley Longman, Inc. +@item Authors @tab Robert Sedgewick +@item Pub Date @tab April 1990 +@item ISBN @tab 0201514257 +@item Pages @tab 648 +@item Price @tab $45.75 +@end multitable + +@emph{Algorithms in C} describes a variety of algorithms in a number of +areas of interest, including: sorting, searching, string-processing, and +geometric, graph and mathematical algorithms. The book emphasizes +fundamental techniques, providing readers with the tools to confidently +implement, run, and debug useful algorithms. +@* + +@emph{Multithreaded Programming with Pthreads} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=0136807291&bfmtype=book, Barnes and Noble} +@item Publisher @tab Prentice Hall +@item Authors @tab Bil Lewis, Daniel J. Berg +@item Pub Date @tab October 1997 +@item ISBN @tab 0136807291 +@item Pages @tab 432 +@item Price @tab $34.95 +@end multitable + +Based on the best-selling @emph{Threads Primer}, +@emph{Multithreaded Programming with Pthreads} gives you a solid +understanding of Posix threads: what they are, how they work, when to use +them, and how to optimize them. It retains the clarity and humor of +@emph{Threads Primer}, but includes expanded comparisons to Win32 and OS/2 +implementations. Code examples tested on all of the major UNIX platforms +are featured along with detailed explanations of how and why they use threads. +@* + +@emph{Programming the PERL DBI: Database Programming with PERL} +@multitable @columnfractions .3 .7 +@item Available @tab @uref{http://service.bfast.com/bfast/click?bfmid=2181&sourceid=34233559&bfpid=1565926994&bfmtype=book, Barnes and Noble} +@item Publisher @tab O'Reilly & Associates, Incorporated +@item Authors @tab Alligator Descartes, Tim Bunce +@item Pub Date @tab February 2000 +@item ISBN @tab 1565926994 +@item Pages @tab 400 +@item Price @tab $27.96 +@end multitable + +@emph{Programming the Perl DBI} is coauthored by Alligator Descartes, one +of the most active members of the DBI community, and by Tim Bunce, the +inventor of DBI. For the uninitiated, the book explains the architecture +of DBI and shows you how to write DBI-based programs. For the experienced +DBI dabbler, this book explains DBI's nuances and the peculiarities of each +individual DBD. + +The book includes: +@itemize @bullet +@item +An introduction to DBI and its design. +@item +How to construct queries and bind parameters. +@item +Working with database, driver, and statement handles. +@item +Debugging techniques. +@item +Coverage of each existing DBD. +@item +A complete reference to DBI. +@end itemize +@* + +@node General-SQL, Useful Links, MySQL-Books, MySQL Information Sources +@subsection General SQL Information and Tutorials The following book has been recommended by several people on the @strong{MySQL} mailing list: @@ -2419,8 +2506,8 @@ Alameda, CA USA A SQL tutorial is available on the net at http://w3.one.net/~jhoffman/sqltut.htm -@node Useful Links, , General-SQL, Introduction -@section Useful MySQL-related Links +@node Useful Links, Questions, General-SQL, MySQL Information Sources +@subsection Useful MySQL-related Links Apart from the following links, you can find and download a lot of @strong{MySQL} programs, tools and APIs from the @@ -3007,10 +3094,11 @@ require that you show a @strong{MySQL} logo somewhere if you wish your site to be added. It is okay to have it on a ``used tools'' page or something similar. +@node Questions, , Useful Links, MySQL Information Sources +@subsection MySQL Mailing Lists + @cindex reporting, errors @cindex MySQL mailing lists -@node Questions, Licensing and Support, Introduction, Top -@chapter MySQL Mailing Lists @menu * Mailing-list:: The @strong{MySQL} mailing lists @@ -3019,11 +3107,11 @@ something similar. * Answering questions:: Guidelines for answering questions on the mailing list @end menu -This chapter introduces you to the @strong{MySQL} mailing lists, and gives +This section introduces you to the @strong{MySQL} mailing lists, and gives some guidelines as to how to use them. @node Mailing-list, Asking questions, Questions, Questions -@section The MySQL Mailing Lists +@subsubsection The MySQL Mailing Lists @cindex mailing lists @cindex email lists @@ -3167,11 +3255,12 @@ Email @code{subscribe mysql-br your@@email.address} to this list. Email @code{subscribe mysql your@@email.address} to this list. @end table +@node Asking questions, Bug reports, Mailing-list, Questions +@subsubsection Asking Questions or Reporting Bugs + @cindex net etiquette @cindex mailing lists, archive location @cindex searching, MySQL webpages -@node Asking questions, Bug reports, Mailing-list, Questions -@section Asking Questions or Reporting Bugs Before posting a bug report or question, please do the following: @@ -3200,6 +3289,9 @@ local @strong{MySQL} expert. If you still can't find an answer to your question, go ahead and read the next section about how to send mail to @email{mysql@@lists.mysql.com}. +@node Bug reports, Answering questions, Asking questions, Questions +@subsubsection How to Report Bugs or Problems + @cindex bugs, reporting @cindex reporting, bugs @cindex problems, reporting @@ -3207,8 +3299,6 @@ question, go ahead and read the next section about how to send mail to @cindex @code{mysqlbug} script @cindex creating, bug reports @cindex scripts, @code{mysqlbug} -@node Bug reports, Answering questions, Asking questions, Questions -@section How to Report Bugs or Problems Writing a good bug report takes patience, but doing it right the first time saves time for us and for you. A good bug report containing a full @@ -3506,12 +3596,13 @@ it is considered good etiquette to summarize the answers and send the summary to the mailing list so that others may have the benefit of responses you received that helped you solve your problem! +@node Answering questions, , Bug reports, Questions +@subsubsection Guidelines for Answering Question on the Mailing List + @cindex net etiquette @cindex questions, answering @cindex answering questions, etiquette @cindex mailing lists, guidelines -@node Answering questions, , Bug reports, Questions -@section Guidelines for Answering Question on the Mailing List If you consider your answer to have broad interest, you may want to post it to the mailing list instead of replying directly to the individual who @@ -3525,10 +3616,11 @@ obliged to quote the entire original message. Please don't post mail messages from your browser with HTML mode turned on! Many users don't read mail with a browser! +@node Licensing and Support, Compatibility, MySQL Information Sources, Introduction +@section MySQL Licensing and Support + @cindex licensing terms @cindex support terms -@node Licensing and Support, Installing, Questions, Top -@chapter MySQL Licensing and Support @menu * Licensing policy:: @strong{MySQL} licensing policy @@ -3538,7 +3630,7 @@ Many users don't read mail with a browser! * Support:: Types of commercial support @end menu -This chapter describes @strong{MySQL} support and licensing +This section describes @strong{MySQL} support and licensing arrangements: @itemize @bullet @@ -3555,12 +3647,13 @@ arrangements: @item Commercial licensing costs @end itemize +@node Licensing policy, Copyright, Licensing and Support, Licensing and Support +@subsection MySQL Licensing Policy + @cindex licensing policy @cindex technical support, licensing @cindex support, licensing @cindex General Public License, MySQL -@node Licensing policy, Copyright, Licensing and Support, Licensing and Support -@section MySQL Licensing Policy The formal terms of the GPL license can be found at @ref{GPL license}. Basically, our licensing policy and interpretation of the GPL is as follows: @@ -3640,9 +3733,10 @@ is to use the license form on @strong{MySQL}'s secure server at @uref{https://order.mysql.com/}. Other forms of payment are discussed in @ref{Payment information}. -@cindex copyrights @node Copyright, Licensing examples, Licensing policy, Licensing and Support -@section Copyrights Used by MySQL +@subsection Copyrights Used by MySQL + +@cindex copyrights @menu * Copyright changes:: Possible future copyright changes @@ -3686,13 +3780,13 @@ want to help secure its development by purchasing licenses or a support contract. @xref{Support}. @node Copyright changes, , Copyright, Copyright -@subsection Copyright Changes +@subsubsection Copyright Changes Version 3.22 of @strong{MySQL} is still using a more strict license. See the documentation for that version for more information. @node Licensing examples, Cost, Copyright, Licensing and Support -@section Example Licensing Situations +@subsection Example Licensing Situations @menu * Products that use MySQL:: Selling products that use @strong{MySQL} @@ -3713,7 +3807,7 @@ Note that a single @strong{MySQL} license covers any number of CPUs and number of clients that connect to the server in any way. @node Products that use MySQL, ISP, Licensing examples, Licensing examples -@subsection Selling Products that use MySQL +@subsubsection Selling Products that use MySQL To determine whether or not you need a @strong{MySQL} license when selling your application, you should ask whether the proper functioning @@ -3757,7 +3851,7 @@ don't need a license. @end itemize @node ISP, Web server, Products that use MySQL, Licensing examples -@subsection ISP MySQL Services +@subsubsection ISP MySQL Services @cindex ISP services @cindex services, ISP @@ -3782,10 +3876,11 @@ it should give its customers at least read access to the source of the @strong{MySQL} installation so that its customer can verify that it is patched correctly. +@node Web server, , ISP, Licensing examples +@subsubsection Running a Web Server Using MySQL + @cindex web server, running @cindex running, a web server -@node Web server, , ISP, Licensing examples -@subsection Running a Web Server Using MySQL If you use @strong{MySQL} in conjunction with a Web server on Unix, you don't have to pay for a license. @@ -3795,12 +3890,13 @@ This is true even if you run a commercial Web server that uses version yourself. However, in this case we would like you to purchase @strong{MySQL} support, because @strong{MySQL} is helping your enterprise. +@node Cost, Support, Licensing examples, Licensing and Support +@subsection MySQL Licensing and Support Costs + @cindex costs, licensing and support @cindex licensing costs @cindex support costs @cindex prices, licensing and support -@node Cost, Support, Licensing examples, Licensing and Support -@section MySQL Licensing and Support Costs @menu * Payment information:: Payment information @@ -3876,13 +3972,15 @@ Note that as our sales staff is very busy, it may take some time until your request is handled. Our support staff does however always answer promptly to support questions! -@cindex payment information @node Payment information, Contact information, Cost, Cost -@subsection Payment information +@subsubsection Payment information + +@cindex payment information Currently we can take SWIFT payments, checks, or credit cards. Payment should be made to: + @example Postgirot Bank AB 105 06 STOCKHOLM, SWEDEN @@ -3922,15 +4020,17 @@ us'' in the comment field. You can also mail a message to @email{sales@@mysql.com} (@strong{not} @code{mysql@@lists.mysql.com}!) with your company information and ask us to bill you. +@node Contact information, , Payment information, Cost +@subsubsection Contact Information + @cindex contact information @cindex licensing, contact information -@cindex partnering, with MySQL +@cindex advertising, contact information +@cindex employment, contact information +@cindex partnering with MySQL @cindex employment with MySQL @cindex jobs at MySQL -@node Contact information, , Payment information, Cost -@subsection Contact Information - For commercial licensing, please contact the @strong{MySQL} licensing team. The much preferred method is by e-mail to @email{licensing@@mysql.com}. Fax is also possible but handling of @@ -3962,11 +4062,12 @@ For general information inquires, please send e-mail to For questions or comments about the workings or content of the Web site, please send e-mail to @email{webmaster@@mysql.com}. +@node Support, , Cost, Licensing and Support +@subsection Types of Commercial Support + @cindex support, types @cindex types, of support @cindex commercial support, types -@node Support, , Cost, Licensing and Support -@section Types of Commercial Support @menu * Basic email support:: Basic email support @@ -3999,10 +4100,11 @@ if you have login support (2000 EURO), you can expect us to work up to 10 hours to help you with things like this. @end itemize +@node Basic email support, Extended email support, Support, Support +@subsubsection Basic E-mail Support + @cindex email, technical support @cindex technical support, by email -@node Basic email support, Extended email support, Support, Support -@subsection Basic E-mail Support Basic e-mail support is a very inexpensive support option and should be thought of more as a way to support our development of @strong{MySQL} @@ -4071,9 +4173,10 @@ If you want us to help optimize your system, you must upgrade to a higher level of support. @end itemize -@cindex extended email support @node Extended email support, Login support, Basic email support, Support -@subsection Extended E-mail Support +@subsubsection Extended E-mail Support + +@cindex extended email support Extended e-mail support includes everything in basic e-mail support with these additions: @@ -4117,9 +4220,10 @@ TODO List. @xref{TODO}. This will ensure that the features you really need will be implemented sooner than they might be otherwise. @end itemize -@cindex login support @node Login support, Extended login support, Extended email support, Support -@subsection Login Support +@subsubsection Login Support + +@cindex login support Login support includes everything in extended e-mail support with these additions: @@ -4160,7 +4264,7 @@ solving the problem. @end itemize @node Extended login support, Telephone support, Login support, Support -@subsection Extended Login Support +@subsubsection Extended Login Support Extended login support includes everything in login support with these additions: @@ -4197,7 +4301,7 @@ rate that is at least 20 % less than our standard rates. @end itemize @node Telephone support, Table handler support, Extended login support, Support -@subsection Telephone Support +@subsubsection Telephone Support Telephone support includes everything in extended login support with these additions: @@ -4212,10 +4316,11 @@ For non critical problem, you can request a @strong{MySQL} developer to phone back within 48 hours to discuss @code{MySQL} related issues. @end itemize +@node Table handler support, , Telephone support, Support +@subsubsection Support for other table handlers + @cindex support, BDB Tables @cindex support, InnoDB Tables -@node Table handler support, , Telephone support, Support -@subsection Support for other table handlers To get support for @code{BDB} tables, @code{InnoDB} tables you have to pay an additional 30% on the standard support price for each of @@ -4231,9 +4336,2629 @@ timely manner, we can't guarantee a quick solution for any problems you can get with the different table handlers. We will however do our best to help you get the problem solved. +@node Compatibility, Comparisons, Licensing and Support, Introduction +@section How Standards-compatible Is MySQL? + +@cindex compatibility, with ANSI SQL +@cindex standards compatibility +@cindex extensions, to ANSI SQL +@cindex ANSI SQL92, extensions to + +@menu +* Extensions to ANSI:: @strong{MySQL} extensions to ANSI SQL92 +* Differences from ANSI:: @strong{MySQL} differences compared to ANSI SQL92 +* ANSI mode:: Running @strong{MySQL} in ANSI mode +* Missing functions:: Functionality missing from @strong{MySQL} +* Standards:: What standards does @strong{MySQL} follow? +* Commit-rollback:: How to cope without @code{COMMIT}-@code{ROLLBACK} +* Bugs:: +@end menu + +This section describes how @strong{MySQL} relates to the ANSI SQL standards. +@strong{MySQL} has many extensions to the ANSI SQL standards, and here you +will find out what they are, and how to use them. You will also find +information about functionality missing from @strong{MySQL}, and how to work +around some differences. + +@node Extensions to ANSI, Differences from ANSI, Compatibility, Compatibility +@subsection MySQL Extensions to ANSI SQL92 + +@strong{MySQL} includes some extensions that you probably will not find in +other SQL databases. Be warned that if you use them, your code will not be +portable to other SQL servers. In some cases, you can write code that +includes @strong{MySQL} extensions, but is still portable, by using comments +of the form @code{/*! ... */}. In this case, @strong{MySQL} will parse and +execute the code within the comment as it would any other @strong{MySQL} +statement, but other SQL servers will ignore the extensions. For example: + +@example +SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ... +@end example + +If you add a version number after the @code{'!'}, the syntax will only be +executed if the @strong{MySQL} version is equal to or newer than the used +version number: + +@example +CREATE /*!32302 TEMPORARY */ TABLE (a int); +@end example + +The above means that if you have Version 3.23.02 or newer, then @strong{MySQL} +will use the @code{TEMPORARY} keyword. + +@strong{MySQL} extensions are listed below: + +@itemize @bullet +@item +The field types @code{MEDIUMINT}, @code{SET}, @code{ENUM}, and the +different @code{BLOB} and @code{TEXT} types. + +@item +The field attributes @code{AUTO_INCREMENT}, @code{BINARY}, @code{NULL}, +@code{UNSIGNED}, and @code{ZEROFILL}. + +@item +All string comparisons are case insensitive by default, with sort +ordering determined by the current character set (ISO-8859-1 Latin1 by +default). If you don't like this, you should declare your columns with +the @code{BINARY} attribute or use the @code{BINARY} cast, which causes +comparisons to be done according to the ASCII order used on the +@strong{MySQL} server host. + +@item +@strong{MySQL} maps each database to a directory under the @strong{MySQL} +data directory, and tables within a database to filenames in the database +directory. + +This has a few implications: + +@cindex database names, case sensitivity +@cindex table names, case sensitivity +@cindex case sensitivity, of database names +@cindex case sensitivity, of table names + +@itemize @minus + +@item +Database names and table names are case sensitive in @strong{MySQL} on +operating systems that have case-sensitive filenames (like most Unix +systems). @xref{Name case sensitivity}. + +@item +Database, table, index, column, or alias names may begin with a digit +(but may not consist solely of digits). + +@item +You can use standard system commands to backup, rename, move, delete, and copy +tables. For example, to rename a table, rename the @file{.MYD}, @file{.MYI}, +and @file{.frm} files to which the table corresponds. +@end itemize + +@item +In SQL statements, you can access tables from different databases +with the @code{db_name.tbl_name} syntax. Some SQL servers provide +the same functionality but call this @code{User space}. +@strong{MySQL} doesn't support tablespaces as in: +@code{create table ralph.my_table...IN my_tablespace}. + +@item +@code{LIKE} is allowed on numeric columns. + +@item +Use of @code{INTO OUTFILE} and @code{STRAIGHT_JOIN} in a @code{SELECT} +statement. @xref{SELECT, , @code{SELECT}}. + +@item +The @code{SQL_SMALL_RESULT} option in a @code{SELECT} statement. + +@item +@code{EXPLAIN SELECT} to get a description on how tables are joined. + +@item +Use of index names, indexes on a prefix of a field, and use of +@code{INDEX} or @code{KEY} in a @code{CREATE TABLE} +statement. @xref{CREATE TABLE, , @code{CREATE TABLE}}. + +@item +Use of @code{TEMPORARY} or @code{IF NOT EXISTS} with @code{CREATE TABLE}. + +@item +Use of @code{COUNT(DISTINCT list)} where 'list' is more than one element. + +@item +Use of @code{CHANGE col_name}, @code{DROP col_name}, or @code{DROP +INDEX}, @code{IGNORE} or @code{RENAME} in an @code{ALTER TABLE} +statement. @xref{ALTER TABLE, , @code{ALTER TABLE}}. + +@item +Use of @code{RENAME TABLE}. @xref{RENAME TABLE, , @code{RENAME TABLE}}. + +@item +Use of multiple @code{ADD}, @code{ALTER}, @code{DROP}, or @code{CHANGE} +clauses in an @code{ALTER TABLE} statement. + +@item +Use of @code{DROP TABLE} with the keywords @code{IF EXISTS}. + +@item +You can drop multiple tables with a single @code{DROP TABLE} statement. + +@item +The @code{LIMIT} clause of the @code{DELETE} statement. + +@item +The @code{DELAYED} clause of the @code{INSERT} and @code{REPLACE} +statements. + +@item +The @code{LOW_PRIORITY} clause of the @code{INSERT}, @code{REPLACE}, +@code{DELETE}, and @code{UPDATE} statements. + +@cindex Oracle compatibility +@cindex compatibility, with Oracle +@item +Use of @code{LOAD DATA INFILE}. In many cases, this syntax is compatible with +Oracle's @code{LOAD DATA INFILE}. @xref{LOAD DATA, , @code{LOAD DATA}}. + +@item +The @code{ANALYZE TABLE}, @code{CHECK TABLE}, @code{OPTIMIZE TABLE}, and +@code{REPAIR TABLE} statements. + +@item +The @code{SHOW} statement. +@xref{SHOW, , @code{SHOW}}. + +@item +Strings may be enclosed by either @samp{"} or @samp{'}, not just by @samp{'}. + +@item +Use of the escape @samp{\} character. + +@item +The @code{SET OPTION} statement. @xref{SET OPTION, , @code{SET OPTION}}. + +@item +You don't need to name all selected columns in the @code{GROUP BY} part. +This gives better performance for some very specific, but quite normal +queries. +@xref{Group by functions}. + +@item +One can specify @code{ASC} and @code{DESC} with @code{GROUP BY}. + +@item +To make it easier for users who come from other SQL environments, +@strong{MySQL} supports aliases for many functions. For example, all +string functions support both ANSI SQL syntax and ODBC syntax. + +@item +@strong{MySQL} understands the @code{||} and @code{&&} operators to mean +logical OR and AND, as in the C programming language. In @strong{MySQL}, +@code{||} and @code{OR} are synonyms, as are @code{&&} and @code{AND}. +Because of this nice syntax, @strong{MySQL} doesn't support +the ANSI SQL @code{||} operator for string concatenation; use +@code{CONCAT()} instead. Because @code{CONCAT()} takes any number +of arguments, it's easy to convert use of the @code{||} operator to +@strong{MySQL}. + +@item +@code{CREATE DATABASE} or @code{DROP DATABASE}. +@xref{CREATE DATABASE, , @code{CREATE DATABASE}}. + +@cindex PostgreSQL compatibility +@cindex compatibility, with PostgreSQL +@item +The @code{%} operator is a synonym for @code{MOD()}. That is, +@code{N % M} is equivalent to @code{MOD(N,M)}. @code{%} is supported +for C programmers and for compatibility with PostgreSQL. + +@item +The @code{=}, @code{<>}, @code{<=} ,@code{<}, @code{>=},@code{>}, +@code{<<}, @code{>>}, @code{<=>}, @code{AND}, @code{OR}, or @code{LIKE} +operators may be used in column comparisons to the left of the +@code{FROM} in @code{SELECT} statements. For example: + +@example +mysql> SELECT col1=1 AND col2=2 FROM tbl_name; +@end example + +@item +The @code{LAST_INSERT_ID()} function. +@xref{mysql_insert_id, , @code{mysql_insert_id()}}. + +@item +The @code{REGEXP} and @code{NOT REGEXP} extended regular expression +operators. + +@item +@code{CONCAT()} or @code{CHAR()} with one argument or more than two +arguments. (In @strong{MySQL}, these functions can take any number of +arguments.) + +@item The @code{BIT_COUNT()}, @code{CASE}, @code{ELT()}, +@code{FROM_DAYS()}, @code{FORMAT()}, @code{IF()}, @code{PASSWORD()}, +@code{ENCRYPT()}, @code{md5()}, @code{ENCODE()}, @code{DECODE()}, +@code{PERIOD_ADD()}, @code{PERIOD_DIFF()}, @code{TO_DAYS()}, or +@code{WEEKDAY()} functions. + +@item +Use of @code{TRIM()} to trim substrings. ANSI SQL only supports removal +of single characters. + +@item +The @code{GROUP BY} functions @code{STD()}, @code{BIT_OR()}, and +@code{BIT_AND()}. + +@item +Use of @code{REPLACE} instead of @code{DELETE} + @code{INSERT}. +@xref{REPLACE, , @code{REPLACE}}. + +@item +The @code{FLUSH flush_option} statement. + +@item +The possibility to set variables in a statement with @code{:=}: +@example +SELECT @@a:=SUM(total),@@b=COUNT(*),@@a/@@b AS avg FROM test_table; +SELECT @@t1:=(@@t2:=1)+@@t3:=4,@@t1,@@t2,@@t3; +@end example + +@end itemize + +@node Differences from ANSI, ANSI mode, Extensions to ANSI, Compatibility +@subsection MySQL Differences Compared to ANSI SQL92 + +We try to make @strong{MySQL} follow the ANSI SQL standard and the +ODBC SQL standard, but in some cases @strong{MySQL} does some things +differently: + +@itemize @bullet +@item +@code{--} is only a comment if followed by a white space. @xref{Missing +comments}. +@item +For @code{VARCHAR} columns, trailing spaces are removed when the value is +stored. @xref{Bugs}. +@item +In some cases, @code{CHAR} columns are silently changed to @code{VARCHAR} +columns. @xref{Silent column changes}. +@item +Privileges for a table are not automatically revoked when you delete a +table. You must explicitly issue a @code{REVOKE} to revoke privileges for +a table. @xref{GRANT, , @code{GRANT}}. +@item +@code{NULL AND FALSE} will evaluate to @code{NULL} and not to @code{FALSE}. +This is because we don't think it's good to have to evaluate a lot of +extra conditions in this case. +@end itemize + +@node ANSI mode, Missing functions, Differences from ANSI, Compatibility +@subsection Running MySQL in ANSI Mode + +@cindex running, ANSI mode +@cindex ANSI mode, running + +If you start @code{mysqld} with the @code{--ansi} option, the following behavior +of @strong{MySQL} changes: + +@itemize @bullet +@item +@code{||} is string concatenation instead of @code{OR}. +@item +You can have any number of spaces between a function name and the @samp{(}. +This forces all function names to be treated as reserved words. +@item +@samp{"} will be an identifier quote character (like the @strong{MySQL} +@samp{`} quote character) and not a string quote character. +@item +@code{REAL} will be a synonym for @code{FLOAT} instead of a synonym of +@code{DOUBLE}. +@item +The default transaction isolation level is @code{SERIALIZABLE}. +@xref{SET TRANSACTION}. +@end itemize + +@node Missing functions, Standards, ANSI mode, Compatibility +@subsection Functionality Missing from MySQL + +@cindex missing functionality +@cindex functionality, missing + +The following functionality is missing in the current version of +@strong{MySQL}. For a prioritized list indicating when new extensions +may be added to @strong{MySQL}, you should consult +@uref{http://www.mysql.com/documentation/manual.php?section=TODO, the +online @strong{MySQL} TODO list}. That is the latest version of the TODO +list in this manual. @xref{TODO}. + +@menu +* Missing Sub-selects:: Sub-selects +* Missing SELECT INTO TABLE:: @code{SELECT INTO TABLE} +* Missing Transactions:: Transactions +* Missing Triggers:: Triggers +* Missing Foreign Keys:: Foreign Keys +* Broken Foreign KEY:: +* Missing Views:: Views +* Missing comments:: @samp{--} as the start of a comment +@end menu + +@node Missing Sub-selects, Missing SELECT INTO TABLE, Missing functions, Missing functions +@subsubsection Sub-selects + +@cindex sub-selects + +The following will not yet work in @strong{MySQL}: + +@example +SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); +SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); +SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id); +@end example + +However, in many cases you can rewrite the query without a sub-select: + +@example +SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; +SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL +@end example + +For more complicated subqueries you can often create temporary tables +to hold the subquery. In some cases, however this option will not +work. The most frequently encountered of these cases arises with +@code{DELETE} statements, for which standard SQL does not support joins +(except in sub-selects). For this situation there are two options +available until subqueries are supported by @strong{MySQL}. + +The first option is to use a procedural programming language (such as +Perl or PHP) to submit a @code{SELECT} query to obtain the primary keys +for the records to be deleted, and then use these values to construct +the @code{DELETE} statement (@code{DELETE FROM ... WHERE ... IN (key1, +key2, ...)}). + +The second option is to use interactive SQL to contruct a set of +@code{DELETE} statements automatically, using the @strong{MySQL} +extension @code{CONCAT()} (in lieu of the standard @code{||} operator). +For example: + +@example +SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';') + FROM tab1, tab2 + WHERE tab1.col1 = tab2.col2; +@end example + +You can place this query in a script file and redirect input from it to +the @code{mysql} command-line interpreter, piping its output back to a +second instance of the interpreter: + +@example +prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb +@end example + +@strong{MySQL} only supports @code{INSERT ... SELECT ...} and +@code{REPLACE ... SELECT ...} Independent sub-selects will probably +be available in Version 4.0. You can now use the function @code{IN()} in +other contexts, however. + +@node Missing SELECT INTO TABLE, Missing Transactions, Missing Sub-selects, Missing functions +@subsubsection @code{SELECT INTO TABLE} + +@findex SELECT INTO TABLE + +@strong{MySQL} doesn't yet support the Oracle SQL extension: +@code{SELECT ... INTO TABLE ...}. @strong{MySQL} supports instead the +ANSI SQL syntax @code{INSERT INTO ... SELECT ...}, which is basically +the same thing. @xref{INSERT SELECT}. + +@example +INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE +tblTemp1.fldOrder_ID > 100; +@end example + +Alternatively, you can use @code{SELECT INTO OUTFILE...} or @code{CREATE +TABLE ... SELECT} to solve your problem. + +@node Missing Transactions, Missing Triggers, Missing SELECT INTO TABLE, Missing functions +@subsubsection Transactions + +@cindex transactions, support + +As @strong{MySQL} does nowadays support transactions, the following +discussion is only valid if you are only using the non-transaction-safe +table types. @xref{COMMIT}. + +The question is often asked, by the curious and the critical, ``Why is +@strong{MySQL} not a transactional database?'' or ``Why does @strong{MySQL} +not support transactions?'' + +@strong{MySQL} has made a conscious decision to support another paradigm +for data integrity, ``atomic operations.'' It is our thinking and +experience that atomic operations offer equal or even better integrity +with much better performance. We, nonetheless, appreciate and understand +the transactional database paradigm and plan, within the next few releases, +to introduce transaction-safe tables on a per table basis. We will be +giving our users the possibility to decide if they need the speed of +atomic operations or if they need to use transactional features in their +applications. + +How does one use the features of @strong{MySQL} to maintain rigorous integrity +and how do these features compare with the transactional paradigm? + +First, in the transactional paradigm, if your applications are written +in a way that is dependent on the calling of ``rollback'' instead of +``commit'' in critical situations, then transactions are more +convenient. Moreover, transactions ensure that unfinished updates or +corrupting activities are not committed to the database; the server is +given the opportunity to do an automatic rollback and your database is +saved. + +@strong{MySQL}, in almost all cases, allows you to solve for potential +problems by including simple checks before updates and by running simple +scripts that check the databases for inconsistencies and automatically +repair or warn if such occurs. Note that just by using the +@strong{MySQL} log or even adding one extra log, one can normally fix +tables perfectly with no data integrity loss. + +Moreover, fatal transactional updates can be rewritten to be +atomic. In fact,we will go so far as to say that all integrity problems +that transactions solve can be done with @code{LOCK TABLES} or atomic updates, +ensuring that you never will get an automatic abort from the database, +which is a common problem with transactional databases. + +Not even transactions can prevent all loss if the server goes down. In +such cases even a transactional system can lose data. The difference +between different systems lies in just how small the time-lap is where +they could lose data. No system is 100% secure, only ``secure +enough.'' Even Oracle, reputed to be the safest of transactional +databases, is reported to sometimes lose data in such situations. + +To be safe with @strong{MySQL}, you only need to have backups and have +the update logging turned on. With this you can recover from any +situation that you could with any transactional database. It is, of +course, always good to have backups, independent of which database you +use. + +The transactional paradigm has its benefits and its drawbacks. Many +users and application developers depend on the ease with which they can +code around problems where an abort appears to be, or is necessary, and they +may have to do a little more work with @strong{MySQL} to either think +differently or write more. If you are new to the atomic operations +paradigm, or more familiar or more comfortable with transactions, do not +jump to the conclusion that @strong{MySQL} has not addressed these +issues. Reliability and integrity are foremost in our minds. Recent +estimates indicate that there are more than 1,000,000 @code{mysqld} servers +currently running, many of which are in production environments. We +hear very, very seldom from our users that they have lost any data, and +in almost all of those cases user error is involved. This is, in our +opinion, the best proof of @strong{MySQL}'s stability and reliability. + +Lastly, in situations where integrity is of highest importance, +@strong{MySQL}'s current features allow for transaction-level or better +reliability and integrity. If you lock tables with @code{LOCK TABLES}, all +updates will stall until any integrity checks are made. If you only obtain +a read lock (as opposed to a write lock), then reads and inserts are +still allowed to happen. The new inserted records will not be seen by +any of the clients that have a @code{READ} lock until they release their read +locks. With @code{INSERT DELAYED} you can queue inserts into a local queue, +until the locks are released, without having the client wait for the insert +to complete. @xref{INSERT DELAYED}. + +``Atomic,'' in the sense that we mean it, is nothing magical. It only means +that you can be sure that while each specific update is running, no other +user can interfere with it, and there will never be an automatic +rollback (which can happen on transaction based systems if you are not +very careful). @strong{MySQL} also guarantees that there will not be +any dirty reads. You can find some example of how to write atomic updates +in the commit-rollback section. @xref{Commit-rollback}. + +We have thought quite a bit about integrity and performance, and we +believe that our atomic operations paradigm allows for both high +reliability and extremely high performance, on the order of three to +five times the speed of the fastest and most optimally tuned of +transactional databases. We didn't leave out transactions because they +are hard to do. The main reason we went with atomic operations as +opposed to transactions is that by doing this we could apply many speed +optimizations that would not otherwise have been possible. + +Many of our users who have speed foremost in their minds are not at all +concerned about transactions. For them transactions are not an +issue. For those of our users who are concerned with or have wondered +about transactions vis-a-vis @strong{MySQL}, there is a ``@strong{MySQL} +way'' as we have outlined above. For those where safety is more +important than speed, we recommend them to use the @code{BDB}, +or @code{InnoDB} tables for all their critical data. @xref{Table types}. + +One final note: We are currently working on a safe replication schema +that we believe to be better than any commercial replication system we +know of. This system will work most reliably under the atomic +operations, non-transactional, paradigm. Stay tuned. + +@node Missing Triggers, Missing Foreign Keys, Missing Transactions, Missing functions +@subsubsection Stored Procedures and Triggers + +@cindex stored procedures and triggers, defined +@cindex procedures, stored +@cindex triggers, stored + + +A stored procedure is a set of SQL commands that can be compiled and stored +in the server. Once this has been done, clients don't need to keep reissuing +the entire query but can refer to the stored procedure. This provides better +performance because the query has to be parsed only once, and less information +needs to be sent between the server and the client. You can also raise the +conceptual level by having libraries of functions in the server. + +A trigger is a stored procedure that is invoked when a particular event +occurs. For example, you can install a stored procedure that is triggered +each time a record is deleted from a transaction table and that automatically +deletes the corresponding customer from a customer table when all his +transactions are deleted. -@node Installing, Compatibility, Licensing and Support, Top +The planned update language will be able to +handle stored procedures, but without triggers. Triggers usually slow +down everything, even queries for which they are not needed. + +To see when @strong{MySQL} might get stored procedures, see @ref{TODO}. + +@node Missing Foreign Keys, Broken Foreign KEY, Missing Triggers, Missing functions +@subsubsection Foreign Keys + +@cindex foreign keys +@cindex keys, foreign + +Note that foreign keys in SQL are not used to join tables, but are used +mostly for checking referential integrity (foreign key constraints). If +you want to get results from multiple tables from a @code{SELECT} +statement, you do this by joining tables: + +@example +SELECT * from table1,table2 where table1.id = table2.id; +@end example + +@xref{JOIN, , @code{JOIN}}. @xref{example-Foreign keys}. + +The @code{FOREIGN KEY} syntax in @strong{MySQL} exists only for compatibility +with other SQL vendors' @code{CREATE TABLE} commands; it doesn't do +anything. The @code{FOREIGN KEY} syntax without @code{ON DELETE ...} is +mostly used for documentation purposes. Some ODBC applications may use this +to produce automatic @code{WHERE} clauses, but this is usually easy to +override. @code{FOREIGN KEY} is sometimes used as a constraint check, but +this check is unnecessary in practice if rows are inserted into the tables in +the right order. @strong{MySQL} only supports these clauses because some +applications require them to exist (regardless of whether or not they +work). + +In @strong{MySQL}, you can work around the problem of @code{ON DELETE +...} not being implemented by adding the appropriate @code{DELETE} statement to +an application when you delete records from a table that has a foreign key. +In practice this is as quick (in some cases quicker) and much more portable +than using foreign keys. + +In the near future we will extend the @code{FOREIGN KEY} implementation so +that at least the information will be saved in the table specification file +and may be retrieved by @code{mysqldump} and ODBC. At a later stage we will +implement the foreign key constraints for application that can't easily be +coded to avoid them. + +@menu +* Broken Foreign KEY:: Reasons NOT to use foreign keys constraints +@end menu + +@node Broken Foreign KEY, Missing Views, Missing Foreign Keys, Missing functions +@subsubsection Reasons NOT to Use Foreign Keys constraints + +@cindex foreign keys, reasons not to use + +There are so many problems with foreign key constraints that we don't +know where to start: + +@itemize @bullet +@item +Foreign key constraints make life very complicated, because the foreign +key definitions must be stored in a database and implementing them would +destroy the whole ``nice approach'' of using files that can be moved, +copied, and removed. + +@item +The speed impact is terrible for @code{INSERT} and @code{UPDATE} +statements, and in this case almost all @code{FOREIGN KEY} constraint +checks are useless because you usually insert records in the right +tables in the right order, anyway. + +@item +There is also a need to hold locks on many more tables when updating one +table, because the side effects can cascade through the entire database. It's +MUCH faster to delete records from one table first and subsequently delete +them from the other tables. + +@item +You can no longer restore a table by doing a full delete from the table +and then restoring all records (from a new source or from a backup). + +@item +If you use foreign key constraints you can't dump and restore tables +unless you do so in a very specific order. + +@item +It's very easy to do ``allowed'' circular definitions that make the +tables impossible to re-create each table with a single create statement, +even if the definition works and is usable. + +@item +It's very easy to overlook @code{FOREIGN KEY ... ON DELETE} rules when +one codes an application. It's not unusual that one loses a lot of +important information just because a wrong or misused @code{ON DELETE} rule. +@end itemize + +The only nice aspect of @code{FOREIGN KEY} is that it gives ODBC and some +other client programs the ability to see how a table is connected and to use +this to show connection diagrams and to help in building applications. + +@strong{MySQL} will soon store @code{FOREIGN KEY} definitions so that a +client can ask for and receive an answer about how the original +connection was made. The current @file{.frm} file format does not have +any place for it. At a later stage we will implement the foreign key +constraints for application that can't easily be coded to avoid them. + +@node Missing Views, Missing comments, Broken Foreign KEY, Missing functions +@subsubsection Views + +@cindex views + +@strong{MySQL} doesn't yet support views, but we plan to implement these +to about 4.1. + +Views are mostly useful for letting users access a set of relations as one +table (in read-only mode). Many SQL databases don't allow one to update +any rows in a view, but you have to do the updates in the separate tables. + +As @strong{MySQL} is mostly used in applications and on web system where +the application writer has full control on the database usage, most of +our users haven't regarded views to be very important. (At least no one +has been interested enough in this to be prepared to finance the +implementation of views). + +One doesn't need views in @strong{MySQL} to restrict access to columns +as @strong{MySQL} has a very sophisticated privilege +system. @xref{Privilege system}. + +@node Missing comments, , Missing Views, Missing functions +@subsubsection @samp{--} as the Start of a Comment + +@cindex comments, starting +@cindex starting, comments + +Some other SQL databases use @samp{--} to start comments. @strong{MySQL} +has @samp{#} as the start comment character, even if the @code{mysql} +command-line tool removes all lines that start with @samp{--}. +You can also use the C comment style @code{/* this is a comment */} with +@strong{MySQL}. +@xref{Comments}. + +@strong{MySQL} Version 3.23.3 and above supports the @samp{--} comment style +only if the comment is followed by a space. This is because this +degenerate comment style has caused many problems with automatically +generated SQL queries that have used something like the following code, +where we automatically insert the value of the payment for +@code{!payment!}: + +@example +UPDATE tbl_name SET credit=credit-!payment! +@end example + +What do you think will happen when the value of @code{payment} is negative? + +Because @code{1--1} is legal in SQL, we think it is terrible that +@samp{--} means start comment. + +In @strong{MySQL} Version 3.23 you can, however, use: +@code{1-- This is a comment} + +The following discussion only concerns you if you are running a @strong{MySQL} +version earlier than Version 3.23: + +If you have a SQL program in a text file that contains @samp{--} comments +you should use: + +@example +shell> replace " --" " #" < text-file-with-funny-comments.sql \ + | mysql database +@end example + +instead of the usual: + +@example +shell> mysql database < text-file-with-funny-comments.sql +@end example + +You can also edit the command file ``in place'' to change the @samp{--} +comments to @samp{#} comments: + +@example +shell> replace " --" " #" -- text-file-with-funny-comments.sql +@end example + +Change them back with this command: + +@example +shell> replace " #" " --" -- text-file-with-funny-comments.sql +@end example + +@node Standards, Commit-rollback, Missing functions, Compatibility +@subsection What Standards Does MySQL Follow? + +Entry level SQL92. ODBC levels 0-2. + +@node Commit-rollback, Bugs, Standards, Compatibility +@subsection How to Cope Without @code{COMMIT}/@code{ROLLBACK} + +@findex COMMIT +@findex ROLLBACK +@cindex transaction-safe tables +@cindex tables, updating +@cindex updating, tables +@cindex @code{BDB} tables +@cindex @code{InnoDB} tables + +The following mostly applies only for @code{ISAM}, @code{MyISAM}, and +@code{HEAP} tables. If you only use transaction-safe tables (@code{BDB}, +or @code{InnoDB} tables) in an an update, you can do +@code{COMMIT} and @code{ROLLBACK} also with @strong{MySQL}. +@xref{COMMIT}. + +The problem with handling @code{COMMIT}-@code{ROLLBACK} efficiently with +the above table types would require a completely different table layout +than @strong{MySQL} uses today. The table type would also need extra +threads that do automatic cleanups on the tables, and the disk usage +would be much higher. This would make these table types about 2-4 times +slower than they are today. + +For the moment, we prefer implementing the SQL server language (something +like stored procedures). With this you would very seldom really need +@code{COMMIT}-@code{ROLLBACK.} This would also give much better performance. + +Loops that need transactions normally can be coded with the help of +@code{LOCK TABLES}, and you don't need cursors when you can update records +on the fly. + +We at TcX had a greater need for a real fast database than a 100% +general database. Whenever we find a way to implement these features without +any speed loss, we will probably do it. For the moment, there are many more +important things to do. Check the TODO for how we prioritize things at +the moment. (Customers with higher levels of support can alter this, so +things may be reprioritized.) + +The current problem is actually @code{ROLLBACK}. Without +@code{ROLLBACK}, you can do any kind of @code{COMMIT} action with +@code{LOCK TABLES}. To support @code{ROLLBACK} with the above table +types, @strong{MySQL} would have to be changed to store all old records +that were updated and revert everything back to the starting point if +@code{ROLLBACK} was issued. For simple cases, this isn't that hard to do +(the current @code{isamlog} could be used for this purpose), but it +would be much more difficult to implement @code{ROLLBACK} for +@code{ALTER/DROP/CREATE TABLE}. + +To avoid using @code{ROLLBACK}, you can use the following strategy: + +@enumerate +@item +Use @code{LOCK TABLES ...} to lock all the tables you want to access. +@item +Test conditions. +@item +Update if everything is okay. +@item +Use @code{UNLOCK TABLES} to release your locks. +@end enumerate + +This is usually a much faster method than using transactions with possible +@code{ROLLBACK}s, although not always. The only situation this solution +doesn't handle is when someone kills the threads in the middle of an +update. In this case, all locks will be released but some of the updates may +not have been executed. + +You can also use functions to update records in a single operation. +You can get a very efficient application by using the following techniques: + +@itemize @bullet +@item Modify fields relative to their current value. +@item Update only those fields that actually have changed. +@end itemize + +For example, when we are doing updates to some customer information, we +update only the customer data that has changed and test only that none of +the changed data, or data that depend on the changed data, has changed +compared to the original row. The test for changed data is done with the +@code{WHERE} clause in the @code{UPDATE} statement. If the record wasn't +updated, we give the client a message: "Some of the data you have changed +have been changed by another user". Then we show the old row versus the new +row in a window, so the user can decide which version of the customer record +he should use. + +This gives us something that is similar to column locking but is actually +even better, because we only update some of the columns, using values that +are relative to their current values. This means that typical @code{UPDATE} +statements look something like these: + +@example +UPDATE tablename SET pay_back=pay_back+'relative change'; + +UPDATE customer + SET + customer_date='current_date', + address='new address', + phone='new phone', + money_he_owes_us=money_he_owes_us+'new_money' + WHERE + customer_id=id AND address='old address' AND phone='old phone'; +@end example + +As you can see, this is very efficient and works even if another client has +changed the values in the @code{pay_back} or @code{money_he_owes_us} columns. + +@findex mysql_insert_id() +@findex LAST_INSERT_ID() +In many cases, users have wanted @code{ROLLBACK} and/or @code{LOCK +TABLES} for the purpose of managing unique identifiers for some tables. This +can be handled much more efficiently by using an @code{AUTO_INCREMENT} column +and either the SQL function @code{LAST_INSERT_ID()} or the C API function +@code{mysql_insert_id()}. @xref{mysql_insert_id, , @code{mysql_insert_id()}}. + +@cindex rows, locking +At @strong{MySQL AB}, we have never had any need for row-level locking +because we have always been able to code around it. Some cases really need +row locking, but they are very few. If you want row-level locking, you +can use a flag column in the table and do something like this: + +@example +UPDATE tbl_name SET row_flag=1 WHERE id=ID; +@end example + +@strong{MySQL} returns 1 for the number of affected rows if the row was +found and @code{row_flag} wasn't already 1 in the original row. + +You can think of it as @strong{MySQL} changed the above query to: + +@example +UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1; +@end example + +@node Bugs, , Commit-rollback, Compatibility +@subsection Known errors and design deficiencies in MySQL + +@cindex bugs, known +@cindex errors, known +@cindex design, issues +@cindex known errors + +The following problems are known and have a very high priority to get +fixed: + +@itemize @bullet +@item +@code{ANALYZE TABLE} on a BDB table may in some case make the table +unusable until one has restarted @code{mysqld}. When this happens you will +see errors like the following in the @strong{MySQL} error file: + +@example +001207 22:07:56 bdb: log_flush: LSN past current end-of-log +@end example + +@item +Don't execute @code{ALTER TABLE} on a @code{BDB} table on which you are +running not completed multi-statement transactions. (The transaction +will probably be ignored). + +@item +@code{ANALYZE TABLE}, @code{OPTIMIZE TABLE} and @code{REPAIR TABLE} may +cause problems on tables for which you are using @code{INSERT DELAYED}. + +@item +Doing a @code{LOCK TABLE ..} and @code{FLUSH TABLES ..} doesn't +guarantee that there isn't a half-finished transaction in progress on the +table. + +@item +BDB tables are a bit slow to open. If you have many BDB tables in a +database, it will take a long time to use the @code{mysql} client on the +database if you are not using the @code{-A} option or if you are using +@code{rehash}. This is especially notable when you have a big table +cache. + +@item +Th current replication protocol cannot deal with @code{LOAD DATA INFILE} +and line terminator characters of more than 1 character. +@end itemize + +The following problems are known and will be fixed in due time: + +@itemize @bullet +@item +For the moment @code{MATCH} only works with @code{SELECT} statements. +@item +When using @code{SET CHARACTER SET}, one can't use translated +characters in database, table and column names. +@item +@code{DELETE FROM merge_table} used without a @code{WHERE} +will only clear the mapping for the table, not delete everything in the +mapped tables +@item +You cannot build in another directory when using +MIT-pthreads. Because this requires changes to MIT-pthreads, we are not +likely to fix this. +@item +@code{BLOB} values can't ``reliably'' be used in @code{GROUP BY} or +@code{ORDER BY} or @code{DISTINCT}. Only the first @code{max_sort_length} +bytes (default 1024) are used when comparing @code{BLOB}bs in these cases. +This can be changed with the @code{-O max_sort_length} option to +@code{mysqld}. A workaround for most cases is to use a substring: +@code{SELECT DISTINCT LEFT(blob,2048) FROM tbl_name}. +@item +Calculation is done with @code{BIGINT} or @code{DOUBLE} (both are +normally 64 bits long). It depends on the function which precision one +gets. The general rule is that bit functions are done with @code{BIGINT} +precision, @code{IF}, and @code{ELT()} with @code{BIGINT} or @code{DOUBLE} +precision and the rest with @code{DOUBLE} precision. One should try to +avoid using bigger unsigned long long values than 63 bits +(9223372036854775807) for anything else than bit fields! +@item +All string columns, except @code{BLOB} and @code{TEXT} columns, automatically +have all trailing spaces removed when retrieved. For @code{CHAR} types this +is okay, and may be regarded as a feature according to ANSI SQL92. The bug is +that in @strong{MySQL}, @code{VARCHAR} columns are treated the same way. +@item +You can only have up to 255 @code{ENUM} and @code{SET} columns in one table. +@item +@code{safe_mysqld} re-directs all messages from @code{mysqld} to the +@code{mysqld} log. One problem with this is that if you execute +@code{mysqladmin refresh} to close and reopen the log, +@code{stdout} and @code{stderr} are still redirected to the old log. +If you use @code{--log} extensively, you should edit @code{safe_mysqld} to +log to @file{'hostname'.err} instead of @file{'hostname'.log} so you can +easily reclaim the space for the old log by deleting the old one and +executing @code{mysqladmin refresh}. +@item +In the @code{UPDATE} statement, columns are updated from left to right. +If you refer to an updated column, you will get the updated value instead of the +original value. For example: +@example +mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1; +@end example +will update @code{KEY} with @code{2} instead of with @code{1}. +@item +You can't use temporary tables more than once in the same query. +For example, the following doesn't work. + +@example +select * from temporary_table, temporary_table as t2; +@end example + +@item +@code{RENAME} doesn't work with @code{TEMPORARY} tables. + +@item +The optimizer may handle @code{DISTINCT} differently if you are using +'hidden' columns in a join or not. In a join, hidden columns are +counted as part of the result (even if they are not shown) while in +normal queries hidden columns doesn't participate in the @code{DISTINCT} +comparison. We will probably change this in the future to never compare +the hidden columns when executing @code{DISTINCT} + +An example of this is: + +@example +SELECT DISTINCT mp3id FROM band_downloads WHERE userid = 9 ORDER BY id +DESC; + +and + +SELECT DISTINCT band_downloads.mp3id, FROM band_downloads,band_mp3 +WHERE band_downloads.userid = 9 AND band_mp3.id = band_downloads.mp3id +ORDER BY band_downloads.id DESC; +@end example + +In the second case you may in @strong{MySQL} 3.23.x get two identical rows +in the result set (because the hidden 'id' column may differ). + +Note that the this only happens for queries where you don't have the +ORDER BY columns in the result, something that is you are not allowed +to do in ANSI SQL. + +@item +Because @strong{MySQL} allows you to work with table types that doesn't +support transactions (and thus can't @code{rollback} data) some things +behaves a little different in @strong{MySQL} than in other SQL servers: +(This is just to ensure that @strong{MySQL} never need to do a rollback +for a SQL command). This may be a little awkward at times as column +Because @strong{MySQL} allows you to work with table types that don't +support transactions, and thus can't @code{rollback} data, some things +behave a little differently in @strong{MySQL} than in other SQL servers. +This is just to ensure that @strong{MySQL} never need to do a rollback +for a SQL command. This may be a little awkward at times as column +values must be checked in the application, but this will actually give +you a nice speed increase as it allows @strong{MySQL} to do some +optimizations that otherwise would be very hard to do. + +If you set a column to an incorrect value, @strong{MySQL} will, instead of +doing a rollback, store the @code{best possible value} in the column: + +@itemize @bullet +@item +If you try to store a value outside the range in a numerical column, +@strong{MySQL} will instead store the smallest or biggest possible value in +the column. +@item +If you try to store a string that doesn't start with a number into a +numerical column, @strong{MySQL} will store 0 into it. +@item +If you try to store @code{NULL} into a column that doesn't take +@code{NULL} values, @strong{MySQL} will store 0 or @code{''} (empty +string) in it instead. (This behavior can, however, be changed with the +-DDONT_USE_DEFAULT_FIELDS compile option). +@item +@strong{MySQL} allows you to store some wrong date values into +@code{DATE} and @code{DATETIME} columns. (Like 2000-02-31 or 2000-02-00). +If the date is totally wrong, @strong{MySQL} will store the special +0000-00-00 date value in the column. +@item +If you set an @code{enum} to an unsupported value, it will be set to +the error value 'empty string', with numeric value 0. +@end itemize + +@item +If you execute a @code{PROCEDURE} on a query that returns an empty set, +in some cases the @code{PROCEDURE} will not transform the columns. +@item +Creation of a table of type @code{MERGE} doesn't check if the underlying +tables are of compatible types. +@item +@strong{MySQL} can't yet handle @code{NaN}, @code{-Inf} and @code{Inf} +values in double. Using these will cause problems when trying to export +and import data. We should as an intermediate solution change @code{NaN} to +@code{NULL} (if possible) and @code{-Inf} and @code{Inf} to the +Minimum respective maximum possible @code{double} value. +@item +@code{LIMIT} on negative numbers are treated as big positive numbers. +@end itemize + +The following are known bugs in earlier versions of @strong{MySQL}: + +@itemize @bullet +@item +You can get a hung thread if you do a @code{DROP TABLE} on a table that is +one among many tables that is locked with @code{LOCK TABLES}. + +@item +In the following case you can get a core dump: +@enumerate +@item +Delayed insert handler has pending inserts to a table. +@item +@code{LOCK table} with @code{WRITE} +@item +@code{FLUSH TABLES} +@end enumerate + +@item +Before @strong{MySQL} Version 3.23.2 an @code{UPDATE} that updated a key with +a @code{WHERE} on the same key may have failed because the key was used to +search for records and the same row may have been found multiple times: + +@example +UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100; +@end example + +A workaround is to use: + +@example +mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100; +@end example + +This will work because @strong{MySQL} will not use index on expressions in +the @code{WHERE} clause. +@item +Before @strong{MySQL} Version 3.23, all numeric types where treated as fixed-point +fields. That means you had to specify how many decimals a floating-point +field shall have. All results were returned with the correct number of +decimals. +@end itemize + +For platform-specific bugs, see the sections about compiling and porting. + +@node Comparisons, TODO, Compatibility, Introduction +@section How MySQL Compares to Other Databases + +@cindex databases, MySQL vs. others +@cindex comparisons, MySQL vs. others + +@menu +* Compare mSQL:: How @strong{MySQL} compares to @code{mSQL} +* Protocol differences:: +* Compare PostgreSQL:: How @strong{MySQL} compares with PostgreSQL +* MySQL-PostgreSQL features:: +@end menu + +This section compares @strong{MySQL} to other popular databases. + +This section has been written by the @strong{MySQL} developers, so it +should be read with that in mind. There are no factual errors contained +in this section that we know of. If you find something which you believe +to be an error, please contact us about it at @email{docs@@mysql.com}. + +For a list of all supported limits, functions, and types, see the +@code{crash-me} Web page at +@uref{http://www.mysql.com/information/crash-me.php}. + +@node Compare mSQL, Protocol differences, Comparisons, Comparisons +@subsection How MySQL Compares to @code{mSQL} + +@table @strong +@item Performance + +For a true comparison of speed, consult the growing @strong{MySQL} benchmark +suite. @xref{Benchmarks}. + +Because there is no thread creation overhead, a small parser, few features, and +simple security, @code{mSQL} should be quicker at: + +@itemize @bullet +@item +Tests that perform repeated connects and disconnects, running a very simple +query during each connection. +@item +@code{INSERT} operations into very simple tables with few columns and keys. +@item +@code{CREATE TABLE} and @code{DROP TABLE}. +@item +@code{SELECT} on something that isn't an index. (A table scan is very +easy.) +@end itemize + +Because these operations are so simple, it is hard to be better at them when +you have a higher startup overhead. After the connection is established, +@strong{MySQL} should perform much better. + +On the other hand, @strong{MySQL} is much faster than @code{mSQL} (and +most other SQL implementations) on the following: + +@itemize @bullet +@item +Complex @code{SELECT} operations. +@item +Retrieving large results (@strong{MySQL} has a better, faster, and safer +protocol). +@item +Tables with variable-length strings, because @strong{MySQL} has more efficient +handling and can have indexes on @code{VARCHAR} columns. +@item +Handling tables with many columns. +@item +Handling tables with large record lengths. +@item +@code{SELECT} with many expressions. +@item +@code{SELECT} on large tables. +@item +Handling many connections at the same time. @strong{MySQL} is fully +multi-threaded. Each connection has its own thread, which means that +no thread has to wait for another (unless a thread is modifying +a table another thread wants to access). In @code{mSQL}, once one connection +is established, all others must wait until the first has finished, regardless +of whether the connection is running a query that is short or long. When the +first connection terminates, the next can be served, while all the others wait +again, etc. +@item +Joins. +@code{mSQL} can become pathologically slow if you change the order of tables +in a @code{SELECT}. In the benchmark suite, a time more than 15000 times +slower than @strong{MySQL} was seen. This is due to @code{mSQL}'s lack of a +join optimizer to order tables in the optimal order. However, if you put the +tables in exactly the right order in @code{mSQL}2 and the @code{WHERE} is +simple and uses index columns, the join will be relatively fast! +@xref{Benchmarks}. +@item +@code{ORDER BY} and @code{GROUP BY}. +@item +@code{DISTINCT}. +@item +Using @code{TEXT} or @code{BLOB} columns. +@end itemize + +@item SQL Features + +@itemize @bullet +@item @code{GROUP BY} and @code{HAVING}. +@code{mSQL} does not support @code{GROUP BY} at all. +@strong{MySQL} supports a full @code{GROUP BY} with both @code{HAVING} and +the following functions: @code{COUNT()}, @code{AVG()}, @code{MIN()}, +@code{MAX()}, @code{SUM()}, and @code{STD()}. @code{COUNT(*)} is optimized to +return very quickly if the @code{SELECT} retrieves from one table, no other +columns are retrieved, and there is no @code{WHERE} clause. @code{MIN()} and +@code{MAX()} may take string arguments. + +@item @code{INSERT} and @code{UPDATE} with calculations. +@strong{MySQL} can do calculations in an @code{INSERT} or @code{UPDATE}. +For example: +@example +mysql> UPDATE SET x=x*10+y WHERE x<20; +@end example + +@item Aliasing. +@strong{MySQL} has column aliasing. + +@item Qualifying column names. +In @strong{MySQL}, if a column name is unique among the tables used in a +query, you do not have to use the full qualifier. + +@item @code{SELECT} with functions. +@strong{MySQL} has many functions (too many to list here; see @ref{Functions}). + +@end itemize + +@item Disk Space Efficiency +That is, how small can you make your tables? + +@strong{MySQL} has very precise types, so you can create tables that take +very little space. An example of a useful @strong{MySQL} datatype is the +@code{MEDIUMINT} that is 3 bytes long. If you have 100,000,000 records, +saving even one byte per record is very important. + +@code{mSQL2} has a more limited set of column types, so it is +more difficult to get small tables. +@item Stability +This is harder to judge objectively. For a discussion of @strong{MySQL} +stability, see @ref{Stability}. + +We have no experience with @code{mSQL} stability, so we cannot say +anything about that. + +@item Price +Another important issue is the license. @strong{MySQL} has a +more flexible license than @code{mSQL}, and is also less expensive than +@code{mSQL}. Whichever product you choose to use, remember to at least +consider paying for a license or e-mail support. (You are required to get +a license if you include @strong{MySQL} with a product that you sell, +of course.) + +@item Perl Interfaces +@strong{MySQL} has basically the same interfaces to Perl as @code{mSQL} with +some added features. + +@item JDBC (Java) +@strong{MySQL} currently has a lot of different JDBC drivers: +@itemize @bullet +@item +The mm driver: A type 4 JDBC driver by Mark Matthews +@email{mmatthew@@ecn.purdue.edu}. This is released under the LGPL. +@item +The Resin driver. This is a commercial JDBC driver released under open +source. @uref{http://www.caucho.com/projects/jdbc-mysql/index.xtp} +@item +The gwe driver: A Java interface by GWE technologies (not supported anymore). +@item +The jms driver: An improved gwe driver by Xiaokun Kelvin ZHU +@email{X.Zhu@@brad.ac.uk} (not supported anymore). +@item +The twz driver: A type 4 JDBC driver by Terrence W. Zellers +@email{zellert@@voicenet.com}. This is commercial but is free for private +and educational use (not supported anymore). +@end itemize + +The recommended driver is the mm driver. The Resin driver may also be +good (at least the benchmarks looks good), but we haven't received that much +information about this yet. + +We know that @code{mSQL} has a JDBC driver, but we have too little experience +with it to compare. + +@item Rate of Development +@strong{MySQL} has a very small team of developers, but we are quite +used to coding C and C++ very rapidly. Because threads, functions, +@code{GROUP BY}, and so on are still not implemented in @code{mSQL}, it +has a lot of catching up to do. To get some perspective on this, you +can view the @code{mSQL} @file{HISTORY} file for the last year and +compare it with the News section of the @strong{MySQL} Reference Manual +(@pxref{News}). It should be pretty obvious which one has developed +most rapidly. + +@item Utility Programs +Both @code{mSQL} and @strong{MySQL} have many interesting third-party +tools. Because it is very easy to port upward (from @code{mSQL} to +@strong{MySQL}), almost all the interesting applications that are available for +@code{mSQL} are also available for @strong{MySQL}. + +@strong{MySQL} comes with a simple @code{msql2mysql} program that fixes +differences in spelling between @code{mSQL} and @strong{MySQL} for the +most-used C API functions. +For example, it changes instances of @code{msqlConnect()} to +@code{mysql_connect()}. Converting a client program from @code{mSQL} to +@strong{MySQL} usually takes a couple of minutes. +@end table + +@menu +* Using mSQL tools:: How to convert @code{mSQL} tools for @strong{MySQL} +@end menu + +@node Using mSQL tools, , Compare mSQL, Compare mSQL +@subsubsection How to Convert @code{mSQL} Tools for MySQL + +@cindex MySQL tools, conversion +@cindex converting, tools +@cindex tools, converting + +According to our experience, it would just take a few hours to convert tools +such as @code{msql-tcl} and @code{msqljava} that use the +@code{mSQL} C API so that they work with the @strong{MySQL} C API. + +The conversion procedure is: + +@enumerate +@item +Run the shell script @code{msql2mysql} on the source. This requires the +@code{replace} program, which is distributed with @strong{MySQL}. +@item +Compile. +@item +Fix all compiler errors. +@end enumerate + +Differences between the @code{mSQL} C API and the @strong{MySQL} C API are: +@itemize @bullet +@item +@strong{MySQL} uses a @code{MYSQL} structure as a connection type (@code{mSQL} +uses an @code{int}). +@item +@code{mysql_connect()} takes a pointer to a @code{MYSQL} structure as a +parameter. It is easy to define one globally or to use @code{malloc()} to get +one. +@code{mysql_connect()} also takes two parameters for specifying the user and +password. You may set these to @code{NULL, NULL} for default use. +@item +@code{mysql_error()} takes the @code{MYSQL} structure as a parameter. Just add +the parameter to your old @code{msql_error()} code if you are porting old code. +@item +@strong{MySQL} returns an error number and a text error message for all +errors. @code{mSQL} returns only a text error message. +@item +Some incompatibilities exist as a result of @strong{MySQL} supporting +multiple connections to the server from the same process. +@end itemize + +@cindex communications protocols +@cindex mSQL vs. MySQL +@node Protocol differences, Compare PostgreSQL, Compare mSQL, Comparisons +@subsection How @code{mSQL} and MySQL Client/Server Communications Protocols Differ + +There are enough differences that it is impossible (or at least not easy) +to support both. + +The most significant ways in which the @strong{MySQL} protocol differs +from the @code{mSQL} protocol are listed below: + +@itemize @bullet +@item +A message buffer may contain many result rows. +@item +The message buffers are dynamically enlarged if the query or the +result is bigger than the current buffer, up to a configurable server and +client limit. +@item +All packets are numbered to catch duplicated or missing packets. +@item +All column values are sent in ASCII. The lengths of columns and rows are sent +in packed binary coding (1, 2, or 3 bytes). +@item +@strong{MySQL} can read in the result unbuffered (without having to store the +full set in the client). +@item +If a single read/write takes more than 30 seconds, the server closes +the connection. +@item +If a connection is idle for 8 hours, the server closes the connection. +@end itemize + +@menu +* Syntax differences:: +@end menu + +@node Syntax differences, , Protocol differences, Protocol differences +@subsubsection How @code{mSQL} 2.0 SQL Syntax Differs from MySQL + +@noindent +@strong{Column types} + +@table @code +@item @strong{MySQL} +Has the following additional types (among others; +@pxref{CREATE TABLE, , @code{CREATE TABLE}}): +@itemize @bullet +@item +@code{ENUM} type for one of a set of strings. +@item +@code{SET} type for many of a set of strings. +@item +@code{BIGINT} type for 64-bit integers. +@end itemize +@item +@strong{MySQL} also supports +the following additional type attributes: +@itemize @bullet +@item +@code{UNSIGNED} option for integer columns. +@item +@code{ZEROFILL} option for integer columns. +@item +@code{AUTO_INCREMENT} option for integer columns that are a +@code{PRIMARY KEY}. +@xref{mysql_insert_id, , @code{mysql_insert_id()}}. +@item +@code{DEFAULT} value for all columns. +@end itemize +@item mSQL2 +@code{mSQL} column types correspond to the @strong{MySQL} types shown below: +@multitable @columnfractions .15 .85 +@item @code{mSQL} @strong{type} @tab @strong{Corresponding @strong{MySQL} type} +@item @code{CHAR(len)} @tab @code{CHAR(len)} +@item @code{TEXT(len)} @tab @code{TEXT(len)}. @code{len} is the maximal length. +And @code{LIKE} works. +@item @code{INT} @tab @code{INT}. With many more options! +@item @code{REAL} @tab @code{REAL}. Or @code{FLOAT}. Both 4- and 8-byte versions are available. +@item @code{UINT} @tab @code{INT UNSIGNED} +@item @code{DATE} @tab @code{DATE}. Uses ANSI SQL format rather than @code{mSQL}'s own format. +@item @code{TIME} @tab @code{TIME} +@item @code{MONEY} @tab @code{DECIMAL(12,2)}. A fixed-point value with two decimals. +@end multitable +@end table + +@noindent +@strong{Index Creation} + +@table @code +@item @strong{MySQL} +Indexes may be specified at table creation time with the @code{CREATE TABLE} +statement. +@item mSQL +Indexes must be created after the table has been created, with separate +@code{CREATE INDEX} statements. +@end table + +@noindent +@strong{To Insert a Unique Identifier into a Table} + +@table @code +@item @strong{MySQL} +Use @code{AUTO_INCREMENT} as a column type +specifier. +@xref{mysql_insert_id, , @code{mysql_insert_id()}}. +@item mSQL +Create a @code{SEQUENCE} on a table and select the @code{_seq} column. +@end table + +@noindent +@strong{To Obtain a Unique Identifier for a Row} + +@table @code +@item @strong{MySQL} +Add a @code{PRIMARY KEY} or @code{UNIQUE} key to the table and use this. +New in Version 3.23.11: If the @code{PRIMARY} or @code{UNIQUE} key consists of only one +column and this is of type integer, one can also refer to it as +@code{_rowid}. +@item mSQL +Use the @code{_rowid} column. Observe that @code{_rowid} may change over time +depending on many factors. +@end table + +@noindent +@strong{To Get the Time a Column Was Last Modified} + +@table @code +@item @strong{MySQL} +Add a @code{TIMESTAMP} column to the table. This column is automatically set +to the current date and time for @code{INSERT} or @code{UPDATE} statements if +you don't give the column a value or if you give it a @code{NULL} value. + +@item mSQL +Use the @code{_timestamp} column. +@end table + +@noindent +@strong{@code{NULL} Value Comparisons} + +@table @code +@item @strong{MySQL} +@strong{MySQL} follows +ANSI SQL, and a comparison with @code{NULL} is always @code{NULL}. +@item mSQL +In @code{mSQL}, @code{NULL = NULL} is TRUE. You +must change @code{=NULL} to @code{IS NULL} and @code{<>NULL} to +@code{IS NOT NULL} when porting old code from @code{mSQL} to @strong{MySQL}. +@end table + +@noindent +@strong{String Comparisons} + +@table @code +@item @strong{MySQL} +Normally, string comparisons are performed in case-independent fashion with +the sort order determined by the current character set (ISO-8859-1 Latin1 by +default). If you don't like this, declare your columns with the +@code{BINARY} attribute, which causes comparisons to be done according to the +ASCII order used on the @strong{MySQL} server host. +@item mSQL +All string comparisons are performed in case-sensitive fashion with +sorting in ASCII order. +@end table + +@noindent +@strong{Case-insensitive Searching} + +@table @code +@item @strong{MySQL} +@code{LIKE} is a case-insensitive or case-sensitive operator, depending on +the columns involved. If possible, @strong{MySQL} uses indexes if the +@code{LIKE} argument doesn't start with a wild-card character. +@item mSQL +Use @code{CLIKE}. +@end table + +@noindent +@strong{Handling of Trailing Spaces} + +@table @code +@item @strong{MySQL} +Strips all spaces at the end of @code{CHAR} and @code{VARCHAR} +columns. Use a @code{TEXT} column if this behavior is not desired. +@item mSQL +Retains trailing space. +@end table + +@noindent +@strong{@code{WHERE} Clauses} + +@table @code +@item @strong{MySQL} +@strong{MySQL} correctly prioritizes everything (@code{AND} is evaluated +before @code{OR}). To get @code{mSQL} behavior in @strong{MySQL}, use +parentheses (as shown in an example below). +@item mSQL +Evaluates everything from left to right. This means that some logical +calculations with more than three arguments cannot be expressed in any +way. It also means you must change some queries when you upgrade to +@strong{MySQL}. You do this easily by adding parentheses. Suppose you +have the following @code{mSQL} query: +@example +mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4; +@end example +To make @strong{MySQL} evaluate this the way that @code{mSQL} would, +you must add parentheses: +@example +mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4)))); +@end example +@end table + +@noindent +@strong{Access Control} + +@table @code +@item @strong{MySQL} +Has tables to store grant (permission) options per user, host, and +database. @xref{Privileges}. +@item mSQL +Has a file @file{mSQL.acl} in which you can grant read/write privileges for +users. +@item +@end table + +@node Compare PostgreSQL, MySQL-PostgreSQL features, Protocol differences, Comparisons +@subsection How MySQL Compares to PostgreSQL + +@cindex PostgreSQL vs. MySQL, overview + +When reading the following, please note that both products are +continually evolving. We at @strong{MySQL AB} and the PostgreSQL +developers are both working on making our respective database as good as +possible, so we are both a serious choice to any commercial database. + +The following comparison is made by us at MySQL AB. We have tried to be +as accurate and fair as possible, but because we don't have a full +knowledge of all PostgreSQL features while we know MySQL througly, we +may have got some things wrong. We will however correct these when they +come to our attention. + +We would first like to note that @code{PostgreSQL} and @strong{MySQL} +are both widely used products, but with different design goals, even if +we are both striving to be ANSI SQL compatible. This means that for +some applications @strong{MySQL} is more suitable and for others +@code{PostgreSQL} is more suitable. When choosing which database to +use, you should first check if the database's feature set satisfies your +application. If you need speed, @strong{MySQL} is probably your best +choice. If you need some of the extra features that only @code{PostgreSQL} +can offer, you should use @code{PostgreSQL}. + +@cindex PostgreSQL/MySQL, strategies +@menu +* MySQL-PostgreSQL goals:: MySQL and PostgreSQL development strategies +@end menu + +@node MySQL-PostgreSQL goals, , Compare PostgreSQL, Compare PostgreSQL +@subsubsection MySQL and PostgreSQL development strategies + +When adding things to MySQL we take pride to do an optimal, definite +solution. The code should be so good that we shouldn't have any need to +change it in the foreseeable future. We also do not like to sacrifice +speed for features but instead will do our utmost to find a solution +that will give maximal throughput. This means that development will take +a little longer, but the end result will be well worth this. This kind +of development is only possible because all server code are checked by +one of a few (currently two) persons before it's included in the +@strong{MySQL} server. + +We at MySQL AB believe in frequent releases to be able to push out new +features quickly to our users. Because of this we do a new small release +about every 3 weeks, which a major branch every year. All releases are +throughly tested with our testing tools on a lot of different platforms. + +PostgreSQL is based on a kernel with lots of contributors. In this setup +it makes sense to prioritize adding a lot of new features, instead of +implementing them optimally, because one can always optimize things +later if there arises a need for this. + +Another big difference between @strong{MySQL} and PostgreSQL is that +nearly all of the code in the MySQL server are coded by developers that +are employed by MySQL AB and are still working on the server code. The +exceptions are the transaction engines and the regexp library. + +This is in sharp contrast to the PostgreSQL code where the majority of +the code is coded by a big group of people with different backgrounds. +It was only recently that the PostgreSQL developers announced that they +current developer group had finally had time to take a look at all +the code in the current PostgreSQL release. + +Both of the above development methods has it's own merits and drawbacks. +We here at @strong{MySQL AB} think of course that our model is better +because our model gives better code consistence, more optimal and +reusable code and, in our opinion, fewer bugs. Because we are the +authors of the @strong{MySQL} server code we are better able to +coordinate new features and releases. + +@cindex PostgreSQL/MySQL, features +@node MySQL-PostgreSQL features, , Compare PostgreSQL, Comparisons +@subsection Featurevise Comparison of MySQL and PostgreSQL + +On the @uref{http://www.mysql.com/information/crash-me.php, crash-me} +page you can find a list of those database constructs and limits that +one can detect automatically with a program. Note however that a lot of +the numerical limits may be changed with startup options for respective +database. The above web page is however extremely useful when you want to +ensure that your applications works with many different databases or +when you want to convert your application from one datbase to another. + +@strong{MySQL} offers the following advantages over PostgreSQL: + +@itemize @bullet +@item +@code{MySQL} is generally much faster than PostgreSQL. +@xref{MySQL-PostgreSQL benchmarks}. +@item +Because @strong{MySQL} has a much larger user base than PostgreSQL the +code is more tested and has historically been more stable than +PostgreSQL. @strong{MySQL} is the much more used in production +environments than PostgreSQL, mostly thanks to that @strong{MySQL AB}, +former TCX DataKonsult AB, has provided top quality commercial support +for @strong{MySQL} from the day it was released, whereas until recently +PostgreSQL was unsupported. +@item +@strong{MySQL} works on more platforms than PostgreSQL. @xref{Which OS}. +@item +@strong{MySQL} works better on Windows; @strong{MySQL} is running as a +native windows application (a service on NT/Win2000/WinXP), while +PostgreSQL is run under the cygwin emulation. We have heard that +PostgreSQL is not yet that stable on windows but we haven't been able to +verify this ourselves. +@item +@strong{MySQL} has more API to other languages and is supported by more +programs than PostgreSQL. @xref{Contrib}. +@item +@strong{MySQL} works on 24/7 heavy duty systems. In most circumstances +you never have to run any cleanups on @code{MySQL}. PostgreSQL doesn't +yet support 24/7 systems because you have have to run @code{vacuum()} +once in a while to reclaim space from @code{UPDATE} and @code{DELETE} +commands and to perform statistics analyzes that are critical to get +good performance with PostgreSQL. Vacuum is also needed after adding +a lot of new rows to a table. On a busy system with lots of changes +vacuum must be run very frequently, in the worst cases even many times a +day. During the @code{vacuum()} run, which may take hours if the +database is big, the database is from a production standpoint +practically dead. The PostgreSQL team has fixing this on their TODO, +but we assume that this is not an easy thing to fix permanently. +@item +A working, tested replication feature used by sites like +@uref{http://finance.yahoo.com, Yahoo finance}, +@uref{http://www.mobile.de/,mobile.de} and +@uref{http://www.slashdot.org,Slashdot}. +@item +Included in the @strong{MySQL} distribution is included two different +testing suits (@file{mysql-test-run} and +@uref{http://www.mysql.com/information/crash-me.php,crash-me}) and a +benchmark suite. The test system is actively updated with code to test +each new feature and almost all repeatable bugs that comes to our +attention. We test @strong{MySQL} with these on a lot of platforms +before every release. These tests are more sofisticated than anything +have seen from PostgreSQL and ensures that the @strong{MySQL} code keeps +at a high standard. +@item +There are far moore books in print on @strong{MySQL} than on PostgreSQL. +O'Reilly, Sams, Que, and New Riders are all major publishers with books +about MySQL. All @strong{MySQL} features is also documented in the +@strong{MySQL} on-line manual because when a feature is implemented, the +@strong{MySQL} developers are required to document it before it's +included in the source. +@item +@strong{MySQL} has supports more of the standard ODBC functions than +@code{PostgreSQL}. +@item +@strong{MySQL} has a much more sophisticated @code{ALTER TABLE}. +@item +@strong{MySQL} has support for tables without transactions for +applications that need all speed they can get. The tables may be memory +based,@code{HEAP} tables or disk based @code{MyISAM}. @xref{Table types}. +@item +@strong{MySQL} has support for 3 different table handles that support +transactions (@code{BDB} and @code{InnoDB}). Because +every transaction engine performs differently under different +conditions, this gives the application writer more options to find an +optimal solution for his/her setup. @xref{Table types}. +@item +@code{MERGE} tables gives you a unique way to instantly make a view over +a set of identical tables and use these as one. This is perfectly for +systems where you have log files that you order for example by month. +@xref{MERGE}. +@item +The option to compress read-only tables, but still have direct access to +the rows in the table, gives you better performance by minimizing disk +reads. This is very useful when you are archiving +things.@xref{myisampack}. +@item +@strong{MySQL} has internal support for text search. @xref{Fulltext Search}. +@item +You can access many databases from the same connection (depending of course +on your privileges). +@item +@strong{MySQL} is coded from the start with multi-threading while +PostgreSQL uses processes. Because context switching and access to +common storage areas is much faster between threads, than are separate +processes, this gives @strong{MySQL} a big speed advantage in multi-user +applications and also makes it easier for @strong{MySQL} to take full +advantage of symmetric multiprocessor systems (SMP). +@item +@strong{MySQL} has a much more sophisticated privilege system than +PostgreSQL. While PostgreSQL only supports @code{INSERT}, +@code{SELECT}, @code{update/delete} grants per user on a database or a +table @strong{MySQL} allows you to define a full set of different +privileges on database, table and columns level. @strong{MySQL} also allows +you to specify the privilege on host+user combinations. @xref{GRANT}. +@item +@strong{MySQL} supports a compressed server/client protocol which +improves performance over slow links. +@item +@strong{MySQL} employs the table handler concept and is the only +relational database we know of built around this concept. This allows +different low level table types to be swapped into the SQL engine, each +table type optimized for a different performance characteristics. +@item +All @code{MySQL} table types (except @strong{InnoDB}) are implemented as +files (ie: one table per file), which makes it really easy to backup, +move, delete and even symlink databases and tables when the server is +down. +@item +Tools to repair and optimize @strong{MyISAM} tables (the most common +@strong{MySQL} table type). A repair tool is only needed when a +physical corruption of a data file happens, usually from a hardware +failure. It allows a majority of the data to be recovered. +@item +Upgrading @strong{MySQL} is painless. When you are upgrading @strong{MySQL}, +you don't need to dump/restore your data, as you have to do with most +PostgreSQL upgrades. +@end itemize + +Drawbacks with @strong{MySQL} compared to PostgreSQL: + +@itemize @bullet +@item +The transaction support in @strong{MySQL} is not yet as well tested as +PostgreSQL's system. +@item +Because @strong{MySQL} uses threads, which are still a moving target on +many OS, one must either use binaries from +@uref{http://www.mysql.com/downloads} or carefully follow our +instructions on +@uref{http://www.mysql.com/doc/I/n/Installing_source.html} to get an +optimal binary that works in all cases. +@item +Table locking, as used by the non-transactional @code{MyISAM} tables, is +in many cases faster than page locks, row locks or versioning. The +drawback however is that if one doesn't take into account how table +locks works, a single long-running query can block a table for updates +for a long time. This can usable be avoided when designing the +application. If not, one can always switch the trouble table to use one +of the transactional table types. @xref{Table locking}. +@item +With UDF (user defined functions) one can extend @strong{MySQL} with +both normal SQL functions and aggregates, but this is not as easy or as +flexible as in PostgreSQL. @xref{Adding functions}. +@item +Updates and deletes that goes over multiple tables is harder to do in +@strong{MySQL}. (Will be fixed in @strong{MySQL} 4.0 with multi-table +@code{DELETE} and multi-table @code{UPDATE} and in @strong{MySQL} 4.1 +with @code{SUB-SELECT}) +@end itemize + +PostgreSQL offers currently the following advantages over @strong{MySQL}: + +Note that because we know the @strong{MySQL} road map, we have included +in the following table the version when @strong{MySQL} should support +this feature. Unfortunately we couldn't do this for previous comparison, +because we don't know the PostgreSQL roadmap. + +@multitable @columnfractions .70 .30 +@item @strong{Feature} @tab @strong{MySQL version} +@item Subselects @tab 4.1 +@item Foreign keys @tab 4.0 and 4.1 +@item Views. @tab 4.2 +@item Stored procedures in multiple languages @tab 4.1 +@item Extensible type system. @tab Not planed +@item Unions @tab 4.0. +@item Full join. @tab 4.0 or 4.1. +@item Triggers. @tab 4.1 +@item Constrainst @tab 4.1 +@item Cursors @tab 4.1 or 4.2 +@item Extensible index types like R-trees @tab R-trees are planned to 4.2 +@item Inherited tables @tab Not planned +@end multitable + +Other reasons to use PostgreSQL: + +@itemize @bullet +@item +Standard usage is in PostgreSQL closer to ANSI SQL in some cases. +@item +One can get speed up PostgreSQL by coding things as stored procedures. +@item +Bigger team of developers that contributes to the server. +@end itemize + +Drawbacks with PostgreSQL compared to @strong{MySQL}: + +@itemize @bullet +@item +@code{Vaccum()} makes PostgreSQL hard to use in a 24/7 environment. +@item +Only transactional tables. +@item +Much slower insert/delete/update. +@end itemize + +For a complete list of drawbacks, you should also examine the first table +in this section. + +@menu +* MySQL-PostgreSQL benchmarks:: +@end menu + +@node MySQL-PostgreSQL benchmarks, , MySQL-PostgreSQL features, MySQL-PostgreSQL features +@subsubsection Benchmarking MySQL and PostgreSQL + +@cindex PostgreSQL vs. MySQL, benchmarks + +The only open source benchmark, that we know of, that can be used to +benchmark @strong{MySQL} and PostgreSQL (and other databases) is our +own. It can be found at: +@uref{http://www.mysql.com/information/benchmarks.html}. + +We have many times asked the PostgreSQL developers and some PostgreSQL +users to help us extend this benchmark to make the definitive benchmark +for databases, but unfortunately we haven't got any feedback for this. + +We, the @strong{MySQL} developers, have because of this spent a lot of +hours to get maximum performance from PostgreSQL for the benchmarks, but +because we don't know PostgreSQL intimately we are sure that there are +things that we have missed. We have on the benchmark page documented +exactly how we did run the benchmark so that it should be easy for +anyone to repeat and verify our results. + +The benchmarks are usually run with and without the @code{--fast} +option. When run with @code{--fast} we are trying to use every trick +the server can do to get the code to execute as fast as possible. +The idea is that the normal run should show how the server would work in +a default setup and the @code{--fast} run shows how the server would do +if the application developer would use extensions in the server to make +his application run faster. + +When running with PostgreSQL and @code{--fast} we do a @code{vacuum()} +between after every major table update and drop table to make the database +in perfect shape for the following selects. The time for vacuum() is +measured separately. + +When running with PostgreSQL 7.1.1 we could however not run with +@code{--fast} because during the insert test, the postmaster (the +PostgreSQL deamon) died and the database was so corrupted that it was +impossible to restart postmaster. (The details about the machine we run +the benchmark can be found on the benchmark page). After this happened +twice, we decided to postpone the @code{--fast} test until next +PostgreSQL release. + +Before going to the other benchmarks we know of, We would like to give +some background to benchmarks: + +It's very easy to write a test that shows ANY database to be best +database in the world, by just restricting the test to something the +database is very good at and not test anything that the database is not +good at; If one after this publish the result with a single figure +things is even easier. + +This would be like we would measure the speed of @strong{MySQL} compared +to PostgreSQL by looking at the summary time of the MySQL benchmarks on +our web page. Based on this @strong{MySQL} would be more than 40 times +faster than PostgreSQL, something that is of course not true. We could +make things even worse by just taking the test where PostgreSQL performs +worst and claim that @strong{MySQL} is more than 2000 times faster than +PostgreSQL. + +The case is that @strong{MySQL} does a lot of optimizations that +PostgreSQL doesn't do and the other way around. An SQL optimizer is a +very complex thing and a company could spend years on just making the +optimizer faster and faster. + +When looking at the benchmark results you should look for things that +you do in your application and just use these results to decide which +database would be best suited for your application. The benchmark +results also shows things a particular database is not good at and should +give you a notion about things to avoid and what you may have to do in +other ways. + +We know of two benchmark tests that claims that PostgreSQL performers +better than @strong{MySQL}. These both where multi-user tests, a test +that we here at @strong{MySQL AB} haven't had time to write and include in +the benchmark suite, mainly because it's a big task to do this in a +manner that is fair against all databases. + +One is the benchmark paid for by +@uref{http://www.greatbridge.com/about/press.php?content_id=4,Great +Bridge}. + +This is the worst benchmark we have ever seen anyone ever conduct. This +was not only tuned to only test what PostgreSQL is absolutely best at, +it was also totally unfair against every other database involved in the +test. + +@strong{NOTE}: We know that not even some of the main PostgreSQL +developers did like the way Great Bridge conducted the benchmark, so we +don't blame them for the way the benchmark was made. + +This benchmark has been condemned in a lot of postings and newsgroups so +we will here just shortly repeat some things that where wrong with it. + +@itemize @bullet +@item +The tests where run with an expensive commercial tool, that makes it +impossible for an open source company like us to verify the benchmarks, +or even check how the benchmark where really done. The tool is not even +a true benchmark tool, but a application/setup testing tool. To refer +this as STANDARD benchmark tool is to stretch the truth a long way. +@item +Great Bridge admitted that they had optimized the PostgreSQL database +(with vacuum() before the test) and tuned the startup for the tests, +something they hadn't done for any of the other databases involved. To +say "This process optimizes indexes and frees up disk space a bit. The +optimized indexes boost performance by some margin". Our benchmarks +clearly indicates that the difference in running a lot of selects on a +database with and without vacuum() can easily differ by a factor of 10. +@item +The test results where also strange; The AS3AP test documentation +mentions that the test does: + +"selections, simple joins, projections, aggregates, one-tuple updates, +and bulk updates" + +PostgreSQL is good at doing selects and joins (especially after a +vacuum()), but doesn't perform as well on inserts/updates; The +benchmarks seem to indicate that only SELECTs where done (or very few +updates) . This could easily explain they good results for PostgreSQL in +this test. The bad results for MySQL will be obvious a bit down in this +document. +@item +They did run the so called benchmark from a Windows machine against a +Linux machine over ODBC, a setup that no normal database user would ever +do when running a heavy multi-user application. This tested more the +ODBC driver and the Windows protocol used between the clients than the +database itself. +@item +When running the database against Oracle and MS-SQL (Great Bridge has +indirectly indicated that the databases they used in the test), +they didn't use the native protocol but instead ODBC. Anyone that has +ever used Oracle, knows that all real application uses the native +interface instead of ODBC. Doing a test through ODBC and claiming that +the results had anything to do with using the database for real can't +be regarded as fair play. They should have done two tests with and +without ODBC to provide the right facts (after having got experts to tune +all involved databases of course). +@item +They refer to the TPC-C tests, but doesn't anywhere mention that the +tests they did where not a true TPC-C test and they where not even +allowed to call it a TPC-C test. A TPC-C test can only be conducted by +the rules approved by the @uref{http://www.tpc.org,TPC-council}. Great +Bridge didn't do that. By doing this they have both violated the TPC +trademark and miscredited their own benchmarks. The rules set by the +TPC-council are very strict to ensure that no one can produce false +results or make unprovable statements. Apparently Great Bridge wasn't +interested in doing this. +@item +After the first test, we contacted Great Bridge and mentioned to them +some of the obvious mistakes they had done with @strong{MySQL}; Running +with a debug version of our ODBC driver, running on a Linux system that +wasn't optimized for threads, using an old MySQL version when there was +a recommended newer one available, not starting @strong{MySQL} with the +right options for heavy multi-user use (the default installation of +MySQL is tuned for minimal resource use). Great Bridge did run a new +test, with our optimized ODBC driver and with better startup options for +MySQL, but refused to either use our updated glibc library or our +standard binary (used by 80% of our users), which was statically linked +with a fixed glibc library. + +According to what we know, Great Bridge did nothing to ensure that the +other databases where setup correctly to run good in their test +environment. We are sure however that they didn't contact Oracle or +Microsoft to ask for their advice in this matter ;) +@item +The benchmark was paid for by Great Bridge, and they decided to publish +only partial chosen results (instead of publishing it all). +@end itemize + +Tim Perdue, a long time PostgreSQL fan and a reluctant MySQL user +published a comparison on +@uref{http://www.phpbuilder.com/columns/tim20001112.php3,phpbuider}. + +When we got aware of the comparison, we phoned Tim Perdue about this +because there was a lot of strange things in his results. For example, +he claimed that MySQL had a problem with five users in his tests, when we +know that there are users with similar machines as his that are using +MySQL with 2000 simultaneous connections doing 400 queries per second (In +this case the limit was the web bandwidth, not the database). + +It sounded like he was using a Linux kernel that either had some +problems with many threads (Linux kernels before 2.4 had a problem with +this but we have documented how to fix this and Tim should be aware of +this problem). The other possible problem could have been an old glibc +library and that Tim didn't use a MySQL binary from our site, which is +linked with a corrected glibc library, but had compiled a version of his +own with. In any of the above cases, the symptom would have been exactly +what Tim had measured. + +We asked Tim if we could get access to his data so that we could repeat +the benchmark and if he could check the MySQL version on the machine to +find out what was wrong and he promised to come back to us about this. +He has not done that yet. + +Because of this we can't put any trust in this benchmark either :( + +Conclusion: + +The only benchmarks that exist today that anyone can download and run +against @strong{MySQL}and PostgreSQL is the MySQL benchmarks. We here +at @strong{MySQL} believe that open source databases should be tested +with open source tools! This is the only way to ensure that no one +does tests that nobody can reproduce and use this to claim that a +database is better than another. Without knowing all the facts it's +impossible to answer the claims of the tester. + +The thing we find strange is that every test we have seen about +PostgreSQL, that is impossible to reproduce, claims that PostgreSQL is +better in most cases while our tests, which anyone can reproduce, +clearly shows otherwise. With this we don't want to say that PostgreSQL +isn't good at many things (It is!) We would just like to see a fair test +where they are very good so that we could get some friendly competition +going! + +For more information about our benchmarks suite see @xref{MySQL +Benchmarks}. + +We are working on an even better benchmark suite, including much better +documentation of what the individual tests really do and how to add more +tests to the suite. + +@node TODO, , Comparisons, Introduction +@section MySQL and the future (The TODO) + +@cindex ToDo list for MySQL + +@menu +* TODO MySQL 4.0:: Things that should be in Version 4.0 +* TODO future:: Things that must be done in the near future +* TODO sometime:: Things that have to be done sometime +* TODO unplanned:: Some things we don't have any plans to do +@end menu + +This appendix lists the features that we plan to implement in @strong{MySQL}. + +Everything in this list is approximately in the order it will be done. If you +want to affect the priority order, please register a license or support us and +tell us what you want to have done more quickly. @xref{Licensing and Support}. + +The plan is that we in the future will support the full ANSI SQL99 +standard, but with a lot of useful extensions. The challenge is to do +this without sacrifying the speed or compromise the code. + +@node TODO MySQL 4.0, TODO future, TODO, TODO +@subsection Things that should be in 4.0 + +We plan to make @strong{MySQL} Version 4.0 a ``quick'' release where we only +add some new stuff to enable others to help us with developing new features +into Version 4.1. The @strong{MySQL} 4.0 version should only take us about +a month to make after which we want to stabilize it and start working on +Version 4.1. Version 4.0 should have the following new features: + +The news section for 4.0 includes a list of the features we have already +implemented in the 4.0 tree. @xref{News-4.0.x}. + +@itemize @bullet +@item +New table definition file format (@code{.frm} files) This will enable us +to not run out of bits when adding more table options. One will still +be able to use the old @code{.frm} file format with 4.0. All newly created +tables will, however, use the new format. + +The new file format will enable us to add new column types, more options +for keys and @code{FOREIGN KEY} support. +@item +@code{mysqld} as a library. This will have the same interface as the +standard @strong{MySQL} client (with an extra function to just set up +startup parameters) but will be faster (no TCP/IP or socket overhead), +smaller and much easier to use for embedded products. + +One will be able to define at link time if one wants to use the +client/server model or a stand-alone application just by defining which +library to link with. + +The @code{mysqld} will support all standard @strong{MySQL} features and +one can use it in a threaded client to run different queries in each +thread. +@item +Replication should work with @code{RAND()} and user variables @code{@@var}. +@item +Online backup with very low performance penalty. The online backup will +make it easy to add a new replication slave without taking down the +master. +@item +@code{DELETE FROM table_name} will return the number of deleted rows. For +fast execution one should use @code{TRUNCATE table_name}. +@item +Allow @code{DELETE} on @code{MyISAM} tables to use the record cache. +To do this, we need to update the threads record cache when we update +the @code{.MYD} file. +@item +Better replication. +@item +More functions for full-text search. +@xref{Fulltext Features to Appear in MySQL 4.0}. +@item +Character set casts and syntax for handling multiple character sets. +@item +Allow users to change startup options without taking down the server. +@item +Help for all commands from the client. +@item +Secure connections (with SSL). +@item +Extend the optimizer to be able to optimize some @code{ORDER BY key_name DESC} +queries. +@item +@code{SHOW COLUMNS FROM table_name} (used by @code{mysql} client to allow +expansions of column names) should not open the table, but only the +definition file. This will require less memory and be much faster. +@item +New key cache +@item +When using @code{SET CHARACTER SET} we should translate the whole query +at once and not only strings. This will enable users to use the translated +characters in database, table and column names. +@item +Add a portable interface over @code{gethostbyaddr_r()} so that we can change +@code{ip_to_hostname()} to not block other threads while doing DNS lookups. +@item +Add @code{record_in_range()} method to @code{MERGE} tables to be +able to choose the right index when there is many to choose from. We should +also extend the info interface to get the key distribution for each index, +of @code{analyze} is run on all sub tables. +@item +@code{SET SQL_DEFAULT_TABLE_TYPE=[MyISAM | INNODB | BDB | HEAP]}. +@end itemize + +@node TODO future, TODO sometime, TODO MySQL 4.0, TODO +@subsection Things that must be done in the real near future + +@itemize @bullet +@item +Fail safe replication. +@item +Subqueries. +@code{select id from t where grp in (select grp from g where u > 100)} +@item +Derived tables. +@example +select a.col1, b.col2 from (select max(col1) as col1 from root_table ) a, +other_table b where a.col1=b.col1 +@end example + +This could be done by automatically creating temporary tables for the +derived tables for the duration of the query. +@item +Add @code{PREPARE} of statements and sending of parameters to @code{mysqld}. +@item +Extend the server/client protocol to support warnings. +@item +Add options to the server/protocol protocol to get progress notes +for long running commands. +@item +Add database and real table name (in case of alias) to the MYSQL_FIELD +structure. +@item +Don't allow more than a defined number of threads to run MyISAM recover +at the same time. +@item +Change @code{INSERT ... SELECT} to optionally use concurrent inserts. +@item +Implement @code{RENAME DATABASE}. To make this safe for all table handlers, +it should work as follows: +@itemize @bullet +@item +Create the new database. +@item +For every table do a rename of the table to another database, as +we do with the @code{RENAME} command. +@item +Drop the old database. +@end itemize +@item +Return the original field types() when doing @code{SELECT MIN(column) +... GROUP BY}. +@item +Multiple result sets. +@item +Change the protocol to allow binary transfer of values. To do this +efficiently, we need to add an API to allow binding of variables. +@item +Make it possible to specify @code{long_query_time} with a granularity +in microseconds. +@item +Add a configurable prompt to the @code{mysql} command line client, with +options like database in use, time and date... +@item +Add range checking to @code{MERGE} tables. +@item +Link the @code{myisampack} code into the server. +@item +Port of @strong{MySQL} to BeOS. +@item +Port of the @strong{MySQL} clients to LynxOS. +@item +Add a temporary key buffer cache during @code{INSERT/DELETE/UPDATE} so that we +can gracefully recover if the index file gets full. +@item +If you perform an @code{ALTER TABLE} on a table that is symlinked to another +disk, create temporary tables on this disk. +@item +Implement a @code{DATE/DATETIME} type that handles time zone information +properly, so that dealing with dates in different time zones is easier. +@item +FreeBSD and MIT-pthreads; Do sleeping threads take CPU? +@item +Check if locked threads take any CPU. +@item +Fix configure so that one can compile all libraries (like @code{MyISAM}) +without threads. +@item +Add an option to periodically flush key pages for tables with delayed keys +if they haven't been used in a while. +@item +Allow join on key parts (optimization issue). +@item +@code{INSERT SQL_CONCURRENT} and @code{mysqld --concurrent-insert} to do +a concurrent insert at the end of the file if the file is read-locked. +@item +Remember @code{FOREIGN} key definitions in the @file{.frm} file. +@item +Cascading @code{DELETE} +@item +Server side cursors. +@item +Check if @code{lockd} works with modern Linux kernels; If not, we have +to fix @code{lockd}! To test this, start @code{mysqld} with +@code{--enable-locking} and run the different fork* test suits. They shouldn't +give any errors if @code{lockd} works. +@item +Allow SQL variables in @code{LIMIT}, like in @code{LIMIT @@a,@@b}. +@item +Allow update of variables in @code{UPDATE} statements. For example: +@code{UPDATE TABLE foo SET @@a=a+b,a=@@a, b=@@a+c} +@item +Change when user variables are updated so that one can use them with +@code{GROUP BY}, as in the following example: +@code{SELECT id, @@a:=count(*), sum(sum_col)/@@a FROM table_name GROUP BY id}. +@item +Don't add automatic @code{DEFAULT} values to columns. Give an error when using +an @code{INSERT} that doesn't contain a column that doesn't have a +@code{DEFAULT}. +@item +Caching of queries and results. This should be done as a separated +module that examines each query and if this is query is in the cache +the cached result should be returned. When one updates a table one +should remove as few queries as possible from the cache. +This should give a big speed bost on machines with much RAM where +queries are often repeated (like WWW applications). +One idea would be to only cache queries of type: +@code{SELECT CACHED ...} +@item +Fix @file{libmysql.c} to allow two @code{mysql_query()} commands in a row +without reading results or give a nice error message when one does this. +@item +Optimize @code{BIT} type to take 1 bit (now @code{BIT} takes 1 char). +@item +Check why MIT-pthreads @code{ctime()} doesn't work on some FreeBSD systems. +@item +Add an @code{IMAGE} option to @code{LOAD DATA INFILE} to not update +@code{TIMESTAMP} and @code{AUTO_INCREMENT} fields. +@item +Added @code{LOAD DATE INFILE.. UPDATE} syntax. +@itemize @bullet +@item +For tables with primary keys, if the data contains the primary key, +entries matching that primary key are updated from the remainder of the +columns. However, columns MISSING from the incoming data feed are not +touched. +@item +For tables tables with primary keys that are missing some part of the key +in the incoming data stream, or that have no primary key, the feed is +treated as a @code{LOAD DATA INFILE ... REPLACE INTO} is now. +@end itemize +@item +Make @code{LOAD DATA INFILE} understand syntax like: +@example +LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name +TEXT_FIELDS (text_field1, text_field2, text_field3) +SET table_field1=concatenate(text_field1, text_field2), table_field3=23 +IGNORE text_field3 + +This can be used to skip over extra columns in the text file, or update columns +based on expressions of the read data... +@end example +@item +@code{LOAD DATA INFILE 'file_name' INTO TABLE 'table_name' ERRORS TO err_table_name} +This would cause any errors and warnings to be logged into the err_table_name +table. That table would have a structure like: + +@example +line_number - line number in data file +error_message - the error/warning message +and maybe +data_line - the line from the data file +@end example +@item +Add true @code{VARCHAR} support (There is already support for this in MyISAM). +@item +Automatic output from @code{mysql} to netscape. +@item +@code{LOCK DATABASES}. (with various options) +@item +Change sort to allocate memory in ``hunks'' to get better memory utilization. +@item +@code{DECIMAL} and @code{NUMERIC} types can't read exponential numbers; +@code{Field_decimal::store(const char *from,uint len)} must be recoded +to fix this. +@item +Fix @code{mysql.cc} to do fewer @code{malloc()} calls when hashing field +names. +@item +Functions: +ADD_TO_SET(value,set) and REMOVE_FROM_SET(value,set) +@item +Add use of @code{t1 JOIN t2 ON ...} and @code{t1 JOIN t2 USING ...} +Currently, you can only use this syntax with @code{LEFT JOIN}. +@item +Add full support for @code{unsigned long long} type. +@item +Many more variables for @code{show status}. Counts for: +@code{INSERT}/@code{DELETE}/@code{UPDATE} statements. Records reads and +updated. Selects on 1 table and selects with joins. Mean number of +tables in select. Number of @code{ORDER BY} and @code{GROUP BY} queries. +@item +If you abort @code{mysql} in the middle of a query, you should open +another connection and kill the old running query. +Alternatively, an attempt should be made to detect this in the server. +@item +Add a handler interface for table information so you can use it as a system +table. This would be a bit slow if you requested information about all tables, +but very flexible. @code{SHOW INFO FROM tbl_name} for basic table information +should be implemented. +@item +Add support for UNICODE. +@item +@code{NATURAL JOIN} and @code{UNION JOIN} +@item +Allow @code{select a from crash_me left join crash_me2 using (a)}; In this +case a is assumed to come from the crash_me table. +@item +Fix that @code{ON} and @code{USING} works with the @code{JOIN} and +@code{INNER JOIN} join types. +@item +Oracle like @code{CONNECT BY PRIOR ...} to search hierarchy structures. +@item +@code{RENAME DATABASE} +@item +@code{mysqladmin copy database new-database}. -- Requires COPY command to be +added to @code{mysqld} +@item +Processlist should show number of queries/thread. +@item +@code{SHOW HOSTS} for printing information about the hostname cache. +@item +@code{DELETE} and @code{REPLACE} options to the @code{UPDATE} statement +(this will delete rows when one gets a duplicate key error while updating). +@item +Change the format of @code{DATETIME} to store fractions of seconds. +@item +Add all missing ANSI92 and ODBC 3.0 types. +@item +Change table names from empty strings to @code{NULL} for calculated columns. +@item +Don't use 'Item_copy_string' on numerical values to avoid +number->string->number conversion in case of: +@code{SELECT COUNT(*)*(id+0) FROM table_name GROUP BY id} +@item +Make it possible to use the new GNU regexp library instead of the current +one (The GNU library should be much faster than the old one). +@item +Change that @code{ALTER TABLE} doesn't abort clients that executes +@code{INSERT DELAYED}. +@item +Fix that when columns referenced in an @code{UPDATE} clause contains the old +values before the update started. +@item +@code{myisamchk}, @code{REPAIR} and @code{OPTIMIZE TABLE} should be able +to handle cases where the data and/or index files are symbolic links. +@item +Add simulation of @code{pread()}/@code{pwrite()} on Windows to enable +concurrent inserts. +@item +A logfile analyzer that could parsed out information about which tables +are hit most often, how often multi-table joins are executed, etc. It +should help users identify areas or table design that could be optimized +to execute much more efficient queries. +@item +Add @code{SUM(DISTINCT)} +@item +Add @code{ANY()},@code{EVERY()} and @code{SOME()} group functions. In +ANSI SQL these only works on boolean columns, but we can extend these to +work on any columns/expressions by applying: value == 0 -> FALSE and +value <> 0 -> TRUE. +@item +Fix that the type for @code{MAX(column)} is the same as the column type. +@example +create table t1 (a DATE); +insert into t1 values (now()); +create table t2 select max(a) from t1; +show columns from t2; +@end example +@item +Come up with a nice syntax for a statement that will @code{UPDATE} the row +if it exists and @code{INSERT} a new row if the row didn't exist. +(Like @code{REPLACE} works with @code{INSERT} / @code{DELETE}) +@end itemize + +@node TODO sometime, TODO unplanned, TODO future, TODO +@subsection Things that have to be done sometime + +@itemize @bullet +@item +Implement function: @code{get_changed_tables(timeout,table1,table2,...)} +@item +Atomic multi-table updates, eg @code{update items,month set +items.price=month.price where items.id=month.id;}; +@item +Change reading through tables to use memmap when possible. Now only +compressed tables use memmap. +@item +Add a new privilege @strong{'Show_priv'} for @code{SHOW} commands. +@item +Make the automatic timestamp code nicer. Add timestamps to the update +log with @code{SET TIMESTAMP=#;} +@item +Use read/write mutex in some places to get more speed. +@item +Full foreign key support. One probably wants to implement a procedural +language first. +@item +Simple views (first on one table, later on any expression). +@item +Automatically close some tables if a table, temporary table or temporary files +gets error 23 (not enough open files). +@item +When one finds a field=#, change all occurrences of field to #. Now this +is only done for some simple cases. +@item +Change all const expressions with calculated expressions if possible. +@item +Optimize key = expression. At the moment only key = field or key = +constant are optimized. +@item +Join some of the copy functions for nicer code. +@item +Change @file{sql_yacc.yy} to an inline parser to reduce its size and get +better error messages (5 days). +@item +Change the parser to use only one rule per different number of arguments +in function. +@item +Use of full calculation names in the order part. (For ACCESS97) +@item +@code{UNION}, @code{MINUS}, @code{INTERSECT} and @code{FULL OUTER JOIN}. +(Currently only @code{LEFT OUTER JOIN} is supported) +@item +Allow @code{UNIQUE} on fields that can be @code{NULL}. +@item +@code{SQL_OPTION MAX_SELECT_TIME=#} to put a time limit on a query. +@item +Make the update log to a database. +@item +Negative @code{LIMIT} to retrieve data from the end. +@item +Alarm around client connect/read/write functions. +@item +Please note the changes to @code{safe_mysqld}: according to FSSTND (which +Debian tries to follow) PID files should go into @file{/var/run/<progname>.pid} +and log files into @file{/var/log}. It would be nice if you could put the +"DATADIR" in the first declaration of "pidfile" and "log", so the +placement of these files can be changed with a single statement. +@item +Allow a client to request logging. +@item +Add use of @code{zlib()} for @code{gzip}-ed files to @code{LOAD DATA INFILE}. +@item +Fix sorting and grouping of @code{BLOB} columns (partly solved now). +@item +Stored procedures. This is currently not regarded to be very +important as stored procedures are not very standardized yet. +Another problem is that true stored procedures make it much harder for +the optimizer and in many cases the result is slower than before +We will, on the other hand, add a simple (atomic) update language that +can be used to write loops and such in the @strong{MySQL} server. +@item +Change to use semaphores when counting threads. One should first implement +a semaphore library to MIT-pthreads. +@item +Don't assign a new @code{AUTO_INCREMENT} value when one sets a column to 0. +Use @code{NULL} instead. +@item +Add full support for @code{JOIN} with parentheses. +@item +As an alternative for one thread / connection manage a pool of threads +to handle the queries. +@item +Allow one to get more than one lock with @code{GET_LOCK}. When doing this, +one must also handle the possible deadlocks this change will introduce. +@end itemize + +Time is given according to amount of work, not real time. + +@node TODO unplanned, , TODO sometime, TODO +@subsection Some things we don't have any plans to do + +@itemize @bullet +@item +Nothing; In the long run we plan to be fully ANSI 92 / ANSI 99 compliant. +@end itemize + +@node Installing, Privilege system, Introduction, Top @chapter Installing MySQL + @cindex installing, overview @menu @@ -11116,879 +13841,8 @@ After you import the @code{mysql} database on the new machine, execute @code{mysqladmin flush-privileges} so that the server reloads the grant table information. -@cindex compatibility, with ANSI SQL -@cindex standards compatibility -@cindex extensions, to ANSI SQL -@cindex ANSI SQL92, extensions to -@node Compatibility, Privilege system, Installing, Top -@chapter How Standards-compatible Is MySQL? - -@menu -* Extensions to ANSI:: @strong{MySQL} extensions to ANSI SQL92 -* ANSI mode:: Running @strong{MySQL} in ANSI mode -* Differences from ANSI:: @strong{MySQL} differences compared to ANSI SQL92 -* Missing functions:: Functionality missing from @strong{MySQL} -* Standards:: What standards does @strong{MySQL} follow? -* Commit-rollback:: How to cope without @code{COMMIT}-@code{ROLLBACK} -@end menu - -This chapter describes how @strong{MySQL} relates to the ANSI SQL standards. -@strong{MySQL} has many extensions to the ANSI SQL standards, and here you -will find out what they are, and how to use them. You will also find -information about functionality missing from @strong{MySQL}, and how to work -around some differences. - -@node Extensions to ANSI, ANSI mode, Compatibility, Compatibility -@section MySQL Extensions to ANSI SQL92 - -@strong{MySQL} includes some extensions that you probably will not find in -other SQL databases. Be warned that if you use them, your code will not be -portable to other SQL servers. In some cases, you can write code that -includes @strong{MySQL} extensions, but is still portable, by using comments -of the form @code{/*! ... */}. In this case, @strong{MySQL} will parse and -execute the code within the comment as it would any other @strong{MySQL} -statement, but other SQL servers will ignore the extensions. For example: - -@example -SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ... -@end example - -If you add a version number after the @code{'!'}, the syntax will only be -executed if the @strong{MySQL} version is equal to or newer than the used -version number: - -@example -CREATE /*!32302 TEMPORARY */ TABLE (a int); -@end example - -The above means that if you have Version 3.23.02 or newer, then @strong{MySQL} -will use the @code{TEMPORARY} keyword. - -@strong{MySQL} extensions are listed below: - -@itemize @bullet -@item -The field types @code{MEDIUMINT}, @code{SET}, @code{ENUM}, and the -different @code{BLOB} and @code{TEXT} types. - -@item -The field attributes @code{AUTO_INCREMENT}, @code{BINARY}, @code{NULL}, -@code{UNSIGNED}, and @code{ZEROFILL}. - -@item -All string comparisons are case insensitive by default, with sort -ordering determined by the current character set (ISO-8859-1 Latin1 by -default). If you don't like this, you should declare your columns with -the @code{BINARY} attribute or use the @code{BINARY} cast, which causes -comparisons to be done according to the ASCII order used on the -@strong{MySQL} server host. - -@item -@strong{MySQL} maps each database to a directory under the @strong{MySQL} -data directory, and tables within a database to filenames in the database -directory. - -This has a few implications: - -@cindex database names, case sensitivity -@cindex table names, case sensitivity -@cindex case sensitivity, of database names -@cindex case sensitivity, of table names - -@itemize @minus - -@item -Database names and table names are case sensitive in @strong{MySQL} on -operating systems that have case-sensitive filenames (like most Unix -systems). @xref{Name case sensitivity}. - -@item -Database, table, index, column, or alias names may begin with a digit -(but may not consist solely of digits). - -@item -You can use standard system commands to backup, rename, move, delete, and copy -tables. For example, to rename a table, rename the @file{.MYD}, @file{.MYI}, -and @file{.frm} files to which the table corresponds. -@end itemize - -@item -In SQL statements, you can access tables from different databases -with the @code{db_name.tbl_name} syntax. Some SQL servers provide -the same functionality but call this @code{User space}. -@strong{MySQL} doesn't support tablespaces as in: -@code{create table ralph.my_table...IN my_tablespace}. - -@item -@code{LIKE} is allowed on numeric columns. - -@item -Use of @code{INTO OUTFILE} and @code{STRAIGHT_JOIN} in a @code{SELECT} -statement. @xref{SELECT, , @code{SELECT}}. - -@item -The @code{SQL_SMALL_RESULT} option in a @code{SELECT} statement. - -@item -@code{EXPLAIN SELECT} to get a description on how tables are joined. - -@item -Use of index names, indexes on a prefix of a field, and use of -@code{INDEX} or @code{KEY} in a @code{CREATE TABLE} -statement. @xref{CREATE TABLE, , @code{CREATE TABLE}}. - -@item -Use of @code{TEMPORARY} or @code{IF NOT EXISTS} with @code{CREATE TABLE}. - -@item -Use of @code{COUNT(DISTINCT list)} where 'list' is more than one element. - -@item -Use of @code{CHANGE col_name}, @code{DROP col_name}, or @code{DROP -INDEX}, @code{IGNORE} or @code{RENAME} in an @code{ALTER TABLE} -statement. @xref{ALTER TABLE, , @code{ALTER TABLE}}. - -@item -Use of @code{RENAME TABLE}. @xref{RENAME TABLE, , @code{RENAME TABLE}}. - -@item -Use of multiple @code{ADD}, @code{ALTER}, @code{DROP}, or @code{CHANGE} -clauses in an @code{ALTER TABLE} statement. - -@item -Use of @code{DROP TABLE} with the keywords @code{IF EXISTS}. - -@item -You can drop multiple tables with a single @code{DROP TABLE} statement. - -@item -The @code{LIMIT} clause of the @code{DELETE} statement. - -@item -The @code{DELAYED} clause of the @code{INSERT} and @code{REPLACE} -statements. - -@item -The @code{LOW_PRIORITY} clause of the @code{INSERT}, @code{REPLACE}, -@code{DELETE}, and @code{UPDATE} statements. - -@cindex Oracle compatibility -@cindex compatibility, with Oracle -@item -Use of @code{LOAD DATA INFILE}. In many cases, this syntax is compatible with -Oracle's @code{LOAD DATA INFILE}. @xref{LOAD DATA, , @code{LOAD DATA}}. - -@item -The @code{ANALYZE TABLE}, @code{CHECK TABLE}, @code{OPTIMIZE TABLE}, and -@code{REPAIR TABLE} statements. - -@item -The @code{SHOW} statement. -@xref{SHOW, , @code{SHOW}}. - -@item -Strings may be enclosed by either @samp{"} or @samp{'}, not just by @samp{'}. - -@item -Use of the escape @samp{\} character. - -@item -The @code{SET OPTION} statement. @xref{SET OPTION, , @code{SET OPTION}}. - -@item -You don't need to name all selected columns in the @code{GROUP BY} part. -This gives better performance for some very specific, but quite normal -queries. -@xref{Group by functions}. - -@item -One can specify @code{ASC} and @code{DESC} with @code{GROUP BY}. - -@item -To make it easier for users who come from other SQL environments, -@strong{MySQL} supports aliases for many functions. For example, all -string functions support both ANSI SQL syntax and ODBC syntax. - -@item -@strong{MySQL} understands the @code{||} and @code{&&} operators to mean -logical OR and AND, as in the C programming language. In @strong{MySQL}, -@code{||} and @code{OR} are synonyms, as are @code{&&} and @code{AND}. -Because of this nice syntax, @strong{MySQL} doesn't support -the ANSI SQL @code{||} operator for string concatenation; use -@code{CONCAT()} instead. Because @code{CONCAT()} takes any number -of arguments, it's easy to convert use of the @code{||} operator to -@strong{MySQL}. - -@item -@code{CREATE DATABASE} or @code{DROP DATABASE}. -@xref{CREATE DATABASE, , @code{CREATE DATABASE}}. - -@cindex PostgreSQL compatibility -@cindex compatibility, with PostgreSQL -@item -The @code{%} operator is a synonym for @code{MOD()}. That is, -@code{N % M} is equivalent to @code{MOD(N,M)}. @code{%} is supported -for C programmers and for compatibility with PostgreSQL. - -@item -The @code{=}, @code{<>}, @code{<=} ,@code{<}, @code{>=},@code{>}, -@code{<<}, @code{>>}, @code{<=>}, @code{AND}, @code{OR}, or @code{LIKE} -operators may be used in column comparisons to the left of the -@code{FROM} in @code{SELECT} statements. For example: - -@example -mysql> SELECT col1=1 AND col2=2 FROM tbl_name; -@end example - -@item -The @code{LAST_INSERT_ID()} function. -@xref{mysql_insert_id, , @code{mysql_insert_id()}}. - -@item -The @code{REGEXP} and @code{NOT REGEXP} extended regular expression -operators. - -@item -@code{CONCAT()} or @code{CHAR()} with one argument or more than two -arguments. (In @strong{MySQL}, these functions can take any number of -arguments.) - -@item The @code{BIT_COUNT()}, @code{CASE}, @code{ELT()}, -@code{FROM_DAYS()}, @code{FORMAT()}, @code{IF()}, @code{PASSWORD()}, -@code{ENCRYPT()}, @code{md5()}, @code{ENCODE()}, @code{DECODE()}, -@code{PERIOD_ADD()}, @code{PERIOD_DIFF()}, @code{TO_DAYS()}, or -@code{WEEKDAY()} functions. - -@item -Use of @code{TRIM()} to trim substrings. ANSI SQL only supports removal -of single characters. - -@item -The @code{GROUP BY} functions @code{STD()}, @code{BIT_OR()}, and -@code{BIT_AND()}. - -@item -Use of @code{REPLACE} instead of @code{DELETE} + @code{INSERT}. -@xref{REPLACE, , @code{REPLACE}}. - -@item -The @code{FLUSH flush_option} statement. - -@item -The possibility to set variables in a statement with @code{:=}: -@example -SELECT @@a:=SUM(total),@@b=COUNT(*),@@a/@@b AS avg FROM test_table; -SELECT @@t1:=(@@t2:=1)+@@t3:=4,@@t1,@@t2,@@t3; -@end example -@end itemize - -@node ANSI mode, Differences from ANSI, Extensions to ANSI, Compatibility -@section Running MySQL in ANSI Mode -@cindex running, ANSI mode -@cindex ANSI mode, running - -If you start @code{mysqld} with the @code{--ansi} option, the following behavior -of @strong{MySQL} changes: - -@itemize @bullet -@item -@code{||} is string concatenation instead of @code{OR}. -@item -You can have any number of spaces between a function name and the @samp{(}. -This forces all function names to be treated as reserved words. -@item -@samp{"} will be an identifier quote character (like the @strong{MySQL} -@samp{`} quote character) and not a string quote character. -@item -@code{REAL} will be a synonym for @code{FLOAT} instead of a synonym of -@code{DOUBLE}. -@item -The default transaction isolation level is @code{SERIALIZABLE}. -@xref{SET TRANSACTION}. -@end itemize - -@node Differences from ANSI, Missing functions, ANSI mode, Compatibility -@section MySQL Differences Compared to ANSI SQL92 - -We try to make @strong{MySQL} follow the ANSI SQL standard and the -ODBC SQL standard, but in some cases @strong{MySQL} does some things -differently: - -@itemize @bullet -@item -@code{--} is only a comment if followed by a white space. @xref{Missing -comments}. -@item -For @code{VARCHAR} columns, trailing spaces are removed when the value is -stored. @xref{Bugs}. -@item -In some cases, @code{CHAR} columns are silently changed to @code{VARCHAR} -columns. @xref{Silent column changes}. -@item -Privileges for a table are not automatically revoked when you delete a -table. You must explicitly issue a @code{REVOKE} to revoke privileges for -a table. @xref{GRANT, , @code{GRANT}}. -@item -@code{NULL AND FALSE} will evaluate to @code{NULL} and not to @code{FALSE}. -This is because we don't think it's good to have to evaluate a lot of -extra conditions in this case. -@end itemize - -@node Missing functions, Standards, Differences from ANSI, Compatibility -@section Functionality Missing from MySQL -@cindex missing functionality -@cindex functionality, missing - -The following functionality is missing in the current version of -@strong{MySQL}. For a prioritized list indicating when new extensions -may be added to @strong{MySQL}, you should consult -@uref{http://www.mysql.com/documentation/manual.php?section=TODO, the -online @strong{MySQL} TODO list}. That is the latest version of the TODO -list in this manual. @xref{TODO}. - -@menu -* Missing Sub-selects:: Sub-selects -* Missing SELECT INTO TABLE:: @code{SELECT INTO TABLE} -* Missing Transactions:: Transactions -* Missing Triggers:: Triggers -* Missing Foreign Keys:: Foreign Keys -* Missing Views:: Views -* Missing comments:: @samp{--} as the start of a comment -@end menu - -@node Missing Sub-selects, Missing SELECT INTO TABLE, Missing functions, Missing functions -@subsection Sub-selects -@cindex sub-selects - -The following will not yet work in @strong{MySQL}: - -@example -SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); -SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); -SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id); -@end example - -However, in many cases you can rewrite the query without a sub-select: - -@example -SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; -SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL -@end example - -For more complicated subqueries you can often create temporary tables -to hold the subquery. In some cases, however this option will not -work. The most frequently encountered of these cases arises with -@code{DELETE} statements, for which standard SQL does not support joins -(except in sub-selects). For this situation there are two options -available until subqueries are supported by @strong{MySQL}. - -The first option is to use a procedural programming language (such as -Perl or PHP) to submit a @code{SELECT} query to obtain the primary keys -for the records to be deleted, and then use these values to construct -the @code{DELETE} statement (@code{DELETE FROM ... WHERE ... IN (key1, -key2, ...)}). - -The second option is to use interactive SQL to contruct a set of -@code{DELETE} statements automatically, using the @strong{MySQL} -extension @code{CONCAT()} (in lieu of the standard @code{||} operator). -For example: - -@example -SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';') - FROM tab1, tab2 - WHERE tab1.col1 = tab2.col2; -@end example - -You can place this query in a script file and redirect input from it to -the @code{mysql} command-line interpreter, piping its output back to a -second instance of the interpreter: - -@example -prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb -@end example - -@strong{MySQL} only supports @code{INSERT ... SELECT ...} and -@code{REPLACE ... SELECT ...} Independent sub-selects will probably -be available in Version 4.0. You can now use the function @code{IN()} in -other contexts, however. - -@node Missing SELECT INTO TABLE, Missing Transactions, Missing Sub-selects, Missing functions -@subsection @code{SELECT INTO TABLE} -@findex SELECT INTO TABLE - -@strong{MySQL} doesn't yet support the Oracle SQL extension: -@code{SELECT ... INTO TABLE ...}. @strong{MySQL} supports instead the -ANSI SQL syntax @code{INSERT INTO ... SELECT ...}, which is basically -the same thing. @xref{INSERT SELECT}. - -@example -INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE -tblTemp1.fldOrder_ID > 100; -@end example - -Alternatively, you can use @code{SELECT INTO OUTFILE...} or @code{CREATE -TABLE ... SELECT} to solve your problem. - -@node Missing Transactions, Missing Triggers, Missing SELECT INTO TABLE, Missing functions -@subsection Transactions -@cindex transactions, support - -As @strong{MySQL} does nowadays support transactions, the following -discussion is only valid if you are only using the non-transaction-safe -table types. @xref{COMMIT}. - -The question is often asked, by the curious and the critical, ``Why is -@strong{MySQL} not a transactional database?'' or ``Why does @strong{MySQL} -not support transactions?'' - -@strong{MySQL} has made a conscious decision to support another paradigm -for data integrity, ``atomic operations.'' It is our thinking and -experience that atomic operations offer equal or even better integrity -with much better performance. We, nonetheless, appreciate and understand -the transactional database paradigm and plan, within the next few releases, -to introduce transaction-safe tables on a per table basis. We will be -giving our users the possibility to decide if they need the speed of -atomic operations or if they need to use transactional features in their -applications. - -How does one use the features of @strong{MySQL} to maintain rigorous integrity -and how do these features compare with the transactional paradigm? - -First, in the transactional paradigm, if your applications are written -in a way that is dependent on the calling of ``rollback'' instead of -``commit'' in critical situations, then transactions are more -convenient. Moreover, transactions ensure that unfinished updates or -corrupting activities are not committed to the database; the server is -given the opportunity to do an automatic rollback and your database is -saved. - -@strong{MySQL}, in almost all cases, allows you to solve for potential -problems by including simple checks before updates and by running simple -scripts that check the databases for inconsistencies and automatically -repair or warn if such occurs. Note that just by using the -@strong{MySQL} log or even adding one extra log, one can normally fix -tables perfectly with no data integrity loss. - -Moreover, fatal transactional updates can be rewritten to be -atomic. In fact,we will go so far as to say that all integrity problems -that transactions solve can be done with @code{LOCK TABLES} or atomic updates, -ensuring that you never will get an automatic abort from the database, -which is a common problem with transactional databases. - -Not even transactions can prevent all loss if the server goes down. In -such cases even a transactional system can lose data. The difference -between different systems lies in just how small the time-lap is where -they could lose data. No system is 100% secure, only ``secure -enough.'' Even Oracle, reputed to be the safest of transactional -databases, is reported to sometimes lose data in such situations. - -To be safe with @strong{MySQL}, you only need to have backups and have -the update logging turned on. With this you can recover from any -situation that you could with any transactional database. It is, of -course, always good to have backups, independent of which database you -use. - -The transactional paradigm has its benefits and its drawbacks. Many -users and application developers depend on the ease with which they can -code around problems where an abort appears to be, or is necessary, and they -may have to do a little more work with @strong{MySQL} to either think -differently or write more. If you are new to the atomic operations -paradigm, or more familiar or more comfortable with transactions, do not -jump to the conclusion that @strong{MySQL} has not addressed these -issues. Reliability and integrity are foremost in our minds. Recent -estimates indicate that there are more than 1,000,000 @code{mysqld} servers -currently running, many of which are in production environments. We -hear very, very seldom from our users that they have lost any data, and -in almost all of those cases user error is involved. This is, in our -opinion, the best proof of @strong{MySQL}'s stability and reliability. - -Lastly, in situations where integrity is of highest importance, -@strong{MySQL}'s current features allow for transaction-level or better -reliability and integrity. If you lock tables with @code{LOCK TABLES}, all -updates will stall until any integrity checks are made. If you only obtain -a read lock (as opposed to a write lock), then reads and inserts are -still allowed to happen. The new inserted records will not be seen by -any of the clients that have a @code{READ} lock until they release their read -locks. With @code{INSERT DELAYED} you can queue inserts into a local queue, -until the locks are released, without having the client wait for the insert -to complete. @xref{INSERT DELAYED}. - -``Atomic,'' in the sense that we mean it, is nothing magical. It only means -that you can be sure that while each specific update is running, no other -user can interfere with it, and there will never be an automatic -rollback (which can happen on transaction based systems if you are not -very careful). @strong{MySQL} also guarantees that there will not be -any dirty reads. You can find some example of how to write atomic updates -in the commit-rollback section. @xref{Commit-rollback}. - -We have thought quite a bit about integrity and performance, and we -believe that our atomic operations paradigm allows for both high -reliability and extremely high performance, on the order of three to -five times the speed of the fastest and most optimally tuned of -transactional databases. We didn't leave out transactions because they -are hard to do. The main reason we went with atomic operations as -opposed to transactions is that by doing this we could apply many speed -optimizations that would not otherwise have been possible. - -Many of our users who have speed foremost in their minds are not at all -concerned about transactions. For them transactions are not an -issue. For those of our users who are concerned with or have wondered -about transactions vis-a-vis @strong{MySQL}, there is a ``@strong{MySQL} -way'' as we have outlined above. For those where safety is more -important than speed, we recommend them to use the @code{BDB}, -or @code{InnoDB} tables for all their critical data. @xref{Table types}. - -One final note: We are currently working on a safe replication schema -that we believe to be better than any commercial replication system we -know of. This system will work most reliably under the atomic -operations, non-transactional, paradigm. Stay tuned. - -@node Missing Triggers, Missing Foreign Keys, Missing Transactions, Missing functions -@subsection Stored Procedures and Triggers -@cindex stored procedures and triggers, defined -@cindex procedures, stored -@cindex triggers, stored - - -A stored procedure is a set of SQL commands that can be compiled and stored -in the server. Once this has been done, clients don't need to keep reissuing -the entire query but can refer to the stored procedure. This provides better -performance because the query has to be parsed only once, and less information -needs to be sent between the server and the client. You can also raise the -conceptual level by having libraries of functions in the server. - -A trigger is a stored procedure that is invoked when a particular event -occurs. For example, you can install a stored procedure that is triggered -each time a record is deleted from a transaction table and that automatically -deletes the corresponding customer from a customer table when all his -transactions are deleted. - -The planned update language will be able to -handle stored procedures, but without triggers. Triggers usually slow -down everything, even queries for which they are not needed. - -To see when @strong{MySQL} might get stored procedures, see @ref{TODO}. - -@node Missing Foreign Keys, Missing Views, Missing Triggers, Missing functions -@subsection Foreign Keys -@cindex foreign keys -@cindex keys, foreign - -Note that foreign keys in SQL are not used to join tables, but are used -mostly for checking referential integrity (foreign key constraints). If -you want to get results from multiple tables from a @code{SELECT} -statement, you do this by joining tables: - -@example -SELECT * from table1,table2 where table1.id = table2.id; -@end example - -@xref{JOIN, , @code{JOIN}}. @xref{example-Foreign keys}. - -The @code{FOREIGN KEY} syntax in @strong{MySQL} exists only for compatibility -with other SQL vendors' @code{CREATE TABLE} commands; it doesn't do -anything. The @code{FOREIGN KEY} syntax without @code{ON DELETE ...} is -mostly used for documentation purposes. Some ODBC applications may use this -to produce automatic @code{WHERE} clauses, but this is usually easy to -override. @code{FOREIGN KEY} is sometimes used as a constraint check, but -this check is unnecessary in practice if rows are inserted into the tables in -the right order. @strong{MySQL} only supports these clauses because some -applications require them to exist (regardless of whether or not they -work). - -In @strong{MySQL}, you can work around the problem of @code{ON DELETE -...} not being implemented by adding the appropriate @code{DELETE} statement to -an application when you delete records from a table that has a foreign key. -In practice this is as quick (in some cases quicker) and much more portable -than using foreign keys. - -In the near future we will extend the @code{FOREIGN KEY} implementation so -that at least the information will be saved in the table specification file -and may be retrieved by @code{mysqldump} and ODBC. At a later stage we will -implement the foreign key constraints for application that can't easily be -coded to avoid them. - -@menu -* Broken Foreign KEY:: Reasons NOT to use foreign keys constraints -@end menu - -@node Broken Foreign KEY, , Missing Foreign Keys, Missing Foreign Keys -@subsubsection Reasons NOT to Use Foreign Keys constraints -@cindex foreign keys, reasons not to use - -There are so many problems with foreign key constraints that we don't -know where to start: - -@itemize @bullet -@item -Foreign key constraints make life very complicated, because the foreign -key definitions must be stored in a database and implementing them would -destroy the whole ``nice approach'' of using files that can be moved, -copied, and removed. - -@item -The speed impact is terrible for @code{INSERT} and @code{UPDATE} -statements, and in this case almost all @code{FOREIGN KEY} constraint -checks are useless because you usually insert records in the right -tables in the right order, anyway. - -@item -There is also a need to hold locks on many more tables when updating one -table, because the side effects can cascade through the entire database. It's -MUCH faster to delete records from one table first and subsequently delete -them from the other tables. - -@item -You can no longer restore a table by doing a full delete from the table -and then restoring all records (from a new source or from a backup). - -@item -If you use foreign key constraints you can't dump and restore tables -unless you do so in a very specific order. - -@item -It's very easy to do ``allowed'' circular definitions that make the -tables impossible to re-create each table with a single create statement, -even if the definition works and is usable. - -@item -It's very easy to overlook @code{FOREIGN KEY ... ON DELETE} rules when -one codes an application. It's not unusual that one loses a lot of -important information just because a wrong or misused @code{ON DELETE} rule. -@end itemize - -The only nice aspect of @code{FOREIGN KEY} is that it gives ODBC and some -other client programs the ability to see how a table is connected and to use -this to show connection diagrams and to help in building applications. - -@strong{MySQL} will soon store @code{FOREIGN KEY} definitions so that a -client can ask for and receive an answer about how the original -connection was made. The current @file{.frm} file format does not have -any place for it. At a later stage we will implement the foreign key -constraints for application that can't easily be coded to avoid them. - -@node Missing Views, Missing comments, Missing Foreign Keys, Missing functions -@subsection Views -@cindex views - -@strong{MySQL} doesn't yet support views, but we plan to implement these -to about 4.1. - -Views are mostly useful for letting users access a set of relations as one -table (in read-only mode). Many SQL databases don't allow one to update -any rows in a view, but you have to do the updates in the separate tables. - -As @strong{MySQL} is mostly used in applications and on web system where -the application writer has full control on the database usage, most of -our users haven't regarded views to be very important. (At least no one -has been interested enough in this to be prepared to finance the -implementation of views). - -One doesn't need views in @strong{MySQL} to restrict access to columns -as @strong{MySQL} has a very sophisticated privilege -system. @xref{Privilege system}. - -@node Missing comments, , Missing Views, Missing functions -@subsection @samp{--} as the Start of a Comment -@cindex comments, starting -@cindex starting, comments - -Some other SQL databases use @samp{--} to start comments. @strong{MySQL} -has @samp{#} as the start comment character, even if the @code{mysql} -command-line tool removes all lines that start with @samp{--}. -You can also use the C comment style @code{/* this is a comment */} with -@strong{MySQL}. -@xref{Comments}. - -@strong{MySQL} Version 3.23.3 and above supports the @samp{--} comment style -only if the comment is followed by a space. This is because this -degenerate comment style has caused many problems with automatically -generated SQL queries that have used something like the following code, -where we automatically insert the value of the payment for -@code{!payment!}: - -@example -UPDATE tbl_name SET credit=credit-!payment! -@end example - -What do you think will happen when the value of @code{payment} is negative? - -Because @code{1--1} is legal in SQL, we think it is terrible that -@samp{--} means start comment. - -In @strong{MySQL} Version 3.23 you can, however, use: -@code{1-- This is a comment} - -The following discussion only concerns you if you are running a @strong{MySQL} -version earlier than Version 3.23: - -If you have a SQL program in a text file that contains @samp{--} comments -you should use: - -@example -shell> replace " --" " #" < text-file-with-funny-comments.sql \ - | mysql database -@end example - -instead of the usual: - -@example -shell> mysql database < text-file-with-funny-comments.sql -@end example - -You can also edit the command file ``in place'' to change the @samp{--} -comments to @samp{#} comments: - -@example -shell> replace " --" " #" -- text-file-with-funny-comments.sql -@end example - -Change them back with this command: - -@example -shell> replace " #" " --" -- text-file-with-funny-comments.sql -@end example - -@node Standards, Commit-rollback, Missing functions, Compatibility -@section What Standards Does MySQL Follow? - -Entry level SQL92. ODBC levels 0-2. - -@node Commit-rollback, , Standards, Compatibility -@section How to Cope Without @code{COMMIT}/@code{ROLLBACK} -@findex COMMIT -@findex ROLLBACK -@cindex transaction-safe tables -@cindex tables, updating -@cindex updating, tables -@cindex @code{BDB} tables -@cindex @code{InnoDB} tables - -The following mostly applies only for @code{ISAM}, @code{MyISAM}, and -@code{HEAP} tables. If you only use transaction-safe tables (@code{BDB}, -or @code{InnoDB} tables) in an an update, you can do -@code{COMMIT} and @code{ROLLBACK} also with @strong{MySQL}. -@xref{COMMIT}. - -The problem with handling @code{COMMIT}-@code{ROLLBACK} efficiently with -the above table types would require a completely different table layout -than @strong{MySQL} uses today. The table type would also need extra -threads that do automatic cleanups on the tables, and the disk usage -would be much higher. This would make these table types about 2-4 times -slower than they are today. - -For the moment, we prefer implementing the SQL server language (something -like stored procedures). With this you would very seldom really need -@code{COMMIT}-@code{ROLLBACK.} This would also give much better performance. - -Loops that need transactions normally can be coded with the help of -@code{LOCK TABLES}, and you don't need cursors when you can update records -on the fly. - -We at TcX had a greater need for a real fast database than a 100% -general database. Whenever we find a way to implement these features without -any speed loss, we will probably do it. For the moment, there are many more -important things to do. Check the TODO for how we prioritize things at -the moment. (Customers with higher levels of support can alter this, so -things may be reprioritized.) - -The current problem is actually @code{ROLLBACK}. Without -@code{ROLLBACK}, you can do any kind of @code{COMMIT} action with -@code{LOCK TABLES}. To support @code{ROLLBACK} with the above table -types, @strong{MySQL} would have to be changed to store all old records -that were updated and revert everything back to the starting point if -@code{ROLLBACK} was issued. For simple cases, this isn't that hard to do -(the current @code{isamlog} could be used for this purpose), but it -would be much more difficult to implement @code{ROLLBACK} for -@code{ALTER/DROP/CREATE TABLE}. - -To avoid using @code{ROLLBACK}, you can use the following strategy: - -@enumerate -@item -Use @code{LOCK TABLES ...} to lock all the tables you want to access. -@item -Test conditions. -@item -Update if everything is okay. -@item -Use @code{UNLOCK TABLES} to release your locks. -@end enumerate - -This is usually a much faster method than using transactions with possible -@code{ROLLBACK}s, although not always. The only situation this solution -doesn't handle is when someone kills the threads in the middle of an -update. In this case, all locks will be released but some of the updates may -not have been executed. - -You can also use functions to update records in a single operation. -You can get a very efficient application by using the following techniques: - -@itemize @bullet -@item Modify fields relative to their current value. -@item Update only those fields that actually have changed. -@end itemize - -For example, when we are doing updates to some customer information, we -update only the customer data that has changed and test only that none of -the changed data, or data that depend on the changed data, has changed -compared to the original row. The test for changed data is done with the -@code{WHERE} clause in the @code{UPDATE} statement. If the record wasn't -updated, we give the client a message: "Some of the data you have changed -have been changed by another user". Then we show the old row versus the new -row in a window, so the user can decide which version of the customer record -he should use. - -This gives us something that is similar to column locking but is actually -even better, because we only update some of the columns, using values that -are relative to their current values. This means that typical @code{UPDATE} -statements look something like these: - -@example -UPDATE tablename SET pay_back=pay_back+'relative change'; - -UPDATE customer - SET - customer_date='current_date', - address='new address', - phone='new phone', - money_he_owes_us=money_he_owes_us+'new_money' - WHERE - customer_id=id AND address='old address' AND phone='old phone'; -@end example - -As you can see, this is very efficient and works even if another client has -changed the values in the @code{pay_back} or @code{money_he_owes_us} columns. - -@findex mysql_insert_id() -@findex LAST_INSERT_ID() -In many cases, users have wanted @code{ROLLBACK} and/or @code{LOCK -TABLES} for the purpose of managing unique identifiers for some tables. This -can be handled much more efficiently by using an @code{AUTO_INCREMENT} column -and either the SQL function @code{LAST_INSERT_ID()} or the C API function -@code{mysql_insert_id()}. @xref{mysql_insert_id, , @code{mysql_insert_id()}}. - -@cindex rows, locking -At @strong{MySQL AB}, we have never had any need for row-level locking -because we have always been able to code around it. Some cases really need -row locking, but they are very few. If you want row-level locking, you -can use a flag column in the table and do something like this: - -@example -UPDATE tbl_name SET row_flag=1 WHERE id=ID; -@end example - -@strong{MySQL} returns 1 for the number of affected rows if the row was -found and @code{row_flag} wasn't already 1 in the original row. - -You can think of it as @strong{MySQL} changed the above query to: - -@example -UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1; -@end example - -@node Privilege system, Reference, Compatibility, Top +@node Privilege system, Reference, Installing, Top @chapter The MySQL Access Privilege System @cindex system, security @cindex access privileges @@ -25865,7 +27719,7 @@ Finland @cindex tables, @code{BDB} @cindex tables, @code{Berkeley DB} -@node BDB, , InnoDB, Table types +@node BDB, , InnoDB, Table types @section BDB or Berkeley_DB Tables @menu @@ -28536,7 +30390,6 @@ group by @menu * Languages:: What languages are supported by @strong{MySQL}? -* Table size:: How big @strong{MySQL} tables can be @end menu This chapter describes the languages @strong{MySQL} supports, how sorting @@ -28547,7 +30400,7 @@ You will also find information about maximum table sizes in this chapter. @cindex messages, languages @cindex files, error messages @cindex language support -@node Languages, Table size, Server, Server +@node Languages, , Server, Server @section What Languages Are Supported by MySQL? @code{mysqld} can issue error messages in the following languages: @@ -28861,60 +30714,6 @@ You must specify the @code{mbmaxlen_MYSET=N} value in the special comment at the top of the source file. @code{N} should be set to the size in bytes of the largest character in the set. -@cindex tables, maximum size -@cindex size of tables -@cindex operating systems, file size limits -@cindex limits, file size -@cindex files, size limits -@node Table size, , Languages, Server -@section How Big MySQL Tables Can Be - -@strong{MySQL} Version 3.22 has a 4G limit on table size. With the new -@code{MyISAM} in @strong{MySQL} Version 3.23 the maximum table size is -pushed up to 8 million terabytes (2 ^ 63 bytes). - -Note, however, that operating systems have their own file size -limits. Here are some examples: - -@multitable @columnfractions .5 .5 -@item @strong{Operating System} @tab @strong{File Size Limit} -@item Linux-Intel 32 bit @tab 2G, 4G or more, depends on Linux version -@item Linux-Alpha @tab 8T (?) -@item Solaris 2.5.1 @tab 2G (possible 4G with patch) -@item Solaris 2.6 @tab 4G -@item Solaris 2.7 Intel @tab 4G -@item Solaris 2.7 ULTRA-SPARC @tab 8T (?) -@end multitable - -On Linux 2.2 you can get bigger tables than 2G by using the LFS patch for -the ext2 file system. On Linux 2.4 there exists also patches for ReiserFS -to get support for big files. - -This means that the table size for @strong{MySQL} is normally limited by -the operating system. - -By default, @strong{MySQL} tables have a maximum size of about 4G. You can -check the maximum table size for a table with the @code{SHOW TABLE STATUS} -command or with the @code{myisamchk -dv table_name}. -@xref{SHOW}. - -If you need bigger tables than 4G (and your operating system supports -this), you should set the @code{AVG_ROW_LENGTH} and @code{MAX_ROWS} -parameter when you create your table. @xref{CREATE TABLE}. You can -also set these later with @code{ALTER TABLE}. @xref{ALTER TABLE}. - -If your big table is going to be read-only, you could use -@code{myisampack} to merge and compress many tables to one. -@code{myisampack} usually compresses a table by at least 50%, so you can -have, in effect, much bigger tables. @xref{myisampack, , -@code{myisampack}}. - -You can go around the operating system file limit for @code{MyISAM} data -files by using the @code{RAID} option. @xref{CREATE TABLE}. - -Another solution can be the included MERGE library, which allows you to -handle a collection of identical tables as one. @xref{MERGE, MERGE -tables}. @cindex replication @cindex increasing, speed @@ -39027,7 +40826,7 @@ to the master database. @xref{Log Replication}. @cindex @code{mysqlclient} library @cindex buffer sizes, client @cindex library, @code{mysqlclient} -@node Clients, Comparisons, Log files, Top +@node Clients, MySQL internals, Log files, Top @chapter MySQL APIs @menu @@ -42488,1000 +44287,11 @@ The @uref{http://www.mysql.com/Downloads/Contrib,Contrib directory} contains a Tcl interface that is based on msqltcl 1.50. -@cindex databases, MySQL vs. others -@cindex comparisons, MySQL vs. others -@node Comparisons, MySQL internals, Clients, Top -@chapter How MySQL Compares to Other Databases - -@menu -* Compare mSQL:: How @strong{MySQL} compares to @code{mSQL} -* Compare PostgreSQL:: How @strong{MySQL} compares with PostgreSQL -@end menu - -This chapter compares @strong{MySQL} to other popular databases. - -This chapter has been written by the @strong{MySQL} developers, so it -should be read with that in mind. There are no factual errors contained -in this chapter that we know of. If you find something which you believe -to be an error, please contact us about it at @email{docs@@mysql.com}. - -For a list of all supported limits, functions, and types, see the -@code{crash-me} Web page at -@uref{http://www.mysql.com/information/crash-me.php}. - -@node Compare mSQL, Compare PostgreSQL, Comparisons, Comparisons -@section How MySQL Compares to @code{mSQL} - -@table @strong -@item Performance - -For a true comparison of speed, consult the growing @strong{MySQL} benchmark -suite. @xref{Benchmarks}. - -Because there is no thread creation overhead, a small parser, few features, and -simple security, @code{mSQL} should be quicker at: - -@itemize @bullet -@item -Tests that perform repeated connects and disconnects, running a very simple -query during each connection. -@item -@code{INSERT} operations into very simple tables with few columns and keys. -@item -@code{CREATE TABLE} and @code{DROP TABLE}. -@item -@code{SELECT} on something that isn't an index. (A table scan is very -easy.) -@end itemize - -Because these operations are so simple, it is hard to be better at them when -you have a higher startup overhead. After the connection is established, -@strong{MySQL} should perform much better. - -On the other hand, @strong{MySQL} is much faster than @code{mSQL} (and -most other SQL implementations) on the following: - -@itemize @bullet -@item -Complex @code{SELECT} operations. -@item -Retrieving large results (@strong{MySQL} has a better, faster, and safer -protocol). -@item -Tables with variable-length strings, because @strong{MySQL} has more efficient -handling and can have indexes on @code{VARCHAR} columns. -@item -Handling tables with many columns. -@item -Handling tables with large record lengths. -@item -@code{SELECT} with many expressions. -@item -@code{SELECT} on large tables. -@item -Handling many connections at the same time. @strong{MySQL} is fully -multi-threaded. Each connection has its own thread, which means that -no thread has to wait for another (unless a thread is modifying -a table another thread wants to access). In @code{mSQL}, once one connection -is established, all others must wait until the first has finished, regardless -of whether the connection is running a query that is short or long. When the -first connection terminates, the next can be served, while all the others wait -again, etc. -@item -Joins. -@code{mSQL} can become pathologically slow if you change the order of tables -in a @code{SELECT}. In the benchmark suite, a time more than 15000 times -slower than @strong{MySQL} was seen. This is due to @code{mSQL}'s lack of a -join optimizer to order tables in the optimal order. However, if you put the -tables in exactly the right order in @code{mSQL}2 and the @code{WHERE} is -simple and uses index columns, the join will be relatively fast! -@xref{Benchmarks}. -@item -@code{ORDER BY} and @code{GROUP BY}. -@item -@code{DISTINCT}. -@item -Using @code{TEXT} or @code{BLOB} columns. -@end itemize - -@item SQL Features - -@itemize @bullet -@item @code{GROUP BY} and @code{HAVING}. -@code{mSQL} does not support @code{GROUP BY} at all. -@strong{MySQL} supports a full @code{GROUP BY} with both @code{HAVING} and -the following functions: @code{COUNT()}, @code{AVG()}, @code{MIN()}, -@code{MAX()}, @code{SUM()}, and @code{STD()}. @code{COUNT(*)} is optimized to -return very quickly if the @code{SELECT} retrieves from one table, no other -columns are retrieved, and there is no @code{WHERE} clause. @code{MIN()} and -@code{MAX()} may take string arguments. - -@item @code{INSERT} and @code{UPDATE} with calculations. -@strong{MySQL} can do calculations in an @code{INSERT} or @code{UPDATE}. -For example: -@example -mysql> UPDATE SET x=x*10+y WHERE x<20; -@end example - -@item Aliasing. -@strong{MySQL} has column aliasing. - -@item Qualifying column names. -In @strong{MySQL}, if a column name is unique among the tables used in a -query, you do not have to use the full qualifier. - -@item @code{SELECT} with functions. -@strong{MySQL} has many functions (too many to list here; see @ref{Functions}). - -@end itemize - -@item Disk Space Efficiency -That is, how small can you make your tables? - -@strong{MySQL} has very precise types, so you can create tables that take -very little space. An example of a useful @strong{MySQL} datatype is the -@code{MEDIUMINT} that is 3 bytes long. If you have 100,000,000 records, -saving even one byte per record is very important. - -@code{mSQL2} has a more limited set of column types, so it is -more difficult to get small tables. -@item Stability -This is harder to judge objectively. For a discussion of @strong{MySQL} -stability, see @ref{Stability}. - -We have no experience with @code{mSQL} stability, so we cannot say -anything about that. - -@item Price -Another important issue is the license. @strong{MySQL} has a -more flexible license than @code{mSQL}, and is also less expensive than -@code{mSQL}. Whichever product you choose to use, remember to at least -consider paying for a license or e-mail support. (You are required to get -a license if you include @strong{MySQL} with a product that you sell, -of course.) - -@item Perl Interfaces -@strong{MySQL} has basically the same interfaces to Perl as @code{mSQL} with -some added features. - -@item JDBC (Java) -@strong{MySQL} currently has a lot of different JDBC drivers: -@itemize @bullet -@item -The mm driver: A type 4 JDBC driver by Mark Matthews -@email{mmatthew@@ecn.purdue.edu}. This is released under the LGPL. -@item -The Resin driver. This is a commercial JDBC driver released under open -source. @uref{http://www.caucho.com/projects/jdbc-mysql/index.xtp} -@item -The gwe driver: A Java interface by GWE technologies (not supported anymore). -@item -The jms driver: An improved gwe driver by Xiaokun Kelvin ZHU -@email{X.Zhu@@brad.ac.uk} (not supported anymore). -@item -The twz driver: A type 4 JDBC driver by Terrence W. Zellers -@email{zellert@@voicenet.com}. This is commercial but is free for private -and educational use (not supported anymore). -@end itemize - -The recommended driver is the mm driver. The Resin driver may also be -good (at least the benchmarks looks good), but we haven't received that much -information about this yet. - -We know that @code{mSQL} has a JDBC driver, but we have too little experience -with it to compare. - -@item Rate of Development -@strong{MySQL} has a very small team of developers, but we are quite -used to coding C and C++ very rapidly. Because threads, functions, -@code{GROUP BY}, and so on are still not implemented in @code{mSQL}, it -has a lot of catching up to do. To get some perspective on this, you -can view the @code{mSQL} @file{HISTORY} file for the last year and -compare it with the News section of the @strong{MySQL} Reference Manual -(@pxref{News}). It should be pretty obvious which one has developed -most rapidly. - -@item Utility Programs -Both @code{mSQL} and @strong{MySQL} have many interesting third-party -tools. Because it is very easy to port upward (from @code{mSQL} to -@strong{MySQL}), almost all the interesting applications that are available for -@code{mSQL} are also available for @strong{MySQL}. - -@strong{MySQL} comes with a simple @code{msql2mysql} program that fixes -differences in spelling between @code{mSQL} and @strong{MySQL} for the -most-used C API functions. -For example, it changes instances of @code{msqlConnect()} to -@code{mysql_connect()}. Converting a client program from @code{mSQL} to -@strong{MySQL} usually takes a couple of minutes. -@end table - -@menu -* Using mSQL tools:: How to convert @code{mSQL} tools for @strong{MySQL} -* Protocol differences:: How @code{mSQL} and @strong{MySQL} client/server communications protocols differ -* Syntax differences:: How @code{mSQL} 2.0 SQL syntax differs from @strong{MySQL} -@end menu - -@cindex MySQL tools, conversion -@cindex converting, tools -@cindex tools, converting -@node Using mSQL tools, Protocol differences, Compare mSQL, Compare mSQL -@subsection How to Convert @code{mSQL} Tools for MySQL - -According to our experience, it would just take a few hours to convert tools -such as @code{msql-tcl} and @code{msqljava} that use the -@code{mSQL} C API so that they work with the @strong{MySQL} C API. - -The conversion procedure is: - -@enumerate -@item -Run the shell script @code{msql2mysql} on the source. This requires the -@code{replace} program, which is distributed with @strong{MySQL}. -@item -Compile. -@item -Fix all compiler errors. -@end enumerate - -Differences between the @code{mSQL} C API and the @strong{MySQL} C API are: -@itemize @bullet -@item -@strong{MySQL} uses a @code{MYSQL} structure as a connection type (@code{mSQL} -uses an @code{int}). -@item -@code{mysql_connect()} takes a pointer to a @code{MYSQL} structure as a -parameter. It is easy to define one globally or to use @code{malloc()} to get -one. -@code{mysql_connect()} also takes two parameters for specifying the user and -password. You may set these to @code{NULL, NULL} for default use. -@item -@code{mysql_error()} takes the @code{MYSQL} structure as a parameter. Just add -the parameter to your old @code{msql_error()} code if you are porting old code. -@item -@strong{MySQL} returns an error number and a text error message for all -errors. @code{mSQL} returns only a text error message. -@item -Some incompatibilities exist as a result of @strong{MySQL} supporting -multiple connections to the server from the same process. -@end itemize - -@cindex communications protocols -@cindex mSQL vs. MySQL -@node Protocol differences, Syntax differences, Using mSQL tools, Compare mSQL -@subsection How @code{mSQL} and MySQL Client/Server Communications Protocols Differ - -There are enough differences that it is impossible (or at least not easy) -to support both. - -The most significant ways in which the @strong{MySQL} protocol differs -from the @code{mSQL} protocol are listed below: - -@itemize @bullet -@item -A message buffer may contain many result rows. -@item -The message buffers are dynamically enlarged if the query or the -result is bigger than the current buffer, up to a configurable server and -client limit. -@item -All packets are numbered to catch duplicated or missing packets. -@item -All column values are sent in ASCII. The lengths of columns and rows are sent -in packed binary coding (1, 2, or 3 bytes). -@item -@strong{MySQL} can read in the result unbuffered (without having to store the -full set in the client). -@item -If a single read/write takes more than 30 seconds, the server closes -the connection. -@item -If a connection is idle for 8 hours, the server closes the connection. -@end itemize - -@node Syntax differences, , Protocol differences, Compare mSQL -@subsection How @code{mSQL} 2.0 SQL Syntax Differs from MySQL - -@noindent -@strong{Column types} - -@table @code -@item @strong{MySQL} -Has the following additional types (among others; -@pxref{CREATE TABLE, , @code{CREATE TABLE}}): -@itemize @bullet -@item -@code{ENUM} type for one of a set of strings. -@item -@code{SET} type for many of a set of strings. -@item -@code{BIGINT} type for 64-bit integers. -@end itemize -@item -@strong{MySQL} also supports -the following additional type attributes: -@itemize @bullet -@item -@code{UNSIGNED} option for integer columns. -@item -@code{ZEROFILL} option for integer columns. -@item -@code{AUTO_INCREMENT} option for integer columns that are a -@code{PRIMARY KEY}. -@xref{mysql_insert_id, , @code{mysql_insert_id()}}. -@item -@code{DEFAULT} value for all columns. -@end itemize -@item mSQL2 -@code{mSQL} column types correspond to the @strong{MySQL} types shown below: -@multitable @columnfractions .15 .85 -@item @code{mSQL} @strong{type} @tab @strong{Corresponding @strong{MySQL} type} -@item @code{CHAR(len)} @tab @code{CHAR(len)} -@item @code{TEXT(len)} @tab @code{TEXT(len)}. @code{len} is the maximal length. -And @code{LIKE} works. -@item @code{INT} @tab @code{INT}. With many more options! -@item @code{REAL} @tab @code{REAL}. Or @code{FLOAT}. Both 4- and 8-byte versions are available. -@item @code{UINT} @tab @code{INT UNSIGNED} -@item @code{DATE} @tab @code{DATE}. Uses ANSI SQL format rather than @code{mSQL}'s own format. -@item @code{TIME} @tab @code{TIME} -@item @code{MONEY} @tab @code{DECIMAL(12,2)}. A fixed-point value with two decimals. -@end multitable -@end table - -@noindent -@strong{Index Creation} - -@table @code -@item @strong{MySQL} -Indexes may be specified at table creation time with the @code{CREATE TABLE} -statement. -@item mSQL -Indexes must be created after the table has been created, with separate -@code{CREATE INDEX} statements. -@end table - -@noindent -@strong{To Insert a Unique Identifier into a Table} - -@table @code -@item @strong{MySQL} -Use @code{AUTO_INCREMENT} as a column type -specifier. -@xref{mysql_insert_id, , @code{mysql_insert_id()}}. -@item mSQL -Create a @code{SEQUENCE} on a table and select the @code{_seq} column. -@end table - -@noindent -@strong{To Obtain a Unique Identifier for a Row} - -@table @code -@item @strong{MySQL} -Add a @code{PRIMARY KEY} or @code{UNIQUE} key to the table and use this. -New in Version 3.23.11: If the @code{PRIMARY} or @code{UNIQUE} key consists of only one -column and this is of type integer, one can also refer to it as -@code{_rowid}. -@item mSQL -Use the @code{_rowid} column. Observe that @code{_rowid} may change over time -depending on many factors. -@end table - -@noindent -@strong{To Get the Time a Column Was Last Modified} - -@table @code -@item @strong{MySQL} -Add a @code{TIMESTAMP} column to the table. This column is automatically set -to the current date and time for @code{INSERT} or @code{UPDATE} statements if -you don't give the column a value or if you give it a @code{NULL} value. - -@item mSQL -Use the @code{_timestamp} column. -@end table - -@noindent -@strong{@code{NULL} Value Comparisons} - -@table @code -@item @strong{MySQL} -@strong{MySQL} follows -ANSI SQL, and a comparison with @code{NULL} is always @code{NULL}. -@item mSQL -In @code{mSQL}, @code{NULL = NULL} is TRUE. You -must change @code{=NULL} to @code{IS NULL} and @code{<>NULL} to -@code{IS NOT NULL} when porting old code from @code{mSQL} to @strong{MySQL}. -@end table - -@noindent -@strong{String Comparisons} - -@table @code -@item @strong{MySQL} -Normally, string comparisons are performed in case-independent fashion with -the sort order determined by the current character set (ISO-8859-1 Latin1 by -default). If you don't like this, declare your columns with the -@code{BINARY} attribute, which causes comparisons to be done according to the -ASCII order used on the @strong{MySQL} server host. -@item mSQL -All string comparisons are performed in case-sensitive fashion with -sorting in ASCII order. -@end table - -@noindent -@strong{Case-insensitive Searching} - -@table @code -@item @strong{MySQL} -@code{LIKE} is a case-insensitive or case-sensitive operator, depending on -the columns involved. If possible, @strong{MySQL} uses indexes if the -@code{LIKE} argument doesn't start with a wild-card character. -@item mSQL -Use @code{CLIKE}. -@end table - -@noindent -@strong{Handling of Trailing Spaces} - -@table @code -@item @strong{MySQL} -Strips all spaces at the end of @code{CHAR} and @code{VARCHAR} -columns. Use a @code{TEXT} column if this behavior is not desired. -@item mSQL -Retains trailing space. -@end table - -@noindent -@strong{@code{WHERE} Clauses} - -@table @code -@item @strong{MySQL} -@strong{MySQL} correctly prioritizes everything (@code{AND} is evaluated -before @code{OR}). To get @code{mSQL} behavior in @strong{MySQL}, use -parentheses (as shown in an example below). -@item mSQL -Evaluates everything from left to right. This means that some logical -calculations with more than three arguments cannot be expressed in any -way. It also means you must change some queries when you upgrade to -@strong{MySQL}. You do this easily by adding parentheses. Suppose you -have the following @code{mSQL} query: -@example -mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4; -@end example -To make @strong{MySQL} evaluate this the way that @code{mSQL} would, -you must add parentheses: -@example -mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4)))); -@end example -@end table - -@noindent -@strong{Access Control} - -@table @code -@item @strong{MySQL} -Has tables to store grant (permission) options per user, host, and -database. @xref{Privileges}. -@item mSQL -Has a file @file{mSQL.acl} in which you can grant read/write privileges for -users. -@item -@end table - -@cindex PostgreSQL/MySQL, overview -@node Compare PostgreSQL, , Compare mSQL, Comparisons -@section How MySQL Compares to PostgreSQL - -When reading the following, please note that both products are -continually evolving. We at @strong{MySQL AB} and the PostgreSQL -developers are both working on making our respective database as good as -possible, so we are both a serious choice to any commercial database. - -The following comparison is made by us at MySQL AB. We have tried to be -as accurate and fair as possible, but because we don't have a full -knowledge of all PostgreSQL features while we know MySQL througly, we -may have got some things wrong. We will however correct these when they -come to our attention. - -We would first like to note that @code{PostgreSQL} and @strong{MySQL} -are both widely used products, but with different design goals, even if -we are both striving to be ANSI SQL compatible. This means that for -some applications @strong{MySQL} is more suitable and for others -@code{PostgreSQL} is more suitable. When choosing which database to -use, you should first check if the database's feature set satisfies your -application. If you need speed, @strong{MySQL} is probably your best -choice. If you need some of the extra features that only @code{PostgreSQL} -can offer, you should use @code{PostgreSQL}. - -@cindex PostgreSQL/MySQL, strategies -@menu -* MySQL-PostgreSQL goals:: MySQL and PostgreSQL development strategies -* MySQL-PostgreSQL features:: Featurevise Comparison of MySQL and PostgreSQL -* MySQL-PostgreSQL benchmarks:: Benchmarking MySQL and PostgreSQL -@end menu - -@node MySQL-PostgreSQL goals, MySQL-PostgreSQL features, Compare PostgreSQL, Compare PostgreSQL -@subsection MySQL and PostgreSQL development strategies - -When adding things to MySQL we take pride to do an optimal, definite -solution. The code should be so good that we shouldn't have any need to -change it in the foreseeable future. We also do not like to sacrifice -speed for features but instead will do our utmost to find a solution -that will give maximal throughput. This means that development will take -a little longer, but the end result will be well worth this. This kind -of development is only possible because all server code are checked by -one of a few (currently two) persons before it's included in the -@strong{MySQL} server. - -We at MySQL AB believe in frequent releases to be able to push out new -features quickly to our users. Because of this we do a new small release -about every 3 weeks, which a major branch every year. All releases are -throughly tested with our testing tools on a lot of different platforms. - -PostgreSQL is based on a kernel with lots of contributors. In this setup -it makes sense to prioritize adding a lot of new features, instead of -implementing them optimally, because one can always optimize things -later if there arises a need for this. - -Another big difference between @strong{MySQL} and PostgreSQL is that -nearly all of the code in the MySQL server are coded by developers that -are employed by MySQL AB and are still working on the server code. The -exceptions are the transaction engines and the regexp library. - -This is in sharp contrast to the PostgreSQL code where the majority of -the code is coded by a big group of people with different backgrounds. -It was only recently that the PostgreSQL developers announced that they -current developer group had finally had time to take a look at all -the code in the current PostgreSQL release. - -Both of the above development methods has it's own merits and drawbacks. -We here at @strong{MySQL AB} think of course that our model is better -because our model gives better code consistence, more optimal and -reusable code and, in our opinion, fewer bugs. Because we are the -authors of the @strong{MySQL} server code we are better able to -coordinate new features and releases. - -@cindex PostgreSQL/MySQL, features -@node MySQL-PostgreSQL features, MySQL-PostgreSQL benchmarks, MySQL-PostgreSQL goals, Compare PostgreSQL -@subsection Featurevise Comparison of MySQL and PostgreSQL - -On the @uref{http://www.mysql.com/information/crash-me.php, crash-me} -page you can find a list of those database constructs and limits that -one can detect automatically with a program. Note however that a lot of -the numerical limits may be changed with startup options for respective -database. The above web page is however extremely useful when you want to -ensure that your applications works with many different databases or -when you want to convert your application from one datbase to another. - -@strong{MySQL} offers the following advantages over PostgreSQL: - -@itemize @bullet -@item -@code{MySQL} is generally much faster than PostgreSQL. -@xref{MySQL-PostgreSQL benchmarks}. -@item -Because @strong{MySQL} has a much larger user base than PostgreSQL the -code is more tested and has historically been more stable than -PostgreSQL. @strong{MySQL} is the much more used in production -environments than PostgreSQL, mostly thanks to that @strong{MySQL AB}, -former TCX DataKonsult AB, has provided top quality commercial support -for @strong{MySQL} from the day it was released, whereas until recently -PostgreSQL was unsupported. -@item -@strong{MySQL} works on more platforms than PostgreSQL. @xref{Which OS}. -@item -@strong{MySQL} works better on Windows; @strong{MySQL} is running as a -native windows application (a service on NT/Win2000/WinXP), while -PostgreSQL is run under the cygwin emulation. We have heard that -PostgreSQL is not yet that stable on windows but we haven't been able to -verify this ourselves. -@item -@strong{MySQL} has more API to other languages and is supported by more -programs than PostgreSQL. @xref{Contrib}. -@item -@strong{MySQL} works on 24/7 heavy duty systems. In most circumstances -you never have to run any cleanups on @code{MySQL}. PostgreSQL doesn't -yet support 24/7 systems because you have have to run @code{vacuum()} -once in a while to reclaim space from @code{UPDATE} and @code{DELETE} -commands and to perform statistics analyzes that are critical to get -good performance with PostgreSQL. Vacuum is also needed after adding -a lot of new rows to a table. On a busy system with lots of changes -vacuum must be run very frequently, in the worst cases even many times a -day. During the @code{vacuum()} run, which may take hours if the -database is big, the database is from a production standpoint -practically dead. The PostgreSQL team has fixing this on their TODO, -but we assume that this is not an easy thing to fix permanently. -@item -A working, tested replication feature used by sites like -@uref{http://finance.yahoo.com, Yahoo finance}, -@uref{http://www.mobile.de/,mobile.de} and -@uref{http://www.slashdot.org,Slashdot}. -@item -Included in the @strong{MySQL} distribution is included two different -testing suits (@file{mysql-test-run} and -@uref{http://www.mysql.com/information/crash-me.php,crash-me}) and a -benchmark suite. The test system is actively updated with code to test -each new feature and almost all repeatable bugs that comes to our -attention. We test @strong{MySQL} with these on a lot of platforms -before every release. These tests are more sofisticated than anything -have seen from PostgreSQL and ensures that the @strong{MySQL} code keeps -at a high standard. -@item -There are far moore books in print on @strong{MySQL} than on PostgreSQL. -O'Reilly, Sams, Que, and New Riders are all major publishers with books -about MySQL. All @strong{MySQL} features is also documented in the -@strong{MySQL} on-line manual because when a feature is implemented, the -@strong{MySQL} developers are required to document it before it's -included in the source. -@item -@strong{MySQL} has supports more of the standard ODBC functions than -@code{PostgreSQL}. -@item -@strong{MySQL} has a much more sophisticated @code{ALTER TABLE}. -@item -@strong{MySQL} has support for tables without transactions for -applications that need all speed they can get. The tables may be memory -based,@code{HEAP} tables or disk based @code{MyISAM}. @xref{Table types}. -@item -@strong{MySQL} has support for 3 different table handles that support -transactions (@code{BDB} and @code{InnoDB}). Because -every transaction engine performs differently under different -conditions, this gives the application writer more options to find an -optimal solution for his/her setup. @xref{Table types}. -@item -@code{MERGE} tables gives you a unique way to instantly make a view over -a set of identical tables and use these as one. This is perfectly for -systems where you have log files that you order for example by month. -@xref{MERGE}. -@item -The option to compress read-only tables, but still have direct access to -the rows in the table, gives you better performance by minimizing disk -reads. This is very useful when you are archiving -things.@xref{myisampack}. -@item -@strong{MySQL} has internal support for text search. @xref{Fulltext Search}. -@item -You can access many databases from the same connection (depending of course -on your privileges). -@item -@strong{MySQL} is coded from the start with multi-threading while -PostgreSQL uses processes. Because context switching and access to -common storage areas is much faster between threads, than are separate -processes, this gives @strong{MySQL} a big speed advantage in multi-user -applications and also makes it easier for @strong{MySQL} to take full -advantage of symmetric multiprocessor systems (SMP). -@item -@strong{MySQL} has a much more sophisticated privilege system than -PostgreSQL. While PostgreSQL only supports @code{INSERT}, -@code{SELECT}, @code{update/delete} grants per user on a database or a -table @strong{MySQL} allows you to define a full set of different -privileges on database, table and columns level. @strong{MySQL} also allows -you to specify the privilege on host+user combinations. @xref{GRANT}. -@item -@strong{MySQL} supports a compressed server/client protocol which -improves performance over slow links. -@item -@strong{MySQL} employs the table handler concept and is the only -relational database we know of built around this concept. This allows -different low level table types to be swapped into the SQL engine, each -table type optimized for a different performance characteristics. -@item -All @code{MySQL} table types (except @strong{InnoDB}) are implemented as -files (ie: one table per file), which makes it really easy to backup, -move, delete and even symlink databases and tables when the server is -down. -@item -Tools to repair and optimize @strong{MyISAM} tables (the most common -@strong{MySQL} table type). A repair tool is only needed when a -physical corruption of a data file happens, usually from a hardware -failure. It allows a majority of the data to be recovered. -@item -Upgrading @strong{MySQL} is painless. When you are upgrading @strong{MySQL}, -you don't need to dump/restore your data, as you have to do with most -PostgreSQL upgrades. -@end itemize - -Drawbacks with @strong{MySQL} compared to PostgreSQL: - -@itemize @bullet -@item -The transaction support in @strong{MySQL} is not yet as well tested as -PostgreSQL's system. -@item -Because @strong{MySQL} uses threads, which are still a moving target on -many OS, one must either use binaries from -@uref{http://www.mysql.com/downloads} or carefully follow our -instructions on -@uref{http://www.mysql.com/doc/I/n/Installing_source.html} to get an -optimal binary that works in all cases. -@item -Table locking, as used by the non-transactional @code{MyISAM} tables, is -in many cases faster than page locks, row locks or versioning. The -drawback however is that if one doesn't take into account how table -locks works, a single long-running query can block a table for updates -for a long time. This can usable be avoided when designing the -application. If not, one can always switch the trouble table to use one -of the transactional table types. @xref{Table locking}. -@item -With UDF (user defined functions) one can extend @strong{MySQL} with -both normal SQL functions and aggregates, but this is not as easy or as -flexible as in PostgreSQL. @xref{Adding functions}. -@item -Updates and deletes that goes over multiple tables is harder to do in -@strong{MySQL}. (Will be fixed in @strong{MySQL} 4.0 with multi-table -@code{DELETE} and multi-table @code{UPDATE} and in @strong{MySQL} 4.1 -with @code{SUB-SELECT}) -@end itemize - -PostgreSQL offers currently the following advantages over @strong{MySQL}: - -Note that because we know the @strong{MySQL} road map, we have included -in the following table the version when @strong{MySQL} should support -this feature. Unfortunately we couldn't do this for previous comparison, -because we don't know the PostgreSQL roadmap. - -@multitable @columnfractions .70 .30 -@item @strong{Feature} @tab @strong{MySQL version} -@item Subselects @tab 4.1 -@item Foreign keys @tab 4.0 and 4.1 -@item Views. @tab 4.2 -@item Stored procedures in multiple languages @tab 4.1 -@item Extensible type system. @tab Not planed -@item Unions @tab 4.0. -@item Full join. @tab 4.0 or 4.1. -@item Triggers. @tab 4.1 -@item Constrainst @tab 4.1 -@item Cursors @tab 4.1 or 4.2 -@item Extensible index types like R-trees @tab R-trees are planned to 4.2 -@item Inherited tables @tab Not planned -@end multitable - -Other reasons to use PostgreSQL: - -@itemize @bullet -@item -Standard usage is in PostgreSQL closer to ANSI SQL in some cases. -@item -One can get speed up PostgreSQL by coding things as stored procedures. -@item -Bigger team of developers that contributes to the server. -@end itemize - -Drawbacks with PostgreSQL compared to @strong{MySQL}: - -@itemize @bullet -@item -@code{Vaccum()} makes PostgreSQL hard to use in a 24/7 environment. -@item -Only transactional tables. -@item -Much slower insert/delete/update. -@end itemize - -For a complete list of drawbacks, you should also examine the first table -in this section. - -@cindex PostgreSQL/MySQL, benchmarks -@node MySQL-PostgreSQL benchmarks, , MySQL-PostgreSQL features, Compare PostgreSQL -@subsection Benchmarking MySQL and PostgreSQL - -The only open source benchmark, that we know of, that can be used to -benchmark @strong{MySQL} and PostgreSQL (and other databases) is our -own. It can be found at: -@uref{http://www.mysql.com/information/benchmarks.html}. - -We have many times asked the PostgreSQL developers and some PostgreSQL -users to help us extend this benchmark to make the definitive benchmark -for databases, but unfortunately we haven't got any feedback for this. - -We, the @strong{MySQL} developers, have because of this spent a lot of -hours to get maximum performance from PostgreSQL for the benchmarks, but -because we don't know PostgreSQL intimately we are sure that there are -things that we have missed. We have on the benchmark page documented -exactly how we did run the benchmark so that it should be easy for -anyone to repeat and verify our results. - -The benchmarks are usually run with and without the @code{--fast} -option. When run with @code{--fast} we are trying to use every trick -the server can do to get the code to execute as fast as possible. -The idea is that the normal run should show how the server would work in -a default setup and the @code{--fast} run shows how the server would do -if the application developer would use extensions in the server to make -his application run faster. - -When running with PostgreSQL and @code{--fast} we do a @code{vacuum()} -between after every major table update and drop table to make the database -in perfect shape for the following selects. The time for vacuum() is -measured separately. - -When running with PostgreSQL 7.1.1 we could however not run with -@code{--fast} because during the insert test, the postmaster (the -PostgreSQL deamon) died and the database was so corrupted that it was -impossible to restart postmaster. (The details about the machine we run -the benchmark can be found on the benchmark page). After this happened -twice, we decided to postpone the @code{--fast} test until next -PostgreSQL release. - -Before going to the other benchmarks we know of, We would like to give -some background to benchmarks: - -It's very easy to write a test that shows ANY database to be best -database in the world, by just restricting the test to something the -database is very good at and not test anything that the database is not -good at; If one after this publish the result with a single figure -things is even easier. - -This would be like we would measure the speed of @strong{MySQL} compared -to PostgreSQL by looking at the summary time of the MySQL benchmarks on -our web page. Based on this @strong{MySQL} would be more than 40 times -faster than PostgreSQL, something that is of course not true. We could -make things even worse by just taking the test where PostgreSQL performs -worst and claim that @strong{MySQL} is more than 2000 times faster than -PostgreSQL. - -The case is that @strong{MySQL} does a lot of optimizations that -PostgreSQL doesn't do and the other way around. An SQL optimizer is a -very complex thing and a company could spend years on just making the -optimizer faster and faster. - -When looking at the benchmark results you should look for things that -you do in your application and just use these results to decide which -database would be best suited for your application. The benchmark -results also shows things a particular database is not good at and should -give you a notion about things to avoid and what you may have to do in -other ways. - -We know of two benchmark tests that claims that PostgreSQL performers -better than @strong{MySQL}. These both where multi-user tests, a test -that we here at @strong{MySQL AB} haven't had time to write and include in -the benchmark suite, mainly because it's a big task to do this in a -manner that is fair against all databases. - -One is the benchmark paid for by -@uref{http://www.greatbridge.com/about/press.php?content_id=4,Great -Bridge}. - -This is the worst benchmark we have ever seen anyone ever conduct. This -was not only tuned to only test what PostgreSQL is absolutely best at, -it was also totally unfair against every other database involved in the -test. - -@strong{NOTE}: We know that not even some of the main PostgreSQL -developers did like the way Great Bridge conducted the benchmark, so we -don't blame them for the way the benchmark was made. - -This benchmark has been condemned in a lot of postings and newsgroups so -we will here just shortly repeat some things that where wrong with it. - -@itemize @bullet -@item -The tests where run with an expensive commercial tool, that makes it -impossible for an open source company like us to verify the benchmarks, -or even check how the benchmark where really done. The tool is not even -a true benchmark tool, but a application/setup testing tool. To refer -this as STANDARD benchmark tool is to stretch the truth a long way. -@item -Great Bridge admitted that they had optimized the PostgreSQL database -(with vacuum() before the test) and tuned the startup for the tests, -something they hadn't done for any of the other databases involved. To -say "This process optimizes indexes and frees up disk space a bit. The -optimized indexes boost performance by some margin". Our benchmarks -clearly indicates that the difference in running a lot of selects on a -database with and without vacuum() can easily differ by a factor of 10. -@item -The test results where also strange; The AS3AP test documentation -mentions that the test does: -"selections, simple joins, projections, aggregates, one-tuple updates, -and bulk updates" - -PostgreSQL is good at doing selects and joins (especially after a -vacuum()), but doesn't perform as well on inserts/updates; The -benchmarks seem to indicate that only SELECTs where done (or very few -updates) . This could easily explain they good results for PostgreSQL in -this test. The bad results for MySQL will be obvious a bit down in this -document. -@item -They did run the so called benchmark from a Windows machine against a -Linux machine over ODBC, a setup that no normal database user would ever -do when running a heavy multi-user application. This tested more the -ODBC driver and the Windows protocol used between the clients than the -database itself. -@item -When running the database against Oracle and MS-SQL (Great Bridge has -indirectly indicated that the databases they used in the test), -they didn't use the native protocol but instead ODBC. Anyone that has -ever used Oracle, knows that all real application uses the native -interface instead of ODBC. Doing a test through ODBC and claiming that -the results had anything to do with using the database for real can't -be regarded as fair play. They should have done two tests with and -without ODBC to provide the right facts (after having got experts to tune -all involved databases of course). -@item -They refer to the TPC-C tests, but doesn't anywhere mention that the -tests they did where not a true TPC-C test and they where not even -allowed to call it a TPC-C test. A TPC-C test can only be conducted by -the rules approved by the @uref{http://www.tpc.org,TPC-council}. Great -Bridge didn't do that. By doing this they have both violated the TPC -trademark and miscredited their own benchmarks. The rules set by the -TPC-council are very strict to ensure that no one can produce false -results or make unprovable statements. Apparently Great Bridge wasn't -interested in doing this. -@item -After the first test, we contacted Great Bridge and mentioned to them -some of the obvious mistakes they had done with @strong{MySQL}; Running -with a debug version of our ODBC driver, running on a Linux system that -wasn't optimized for threads, using an old MySQL version when there was -a recommended newer one available, not starting @strong{MySQL} with the -right options for heavy multi-user use (the default installation of -MySQL is tuned for minimal resource use). Great Bridge did run a new -test, with our optimized ODBC driver and with better startup options for -MySQL, but refused to either use our updated glibc library or our -standard binary (used by 80% of our users), which was statically linked -with a fixed glibc library. - -According to what we know, Great Bridge did nothing to ensure that the -other databases where setup correctly to run good in their test -environment. We are sure however that they didn't contact Oracle or -Microsoft to ask for their advice in this matter ;) -@item -The benchmark was paid for by Great Bridge, and they decided to publish -only partial chosen results (instead of publishing it all). -@end itemize - -Tim Perdue, a long time PostgreSQL fan and a reluctant MySQL user -published a comparison on -@uref{http://www.phpbuilder.com/columns/tim20001112.php3,phpbuider}. - -When we got aware of the comparison, we phoned Tim Perdue about this -because there was a lot of strange things in his results. For example, -he claimed that MySQL had a problem with five users in his tests, when we -know that there are users with similar machines as his that are using -MySQL with 2000 simultaneous connections doing 400 queries per second (In -this case the limit was the web bandwidth, not the database). - -It sounded like he was using a Linux kernel that either had some -problems with many threads (Linux kernels before 2.4 had a problem with -this but we have documented how to fix this and Tim should be aware of -this problem). The other possible problem could have been an old glibc -library and that Tim didn't use a MySQL binary from our site, which is -linked with a corrected glibc library, but had compiled a version of his -own with. In any of the above cases, the symptom would have been exactly -what Tim had measured. - -We asked Tim if we could get access to his data so that we could repeat -the benchmark and if he could check the MySQL version on the machine to -find out what was wrong and he promised to come back to us about this. -He has not done that yet. - -Because of this we can't put any trust in this benchmark either :( - -Conclusion: - -The only benchmarks that exist today that anyone can download and run -against @strong{MySQL}and PostgreSQL is the MySQL benchmarks. We here -at @strong{MySQL} believe that open source databases should be tested -with open source tools! This is the only way to ensure that no one -does tests that nobody can reproduce and use this to claim that a -database is better than another. Without knowing all the facts it's -impossible to answer the claims of the tester. - -The thing we find strange is that every test we have seen about -PostgreSQL, that is impossible to reproduce, claims that PostgreSQL is -better in most cases while our tests, which anyone can reproduce, -clearly shows otherwise. With this we don't want to say that PostgreSQL -isn't good at many things (It is!) We would just like to see a fair test -where they are very good so that we could get some friendly competition -going! - -For more information about our benchmarks suite see @xref{MySQL -Benchmarks}. - -We are working on an even better benchmark suite, including much better -documentation of what the individual tests really do and how to add more -tests to the suite. @cindex internals @cindex threads -@node MySQL internals, Environment variables, Comparisons, Top +@node MySQL internals, Environment variables, Clients, Top @chapter MySQL Internals This chapter describes a lot of things that you need to know when @@ -45565,7 +46375,7 @@ Development on the embedded @code{mysqld} version. @code{--skip-show-variables} @end table -@node News, Bugs, Credits, Top +@node News, Porting, Credits, Top @appendix MySQL change history This appendix lists the changes from version to version in the @strong{MySQL} @@ -50803,739 +51613,10 @@ fields, the @code{BLOB} was garbage on output. Fixed @code{DISTINCT} with calculated columns. @end itemize -@cindex bugs, known -@cindex errors, known -@cindex design, issues -@cindex known errors -@node Bugs, TODO, News, Top -@appendix Known errors and design deficiencies in MySQL - -The following problems are known and have a very high priority to get -fixed: - -@itemize @bullet -@item -@code{ANALYZE TABLE} on a BDB table may in some case make the table -unusable until one has restarted @code{mysqld}. When this happens you will -see errors like the following in the @strong{MySQL} error file: - -@example -001207 22:07:56 bdb: log_flush: LSN past current end-of-log -@end example - -@item -Don't execute @code{ALTER TABLE} on a @code{BDB} table on which you are -running not completed multi-statement transactions. (The transaction -will probably be ignored). - -@item -@code{ANALYZE TABLE}, @code{OPTIMIZE TABLE} and @code{REPAIR TABLE} may -cause problems on tables for which you are using @code{INSERT DELAYED}. - -@item -Doing a @code{LOCK TABLE ..} and @code{FLUSH TABLES ..} doesn't -guarantee that there isn't a half-finished transaction in progress on the -table. - -@item -BDB tables are a bit slow to open. If you have many BDB tables in a -database, it will take a long time to use the @code{mysql} client on the -database if you are not using the @code{-A} option or if you are using -@code{rehash}. This is especially notable when you have a big table -cache. - -@item -Th current replication protocol cannot deal with @code{LOAD DATA INFILE} -and line terminator characters of more than 1 character. -@end itemize - -The following problems are known and will be fixed in due time: - -@itemize @bullet -@item -For the moment @code{MATCH} only works with @code{SELECT} statements. -@item -When using @code{SET CHARACTER SET}, one can't use translated -characters in database, table and column names. -@item -@code{DELETE FROM merge_table} used without a @code{WHERE} -will only clear the mapping for the table, not delete everything in the -mapped tables -@item -You cannot build in another directory when using -MIT-pthreads. Because this requires changes to MIT-pthreads, we are not -likely to fix this. -@item -@code{BLOB} values can't ``reliably'' be used in @code{GROUP BY} or -@code{ORDER BY} or @code{DISTINCT}. Only the first @code{max_sort_length} -bytes (default 1024) are used when comparing @code{BLOB}bs in these cases. -This can be changed with the @code{-O max_sort_length} option to -@code{mysqld}. A workaround for most cases is to use a substring: -@code{SELECT DISTINCT LEFT(blob,2048) FROM tbl_name}. -@item -Calculation is done with @code{BIGINT} or @code{DOUBLE} (both are -normally 64 bits long). It depends on the function which precision one -gets. The general rule is that bit functions are done with @code{BIGINT} -precision, @code{IF}, and @code{ELT()} with @code{BIGINT} or @code{DOUBLE} -precision and the rest with @code{DOUBLE} precision. One should try to -avoid using bigger unsigned long long values than 63 bits -(9223372036854775807) for anything else than bit fields! -@item -All string columns, except @code{BLOB} and @code{TEXT} columns, automatically -have all trailing spaces removed when retrieved. For @code{CHAR} types this -is okay, and may be regarded as a feature according to ANSI SQL92. The bug is -that in @strong{MySQL}, @code{VARCHAR} columns are treated the same way. -@item -You can only have up to 255 @code{ENUM} and @code{SET} columns in one table. -@item -@code{safe_mysqld} re-directs all messages from @code{mysqld} to the -@code{mysqld} log. One problem with this is that if you execute -@code{mysqladmin refresh} to close and reopen the log, -@code{stdout} and @code{stderr} are still redirected to the old log. -If you use @code{--log} extensively, you should edit @code{safe_mysqld} to -log to @file{'hostname'.err} instead of @file{'hostname'.log} so you can -easily reclaim the space for the old log by deleting the old one and -executing @code{mysqladmin refresh}. -@item -In the @code{UPDATE} statement, columns are updated from left to right. -If you refer to an updated column, you will get the updated value instead of the -original value. For example: -@example -mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1; -@end example -will update @code{KEY} with @code{2} instead of with @code{1}. -@item -You can't use temporary tables more than once in the same query. -For example, the following doesn't work. - -@example -select * from temporary_table, temporary_table as t2; -@end example - -@item -@code{RENAME} doesn't work with @code{TEMPORARY} tables. - -@item -The optimizer may handle @code{DISTINCT} differently if you are using -'hidden' columns in a join or not. In a join, hidden columns are -counted as part of the result (even if they are not shown) while in -normal queries hidden columns doesn't participate in the @code{DISTINCT} -comparison. We will probably change this in the future to never compare -the hidden columns when executing @code{DISTINCT} - -An example of this is: - -@example -SELECT DISTINCT mp3id FROM band_downloads WHERE userid = 9 ORDER BY id -DESC; - -and - -SELECT DISTINCT band_downloads.mp3id, FROM band_downloads,band_mp3 -WHERE band_downloads.userid = 9 AND band_mp3.id = band_downloads.mp3id -ORDER BY band_downloads.id DESC; -@end example - -In the second case you may in @strong{MySQL} 3.23.x get two identical rows -in the result set (because the hidden 'id' column may differ). - -Note that the this only happens for queries where you don't have the -ORDER BY columns in the result, something that is you are not allowed -to do in ANSI SQL. - -@item -Because @strong{MySQL} allows you to work with table types that doesn't -support transactions (and thus can't @code{rollback} data) some things -behaves a little different in @strong{MySQL} than in other SQL servers: -(This is just to ensure that @strong{MySQL} never need to do a rollback -for a SQL command). This may be a little awkward at times as column -Because @strong{MySQL} allows you to work with table types that don't -support transactions, and thus can't @code{rollback} data, some things -behave a little differently in @strong{MySQL} than in other SQL servers. -This is just to ensure that @strong{MySQL} never need to do a rollback -for a SQL command. This may be a little awkward at times as column -values must be checked in the application, but this will actually give -you a nice speed increase as it allows @strong{MySQL} to do some -optimizations that otherwise would be very hard to do. - -If you set a column to an incorrect value, @strong{MySQL} will, instead of -doing a rollback, store the @code{best possible value} in the column: - -@itemize @bullet -@item -If you try to store a value outside the range in a numerical column, -@strong{MySQL} will instead store the smallest or biggest possible value in -the column. -@item -If you try to store a string that doesn't start with a number into a -numerical column, @strong{MySQL} will store 0 into it. -@item -If you try to store @code{NULL} into a column that doesn't take -@code{NULL} values, @strong{MySQL} will store 0 or @code{''} (empty -string) in it instead. (This behavior can, however, be changed with the --DDONT_USE_DEFAULT_FIELDS compile option). -@item -@strong{MySQL} allows you to store some wrong date values into -@code{DATE} and @code{DATETIME} columns. (Like 2000-02-31 or 2000-02-00). -If the date is totally wrong, @strong{MySQL} will store the special -0000-00-00 date value in the column. -@item -If you set an @code{enum} to an unsupported value, it will be set to -the error value 'empty string', with numeric value 0. -@end itemize - -@item -If you execute a @code{PROCEDURE} on a query that returns an empty set, -in some cases the @code{PROCEDURE} will not transform the columns. -@item -Creation of a table of type @code{MERGE} doesn't check if the underlying -tables are of compatible types. -@item -@strong{MySQL} can't yet handle @code{NaN}, @code{-Inf} and @code{Inf} -values in double. Using these will cause problems when trying to export -and import data. We should as an intermediate solution change @code{NaN} to -@code{NULL} (if possible) and @code{-Inf} and @code{Inf} to the -Minimum respective maximum possible @code{double} value. -@item -@code{LIMIT} on negative numbers are treated as big positive numbers. -@end itemize - -The following are known bugs in earlier versions of @strong{MySQL}: - -@itemize @bullet -@item -You can get a hung thread if you do a @code{DROP TABLE} on a table that is -one among many tables that is locked with @code{LOCK TABLES}. - -@item -In the following case you can get a core dump: -@enumerate -@item -Delayed insert handler has pending inserts to a table. -@item -@code{LOCK table} with @code{WRITE} -@item -@code{FLUSH TABLES} -@end enumerate - -@item -Before @strong{MySQL} Version 3.23.2 an @code{UPDATE} that updated a key with -a @code{WHERE} on the same key may have failed because the key was used to -search for records and the same row may have been found multiple times: - -@example -UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100; -@end example - -A workaround is to use: - -@example -mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100; -@end example - -This will work because @strong{MySQL} will not use index on expressions in -the @code{WHERE} clause. -@item -Before @strong{MySQL} Version 3.23, all numeric types where treated as fixed-point -fields. That means you had to specify how many decimals a floating-point -field shall have. All results were returned with the correct number of -decimals. -@end itemize - -For platform-specific bugs, see the sections about compiling and porting. - -@cindex ToDo list for MySQL -@node TODO, Porting, Bugs, Top -@appendix MySQL and the future (The TODO) - -@menu -* TODO MySQL 4.0:: Things that should be in Version 4.0 -* TODO future:: Things that must be done in the near future -* TODO sometime:: Things that have to be done sometime -* TODO unplanned:: Some things we don't have any plans to do -@end menu - -This appendix lists the features that we plan to implement in @strong{MySQL}. - -Everything in this list is approximately in the order it will be done. If you -want to affect the priority order, please register a license or support us and -tell us what you want to have done more quickly. @xref{Licensing and Support}. - -The plan is that we in the future will support the full ANSI SQL99 -standard, but with a lot of useful extensions. The challenge is to do -this without sacrifying the speed or compromise the code. - -@node TODO MySQL 4.0, TODO future, TODO, TODO -@appendixsec Things that should be in 4.0 - -We plan to make @strong{MySQL} Version 4.0 a ``quick'' release where we only -add some new stuff to enable others to help us with developing new features -into Version 4.1. The @strong{MySQL} 4.0 version should only take us about -a month to make after which we want to stabilize it and start working on -Version 4.1. Version 4.0 should have the following new features: - -The news section for 4.0 includes a list of the features we have already -implemented in the 4.0 tree. @xref{News-4.0.x}. - -@itemize @bullet -@item -New table definition file format (@code{.frm} files) This will enable us -to not run out of bits when adding more table options. One will still -be able to use the old @code{.frm} file format with 4.0. All newly created -tables will, however, use the new format. - -The new file format will enable us to add new column types, more options -for keys and @code{FOREIGN KEY} support. -@item -@code{mysqld} as a library. This will have the same interface as the -standard @strong{MySQL} client (with an extra function to just set up -startup parameters) but will be faster (no TCP/IP or socket overhead), -smaller and much easier to use for embedded products. - -One will be able to define at link time if one wants to use the -client/server model or a stand-alone application just by defining which -library to link with. -The @code{mysqld} will support all standard @strong{MySQL} features and -one can use it in a threaded client to run different queries in each -thread. -@item -Replication should work with @code{RAND()} and user variables @code{@@var}. -@item -Online backup with very low performance penalty. The online backup will -make it easy to add a new replication slave without taking down the -master. -@item -@code{DELETE FROM table_name} will return the number of deleted rows. For -fast execution one should use @code{TRUNCATE table_name}. -@item -Allow @code{DELETE} on @code{MyISAM} tables to use the record cache. -To do this, we need to update the threads record cache when we update -the @code{.MYD} file. -@item -Better replication. -@item -More functions for full-text search. -@xref{Fulltext Features to Appear in MySQL 4.0}. -@item -Character set casts and syntax for handling multiple character sets. -@item -Allow users to change startup options without taking down the server. -@item -Help for all commands from the client. -@item -Secure connections (with SSL). -@item -Extend the optimizer to be able to optimize some @code{ORDER BY key_name DESC} -queries. -@item -@code{SHOW COLUMNS FROM table_name} (used by @code{mysql} client to allow -expansions of column names) should not open the table, but only the -definition file. This will require less memory and be much faster. -@item -New key cache -@item -When using @code{SET CHARACTER SET} we should translate the whole query -at once and not only strings. This will enable users to use the translated -characters in database, table and column names. -@item -Add a portable interface over @code{gethostbyaddr_r()} so that we can change -@code{ip_to_hostname()} to not block other threads while doing DNS lookups. -@item -Add @code{record_in_range()} method to @code{MERGE} tables to be -able to choose the right index when there is many to choose from. We should -also extend the info interface to get the key distribution for each index, -of @code{analyze} is run on all sub tables. -@item -@code{SET SQL_DEFAULT_TABLE_TYPE=[MyISAM | INNODB | BDB | HEAP]}. -@end itemize - -@node TODO future, TODO sometime, TODO MySQL 4.0, TODO -@appendixsec Things that must be done in the real near future - -@itemize @bullet -@item -Fail safe replication. -@item -Subqueries. -@code{select id from t where grp in (select grp from g where u > 100)} -@item -Derived tables. -@example -select a.col1, b.col2 from (select max(col1) as col1 from root_table ) a, -other_table b where a.col1=b.col1 -@end example - -This could be done by automatically creating temporary tables for the -derived tables for the duration of the query. -@item -Add @code{PREPARE} of statements and sending of parameters to @code{mysqld}. -@item -Extend the server/client protocol to support warnings. -@item -Add options to the server/protocol protocol to get progress notes -for long running commands. -@item -Add database and real table name (in case of alias) to the MYSQL_FIELD -structure. -@item -Don't allow more than a defined number of threads to run MyISAM recover -at the same time. -@item -Change @code{INSERT ... SELECT} to optionally use concurrent inserts. -@item -Implement @code{RENAME DATABASE}. To make this safe for all table handlers, -it should work as follows: -@itemize @bullet -@item -Create the new database. -@item -For every table do a rename of the table to another database, as -we do with the @code{RENAME} command. -@item -Drop the old database. -@end itemize -@item -Return the original field types() when doing @code{SELECT MIN(column) -... GROUP BY}. -@item -Multiple result sets. -@item -Change the protocol to allow binary transfer of values. To do this -efficiently, we need to add an API to allow binding of variables. -@item -Make it possible to specify @code{long_query_time} with a granularity -in microseconds. -@item -Add a configurable prompt to the @code{mysql} command line client, with -options like database in use, time and date... -@item -Add range checking to @code{MERGE} tables. -@item -Link the @code{myisampack} code into the server. -@item -Port of @strong{MySQL} to BeOS. -@item -Port of the @strong{MySQL} clients to LynxOS. -@item -Add a temporary key buffer cache during @code{INSERT/DELETE/UPDATE} so that we -can gracefully recover if the index file gets full. -@item -If you perform an @code{ALTER TABLE} on a table that is symlinked to another -disk, create temporary tables on this disk. -@item -Implement a @code{DATE/DATETIME} type that handles time zone information -properly, so that dealing with dates in different time zones is easier. -@item -FreeBSD and MIT-pthreads; Do sleeping threads take CPU? -@item -Check if locked threads take any CPU. -@item -Fix configure so that one can compile all libraries (like @code{MyISAM}) -without threads. -@item -Add an option to periodically flush key pages for tables with delayed keys -if they haven't been used in a while. -@item -Allow join on key parts (optimization issue). -@item -@code{INSERT SQL_CONCURRENT} and @code{mysqld --concurrent-insert} to do -a concurrent insert at the end of the file if the file is read-locked. -@item -Remember @code{FOREIGN} key definitions in the @file{.frm} file. -@item -Cascading @code{DELETE} -@item -Server side cursors. -@item -Check if @code{lockd} works with modern Linux kernels; If not, we have -to fix @code{lockd}! To test this, start @code{mysqld} with -@code{--enable-locking} and run the different fork* test suits. They shouldn't -give any errors if @code{lockd} works. -@item -Allow SQL variables in @code{LIMIT}, like in @code{LIMIT @@a,@@b}. -@item -Allow update of variables in @code{UPDATE} statements. For example: -@code{UPDATE TABLE foo SET @@a=a+b,a=@@a, b=@@a+c} -@item -Change when user variables are updated so that one can use them with -@code{GROUP BY}, as in the following example: -@code{SELECT id, @@a:=count(*), sum(sum_col)/@@a FROM table_name GROUP BY id}. -@item -Don't add automatic @code{DEFAULT} values to columns. Give an error when using -an @code{INSERT} that doesn't contain a column that doesn't have a -@code{DEFAULT}. -@item -Caching of queries and results. This should be done as a separated -module that examines each query and if this is query is in the cache -the cached result should be returned. When one updates a table one -should remove as few queries as possible from the cache. -This should give a big speed bost on machines with much RAM where -queries are often repeated (like WWW applications). -One idea would be to only cache queries of type: -@code{SELECT CACHED ...} -@item -Fix @file{libmysql.c} to allow two @code{mysql_query()} commands in a row -without reading results or give a nice error message when one does this. -@item -Optimize @code{BIT} type to take 1 bit (now @code{BIT} takes 1 char). -@item -Check why MIT-pthreads @code{ctime()} doesn't work on some FreeBSD systems. -@item -Add an @code{IMAGE} option to @code{LOAD DATA INFILE} to not update -@code{TIMESTAMP} and @code{AUTO_INCREMENT} fields. -@item -Added @code{LOAD DATE INFILE.. UPDATE} syntax. -@itemize @bullet -@item -For tables with primary keys, if the data contains the primary key, -entries matching that primary key are updated from the remainder of the -columns. However, columns MISSING from the incoming data feed are not -touched. -@item -For tables tables with primary keys that are missing some part of the key -in the incoming data stream, or that have no primary key, the feed is -treated as a @code{LOAD DATA INFILE ... REPLACE INTO} is now. -@end itemize -@item -Make @code{LOAD DATA INFILE} understand syntax like: -@example -LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name -TEXT_FIELDS (text_field1, text_field2, text_field3) -SET table_field1=concatenate(text_field1, text_field2), table_field3=23 -IGNORE text_field3 - -This can be used to skip over extra columns in the text file, or update columns -based on expressions of the read data... -@end example -@item -@code{LOAD DATA INFILE 'file_name' INTO TABLE 'table_name' ERRORS TO err_table_name} -This would cause any errors and warnings to be logged into the err_table_name -table. That table would have a structure like: - -@example -line_number - line number in data file -error_message - the error/warning message -and maybe -data_line - the line from the data file -@end example -@item -Add true @code{VARCHAR} support (There is already support for this in MyISAM). -@item -Automatic output from @code{mysql} to netscape. -@item -@code{LOCK DATABASES}. (with various options) -@item -Change sort to allocate memory in ``hunks'' to get better memory utilization. -@item -@code{DECIMAL} and @code{NUMERIC} types can't read exponential numbers; -@code{Field_decimal::store(const char *from,uint len)} must be recoded -to fix this. -@item -Fix @code{mysql.cc} to do fewer @code{malloc()} calls when hashing field -names. -@item -Functions: -ADD_TO_SET(value,set) and REMOVE_FROM_SET(value,set) -@item -Add use of @code{t1 JOIN t2 ON ...} and @code{t1 JOIN t2 USING ...} -Currently, you can only use this syntax with @code{LEFT JOIN}. -@item -Add full support for @code{unsigned long long} type. -@item -Many more variables for @code{show status}. Counts for: -@code{INSERT}/@code{DELETE}/@code{UPDATE} statements. Records reads and -updated. Selects on 1 table and selects with joins. Mean number of -tables in select. Number of @code{ORDER BY} and @code{GROUP BY} queries. -@item -If you abort @code{mysql} in the middle of a query, you should open -another connection and kill the old running query. -Alternatively, an attempt should be made to detect this in the server. -@item -Add a handler interface for table information so you can use it as a system -table. This would be a bit slow if you requested information about all tables, -but very flexible. @code{SHOW INFO FROM tbl_name} for basic table information -should be implemented. -@item -Add support for UNICODE. -@item -@code{NATURAL JOIN} and @code{UNION JOIN} -@item -Allow @code{select a from crash_me left join crash_me2 using (a)}; In this -case a is assumed to come from the crash_me table. -@item -Fix that @code{ON} and @code{USING} works with the @code{JOIN} and -@code{INNER JOIN} join types. -@item -Oracle like @code{CONNECT BY PRIOR ...} to search hierarchy structures. -@item -@code{RENAME DATABASE} -@item -@code{mysqladmin copy database new-database}. -- Requires COPY command to be -added to @code{mysqld} -@item -Processlist should show number of queries/thread. -@item -@code{SHOW HOSTS} for printing information about the hostname cache. -@item -@code{DELETE} and @code{REPLACE} options to the @code{UPDATE} statement -(this will delete rows when one gets a duplicate key error while updating). -@item -Change the format of @code{DATETIME} to store fractions of seconds. -@item -Add all missing ANSI92 and ODBC 3.0 types. -@item -Change table names from empty strings to @code{NULL} for calculated columns. -@item -Don't use 'Item_copy_string' on numerical values to avoid -number->string->number conversion in case of: -@code{SELECT COUNT(*)*(id+0) FROM table_name GROUP BY id} -@item -Make it possible to use the new GNU regexp library instead of the current -one (The GNU library should be much faster than the old one). -@item -Change that @code{ALTER TABLE} doesn't abort clients that executes -@code{INSERT DELAYED}. -@item -Fix that when columns referenced in an @code{UPDATE} clause contains the old -values before the update started. -@item -@code{myisamchk}, @code{REPAIR} and @code{OPTIMIZE TABLE} should be able -to handle cases where the data and/or index files are symbolic links. -@item -Add simulation of @code{pread()}/@code{pwrite()} on Windows to enable -concurrent inserts. -@item -A logfile analyzer that could parsed out information about which tables -are hit most often, how often multi-table joins are executed, etc. It -should help users identify areas or table design that could be optimized -to execute much more efficient queries. -@item -Add @code{SUM(DISTINCT)} -@item -Add @code{ANY()},@code{EVERY()} and @code{SOME()} group functions. In -ANSI SQL these only works on boolean columns, but we can extend these to -work on any columns/expressions by applying: value == 0 -> FALSE and -value <> 0 -> TRUE. -@item -Fix that the type for @code{MAX(column)} is the same as the column type. -@example -create table t1 (a DATE); -insert into t1 values (now()); -create table t2 select max(a) from t1; -show columns from t2; -@end example -@item -Come up with a nice syntax for a statement that will @code{UPDATE} the row -if it exists and @code{INSERT} a new row if the row didn't exist. -(Like @code{REPLACE} works with @code{INSERT} / @code{DELETE}) -@end itemize - -@node TODO sometime, TODO unplanned, TODO future, TODO -@appendixsec Things that have to be done sometime - -@itemize @bullet -@item -Implement function: @code{get_changed_tables(timeout,table1,table2,...)} -@item -Atomic multi-table updates, eg @code{update items,month set -items.price=month.price where items.id=month.id;}; -@item -Change reading through tables to use memmap when possible. Now only -compressed tables use memmap. -@item -Add a new privilege @strong{'Show_priv'} for @code{SHOW} commands. -@item -Make the automatic timestamp code nicer. Add timestamps to the update -log with @code{SET TIMESTAMP=#;} -@item -Use read/write mutex in some places to get more speed. -@item -Full foreign key support. One probably wants to implement a procedural -language first. -@item -Simple views (first on one table, later on any expression). -@item -Automatically close some tables if a table, temporary table or temporary files -gets error 23 (not enough open files). -@item -When one finds a field=#, change all occurrences of field to #. Now this -is only done for some simple cases. -@item -Change all const expressions with calculated expressions if possible. -@item -Optimize key = expression. At the moment only key = field or key = -constant are optimized. -@item -Join some of the copy functions for nicer code. -@item -Change @file{sql_yacc.yy} to an inline parser to reduce its size and get -better error messages (5 days). -@item -Change the parser to use only one rule per different number of arguments -in function. -@item -Use of full calculation names in the order part. (For ACCESS97) -@item -@code{UNION}, @code{MINUS}, @code{INTERSECT} and @code{FULL OUTER JOIN}. -(Currently only @code{LEFT OUTER JOIN} is supported) -@item -Allow @code{UNIQUE} on fields that can be @code{NULL}. -@item -@code{SQL_OPTION MAX_SELECT_TIME=#} to put a time limit on a query. -@item -Make the update log to a database. -@item -Negative @code{LIMIT} to retrieve data from the end. -@item -Alarm around client connect/read/write functions. -@item -Please note the changes to @code{safe_mysqld}: according to FSSTND (which -Debian tries to follow) PID files should go into @file{/var/run/<progname>.pid} -and log files into @file{/var/log}. It would be nice if you could put the -"DATADIR" in the first declaration of "pidfile" and "log", so the -placement of these files can be changed with a single statement. -@item -Allow a client to request logging. -@item -Add use of @code{zlib()} for @code{gzip}-ed files to @code{LOAD DATA INFILE}. -@item -Fix sorting and grouping of @code{BLOB} columns (partly solved now). -@item -Stored procedures. This is currently not regarded to be very -important as stored procedures are not very standardized yet. -Another problem is that true stored procedures make it much harder for -the optimizer and in many cases the result is slower than before -We will, on the other hand, add a simple (atomic) update language that -can be used to write loops and such in the @strong{MySQL} server. -@item -Change to use semaphores when counting threads. One should first implement -a semaphore library to MIT-pthreads. -@item -Don't assign a new @code{AUTO_INCREMENT} value when one sets a column to 0. -Use @code{NULL} instead. -@item -Add full support for @code{JOIN} with parentheses. -@item -As an alternative for one thread / connection manage a pool of threads -to handle the queries. -@item -Allow one to get more than one lock with @code{GET_LOCK}. When doing this, -one must also handle the possible deadlocks this change will introduce. -@end itemize - -Time is given according to amount of work, not real time. - -@node TODO unplanned, , TODO sometime, TODO -@appendixsec Some things we don't have any plans to do - -@itemize @bullet -@item -Nothing; In the long run we plan to be fully ANSI 92 / ANSI 99 compliant. -@end itemize @cindex porting, to other systems -@node Porting, Regexp, TODO, Top +@node Porting, Regexp, News, Top @appendix Comments on porting to other systems A working Posix thread library is needed for the server. On Solaris 2.5 |