Bug #54316 mysqldump and restore ndbinfo fails
Submitted: 8 Jun 2010 0:03 Modified: 19 Aug 2010 10:42
Reporter: Adam Dixon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1-telco-7.1 OS:Linux
Assigned to: Magnus Blåudd CPU Architecture:Any
Tags: 7.1.3

[8 Jun 2010 0:03] Adam Dixon
Description:
After taking a mysqldump of 2 databases, test and ndbinfo, and attempting to restore them ndbinfo views fail to work, and or fail to restore.

How to repeat:
- Install Cluster 7.1.3, install MySQL node per usual, find ndbinfo database working per normal.
- Show that its working..
- mysql -u root ndbinfo -e 'select * from counters;'
+---------+------------+----------------+------------+--------------+-------+
| node_id | block_name | block_instance | counter_id | counter_name | val |
+---------+------------+----------------+------------+--------------+-------+
| 2 | DBLQH | 0 | 10 | OPERATIONS | 3032 |
| 3 | DBLQH | 0 | 10 | OPERATIONS | 2271 |
| 2 | DBTC | 0 | 1 | ATTRINFO | 15028 |
| 2 | DBTC | 0 | 2 | TRANSACTIONS | 636 |
| 2 | DBTC | 0 | 3 | COMMITS | 626 |
| 2 | DBTC | 0 | 4 | READS | 463 |
| 2 | DBTC | 0 | 5 | SIMPLE_READS | 0 |
| 2 | DBTC | 0 | 6 | WRITES | 2158 |
| 2 | DBTC | 0 | 7 | ABORTS | 10 |
| 2 | DBTC | 0 | 8 | TABLE_SCANS | 15 |
| 2 | DBTC | 0 | 9 | RANGE_SCANS | 0 |
| 3 | DBTC | 0 | 1 | ATTRINFO | 3108 |
| 3 | DBTC | 0 | 2 | TRANSACTIONS | 413 |
| 3 | DBTC | 0 | 3 | COMMITS | 407 |
| 3 | DBTC | 0 | 4 | READS | 401 |
| 3 | DBTC | 0 | 5 | SIMPLE_READS | 0 |
| 3 | DBTC | 0 | 6 | WRITES | 75 |
| 3 | DBTC | 0 | 7 | ABORTS | 6 |
| 3 | DBTC | 0 | 8 | TABLE_SCANS | 39 |
| 3 | DBTC | 0 | 9 | RANGE_SCANS | 0 |
+---------+------------+----------------+------------+--------------+-------+

Dump it..
- mysqldump -u root -d --databases test ndbinfo > /data/tmp/test_ndbinfo.sql

Restore it...
- mysql -u root < /data/tmp/test_ndbinfo.sql

Check it now;
- mysql -u root ndbinfo

mysql> select * from counters;
Empty set (0.00 sec)

mysql> select * from ndb$counters;
+---------+--------------+----------------+------------+-------+
| node_id | block_number | block_instance | counter_id | val |
+---------+--------------+----------------+------------+-------+
| 2 | 245 | 0 | 1 | 15457 |
| 2 | 245 | 0 | 2 | 679 |
| 2 | 245 | 0 | 3 | 668 |
| 2 | 245 | 0 | 4 | 503 |
| 2 | 245 | 0 | 5 | 0 |
| 2 | 245 | 0 | 6 | 2167 |
| 2 | 245 | 0 | 7 | 11 |
| 2 | 245 | 0 | 8 | 16 |
| 2 | 245 | 0 | 9 | 0 |
| 2 | 247 | 0 | 10 | 3114 |
| 3 | 245 | 0 | 1 | 3266 |
| 3 | 245 | 0 | 2 | 450 |
| 3 | 245 | 0 | 3 | 444 |
| 3 | 245 | 0 | 4 | 437 |
| 3 | 245 | 0 | 5 | 0 |
| 3 | 245 | 0 | 6 | 78 |
| 3 | 245 | 0 | 7 | 6 |
| 3 | 245 | 0 | 8 | 43 |
| 3 | 245 | 0 | 9 | 0 |
| 3 | 247 | 0 | 10 | 2289 |
+---------+--------------+----------------+------------+-------+

mysql> show create table counters;
| counters | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `counters` AS select `c`.`node_id` AS `node_id`,`b`.`block_name` AS `block_name`,`c`.`block_instance` AS `block_instance`,`c`.`counter_id` AS `counter_id`,(case `c`.`counter_id` when 1 then 'ATTRINFO' when 2 then 'TRANSACTIONS' when 3 then 'COMMITS' when 4 then 'READS' when 5 then 'SIMPLE_READS' when 6 then 'WRITES' when 7 then 'ABORTS' when 8 then 'TABLE_SCANS' when 9 then 'RANGE_SCANS' when 10 then 'OPERATIONS' else '<unknown>' end) AS `counter_name`,`c`.`val` AS `val` from (`ndb$counters` `c` join `blocks` `b`) where (`c`.`block_number` = `b`.`block_number`) | latin1 | latin1_swedish_ci |

mysql> show create table ndb$counters; |
| ndb$counters | CREATE TABLE `ndb$counters` (
`node_id` int(10) unsigned DEFAULT NULL,
`block_number` int(10) unsigned DEFAULT NULL,
`block_instance` int(10) unsigned DEFAULT NULL,
`counter_id` int(10) unsigned DEFAULT NULL,
`val` bigint(20) unsigned DEFAULT NULL COMMENT 'monotonically increasing since process start'
) ENGINE=NDBINFO DEFAULT CHARSET=latin1 COMMENT='monotonic counters' |

