MYSQL :: Slow / Server Crash When Adding A Column

Recovery From Server Crash
I have a set of database files from MySQL where the
root partition crashed but the /home was saved. How do I remount
these databases with a new installation of the O/S and database
executables? N.B. I _don't_ have SQL dumps, just the actual raw
database files themselves

Server Crash - Reinstall - Reconnect To MySQL
I am running a Wordpress blog on my server. My MySQL version number is 4.1.21. The other day the raid in my server completely failed so I had to reinstall everything. Luckily I have a physical backup of all the files although I never ran any kind of dump or backup from MySQL.

I would like to know how I can re-connect to the already existing database files. Keep in mind I am a total newb to MySQL so please be gentle. All of the Internet and forum searches do not reveal anything with my circumstance (or I am not using the correct search terms).

The server names and file structures (i.e. location of the db folders) are the same and I do have a copy of the old MySQL.ini file.

I should clarify what I have done so far: I installed MySQL, made a copy of the ini file it created and copied over the old ini file from my backup. I have not run anything from the MySQL command prompt.

Server Performance Slow
I have designed a service on my site which requires about 30 -40 SQL queries per page to run. Everything works without problems apart from the slowness. The page will load very quickly until it gets to the first query, then it pauses..... and carries on slowly producing the results. (When I say slowly, I mean relative slow compared to the speed this forum loads for instance)All the other web apps with mySQL work without slowing down, but they don't take up as much resources. Even the VB board is very fast.
I'm running a 2.8Ghz 1GB Win2003 server, so I doubt that is a problem.
Is there a guide to optimizing mySQL server or a key "setting" I should be changing?

Multi-column Index, Is It Slow ?
If I use a multi-column index, is it ineffecient ? how much of a
performance hit ?

I'm creating an order table, that will have a city code as one of the
columns.
index will be ( orderID, city_code )

my other option is to use separate tables, and store the table name for
that city, in a PHP session variable.

Adding To Value Of Column
Hi I am new to mysql. Is there a way to add to a field instead of just replacing it. I want to create a query that adds an input to what is already in the table. So for instance if I already have 2 of a product in stock and get 3 more of that item in i want my the stock column of my table to equal 5.

this is what i have now:

