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?
sharingsunshineAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gheistCommented:
Please elaborate more on your environment:
1) what linux distribution
2) full mysqld version
3) FULL SQL ERROR TEXT
nociSoftware EngineerCommented:
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.
Steve BinkCommented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

sharingsunshineAuthor Commented:
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.
sharingsunshineAuthor Commented:
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.
sharingsunshineAuthor Commented:
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.
Steve BinkCommented:
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

sharingsunshineAuthor Commented:
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?
sharingsunshineAuthor Commented:
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?
Steve BinkCommented:
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.
sharingsunshineAuthor Commented:
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,
Steve BinkCommented:
It has been my experience that phpMyAdmin is not a good tool to use.  I recommend doing work from within the MySQL client.

As with any other SQL statements, the ALTER TABLE statement will act on a table within the currently selected database by default.  You can, of course, specify the database in the statement, e.g., "ALTER TABLE myDatabase.myTable ..."  Lacking that qualifier, though, MySQL will assume the current database.  

Here is an example of changing the increment value:
mysql> create database myDatabase;
Query OK, 1 row affected (0.00 sec)

mysql> use myDatabase;
Database changed
mysql> create table myTable (pk INT AUTO_INCREMENT PRIMARY KEY, fld VARCHAR(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into myTable (fld) VALUES ('a'),('b'),('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from myTable;
+----+------+
| pk | fld  |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql> alter table myTable AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into myTable (fld) VALUES ('d'),('e'),('f');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from myTable;
+----+------+
| pk | fld  |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
| 10 | d    |
| 11 | e    |
| 12 | f    |
+----+------+
6 rows in set (0.00 sec)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sharingsunshineAuthor Commented:
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
Steve BinkCommented:
That's awesome.  A nice quick fix.  Good luck!
sharingsunshineAuthor Commented:
Thanks, I appreciate that.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.