Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I got a SQLdump from my database and now I want to import it in to an other mysql server with same version(5.5.22-0ubuntu1) in ubuntu 12.04 .but at the middle of importing, it said:

ERROR 1034 (HY000) at line 3502: Key 2 - Found wrong stored record at 232882088

I do it again and I got another error

ERROR 1034 (HY000) at line 3502: Wrong aligned block at 11847827

I use default parameter to create SQL dump This is my my.conf: [client] port = 3306 socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /media/HDD1/comparable/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1

key_buffer              = 16M
max_allowed_packet      = 11600M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit       = 1M
query_cache_size        = 16M

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

# chroot = /var/lib/mysql/
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 11600M
net_write_timeout       = 10000
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

start of SQL dumb:

DROP TABLE IF EXISTS `DescIdf`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DescIdf` (
  `word` varchar(255) NOT NULL,
  `idf` float NOT NULL,
  PRIMARY KEY (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

...

Can anyone tell me how can I fix it?

share|improve this question
    
1) What parameters did you use to run the original mysqldump ? 2) Are you loading the mysqldump into the same MySQL Instance you dumped it from ? 3) If you find out the offending table, please post the SHOW CREATE TABLE tblname\G in the question 4) Can you please post your my.cnf into the question ? 5) What version of MySQL did you mysqldump with ? 6) What version of MySQL are you loading the mysqldump into ? –  RolandoMySQLDBA Apr 12 '13 at 15:06
add comment

1 Answer

At first glance, I would say you have some corruption. But, this an import. That scares me. Take your mysqldump and run this:

head -20 SQLDump.sql

Does the dump contain DROP TABLE and CREATE TABLE ? My guess would be no.

Did you change the table structure, i.e.,

  • added columns to the table
  • removed columns from the table
  • changed charset/collation to utf8

after doing that mysqldump ? My guess would be yes.

If neither of these are the case, then your table has some corruption in the .frm.

Try installing MySQL on another DB Server and loading the SQLDump there. If nothing errors out, the mysqldump is fine. This verifies some corruption.

Try dropping the problem table, create it from scratch. Then, load it back. You could run myisamchk against the MyISAM table.

share|improve this answer
    
SQLDump contain DROP TABLE and Create Table and also I create new database in my mysql server so there is no .frm file at the begining –  hoosssein Apr 12 '13 at 13:57
    
I agree...er, I mean +1 ... installing MySQL on another machine and trying to restore the dump file there is a good idea, because this error is almost certainly nothing to do with the dump file and everything to do with something corrupt or some defective hardware on the server where you're attempting to restore. The errors seem like they must be coming from storage/myisam/mi_check.c and nothing in the dump file should be able to cause alignment errors in the storage engine. –  Michael - sqlbot Apr 13 '13 at 0:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.