diff options
36 files changed, 652 insertions, 197 deletions
diff --git a/BUILD/compile-pentium-debug-max b/BUILD/compile-pentium-debug-max index ccd6faa0448..4149267811d 100755 --- a/BUILD/compile-pentium-debug-max +++ b/BUILD/compile-pentium-debug-max @@ -8,6 +8,6 @@ c_warnings="$c_warnings $debug_extra_warnings" cxx_warnings="$cxx_warnings $debug_extra_warnings" extra_configs="$pentium_configs $debug_configs" -extra_configs="$extra_configs --with-berkeley-db --with-innodb --with-vio --with-openssl --with-embedded-server" +extra_configs="$extra_configs --with-berkeley-db --with-innodb --with-embedded-server" . "$path/FINISH.sh" diff --git a/Docs/manual.texi b/Docs/manual.texi index c88455289c8..b1815b4eac9 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -4368,6 +4368,7 @@ 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! +MySQL 4.0 has better @code{BIGINT} handling than 3.23. @item All string columns, except @code{BLOB} and @code{TEXT} columns, automatically @@ -5690,6 +5691,15 @@ implemented in the 4.0 tree. @xref{News-4.0.x}. @itemize @bullet @item +Allow users to change startup options without taking down the server. +@item +Fail safe replication. +@item +More functions for full-text search. +@xref{Fulltext Features to Appear in MySQL 4.0}. +@item +New key cache +@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 @@ -5698,19 +5708,6 @@ 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 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 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 @@ -5720,15 +5717,8 @@ 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). @@ -5737,8 +5727,6 @@ Secure connections (with SSL). 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. @@ -5760,11 +5748,12 @@ of @code{analyze} is run on all sub tables. @itemize @bullet @item -Fail safe replication. -@item Subqueries. @code{select id from t where grp in (select grp from g where u > 100)} @item +Atomic multi-table updates, eg @code{update items,month set +items.price=month.price where items.id=month.id;}; +@item Derived tables. @example select a.col1, b.col2 from (select max(col1) as col1 from root_table ) a, @@ -6046,9 +6035,6 @@ if it exists and @code{INSERT} a new row if the row didn't exist. @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 @@ -6144,8 +6130,6 @@ Nothing; In the long run we plan to be fully ANSI 92 / ANSI 99 compliant. @end itemize - - @node Installing, Tutorial, Introduction, Top @chapter MySQL Installation @@ -9349,7 +9333,7 @@ you have to use @code{mysqldump}. Old clients should work with a Version 4.0 server without any problems. The following lists tell what you have to watch out for when upgrading to -Version 4.0; +version 4.0; @itemize @bullet @item @@ -9366,6 +9350,15 @@ from a table and you don't care of how many rows where deleted. You will get an error if you have an active @code{LOCK TABLES} or transaction when trying to execute @code{TRUNCATE TABLE} or @code{DROP DATABASE}. +@item +You should use integers to store values in BIGINT columns (instead of using +strings as you did in MySQL 3.23). Using strings will still work, but using +integers is more efficient. +@item +Format of @code{SHOW OPEN TABLE} has changed. +@item +Multithreaded clients should use @code{mysql_thread_init()} and +@code{mysql_thread_end()}. @xref{Threaded clients}. @end itemize @node Upgrading-from-3.22, Upgrading-from-3.21, Upgrading-from-3.23, Upgrade @@ -20270,19 +20263,13 @@ operations. @item @code{table_cache} The number of open tables for all threads. Increasing this value increases the number of file descriptors that @code{mysqld} requires. -MySQL needs two file descriptors for each unique open table. -See below for comments on file descriptor limits. You can check if you -need to increase the table cache by checking the @code{Opened_tables} -variable. @xref{SHOW}. If this variable is big and you don't do -@code{FLUSH TABLES} a lot (which just forces all tables to be closed and -reopenend), then you should increase the value of this variable. - -Make sure that your operating system can handle the number of open file -descriptors implied by the @code{table_cache} setting. If @code{table_cache} -is set too high, MySQL may run out of file descriptors and refuse -connections, fail to perform queries, and be very unreliable. +You can check if you need to increase the table cache by checking the +@code{Opened_tables} variable. @xref{SHOW}. If this variable is big and +you don't do @code{FLUSH TABLES} a lot (which just forces all tables to +be closed and reopenend), then you should increase the value of this +variable. -For information about how the table cache works, see @ref{Table cache}. +For more information about the table cache, see @ref{Table cache}. @item @code{table_type} The default table type @@ -26430,6 +26417,16 @@ at least @code{200 * n}, where @code{n} is the maximum number of tables in a join. You also need to reserve some extra file descriptors for temporary tables and files. +Make sure that your operating system can handle the number of open file +descriptors implied by the @code{table_cache} setting. If +@code{table_cache} is set too high, MySQL may run out of file +descriptors and refuse connections, fail to perform queries, and be very +unreliable. You also have to take into account that the MyISAM table +handler needs two file descriptors for each unique open table. You can +in increase the number of file descriptors available for MySQL with +the @code{--open-files-limit=#} startup option. @xref{Not enough file +handles}. + The cache of open tables can grow to a maximum of @code{table_cache} (default 64; this can be changed with the @code{-O table_cache=#} option to @code{mysqld}). A table is never closed, except when the @@ -26461,6 +26458,12 @@ use of the table takes only one file descriptor. The extra descriptor for the first open is used for the index file; this descriptor is shared among all threads. +If you are opening a table with the @code{HANDLER table_name OPEN} +statement, a dedicated table object is allocated for the thread. +This table object is not shared by other threads an will not be closed +until the thread calls @code{HANDLER table_name CLOSE} or the thread dies. +@xref{HANDLER}. + You can check if your table cache is too small by checking the mysqld variable @code{opened_tables}. If this is quite big, even if you haven't done a lot of @code{FLUSH TABLES}, you should increase your table @@ -28116,14 +28119,23 @@ values, so you shouldn't use unsigned big integers larger than @code{9223372036854775807} (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting the @code{BIGINT} to a @code{DOUBLE}. + +MySQL 4.0 can handle @code{BIGINT} in the following cases: +@itemize @bullet +@item +Use integers to store big unsigned values in a @code{BIGINT} column. +@item +In @code{MIN(big_int_column)} and @code{MAX(big_int_column)}. +@item +When using operators (@code{+}, @code{-}, @code{*} etc) where +both operands are integers. +@end itemize + @item You can always store an exact integer value in a @code{BIGINT} column by storing it as a string, as there is in this case there will be no intermediate double representation. @item -In MySQL 4.0 you can use integers to store big unsigned values in a -@code{BIGINT} string. -@item @samp{-}, @samp{+}, and @samp{*} will use @code{BIGINT} arithmetic when both arguments are @code{INTEGER} values! This means that if you multiply two big integers (or results from functions that return @@ -33818,6 +33830,7 @@ or DELAY_KEY_WRITE = @{0 | 1@} or ROW_FORMAT= @{ default | dynamic | fixed | compressed @} or RAID_TYPE= @{1 | STRIPED | RAID0 @} RAID_CHUNKS=# RAID_CHUNKSIZE=# or UNION = (table_name,[table_name...]) +or INSERT_METHOD= @{NO | FIRST | LAST @} or DATA DIRECTORY="directory" or INDEX DIRECTORY="directory" @@ -34160,13 +34173,13 @@ original tables, MySQL will not allow concurrent inserts during @code{CREATE TABLE .... SELECT}. @item The @code{RAID_TYPE} option will help you to break the 2G/4G limit for -the MyISAM data file (not the index file) on -operating systems that don't support big files. You can get also more speed -from the I/O bottleneck by putting @code{RAID} directories on different -physical disks. @code{RAID_TYPE} will work on any OS, as long as you have -configured MySQL with @code{--with-raid}. For now the only allowed -@code{RAID_TYPE} is @code{STRIPED} (@code{1} and @code{RAID0} are aliases -for this). +the MyISAM data file (not the index file) on operating systems that +don't support big files. +You can get more speed from the I/O bottleneck by putting +@code{RAID} directories on different physical disks. @code{RAID_TYPE} +will work on any OS, as long as you have configured MySQL with +@code{--with-raid}. For now the only allowed @code{RAID_TYPE} is +@code{STRIPED} (@code{1} and @code{RAID0} are aliases for this). If you specify @code{RAID_TYPE=STRIPED} for a @code{MyISAM} table, @code{MyISAM} will create @code{RAID_CHUNKS} subdirectories named 00, @@ -34182,6 +34195,12 @@ tables as one. This only works with MERGE tables. @xref{MERGE}. For the moment you need to have @code{SELECT}, @code{UPDATE}, and @code{DELETE} privileges on the tables you map to a @code{MERGE} table. All mapped tables must be in the same database as the @code{MERGE} table. + +@item +If you want to insert data in a @code{MERGE} table, you have to specify with +@code{INSERT_METHOD} into with table the row should be inserted. +@xref{MERGE}. + @item In the created table the @code{PRIMARY} key will be placed first, followed by all @code{UNIQUE} keys and then the normal keys. This helps the @@ -35005,6 +35024,8 @@ interface, bypassing SQL optimizer. Thus, it is faster then SELECT. The first form of @code{HANDLER} statement opens a table, making in accessible via the following @code{HANDLER ... READ} routines. +This table object is not shared by other threads an will not be closed +until the thread calls @code{HANDLER table_name CLOSE} or the thread dies. The second form fetches one (or, specified by @code{LIMIT} clause) row where the index specified complies to the condition and @code{WHERE} @@ -35023,8 +35044,8 @@ in data file) matching @code{WHERE} condition. It is faster than The last form closes the table, opened with @code{HANDLER ... OPEN}. @code{HANDLER} is somewhat low-level statement, for example it does not -provide consistency. That is @code{HANDLER ... OPEN} does @strong{not} -takes a snapshot of the table, and does @strong{not} locks the table. The +provide consistency. That is @code{HANDLER ... OPEN} does @strong{NOT} +takes a snapshot of the table, and does @strong{NOT} locks the table. The above means, that after @code{HANDLER ... OPEN} table data can be modified (by this or other thread) and these modifications may appear only partially in @code{HANDLER ... NEXT} or @code{HANDLER ... PREV} scans. @@ -35885,7 +35906,7 @@ specification. Note that @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. (We plan to fix this in 4.0). +mapped tables. (We plan to fix this in 4.1). With identical tables we mean that all tables are created with identical column and key information. You can't put a MERGE over tables where the @@ -35945,11 +35966,12 @@ The disadvantages with @code{MERGE} tables are: @itemize @bullet @item -You can't use @code{INSERT} on @code{MERGE} tables, as MySQL -can't know in which of the tables we should insert the row. -@item You can only use identical @code{MyISAM} tables for a @code{MERGE} table. @item +@code{AUTO_INCREMENT} columns are not automaticly updated on @code{INSERT}. +@item +@code{REPLACE} doesn't work. +@item @code{MERGE} tables uses more file descriptors. If you are using a @strong{MERGE} that maps over 10 tables and 10 users are using this, you are using 10*10 + 10 file descriptors. (10 data files for 10 users @@ -35971,6 +35993,14 @@ mapped by a @code{MERGE} table that is 'open'. If you do this, the get unexpected results. @end itemize +When you create a @code{MERGE} table you have to specify with +@code{UNION(list-of-tables)} which tables you want to use as +one. Optionally you can specify with @code{INSERT_METHOD} if you want +insert for the @code{MERGE} table to happen in the first or last table +in the @code{UNION} list. If you don't specify @code{INSERT_METHOD} or +specify @code{NO}, then all @code{INSERT} commands on the @code{MERGE} +table will return an error. + The following example shows you how to use @code{MERGE} tables: @example @@ -35978,7 +36008,7 @@ CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2"); -CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2); +CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; @end example Note that we didn't create a @code{UNIQUE} or @code{PRIMARY KEY} in the @@ -38908,19 +38938,37 @@ Note that if you are using MySQL Version 3.22, you must to apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and above to go around this problem. @item -Set the ``Return matching rows'' MyODBC option field when connecting to -MySQL. -@item -You should have a primary key in the table. If not, new or updated rows -may show up as @code{#Deleted#}. +For all Access versions, you should enable the MyODBC option flag +@code{Return matching rows}. For Access 2.0, you should additionally enable +@code{Simulate ODBC 1.0}. @item You should have a timestamp in all tables you want to be able to update. For maximum portability @code{TIMESTAMP(14)} or simple @code{TIMESTAMP} is recommended instead of other @code{TIMESTAMP(X)} variations. @item -Only use double float fields. Access fails when comparing with single floats. -The symptom usually is that new or updated rows may show up as @code{#Deleted#} -or that you can't find or update rows. +You should have a primary key in the table. If not, new or updated rows +may show up as @code{#DELETED#}. +@item +Only use @code{DOUBLE} float fields. Access fails when comparing with +single floats. The symptom usually is that new or updated rows may show +up as @code{#DELETED#} or that you can't find or update rows. +@item +If you are linking a table through MyODBC, which has @code{BIGINT} as +one of the column, then the results will be displayed as @code{#DELETED}. The +work around solution is: +@itemize @bullet +@item +Have one more dummy column with @code{TIMESTAMP} as the data type, preferably +@code{TIMESTAMP(14)}. +@item +Check the @code{'Change BIGINT columns to INT'} in connection options dialog in +ODBC DSN Administrator +@item +Delete the table link from access and re-create it. +@end itemize + +It still displays the previous records as @code{#DELETED#}, but newly +added/updated records will be displayed properly. @item If you still get the error @code{Another user has changed your data} after adding a @code{TIMESTAMP} column, the following trick may help you: @@ -38931,6 +38979,10 @@ set the @code{DefaultValue} property for the @code{TIMESTAMP} column to @code{NOW()}. It may be a good idea to hide the @code{TIMESTAMP} column from view so your users are not confused. @item +In some cases, Access may generate illegal SQL queries that +MySQL can't understand. You can fix this by selecting +@code{"Query|SQLSpecific|Pass-Through"} from the Access menu. +@item Access on NT will report @code{BLOB} columns as @code{OLE OBJECTS}. If you want to have @code{MEMO} columns instead, you should change the column to @code{TEXT} with @code{ALTER TABLE}. @@ -38938,19 +38990,9 @@ column to @code{TEXT} with @code{ALTER TABLE}. Access can't always handle @code{DATE} columns properly. If you have a problem with these, change the columns to @code{DATETIME}. @item -In some cases, Access may generate illegal SQL queries that -MySQL can't understand. You can fix this by selecting -@code{"Query|SQLSpecific|Pass-Through"} from the Access menu. -@item -If you have in Access a column defined as BYTE, Access will try to export this -as @code{TINYINT} instead of @code{TINYINT UNSIGNED}. This will give you -problems if you have values > 127 in the column! -@item -If you are using Access 7.0, You should use the option flag @code{Return -matching rows}. -@item -If you are using Access 2.0, You should use the option flags @code{Return -matching rows} and @code{Simulate ODBC 1.0}. +If you have in Access a column defined as @code{BYTE}, Access will try +to export this as @code{TINYINT} instead of @code{TINYINT UNSIGNED}. +This will give you problems if you have values > 127 in the column! @end itemize @cindex ADO program @@ -42971,6 +43013,10 @@ functions}. For the UDF mechanism to work, functions must be written in C or C++, your operating system must support dynamic loading and you must have compiled @code{mysqld} dynamically (not statically). +Note that to make @code{AGGREGATE} work, you must have a +@code{mysql.func} table that contains the column @code{type}. If this +is not the case, you should run the script +@code{mysql_fix_privilege_tables} to get this fixed. @node Adding UDF, Adding native function, CREATE FUNCTION, Adding functions @@ -43135,6 +43181,10 @@ digits. For real functions, the default is 13 plus the number of decimals indicated by @code{initid->decimals}. (For numeric functions, the length includes any sign or decimal point characters.) +If you want to return a blob, you can set this to 65K or 16M; This +memory is not allocated but used to decide which column type to use if +there is a need to temporary store the data. + @item char *ptr A pointer that the function can use for its own purposes. For example, functions can use @code{initid->ptr} to communicate allocated memory @@ -43275,7 +43325,8 @@ terminal screen. The return value of the main function @code{xxx()} is the function value, for @code{long long} and @code{double} functions. A string functions should return a pointer to the result and store the length of the string in the -@code{length} arguments. @code{result} is a buffer at least 255 bytes long. +@code{length} arguments. + Set these to the contents and length of the return value. For example: @example @@ -43283,8 +43334,12 @@ memcpy(result, "result string", 13); *length = 13; @end example -If your string functions that needs to return a string longer than 255 -bytes, you must allocate the space for it with @code{malloc()} in your +The @code{result} buffer that is passed to the calc function is 255 byte +big. If your result fits in this, you don't have to worry about memory +allocation for results. + +If your string function needs to return a string longer than 255 bytes, +you must allocate the space for it with @code{malloc()} in your @code{xxx_init()} function or your @code{xxx()} function and free it in your @code{xxx_deinit()} function. You can store the allocated memory in the @code{ptr} slot in the @code{UDF_INIT} structure for reuse by @@ -43389,6 +43444,7 @@ mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so"; mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so"; mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so"; +mysql> CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so"; @end example Functions can be deleted using @code{DROP FUNCTION}: @@ -43399,6 +43455,7 @@ mysql> DROP FUNCTION myfunc_double; mysql> DROP FUNCTION myfunc_int; mysql> DROP FUNCTION lookup; mysql> DROP FUNCTION reverse_lookup; +mysql> DROP FUNCTION avgcost; @end example The @code{CREATE FUNCTION} and @code{DROP FUNCTION} statements update the @@ -47308,6 +47365,9 @@ Configure updates for Tru64, large file support and better TCP wrappers support. @item John Birrell Emulation of pthread_mutex() for OS/2. +@item Benjamin Pflugmann +Extended @code{MERGE} tables to handle @code{INSERTS}. Active member +on the MySQL mailing lists. @end table Other contributors, bugfinders, and testers: James H. Thompson, Maurizio @@ -47434,16 +47494,16 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}. @itemize @bullet @item -Added documentation for @code{libmysqld}, the embedded -MySQL server library. Also added example programs (a -@code{mysql} client and @code{mysqltest} test program) which use -@code{libmysqld}. +Added documentation for @code{libmysqld}, the embedded MySQL server +library. Also added example programs (a @code{mysql} client and +@code{mysqltest} test program) which use @code{libmysqld}. @item Removed @code{my_thread_init()} and @code{my_thread_end()} from mysql_com.h, and added @code{mysql_thread_init()} and @code{mysql_thread_end()} to mysql.h. @item -Fixed handling of big unsigned bigint constants. +Unsigned @code{BIGINT} constants now work. @code{MIN()} and @code{MAX()} +now handles signed and unsigned @code{BIGINT} numbers correctly. @item New character set @code{latin_de} which provides correct German sorting. @item @@ -47454,45 +47514,40 @@ the number of deleted rows. @code{DROP DATABASE} now executes a @code{DROP TABLE} on all tables in the database, which fixes a problem with InnoDB tables. @item -Changed @code{WEEK(#,0)} to match the calender in the USA. +Added support for @code{UNION}. @item -Cleaned up global lock handling for @code{FLUSH TABLES WITH READ LOCK} +A new @code{HANDLER} interface to @code{MyISAM} tables. @item -Fixed problem with @code{DATETIME = constant} in @code{WHERE} optimization. +Added support for @code{INSERT} on @code{MERGE} tables. Patch from +Benjamin Pflugmann. @item -Speed up all internal list handling. +Changed @code{WEEK(#,0)} to match the calender in the USA. @item -Added support for @code{UNION}. +@code{COUNT(DISTINCT)} is about 30% faster. @item -Allow ANSI SQL syntax @code{X'hexadecimal-number'} +Speed up all internal list handling. +@item +Creating full text indexes are now much faster. @item Tree-like cache to speed up bulk inserts and @code{myisam_bulk_insert_tree_size} variable. @item -Added @code{ALTER TABLE table_name DISABLE KEYS} and -@code{ALTER TABLE table_name ENABLE KEYS} commands. +Searching on packed (@code{CHAR}/@code{VARCHAR}) keys are now much faster. @item -@code{LOAD DATA FROM MASTER} "auto-magically" sets up a slave. +Optimized queries of type: +@code{SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #} @item -Renamed @code{safe_mysqld} to @code{mysqld_safe}. +@code{ORDER BY ... DESC} can now use keys. @item -Allow one to use @code{IN} instead of @code{FROM} in @code{SHOW} commands. +@code{LOAD DATA FROM MASTER} "auto-magically" sets up a slave. @item -@code{SHOW INDEXES} is now a synonym for @code{SHOW INDEX}. +Renamed @code{safe_mysqld} to @code{mysqld_safe}. @item Added support for symbolic links to @code{MyISAM} tables. Symlink handling is now enabled by default for Windows. @item @code{LOAD DATA FROM MASTER} "auto-magically" sets up a slave. @item -A new @code{HANDLER} interface to @code{MyISAM} tables. -@item -@code{COUNT(DISTINCT)} is about 30% faster. -@item -Creating full text indexes are now much faster. -@item -Searching on packed (@code{CHAR}/@code{VARCHAR}) keys are now much faster. -@item Added @code{SQL_CALC_FOUND_ROWS} and @code{FOUND_ROWS()}. This makes it possible to know how many rows a query would have returned without a @code{LIMIT} clause. @@ -47505,8 +47560,18 @@ Added @code{IDENTITY} as a synonym for @code{AUTO_INCREMENT} (like Sybase). @item Added @code{ORDER BY} syntax to @code{UPDATE} and @code{DELETE}. @item -Optimized queries of type: -@code{SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #} +@code{SHOW INDEXES} is now a synonym for @code{SHOW INDEX}. +@item +Added @code{ALTER TABLE table_name DISABLE KEYS} and +@code{ALTER TABLE table_name ENABLE KEYS} commands. +@item +Allow one to use @code{IN} instead of @code{FROM} in @code{SHOW} commands. +@item +Allow ANSI SQL syntax @code{X'hexadecimal-number'} +@item +Cleaned up global lock handling for @code{FLUSH TABLES WITH READ LOCK} +@item +Fixed problem with @code{DATETIME = constant} in @code{WHERE} optimization. @end itemize diff --git a/acinclude.m4 b/acinclude.m4 index d7e492856bb..8a7413b9b08 100644 --- a/acinclude.m4 +++ b/acinclude.m4 @@ -715,6 +715,8 @@ AC_MSG_CHECKING(for OpenSSL) [openssl="$withval"], [openssl=no]) + openssl_libs="" + openssl_includes="" if test "$openssl" = "yes" then if test -n "$vio_dir" @@ -722,14 +724,12 @@ AC_MSG_CHECKING(for OpenSSL) AC_MSG_RESULT(yes) openssl_libs="-L/usr/local/ssl/lib -lssl -lcrypto" openssl_includes="-I/usr/local/ssl/include" + AC_DEFINE(HAVE_OPENSSL) else - AC_MSG_ERROR([OpenSSL requires Virtual IO support (--with-vio)]) + AC_MSG_RESULT("disabled because --with-vio wasn't used") fi - AC_DEFINE(HAVE_OPENSSL) else AC_MSG_RESULT(no) - openssl_libs="" - openssl_includes="" fi NON_THREADED_CLIENT_LIBS="$NON_THREADED_CLIENT_LIBS $openssl_libs" AC_SUBST(openssl_libs) @@ -748,16 +748,15 @@ dnl Call MYSQL_CHECK_ORBIT even if mysqlfs == no, so that @orbit_*@ dnl get substituted. MYSQL_CHECK_ORBIT + fs_dirs="" if test "$mysqlfs" = "yes" then if test -n "$orbit_exec_prefix" then fs_dirs=fs else - AC_MSG_ERROR([mysqlfs requires ORBit, the CORBA ORB]) + AC_MSG_RESULT("disabled because ORBIT, the CORBA ORB, wasn't found"]) fi - else - fs_dirs= fi AC_SUBST([fs_dirs]) ]) diff --git a/configure.in b/configure.in index 14816515e97..df9c6bbec4d 100644 --- a/configure.in +++ b/configure.in @@ -1758,11 +1758,15 @@ AC_ARG_WITH(extra-tools, [with_tools=yes] ) +tools_dirs="" if test "$with_tools" = "yes" then - tools_dirs="tools" -else - tools_dirs="" + if test "$THREAD_SAFE_CLIENT" = "no" + then + echo "Warning: extra-tools disabled because --enable-thread-safe-client wasn't used" + else + tools_dirs="tools" + fi fi AC_SUBST(tools_dirs) diff --git a/include/mysql_com.h b/include/mysql_com.h index 63fbc05b0cb..47908c5f7be 100644 --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -104,6 +104,8 @@ enum enum_server_command {COM_SLEEP,COM_QUIT,COM_INIT_DB,COM_QUERY, struct st_vio; /* Only C */ typedef struct st_vio Vio; +#define MAX_BLOB_WIDTH 8192 // Default width for blob + typedef struct st_net { Vio* vio; my_socket fd; /* For Perl DBI/dbd */ diff --git a/myisammrg/myrg_create.c b/myisammrg/myrg_create.c index 5c6638b6ef2..d1ea018741d 100644 --- a/myisammrg/myrg_create.c +++ b/myisammrg/myrg_create.c @@ -54,7 +54,7 @@ int myrg_create(const char *name, const char **table_names, if (insert_method != MERGE_INSERT_DISABLED) { end=strxmov(buff,"#INSERT_METHOD=", - get_type(&merge_insert_method,insert_method),"\n",NullS); + get_type(&merge_insert_method,insert_method-1),"\n",NullS); if (my_write(file,buff,(uint) (end-buff),MYF(MY_WME | MY_NABP))) goto err; } diff --git a/myisammrg/myrg_static.c b/myisammrg/myrg_static.c index ad57ea847d5..d667c5a5475 100644 --- a/myisammrg/myrg_static.c +++ b/myisammrg/myrg_static.c @@ -26,5 +26,5 @@ LIST *myrg_open_list=0; static const char *merge_insert_methods[] = { "FIRST", "LAST", NullS }; -TYPELIB merge_insert_method= { array_elements(merge_insert_methods),"", +TYPELIB merge_insert_method= { array_elements(merge_insert_methods)-1,"", merge_insert_methods}; diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 46ce0fda2c1..a29a9af06da 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -13,3 +13,19 @@ a 18446744073709551615 a 18446744073709551614 +min(big) max(big) max(big)-1 +-1 9223372036854775807 9223372036854775806 +min(big) max(big) max(big)-1 +-1 9223372036854775807 9223372036854775806 +min(big) max(big) max(big)-1 +12345678901234567 18446744073709551615 18446744073709551614 +min(big) max(big) max(big)-1 +12345678901234567 18446744073709551615 18446744073709551614 +min(big) max(big) max(big)-1 +12345678901234567 18446744073709551615 18446744073709551614 +min(big) max(big) max(big)-1 +12345678901234567 18446744073709551615 18446744073709551614 +min(big) max(big) max(big)-1 +-1 9223372036854775807 9223372036854775806 +min(big) max(big) max(big)-1 +-1 9223372036854775807 9223372036854775806 diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 653e25af799..704b541e25e 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -81,6 +81,56 @@ a 412 412 411 +a b +1 Testing +1 Testing +2 table +2 table +4 Testing +4 Testing +5 table +5 table +6 t2 +6 t1 +7 Testing +7 Testing +8 table +8 table +9 t2 +9 t2 +a b +1 Testing +1 Testing +2 table +2 table +4 Testing +4 Testing +5 table +5 table +9 t2 +9 t2 +a b +1 Testing +1 Testing +2 table +2 table +3 t2 +3 t2 +4 Testing +4 Testing +5 table +5 table +a b +1 Testing +1 Testing +2 table +2 table +3 t2 +3 t2 +4 Testing +4 Testing +5 table +5 table Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', @@ -130,3 +180,155 @@ a a b 1 1 1 2 +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) NOT NULL default '0', + `b` int(11) NOT NULL default '0', + KEY `a` (`a`,`b`) +) TYPE=MyISAM +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) NOT NULL default '0', + `b` int(11) NOT NULL default '0', + KEY `a` (`a`,`b`) +) TYPE=MRG_MyISAM UNION=(t1,t2) +Table Create Table +t5 CREATE TABLE `t5` ( + `a` int(11) NOT NULL default '0', + `b` int(11) NOT NULL default '0', + KEY `a` (`a`,`b`) +) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2) +Table Create Table +t6 CREATE TABLE `t6` ( + `a` int(11) NOT NULL default '0', + `b` int(11) NOT NULL default '0', + KEY `a` (`a`,`b`) +) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1,t2) +a b +a b +1 1 +2 1 +1 2 +a b +2 2 +1 3 +2 3 +a b +1 4 +2 4 +a b +1 1 +1 2 +1 3 +1 4 +5 1 +5 2 +a b +2 1 +2 2 +2 3 +2 4 +6 1 +6 2 +a b +1 1 +1 2 +1 3 +1 4 +2 1 +2 2 +2 3 +2 4 +5 1 +5 2 +6 1 +6 2 +a b +3 1 +3 2 +3 3 +3 4 +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) NOT NULL default '0', + `b` int(11) NOT NULL default '0', + KEY `a` (`a`,`b`) +) TYPE=MRG_MyISAM UNION=(t1,t2,t3) +a b +1 1 +1 2 +1 3 +1 4 +2 1 +2 2 +2 3 +2 4 +3 1 +3 2 +3 3 +3 4 +5 1 +5 2 +6 1 +6 2 +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) NOT NULL default '0', + `b` int(11) NOT NULL default '0', + KEY `a` (`a`,`b`) +) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2,t3) +a b +1 1 +1 2 +1 3 +1 4 +4 1 +4 2 +5 1 +5 2 +a b +2 1 +2 2 +2 3 +2 4 +6 1 +6 2 +a b +3 1 +3 2 +3 3 +3 4 +a b +1 1 +1 2 +1 3 +1 4 +2 1 +2 2 +2 3 +2 4 +3 1 +3 2 +3 3 +3 4 +4 1 +4 2 +5 1 +5 2 +6 1 +6 2 +a b +1 1 +1 2 +1 3 +1 4 +2 1 +2 2 +2 3 +2 4 +4 1 +4 2 +5 1 +5 2 +6 1 +6 2 diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index abb8ef8cb09..96643cc8710 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -131,7 +131,7 @@ auto auto auto auto 16 16 Field Type Null Key Default Extra Privileges -auto bigint(17) PRI 0 select,insert,update,references +auto bigint(17) unsigned PRI 0 select,insert,update,references t1 bigint(1) 0 select,insert,update,references t2 char(1) select,insert,update,references t3 mediumtext select,insert,update,references diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 6470b6f6a30..52ae9ad35db 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -14,3 +14,18 @@ select * from t1 where a='18446744073709551615'; delete from t1 where a=18446744073709551615; select * from t1; drop table t1; + +create table t1 ( a int not null default 1, big bigint ); +insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615); +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +alter table t1 modify big bigint unsigned not null; +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +alter table t1 add key (big); +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +alter table t1 modify big bigint not null; +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +drop table t1; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 188f699cd64..5bd78769a05 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -2,7 +2,7 @@ # test of MERGE TABLES # -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; create table t1 (a int not null primary key auto_increment, message char(20)); create table t2 (a int not null primary key auto_increment, message char(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); @@ -31,6 +31,14 @@ select * from t3 where a > 10 and a < 20; explain select a from t3 order by a desc limit 10; select a from t3 order by a desc limit 10; select a from t3 order by a desc limit 300,10; +delete from t3 where a=3; +select * from t3 where a < 10; +delete from t3 where a >= 6 and a <= 8; +select * from t3 where a < 10; +update t3 set a=3 where a=9; +select * from t3 where a < 10; +update t3 set a=6 where a=7; +select * from t3 where a < 10; show create table t3; # The following should give errors @@ -114,3 +122,49 @@ insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6); flush tables; select * from t3 where a=1 order by b limit 2; drop table t3,t1,t2; + +# +# [phi] testing INSERT_METHOD stuff +# + +drop table if exists t6, t5, t4, t3, t2, t1; +# first testing of common stuff with new parameters +create table t1 (a int not null, b int not null, key(a,b)); +create table t2 (a int not null, b int not null, key(a,b)); +create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; +create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; +create table t5 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +create table t6 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; +show create table t3; +show create table t4; +show create table t5; +show create table t6; +insert into t1 values (1,1),(1,2),(1,3),(1,4); +insert into t2 values (2,1),(2,2),(2,3),(2,4); +select * from t3 order by b,a limit 3; +select * from t4 order by b,a limit 3; +select * from t5 order by b,a limit 3,3; +select * from t6 order by b,a limit 6,3; +# now testing inserts and where the data gets written +insert into t5 values (5,1),(5,2); +insert into t6 values (6,1),(6,2); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t4 order by a,b; +# preperation for next test +insert into t3 values (3,1),(3,2),(3,3),(3,4); +select * from t3 order by a,b; +# now testing whether options are kept by alter table +alter table t4 UNION=(t1,t2,t3); +show create table t4; +select * from t4 order by a,b; +# testing switching off insert method and inserts again +alter table t4 INSERT_METHOD=FIRST; +show create table t4; +insert into t4 values (4,1),(4,2); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t3 order by a,b; +select * from t4 order by a,b; +select * from t5 order by a,b; +drop table if exists t6, t5, t4, t3, t2, t1; diff --git a/mysys/typelib.c b/mysys/typelib.c index b18959442ae..f4b638e047e 100644 --- a/mysys/typelib.c +++ b/mysys/typelib.c @@ -84,7 +84,8 @@ int find_type(my_string x, TYPELIB *typelib, uint full_name) /* Get name of type nr 'nr' */ /* Warning first type is 1, 0 = empty field */ -void make_type(register my_string to, register uint nr, register TYPELIB *typelib) +void make_type(register my_string to, register uint nr, + register TYPELIB *typelib) { DBUG_ENTER("make_type"); if (!nr) diff --git a/sql/field.h b/sql/field.h index b9d8e1957c9..8f60b7e008b 100644 --- a/sql/field.h +++ b/sql/field.h @@ -415,10 +415,11 @@ public: unireg_check_arg, field_name_arg, table_arg, 0, zero_arg,unsigned_arg) {} - Field_longlong(uint32 len_arg,bool maybe_null_arg, const char *field_name_arg, - struct st_table *table_arg) + Field_longlong(uint32 len_arg,bool maybe_null_arg, + const char *field_name_arg, + struct st_table *table_arg, bool unsigned_arg) :Field_num((char*) 0, len_arg, maybe_null_arg ? (uchar*) "": 0,0, - NONE, field_name_arg, table_arg,0,0,0) + NONE, field_name_arg, table_arg,0,0,unsigned_arg) {} enum Item_result result_type () const { return INT_RESULT; } enum_field_types type() const { return FIELD_TYPE_LONGLONG;} diff --git a/sql/ha_berkeley.cc b/sql/ha_berkeley.cc index e9cd3f57122..b10939227ff 100644 --- a/sql/ha_berkeley.cc +++ b/sql/ha_berkeley.cc @@ -90,7 +90,7 @@ const char *berkeley_lock_names[] = { "DEFAULT", "OLDEST","RANDOM","YOUNGEST",0 }; u_int32_t berkeley_lock_types[]= { DB_LOCK_DEFAULT, DB_LOCK_OLDEST, DB_LOCK_RANDOM }; -TYPELIB berkeley_lock_typelib= {array_elements(berkeley_lock_names),"", +TYPELIB berkeley_lock_typelib= {array_elements(berkeley_lock_names)-1,"", berkeley_lock_names}; static void berkeley_print_error(const char *db_errpfx, char *buffer); diff --git a/sql/ha_gemini.cc b/sql/ha_gemini.cc index e8130c55fc7..a60841c3fe6 100644 --- a/sql/ha_gemini.cc +++ b/sql/ha_gemini.cc @@ -68,7 +68,7 @@ ulong gemini_recovery_options = GEMINI_RECOVERY_FULL; /* bits in gemini_recovery_options */ const char *gemini_recovery_names[] = { "FULL", "NONE", "FORCE" }; -TYPELIB gemini_recovery_typelib= {array_elements(gemini_recovery_names),"", +TYPELIB gemini_recovery_typelib= {array_elements(gemini_recovery_names)-1,"", gemini_recovery_names}; const int start_of_name = 2; /* Name passed as ./<db>/<table-name> diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc index 812e0b77077..36d06022d58 100644 --- a/sql/ha_myisam.cc +++ b/sql/ha_myisam.cc @@ -36,7 +36,7 @@ ulong myisam_recover_options= HA_RECOVER_NONE; /* bits in myisam_recover_options */ const char *myisam_recover_names[] = { "DEFAULT", "BACKUP", "FORCE", "QUICK", NullS}; -TYPELIB myisam_recover_typelib= {array_elements(myisam_recover_names),"", +TYPELIB myisam_recover_typelib= {array_elements(myisam_recover_names)-1,"", myisam_recover_names}; diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc index 92c1372a7a1..88ae8d22f73 100644 --- a/sql/ha_myisammrg.cc +++ b/sql/ha_myisammrg.cc @@ -187,6 +187,11 @@ void ha_myisammrg::info(uint flag) int ha_myisammrg::extra(enum ha_extra_function operation) { + /* As this is just a mapping, we don't have to force the underlying + tables to be closed */ + if (operation == HA_EXTRA_FORCE_REOPEN || + operation == HA_EXTRA_PREPARE_FOR_DELETE) + return 0; return myrg_extra(file,operation); } @@ -285,8 +290,7 @@ void ha_myisammrg::append_create_info(String *packet) if (file->merge_insert_method != MERGE_INSERT_DISABLED) { packet->append(" INSERT_METHOD=",15); - const char *tmp = get_type(&merge_insert_method,file->merge_insert_method); - packet->append(tmp); + packet->append(get_type(&merge_insert_method,file->merge_insert_method-1)); } packet->append(" UNION=(",8); MYRG_TABLE *table,*first; diff --git a/sql/item.cc b/sql/item.cc index dbb9c4ec38d..5eae3a9b542 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -37,7 +37,7 @@ void item_init(void) Item::Item() { marker=0; - binary=maybe_null=null_value=with_sum_func=0; + binary=maybe_null=null_value=with_sum_func=unsigned_flag=0; name=0; decimals=0; max_length=0; next=current_thd->free_list; // Put in free list @@ -116,6 +116,7 @@ void Item_field::set_field(Field *field_par) table_name=field_par->table_name; field_name=field_par->field_name; binary=field_par->binary(); + unsigned_flag=test(field_par->flags & UNSIGNED_FLAG); } const char *Item_ident::full_name() const @@ -326,6 +327,8 @@ void Item::init_make_field(Send_field *tmp_field, tmp_field->type=field_type; tmp_field->length=max_length; tmp_field->decimals=decimals; + if (unsigned_flag) + tmp_field->flags |= UNSIGNED_FLAG; } /* ARGSUSED */ @@ -345,6 +348,7 @@ void Item_uint::make_field(Send_field *tmp_field) { init_make_field(tmp_field,FIELD_TYPE_LONGLONG); tmp_field->flags|= UNSIGNED_FLAG; + unsigned_flag=1; } void Item_real::make_field(Send_field *tmp_field) diff --git a/sql/item.h b/sql/item.h index 98abdc834d9..9ab41af3398 100644 --- a/sql/item.h +++ b/sql/item.h @@ -43,6 +43,7 @@ public: my_bool maybe_null; /* If item may be null */ my_bool null_value; /* if item is null */ my_bool binary; + my_bool unsigned_flag; my_bool with_sum_func; diff --git a/sql/item_func.cc b/sql/item_func.cc index 3ef5ed5d7a3..a4d0e1a7ed1 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -184,8 +184,10 @@ String *Item_num_func::val_str(String *str) longlong nr=val_int(); if (null_value) return 0; /* purecov: inspected */ - else + else if (!unsigned_flag) str->set(nr); + else + str->set((ulonglong) nr); } else { @@ -213,18 +215,26 @@ String *Item_int_func::val_str(String *str) longlong nr=val_int(); if (null_value) return 0; - else + else if (!unsigned_flag) str->set(nr); + else + str->set((ulonglong) nr); return str; } -/* Change from REAL_RESULT (default) to INT_RESULT if both arguments are integers */ +/* + Change from REAL_RESULT (default) to INT_RESULT if both arguments are + integers +*/ void Item_num_op::find_num_type(void) { if (args[0]->result_type() == INT_RESULT && args[1]->result_type() == INT_RESULT) + { hybrid_type=INT_RESULT; + unsigned_flag=args[0]->unsigned_flag | args[1]->unsigned_flag; + } } String *Item_num_op::val_str(String *str) @@ -234,8 +244,10 @@ String *Item_num_op::val_str(String *str) longlong nr=val_int(); if (null_value) return 0; /* purecov: inspected */ - else + else if (!unsigned_flag) str->set(nr); + else + str->set((ulonglong) nr); } else { @@ -667,8 +679,10 @@ String *Item_func_min_max::val_str(String *str) longlong nr=val_int(); if (null_value) return 0; - else + else if (!unsigned_flag) str->set(nr); + else + str->set((ulonglong) nr); return str; } case REAL_RESULT: @@ -1306,8 +1320,10 @@ String *Item_func_udf_int::val_str(String *str) longlong nr=val_int(); if (null_value) return 0; - else + else if (!unsigned_flag) str->set(nr); + else + str->set((ulonglong) nr); return str; } diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index 12561fe4326..355f28d5432 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -17,7 +17,7 @@ /* This file defines all string functions ** Warning: Some string functions doesn't always put and end-null on a String -** (This shouldn't be neaded) +** (This shouldn't be needed) */ #ifdef __GNUC__ @@ -384,7 +384,7 @@ void Item_func_reverse::fix_length_and_dec() /* ** Replace all occurences of string2 in string1 with string3. -** Don't reallocate val_str() if not neaded +** Don't reallocate val_str() if not needed */ /* TODO: Fix that this works with binary strings when using USE_MB */ diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 812fc636fef..712c0fa308e 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -52,6 +52,8 @@ void Item_sum::make_field(Send_field *tmp_field) tmp_field->flags=0; if (!maybe_null) tmp_field->flags|= NOT_NULL_FLAG; + if (unsigned_flag) + tmp_field->flags |= UNSIGNED_FLAG; tmp_field->length=max_length; tmp_field->decimals=decimals; tmp_field->type=(result_type() == INT_RESULT ? FIELD_TYPE_LONG : @@ -150,7 +152,7 @@ Item_sum_hybrid::fix_fields(THD *thd,TABLE_LIST *tables) return 1; hybrid_type=item->result_type(); if (hybrid_type == INT_RESULT) - max_length=21; + max_length=20; else if (hybrid_type == REAL_RESULT) max_length=float_length(decimals); else @@ -158,6 +160,7 @@ Item_sum_hybrid::fix_fields(THD *thd,TABLE_LIST *tables) decimals=item->decimals; maybe_null=item->maybe_null; binary=item->binary; + unsigned_flag=item->unsigned_flag; result_field=0; null_value=1; fix_length_and_dec(); @@ -323,12 +326,27 @@ double Item_sum_hybrid::val() { if (null_value) return 0.0; - if (hybrid_type == STRING_RESULT) - { + switch (hybrid_type) { + case STRING_RESULT: String *res; res=val_str(&str_value); return res ? atof(res->c_ptr()) : 0.0; + case INT_RESULT: + if (unsigned_flag) + return ulonglong2double(sum_int); + return (double) sum_int; + case REAL_RESULT: + return sum; } - return sum; + return 0; // Keep compiler happy +} + +longlong Item_sum_hybrid::val_int() +{ + if (null_value) + return 0; + if (hybrid_type == INT_RESULT) + return sum_int; + return (longlong) Item_sum_hybrid::val(); } @@ -337,25 +355,26 @@ Item_sum_hybrid::val_str(String *str) { if (null_value) return 0; - if (hybrid_type == STRING_RESULT) + switch (hybrid_type) { + case STRING_RESULT: return &value; - str->set(sum,decimals); - return str; + case REAL_RESULT: + str->set(sum,decimals); + break; + case INT_RESULT: + if (unsigned_flag) + str->set((ulonglong) sum_int); + else + str->set((longlong) sum_int); + break; + } + return str; // Keep compiler happy } - bool Item_sum_min::add() { - if (hybrid_type != STRING_RESULT) - { - double nr=args[0]->val(); - if (!args[0]->null_value && (null_value || nr < sum)) - { - sum=nr; - null_value=0; - } - } - else + switch (hybrid_type) { + case STRING_RESULT: { String *result=args[0]->val_str(&tmp_value); if (!args[0]->null_value && @@ -366,22 +385,39 @@ bool Item_sum_min::add() null_value=0; } } - return 0; -} - - -bool Item_sum_max::add() -{ - if (hybrid_type != STRING_RESULT) + break; + case INT_RESULT: + { + longlong nr=args[0]->val_int(); + if (!args[0]->null_value && (null_value || + (unsigned_flag && + (ulonglong) nr < (ulonglong) sum_int) || + (!unsigned_flag && nr < sum_int))) + { + sum_int=nr; + null_value=0; + } + } + break; + case REAL_RESULT: { double nr=args[0]->val(); - if (!args[0]->null_value && (null_value || nr > sum)) + if (!args[0]->null_value && (null_value || nr < sum)) { sum=nr; null_value=0; } } - else + break; + } + return 0; +} + + +bool Item_sum_max::add() +{ + switch (hybrid_type) { + case STRING_RESULT: { String *result=args[0]->val_str(&tmp_value); if (!args[0]->null_value && @@ -392,6 +428,31 @@ bool Item_sum_max::add() null_value=0; } } + break; + case INT_RESULT: + { + longlong nr=args[0]->val_int(); + if (!args[0]->null_value && (null_value || + (unsigned_flag && + (ulonglong) nr > (ulonglong) sum_int) || + (!unsigned_flag && nr > sum_int))) + { + sum_int=nr; + null_value=0; + } + } + break; + case REAL_RESULT: + { + double nr=args[0]->val(); + if (!args[0]->null_value && (null_value || nr > sum)) + { + sum=nr; + null_value=0; + } + } + break; + } return 0; } @@ -676,9 +737,17 @@ Item_sum_hybrid::min_max_update_int_field(int offset) nr=args[0]->val_int(); if (!args[0]->null_value) { - if (result_field->is_null(offset) || - (cmp_sign > 0 ? old_nr > nr : old_nr < nr)) + if (result_field->is_null(offset)) old_nr=nr; + else + { + bool res=(unsigned_flag ? + (ulonglong) old_nr > (ulonglong) nr : + old_nr > nr); + /* (cmp_sign > 0 && res) || (!(cmp_sign > 0) && !res) */ + if (cmp_sign > 0 ^ !res) + old_nr=nr; + } result_field->set_notnull(); } else if (result_field->is_null(offset)) @@ -1079,7 +1148,7 @@ void Item_udf_sum::reset() bool Item_udf_sum::add() { - DBUG_ENTER("Item_udf_sum::reset"); + DBUG_ENTER("Item_udf_sum::add"); udf.add(&null_value); DBUG_RETURN(0); } diff --git a/sql/item_sum.h b/sql/item_sum.h index 7356eeda28c..18b0c3ff577 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -271,6 +271,7 @@ class Item_sum_hybrid :public Item_sum protected: String value,tmp_value; double sum; + longlong sum_int; Item_result hybrid_type; int cmp_sign; table_map used_table_cache; @@ -286,12 +287,13 @@ class Item_sum_hybrid :public Item_sum void reset() { sum=0.0; + sum_int=0; value.length(0); null_value=1; add(); } double val(); - longlong val_int() { return (longlong) val(); } /* Real as default */ + longlong val_int(); void reset_field(); String *val_str(String *); void make_const() { used_table_cache=0; } diff --git a/sql/mf_iocache.cc b/sql/mf_iocache.cc index 24af439961e..40b98983291 100644 --- a/sql/mf_iocache.cc +++ b/sql/mf_iocache.cc @@ -81,7 +81,7 @@ int init_io_cache(IO_CACHE *info, File file, uint cachesize, if ((my_off_t) cachesize > end_of_file-seek_offset+IO_SIZE*2-1) { cachesize=(uint) (end_of_file-seek_offset)+IO_SIZE*2-1; - use_async_io=0; /* No nead to use async */ + use_async_io=0; /* No need to use async */ } } } diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 34fae62ad56..7a32a253d6c 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -71,7 +71,6 @@ char* query_table_status(THD *thd,const char *db,const char *table_name); #define HASH_PASSWORD_LENGTH 16 #define HOST_CACHE_SIZE 128 #define MAX_ACCEPT_RETRY 10 // Test accept this many times -#define MAX_BLOB_WIDTH 8192 // Default width for blob #define MAX_FIELDS_BEFORE_HASH 32 #define USER_VARS_HASH_SIZE 16 #define STACK_MIN_SIZE 8192 // Abort if less stack during eval. diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 67b5ba882d2..5b25b1fe660 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -342,7 +342,7 @@ ulong opt_sql_mode = 0L; const char *sql_mode_names[] = { "REAL_AS_FLOAT", "PIPES_AS_CONCAT", "ANSI_QUOTES", "IGNORE_SPACE", "SERIALIZE","ONLY_FULL_GROUP_BY", NullS }; -TYPELIB sql_mode_typelib= {array_elements(sql_mode_names),"", +TYPELIB sql_mode_typelib= {array_elements(sql_mode_names)-1,"", sql_mode_names}; MY_BITMAP temp_pool; @@ -1738,7 +1738,7 @@ int main(int argc, char **argv) pthread_attr_setscope(&connection_attrib, PTHREAD_SCOPE_SYSTEM); #if defined( SET_RLIMIT_NOFILE) || defined( OS2) - /* connections and databases neads lots of files */ + /* connections and databases needs lots of files */ { uint wanted_files=10+(uint) max(max_connections*5, max_connections+table_cache_size*2); @@ -1906,7 +1906,7 @@ The server will not act as a slave."); (void) pthread_kill(signal_thread,MYSQL_KILL_SIGNAL); #ifndef __WIN__ if (!opt_bootstrap) - (void) my_delete(pidfile_name,MYF(MY_WME)); // Not neaded anymore + (void) my_delete(pidfile_name,MYF(MY_WME)); // Not needed anymore #endif exit(1); } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 0831634a9a6..cc659623f1d 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -607,7 +607,7 @@ int SQL_SELECT::test_quick_select(key_map keys_to_use, table_map prev_tables, if (limit < records) read_time=(double) records+scan_time+1; // Force to use index else if (read_time <= 2.0 && !force_quick_range) - DBUG_RETURN(0); /* No nead for quick select */ + DBUG_RETURN(0); /* No need for quick select */ DBUG_PRINT("info",("Time to scan table: %ld",(long) read_time)); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 89910d3745e..4c012804c3e 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -15,7 +15,7 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ -/* Basic functions neaded by many modules */ +/* Basic functions needed by many modules */ #include "mysql_priv.h" #include "sql_acl.h" @@ -165,14 +165,15 @@ OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild) open_list=0; // Out of memory break; } - (*start_list)->table=(strmov((*start_list)->db=(char*) ((*start_list)+1), - entry->table_cache_key)+1, - entry->real_name); + strmov((*start_list)->table= + strmov(((*start_list)->db= (char*) ((*start_list)+1)), + entry->table_cache_key)+1, + entry->real_name); (*start_list)->in_use= entry->in_use ? 1 : 0; (*start_list)->locked= entry->locked_by_name ? 1 : 0; start_list= &(*start_list)->next; + *start_list=0; } - *start_list=0; VOID(pthread_mutex_unlock(&LOCK_open)); DBUG_RETURN(open_list); } diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 1d2805d8e90..54c3e91bcc3 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -391,7 +391,7 @@ int write_record(TABLE *table,COPY_INFO *info) } else { - if (table->file->extra(HA_EXTRA_FLUSH_CACHE)) /* Not neaded with NISAM */ + if (table->file->extra(HA_EXTRA_FLUSH_CACHE)) /* Not needed with NISAM */ { error=my_errno; goto err; @@ -534,7 +534,7 @@ public: } ~delayed_insert() { - /* The following is not really neaded, but just for safety */ + /* The following is not really needed, but just for safety */ delayed_row *row; while ((row=rows.get())) delete row; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index ff404cce0d6..ec147c38e9b 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -22,7 +22,7 @@ class Table_ident; class sql_exchange; class LEX_COLUMN; -// The following hack is neaded because mysql_yacc.cc does not define +// The following hack is needed because mysql_yacc.cc does not define // YYSTYPE before including this file #ifdef MYSQL_YACC diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index c6e23eb927a..929891da889 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -855,7 +855,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd, DBUG_PRINT("info",("query ready")); break; } - case COM_FIELD_LIST: // This isn't actually neaded + case COM_FIELD_LIST: // This isn't actually needed #ifdef DONT_ALLOW_SHOW_COMMANDS send_error(&thd->net,ER_NOT_ALLOWED_COMMAND); /* purecov: inspected */ break; @@ -2390,7 +2390,7 @@ bool my_yyoverflow(short **yyss, YYSTYPE **yyvs, int *yystacksize) /**************************************************************************** - Initialize global thd variables neaded for query + Initialize global thd variables needed for query ****************************************************************************/ static void diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3aa534222e7..c122ba51a96 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3306,7 +3306,7 @@ Field *create_tmp_field(TABLE *table,Item *item, Item::Type type, item->name,table,item_sum->decimals); case INT_RESULT: return new Field_longlong(item_sum->max_length,maybe_null, - item->name,table); + item->name,table,item->unsigned_flag); case STRING_RESULT: if (item_sum->max_length > 255) return new Field_blob(item_sum->max_length,maybe_null, @@ -3357,7 +3357,7 @@ Field *create_tmp_field(TABLE *table,Item *item, Item::Type type, break; case INT_RESULT: new_field=new Field_longlong(item->max_length,maybe_null, - item->name,table); + item->name,table, item->unsigned_flag); break; case STRING_RESULT: if (item->max_length > 255) diff --git a/sql/structs.h b/sql/structs.h index 12ba5004a2e..469d3feea08 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -170,7 +170,7 @@ typedef struct st_lex_user { /* Bits in form->status */ #define STATUS_NO_RECORD (1+2) /* Record isn't usably */ #define STATUS_GARBAGE 1 -#define STATUS_NOT_FOUND 2 /* No record in database when neaded */ +#define STATUS_NOT_FOUND 2 /* No record in database when needed */ #define STATUS_NO_PARENT 4 /* Parent record wasn't found */ #define STATUS_NOT_READ 8 /* Record isn't read */ #define STATUS_UPDATED 16 /* Record is updated by formula */ diff --git a/sql/time.cc b/sql/time.cc index d1e0aee0fa4..440cba457f4 100644 --- a/sql/time.cc +++ b/sql/time.cc @@ -24,7 +24,7 @@ static ulong const days_at_timestart=719528; /* daynr at 1970.01.01 */ uchar *days_in_month= (uchar*) "\037\034\037\036\037\036\037\037\036\037\036\037"; - /* Init some variabels neaded when using my_local_time */ + /* Init some variabels needed when using my_local_time */ /* Currently only my_time_zone is inited */ static long my_time_zone=0; diff --git a/sql/unireg.cc b/sql/unireg.cc index c5bfbbbea88..b0dd3471a64 100644 --- a/sql/unireg.cc +++ b/sql/unireg.cc @@ -20,7 +20,7 @@ struct. In the following functions FIELD * is an ordinary field-structure with the following exeptions: - sc_length,typepos,row,kol,dtype,regnr and field nead not to be set. + sc_length,typepos,row,kol,dtype,regnr and field need not to be set. str is a (long) to record position where 0 is the first position. */ @@ -391,8 +391,8 @@ static bool pack_header(uchar *forminfo, enum db_type table_type, int2store(forminfo+272,int_parts); int2store(forminfo+274,int_length); int2store(forminfo+276,time_stamp_pos); - int2store(forminfo+278,80); /* Columns neaded */ - int2store(forminfo+280,22); /* Rows neaded */ + int2store(forminfo+278,80); /* Columns needed */ + int2store(forminfo+280,22); /* Rows needed */ int2store(forminfo+282,null_fields); DBUG_RETURN(0); } /* pack_header */ |