(mysql.info.gz) Upgrading-from-3
Info Catalog
(mysql.info.gz) Upgrading-from-4
(mysql.info.gz) Upgrade
(mysql.info.gz) Upgrading-from-3
2.10.3 Upgrading from Version 3.23 to 4.0
-----------------------------------------
In general, you should do the following when upgrading to MySQL 4.0
from 3.23:
* Check the items in the change lists found later in this section to
see whether any of them might affect your applications.
* Read the 4.0 news items to see what significant new features you
can use in 4.0. News-4.0.x.
* If you are running MySQL Server on Windows, see Windows
upgrading.
* After upgrading, update the grant tables to add new privileges and
features. The procedure uses the `mysql_fix_privilege_tables'
script and is described in Upgrading-grant-tables.
* If you are using replication, see Replication Upgrade for
information on upgrading your replication setup.
* Edit any MySQL startup scripts or option files to not use any of
the deprecated options described later in this section.
* Convert your old `ISAM' files to `MyISAM' files. One way to do this
is with the `mysql_convert_table_format' script. (This is a Perl
script; it requires that DBI be installed.) To convert the tables
in a given database, use this command:
shell> mysql_convert_table_format database DB_NAME
Note that this should be used only if all tables in the given
database are `ISAM' or `MyISAM' tables. To avoid converting tables
of other types to `MyISAM', you can explicitly list the names of
your `ISAM' tables after the database name on the command line.
Individual tables can be changed to `MyISAM' by using the following
`ALTER TABLE' statement for each table to be converted:
mysql> ALTER TABLE TBL_NAME TYPE=MyISAM;
If you are not sure of the table type for a given table, use this
statement:
mysql> SHOW TABLE STATUS LIKE 'TBL_NAME';
* Ensure that you don't have any MySQL clients that use shared
libraries (like the Perl `DBD::mysql' module). If you do, you
should recompile them, because the data structures used in
`libmysqlclient.so' have changed. The same applies to other MySQL
interfaces as well, such as the Python `MySQLdb' module.
MySQL 4.0 will work even if you don't perform the preceding actions,
but you will not be able to use the new security privileges in MySQL
4.0 and you may run into problems when upgrading later to MySQL 4.1 or
newer. The `ISAM' file format still works in MySQL 4.0, but is
deprecated and is not compiled in by default as of MySQL 4.1. `MyISAM'
tables should be used instead.
Old clients should work with a MySQL 4.0 server without any problems.
Even if you perform the preceding actions, you can still downgrade to
MySQL 3.23.52 or newer if you run into problems with the MySQL 4.0
series. In this case, you must use `mysqldump' to dump any tables that
use full-text indexes and reload the dump file into the 3.23 server.
This is necessary because 4.0 uses a new format for full-text indexing.
The following lists describe changes that may affect applications and
that you should watch out for when upgrading to version 4.0:
*Server Changes:*
* MySQL 4.0 has a lot of new privileges in the `mysql.user' table.
Privileges provided.
To get these new privileges to work, you must update the grant
tables. The procedure is described in
Upgrading-grant-tables. Until you do this, all accounts have
the `SHOW DATABASES', `CREATE TEMPORARY TABLES', and `LOCK TABLES'
privileges. `SUPER' and `EXECUTE' privileges take their value from
`PROCESS'. `REPLICATION SLAVE' and `REPLICATION CLIENT' take their
values from `FILE'.
If you have any scripts that create new MySQL user accounts, you
may want to change them to use the new privileges. If you are not
using `GRANT' commands in the scripts, this is a good time to
change your scripts to use `GRANT' instead of modifying the grant
tables directly.
From version 4.0.2 on, the option `--safe-show-database' is
deprecated (and no longer does anything). Privileges
options.
If you get `Access denied' errors for new users in version 4.0.2
and up, you should check whether you need some of the new grants
that you didn't need before. In particular, you will need
`REPLICATION SLAVE' (instead of `FILE') for new slave servers.
* `safe_mysqld' has been renamed to `mysqld_safe'. For backward
compatibility, binary distributions will for some time include
`safe_mysqld' as a symlink to `mysqld_safe'.
* `InnoDB' support is included by default in binary distributions.
If you build MySQL from source, `InnoDB' is configured in by
default. If you do not use `InnoDB' and want to save memory when
running a server that has `InnoDB' support enabled, use the
`--skip-innodb' server startup option. To compile MySQL without
`InnoDB' support, run `configure' with the `--without-innodb'
option.
* Values for the startup parameters
`myisam_max_extra_sort_file_size' and
`myisam_max_extra_sort_file_size' are given in bytes (they were
given in megabytes before 4.0.3).
* `mysqld' has the option `--temp-pool' enabled by default because
this gives better performance with some operating systems (most
notably Linux).
* The `mysqld' startup options `--skip-locking' and
`--enable-locking' were renamed to `--skip-external-locking' and
`--external-locking'.
* External system locking of `MyISAM'/`ISAM' files is turned off by
default. You can turn this on with `--external-locking'.
(However, this is never needed for most users.)
* The following startup variables and options have been renamed:
*Old Name* *New Name*
`myisam_bulk_insert_tree_size' `bulk_insert_buffer_size'
`query_cache_startup_type' `query_cache_type'
`record_buffer' `read_buffer_size'
`record_rnd_buffer' `read_rnd_buffer_size'
`sort_buffer' `sort_buffer_size'
`warnings' `log-warnings'
`--err-log' `--log-error' (for `mysqld_safe')
The startup options `record_buffer', `sort_buffer', and `warnings'
will still work in MySQL 4.0 but are deprecated.
*SQL Changes:*
* The following SQL variables have been renamed:
*Old Name* *New Name*
`SQL_BIG_TABLES' `BIG_TABLES'
`SQL_LOW_PRIORITY_UPDATES' `LOW_PRIORITY_UPDATES'
`SQL_MAX_JOIN_SIZE' `MAX_JOIN_SIZE'
`SQL_QUERY_CACHE_TYPE' `QUERY_CACHE_TYPE'
The old names still work in MySQL 4.0 but are deprecated.
* You have to use `SET GLOBAL SQL_SLAVE_SKIP_COUNTER=skip_count'
instead of `SET SQL_SLAVE_SKIP_COUNTER=skip_count'.
* `SHOW MASTER STATUS' returns an empty set if binary logging is not
enabled.
* `SHOW SLAVE STATUS' returns an empty set if the slave is not
initialized.
* `SHOW INDEX' has two more columns than it had in 3.23 (`Null' and
`Index_type').
* The format of `SHOW OPEN TABLES' has changed.
* `ORDER BY col_name DESC' sorts `NULL' values last, as of MySQL
4.0.11. In 3.23 and in earlier 4.0 versions, this was not always
consistent.
* `CHECK', `LOCALTIME', and `LOCALTIMESTAMP' are reserved words.
* `DOUBLE' and `FLOAT' columns honor the `UNSIGNED' flag on storage
(before, `UNSIGNED' was ignored for these columns).
* The result of all bitwise operators (`|', `&', `<<', `>>', and
`~') is unsigned. This may cause problems if you are using them
in a context where you want a signed result. Cast
Functions.
* When you use subtraction between integer values where one
is of type `UNSIGNED', the result will be unsigned. In other
words, before upgrading to MySQL 4.0, you should check your
application for cases in which you are subtracting a value from an
unsigned entity and want a negative answer or subtracting an
unsigned value from an integer column. You can disable this
behavior by using the `--sql-mode=NO_UNSIGNED_SUBTRACTION' option
when starting `mysqld'. Server SQL mode.
* 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.
* In MySQL 3.23, `INSERT INTO ... SELECT' always had `IGNORE'
enabled. As of 4.0.1, MySQL will stop (and possibly roll back) by
default in case of an error unless you specify `IGNORE'.
* You should use `TRUNCATE TABLE' when you want to delete all rows
from a table and you don't need to obtain a count of the number of
rows that were deleted. (`DELETE FROM TBL_NAME' returns a row
count in 4.0 and doesn't reset the `AUTO_INCREMENT' counter, and
`TRUNCATE TABLE' is faster.)
* You will get an error if you have an active transaction or `LOCK
TABLES' statement when trying to execute `TRUNCATE TABLE' or `DROP
DATABASE'.
* To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' full-text searches
with your tables, you must rebuild their indexes with `REPAIR TABLE
tbl_name USE_FRM'. If you attempt a boolean full-text search
without rebuilding the indexes this way, the search will return
incorrect results. Fulltext Fine-tuning.
* `LOCATE()' and `INSTR()' are case sensitive if one of the
arguments is a binary string. Otherwise they are case insensitive.
* `STRCMP()' uses the current character set when performing
comparisons. This makes the default comparison behavior not case
sensitive unless one or both of the operands are binary strings.
* `HEX(STR)' returns the characters in STR converted to hexadecimal.
If you want to convert a number to hexadecimal, you should ensure
that you call `HEX()' with a numeric argument.
* `RAND(seed)' returns a different random number series in 4.0 than
in 3.23; this was done to further differentiate `RAND(seed)' and
`RAND(seed+1)'.
* The default type returned by `IFNULL(A,B)' is set to be the more
"general" of the types of `A' and `B'. (The general-to-specific
order is string, `REAL', `INTEGER').
*C API Changes:*
* The old C API functions `mysql_drop_db()', `mysql_create_db()', and
`mysql_connect()' are no longer supported unless you compile MySQL
with `CFLAGS=-DUSE_OLD_FUNCTIONS'. However, it is preferable to
change client programs to use the new 4.0 API instead.
* In the `MYSQL_FIELD' structure, `length' and `max_length' have
changed from `unsigned int' to `unsigned long'. This should not
cause any problems, except that they may generate warning messages
when used as arguments in the `printf()' class of functions.
* Multi-threaded clients should use `mysql_thread_init()' and
`mysql_thread_end()'. Threaded clients.
*Other Changes:*
* If you want to recompile the Perl `DBD::mysql' module, use a recent
version. Version 2.9003 is recommended. Versions older than
1.2218 should not be used because they use the deprecated
`mysql_drop_db()' call.
Info Catalog
(mysql.info.gz) Upgrading-from-4
(mysql.info.gz) Upgrade
(mysql.info.gz) Upgrading-from-3
automatically generated byinfo2html