Upgrade Issues

[jajtiii]

I have basically done a stupid thing, primarily due to laziness.

 

Overview: I am pretty sure that I was running something around 5.1.41 on my laptop. I have tables in a database that I was building for deploying to a production system when I determined the app was as 'bug free' as I was able to stand.

 

In a stroke of genius, I got sidetracked about a week ago dealing with a separate issue when moving data from the dev db (on my laptop) to the prod db (on a hosted solution.) I ran into a weird error and simply figured it was due to a difference in the Mysql versions on my laptop, vs the hosted platform.

 

So, I upgraded... My guess is that no one on this forum would call 'what I did' as an upgrade... I downloaded something like 5.1.6 and tried to install it. My laptop choked on that, because another Mysql was already installed. So, I coped the /data folder to a safe spot, uninstalled the old version and installed the newer version. I then copied my dB's from the old data folder into the new data folder...

 

I didn't back anything up. I'm in IT. I recommend other folks to back up stuff. I don't....sheesh

 

So, things seemed to be ok, but I realized today that I'm missing some of my tables (maybe the innob). phpMysqlAdmin actually will say in the dB selector (in parenthesis) that I have 87 tables - but, it only shows 35.... As you might imagine, I need all 87...

 

So, now I am in a quandary. Should I uninstall the recent one and take a guess at what version I used to have and reinstall? Is there (fingers crossed) a Mysql tool that can help me fix my broken tables and not have to uninstall/reinstall?

 

I am very aware that I really made a lot of mistakes here (after getting around to reading the upgrade documentation, after the fact, I have come to the conclusion that the only thing that I did that was similar to what you're supposed to do, is download the new version...). Just wondering if there is any way to get out of this mess.

 

thanks for your consideration.

 

Jones

[fenway]

InnoDB? MyISAM? Both?

[jajtiii]

Innob only, as far as I can tell.

[fenway]

What's showing you 87 vs 35? What does "SHOW TABLES" produce?  Single database?

[jajtiii]

phpAdmin shows me the different numbers. In parenthesis, it will say a number by the dB that indicates the number of tables, but when I click on the dB, all I can see are the myisam tables.

 

Show Tables on one of the affected dB's says 87 rows and lists out all of the tables (including the innob ones that I noticed were missing, since my app could no longer find them.)

 

This affected all dB's on my local dev laptop, but only one was critical (although there is a second that is also sort of important.)

 

If I try to actually CREATE one of the missing tables, using the structure that I had from the Prod system. It wouldn't let me create it because 'it already existed'! Heh. But, if you try to select from it, it says it doesn't exist. I even tried to delete one of the unimportant tables that had been affected, but it wouldn't let me do it.

[fenway]

I don't know what phpmyadmin is doing -- what about "Show table status"?

[jajtiii]

Well, Show Table Status actually does not spit out the Innob tables, oddly enough. It only shows the status on the 35 or so myisam tables.

 

But, as mentioned above, Show Tables lists out both myisam and innob, coming to a total of 87 tables.

[fenway]

Are you sure innodb is actually running?

[jajtiii]

I am not sure if this answers your question, but I have created and successfully used innodb tables since this problem in another database that runs a different version of the application. I just tested a few queries to make sure I wasn't losing my mind. I built this database from a sql dump from the production db. Those innodb tables seem to be working fine, knock on wood.

[fenway]

What makes you sure that they were created as innodb tables?

[jajtiii]

I suppose I am not 100% sure.

 

I selected innodb when I created them with phpmyadmin and they show up as innodb when I do show table status on that database. Is there any other way to check?

[fenway]

Didn't you say that SHOW TABLE STATUS doesn't show them?

[jajtiii]

The mysql install has about 18 databases. The majority are for a content management engine that I built for a customer some time ago, in various stages of development or testing.

 

After the upgrade, all innodb tables in all 18 database disappeared (they show up under show tables, but not show table status and you cannot get data from them.)

 

Post upgrade, I had several projects that were not related to the lost innodb tables, so I exported the production database and created a new test db in my upgraded install to use for the time being.

 

So, all original databases have no innodb tables. The one db that I created after the 'upgrade' does have functioning innodb tables in it.

[fenway]

If "SHOW TABLE STATUS" doesn't show any innodb tables, then you don't have any.  Does your my.cnf have "skip-innodb"?

[jajtiii]

I still have the 'data' directory from the original version (I copied this to a different location before the upgrade), which definitely has Innodb tables. I am aware that, post-upgrade, I cannot access the tables. But, although they may not exist (per your assessment), I cannot create them again (mysql kicks out an error, telling me the table already exists).

 

Due to the value of the data in those tables, I am willing to do just about anything to get them back. I have a suspicion that I may have accidentally stopped Apache when I meant to stop the mysql service when I performed my original action. Regardless, if I remembered what version I used to have, I would reinstall it and try it, but I do not know.

 

Here are snippets with 'innodb' from config:

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

#*** INNODB Specific options ***

# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=3M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=2M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=106M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=53M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=8

[fenway]

And you're certain that the innodb data dir is pointing to the correct place?

[jajtiii]

I'm not sure about this at all.

 

I can say that the data directory holds all of the databases, including the one with the functioning innodb tables. The log file ibdata1 is also in this directory and getting updated (according to timestamp).

 

Is there some other way I could check this?

[fenway]

I'm just wondering if somehow you have 2 datadirs, one of which isn't being referenced by the my.cnf file.

[jajtiii]

Gotcha.

 

During my initial troubleshooting, I considered this possibility and first renamed the original data directory (which I had already moved to a storage location on my computer, away from the original path) and then just moved it to a server on my home directory.

 

I have also confirmed that my install is using the my.ini that I have tweaked to resolve this issue.

 

I appreciate your willingness to offer advice on my issue and I regret not having shared the following with you before now (I forgot about this - an error that I tried to run down prior to comign to these forums, to no avail, so I ended up forgetting about it...)

 

This is in my error log - maybe you can make more of it than I could:

 

120702 23:08:06  InnoDB: Error: page 234 log sequence number 0 5957144

InnoDB: is in the future! Current system log sequence number 0 3789003.

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.

[fenway]

Well, yes, that's significant -- did you follow the instructions in the link provided?

[jajtiii]

Yes and it did not help. After a few unsuccessful tries, I came here to see if there was another way to get around my problem.

 

Since I am still unable to select the data to dump it, I was thinking (hoping) that this error message was the only thing MySQL had for a wide scope of problems. So, I came here.

[fenway]

So no matter what you get for innodb_recovery, you get the same messge in the log? can't be.

[jajtiii]

Sigh.

 

I have run through all scenarios with the config set between 1 and 6, which is apparently not recommended. But, when the others didn't work, we figured that we had nothing to lose.

 

At this point, I need to be a little bit more direct and I am sorry if this comes off as rude. I no longer need dart board responses. I need an expert. I manage a large team of ms sql and oracle dba's. This one database is a small thing and is on a daggone workstation - not a farm or server that is backed up (I rue the day that I went down this path, to be sure). It was just a test case. Although I am not a dba, I know enough about managing enterprise issues that error messages do not always point directly to the problem. I need someone who fully understands this error message.

 

It appears that trying to resolve this through this forum is not going to work. I can cut a PO to have someone resolve it, so  I would be interested in possible consultants or experts that understand MySQL at a very deep level. I need this data back.

 

Please message me if you are interested.

[fenway]

You're right -- then this board really isn't for you.  But that error message is quite clear -- and what likely happened is that without the log, it decided to rollback, or otherwise remove, any data that it couldn't account for in the log.