MYSQL :: REPAIR TABLE

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.

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

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)

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.

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.

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

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.

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.

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

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.

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.

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?

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

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?

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 ?

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

Create Virtual Table During Query Or Load Table To Memory From Text File
I have access to my application database via a secure link, so I cannot link a local database to it, only run queries on the remote server and download the result.

The database is missing some useful information, and I cannot add a table at this stage. I would like to achieve the result by creating a virtual table or the like. ...

Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured.

A & B

Table A, has column: Product (product code) as primary key

Table B doesn't.

Apart from that they have the exact same fields.

There's also a column: supplier

I want to

Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples

So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes.

mysql version 4.0.27

Inserting Multiple Items (# Unknown) Into A Table Using Primary Id From Master Table
In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table.

To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table.

My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered.

The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"?

Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?

Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so:

PHP Code:

$selectt = mysql_query("SELECT trailers.trailer_id,
trailers.trailer_title,
trailers.description, trailers.file_url,
trailers.runtime, trailers.views,
trailers.rating, trailers.tt, trailers.keyw, trailers.date,
tags.snub,
films.film_title, films.studio, films.keyw, films.image
FROM films,tags,trailers
WHERE trailers.trailer_id=tags.trailer_id
AND trailers.film_id=films.film_id
ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh);

I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it.

Possible?

ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211).

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 5359211
Avg_row_length: 801
Data_length: 4294967288
Max_data_length: 4294967295
Index_length: 45783040
Data_free: 0
Auto_increment: 5406252
Create_time: 2007-04-20 18:26:38
Update_time: 2007-08-22 09:55:22
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Here is the table structure.

CREATE TABLE `messages` (
`id` int(11) NOT NULL auto_increment,
`subject` varchar(255) NOT NULL default '',
`message` text NOT NULL,
`attachment_path` varchar(255) default NULL,
`new` tinyint(4) NOT NULL default Ɔ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On searching I found the this link.
http://dev.mysql.com/doc/refman/4.1/en/full-table.html

According to manual i executed the following query

MySQL
ALTER TABLE messages MAX_ROWS=20000000000;
I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this.

After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left.

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 170109
Avg_row_length: 649
Data_length: 110563036
Max_data_length: 281474976710655
Index_length: 1961984
Data_free: 0
Auto_increment: 5409214
Create_time: 2007-08-23 10:07:08
Update_time: 2007-08-23 13:41:57
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=4294967295
Comment:
1 row in set (0.00 sec)
What could be the reason of this rows deletion?

I am on 32 bit system.
MySQL version: 4.1.18-standard-log
Operating System : CentOS 3.x
Memory: 4 GB DDR

3,000,000 Unique INT(11) Low-Hi Ranges In My TABLE, Am I Stuck With FULL TABLE SCAN?
I have a table with 3,000,000 different IP netranges.

each row has a unique range: ie: 1xxxxxxx-1xxxxxxx2
..

I try to find the primary_index when querying WHERE var BETWEEN lowIP and highIPrange.

Since there all of the netranges are unique, it seems like my INDEXES are useless, so the only way to optimize this would be to horizontally partition the data? The query is extremely slow ~1 second in MyISAM, and ~.50 second in MEMORY table format.

I'm assuming the only option is to horizontally position the data to improve performance, am I correct?

ERROR 1005: Can't Create Table './db/table.frm' (errno: 150)
I found the key to solve this problem in:

http://darkstar.ist.utl.pt/mysql/do...onstraints.html

You'll probably need an INDEX for that new foreign key you are declaring in
older versions this isn't neccesary but in latest ones it is a restriction.

You can have more info about the error description if you have root access
by typing
mysql> show innodb status;

LATEST FOREIGN KEY ERROR
------------------------
030807 1:27:10 Error in foreign key constraint of table database/table:
There is no index in the table database/table where the columns appear
as the first columns. Constraint:
foreign key (key) references database.table(key) on delete cascade)
type=innodb

also if you type:
shell> perror 150

you obtain:
150 = Foreign key constraint is incorrectly formed

I hope this to be enough...

Trying Abort One Select Accessing One Table Locked By Lock Table.
I'd like to configure one time to abort one transaction that are accessing one table locked by lock table write command ex:

Transaction A
mysql> start transaction;
mysql> lock table mytable write;

After I'll start new transaction B (another connection)

mysql> start transaction;
mysql> select * from mytable;

It's will become long time to wait...

My transaction B never "die".

I tried to configure innodb_lock_wait_timeout=20 in my.ini without success,

Copying Column Names And Properties From An Existing Table To A New Table?
I've got a table with 42 columns, set to different types (string, datetime, date, integer). It has 8 million rows. I want to make a new table with the exact same columns, but with no data in it. Is there an easy way to do this without re-defining every column?

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.
I have a table in the form:

year | season | hits

2004 | Summer | 42
2004 | Autumn | 43
2005 | Spring | 51
2005 | Summer | 52
2005 | Autumn | 53
2006 | Spring | 61

I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form

year | Spring | Summer | Autumn
2004 | ------ | ----42 | ----43
2005 | ----51 | ----52 | ----53
2006 | ----61 | ------ | ------

Could anyone suggest a mysql query to generate the required output?

1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly.

With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B.

I will do this using the Cron.

But what would be the best insert query to use so that it does the process really quick.

The query that I have so far is:

Quote:

mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1");

With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A.

How can I add to the query so that it does this.

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

