?
Solved

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

Posted on 2013-05-23
11
Medium Priority
?
1,188 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

807 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