?
Solved

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

Posted on 2013-05-23
11
Medium Priority
?
1,101 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
Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
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…

771 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