ALTER TABLE `table` ORDER BY `column_a` DESC
I try to get better performance by implementing this:

http://dev.mysql.com/doc/mysql/en/alter-table.html

"ORDER BY allows you to create the new table with the rows in a
specific order. Note that the table does not remain in this order after
inserts and deletes. This option is mainly useful when you know that
you are mostly going to query the rows in a certain order; by using
this option after big changes to the table, you might be able to get
higher performance. In some cases, it might make sorting easier for
MySQL if the table is in order by the column that you want to order it
by later."

I run this SQL query on one of the table:

Not Unique Table/alias: <tablename> For Temporary Table
Iâ€™m converting an application from Paradox to MySQL (first time Iâ€™ve used this) and am replacing Paradox local tables with MySQL temporary tables â€“ I understand each user will have a unique copy of the table. Basically I issue the following two commands :

'drop temporary table if exists <tablename>'

'create temporary table <tablename> like <tablename>'

where <tablename> is a table structure in the database with 0 records. This strategy was successful while I was working with a local copy of MySQL on my development PC, but when I run the system from a server on the office network I get the following error :

#42000Not unique table/alias: <tablename>

The environment I work in is Windows XP Pro SP2, Delphi 5 and CoreLabs MyDAC data access components. Iâ€™ve searched this site and Googled to no avail. Hoping someone has seen this before.

Renaming Table In Database, Restoring Data To Table
A consultant recently hired at my company overlayed/wiped- out a table in a database I created for him. This database was a copy of a live database so I have the original table data he deleted.

How do I rename the product table he created to os-product, it must live in the same database?

How do I create a the product table and import the original data from the live database?

Fixed Table, Variable Number Of References To Another Table?
I have two tables A and B. I want to have a column in table A that can reference to zero or more rows in table B. Is there a way to do this without making my table dynamic? I want to keep it fixed. Is there a solution or workaround?

1036:Table Read Only Error, Cant Add A New Column To My Table
I have a table with arround 30 columns, and I am trying to add another column to it with the following query.

alter table tablename add column column_name varchar(10);

and I get the following error.
ERROR 1036: Table 'ProfilesDesc' is read only

what could be wrong?

Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName

This obviously doesn't work. Any assistance on how to forumulate this
query (or, if I'm headed down the wrong path, the correct way to do this
operation) greatly appreciated.

Can't Create Table -- Error 1050 - Table Already Exists
I tried to create a table in the database. Here is the query sentence :

CREATE TABLE word (aff_code varchar( 10 ) NOT NULL default '',position tinyint( 1 ) NOT NULL default '0',flag tinyint( 1 ) NOT NULL default '0',PRIMARY KEY ( aff_code, position )) ENGINE = MYISAM DEFAULT CHARSET = latin1;

I was expecting to create a three column table. However, MySQL kept reporting error : ERROR 1050 (42S01): Table 'word' already exists. The fact is there is no table in this database. I then changed the table name, engine type, MySQL still report the same problem. I've tried changing database name. Still the same problem.

Prior to the query, I run a program that created (and inserted data as well) 65,000 tables in another database (same server although). I'm wonderring would this cause some kind of dead lock that result in the problem of not able to create any more table?

Can anybody give me some tips on how to solve the prolem?

Rights To Create Table, Select, Then Drop Table..
I have a need to get data from the db that requires me to=20

1) do a select and create a new table with the results=20
2) run a query against that new table=20
3) drop the new table=20

I have a script on my server that does this using the root account that has all on *.* for the db. It works fine.=20

I now want to get these results on a web page.=20 I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done.=20

what priviledges do I need to give that user?=20

currently I have the following but the user can't even log into the db from the command line..=20

mysql> show grants for user;=20
+-----------------------------------------------------------------------
---------+=20
| Grants for user@% |=20
+-----------------------------------------------------------------------
---------+=20
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD
'6fe4c0ab2cf30ae3' |=20
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' |=20
+-----------------------------------------------------------------------
---------+=20
2 rows in set (0.00 sec)=20

when I do a "show grants for user", what should I see to allow what I want?

PhpMyAdmin: Export Table - Import Table
I find in PhpMyAdmin where I can export a table, however when I click the "GO" button where is the exported file? And how do I name it?

When I go to import a table I can select the file to import and that makes sense.

My goal is to take a table from one database and use it in another database, so I hope I am in the correct place?

Mysql Move Table Content To Another Table
How do I move the contents from one table to another table in the same
database. ie the contents of "cart table" to "orders table" thus removing the
contents of the "cart table" and placing it in the "orders table".
I have looked everywhere for a simple method other than the long winded Select, Insert and the Deleting.

Perform Alter Table Only If A Table Exists
I need to run a batch of "Alter table" statements on a set of database tables.
However, some of the databases may not exist, and even if they exist, the specific table might not exist.

Alter table only works if the table exists. Is there a way to tell MySQL to execute the "Alter table" statement only if the database and table exist? If so, how?

Convert The MyISAM Table To InnoDB Table
Greeting, do some body know how to "Convert The MyISAM table to InnoDB table"?

Inserting Into A Table From Antoher Table With A Different Structure
Say i have table A with column structure 'a','b','c','d'
and I have table B with column structure 'a','b','c'

I've tried doing an insert into A select * from B; but it says I cannot do this due to the size difference in tables.

Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning.

current query looks like this:

SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id;

So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?

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.