Phanindra's questions

I wanted to fetch only the value from status variables. Suppose the query is

show status where Variable_name='Aborted_clients'

It returns

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 4     |
+-----------------+-------+

From here I need to fetch only the value of column "Value". Is there any way. When I am trying to this query as sub query, it is throwing error.

I am using MySQL version 5.0.24. And OS is windows

There are some rows in a table, by mistakenly I had deleted all rows from the table and data got commited. So is there any way to restore data for that table.

I am not having backup of the database.

I did not enable Binary Logging.

I am getting following error in err file.

110803  6:51:26  InnoDB: Error: table `ims`.`temp_discoveryjobdetails` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

And when I do the same, like copying the frm file from other database to here and drop the table, i am getting following error,

InnoDB: Error: trying to load index PRIMARY for table ims/temp_discoveryjobdetails
InnoDB: but the index tree has been freed!
110803  6:50:26  InnoDB: Error: table `ims`.`temp_discoveryjobdetails` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

Please any one help me out of this. Also can any one tell me why this error is coming.

EDIT:

The issue is occurring only when disk size is full and when we use Truncate table. Also this is occurring only in 5.1 version but not in 5.0 version.

Our Application is having huge data and full of inserts and deletes. So in 3 weeks, the ibdata1 file reached 30 GB. So I thought we can restrict the size of ibdata1 file and changed the variable innodb_data_file_path to "ibdata1:10M:autoextend:max:32G".

Once the file reached to 32 GB, it is continuously throwing errors like table is full for every insert statement.

Please help me out of this either to compress or limit the data file size.

As we need to run our application for years.

I am using MySQL server 5.1.45. And I am having a procedure with huge business logic. With less number of invocation of this procedure, my application is working fine, but when the number of invocations are getting increased this procedure is throwing Lock wait timeout exception.

My Question is will Procedure creates temporary tables dynamically..?

As in my procedure I am using Truncate statement which may cause to release all transactions.

I am not DBA, please help me out of this.

I need a table which will allow to insert values into a column with values greater than 10. When I tried in net, they suggested to create table in following way.

CREATE TABLE test ( UID int(11) NOT NULL auto_increment, ID int(11) default NULL, CHECK (ID > 10), PRIMARY KEY (UID) );

And when I try to insert an entry with values less than 10, still values are getting inserted.

I can use Before trigger on the table to restrict insertion of values less than 10. But I wanted other approach which satisfies this condition.

I am using MySQL server 5.0.24

Please help me out of this.

I had enabled MySQL Query Log for checking which queries are getting executed from our application at certain time interval.

I wanted Logs to be refreshed for 3 days once, So I had enabled Expiry Log days as 3 days.

But when I saw the log file, the log was there from the date of enabled the Query Log which was more than a week.

Due to this the log file got increased to more than 2 GB file which was not able to get the information from it.

Is there any option to enable so that Query Log file will be refreshed/purged after some days.

I am using MySQL 5.0.24 server.

I am continuously receiving below error in event viewer... from which it stop mysql service...

This is the following error,

Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x0022401c.

When I checked MySQL error log file, there is no ERROR or WARNING message regarding crash, it is showing normal shutdown.

Can any help me out of this.

I am having a table with one column of primary key and auto increment of int(11) data type.

When I am trying to insert into that table, I am getting following error. Failed to read auto-increment value from storage engine Query

When I restart MySQL service, it is working fine.

I am sure that this is not problem with "Auto Increment Number Exceeded", as when I try to insert from outside it is getting inserted properly.

Is this problem with Auto Increment or some other one.

Whenever I am doing a backup, I get this error.

I checked the net for this error and have done the following things:

1. Create a folder in MySQL\Temp and changed the temporary directory of MySQL temp directory path to newly created one and excluded the newly created folder from Antivirus Scan.
2. Given full permissions to the newly created temporary folder.
3. Free space is allocated to that drive.

Even after doing these changes, I am not able to take a backup, and this is happening every time.

Is any thing else I had missed out for this one?