importMySQL
I have made a mysql dump with mysqldump, tables are dropped and re-created with
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
However for a read-intensive database I sometimes get crashes connected with table "users" missing completely – this must happen when table is dropped and immediately created again. How do I make mysql wait until table is created?
It is rather easy for a MyISAM table to crash.
In the header of every MyISAM table is a counter that tracks how many open file handles there are against the table.
If you start up mysql and the number in the header does not match the number of actual file handles against, mysqld treates the table as crashed.
If a simple REPAIR TABLE mdl_user makes it work again every time without loss of data, this may indicate that you have a very highly trafficked site that writes to mdl_user.
If dozens of tables require this REPAIR TABLE, I would convert all the tables to InnoDB. However, if the mdl_user table is the only table with this problem, there is something you can do (for this example, let say the database is moodle);
echo "REPAIR TABLE moodle.mdl_user;" > /var/lib/mysql/MoodleStartUp.sql
Add this to /etc/my.cnf
[mysqld]
init-file=/var/lib/mysql/MoodleStartUp.sql
Every restart of mysql will trigger the Repair Table Script
Run this code to make a bulk conversion script of MyISAM tables to InnoDB, and view it
MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql ${MYSQL_CONN} -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql
Once you are satisfied with the conversion script's contents, then run it
mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql
@Kevin, While using MyISAM, what if running out of Disk Space is your isuue?
Here is something to consider: According to MySQL 5.0 Certification Study Guide,
bulletpoint #11 says the following on Pages 408,409 Section 29.2:
If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.
When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available.
ok, i best solution which suited me well.
for being cautious in choosing the above solutions provided in my question above..
(cuz the solutions didn't guarantee 100% that my data would be intact), i made a backup for my drupal database folder just in case.
chose solution (2), which is adding the force_recovery..but no luck in fixing the problem..
so i went to the location of the corrupted file in "C:\xampp\mysql\data\bitnami_drupal7", and moved the corrupted file to another folder (NOTE: i did not delete the corrupted file).
then started mysql service, and viola! my drupal was back to life again.
and i was able to access phpmyadmin with sql queries.
(back then, mysql couldn't execute sql queries because it was in a state where its about to crash).
i went straight to the cache tables of drupal to clear them..
(back then, i wanted to clear drupals' cache since the error started, cuz ever since i knew that mysql crashes because of a table in drupal; but mysql couldn't have enough time to execute sql queries in phpmyadmin, again cuz of the the crash).
hence, after clearing all drupal cache at "bitnami_drupal7" with this code:
TRUNCATE TABLE cache;
TRUNCATE TABLE cache_block;
TRUNCATE TABLE cache_bootstrap;
TRUNCATE TABLE cache_field;
TRUNCATE TABLE cache_filter;
TRUNCATE TABLE cache_form;
TRUNCATE TABLE cache_image;
TRUNCATE TABLE cache_menu;
TRUNCATE TABLE cache_page;
TRUNCATE TABLE cache_path;
TRUNCATE TABLE cache_token;
TRUNCATE TABLE cache_update;
then, i replaced the corrupted file back again to try if it becomes recognized or not, and started mysql..and it worked!!
mysql never crashed again and complain about that file being corrupted anymore, and that saved me from deleting or affecting any data.
Best Answer
This is a big problem with the design of your system!
You are apparently performing DDL on a running application. This is never a good idea. The one Open Source server which does support transactional DDL is PostgreSQL - from here:
MySQL even in its latest version (as of 05/2018) - verion 8, doesn't support tranactional DDL - from here:
This page goes on to say:
What, exactly, will, or will not, be rolled back isn't specified in detail - bit of a lottery situation which will require lots of testing! I wouldn't open that can of worms if I were you!
Could you possibly expand expand on your question by letting us know why, exactly, you feel that you need to issue such commands against a running application and maybe we can help you find a more suitable alternative solution?