Ошибка 1146 что это

18 ответов

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

Are you trying to figure out MYSQL replication error 1146?

This is a generic error when we set up the MYSQL replication.

Also, the main reasons for the MYSQL replication error 1146 due to invalid MySQL queries or non-existing SQL queries.

At Bobcares, we often get requests to solve such MYSQL replication errors as part of our Server Management Services.

Today, let’s analyze the cause and see how our Support Team fix it for our customers.

What is MYSQL replication?

Normally, it is a process that enables data from one MySQL database server (the master) to copied automatically to one or more MySQL database servers (the slaves). However, general principles of setting up the MySQL master-slave replication on the same machine are the same for all operating systems.

It is simply copying data from one server to another server. So all the users can share the same data without any inconsistency.

The main Advantages of MySQL Replication includes,

1. High Performance
2. Backup and Security
3. Remote Access

Reasons for MYSQL replication error 1146.

This is a generic error when we discover the slave MySQL server is having a problem replicating data from the master. The main reason for this error is due to invalid MySQL queries or non-existing mysql queries.

Recently one of our customers contacted us with this MYSQL error when he tried to set-up the MYSQL replication. Then he checked the slave status and returned the error code like this.

The above error message shows that the table “db3.table3” does not exist in the slave database. To fix this error, we just simply ignore this error and resume the replication.

How we fix the MYSQL replication error 1146.

So far, we discuss the MYSQL replication and reasons for the replication error 1146. Now let’s see how our Support Engineers fix this error for our customers.

Solution 1

To fix the replication error we follow the below steps.

1. First, we log into the MYSQL.

mysql -u root -p

2. On the MySQL shell, we check the slave status.

mysql> SHOW SLAVE STATUS

The sample result as follows.

mysql> SHOW SLAVE STATUS
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001089
Read_Master_Log_Pos: 269214467
Relay_Log_File: slave-relay.000234
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: mydb
Last_Errno: 1146

If anyone of the Slave_IO_Running or Slave_SQL_Running is set as NO, it means the replication is broken.

So, we start to repair the MYSQL replication.

3. For that, we stop the slave from replication, using the below command.

mysql> STOP SLAVE;

4. Next, we tell the slave to simply skip the invalid SQL query. So we use the below command.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

This query tells the slave to skip one query (which is the invalid one that caused the replication to stop).  If we like to skip two queries, we use the following code instead.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;

That’s it.

5. Again, we start the slave.

mysql> START SLAVE;

6. After that, we check if replication is working again.

mysql> SHOW SLAVE STATUS

mysql> SHOW SLAVE STATUS
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001089
Read_Master_Log_Pos: 269214467
Relay_Log_File: slave-relay.000234
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
Last_Errno: 1146 

Both Slave_IO_Running and Slave_SQL_Running are set to Yes now. And the replication is running without any error.

Then we leave the MySQL shell.

mysql> quit;

Solution 2

Recently another customer contacted us with the MYSQL replication error. He also tried to set up the MYSQL replication and returns the error as follows.

[ERROR] Slave I/O: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist, Internal MariaDB error code: 1146