db_query ("UPDATE products SET stock='$newstock' WHERE productid='$pid');

Adding A New Column
To ADD a new column I can say that I want it AFTER some named column.

But what if I want this new column to be the first, please, Experts?

Adding New Column
After i added a new column to my existing table, i cant insert any information using the same form i was using all along. if i remove the column (drop) then i can use the form fine..

Adding A Column
For a website I'm developing I'm trying to add a column of calculated values back into a table so that I can use those values for selection criteria. I have no problem generating the values, and displaying them in an html table. But I now need to rewrite these values into a table for a subsequent query. How do I do this?

Adding To A Column Value
I have something like this:PHP Code:

TABLE: sectionrating
section     GOOD    BAD
   1         23      5
   2         12     10

A user can comment on a section and either the good or bad
value will be incremented accordingly by a value between 1and
5.

My question is how do i add the value the user supplied to the
value already in the column.

Need Help With Adding Column Syntax
I understand how to add a column, but I'm having trouble setting the defaul value.

Here is what I have:

Adding Third Column Doesn't Work
CREATE TABLE img_cat(
img_id INT NOT NULL,
cat_id INT NOT NULL,
order INT,
PRIMARY KEY(img_id, cat_id)
);

I'm looking to use this table to organise photographs into categories and then add the 'order' column to control the order they appear on the web page. Without the order column it works fine, with it it doesn't seem to work at all.

Adding A Column To A Table
I am looking to add a column to a table, however I only want to add it if it doesn't already exsists. Is there a command to do this?

Adding And Filling A New Column
What do I need to do to fill in new column, I would like to have it
auto_increment, and adding that field is easy, however I have about 200
or so entries in the DB already.

So I can add the column, and I can set it to have an auto_increment, but
will that start out at 201? or will it start at 1 and propogate through?

Adding An Unrealted Column
I have crosstab query that uses a where clause to limit the data retrieved. Is there a way to add an additional column to the crosstab that isn't limited by the where clause?

Adding Column Values
I have a table with a column that holds integers. In the column, there are 30 rows. My question is this: How do I add up all the values? (I am working also with PHP5) So say the name of the coumn is: CARS. There are 30 rows.Some rows have 0s and others have 1s. So if there are say 15 rows with 0 and 15 with 1s. How do I add up all the values and get the final result: 15 for the column?

Adding A Boolean To My Column Type
I need to add a column in my database, which will be called 'MemberPackSent' basically I want to send out a pack to members, just wondering would I make this a Boolean? Do I need to specify the 'Length' or is a Boolean simply always 1 or 0?

Adding Multiple Column Values
I am tring to find out if it is possible to add INT fields from multiple records on a single query ....

Adding ON UPDATE CURRENT_TIMESTAMP To A Column
I'm trying to add ON UPDATE CURRENT_TIMESTAMP to a TIMESTAMP column but can't figure out the syntax. I tried various SQL strings including:

ALTER TABLE `Content_elements` ALTER `last_updated` SET ON UPDATE CURRENT_TIMESTAMP

Adding Values Of A Single Column
how can i add all the values of one column of the table to get a total?

Adding An Unrealted Column To A Cross
I have crosstab query that uses a where clause to limit the data retrieved. Is there a way to add an additional column to the crosstab that isn't limited by the where clause?
Ideally I would like the results of this query as a column in the main query: Code:

Adding And Populating A New Column In A Table
I would like to add a new column to an existing table, with the following caveats:

1. For existing records, a fixed value (same value for every record) should be added in the new column.

2. This value should not become the default value for records created in the future.

For reference, the column I want to add is Year. Existing records should have this set to 2006, however future records will be set to their respective year.

I know I can do this in two steps - ALTER table to add the column and then UPDATE to add the value 2006 to each record, but I was wondering whether there was a way to do it all in one go.

Adding New Column In A Table With Data From Another
I have been trying to accomplish the following aince quite some time.. I have tried various combinations of Union, Merge, Insert.Select, etc..

Tables:
table_1 with 22 data columns, and a autoincrement primary key column.
table_2 with 1 column (newtimenumber in query below) with double values, no primary key. (Although i can generate it)

Relation between tables:
The number of rows in both the tables are same, and if required can be mapped on one to one basis. (Not yet done as i have not assigned primary keys to the table_2; but row 1 of table_1 corresponds to row 1 of table_2)

The table_2 is generated by performing a set of calculations on one of the columns in table_1; hence they correspond to each other.

Problem:
I want to insert the all the rows in table_2 into a new Column (newtimenumber in query below) in table_1; but maintaining the corresponding row by row relation.

Tried Insert..select :
INSERT INTO dbname.table_1 (newtimenumber) SELECT swapper.newtimenumber FROM dbname.table_2

this did not work, and generated new column in table_1 but the rows were appended at the end.

I would be very glad if some one could help me with this. I have tried many things, but i am not getting something right.

Adding A Boolean To My Column Type
I need to add a column in my database, which will be called 'MemberPackSent' basically I want to send out a pack to members.

just wondering would I make this a Boolean? Do I need to specify the 'Length' or is a Boolean simply always 1 or 0?

Adding A Column Number To A Date
I want to add a column to a date. i.e.

date('Ymd', mktime(0, 0, 0, $expmonth , $expday + $paymentterms, $expyear));

this shows a column paymentterms is added to the day part of the date.
I have achieved this in php but if I can do it in sql I will be able to reduce the number of results as I want the sql to not select records where the date+column less than today.

Adding Column Values In MySQL Database Using PHP..
Just a quickie. Is there any way of adding up the values of a column in a table and then assigning them to a variable.

my query is this:

$query=mysql_query("SELECT * FROM table WHERE amount='$amount'");

this will select around 3 or 4 rows from the database. what i want to do is have it add these amounts up and then assign the total amount to one variable.

Viewing Date Range Then Adding Column Totals?
this is probably my most complex question to date. Basically i have a table that stores order information for products. What i need to do is:

- Specify a Date range
- Count number of rows in that range
- Get column totals for that range
- Return Array with column totals eg, if the array was named $total, $total['column1'] would be the column 1 total :)

This is a large table with many columns so here is what i had planned:

//OPEN CONNECTION HERE, SET DB
//First query gets date range:
$result = mysql_query("SELECT * FROM D_Orders_Columbus WHERE odate > '" . $startdate . "' AND odate < '" . $enddate . "'");
//now we get number of rows:
$num_orders = mysql_num_rows($result);

After that i get stuck, i need it to ADD the column values together, for this i assume i will need to set the column types to 'SMALLINT' (i dont assume anyone will order 32000 items :p). How can i get mysql to total all the columns that can be (eg. have number types) and then return an array with the totals?

