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!
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 a ect 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.
Upgrading From Version 3.23 to Version 4.0
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; • INSERT INTO ... SELECT had in 3.23 always IGNORE enabled. In 4.0.1 MySQL will stop (and possible rollback) in case of an error if you don’t specify IGNORE.
• ‘safe_mysqld’ is renamed to ‘mysqld_safe’.
• The old C API functions mysql_drop_db, mysql_create_db and mysql_connect are not supported anymore, unless one compiles MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS. Instead of doing this, one should change the client to use the new 4.0
API.
• In the MYSQL_FIELD structure, length and max_length has 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 of how many rows where 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 e cient.
• Format of SHOW OPEN TABLE has changed.
• Multithreaded clients should use mysql_thread_init() and mysql_thread_end().
• 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.
Version 3.22 and 3.21 clients will work without any problems with a Version 3.23 server.
The following lists tell what you have to watch out for when upgrading to Version 3.23:
• All tables that uses 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 is 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 part 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 is now for MyISAM tables 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 oating-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 ag.
• 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. (Later 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. You should now 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 is 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 a ects
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.
2.5.3 Upgrading from Version 3.21 to Version 3.22
Nothing that a ects compatibility has changed between Version 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.
Upgrading from Version 3.20 to Version 3.21
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 a ecting 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, password (the user and password arguments have changed places).
The following changes may a ect 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.
Upgrading to Another Architecture
If you are using MySQL Version 3.23, you can copy the .frm, .MYI, and .MYD files between di erent architectures that support the same oating-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 di erent 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_name
If 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_name
You 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 <> 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.
Posts
0 comments:
Post a Comment