MYSQL :: Release Disk Space By Drop Database

How Do I Reclaim Disk Space In Data Folder
I have 5 databases:

- mysql
- test
- product1
- product2
- product3

The hard disk on my server was near full, so I decided to delete databases product2 and product3 as I no longer need them. I dropped the databases product2 and I noticed the disk space was not freeed by MySQL after dropping this database. The tables in databases product2 and product3 are INNODB.

What is the best method to reclaim my disk space when dropping entire databases?

Restore Database From Another Disk
I'm wanting to restore my old database by copying the contents of the old /var/lib/mysql folder to the new server, but this isn't working.

Is there a documented procedure for doing this? I'm using InnoDB and MyISAM.

How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is:

CREATE TABLE `big` (
`keyval` int(10) unsigned zerofill NOT NULL,
`name` varchar(20) default NULL,
`avail` enum('y','n') default NULL,
PRIMARY KEY (`keyval`),
KEY `keyval` (`keyval`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.

Disk Size Of Tables[or Database] In Mysql Or Oracle
how can i know disk memory occupied by database[tables] in mysql or oracle.

How Much Space Is The MySQL Database Using On IIS?
I'm running IIS on Windows Server 2003 with full access to my server.

How do I find out how much space the MySQL database is using on my server? Is there a way to break it down by Schema and/or table?

We do not have PHP MyAdmin installed.

Database Restore Space
i want to increase space of the database.. while restoring.. wen i click import it was '(Max: 2,048KiB)' i want to increase this space to.. higher

Extend The Space Of Database
I`m testing some queries about loading tables from files with a large amount of data, millions of records.
It proceeded until the half of the file, interting in the table 10 million records approximately, and then failed showing an error in MySQL query browser telling me that I have to remove some things in databse in order to free the space. All I want to do is to extend the actual database capacity but I dont know how and from where to do it.

Can I Upload A MYSQL Database To My Free Web Hosting Space?
I have a domain with awardspace.com which has php and MYSQL enabled.

I am a newbie and not quite sure how to set it all up or if it's even possible with the Database I've created on my desktop.

When I go to the Database Manager in the administrator section it has a spot to create a database,and confirm a password. From there I take it you would use the PHPMyAdmin to alter the database.

Can I just upload a database using an FTP manager like Dreamweaver, or do I need to start from scratch and create the database on this server. If so is there a way to eventually download it so I don't lose any of the information in it.

Can't DROP DATABASE
I'm having a problem using the DROP DATABASE command. I use plesk so I have a limited access to phpmyadmin and somehow I created a database that I cannot access through plesk and my only choice is SSH.

So when I get into MySQL through SSH I first listed all my databases using:

SHOW DATABASES;

+-----------+
| Database |
+-----------+
| database1 |
| database2 |
| database3 |
| horde |
| mysql |
| psa |
| test |
+-----------+

Then I tried

DROP DATABASE database1;

and i receive:

Query OK, 0 rows affected (1.89 sec)

But the database is still there and I cant use the same name to create another database in Plesk.

Drop Database
I have a question about dropping MySQL databases. I backup my databases using the following command:

mysqldump db_name | gzip > db_name.gz

I can scp the file to another server (Server B) and restore the database by executing the following 2 commands:

mysqladmin create db_name
gunzip < db_name.gz | mysql db_name

If I drop the database on Server B, the following message is displayed:

Database "db_name" dropped

But the database still shows up when I issue the following command:

mysqlshow

So my question is, why isnt the database being dropped?

Cannot DROP Database
I've tried to Delete this one Database through "phpMyAdmin" and also through the "Terminal", OSX. I keep getting the same Error:

"Error
SQL query:

DROP DATABASE `PEI`

MySQL said:

#1010 - Error dropping database (can't rmdir './pei', errno: 66)"

I've deleted all the tables in it and also Flushed all Privileges but still no luck...

How I Can DROP The Database Using PHP Code
I use this script to add database, this script successful to ceate the database fox

<?
$my_db = "fox";
mysql_create_db("$my_db");
?>
-------------------------------------------------------------------------------

When I use this script to drop database, this script fail to drop the database fox

<?
$my_db = "fox";
mysql_drop_db("$my_db");
?>
-------------------------------------------------------------------------------

How i can DROP the database fox?

Can NOT Create Or Drop A Database
I just Installed MySQL v4.1.14 and had created a Database (from a
script). But I would like to redo the Database portion of it but am not
able to drop the Database. If I do a "show database;" I still see the
database. But when I look in the data directory, it is not there. Below
is my attempt the create / drop the DB:

mysql> create database testlink;
ERROR 1007 (HY000): Can't create database 'testlink'; database exists
mysql> drop database testlink;
ERROR 1008 (HY000): Can't drop database 'testlink'; database doesn't
exist

CREATE/DROP DATABASE In A SCRIPT
I have a script where I create all the tables, constraints, stored procedures and so forth.

The only thing I didn't find (but must be easy) is a way to check if the DB already exists, remove and create a new one (if it does) or just create a new one (if it doesn't).

I want to add this to the same script where I create the tables, SP.

Restrict Drop Database, Not Table
Is it possible to restrict my users from Dropping their database, but still allow them to drip their tables and data?

Rollback DROP Database Statement
I have done the beyond foolish... I accidentally clicked the DROP
button on phpadmin for the horde database that runs our schools email
server. I am in a world of hurt if I can't undo this transaction. I
haven't done anything except for read the documentation and I can't
seem to find anything that would rollback or undo the last statement.

Sorry for the desperate newbie whining, but I really, really, really
would like to get that database back. I don't have any backups that I
know of and I could really use some help.

Disallowing DROP DATABASE Using Privileges
I run a small shared webhosting server and have a problem regarding user privileges and dropping databases. Users are not able to create new databases. But as they have to have access to their own databases, I use a statement such as this one:

GRANT ALL PRIVILEGES ON `database` TO 'username';

This gives them the right to DROP TABLEs, but also to DROP DATABASEs. This means that they can delete their own database, but not recreate them.

I want to disallow them to drop any database. How do I do this and why is there no separation between DROP TABLE and DROP DATABASE?

I don't know the logic behind this; someone said "if you can drop all tables you practically dropped the database as well" but I do not agree with that. I can give a user DELETE/ALTER privileges so that they can empty and mutilate their tables but still they would be unable to drop them.

Rollback DROP Database Statement
I accidentally clicked the DROP button on phpadmin for the horde database that runs our schools email server. I am in a world of hurt if I can't undo this transaction. I
haven't done anything except for read the documentation and I can't seem to find anything that would rollback or undo the last statement.

Problem With Drop Index And Drop Column
I have a table named employees and a table named chargeaccount. Employees has a foreign key with chargeaccount called chargeaccountid. All I want to do is remove that foreign key column. The index name is fk_chargeaccount_employees

But I have a problem with this query:

alter table employees drop index fk_chargeaccount_employees

and this one too:

ALTER TABLE `timesheet`.`employees` DROP COLUMN `ChargeAccountID`;

How To Remove The Related Privileges Of Accouts When Drop A Database?
I 'am using Mysql 5.0. When I drop a database, but the related accounts still exist,
how can I remove the privieleges in these accounts which used to access this removed databases ?But I do not know which accounts can access db2 before.

e.g.
some accounts with select ,delete update to database db1 ,db2 and db3, when I drop db2, how to remove the privileges related db2 in these accounts ? but not impact on other accounts and databases ?

Release Of 5.1?
MySQL 5.1 has been around for quite some time now - when is it likely to be released??

Is any one feature in particular holding back the release?

MySQL 5.1 Release
Does anyone know when MySQL 5.1 stable version will be available? We are planning to move to mysql cluster, and decided to wait for mysql 5.1 instead of current version.

Should we move to mysql 5.0 cluster or wait for 5.1?

Mysql 5.1. Release
Any idea as to when mysql 5.1 would be a release candidate. Its still in beta and I was wondering when is the release candidate planned.

Mysql Release
Will 5.0 of mysql work with aix 5.1 ?

Sleep Conections Doesn't Release
I'm using Mysql version 5.0.24a on a Windows 2003 server

Does mySql is suppose to release connections after some period of time of
connections at sleep? Because I'm having problems with some connections are been
left even after I close connections and programs is there something related to
this?

Sometimes happens and some other times it doesn't happend. I'm worried because I
have to be cleaning connections manually with the Administrator.

Mysql 5 Release Date
I tried searching but to no avail. Does any one know when MySQL 5,
stable production release will come out? Also will the MySQL 5, stable,
production release include InnoDB engine?

When Is MySQL 5.1 Available In A Production Release?
Does anybody know when MySQL 5.1 is available in a production release?

Estimated Release Date For 5.1
I've been searching and didn't find any estimated release date for 5.1

MySQL 5.0 Release Candidate Available
I'm downloading mine already - anybody else tried it? This new version meets ANSI SQL and offers a plethora of enterprise features including

* Views (both read-only and updatable views)
* Stored Procedures and Stored Functions, using the SQL:2003 syntax,
* Triggers (row-level)
* Server-side cursors (read-only, non-scrolling)

and loads of other cool things.

Somehow I thought that was worth a mention. I'll be interested to hear what you guys make of this newest offering from mysql.

The MySQL Release Criteria
Compared to other Open Source projects, could anyone please tell me the process which MySQL goes through before a new build can be released?
i.e. Apache you got Developers/ Committers, PMC Members, etc all working together to ensure releases are qualified and bug free, etc.
How does this process work with MySQL? I have been looking all over and cannot seem to find any information on how this project is managed, or what levels of management a build travels through.

MySQL 5.0.24 Release Date
I suffer of MySQL 5.0.22 bugs
http://www.vbulletin.com/forum/bugs...view&bugid=2449
and was waiting for MySQL 5.0.23 to fix this issue, now there is
5.0.24, so when it's going to be released?

Recover ECCN Code Of Mysql Release
Is it possible to recover ECCN code (Export Control Code Number) of a mysql distribution (or another identificatio product code if not possible).

Disk Performance
We have an application which tends to get very disk-io bound, spending large amounts of processor time in "iowait" during select queries.  Given that writes are relatively infrequent, which would the experienced admins recommend for the disk configuration:

Raid5 or Raid10?  Raid0 is even an option, since we have a backup server and could easily add another, but it's not preferable unless the read performance would increase substantially.

The hardware looks like this:

Dell PowerEdge 6650, 4x2.8ghz CPU, 12gb RAM, 4x36gb internal drives, raid 5
Powervault 220S, 14x73gb 15K drives, dual-connected to the server.  Current configuration is Raid5.

MySQL 4.0.18, RHEL3 (2.4.21-27.0.1.ELsmp) and now using the megaraid2 driver.

There is probably room for optimization in the application, but that's currently out of our control.  We may even move to a different database platform, but if MySQL can handle the load I'd just as soon not.

Running MtSQL On A RAM Disk
I am trying to set up the same thing but have been stopped right from the
get go as I can not get a larger than 2G RAM disk going (using a 64 bit
Ubuntu installation).

I am hoping someone who has gone through the basic set up might get me
squared away to start. An offline conversation would be fine and
perhaps save the rest of y'all wading through said basics.

Disk Quota In MySQL
Is there a way to assign a limit on the size of a specific database? For example database A can take up no more than 500 MB.

One of my databases can potentially grow without any bounds and possibly stop other critical databases from operating. We are using MySQL 4.0.21 on Linux. We use innodb tables.

Data Kept On The Hard Disk
my mysql database went down... had to reinstall it... where is all the data for the tables kept - i got the /var/lib/mysql/ bit but thats only the tables

Showing Disk Usage
How can I see how much disk space is being occupied by an InnoDB table?

Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables.

Please tell also if there is a way to see how much space is occupied by
a particular row or column of a table.

Data Location On Hard Disk
I have just installed MySQL 5.0 and finished the first test from a database application.

I saw that the data is stored in c:program...MySQl...

I am not happy with that. I want MySQL stores all data in a map of my own choice.

Flush A Heap Table To Disk?
As the subject says, is it possible to flush the contents of a heap
table to disk?

For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

Copying Table To Disk During Execution
I seem to be having an issue with a query's results being paged out to
disk during runtime. I notice in processlist it saying "Copying to tmp
table on disk".

select col1, col2, count(ip) as 'COUNT' from db.table group by 2 order
by 1, 3;

None of the columns are a primary/foreign or an index. This query
returns 92K rows and has taken anywhere from 50 seconds to run to over
1.5 minutes. The table size on disk is around 33MB.

After doing some research i found that i should be changing the
tmp_table_size system variable. Originally it was set to the MySQL
default, 33554432. I set the varaible to 90,000,000 and it still
started paging out to the disk (this was the only query running at the
time). I would think that 90MB in space would have been enough to
handle the query since that would assume over 600K row size.

I actually had some trouble setting the tmp_table_size for the global
variable but was able to change it as a session variable.

I have also tried altering the query_cache variable (which has only
slowed the query down - currently it sits at a size of 50MB) and also
the sort_buffer_size but neither have made a difference.

What is the magic variable that i should be messing with?

Moving Mysql To Another Hard Disk
I have a web based intranet application (php) that uses a mysql database. The server has just had a new drive added to it and am looking for a way to move the database from the current c drive to this new drive.I'm looking for a way to either:

move the entire database to the new drive (in which case do i have to do anything within the code - the database name is still the same and i'll presume another drive is still localhost can)

Can i just make Mysql save data into the new drive (and also move just the old data file to the new drive leaving everything else as it was).

Ibdata1 Is Close To Disk Size
I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my MySQL?

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
query_cache_size = 16M
query_cache_limit = 8M

log-bin
server-id = 1

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

( I had to comment the innodb to stop crashing )

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Flush A Heap Table To Disk
For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

MyISAM Table And Sort Order On The Disk
I ran the myisamchk tool with the options --sort-index
--sort-records=1 against an MYI file. Is there something I can use to
confirm what this command did? I would like to see what the sort order
on the disk for each table in my database.

Disk Usage Increases After Dropping A Large Index?
I have a linux server which is hosting several very large (~20GB)
databases. In order to save some disk space I dropped an index on one
of the larger (InnoDB) tables because it is no longer needed. It took
about 30 minutes for MySQL to drop the index, during which time I saw
the free disk space decrease. I didn't think anything of it while the
drop operation was running because I figured it was some kind of temp
file being used by the drop (even though the temp directory for mysql
is supposed to be on another partition). To my surprise, after the
index was dropped, not only did I not have more free space as I
expected, but in fact I had about 2 GB less than I did before I
dropped the index. Huh?

So now I'm down 1 index and 2 GB of space. How does dropping a db
object actually increases the amount of disk used by mysql? Is there
anyway I can reclaim this space? MySQL version is 4.0.15

Release Disk Space By Drop Database

OS. linux 2.4.22 MYsql : 4.0.18-standard I created a database and populate 2 big InnoDB tables, the total size is > 3G. After all testing, I droped the database . But, the harddisk space is not release to the filesystem, Why, How could I recover that.

OS. linux 2.4.22
MYsql : 4.0.18-standard

I created a database and populate 2 big InnoDB tables, the total size
is > 3G. After all testing, I droped the database .

But, the harddisk space is not release to the filesystem, Why, How
could I recover that.