MYSQL :: Cannot Repair Tables

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 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.

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

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.

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.

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.

Big Tables
I have a big inno table (about 20 GB). Is it posible to divide the table into several files?

Tables
I have done the SHOW TABLE STATUS FROM database;

Now I just watnt to list the names of all tables, Data_length, and Data_Free. Is there away to do this?

Two Tables At Once
I have two tables
Contact
ID = autonumber
NameID = Id1 from Master_Roll tabel
ContactDate = contact Date
details = Deatails of this contact

Master_Roll
ID1 = Autonumber
Surname = Surname
First NAme = First Name
DOB
SEX
PHONE
ADDRESS
EMAIL

I want to query the contact Table and the Master_roll Details table, I also want to add and update both tables.
Which is the best way to do this?

Sum From 2 Tables
I cannot seem to get the query to work.

I have 3 tables. A, B and C. A.id = B.id and A.id = C.id

I want to get A.* , sum(B.value), sum(C.value) for all records in A.

I am trying this but I can't seem to get it to work.

Select A.id, A.name, sum(B.value), sum(C.value) from A, B, C where A.id = B.id and A.id = C.id group by A.id;

When I run the query it just sits there.

Is this query correct? How can I achieve this, if my syntax is wrong.

How Many Tables
I am currently trying to speed up a database that is performing extremely slowly (1 hour or more for the average selection, which I would like to take a few seconds in an ideal world). The biggest table in the database currently has 50 million lines (around 30GiB for the whole database). Each line is made up of a source id (a number between 1 and 37) a datetime column, a user column, a port column (of which there can be up to 65535*2 ports per hour per user per source id). I'm currently using innodb and MySQL server 4.1.14.

Now the source column obviously has a very low uniqueness, and importantly, no read request will EVER request more than one source id. I am planning to summarize the information older than 2 months into two other tables (daily data and monthly data when it gets old enough) and I am thinking of splitting up the information into separate tables for each source id - however, this is all in preparation for the database to grow roughly 30 times its current size (1000 source ids) - this would mean 3000 tables.... is that a bit extreme? Is there a better solution?

Look Up Between Two Tables
I'm using mysqlcc and mysql server 4.1

I have two tables: tbl01 and tbl02

tbl01 needs to look up data from tbl02 and store it.

Then when I change the data in tbl02 it will automatically update the data in tbl01.

I have been trying to do a look up as in Access but canâ€™t find how to.

One Or Many Tables
What is a better method: creating one large table with a category field or several smaller table
distinguished by the category?
The tables have identical structures. The records are retrieved only form one category at the
time.

Set Up My Tables
I need to set up some tables for a bookmarking module that I am making for a CMS. The data contained will be:

userID
catID
catNAME
bmName
bmURL

This will all have to be grouped around the userID as that will vary depending on who is logged in at any given time and I will be running queries like

select all where username = 1 and catID = 10 , etc, etc

I will also need to be able to insert new categorys (catID) that will reamin unique to each user.Now I would like to know what the best way would be to set up the tables for this - I could do this by trial and error, but I would like to hear an experts advice/opinion.

Two Tables
there are two kinds of users

- ordinary users
- special users

Both kinds have the same fields like login,password,email etc. Special users have few more like name,surname,specialisation etc.

Should I use one table ?

user_profile:

id
login
password
email
name - NULL
surname - NULL

Big Tables
So i've got a table with 15 million rows.. id is the primary key

SELECT COUNT(id) FROM listings WHERE id != 0;

takes about a minute to load... which is insanely slow..
anyone know what could be causing this?

Tables
I just did an update to mysql 5 from 4. Now it seems a bunch of my tables are corrupt. In phpMyAdmin they are comming back as "in use".
When I try to run a query I get this:

#1017 - Can't find file: 'tablename' (errno: 2)

it seems to only be affecting the really old websites/tables. Please help.