Re: Upgrading databases?
Hi Seth - I believe MySQL's official position is that you should always dump-and-load the data when upgrading major or minor versions (4.0 to 4.1, 4.1 to 5.0, etc.) I've done it both ways (dump-load and just moving table files) and have never had a problem with either, even when moving files across OS platforms *knock on wood*. That said, I think you might find dump-and-load just the ticket to work around the problem you're having. What you're doing *should* work, but since it isn't, I'd try another avenue myself to avoid spending much more time on it. Something as simple as mysqldump -u root -ppassword -h hostwith41 --all-databases | mysql -u root -ppassword -h hostwith51 would do it, if you want to transfer everything Mind that you've got an appropriate network connection - you wouldn't want to make your laptop on home wireless with DSL the in-between if you have 50GB of data to transfer. Hope this helps, and let me know if you have any questions. Dan On 6/21/07, Seth Seeger <[EMAIL PROTECTED]> wrote: On Jun 21, 2007, at 12:21 PM, Gerald L. Clark wrote: > Seth Seeger wrote: >> Hello, >> I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- >> beta. Both are FreeBSD i386-based machines. I have run the >> following commands: >> mysqlcheck --check-upgrade --all-databases --auto-repair >> mysql_fix_privilege_tables >> Both executed with no problems. (mysqlcheck reported "OK" for >> all tables.) When I try to access any of the tables, I get this: >> mysql> select * from users; >> ERROR 1034 (HY000): Incorrect key file for table 'users'; try to >> repair it >> So I tried to repair it: >> mysql> repair table users; >> +++-- >> ++ >> | Table | Op | Msg_type | >> Msg_text | >> +++-- >> ++ >> | seth_icsx_mands_live.users | repair | error| Incorrect key >> file for table 'users'; try to repair it | >> +++-- >> ++ >> 1 row in set, 1 warning (0.10 sec) >> Running "repair table users" doesn't seem to have any effect on >> it because the problem persists. I have tried to run >> mysql_upgrade, with no success: >> # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose >> Looking for 'mysql' in: mysql >> FATAL ERROR: Can't find 'mysql' >> I have tried it with all different combinations for the two >> directory options with no luck. All tables are MyISAM. >> Can anyone shed some light on what I'm supposed to do? >> Thanks, >> Seth > Shut the server down and run myisamchk on users.MYI Sadly, no success. I tried running it two different ways: # myisamchk -e -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 Found block with too small length at 101420; Skipped # myisamchk -c -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 I still get the same error when I try to access the table. Thanks, Seth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading databases?
On Jun 21, 2007, at 12:21 PM, Gerald L. Clark wrote: Seth Seeger wrote: Hello, I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- beta. Both are FreeBSD i386-based machines. I have run the following commands: mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables Both executed with no problems. (mysqlcheck reported "OK" for all tables.) When I try to access any of the tables, I get this: mysql> select * from users; ERROR 1034 (HY000): Incorrect key file for table 'users'; try to repair it So I tried to repair it: mysql> repair table users; +++-- ++ | Table | Op | Msg_type | Msg_text | +++-- ++ | seth_icsx_mands_live.users | repair | error| Incorrect key file for table 'users'; try to repair it | +++-- ++ 1 row in set, 1 warning (0.10 sec) Running "repair table users" doesn't seem to have any effect on it because the problem persists. I have tried to run mysql_upgrade, with no success: # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose Looking for 'mysql' in: mysql FATAL ERROR: Can't find 'mysql' I have tried it with all different combinations for the two directory options with no luck. All tables are MyISAM. Can anyone shed some light on what I'm supposed to do? Thanks, Seth Shut the server down and run myisamchk on users.MYI Sadly, no success. I tried running it two different ways: # myisamchk -e -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 Found block with too small length at 101420; Skipped # myisamchk -c -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 I still get the same error when I try to access the table. Thanks, Seth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading databases?
Seth Seeger wrote: Hello, I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- beta. Both are FreeBSD i386-based machines. I have run the following commands: mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables Both executed with no problems. (mysqlcheck reported "OK" for all tables.) When I try to access any of the tables, I get this: mysql> select * from users; ERROR 1034 (HY000): Incorrect key file for table 'users'; try to repair it So I tried to repair it: mysql> repair table users; +++-- ++ | Table | Op | Msg_type | Msg_text | +++-- ++ | seth_icsx_mands_live.users | repair | error| Incorrect key file for table 'users'; try to repair it | +++-- ++ 1 row in set, 1 warning (0.10 sec) Running "repair table users" doesn't seem to have any effect on it because the problem persists. I have tried to run mysql_upgrade, with no success: # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose Looking for 'mysql' in: mysql FATAL ERROR: Can't find 'mysql' I have tried it with all different combinations for the two directory options with no luck. All tables are MyISAM. Can anyone shed some light on what I'm supposed to do? Thanks, Seth Shut the server down and run myisamchk on users.MYI -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]