2.5.2 Upgrading From Version 3.23 to 4.0

In general, you should do the following when upgrading to 4.0 from an earlier MySQL version:

• Run the script mysql_fix_privilege_tables to add new privileges and features to the MySQL privilege tables.
• Edit any MySQL startup scripts or configure files to not use any of the deprecated options described later in this section.
• Convert your old ISAM files to MyISAM files with the mysql_convert_table_format database 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 only be used 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. You can also issue a ALTER TABLE table_name TYPE=MyISAM statement for each ISAM table to convert it to MyISAM.
• Ensure that you don't have any MySQL clients that use shared libraries (like the Perl DBD-mysql mode). 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 do the above, but you will not be able to use the new security privileges that 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 it's deprecated and will be disabled in MySQL 5.0.

Old clients should work with a Version 4.0 server without any problems.

Even if you do the above, 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 is a more complete list that tells what you must watch out for when upgrading to version 4.0:

• MySQL 4.0 has a lot of new privileges in the mysql.user table. See section 4.3.1 GRANT and REVOKE Syntax. To get these new privileges to work, you must run the mysql_fix_privilege_tables script. Until you do, all users 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 users, 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). See section 4.2.3 Startup Options for mysqld Concerning Security. If you get Access denied errors for new users in version 4.0.2 and up, you should check if 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 slaves.
• `safe_mysqld' is renamed to `mysqld_safe'. For backward compatibility, binary distributions will for some time include safe_mysqld as a symlink to mysqld_safe.
• The startup parameters myisam_max_extra_sort_file_size and myisam_max_extra_sort_file_size are now given in bytes (they were given in megabytes before 4.0.3).
• External system locking of MyISAM/ISAM files is now turned off by default. Your can turn this on by doing --external-locking. (However, this is never needed for most users.)
• The following startup variables/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.
• The following SQL variables have changed name.

  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=# instead of SET SQL_SLAVE_SKIP_COUNTER=#.
• The mysqld startup options --skip-locking and --enable-locking were renamed to --skip-external-locking and --external-locking.
• SHOW MASTER STATUS now returns an empty set if binary logging is not enabled.
• SHOW SLAVE STATUS now returns an empty set if slave is not initialised.
• mysqld now has the option --temp-pool enabled by default as this gives better performance with some operating systems (most notably Linux).
• DOUBLE and FLOAT columns now honour the UNSIGNED flag on storage (before, UNSIGNED was ignored for these columns).
• 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.
• SHOW INDEX has two more columns (Null and Index_type) than it had in 3.23.
• CHECK, SIGNED, LOCALTIME and LOCALTIMESTAMP are now reserved words.
• 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 Cast Functions.
• 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 Cast Functions.
• To use MATCH ... AGAINST (... IN BOOLEAN MODE) with your tables, you need to rebuild them with REPAIR TABLE table_name USE_FRM.
• LOCATE() and INSTR() are case-sensitive if one of the arguments is a binary string. Otherwise they are case-insensitive.
• STRCMP() now uses the current character set when doing comparisons, which means that the default comparison behaviour 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) by default in case of an error unless you specify IGNORE.
• 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.
• 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 table_name returns a row count in 4.0, and TRUNCATE TABLE is faster.)
• 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.
• The format of SHOW OPEN TABLES has changed.
• Multi-threaded clients should use mysql_thread_init() and mysql_thread_end(). See section 9.1.14 How to Make a Threaded Client.
• If you want to recompile the Perl DBD::mysql module, you must get DBD-mysql version 1.2218 or newer because older DBD modules used the deprecated mysql_drop_db() call. Version 2.1022 or newer is recommended.
• 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 now set to be the more 'general' of the types of A and B. (The general-to-specific order is string, REAL or INTEGER).

If you are running MySQL Server on Windows, please also see section 2.5.7 Upgrading MySQL under Windows. If you are using replication, please also see section 4.10.2 Replication Implementation Overview.

User Comments

Steve,
I just upgraded without any prior research today with ease. I started by reading this page about an hour ago. Then upgraded my 2 production servers. The biggest hold-up was waiting for it to build. I believe they've done an awesome job. And sometimes it just makes more sense to omit old code for the sake of new code, it happens all the time, it's something hard to understand, unless you're worknig on it yourself. BTW, There is a commercial version of Mysql. And if you're having problems and you can't afford to have your server down, you should really look into MySql's support contract, or hire an outside consultant.

Note to Win32 users planning to upgrade to 4.0:

There appears to be no allowance made for a Win compatible mysql_fix_privilege_tables script. Both mysql_fix_privilege_tables and mysql_fix_privilege_tables.sh are identical and are both shell scripts.

Upgrading to 4.0 MySQL may not be possible if you run PHP, Perl or MyODBC.... At this time, most rpm's and packages have a dependency on libmysqlclient.so.10, instead of libmysqlclient.so or libmysqlclient.so.12. This does not occur in every case since the linkage process differs with different build strategies. If you have this difficulty may wish to file a bug "http://www.mysql.com/doc/en/Bug_reports.html report" as well as the particular 3rd party vendor/packager and request that the 3rd party upgrade thier package linkage for libmysqlclient.so. Any strategies for short term work arounds would be helpful.

Anybody have a "simple" way to upgrade the privileges for a newbie mySQL/Windows user?

