Upgrading from MySQL 3.x to MySQL 4

MySQL has made major strides in its development from version 3.23.xx to version 4, and there are quite a few differences to look out for when upgrading:

The script mysqld_safe replaces safe_mysqld.
There are a large number of new privileges in the user table (in the mysql database). MySQL has supplied a script to add these new permissions, while maintaining the existing permissions. Called mysql_fix_privilege_tables, it takes the REPLICATION SLAVE and REPLICATION CLIENT privileges from the old FILE privilege, and the SUPER and EXECUTE privileges from the old PROCESS. Without running this script, all users will have SHOW DATABASES, CREATE TEMPORARY TABLES, and LOCK TABLES privileges.
The attributes of length and max_length (in the MYSQL_FIELD structure) are now unsigned long instead of unsigned int.
The old --safe-show-database option is deprecated (it no longer does anything, being replaced by the SHOW DATABASES privilege in the user table).

A number of variables have been renamed:

myisam_bulk_insert_tree_size to bulk_insert_buffer_size  query_cache_startup_type to query_cache_type  record_buffer to read_buffer_size  record_rnd_buffer to read_rnd_buffer_size  sort_buffer to sort_buffer_size  warnings to log-warnings

A number of mysqld startup options have a new name:

--skip-locking to --skip-external-locking --enable-locking to --external-locking

The size of the startup parameters myisam_max_extra_sort_file_size and myisam_max_extra_ sort_file_size are now given in bytes, not megabytes.
A number of startup options have been deprecated (they will still work for now):
```
record_buffer sort_buffer warnings
```
External locking is now turned off by default.

The following SQL variables have a new name (the old names still work but are deprecated):

SQL_BIG_TABLES to BIG_TABLES  SQL_LOW_PRIORITY_UPDATES to LOW_PRIORITY_UPDATES SQL_MAX_JOIN_SIZE to MAX_JOIN_SIZE  SQL_QUERY_CACHE_TYPE to QUERY_CACHE_TYPE

SIGNED is a reserved word.
Columns of type DOUBLE and FLOAT no longer ignore the UNSIGNED flag.
BIGINT columns now store integers more efficiently than strings.
Default behavior for the STRCMP() function is now case insensitive because it uses the current character set when doing comparisons.
TRUNCATE TABLE is faster than DELETE FROM tablename because it does not return the number of rows deleted.
The LOCATE() and INSTR() functions are now case sensitive if one of the arguments is a binary string.
The HEX() function now, when passed a string, converts each character to two hexadecimal digits.
The SHOW INDEX statement has two extra columns: Null and Index_type.
You will no longer be able to run the TRUNCATE TABLE or DROP DATABASE statements when there is an active lock (whether from LOCK TABLES or a transaction). An error will be returned instead.
The ORDER BY column_name DESC clause will sort NULL values first in all cases, where previously it did so inconsistently.
Results of all bitwise operators (<<, >>, |, &, ~) are now unsigned, as is the result of subtraction between two integers when either of them is unsigned (the latter can be disabled by starting MySQL with the --sql-mode=NO_UNSIGNED_SUBTRACTION option).
If you want to use the statement MATCH ... AGAINST (... IN BOOLEAN MODE), you'll have to rebuild your tables with ALTER TABLE table_name TYPE=MyISAM. This applies even if the tables are already MyISAM.
You need to specify an IGNORE clause when using a statement of the type INSERT INTO ... SELECT, or else MySQL will stop and possibly roll back.
The RAND(seed) function now returns a different random number series than before (to further differentiate RAND(seed) from RAND(seed+1)).
The format of SHOW OPEN TABLES has changed.
The old mysql_drop_db(), mysql_create_db(), and mysql_connect() C API functions are no longer supported. You can compile MySQL with the CFLAGS=-DUSE_OLD_FUNCTIONS option, but instead update your clients to use the version 4.0 API.
If you're using the Perl DBD::mysql module, you'll need to use a version more recent than 1.2218, as earlier versions used the old drop_db() function.
Instead of using SET SQL_SLAVE_SKIP_COUNTER=#, you need to use SET GLOBAL SQL_ SLAVE_SKIP_COUNTER=#.
Multithreaded clients should use the functions mysql_thread_init() and mysql_thread_end().