MYSQL :: Repair Corrupt Tables

Repair Corrupt Table
I am using xoops (a CMS) and MySQL on a website with not so much traffic. I am using "phpMyAdmin - 2.8.0.3-Debian-1" with "MySQL - 5.0.21-Debian_3-log" on a Debian unstable.
Three tables have gotten corrupt recently and won't let themselves be repaired. When I use repair it says status OK, but if I try to delete a record or if I analyze the table again, it says that the table was reported as crashed and that it needed a repair. The only useful message I get, if I analyze the table is about wrong key at page 111616.

Deleting Corrupt Tables
I'm pretty new to MySQL but have a reasonable grasp of relational databases... I created a new database and attempted to import a table structure into it... Now I obviously had something wrong in the import data because I got an error... Sorry didn't write it down.

Now if I attempt to open the database I get the following error #1142 - SELECT command denied to user 'runey'@'localhost' for table 'test'

What I would like to do is delete the faulty tables so I have an empty database, but if I try that then I get this error #1142 - DROP command denied to user 'runey'@'localhost' for table 'test'.

I have asked my ISP to delete the whole database so I can start again, but would like to know if there was something I could have done, just in case it happens again.

Cannot Repair Tables
I lost my developers and an office i do hardware support for is down. I can do the basics, BUT checking and repairing the tables dont seem to work. I need some better direction. Here is a link to a text file that shows ALL of the errors. Can someone please reply with answers or even a direction to go. The is NO backup of the database and the data is vital.

http://members.planetwebdev.com/web/admin/mysql/report.txt

if "holy $#%@%#$" is the only answer you have. Please spare me your comments.

Optimize Or Repair Db Tables
I bought a dating script online and been running it for 1 year, has about 1500 profiles, but the site is too slow now. Not about the host because I have godaddy host.
When I ask the script owner, they told me that I need to optimize the db to make tables indexed.
I already tried to go to phpmyadmin, click on all tables, then, select OPTIMIZE table from drop-down list,.... but it does not help. The site is still running too slow to load. I am using high speed intenet.

Repair Mysql Tables
is there some code to repair all the tables in my database in one go? instead of typing in "Repair Table <TableName>;" many times.

Auto Repair Tables
This is my first post on this board.
Well.. now to my question.
I´m running MySQL 4.1.11 on a linux-mandriva server.
Some days, there are a few tables in on of my databases that gets "corrupted" for some strange reason.
And this always happends, when i´m NOT around to repair the tables.
So.. i was wondering if there is some kind of php-script or program, that could repair the tables automaticly

Corrupt Downloads
I'm trying to download MySQL but no matter which mirror I download the zip file from the file I get doens't work. My computer keeps saying it is "no zip file, bad zip file or part of a split/spanned zip file" What am I doing wrong? And can anyone point me in the direction of a good tutorial on running it locally when I finally get it installed?

Corrupt MySQL DB
I'm in a bit of a conundrum on this one. I have a MySQL 5.0 DB running on CentOS 5 that seems to have corrupted. In PHPMyAdmin, all of the tables show as "in use". When I attempt to repair them, I receive both mysql error #1017 - Can't find file with a path to the directory where it should be, as well as errno 13, which is a permissions error.

In mysqladmin, I can navigate to the DB, show the tables which appear to have data, but if I attempt even a simple SELECT, I receive the same errors.

The mysql docs have information on repairing individual tables, but I haven't seen any references for EVERY table being hosed.

Corrupt BLOB Data
I'm using mySQL to store data in a blob field. This seems to work on some machines, and not on others. What usually end up happening is that the data ends up getting corrupted either while uploading or downloading it.

I am not sure what's wrong. Using Perl to upload and download the data. I am using the latest DBI package.
binary file size = 4168 bytes

Possible errors:
*these errors depend on what machine the code is run on
4168 bytes retrieved, but contains incorrect data
<4168 bytes retrieved
<4168 bytes retrieved
<4168 bytes uploaded
>4168 bytes uploaded

The Perl code works on my unix machine, but not on my win machine
The Perl code works on my co-workers win machine, but not our unix server.

