While the answer above is true in that a SELECT … FOR UPDATE will prevent concurrent sessions / transactions from inserting the same record, that is not the full truth. I am currently fighting with the same problem and have come to the conclusion that the SELECT … FOR UPDATE is nearly useless in that … Read more
Something has deleted your ibdata1 file where InnoDB keeps the dictionary. Definitely it’s not MySQL who does
The ibtmp1 once created can’t be shrink by any method without restarting mysql service. There are two ways to handle it: Precaution : At the time of server start you should limit the size of this file as: innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G where max 5G means this file size limits to 5GB. Cure : If file … Read more
Old questions, but following is a possible way around. Basically move the data rather than renaming the tables. You need to of course make sure the new data adhere to the foreign key rules. SET foreign_key_checks = 0; CREATE TABLE IF NOT EXISTS foo_old LIKE foo; INSERT INTO foo_old SELECT * FROM foo; TRUNCATE foo; … Read more
First of all stop the server and image the disc. There’s no point only having one shot at this. Then take a look here.
Yes you can. You just need to make that column be an index. CREATE TABLE `test` ( `testID` int(11) NOT NULL, `string` varchar(45) DEFAULT NULL, `testInc` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`testID`), KEY `testInc` (`testInc`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; insert into test( testID, string ) values ( 1, ‘Hello’ ); insert into test( … Read more
What jumps out immediately at me is MyISAM. ASPECT #1 : The JOIN itself Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up having table-level lock behavior instead of row-level locking because of MyISAM’s involvement in the query and MVCC cannot be applied to the MyISAM data. MVCC cannot even be … Read more
Kindly tell me, In this situation what engine I have to use for improve performance? The performance of each storage engine will depend on the queries you perform. However, be aware that different tables within the same database can use different storage engines. Why MyISAM engine does not support foreign key relationship and InnoDB does? … Read more