Solved

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

Posted on 2013-05-23
11
1,044 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
  • 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now