The above occurs every time, added to this, I rm -f the contents of the ndbinfo db, and then tried to restore the dump I get.

mysql -u root < /data/tmp/test_ndbinfo.sql
ERROR 1146 (42S02) at line 241: Table 'ndbinfo.ndb$counters' doesn't exist

Strangely enough, when I ran the same SQL provided in share/ndbinfo.sql manually to drop/create the table/view it still did not work for me (0 rows).

It looks to me that mysqldump is not handling the situation correctly.
[8 Jun 2010 0:03] Adam Dixon
mysqldump of test and ndbinfo databases

Attachment: test_ndbinfo.sql (application/octet-stream, text), 16.80 KiB.

[8 Jun 2010 8:49] Magnus Blåudd
Seems like two problems:

1. mysqldump is using a "trick" when it's dumping views. The trick is that in order to avoid problem with restoring a view that depend on another view it will precreate all views as tables. Add when the restore fails in the middle(like it did on line 241) the trick table is never replaced by the proper view and thus selecting from the view (which is still the trick table) returns the proper columns but no rows.

2. The dump does not include the hidden ndb$xxx tables, in this csase ndb$counters is missing and that is what's causing the restore to fail.
[8 Jun 2010 9:14] Adam Dixon
Just on the fact that the ndb$info tables are missing. I noticed this as well I tested again just now and I could not get these tables to be included in the dump.

I tried all sorts, but the ndb$counters table for example would never be included.
mysqldump ndbinfo ndb$counters
mysqldump: Couldn't find table: "ndb"

mysqldump -u root ndbinfo 'ndb$counters'
mysqldump: Couldn't find table: "ndb$counters"

mysqldump -u root ndbinfo ndb\$counters
mysqldump: Couldn't find table: "ndb$counters"

So mysqldump fails to handle the slightly hidden ndb$info tables of engine=NDBINFO it seems.
[8 Jun 2010 9:41] Adam Dixon
Magnus, to clarify, the table is actually on the server when taking these dumps, just that mysqldump cannot dump them. So my guess its either having a problem with the table name, or the table engine.
[8 Jun 2010 9:52] Magnus Blåudd
The ndbinfo database and it's objects should not need to be dumped or restored since the tables are hardcoded and have been compiled into the ndbd kernel.

The tables and views for ndbinfo in MySQL Server should preferrably be using the same layout and be created by mysql_install_db or the ndbinfo.sql script.
[12 Aug 2010 8:36] Magnus Blåudd
The problem described here is that ndbinfo database is dumped _without_ data using the -d flag to mysqldump. When the tables are restored it has the effect that the lookup tables which are used to present nice views to the user are empty and thus the join(see below) the views are issuing does not return any rows.

select <fields> from (`ndb$counters` `c` join `blocks` `b`) where (`c`.`block_number` = `b`.`block_number`)
[12 Aug 2010 9:06] Magnus Blåudd
Ideas for solution:

1) Currently the "blocks" table which contains a list of block numbers and corresponding block name is stored in MyISAM and is used to present block names instead of block numbers to the user. We could make it a ndbinfo table and let the handler create the table on the fly. I.e "if (table_name == 'blocks') write_hardcoded_tables(blocks)". This would avoid the need to depend on MyISAM or another engine. We already have hardcoded tables for "tables" and "columns". Same should be done for the other hardcoded table "config_params".

2) Rename blocks to ndb$blocks, that would hide the lookup table and not make it part of the dump(unless of course ndbinfo_show_hidden is set to TRUE). So this is not as universal.

3) Don't allow mysqldump to dump any tables in ndbinfo database similar to information_schema. Doing this in a similar fashion as for "information_schema" which is skipped in the case when mysqldump is run with --all-databases option. It's however still possible to dump "information_schema" or "ndbinfo" by explicitly telling mysqldump to dump those databases -> ie. could still be a problem.
[12 Aug 2010 10:41] Magnus Blåudd
4. Use LEFT JOIN so that rows are returned even though "blocks" table is empty. Not so good that these lookup tables are not readonly actually.
[13 Aug 2010 9:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115652
[17 Aug 2010 13:56] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.47-ndb-7.0.18 (revid:magnus.blaudd@sun.com-20100817134426-hseh2hx05mgzgwfb) (version source revid:magnus.blaudd@sun.com-20100817133752-niob81fpnpue9xtb) (merge vers: 5.1.47-ndb-7.0.18) (pib:20)
[17 Aug 2010 14:09] Magnus Blåudd
Pushed to 7.0.18 and 7.1.7
[18 Aug 2010 7:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116043
[18 Aug 2010 8:08] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.47-ndb-7.0.18 (revid:magnus.blaudd@sun.com-20100818080258-5ran6rl8ljptqbcd) (version source revid:magnus.blaudd@sun.com-20100818075058-g57ku8o46honoztl) (merge vers: 5.1.47-ndb-7.0.18) (pib:20)
[19 Aug 2010 10:42] Jon Stephens
Documented in the NDB-7.1.7 changelog as follows:

      It is no longer possible to make a dump of the ndbinfo database using
      mysqldump.

Closed.

(Note: We support ndbinfo only in 7.1.1+.)