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!
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
• 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
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 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
• 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.
If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog: