You can always move the MySQL form and data files between different versions on the same architecture as long as you have the same base version of MySQL. The current base version is 3. If you change the character set when running MySQL (which may also change the sort order), you must run myisamchk -r -q on all tables. Otherwise, your indexes may not be ordered correctly.
If you are afraid of new versions, you can always rename your old mysqld to something like mysqld-old-version-number. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld!
When you do an upgrade you should also back up your old databases, of course.
If after an upgrade, you experience problems with recompiled client programs,
like Commands out of sync or unexpected core dumps, you probably have
used an old header or library file when compiling your programs. In this
case you should check the date for your mysql.h file and
libmysqlclient.a library to verify that they are from the new
MySQL distribution. If not, please recompile your programs!
If you get some problems that the new mysqld server doesn’t want to
start or that you can’t connect without a password, check that you don’t
have some old my.cnf file from your old installation! You can
check this with: program-name --print-defaults. If this outputs
anything other than the program name, you have an active my.cnf
file that will affect things!
It is a good idea to rebuild and reinstall the Msql-Mysql-modules distribution whenever you install a new release of MySQL, particularly if you notice symptoms such as all your DBI scripts dumping core after you upgrade MySQL.
You can use your old data files without any modification with Version 4.0. If you want to move your data from a MySQL 4.0 server to an older server, you have to use 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;
DOUBLE and FLOAT columns are now honoring the UNSIGNED flag on storage (before, UNSIGNED was ignored for these columns).
Use ORDER BY column DESC now always sorts NULL values first; in 3.23 this was not always consistent.
SHOW INDEX has 2 columns more (Null and Index_type) than it had in 3.23.
SIGNED is a reserved word.
The result of all bitwise operators |, &, <<, >>, and ~ is now unsigned. This may cause problems if you are using them in a context where you want a signed result. See Section 6.3.5.
Note: 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 where 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 behaviour by using the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. See Section 6.3.5.
To use MATCH ... AGAINST (... IN BOOLEAN MODE) with your tables, you need to rebuild them with ALTER TABLE table_name TYPE=MyISAM, even if they are of MyISAM type.
LOCATE( ) and INSTR( ) are case-sensitive if one of the arguments is a binary string.
STRCMP( ) now uses the current character set when doing comparisons, which means that the default comparison behavior now is case-insensitive.
HEX(string) now returns the characters in string converted to hexadecimal. If you want to convert a number to hexadecimal, you should ensure that you call HEX( ) with a numeric argument.
In 3.23, INSERT INTO ... SELECT always had IGNORE enabled. In 4.0.1, MySQL will stop (and possibly roll back) in case of an error if you don’t specify IGNORE.
safe_mysqldis renamed tomysqld_safe.The old C API functions mysql_drop_db, mysql_create_db, and mysql_connect are not supported anymore, unless you compile MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS. Instead of doing this, it is preferable to change the client to use the new 4.0 API.
In the MYSQL_FIELD structure, length and max_length have changed from unsigned int to unsigned long. This should not cause any other problems than some warnings if you use these to printf( ) type function.
You should use TRUNCATE TABLE when you want to delete all rows from a table and you don’t care how many rows were deleted. (Because TRUNCATE TABLE is faster than DELETE FROM table_name).
You will get an error if you have an active LOCK TABLES or transaction when trying to execute TRUNCATE TABLE or DROP DATABASE.
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.
Format of SHOW OPEN TABLE has changed.
Multi-threaded clients should use mysql_thread_init( ) and mysql_thread_end( ). See Section 8.4.8.
If you want to recompile the Perl DBD::mysql module, you must get Msql-Mysql-modules version 1.2218 or newer because the older DBD modules used the deprecated drop_db( ) call.
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).
MySQL Version 3.23 supports tables of the new MyISAM type and the old ISAM type. You don’t have to convert your old tables to use these with Version 3.23. By default, all new tables will be created with type MyISAM (unless you start mysqld with the --default-table-type=isam option). You can change an ISAM table to a MyISAM table with ALTER TABLE table_name TYPE=MyISAM or the Perl script mysql_convert_table_format.
Version 3.22 and 3.21 clients will work without any problems with a Version 3.23 server.
The following list tells what you have to watch out for when upgrading to Version 3.23:
All tables that use the tis620 character set must be fixed with myisamchk -r or REPAIR TABLE.
If you do a DROP DATABASE on a symbolic linked database, both the link and the original database are deleted. (This didn’t happen in 3.22 because configure didn’t detect the readlink system call.)
OPTIMIZE TABLE now only works for MyISAM tables. For other table types, you can use ALTER TABLE to optimise the table. During OPTIMIZE TABLE the table is now locked from other threads.
The MySQL client mysql is now by default started with the option --no-named-commands (-g). This option can be disabled with --enable-named-commands (-G). This may cause incompatibility problems in some cases—for example, in SQL scripts that use named commands without a semicolon! Long format commands still work from the first line.
Date functions that work on parts of dates (like MONTH( )) will now return 0 for 0000-00-00 dates. (MySQL 3.22 returned NULL.)
If you are using the german character sort order, you must repair all your tables with isamchk -r, as we have made some changes in the sort order!
The default return type of IF will now depend on both arguments and not only the first argument.
AUTO_INCREMENT will not work with negative numbers. The reason for this is that negative numbers caused problems when wrapping from -1 to 0. AUTO_INCREMENT for MyISAM tables is no handled at a lower level and is much faster than before. For MyISAM tables old numbers are also not reused anymore, even if you delete some rows from the table.
CASE, DELAYED, ELSE, END, FULLTEXT, INNER, RIGHT, THEN, and WHEN are now reserved words.
FLOAT(X) is now a true floating-point type and not a value with a fixed number of decimals.
When declaring DECIMAL(length,dec) the length argument no longer includes a place for the sign or the decimal point.
A TIME string must now be of one of the following formats: [[[DAYS] [H]H:]MM:]SS[.fraction] or [[[[[H]H]H]H]MM]SS[.fraction].
LIKE now compares strings using the same character comparison rules as =. If you require the old behavior, you can compile MySQL with the CXXFLAGS=-DLIKE_CMP_TOUPPER flag.
REGEXP is now case-insensitive for normal (not binary) strings.
When you check/repair tables you should use CHECK TABLE or myisamchk for MyISAM tables (
.MYI) and isamchk for ISAM (.ISM) tables.If you want your mysqldump files to be compatible between MySQL Version 3.22 and Version 3.23, you should not use the --opt or --full option to mysqldump.
Check all your calls to DATE_FORMAT( ) to make sure there is a
%before each format character. (MySQL Version 3.22 did allow this syntax.)mysql_fetch_fields_direct is now a function (it was a macro) and it returns a pointer to a MYSQL_FIELD instead of a MYSQL_FIELD.
mysql_num_fields( ) can no longer be used on a MYSQL* object (it’s now a function that takes MYSQL_RES* as an argument, so you should use mysql_field_count( ) instead).
In MySQL Version 3.22, the output of SELECT DISTINCT ... was almost always sorted. In Version 3.23, you must use GROUP BY or ORDER BY to obtain sorted output.
SUM( ) now returns NULL, instead of 0, if there are no matching rows. This is according to ANSI SQL.
An AND or OR with NULL values will now return NULL instead of 0. This mostly affects queries that use not on an AND/OR expression as NOT NULL = NULL. LPAD( ) and RPAD( ) will shorten the result string if it’s longer than the length argument.
Nothing that affects compatibility has changed between versions 3.21 and 3.22. The only pitfall is that new tables that are created with DATE type columns will use the new way to store the date. You can’t access these new fields from an old version of mysqld.
After installing MySQL Version 3.22, you should start the new server and then run the mysql_fix_privilege_tables script. This will add the new privileges that you need to use the GRANT command. If you forget this, you will get Access denied when you try to use ALTER TABLE, CREATE INDEX, or DROP INDEX. If your MySQL root user requires a password, you should give this as an argument to mysql_fix_privilege_tables.
The C API interface to mysql_real_connect( ) has changed. If you have an old client program that calls this function, you must place a 0 for the new db argument (or recode the client to send the db element for faster connections). You must also call mysql_init( ) before calling mysql_real_connect( )! This change was done to allow the new mysql_options( ) function to save options in the MYSQL handler structure.
The mysqld variable key_buffer has changed names to key_buffer_size, but you can still use the old name in your startup files.
If you are running a version older than Version 3.20.28 and want to switch to Version 3.21, you need to do the following:
You can start the mysqld Version 3.21 server with safe_mysqld --old-protocol to use it with clients from a Version 3.20 distribution. In this case, the new client function mysql_errno( ) will not return any server error, only CR_UNKNOWN_ERROR (but it works for client errors), and the server uses the old password( ) checking rather than the new one.
If you are not using the --old-protocol option to mysqld, you will need to make the following changes:
All client code must be recompiled. If you are using ODBC, you must get the new MyODBC 2.x driver.
The script scripts/add_long_password must be run to convert the Password field in the mysql.user table to CHAR(16).
All passwords must be reassigned in the mysql.user table (to get 62-bit rather than 31-bit passwords).
The table format hasn’t changed, so you don’t have to convert any tables.
MySQL Version 3.20.28 and above can handle the new user table format without affecting clients. If you have a MySQL version earlier than Version 3.20.28, passwords will no longer work with it if you convert the user table. So to be safe, you should first upgrade to at least Version 3.20.28 and then upgrade to Version 3.21.
The new client code works with a 3.20.x mysqld server, so if you experience problems with 3.21.x, you can use the old 3.20.x server without having to recompile the clients again.
If you are not using the --old-protocol option to mysqld, old clients will issue the error message:
ERROR: Protocol mismatch. Server Version = 10 Client Version = 9
The new Perl DBI/DBD interface also supports the old mysqlperl interface. The only change you have to make if you use mysqlperl is to change the arguments to the connect( ) function. The new arguments are: host, database, user, and password (the user and password arguments have changed places). See Section 8.2.2.
The following changes may affect queries in old applications:
HAVING must now be specified before any ORDER BY clause.
The parameters to LOCATE( ) have been swapped.
There are some new reserved words. The most notable are DATE, TIME, and TIMESTAMP.
If you are using MySQL Version 3.23, you can copy the .frm,
.MYI, and .MYD files between different architectures that
support the same floating-point format. (MySQL takes care of any
byte-swapping issues.)
The MySQL ISAM data and index files (.ISD and
*.ISM, respectively) are architecture-dependent and in some cases
OS-dependent. If you want to move your applications to another machine
that has a different architecture or OS than your current machine, you
should not try to move a database by simply copying the files to the
other machine. Use mysqldump instead.
By default, mysqldump will create a file full of SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.
Try mysqldump --help to see what options are available. If you are moving the data to a newer version of MySQL, you should use mysqldump --opt with the newer version to get a fast, compact dump.
The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located:
shell> mysqladmin -h 'other hostname' create db_name
shell> mysqldump --opt db_name \
| mysql -h 'other hostname' db_nameIf you want to copy a database from a remote machine over a slow network, you can use:
shell> mysqladmin create db_name
shell> mysqldump -h 'other hostname' --opt --compress db_name \
| mysql db_nameYou can also store the result in a file, then transfer the file to the target machine and load the file into the database there. For example, you can dump a database to a file on the source machine like this:
shell> mysqldump --quick db_name | gzip > db_name.contents.gz
(The file created in this example is compressed.) Transfer the file containing the database contents to the target machine and run these commands there:
shell> mysqladmin create db_name shell> gunzip < db_name.contents.gz | mysql db_name
You can also use mysqldump and mysqlimport to accomplish the database transfer. For big tables, this is much faster than simply using mysqldump. In the following commands, DUMPDIR represents the full pathname of the directory you use to store the output from mysqldump.
First, create the directory for the output files and dump the database:
shell> mkdir DUMPDIR shell> mysqldump --tab=DUMPDIR db_name
Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:
shell> mysqladmin create db_name # create database shell> cat DUMPDIR/*.sql | mysql db_name # create tables in database shell> mysqlimport db_name DUMPDIR/*.txt # load data into tables
Also, don’t forget to copy the mysql database because that’s where the grant tables (user, db, host) are stored. You may have to run commands as the MySQL root user on the new machine until you have the mysql database in place.
After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
