MySQL: Upgrading MySQL 4.0 to 4.1

Upgrading to next MySQL version was usually quite safe. I remember quite painlessly uprading from 3.22 to 3.23, Upgrade 3.23 to 4.0 cause a bit of head ache as behavior of substraction was changed, this however was easily fixed by --sql-mode=NO_UNSIGNED_SUBTRACTION option.

With upgrade of 4.0 to 4.1 there are much more surprises, which I still see users and customer running into. We have extensive list of upgrade notes
but the practice shows not everyone has read it before upgrading: http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html

So few highlights, ranging from minor to serve.

- There are various minor but annoying syntax changes. For example you can't use "create table x(i int)" any more as I became function,
you also now need () in your UNION queries when you previously could have skipped them.

- SHOW CREATE TABLE and SHOW TABLE STATUS was changed to use "Engine" instead of "Type" which broke some of my scripts which detect storage engine of the table to decide how to work.

- ENUM values are now case insensitive by default which can give you some surprises,

- TIMESTAMP fomat output has changed significantly so if you parse it in your scripts it is very likely they are broken.

- "Illegal mix of collations" can drive you nuts, for example previously you could compare string to binary string while now you can't

- Sorting order was changed if you have characters with codes less than 32 in the end, for example "\t" and "\n" This can corrupt your tables, and even reported to hang some applications, so it is better to run CHECK TABLE after upgrade or check if you have such data in tables before. There are
few more cases when data dump/reload may be needed for upgrade.

- Authentication changes may break your applications linked to old client library, use --old-passwords option at server or relink

- Client library name (version part) changed so dynamically linked applications may no more work after upgrade. MySQL-Compat RPM package can be used
to cure it. Especially this is frequently reported with PHP

- Limit does not accept -1 as second parameter, use some large number instead (yes find all your queries and fix them)

- I also heard some changes were made in a way meta-data returned (column types etc) - this can affect PHP or JDBC applications getting extensive
use of it.

- Database,Table names and meta data are treated as unicode now, so if you used non-latin1 (ie Russian) table name you better to rename them for
upgrade otherwise you will get garbage where.

I'm sure there are more surprises, these are just I have seem most typically to happen.

Also be careful upgrading/downgrading if you're using UTF8. If you will configure MySQL 4.1 to use utf8 by default and just start it with MySQL 4.0 latin1 tables you're likely to trash your data, as there is no charset information in old tables and MySQL 4.1 will assume the data is in unicode.
So do not change character set straight ahead but run 4.1 with same charset as 4.0 before and use ALTER TABLE to convert tables to 4.1 format - this
has character set information so you should be safe.

And yes... Downgrade from 4.1 to 4.0 is no more safe with this all these changes. Worst of all it is not clearly sure when it is safe and when it is not
(On my dev machine I bounce between 4.1 and 4.0 pretty frequently and it works fine, but I do not do anything advanced wher), so take backup before upgrade and test it well before going to production (which is always good idea anyway).