Adding A "date Added/date Modified" Column?
I want to have a column in my table that is automatically populated with the current date/time when record is inserted? Hopefully there is an easy way to do this that I don't know about.

Big Crash
problem with my Dbase, to day when i open the ADMINISTRATOR TOOL i found in my SCHEMATA in all table and view this information:
INCORRECT INFORMATION FILE
I try to repayr but nothing change.

MySQL Crash
I was packing my tables full of goodies today, via the command line client, and when I initialized my -- INSERT INTO TABLE -- mysql sat with a blank stare with the cursor blinking at me. I waited, waited, and finally after 5 minutes realized something wasn't right. I didn't have proper Foreign Key constraints. I aborted the mission, only to stumble back and realize I was unable to provide the correct password for any user.

-A friendly telnet to 3306 responded with "couldn't connect to port 23"
-A telnet to localhost "Could not open a connection to host on port 23"
-When rebooting, MySQL Admin states "the service is running" although it's clearly not. It then frets about being shutdown or started. It's as though the service has a spirit there, but nothing else. Code:

Database Crash
my mysql-database crashes two times (power suspend). Now I have two defect tables (powerdns tables).

I get the following error:

#1016 - Can't open file: 'domains.InnoDB'. (errno: 1)
#1016 - Can't open file: 'records.InnoDB'. (errno: 1)

I transfer the tables direct in a new mysql-server (last day). On the other server all works just fine. But after I transfered the database to an new server I get the errors by connecting on it (on top).

I have 1 month, 1 week and 1 day old backups, but I get allways the same errors (now I'm a little bit confused). I have the .bin files and all other databases just work fine. Why are all backups with this tables defect? The old server is now offline :-( Is there anything that I can do to repair them? Code:

MySQL 5.0.2 Crash
I have just create my DB, but using whatever kind of DB editor (also MySQL Administrator 1.0), service go down when I execute a 'Show fields from ...' command. Tables are all MyISAM tables and created from .SQL file without errors.

Database Crash
Cannot open one of my tables after the server crash. Any ideas on how i can reclaim my data.

Why Do The Tables Crash
I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes?

Windows Crash
I have one computer where I cannot startup the windows operating system.
In that computer I had installed the MySQL Server.

Putting the hard drive has a slave on a computer with MySQL Server installed, how can I access the databases?

Mysql Crash
i had a server crash and since that mysql and with it my whole plesk server are broken. already on startup mysql crashes. from the log file i see, that an innodb database is being recovered, but the recovery seems to fail.
i really have to bring the server back online asap. what can i do without losing all my databases? i don't mind the corrupt one being lost. although i would like to know which one it is..can i just reinstall mysql through yast (i'm on suse) or am i going to lose all data? can i backup data, reinstall and just put data back to place?

Mysql Crash
My mysql has crashed.
I ran chkdsk after my pc failed to boot and now the mysql won't load as i get

Error

MySQL said: Documentation
#2003 - The server is not responding

I have data on iti still want but what can I do?
Mysql was working fine until I needed to fix the boot up.

Major Crash
This what I get when I try to go to my site that is running Geeklog.

>An SQL error has occured. Please see error.log for details.<

The errors log is here: http://www.motographicmedia.com/home/logs/error.log

Everything was running fine until I tried to install a chat program phpMyChat. And the whole site went down. But what confuses me is that, that install creates a whole new database.

Periodical Mysql Crash
I hope someone here has an idea how to fix my problem:

For some reason MySQL (4.0.24) on my Debian Server crashes since a couple of weeks periodical on every or every second Sunday and sometimes on Saturday alway between 3:25 and 3:59am.

I checked the mysql.log for the last crashed, but could not find any conspicuity.
Here two examples of the last log before crashing:
060923 3:26:59 197729 Query select main from xxx_main
060923 3:27:14 168802 Init DB usr_xxx

and

060924 3:31:21 18365 Query select inhalt from xxx_users_ban where art='ips'
060924 3:31:26 18365 Query select pass from xxx_users where uname=''

I also checked the users cron jobs, but could not find any cron that will use MySQL to that time.

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.

