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