the shell script doesn't help us much. Amazing that a mysql release left us out completely with such an important issue

Mood

I guess I'll have to rewrite the mysql_fix_privilege_tables script in Perl so I could run it on my win32 box...

... or google it up?

As I couldn't find any perl version of the mysql_fix_privilege_tables script, I had to port to Perl myself. To spare your time, you may download the script from my server.

http://www.vladb.com/pub/mysql_fix_privilege_tables.tar

The file hack above did not work for me. The file runs without error, but does not fix the tables. The good news is that a perl-scripted mysql_fix_privilege_tables file is contained in the MySQL source distribution, although it's true that its not contained in the MySQL 4.0.x binary.

You can download the binary for ease of installation; then, download the source, extract the file and run it on your Windows OS system. If you are not using Perl on your system, you will need to download a package from www.perl.com or www.activestate.com.

PLz... i now this is a VERY STUPID and innocent question...

i Don't know how to run a script :(
i am upgrading from 3.23 to 4.0.12 @ Windows platform

I ve installed the 4.0.12 version, and all seems to be alright, but it tells me (and also in the web site) that i should tun the
mysql_fix_privilege_tables
script...
i 've located the file in mysql dir, but... my problem is i dont know how to run a script :/ :(
PLZ s1 hel me .. ,:(

PS: is it a SQL query? a PHP command? :(
my mail if anyone could help me :
mailto:mfpinhal@netcabo.pt
Tnhx anyway :)

You need to install perl to run that script. I don't have a windows installation of mysql but I imagine that it must be the same way. See the comment preceeding yours, that says:

"The good news is that a perl-scripted mysql_fix_privilege_tables..."

Installing Perl, I suppose, would be an option to run the script. But, why do that for just this one purpose. It would be helpful if it could be done otherwise... a way that is native to Windows. Any chance of that?

You could run the script (talking win32 here) from the Cygwin shell if you have cygwin installed.
Created a slightly modified script for that.
[ http://sampe.ngstudios.tk/stuff/mysql_fix_privilege_tables.zip ]

To make it work you have to add the path as an argument, like this:
sh mysql_fix_privilege_tables <password> <path>

ex. (default MySQL installation)
sh mysql_fix_privilege_tables password /cygdrive/c/mysql/bin/mysql

What is the best way to upgrade the rpms on Redhat 7.3
with Mysql 3.23.56 already loaded ?

rpm -e all that it errors with or is there a better way ?
then do an rpm -ivh Mysql*.rpm

Forth those of you with PHP and PERL DBD installed, you will have to recompile those packages. There are a few other packages that look for MySQL files that may need to be rebuilt. The easiest way in the RedHat world is to do the following.

D/L and install MySQL 4.0 via RPM to find out what packages bitch

rpm -Uvh mysql4....rpm

This will give you errors, write down each package that presents a dependancy error and then D/L the source RPM's for each package.

Then REALLY install the package

rpm -Uvh --nodeps mysql4....rpm

Install all the source RPM's you D/L ed above

rpm -ivh *.srpm

**Build each package by going to /usr/src/redhat/SPECS and then running

rpmbuild -ba package-name.spec

This will recompile the package, and put the new RPM in /usr/src/redhat/RPMS

You can the upgrade your rpm with

rpm -Uvh --force package-name.rpm

Go back to ** and repeat for each RPM SPEC file that you have.

Well this is very interesting

At the start of the script they have
root_password="$1"
host="localhost"
user="root"

I added in just below the above
echo "----------------------------------------------------------"
echo $root_password
echo "----------------------------------------------------------"

Then if you try
sh mysql_fix_privilege_tables -p somepassword
Guess what you get...
----------------------------------------------------------
-p somepassword
----------------------------------------------------------

So it seems the script takes the whole argument...

sh mysql_fix_privilege_tables my_root_pass
Works fine

Is this standard behavior?
The Script says
echo "If you get 'Access denied' errors, you should run this script again"
echo "and give the MySQL root user password as an argument!"

Does not really tell you there is some special way to do it. What is the
heck is $1 anyway? And why is it a literally quoted value?

I am surprised that no one has put together a set of SQL statements instead of the fix script to make the upgrade easier and platform independent.

i think you should have a "Complaints" Type for comments.

Isn't the easiest way to upgrade this:

Make a dump of all databases.
Stop and uninstall server
Install new MySQL 4.0.x
Import all databases again
Start server

Or is there a problem with this method?

@Kim Terp: Yes there is a problem with your method. You loose all the privileges and users that you have setup. You could also transfer the mysql databse which contains users and privileges but then you would have the same problem again.

It seems the command to find out if you have ISAM tables and or MyISAM tables is SHOW TABLE STATUS;

Be nice if that had been mentioned above. I spent most of this morning trying to find that out. No one on IRC knew and it was a student here at our college who finally kenw the answer.

The mysql_fix_privilege_tables script has several flaws. I've rectified them by turning it into a purely SQL script:

http://www.analysisandsolutions.com/code/mysql_fix_privilege_tables_323_to_40.sql

The problems with mysql_fix_privilege_tables is that it doesn't work if any of the following are true:
1) Computer uses Windows operating systems.
2) Have changed the name of their "root" user.
3) Have mysql instlalled in non-default locations.

Also, the case of several field names is incorrect.

Add your own comment.