Indexes disabled after a restore

**MSD-Professional** Joined: 29 May 2009 Posts: 77

I think this is more of a but than a problem I encountered. After I restore a database, all the indexes are disabled. I can manually enable them again but maybe other users can't or even didn't notice.

Posted: 2011-08-22, 07:57 (No subject)

Indexes are only disabled when you've set the config option "IGNORE ENABLE KEYS" to "Yes".
If you did so, it is the job of the user to activate them manually, after the restore is done. Wink

So it is not generally the case that indexes are disabled. If the mentioned option is set to no (default value) indexes are enabled.
Nevertheless: for very big tables it can take quite a long time to build the index which can reach the timelimit and could break the restoring process.
So this option is a workaround to bypass that special case.
The price is that you'll have to activate the indexes manually after restoring.

**MSD-Professional** Joined: 29 May 2009 Posts: 77

Thanks for the answer but ... if I look at the configuration I see: Ignore "ENABLE KEYS": yes no (and the no is checked)

Posted: 2011-08-22, 10:28 (No subject)

In that case the backup file was not created by MySQLDumper and the commands to enable them are missing in that file.

**MSD-Professional** Joined: 29 May 2009 Posts: 77

Nope, the backup was created by MySQLDumper (4 times per day) and I did something stupid and had to restore the backup. Still made by MySQLDumper luckily just an hour before I made the stupid mistake.

Posted: 2011-08-22, 11:38 (No subject)

Well, this can't be. Wink

MySQLDumper adds "ALTER TABLE xxx ENABLE KEYS"-Commands to the backup file. And if you didn't set the option "IGNORE ENABLE KEYS" to "YES", they are executed while restoring. Maybe this setting was set to "yes" while you did the restore?

**MSD-Professional** Joined: 29 May 2009 Posts: 77

Again, nope. I didn't change anything. It happened already a few times but I couldn't figure out how the indexes were disabled, so I decided to mention it here. I also restore on another server to where the backups are ftp-ed to and there the indexes aren't disabled. I even upgraded MySQLDumper to see if that would solve the problem but it doesn't.

If I have to restore I go immediately to the restore menu and restore and to my knowledge I have never changed the settings for Ignore "ENABLE KEYS" I am downloading now one of the backup files too see what's inside but that might take a while, since they are pretty big.

**MSD-Professional** Joined: 29 May 2009 Posts: 77

Alright, since it's a huge file, I managed to open it. So what am I looking for? I searched for "ABLE KEYS" (disable or enable) but couldn't find anything. After searching for ALTER TABLE, I found the following

/*!40000 ALTER TABLE `ij1n_updatelog` DISABLE KEYS */;
INSERT INTO `ij1n_updatelog` (`ul_key`,`ul_value`) VALUES ('convert transcache field',NULL);
<SNIP>
/*!40000 ALTER TABLE `ij1n_updatelog` ENABLE KEYS */;

So obviously it is supposed to be enabled but it isn't.

**MSD-Professional** Joined: 29 May 2009 Posts: 77

I just checked and indeed. In all the tables "around" the insert statements there are
/*!40000 ALTER TABLE `whatever_table` DISABLE KEYS */;
INSERT INTO `whatever_table` (etc)
/*!40000 ALTER TABLE `whatever_table` ENABLE KEYS */;

Posted: 2011-08-22, 19:05 (No subject)

So now you understand that I cannot reproduce what you said. Wink

If you didn't change the mentioned option, the indexes are enabled and therefor build.

Posted: 2011-08-22, 19:40 (No subject)

You are running a MySQL-Server version >= 4.0?

**MSD-Professional** Joined: 29 May 2009 Posts: 77

Yes I am.

MySQL

Server: Localhost via UNIX socket
Server version: 5.1.57-log
Protocol version: 10
User: gebruikers@localhost
MySQL charset: UTF-8 Unicode (utf8)

Web server

sw-cp-server/1.0.0
MySQL client version: 5.0.77
PHP extension: mysql

phpMyAdmin

Version information: 3.3.3

Posted: 2011-08-23, 17:08 (No subject)

ok, any server >= version 4 will interprete and execute the ENABLE/DISABLE commands.
So I still can not reproduce what you are saying. The indexes and keys must be enabled.

**MSD-Professional** Joined: 29 May 2009 Posts: 77

Thanks a lot for the answer but unfortunately they aren't. The commands are there, but they aren't executed. It didn't happen before and I don't know when it started but I think I discovered it after the last upgrade. Might it be that it happens because the database is so big?

Greetings

Posted: 2011-08-23, 17:21 (No subject)

Maybe your MySQL user doesn't have the right to execute "ALTER"?