MySQL restore failing

--=-/TOqFS8d+5KfHkwOoZt6
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Hi All

I am hoping that someone could assist me with this one.

I am trying to restore a database to a new machine for testing
purposes.

The current machine is running mysql-5.0.72sp1 and the new
machine 5.1.50.

When running the restore is runs for quite a while and then
crashes with the following message:



[root@mysql2 home]# mysql -u root -p <
mysql_full_021210.dump
Enter password:
ERROR 1547 (HY000) at line 14284: Column count
of mysql.proc is wrong. Expected 20, found 16.
The table is probably corrupted


I have checked the current production system for this table and
the column count is 16, I then checked the new system as well as a newly
setup clean MySQL-5.1.50 database and they are all 16 columns and
exactly the same as far as I can see.

Is there anyone that might know why I am getting this?

I would really apprecaite any help as this machine needs to be
up and running by eod today.

Regards
Machiel

--=-/TOqFS8d+5KfHkwOoZt6--

Re: MySQL restore failing

In mysql 5.1, mysql.proc table contains 20 columns. Can you check your new =
DB =0Aagain? -Partha ----- Original Message ----=0AFrom: Ma=
chiel Richards =0ATo: mysql mailing list ysql.com>=0ASent: Fri, 3 December, 2010 1:38:24 PM=0ASubject: MySQL restore=
failing Hi All I am hoping that someone could assist me =
with this one. I am trying to restore a database to a new mach=
ine for testing=0Apurposes. The current machine is running mys=
ql-5.0.72sp1 and the new=0Amachine 5.1.50. When running the re=
store is runs for quite a while and then=0Acrashes with the following messa=
ge: [root@mysql2 home]# mysql -u root -p=
<=0A mysql_full_021210.dump=0A =
Enter password: =0A ERROR 1547 (HY000) at line 14=
284: Column count=0A of mysql.proc is wrong. Expecte=
d 20, found 16.=0A The table is probably corrupted=
=0A I have checked the current production system for this tabl=
e and=0Athe column count is 16, I then checked the new system as well as a =
newly=0Asetup clean MySQL-5.1.50 database and they are all 16 columns and=
=0Aexactly the same as far as I can see. Is there anyone that =
might know why I am getting this? I would really apprecaite an=
y help as this machine needs to be=0Aup and running by eod today. Rega=
rds=0AMachiel=0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL restore failing

--=-VkGRgJk0ru9lvW8gQPOy
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

I checked now and saw that they have already attempted a restore
previously and hence the original table was dropped and recreated.

I found some links on the internet stating that after restoring the
dump file to the new version, the proc table should be dumped using the
new version and reloaded.

After this a mysql_upgrade needs to be run.

I am however not sure how valid this is or whether this is
really a solution.

I am able to drop the database and reload the mysql to get the
original proc table again, however how would I fix the column problem
then?

The current proc tables have 16 columns.


Any ideas?

Regards
Machiel

-----Original Message-----
From: partha sarathy
To: Machiel Richards , mysql mailing list

Subject: Re: MySQL restore failing
Date: Fri, 3 Dec 2010 14:02:59 +0530 (IST)


In mysql 5.1, mysql.proc table contains 20 columns. Can you check your new DB
again?

-Partha



----- Original Message ----
From: Machiel Richards
To: mysql mailing list
Sent: Fri, 3 December, 2010 1:38:24 PM
Subject: MySQL restore failing

Hi All

I am hoping that someone could assist me with this one.

I am trying to restore a database to a new machine for testing
purposes.

The current machine is running mysql-5.0.72sp1 and the new
machine 5.1.50.

When running the restore is runs for quite a while and then
crashes with the following message:



[root@mysql2 home]# mysql -u root -p <
mysql_full_021210.dump
Enter password:
ERROR 1547 (HY000) at line 14284: Column count
of mysql.proc is wrong. Expected 20, found 16.
The table is probably corrupted


I have checked the current production system for this table and
the column count is 16, I then checked the new system as well as a newly
setup clean MySQL-5.1.50 database and they are all 16 columns and
exactly the same as far as I can see.

Is there anyone that might know why I am getting this?

I would really apprecaite any help as this machine needs to be
up and running by eod today.

Regards
Machiel


--=-VkGRgJk0ru9lvW8gQPOy--

Re: MySQL restore failing

On 12/3/2010 03:40, Machiel Richards wrote:
> I checked now and saw that they have already attempted a restore
> previously and hence the original table was dropped and recreated.
>
> I found some links on the internet stating that after restoring the
> dump file to the new version, the proc table should be dumped using the
> new version and reloaded.
> ... snip ...
> Any ideas?


The easiest way to migrate between major versions is to dump logical
contents of the system data tables (the entire MySQL database)
separately from the rest of the data. It changes very slowly so there is
no risk of being out of sync with the rest of the data.


For example, instead of dumping the user tables (user, db, privs-table,
etc...) and restoring them as raw data on the new system, you should get
the SHOW GRANTS reports for each of your users.

http://dev.mysql.com/doc/refman/5.1/en/show-grants.html

Instead of dumping the raw data in the `proc` table, use the --routines
option of mysqldump instead to write out the stored procedures as SQL
statements.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option _mysqldump_routines

Did you remember to dump your triggers (which are database-specific)
using the --events option so that they were recreated with your
production data tables?

The tables in the `mysql` database can and usually do change sizes and
definitions between major versions. The utility mysql_upgrade will
modify the table definitions to match the current version after you
restore your old-version tables but if you want to try to avoid that
step, you can use my techniques.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL restore failing

--0023547c9be1a091a70497f7e97c
Content-Type: text/plain; charset=UTF-8

Hi, I think you didn't run mysql_upgrade script.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0608@gmail.com



2010/12/3 Shawn Green (MySQL)

> On 12/3/2010 03:40, Machiel Richards wrote:
>
>> I checked now and saw that they have already attempted a restore
>> previously and hence the original table was dropped and recreated.
>>
>> I found some links on the internet stating that after restoring the
>> dump file to the new version, the proc table should be dumped using the
>> new version and reloaded.
>> ... snip ...
>> Any ideas?
>>
>
>
> The easiest way to migrate between major versions is to dump logical
> contents of the system data tables (the entire MySQL database) separately
> from the rest of the data. It changes very slowly so there is no risk of
> being out of sync with the rest of the data.
>
>
> For example, instead of dumping the user tables (user, db, privs-table,
> etc...) and restoring them as raw data on the new system, you should get the
> SHOW GRANTS reports for each of your users.
>
> http://dev.mysql.com/doc/refman/5.1/en/show-grants.html
>
> Instead of dumping the raw data in the `proc` table, use the --routines
> option of mysqldump instead to write out the stored procedures as SQL
> statements.
>
>
> http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option _mysqldump_routines
>
> Did you remember to dump your triggers (which are database-specific) using
> the --events option so that they were recreated with your production data
> tables?
>
> The tables in the `mysql` database can and usually do change sizes and
> definitions between major versions. The utility mysql_upgrade will modify
> the table definitions to match the current version after you restore your
> old-version tables but if you want to try to avoid that step, you can use my
> techniques.
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0608@gmail.com
>
>

--0023547c9be1a091a70497f7e97c--