Innodb Corrupt? > Incorrect Information In .frm
I have a database from shared hosting that is corrupted. It is a mysql database. It feds out that the corrupted tables are of innodb type. The hoster didn't make backups, so I really need to repair this database.
I have given some hints to my hoster. It feds out that the hoster has upgraded the mysql-server to 5.0.27. I conclude from my internet research that the repairing of innnodb tables should be quite feasible. I directed my hoster to http://dev.mysql.com/doc/refman/5.0/...-recovery.html, but according to him this doesn't work.
The error message that I see when accessing the corrupted tables looks like

#1033 - Incorrect information in file: './foo/bar.frm'

Corrupt Text After Moving Forum
hello i just moved my vbulletin forum to another server, but the forum is in spanish and after moving all the database the special spanish characters are corrupted in all the forum, for example instead of

Quote:

TamaÃ±o mÃ¡ximo para subir archivos

Quote:

TamaÃƒÂ±o mÃƒÂ¡ximo para subir archivos

the new server is 4.1.20 and the old one is 4.1.14, theres any way i could solve this?

Detecting Corrupt Table Structure?
I think I may have a corrupt table.

Problem:

An INSERT leaves 3 fields (City,State,Country) blank 30% of the time. I added 3 additional columns: myCity, MyState, myCountry to the end of the table and modified the INSERT to put the same information into these fields and they work perfectly, whereas the prior fields are still occasionally blank. I am suspecting a corrupt table structure. MySQL Administrator reports all OK, however.

Environment: Windows Server 2003, MySQL 4.1.9, MyODBC 3.51.10 (not 10-2). Table created originally from Access database and conveted to MySQL MyISAM with "Access2MySQL Sync" product from DMSoft.

Your Database May Be Corrupt Or You May Have Copied The InnoDB
I am seeing the below message in the log file. The only reference I can find about it points to a bug. The link provided confuses me more. Do I have a problem or not is my question.

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.0/en/forcing-recovery.html
InnoDB: for more information.

Can A Mysql Error Corrupt A Password File?
This may not be the appropriate place to ask this, but I'm not sure where else to go.

I created a mailing app for a customer to send out bulk emails -in batches- using phpmailer. Yesterday an email address was encountered that had an apostrophe in it, and mysql stopped the mailing and spit out an error. I received an email from the person sending the mailing to inform me of this. I went into phpmyadmin and fixed the address.

Well, a couple hours later I got a phone call from the client saying that nobody could access their website. They were receiving 'password denied' connection errors from mysql. The solution was to go into the server's control panel and reset the master password for the site.

Has anybody heard of this before? Could an apostrophe in mysql actually corrupt the password file on a cpanel server?

More importantly, the client is blaming me for this problem. I think it was just a coincidence. But, of course, I'm not sure.

How To Repair A Db ?
when I execute from command line (linux centos server)

# mysqlcheck -o chatspa_main

chatspa_main.BAmigMSG
error : Can't find file: 'BAmigMSG.MYI' (errno: 2)
chatspa_main.BuscaAmigos
error : Can't find file: 'BuscaAmigos.MYI' (errno: 2)
chatspa_main.CLKEXclkcontrol OK
chatspa_main.CLKEXmain OK
chatspa_main.CLKEXstats OK
chatspa_main.CTICelebPhotos
error : Can't find file: 'CTICelebPhotos.MYI' (errno: 2)
chatspa_main.CTICelebrities
error : Can't find file: 'CTICelebrities.MYI' (errno: 2)
chatspa_main.ChatSpain_Users OK
chatspa_main.MCmain
error : Can't find file: 'MCmain.MYI' (errno: 2)
chatspa_main.MCvotecontrol
error : Can't find file: 'MCvotecontrol.MYI' (errno: 2)
chatspa_main.phpCardData
error : Can't find file: 'phpCardData.MYI' (errno: 2)
chatspa_main.phpCardUsers
error : Can't find file: 'phpCardUsers.MYI' (errno: 2)
chatspa_main.phpCatData
error : Can't find file: 'phpCatData.MYI' (errno: 2)

