When doing a mysqldump, some tables are being skipped because mysqldump treats them as View rather than Table although they're 100% tables.

So for some tables it looks like

-- Retrieving table structure for table services...
-- Sending SELECT query...
-- Retrieving rows...

and for some ones:

-- Retrieving view structure for table users...
-- It's base table, skipped

I saw this issue was already mentioned a few times however there's no solution.


CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)


*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6638
 Avg_row_length: 713
    Data_length: 4734976
Max_data_length: 0
   Index_length: 2899968
      Data_free: 469762048
 Auto_increment: 8923
    Create_time: 2015-09-04 10:00:26
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
1 row in set (0.00 sec)

MySQL 5.5.41-0ubuntu0.14.04.1


So for some tables it looks like

Well, it should be that way for all the tables.

That message is generated by the --verbose option, and you should see it on all the tables, when mysqldump makes its second pass through the database.

Because views can't reference nonexistent objects, mysqldump iterates through all of the tables in each database, twice, if views are encountered. On the first pass, it creates a dummy table for views so that, if it turns out a view references another view, the dummy table stands in place of the target view so that the referencing view can be valid while the restoration of the backup takes place. If views were encountered, it makes a second pass, dropping each dummy table and replacing it with the actual view.

It does this by calling mysql_list_tables() in the c-client API, which sends "show tables" to the server (after switching the current database). Since this contains a list of base tables and views, mysqldump has to iterate through each one and decide what the current identifier represents -- a table, in which case, it does nothing because the table was already dumped -- or a view, in which case it drops the dummy table and creates a proper view. (Why it doesn't discover this information more intelligently is probably related to backwards compatibility with ancient versions of MySQL Server).

In short, this is normal. The --verbose messages are just telling you something you already knew. When you see these, mysqldump is iterating through the list of all tables in the database, checking whether they are views or not (for the second time).

The base tables are dumped and dummy view "placeholder" tables are created during the first pass; the base tables are skipped, placeholder tables dropped, and view definitions are dumped during the second pass.

The message -- Retrieving view structure for table users... is poorly worded, since in fact mysqldump is, at that point, checking whether the table is a view, by requesting its CREATE statement from the server, and the message appears to have given the impression in the original question that the table was first mischaracterized as a view, then omitted from the dump because it was not a view. This was an inaccurate perception, as the table had already been dumped during the first pass.

  • 1
    In original question by @Alexey Kosov , "When doing a mysqldump, some tables are being skipped because mysqldump treats them as View rather than Table although they're 100% tables" ; If I am parsing this answer correctly , that question was wrong : no real tables were skipped. It is only mysqldump resolving the tables&views in the second pass. Working as Designed
    – Prem
    Sep 6 '15 at 14:03
  • 1
    @Prem yes, that's correct. Updated to clarify. Sep 6 '15 at 17:32

Hm, I just checked and it seems MySQL dumps all the tables properly despite the weird output. So I can just close eyes on that. But if anybody knows what that means, it'd be much appreciated.


The reason a view is considered a table has to do with how INFORMATION_SCHEMA represents its existence in the database. (I mentioned this 3.5 years ago in my answer to How is it possible for mysql storage engine to be NULL?)

If you run this query

SELECT IFNULL(table_schema,'Total') db,COUNT(1) ViewCount
FROM information_schema.tables
GROUP BY table_schema

you will get how many views appear in each database along with the total views.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.