Link to home
Start Free TrialLog in
Avatar of sharingsunshine
sharingsunshineFlag for United States of America

asked on

InnoDB Problem With MySQL

I have a message in my mysqld.log
MySQL and InnoDB data dictionaries are out of sync

I have Server version: 5.5.45 - MySQL Community Server (GPL) installed and Apache 2.2.31

This became obvious trying to run a pdo update and got the message the db can't be updated.  What can I do to fix this problem?

This seems to be the only DB that is affected and I was told if I wanted to switch to file-per-table I could but until then the DB's would work normally.

Can anyone help me with this?
Avatar of gheist
gheist
Flag of Belgium image

Please elaborate more on your environment:
1) what linux distribution
2) full mysqld version
3) FULL SQL ERROR TEXT
Avatar of noci
noci

was mysql  updated (since prior pdo update).
if so maybe you should run:  mysqlrepair --check-upgrade

mysqlrepair --analyze might be a wise choice as well.
This looks like some good information for you:  https://forums.cpanel.net/threads/innodb-corruption-repair-guide.418722/

Try to identify the actual reason MySQL is reporting out of sync.  Which table?  In what way is it out of sync?  The repair will depend on the actual problem.  The guide above has some of the first steps you want to take, based on what you find.
Avatar of sharingsunshine

ASKER

Sorry for the delay in getting back but trying to get more details as to what is going on.

Please elaborate more on your environment:
1) what linux distribution
2) full mysqld version
3) FULL SQL ERROR TEXT

Amazon Linux AMI release 2015.09
 Server information: Linux #1 SMP Wed May 13 20:33:05 UTC 2015 x86_64
Server software version: Apache/2.2.31 (Amazon)

Database information
MySQL version: 5.5.45
MySQL engine: InnoDB

151005 16:16:04  InnoDB: MySQL and InnoDB data dictionaries are out of sync.
InnoDB: Unable to find the AUTOINC column id in the InnoDB table theherbs_chores/home.
InnoDB: We set the next AUTOINC column value to 0,
InnoDB: in effect disabling the AUTOINC next value generation.
InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE
InnoDB: or fix the data dictionary by recreating the table.
was mysql  updated (since prior pdo update).
if so maybe you should run:  mysqlrepair --check-upgrade

mysqlrepair --analyze might be a wise choice as well.

mysql was updated before the pdo update attempt.

It appears that InnoDB can't utilize the repair or optimize option.
This looks like some good information for you:  https://forums.cpanel.net/threads/innodb-corruption-repair-guide.418722/

Try to identify the actual reason MySQL is reporting out of sync.  Which table?  In what way is it out of sync?  The repair will depend on the actual problem.  The guide above has some of the first steps you want to take, based on what you find.

this does look promising.  The reason this may have happened because I ran out of disk space and mysql crashed.  So we zipped up a large log file that I didn't need.  Then deleted it and then started mysql back up.

I ran mysqlcheck [database name] and it never asks for my password like normal and gives me this output.  This is the syntax I used.  
mysql -u[username] -p mysqlcheck [database name];

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
auto-rehash                       TRUE
auto-vertical-output              FALSE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
database                          (No default value)
default-character-set             auto
delimiter                         ;
enable-cleartext-plugin           FALSE
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
ignore-spaces                     FALSE
init-command                      (No default value)
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              0
prompt                            mysql>
quick                             FALSE
raw                               FALSE
reconnect                         TRUE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
table                             FALSE
user                              root
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect-timeout                   0
max-allowed-packet                16777216
net-buffer-length                 16384
select-limit                      1000
max-join-size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE
plugin-dir                        (No default value)
default-auth                      (No default value)

If we can get this to run properly I believe it will tell me what is wrong.
As it happens, the text of the error identifies the table:

>>> InnoDB: Unable to find the AUTOINC column id in the InnoDB table theherbs_chores/home.

... and tells you what to do:

>>> InnoDB: We set the next AUTOINC column value to 0,
>>> InnoDB: in effect disabling the AUTOINC next value generation.
>>> InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE
>>> InnoDB: or fix the data dictionary by recreating the table.

I doubt mysqlcheck or mysqlrepair are going to help you.  They may even make the problem worse until you resolve the issue with the autoincrement column.  The mysqlcheck application is for the data files, not the dictionaries, and it trying to "correct" data with an invalid dictionary may cause harm.

You will need to analyze the table mentioned in the error, and reset the field manually.  Alternatively, you can recreate the table in whole.  Be aware that if this incremented field acts as a foreign key for any  other table, recreating the table could be painful.

To set the NEXT autoincrement value on a table:
/* The next record inserted will have this increment value */
ALTER TABLE MyTable AUTO_INCREMENT=1;

Open in new window

To set the NEXT autoincrement value on a table:

/* The next record inserted will have this increment value */
ALTER TABLE MyTable AUTO_INCREMENT=1;

                                         

This is the id that is used to find, update and delete the record with the matching id.  So are you saying to run the above command the id value will set back to 1?  Thus now providing a duplicate id number.

If so, that won't work.  through phpMyADmin I can see all of the records.

I dumped the db to my test site and it worked fine just by importing the DB.  However, it does have a  different ibdata1 file since there are only a few databases on the test site.

Given this information which way should I go?
is there anyway to use the original ibdata1 file on the new test site to try out what the alter command will do to the db?
You can set the AUTO_INCREMENT value to whatever you need.  I was merely demonstrating how one would set it.

The problem is that the table structure that MySQL sees generally does not match the table structure the InnoDB engine is using inside ibdata1.  Those two views need to match to guarantee integrity.  So, your options are 1) try to sync the dictionaries manually (i.e., set the AUTO_INCREMENT), or 2) rebuild the table from scratch so they match properly.
trying the option of rebuilding the table since it only has 9 columns then I was going to import the data.  However, it has been 20 minutes since I pushed go on phpMyAdmin to create the table.

do you have another suggestions? If you still think the syncing will work please give more explicit directions.  For example, Is this something done from the client?  Your syntax isn't specifying which DB so please supply the syntax.

Thanks,
ASKER CERTIFIED SOLUTION
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I used the client and recreated the table and reloaded the table and now it works fine.  Thanks for the expertise and help.  Here is a link that details what I did.


http://serverfault.com/questions/495990/mysql-timeout-for-1-database-others-are-ok-mysql-and-innodb-data-dictionaries
That's awesome.  A nice quick fix.  Good luck!
Thanks, I appreciate that.