[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MYSQL Error 1045 - Access Denied, log sequence number ibdata files does not match

Posted on 2013-05-23
11
Medium Priority
?
1,140 Views
Last Modified: 2013-05-29
After doing a Windows update, our server required a restart.  We now cannot connect to the MYSQL database.  Here is the log file:

130523  9:21:44 [Warning] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Forcing close of thread 48395  user: 'root'

130523  9:21:44  InnoDB: Starting shutdown...
130523  9:24:32 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130523  9:24:34  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...
InnoDB: Last MySQL binlog file position 0 146162675, file name .\mysql-bin.000131
130523  9:24:35  InnoDB: Started; log sequence number 18 4204824874
130523  9:24:35 [Note] Event Scheduler: Loaded 0 events
130523  9:24:35 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.43-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
130523 10:03:04 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Normal shutdown

130523 10:03:04 [Note] Event Scheduler: Purging the queue. 0 events
130523 10:03:05  InnoDB: Starting shutdown...
130523 10:03:05  InnoDB: Shutdown completed; log sequence number 18 4204824874
130523 10:03:05 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete

130523 10:03:10 [Note] Plugin 'FEDERATED' is disabled.
130523 10:03:11  InnoDB: Started; log sequence number 18 4204824874
130523 10:03:11 [Note] Event Scheduler: Loaded 0 events
130523 10:03:11 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.43-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

Open in new window


Any ideas where I should start?
0
Comment
Question by:DanielAttard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39191955
According to this log, everything is fine.  I can see your server starts up at 9:24 and runs fine until you shut it down at 10:03 (which was actually a *restart* since the server comes right back up again).

What error are you getting when you try to connect to the database?
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39191981
Oh wait - I see it in your title (duh).  The suggestion here:

http://forums.mysql.com/read.php?22,296026,296188#msg-296188

Is to shut down your server and delete (I would copy, backup, etc. - ie *move* them, don't delete them) your INNODB log files.  Upon restart, they should re-build.
0
 

Author Comment

by:DanielAttard
ID: 39191992
Thanks for the comment.  This might be a silly question, but what are the INNODB log files?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 2000 total points
ID: 39192013
INNODB is a *transactional* database system.  Meaning if I issue these SQL commands:

BEGIN TRAN;
DELETE * FROM my_important_table;
ROLLBACK;

Open in new window


Then .. *nothing* happens to my database in the end.  The INNODB LOG files contain changes to the database that may or may not have been "COMMIT"ted to the database yet.  


I guess I should ask at this point... you *are* backing up your databases as well, aren't you?  You also back up your server before you apply a system update, don't you?
0
 

Author Comment

by:DanielAttard
ID: 39192026
Yes, we do backup our databases, although we did not backup our server before applying the latest Windows system update.  I guess we should have done that.  

So where exactly are those INNODB LOG files that I need to move?  Thx.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39192050
Probably in:

C:\Program Files\MySQL\MySQL Server 5.1\data
0
 

Author Comment

by:DanielAttard
ID: 39192087
Do you mean the files named:

ib_logfile0
ib_logfile1

There are a number of files named:

mysql-bin.000129

Do we leave the "bin" files in place?
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 39192127
Yup - the ib_logfile[01] files.

I just tested this on my server (shut MySQL down, moved the log files, and re-started) and it correctly re-created the log files.  I'm running 5.6, which improved the speed of this process, so it may take awhile longer for yours to come up, but you should be able to do so somewhat worry-free. ;-)
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39192244
Oh and yes - leave the bin files alone.  Just delete the logfiles.
0
 

Author Comment

by:DanielAttard
ID: 39192266
We stopped the MYSQL service, then removed the logfiles, then restarted the MYSQL service.  

No new logfiles seem to be created.

We seem to be getting a different error now:

130523 15:32:44  InnoDB: Error: page 467418 log sequence number 17 2823632086
InnoDB: is in the future! Current system log sequence number 7 935757883.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: for more information.

Open in new window

0
 
LVL 23

Expert Comment

by:nemws1
ID: 39192323
That link is bad.  You want this instead:

http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

The comments suggest forcing recover mode to 1, dumping the DB to a file (not a bad idea in any case), and then removing that value.  Worth a try.
0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question