How to fix/remove these "Can't find file" errors please ?

Repair Table
Is there a way to issue a "REPAIR TABLE table_name" command to all
tables at once? Something like "REPAIR TABLE *"

Repair By Sorting
I have an application which uses MySQL to manipulate large amounts of data for short periods of time. The data set contains 13 columns each of which is indexed and typically about 30 Million rows.

I create a new table, load the data in to the table using load local infile from a CSV and then add indexes to each of the columns; the columns are a combination of integer, date and text. The text columns have a simple index and a full text index.

I need to be able to sort on each column (in a grid - I use DevExpress) and search on the textual fields.

I appreciate that getting the data into MySQL and creating the indexes is never going to be quick but I need to optimise this process as much as possible. Typically a data set would be loaded and viewed for a few hours and then a new dataset loaded and the process repeated.

When the index process is underway I get the above state displayed by show processlist and wondered if this is a problem. The whole load/index process takes about an hour and I would like to improve on this if I can.

I am also looking for suggestions as to how I may be able to best optimise this.

I use a dedicated server and the MyISAM engine.

Repair Message
Running MySQL 4.0. I had an error in my database that wouldn't let me run selects (it said error 127 from table handler). I ran repair on the table and it fixed it dropping one row. I was just wondering what the meaning of the repair message is so that I can try to avoid the error in the future.

Found link that points at 11548 (outside data file) at 10152.

REPAIR TABLE
Can REPAIR TABLE be used for rebuilding indexes on an InnoDB table? The only documentation I have seen is for MyIsam. There are 30 million records which has taken me 9 days to populate. I've made a backup but want to make sure this will work before I try it.

Keycache Repair
I ran the following querry on a large database:
ALTER TABLE vbpost ADD FULLTEXT INDEX (title,pagetext).
The querry is taking a very long time to execute (the database is also very large). I logged in with another shell and ran showprocess. It showed the querry in state keycache repair.
Do I need to do anything at this point? Is there a way to tell if the querry is still running?

REPAIR Repeatedly
I'm running MySQL 4.018 on Fedora. I've got a few tables with more than 1M records, and if a larger number of deletes is thrown at them or a MYSQLDUMP is attempted, I get a "got error 127 ...". I've run "REPAIR", MYSQLCHECK and every other repair possibility I could find. The tables are supposedly ok after the repair operation, and can be queried normally. As soon as I try to run a MYSQLDUMP or a large number of deletes, I get the corrupted tables again.?

Can't Repair A Table
It seems I have a corrupt table and have tried the REPAIR TABLE command but during the operation I get this return.
mysql> REPAIR TABLE users;
+-----------+--------+----------+-------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------+----------+-------------------------------------------+
| sdc.users | repair | error | Can't open file: 'users.MYD'. (errno: -1) |
+-----------+--------+----------+-------------------------------------------+
1 row in set (0.00 sec)

