Repair MySQL Database Error | Drupal 8

Repair MySQL Database Error

Submitted by editor on Mon, 10/24/2016 - 23:37

Repair all tables
mysqlcheck -u root -p --auto-repair --check --all-databases

Repair a database
mysqlcheck -u root -p  --repair --databases databasesname

Check a database
mysqlcheck -u root -p --databases databasesname

Mysql table repair failed: “Can't create new tempfile”
myisamchk -r -f databasename/the_table.MYI

 

MYSQL: Out of resources when opening file... (Errcode: 24)

The mysql error:  Out of resources when opening file... (Errcode: 24)   indicates that the number of files that msyql is permitted to open has been exceeded.
To set this variable to a higher number, edit the /etc/my.cnf file and add the lines:

[mysqld]
open_files_limit = 5000

Then be sure to restart mysql with:   sudo /etc/init.d/mysql restart

 

Database create error (Error Code : #1146)

#1146 - Table 'database_name.table_name' doesn't exist

probably due to a corrupted file

Solution 1

(You can delete the corrupted file like ..../database_name/table_name.frm)

1.Export the database.
mysqldump -u root -p database_name > db_temp_file.sql
2. Delete the database : database_name
3. Recreate a DB wit same name (database_name)
3. Import backup file
mysql -u root -p database_name < db_temp_file.sql

Solution 2 (You can also try this solution)
mysql -u mysql_user -p
mysql> use database_name;
mysql> show tables; # If the table that's been giving you grief shows here, then you can try to run a SELECT query on it to see if any data is there, but if you get an error saying the table doesn't exist, then...
mysql> drop table table_name;
mysql> quit

 

Error: Couldn't read status information for table drupal_install_test ()

If you see an error like:
Error: Couldn't read status information for table drupal_install_test ()
mysqldump: Couldn't execute 'show create table `drupal_install_test`': Table 'MY_DATABASE_NAME.drupal_install_test' doesn't exist (1146)

Probable you have an unwanted table in your database due to X event.

Solution :
0. MAKE A BACKUP OF THE DB BEFORE ANY OPERATION (simple copy DB directory)
1. Goto the database files directory (Here : /var/lib/mysql/MY_DATABASE_NAME )
2. Locate the bade file (Here : drupal_install_test.frm) and delete it.
-> #rm drupal_install_test.frm
Then try again !!!

Comments

Net (not verified)

Thu, 01/31/2019 - 14:19

And you can also clone tables from another database if you have.

Clone a table from another database

CREATE TABLE db_target.cloned_table
SELECT *
FROM db_source.source_table;

Example:

CREATE TABLE target.cache_bootstrap
SELECT *
FROM source.cache_bootstrap;

Recreate all cash tables from another database.

drop table cache_bootstrap;
drop table cache_config;
drop table cache_container;
drop table cache_data;
drop table cache_default;
drop table cache_discovery;
drop table cache_dynamic_page_cache;
drop table cache_entity;
drop table cache_menu;
drop table cache_page;
drop table cache_render;

CREATE TABLE db_target.cache_bootstrap SELECT * FROM db_source.cache_bootstrap;
CREATE TABLE db_target.cache_config SELECT * FROM db_source.cache_config;
CREATE TABLE db_target.cache_container SELECT * FROM db_source.cache_container;
CREATE TABLE db_target.cache_data SELECT * FROM db_source.cache_data;
CREATE TABLE db_target.cache_default SELECT * FROM db_source.cache_default;
CREATE TABLE db_target.cache_discovery SELECT * FROM db_source.cache_discovery;
CREATE TABLE db_target.cache_dynamic_page_cache SELECT * FROM db_source.cache_dynamic_page_cache;
CREATE TABLE db_target.cache_entity SELECT * FROM db_source.cache_entity;
CREATE TABLE db_target.cache_menu SELECT * FROM db_source.cache_menu;
CREATE TABLE db_target.cache_page SELECT * FROM db_source.cache_page;
CREATE TABLE db_target.cache_render SELECT * FROM db_source.cache_render;

TRUNCATE TABLE cache_bootstrap;
TRUNCATE TABLE cache_config;
TRUNCATE TABLE cache_container;
TRUNCATE TABLE cache_data;
TRUNCATE TABLE cache_default;
TRUNCATE TABLE cache_discovery;
TRUNCATE TABLE cache_dynamic_page_cache;
TRUNCATE TABLE cache_entity;
TRUNCATE TABLE cache_menu;
TRUNCATE TABLE cache_page;
TRUNCATE TABLE cache_render;

TRUNCATE TABLE watchdog;

 

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.