Solved

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

Posted on 2013-05-23
11
1,066 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 500 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 500 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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
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…
The viewer will learn how to dynamically set the form action using jQuery.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

707 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