Where Can I Find 'crash-me' Tool
I remember I could find and use 'crash-me' tool on the mysql.com site.
It was very nice to compare mysql with other database products, although not neatly arranged with too many details.
Now when I go to the place (http://dev.mysql.com/tech-resources/crash-me.php), it says:

Note: The crash-me table is normally generated from DBMSs started with default parameters. If this is not the case, the fact is noted in the comment row.

I can I access and use the tool to compare the features of each databases?

MySQL Service Crash
I have the following query which runs fine without the insert
statment.

INSERT INTO `db_x`.`attributes` (id, typeid, value, start_time,
end_time)
SELECT distincti.id,
at.id AS typeid,
d.megapixels AS value,
NULL AS start_time,
NULL AS end_time
FROM `db_y`.`data` d, `db_x`.`attribute_types` at, `db_x`.`items`
i
WHERE at.name = 'megapixel' AND i.secondid = d.p_id
AND d.megapixels is not null;

When I run it without the insert statement it return 500 rows in about
90ms, when I add the insert statement the whole MySQL service stops.
The `db_x`.`attributes` table furthermore contains a field 'yo' which
is the primary key and an auto_increment field.
I am running MySQL 4.1.9-nt. It is running on Windows Server 2003.

Mysql Crash - Trace The Log
Yesterday,mysql process disappear sliently. After I check the err-log,no clues found.
But the PID file still exising.I think the mysql terminate by some reason.But I still not found why.

The error-log mysqld.log wouldn't provide any valueable msg.It just contains the shutdown & start up log. Any idea?

Crash. Dump Included. Cause?
Our MySQL master crashed the other day. The mysqld process was running but not accepting database connections. The crash dump is below. Does anyone have ideas as to what the cause could be? Someone said that we had corrupt pages in memory. Code:

MySQL Service Crash
I have the following query which runs fine without the insert statment.

INSERT INTO  `db_x`.`attributes` (id, typeid, value, start_time,
end_time)
SELECT   distinct i.id,
   at.id AS typeid,
   d.megapixels AS value,
   NULL AS start_time,
   NULL AS end_time
FROM     `db_y`.`data` d, `db_x`.`attribute_types` at, `db_x`.`items`
i
WHERE    at.name = 'megapixel' AND i.secondid = d.p_id
AND   d.megapixels is not null;

When I run it without the insert statement it return 500 rows in about 90ms, when I add the insert statement the whole MySQL service stops. The `db_x`.`attributes` table furthermore contains a field 'yo' which is the primary key and an auto_increment field.

I am running MySQL 4.1.9-nt. It is running on Windows Server 2003. I found out it's the following error: 2013 Lost connection to MySQL server during query. It also happens when I execute a large batch file.

Connection/Client Crash
I got the following problem:

A client connects to the MySQL server from a remote machine and
creates a lock using GET_LOCK() method. Then the client crashes or the
connection crashes (e.g.somebody pulls the cable) - whatever. The
point is that the client does not manage to send any disconnection
message to the server.

The process of the crashed user remains pretty long (I checked it by
calling SHOW PROCESSLIST) and holds the lock.

Is there any way to force the server to check periodically whether
clients are still present and active? Or is there a way to check
wether a client shown in the processes list is really connected and
then kill the invalid processes?

Restoring Php Nuke Db After Crash
I was running PHP Nuke on a Win2k box using Apache2Triad. Fortunately I
backed up the drive using windows backup prior to the hard drive failure.

Now I have installed Apache2Triad on another machine and wish to reimport
the mySQL database that PHP Nuke relies upon.

Mysql Weekend Crash Course
I got this book but unfortunately it was missing the cd. The early chapter
that sets up the database has the
code to do this on the cd. The web site is www.mysqlwcc.com I tried to get
a copy of the material on the cd from the web site.Maybe someone could help. I could use a good beginners tutorial.

I got this at the library and it seems
to be a fast way to get introduced to a lot of the mysql info. that one
would need... a good manageable way to learn.
Running out and buying a book is a bit hard right now, financially. So, I'm
open to any suggestions on how to get the
material or to find a similarly useful course/tutorial that will really get
me up to speed.

Mysql Crash On Win 2000
I have latest mysql version 5.0.2 alpha installed on my windows 2000 machine. Now there is bug in the version, which causes the server to crash everytime I try to use any mysql client like phpmyadmin, mysql query browser. The only possible way of accessing the database for now is only through the command prompt and not theough any other client. This bug has already been reported to mysql. But it appears they have fixed in the cvs. But as of now there is no binary patch for windows. Anyone got any ideas on how do I tackle this one without having to change my version.