Site5 - Built For Designers & Developers MENU

MySQL corruption problem – update auto-increment for whole table

[2 Grey Star Level]


I have a problem with a database. Well, there are lots of problems with this database but I am struggling to make it function at the moment without errors.

The problem is that when the tables were designed, the developer did not make the right fields Primary or unique. This means that on a particular table, with members of an organisation, a field contains many duplicate values. This in turn leads to problems because some of the PHP relies on this field to update information.

The table in question appears not to have a primary key at all, it does specify unique for a different field (which should have been designated the Primary Key at the design stage, but which wasn’t)

There are many things wrong with this database, enum fields etc, but I am trying to resolve this critical problem at the moment.

The table is in a real mess with duplicates of on field causing problems. Is there any way I can do the following easily.

Answer #1


That sounds like quite a situation! I’m not sure how much we’ll be able to do, but we can certainly take a look for you. This is not really something we can do in this format, though, and I would recommend that you contact our support team with a ticket. You can enter this from Backstage, under Support, and our team will be more than happy to take a look and offer some advice.

Answers Answered By: jdavey [359 Blue Star Level]

Answer #2

But in case your corruption can’t be overcome, then I would suggest you more powerful instrument for such cases Recovery Toolbox for MySQL

Repairing tables

In most cases, only the index will be corrupted (the index is a separate, smaller, file with records that point to the main data file) – actual data corruption is extremely rare. Fixing most forms of corruption is relatively easy. As with checking, there are three ways to repair tables. These all only work with MyISAM tables – to repair corruption of the other table types, you will need to restore from backup:

• The REPAIR TABLE SQL statement (obviously the server must be running for this)
• The mysqlcheck command-line utility (the server can be running)
• The myisamchk command-line utility (the server must be down, or the tables inactive)
Repairing a table requires twice as much disk space as the original table (a copy of the data is made), so make sure you are not going to run out of disk space before you start.

Answers Answered By: sultantrum [2 Grey Star Level]

Answer this Question

You must be Logged In to post an Answer.

Not a member yet? Sign Up Now »

Money Back Guarantees
Click to verify BBB accreditation and to see a BBB report.