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: | |
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
[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+.)