Mysqldump treats some table as View

Question

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.

SHOW CREATE TABLE users;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8915 DEFAULT CHARSET=utf8 

SHOW TABLE STATUS LIKE 'users' \G;

*************************** 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
 Create_options:
        Comment:
1 row in set (0.00 sec)

MySQL 5.5.41-0ubuntu0.14.04.1

Answer 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.

Answer 2

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.

Answer 3

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
WHERE engine IS NULL
GROUP BY table_schema
WITH ROLLUP;

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