MySQL – How to Recover Truncated Table

MySQL

I am getting the "wrong input table name" when use TRUNCATE as following:

TRUNCATE `stats`

The table should bans.

How to recover stats table?

I am using CentOS 6, and MySQL-Server.

Best Answer

If you have binary logging enabled, you can replay changes that went into creating the stats table, up to the point where you did the TRUNCATE.

But you may not have binary logs for all changes to that table from the beginning of time. It's common to have binary logs only back a few days. Any data that was created in that table prior to your oldest binary log is not recoverable.

Figuring out the location in the binary log where you need to stop replaying (the TRUNCATE statement) is a bit tricky. Basically, use mysqlbinlog to dump the contents of that log as a SQL script, then open that script in an editor and delete everything from the TRUNCATE statement to the end.

See also https://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html for more tips on using mysqlbinlog for recovery.

Replaying only the changes to one specific table is also a bit tricky. Here's one solution proposed by long-time MySQL expert Giuseppe Maxia: http://datacharmer.blogspot.com/2010/11/filtering-binary-logs-with-mysql.html

You could also filter the SQL script produced from the binary log using pt-query-digest, and find only log events that reference your stats table.

If you don't have binary logs back far enough, none of that will help though. You could try to use an undelete tool for your filesystem (you don't say if you're on Linux or Windows or other), but the longer you wait, the more likely the space on disk is going to be recycled and used for other files.

i can't recover from .frm file? – Putra Fajar Hasanuddin Mar 12 at 6:20

No, the .frm file contains only the table definition, but no data. And since you just used TRUNCATE TABLE and not DROP TABLE, you still have easy access to the table definition anyway.

I'm sorry if you have suffered unrecoverable data loss, but I hope this has at least convinced you of the importance of regular database backups.

Related Solutions

MySQL – Cannot Create Database with Name ‘5e370227_db’

Interesting problem and I think that I've figured it out.

For some reason, MySQL interprets 1e_a_number_ as an exponent - i.e. 10 to the power of something. 1a_a_number has no mathematical meaning, therefore it's not parsed as being a number and accepted as valid. If you notice at the beginning of my experiments below - pure numbers aren't allowed as database/schema names.

Congratulations - it looks like you've spotted a MySQL bug.

Something like this (underscore following number and e) works.

MariaDB [(none)]> create schema 1_e34343_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>

which appears to confirm my conclusion.

This does also - i.e. 1ex works, but 1e1 fails.

MariaDB [(none)]> create schema 1ex;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create schema 1e1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e1' at line 1
MariaDB [(none)]>

=== Experiments leading me to my conclusion ============

MariaDB [(none)]> create schema 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create schema  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop schema  5a370227_db;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> create database  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop database  5a370227_db;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database 100;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '100' at line 1
MariaDB [(none)]> create database 100_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1e0000000000000000000000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000000000000000000000000000_db' at line 1
MariaDB [(none)]> create database 1e000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e000000000000000_db' at line 1
MariaDB [(none)]> create database 1e0000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000_db' at line 1
MariaDB [(none)]> create database 1e4_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_db' at line 1
MariaDB [(none)]> create database 1e4_zx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_zx' at line 1
MariaDB [(none)]> 
MariaDB [(none)]> create database 1a4_zx;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>

Mysql – Slow “CREATE TABLE” and “TRUNCATE” on MySQL / MariaDB

I experimented with this and found something very interesting.

Running Fedora release 20 (Heisenbug) 3.11.10-301.fc20.x86_64, 2GB RAM (I know!), 2 processor Intel centrino.

If you enable the Performance Schema (P_S), the times seem to drop dramatically. Why this is, I simply don't know - the P_S is for monitoring, and not changing anything. I have put what I put in my my.cnf at the end.

Could you test this for your 3 scenarios (CREATE, TRUNCATE & DELETE) running your server using the new my.cnf. If you obtain similar results to mine, then we can investigate further.

Strangely, as far as I can see, the client reports a time of approx 0.3 seconds for me for all of my experiments - but the results from the profiling seem very different. Still puzzling through this one.

Below are my results just for the CREATE table statement before and after enabling the performance schema.

Before enabling P_S.

mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000290 |
| checking permissions | 0.000021 |
| Opening tables       | 0.000141 |
| creating table       | 0.365769 |  <<====
| After create         | 0.000037 |
| query end            | 0.000023 |
| closing tables       | 0.000018 |
| freeing items        | 0.000114 |
| cleaning up          | 0.000030 |
+----------------------+----------+
9 rows in set, 1 warning (0.00 sec)

After enabling P_S

mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000188 |
| checking permissions | 0.000131 |
| query end            | 0.000015 |
| closing tables       | 0.000020 |
| freeing items        | 0.000068 |
| cleaning up          | 0.000052 |
+----------------------+----------+
6 rows in set, 1 warning (0.00 sec)

MariaDB

Before enabling P_S

Query OK, 0 rows affected (0.33 sec)

MariaDB [test]> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000321 |
| checking permissions | 0.000024 |
| Opening tables       | 0.000073 |
| After opening tables | 0.000017 |
| System lock          | 0.000011 |
| Table lock           | 0.000019 |
| After opening tables | 0.000056 |
| creating table       | 0.328769 |  <<======
| After create         | 0.000043 |
| query end            | 0.000026 |
| closing tables       | 0.000022 |
| freeing items        | 0.000043 |
| updating status      | 0.000050 |
| cleaning up          | 0.000039 |
+----------------------+----------+
14 rows in set (0.00 sec)

After enabling P_S on MariaDB

MariaDB [test]> show profile for CREATE TABLE cache_bootstrap
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000311 |
| checking permissions | 0.000024 |
| Opening tables       | 0.000044 |
| After opening tables | 0.000038 |
| System lock          | 0.000014 |
| Table lock           | 0.000012 |
| After opening tables | 0.000347 |
| After create         | 0.000015 |
| query end            | 0.000014 |
| closing tables       | 0.000021 |
| freeing items        | 0.000014 |
| updating status      | 0.000026 |
| cleaning up          | 0.000034 |
+----------------------+----------+
13 rows in set (0.00 sec)

To be added in the [mysqld] section of my.cnf

[mysqld]

performance_schema
performance_schema_instrument = '%=on'
performance_schema_consumer_events_stages_current = ON
performance_schema_consumer_events_stages_history = ON
performance_schema_consumer_events_stages_history_long = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_waits_history_long = ON

Best Answer

Related Solutions

MySQL – Cannot Create Database with Name ‘5e370227_db’

Mysql – Slow “CREATE TABLE” and “TRUNCATE” on MySQL / MariaDB

Related Question