Next, he tried mysql_upgrade` and it looked already up to date, so he tried –force, which gave:

mysql.gtid_slave_pos
Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status: Operation failed

Then he checked the dB/mysql folder and found that the .frm and .ibd is already existing.

gtid_slave_pos.frm
gtid_slave_pos.ibd

Then we just removed the files and recreated the table, which solved the error.

1. So we go to the mysql folder and drop the following files using the command.

rm gtid_slave_pos.frm

rm gtid_slave_pos.ibd

Also, to create the table we use the command:

CREATE TABLE table_name (column_name column_type);

2. Then, we stop and start the slave

mysql]> stop slave;

mysql]> start slave;

This fixes the error.

[Need more assistance for MYSQL replication error 1146? We’ll help you]

Conclusion

In short, the main reasons for the MYSQL replication error 1146 is invalid MySQL queries or non-existing SQL queries. Today, we saw how our Support Engineers fix this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Добавить комментарий

We may encounter mysqldump error 1146 table doesn’t exist while we perform the database dump.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related MySQL queries.

In this context, we shall look into how to fix this error in Plesk and Directadmin.

Nature of mysqldump: Got error: 1146: Table doesn’t exist

Recently, while performing the database dump, some of our users notice the error:

mysqldump: Got error: Table ‘myDatabase.table’ doesn‘t exist when using LOCK TABLES

In order to check, we go to MySQL:

mysql -u admin -p

Then we query for the tables:

show tables;

Here, we can find the table. However, when we query for that particular table:

select * from table

We get the same error:

ERROR 1146 (42S02): Table 'myDatabase.table' doesn't exist

We can try to repair it via:

mysqlcheck -u admin -p --auto-repair --check --all-databases

However, the error may prevail:

Error : Table 'myDatase.table' doesn't exist

The main causes of error 1146: Table doesn’t exist:

• InnoDB tablespace might have been deleted and recreated but corresponding .frm files of InnoDB tables from the database directory were not removed, or .frm files were moved to another database.
• Incorrect permissions and ownership on table’s files in MySQL data directory.
• A corrupt table data.

How to fix mysqldump: Got error: 1146: Table doesn’t exist ?

On Plesk

1. Initially, we try to connect to the server using SSH

2. Then we try to use —skip-lock-tables parameter with mysqldump to skip lock tables.

For example,

#mysqldump –skip-lock-tables -u<db_user> -p<db_user_password> database_name > /root/database_dump.sql

3. If it does not help, we check permissions and ownership on the table’s files in the MySQL data directory for the database that fails to dump. It should be mysql for both owner and group:

i. Find data dir location:

RHEL/CentOS

#grep datadir /etc/my.cnf

datadir=/var/lib/mysql

Debian/Ubuntu

#grep -iR datadir /etc/mysql*
/etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysql

ii. Check permissions:

# ls -la /var/lib/mysql/example_db/

iii. Fix permissions:

# chown -R mysql:mysql /var/lib/mysql/example_db/

4. If it is still not possible, we try to repair the table in the error using the native MySQL repair tool:

# plesk db
mysql> use example_db;
mysql> REPAIR TABLE <TABLENAME>;

Note: We need to replace the <TABLENAME> with table name in the error message.

5. If the issue still persists, most probably ibdata* file does not have the info about the table. However, the orphaned .frm files still persist on the file system. We remove it:

i. To verify that table is corrupt or not, we run:

# plesk db
mysql> use database example_db;
mysql> desc <TABLENAME>;

If this command fails with the error, it means that ibdata* does not have the information about the table and we need to remove the .frm file.

ii. To do so, we browse to the database directory /var/lib/mysql/example_db/ and move .frm file:

# cd /var/lib/mysql/example_db/
# mv <TABLENAME>.frm /root/<TABLENAME>.frm

6. If these options fail and we have no valid backups to restore, the only available option to save the database is to dump it with the innodb_force_recovery option.

On Directadmin

Suppose, we get the error for the User database and Table:

mysqldump error output: mysqldump: Got error: 1146: Table ‘user_db.table‘ doesn’t exist when using LOCK TABLES

1. In this case, we check to see if there are any other data files, or if it’s just the .frm file:

cd /var/lib/mysql/user_db
ls -la table.*

If it’s just the table.frm file, then the rest of the data is likely lost. However, we may be able to rebuild the table.

2. To do so, we need to read the .frm file. We need the mysqlfrm tool for that, eg: yum install mysql-utilities. Once we install it, we check if it can be read:

mysqlfrm –diagnostic table.frm

This can output the full CREATE TABLE syntax.  We save this somewhere, until the end of the last ; character.

Note, we can either delete the “CHARACTER SET “,  or change it to the correct charset.

3. Then, we remove the broken table.  To do so, we login to /phpMyAdmin and run the query:

DROP TABLE user_db.table

4. Finally, we run the CREATE TABLE query from above, to rebuild the table.

[Need to fix Mysql errors? We’d be happy to assist you. ]