# mysql 4.1.16 upgrade troubles

## ripp

This has bitten me now on two machines.  I thought the first was just some disk trouble until the 2nd one did it also.

After upgrading the 4.1.16 from 4.1.15, mysql refuses to start.  The errors look like this:

051215  7:49:49  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

051215  7:49:54  InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 2360559.

InnoDB: Doing recovery: scanned up to log sequence number 0 2360601

InnoDB: Last MySQL binlog file position 0 79, file name ./[mymachine]-bin.000180

051215  7:49:55  InnoDB: Flushing modified pages from the buffer pool...

051215  7:49:55  InnoDB: Started; log sequence number 0 2360601

051215  7:49:56 [Warning] './mysql/host' had no or invalid character set, and default

 character set is multi-byte, so character column sizes may have changed

051215  7:49:56 [Warning] './mysql/user' had no or invalid character set, and default

 character set is multi-byte, so character column sizes may have changed

051215  7:49:56 [Warning] './mysql/db' had no or invalid character set, and default c

haracter set is multi-byte, so character column sizes may have changed

051215  7:49:56 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.

20 format.

Luckily I didn't have any critical user data, and I've been able to rebuild the databases from the old files from /var/lib/mysql after basically wiping it all...

Before I upgrade the machines where this will actually matter, is there anything I can do to prevent this from happening?

----------

## jkt

 *ripp wrote:*   

> Before I upgrade the machines where this will actually matter, is there anything I can do to prevent this from happening?

 

RTFM?  :Wink: 

----------

## ripp

I thought I did all of that upgrading from 4.0.x to 4.1.x...

I may have left off a fix-priv-tables which might explain it, but you'd think that would've broken it right away...

 :Rolling Eyes: 

Only one way to find out I guess...

----------

## mbjr

 *jkt wrote:*   

>  *ripp wrote:*   Before I upgrade the machines where this will actually matter, is there anything I can do to prevent this from happening? 
> 
> RTFM? 

 

 :Very Happy:  this is a support form imho  :Smile:  FTFM  :Smile: 

Cheers,

----------

## ripp

Solved, I think.

I think it was a problem w/ the new 4.1.x charsets.  On one machine (my mythtv box) the database was in no particular charset that I was aware of, and mysql mangled it horribly (ascii vs. utf_general_ci etc.).  I took care to fix this properly on the 'production' machines and it went off without a hitch.

rtfm doesn't help when you already have, either  :Wink: 

----------

## jkt

 *mbjr wrote:*   

>  *jkt wrote:*    *ripp wrote:*   Before I upgrade the machines where this will actually matter, is there anything I can do to prevent this from happening? 
> 
> RTFM?  
> 
>  this is a support form imho  FTFM 

 

Have you noticed that I've included a link as well?

----------

## autom8on

I'm an idiot... Upgraded mysql from 4.0.25-r2 to 4.1.19 as part of a upgrade session without really thinking about what I was doing. Hence, it didn't occur to me to back any of the data up first (*kicking myself*). As a result, I'm now getting the same errors as previously described:

 *Quote:*   

> 060507  4:01:17 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
> 
> 060507  4:01:17 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
> 
> 060507  4:01:17 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
> ...

 

At someone else's advice - I backed up the contents of /var/lib/mysql/mysql and removed the directory before running mysql_install_db. This at least leaves me with a running mysql installation.

As well as the old /var/lib/mysql/mysql subdirectory, there are a lot of other subdirectories in /var/lib/mysql/ - the contain a lot of .MYD, .MYI and .frm files - which appear to be the actual data files from the old database installations.

So, now my question - Is there any way I can easily convert/import the data from the old files into the new format/location? Searching with Google hasn't really led to anything of any relevance...

Cheers,

Steve.

----------

## magic919

I'd consider sticking the data you've 'backed up' into an install of the old version.  Then do the mysqldump.  Try to import this into the newer version.

----------

## autom8on

 *magic919 wrote:*   

> I'd consider sticking the data you've 'backed up' into an install of the old version.  Then do the mysqldump.  Try to import this into the newer version.

 

Yeah - after some brief playing with the new version (and finding bugs between it and some of the php stuff I'm using) I decided I was probably better off trying to roll-back to the previous version and get that up and working again. Had to delete the /var/lib/mysql/mysql directory and create a new one with the mysql_install_db script. Once that was done, for each database I wanted to restore, I had to backup the original directory from /var/lib/mysql, create a new database with the same name and then copy the original files back over. Re-started mysql and it all seems to have worked OK (as far as I can tell) - so am planning on getting the newer version installed once I've caught up on some sleep (I don't want to make any more stupid mistakes - spending 4 hours to get back where I was when I began isn't exactly my idea of fun   :Sad:  - though at least I've learnt something about SQL, I suppose   :Smile:  )

----------

## magic919

At least we get to learn stuff along the way.  I'd put off my move from 4.0.x and in the end moved straight to MySQL 5 and then rid myself of mod-php.  Took me most of the last week to get it all settled and DSPAM working again.  But it's all a learning process  :Smile: 

----------

