Just a minor caution, though ....

I've just upgraded our development server to MySQL 4.1 (previously running 4.0.summat) and there were a couple of issues that I thought I should highlight:

a) The new version uses a new password authentication system by default, which is no bad thing except that the older client programs can't access it by default.  The recommended cure is (of course) to upgrade your clients but that's not always possible ... if you need the old authentication system to work you can set an old password onto an account:

SET PASSWORD FOR "wwweb"@"localhost" = OLD_PASSWORD("paddington") ;

for example

b) I had an issue with equality testing a field from PHP - a clause
     $where = "WHERE place = '$placevar'";
that had previously worked now fails - fixed by changing it to:
     $where = "WHERE place = '".trim($placevar)."'";

I thing that I have a trailing new line character on $placevar which the older system was accepting but the new one does not - but I haven't spend much time analysing it and it's possible that there was another issue involved as well.

Graham,
A few questions/statements regarding MySQL 4.1:-

What does the collation do and why does it default to latin1_swedish_ci? How do I chage this default?

What should I set it to in order to enable my older MySQL 4.0.x tables to import correctly into version 4.1.x.? As when I imported mine, the special characters like 'tm' (trademark) were lost and converted to question marks '?'.

If you upgrade to 4.1 then your tables will not export back to an older 4.0.x version. (I assume this is due to the collation information being included in the structure).
So be sure to keep a backup of your database before upgrading.

Anyway, it's confused me so I've gone back to version 4.0.x until I have had more time to play with this new version.

Thanks

Keith

on 02/17/05 at 13:47:54, keith wrote:

Anyway, it's confused me so I've gone back to version 4.0.x until I have had more time to play with this new version.

Probably wise ... the change from 4.0 to 4.1 is bigger that you might expect from just a change to the second digit of the release number.   And I'm sure the "swedish" default is because MySQL comes from Scandinavia.

Keith - I've been away and on line from some poor connections - only just spotted the question even though it's been here for a few days. I'll have a look / dig overnight

Hi Graham,

Thanks for your response.
I have been checking back most days since I posted this.

Did you manage to find out what I need to set MySQL 4.1.x to in order for correct import, without loss of characters?

How do I set the default language settings?

Is it possible to export back to 4.0.x?

Don't worry if these questions are causing headaches. I realise that its a new version and hasn't been really been adopted yet. (like PHP 5).
So if this is time consuming....just forget it for now.. I'll wait until this version is better established.
(I know your a very busy man    )

My goodness you're right - I've been so busy it's untrue. Just look at the time of day I'm posting  

Yes, it does take a time for new versions to be adopted, especially where there's any form of compatability issue and there's a huge installed base of MySQL out there.   It's also MySQL's stated policy to continue to support older code in a very much more thorough way that othe software authors continue on with older versions, so there's no big need to rush across.   More about that in the next "Of Course" ...

Over the weekend, I did have to export some data from 4.1 (on my own test system) to install back on a 3.23 system (on an ISP's machine).   Single table, and no special characters ... I did a mysqldump and then selected the commands that I needed to source the code on the older system.  Bit of a "kludge" but it worked and I've still got a pile of things to do before today's course!!

For your information.

To convert back from 4.1.x to 4.0.x. Do the following
1) Export the database as a .SQL file. (eg via PHPMyAdmin)
2) Open it in notepad
3) Do a Edit -> Replace. Replace ENGINE= for TYPE=
4) Do another Edit -> Replace.  Replace DEFAULT CHARSET=latin1 for <nothing>.
5) Save the file (don't overwrite the exisiting file as you may still need it!)
It should now import successfully into 4.0.x.

Notes :
i) Where <nothing> means don't type anything in the 'Replace with' box.
ii) If latin1 is not the default charset, change this to match yours in step 4.

There, answered my own question and hopefully it will help others out there.

Magic, thanks Keith!

---