Mysql Crash/repair
I've got a couple of directory sites that looks like somehow something's been deleted or crashed (the directory isn't showing any listings).

I had a look in the mysql directory, and all I see are a bunch of .frm files. I don't see any MYI or MYD files.

Is there any way to recover or repair? I'm assuming I need those files, and that the lack of them is what's causing my problems. We've done a couple of things lately that may have caused the problem like a server move and a hard reset.

Server To Repair Mysql DB
I've got a corrupt 8GB mysql table and am obliged to use
myisamchk -r -o -f --safe-recover

But this is taking immensely long time, and is going slower and slower
The time it would take to repair 2.7 million rows would be far too much than I can make my client wait.is there somewhere a service where I could sync a copy of the db files under /var/lib/mysql/, repair it on their powerful server, and bring it back home?

ANALYZE, OPTIMIZE, CHECK, REPAIR
when to use the ANALYZE, OPTIMIZE, CHECK, REPAIR commands. From what I can tell, ANALYZE and OPTIMIZE are for making the table optimal to increase performance. CHECK and REPAIR are used to find & fix corruptions. ANAYLZE takes a lot less time to run than CHECK.

The questions I have are:

1. What is the general rule of thumb on regular (daily) maintenance of a MySQL DB?

2. Will ANALYZE report table corruptions? If so, I don't even need to call CHECK, I can directly go
do REPAIR?

3. If the anwer to #2 is no, meaning I have to run CHECK TABLE, should I separate the optimization exercises from the find-corruption-and-repair routines?

Indexing Table Forces Repair By Sorting
I have a problem whereby i am loading a large table into MySQL - approx 3 million rows

I then create indexes on most of the columns including normal and full text indexes on certain columns that I need to sort and search.

The indexing operation takes an age (about 7-8 mins) on each column, whether it is a normal or fulltext index. Example:

mysql> show full processlist;
| Id | User | Host | db | Command | Time | State | Info
|
| 1196 | root | PAULS:2078 | registries | Query | 457 | Repair by sorting | CREATE INDEX VALNAMEidx ON FULLTAB_113 (VALNAME
(64)) |
5 rows in set (0.01 sec)

I have tried SET MYISAM_SORT_BUFFER_SIZE=1024000000 - this has no affect

I have also tried creating the indexes when I create the table but I get the same thing.

The tables are populated by load data local infile and no additional rows are added once populated I am running MySQL 5.0.11 on a dual core pentium 2.4Ghz with 2GB RAM Any ideas how I can speed this up?

Mysqlcheck: Table Is Marked As Crashed And Last Repair Failed
I'm using the command "mysqlcheck --all-databases --auto-repair --silent"
and I see these warnings and errors (listed below) on a daily occurrence.
What could cause these indexes to be out of order? Another thing I don't
understand is why an increase in the number rows constitutes a warning?

Hoping to here from MySQL users who have came across this. I'm using the
MyISAM storage engine and MySQL 4.1.11 on Red Hat Enterprise Linux.

MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same).

My server specifications:
Red Hat 9.0
Pentium 4 3.0 GHz
2 GB Ram, Burst RAM 5 GB
Running webserver, mail server as well.

Is there a way I can view the minute process details, as what table record is being updated ?

Check Table And Repair Versus Optimize Table
I am confused about the difference between running a CHECK table and REPAIR(if necessary) versus running an OPTIMIZE table. It's not very clear in the documentation, but seems that OPTIMIZE table does a check and repair in addition to the other functions performed by optimize.

I have inherited some legacy code that does a check and repair and optionally follows it with an optimize. That seems redundant to me and I would like to streamline the code to make it an "either-or" selection.

Maximum Allowed Tables/db, Fields/tables, Records/table
1)tables that can be created in a MySql database.
2)fields that can be created in a MySql table.
3)records allowed in a MySql table and in a MySql database.
4) allowed joins in a MySql table/database.

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

Multiple Smaller Tables V.s. Fewer Bigger Tables
What is the best way to store data in a database? Multiple smaller tables (which means many Inner/Left Joins when fetching the information) or fewer bigger tables (which means few or no Inner/Left Joins when fetching information).

InnoDB Tables And MyISAM Tables In ONE Database?
My application demands some advantages from InnoDB and some from MyISAM.

Is it attainable to have both InnoDB tables and MyISAM tables in ONE database?
Or else, getting views from two databases, one from InnoDB and the other from MyISAM?

If not possible, any other approaches will do the job?

How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi,
I have two tables:

table A

Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13

table B

Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6

How can I get sum of unique keys from table A and B (id_result) like this?:

id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11

I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?

100 Databases, 4 Tables Each, Or 1 Database, 400 Tables
I was able to build, tweak and do whatever necessary to establish a rather different project but am worried that it may sooner or later crash due to idiotic programming / database usage.

In essense I took 1 database with 4 tables and duplicate it 100 times BUT because I need to connect to the different tables within the php script and am not experienced enough to created nested database connections, I duplicate the 400 tables within the same database.

What I want to know from a MySql perspective, would it make any difference if the script "play around" with 1 database connection and 400 tables or would 100 databases with 4 tables each be better? (Maximum database queries to only 17 tables at a time)

More Tables, Or Huge Tables?
i'm opening a web-service where people can open their own sites.
i have about 18 tables for a website to be put on the air.

i wonder what's better for my users:
one huge table with a lot of rows, or opening 18 new tables in a database in the format:
0_users
0_images
1_users
1_images
2_users
2_images

what's better performance wise? for 1000 users? for 10000 users? 100k users?
i assume that in about 20k users i'll have to get a standalone db server..

How Can I Tell If My Tables Are MYISAM Tables Or What?
Is there a MySQL commad to determine what table type existing tables in a
database are?

Small Tables Or A Few Big Tables
My Web site is going to use a MySQL database extensively throughout the
Web site. It is a content management system type Web site with lots of
users and written articles and other things. With my database I'm
trying to figure out if I should create big tables that have a lot of
rows in them or if I should create a lot of tables that are very
specific to its data and use a lot of table joins in my select
statements. I'm mostly concerned about performance.

I'm thinking that if I make a lot of tables, then select statements I
do will be really fast because my tables won't contain very many rows.
But I don't know if this is true or not or if it matters for
performance reasons or not.

In my Web site I have a lot of different kinds of data that could put
in many different tables. But, without data redundancy and without
using extra storage space, I can still make a few big tables, and make
that work. I just don't know which way is best for performance --
speed, I want speed. Anybody know what I should do or know a book I
should read or a Web site to go to?

Tables On ISP
I am a newbie. I recently had the web developers for my company create a form online for one of my vendors that uses MySQL as the database. I have been unsuccessful at using the command line to connect to the tables. Do i need the mysql service on the computer that i am using to connect to my web server? What exactly do i need to connect to the tables?

SQL Tables
I am working on my table structure and am alittle stuck on what to do. The user has the ability to reg himself, and if he doesnt then his details wont be saved but his transaction will. Now, when I store the transactions for regged and unregged users should I store them in different tables ? See the tables below:

User Table
UserID
Name
Contact
Email
Gender
Registered
username
password
moderator

Event Table
EventID
EventName
MaxNoTickets
SingleTicketPrice

Purchase Table
PurchaseID
UserID
EventID
NoOfTicketsPurchased
TotalPrice

As you can see, the following table would only work if the user was registered. But now the question is how I should store events for unregistered users. So far it looks like I will need to create a seperate table.

Many Tables In 1 DB Vs. Few Tables In Many DBs
How much of a performance hit will I have if I have 1 database with 300 tables, versus 30 databases with 10 tables each? (the single database would have about 30x the traffic of any single database). I don't expect the tables themselves to be very long at all, perhaps a couple hundred records max.

Also, this will all run on the same server installation either way, so with 30 tables I don't get 30 MySQL servers. The traffic isn't going to be super heavy at all.

So, the question is, is it really worth setting up 30 DBs or should I just drop everything into one and keep it simple?

Get Max() Value Between Two Tables
What is the best way to get the maximum value of a "like" field in two tables?(I.e.

Table1 - Field1 - Values: 0002, 0003, 0005
Table2 - Field1 - Values: 0001, 0005 )

Result should be: 0005

Too Many Tables - Bad?
I have a database with 138 tables. Is having too many tables bad? Can it slow things down?

Tables In Use
Everytime I try a repair with my Web Host Manager my whole site freezes up.

I have one table that is "in use" a sessions table.

I can use putty and get to the
>Mysql prompt but I am lost from there...

Is there a way to repair or fix this? I have tried using phpmyadmin and cpanel and neither work, they just time out.

About Tables
I have some tables, but forgot to save the folder of these table. I want to know how i can open my tables and find the database name. i konw this is a silly question. but i really can't find the anwser.

Dba Tables ?
In Oracle database there are tables for administration purpuse like dba_users, dba_tables, etc. There are any tables like those in mysql?

I want to see for example the structure of a table without using php admin or other graphical tool.

Repair Corrupt Tables

I have a couple of tables in a database the give me the following error when I try to open them: thanks! 'can't open file: 'clinch_a.ibd (errno:1)' What can I do to fix this problem.

I have a couple of tables in a database the give me the following error when I try to open them:
thanks!
'can't open file: 'clinch_a.ibd (errno:1)'
What can I do to fix this problem.