| Bug #31782 | No way to detect empty tablespaces using SQL | ||
|---|---|---|---|
| Submitted: | 23 Oct 2007 10:58 | Modified: | 11 Feb 2010 15:35 |
| Reporter: | Hartmut Holzgraefe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Disk Data | Severity: | S3 (Non-critical) |
| Version: | mysql-5.1 | OS: | Linux |
| Assigned to: | Pekka Nousiainen | CPU Architecture: | Any |
| Tags: | usability | ||
[10 Feb 2009 17:05]
Hartmut Holzgraefe
Suggested fix: add one DATAFILE entries with FILE_NAME=NULL per table space
=== modified file 'sql/ha_ndbcluster.cc'
--- sql/ha_ndbcluster.cc 2009-01-29 16:27:42 +0000
+++ sql/ha_ndbcluster.cc 2009-02-10 17:01:17 +0000
@@ -12491,6 +12491,8 @@ static int ndbcluster_fill_files_table(h
uint i;
DBUG_ENTER("ndbcluster_fill_files_table");
+ // first we report the tablespace data files per node
+
dict->listObjects(dflist, NdbDictionary::Object::Datafile);
ndberr= dict->getNdbError();
if (ndberr.classification != NdbError::NoError)
@@ -12574,6 +12576,58 @@ static int ndbcluster_fill_files_table(h
}
}
+ // ... then the tablespaces ...
+ NdbDictionary::Dictionary::List tslist;
+ dict->listObjects(tslist, NdbDictionary::Object::Tablespace);
+ ndberr= dict->getNdbError();
+ if (ndberr.classification != NdbError::NoError)
+ ERR_RETURN(ndberr);
+
+ for (i= 0; i < tslist.count; i++)
+ {
+ NdbDictionary::Dictionary::List::Element& elt= tslist.elements[i];
+
+ NdbDictionary::Tablespace ts= dict->getTablespace(elt.name);
+ ndberr= dict->getNdbError();
+ if (ndberr.classification != NdbError::NoError)
+ {
+ if (ndberr.classification == NdbError::SchemaError)
+ continue;
+ ERR_RETURN(ndberr);
+ }
+
+ init_fill_schema_files_row(table);
+ table->field[IS_FILES_FILE_TYPE]->set_notnull();
+ table->field[IS_FILES_FILE_TYPE]->store("DATAFILE", 8,
+ system_charset_info);
+
+ table->field[IS_FILES_TABLESPACE_NAME]->set_notnull();
+ table->field[IS_FILES_TABLESPACE_NAME]->store(elt.name,
+ strlen(elt.name),
+ system_charset_info);
+ table->field[IS_FILES_LOGFILE_GROUP_NAME]->set_notnull();
+ table->field[IS_FILES_LOGFILE_GROUP_NAME]->
+ store(ts.getDefaultLogfileGroup(),
+ strlen(ts.getDefaultLogfileGroup()),
+ system_charset_info);
+
+ table->field[IS_FILES_ENGINE]->set_notnull();
+ table->field[IS_FILES_ENGINE]->store(ndbcluster_hton_name,
+ ndbcluster_hton_name_length,
+ system_charset_info);
+
+ table->field[IS_FILES_EXTENT_SIZE]->set_notnull();
+ table->field[IS_FILES_EXTENT_SIZE]->store(ts.getExtentSize());
+
+ table->field[IS_FILES_VERSION]->set_notnull();
+ table->field[IS_FILES_VERSION]->store(ts.getObjectVersion());
+
+ schema_table_store_record(thd, table);
+ }
+
+
+ // ... and then the undo log files per node
+
NdbDictionary::Dictionary::List uflist;
dict->listObjects(uflist, NdbDictionary::Object::Undofile);
ndberr= dict->getNdbError();
@@ -12652,7 +12706,7 @@ static int ndbcluster_fill_files_table(h
}
}
- // now for LFGs
+ // ... and finally the log file groups
NdbDictionary::Dictionary::List lfglist;
dict->listObjects(lfglist, NdbDictionary::Object::LogfileGroup);
ndberr= dict->getNdbError();
@@ -12704,6 +12758,7 @@ static int ndbcluster_fill_files_table(h
table->field[IS_FILES_EXTRA]->store(extra, len, system_charset_info);
schema_table_store_record(thd, table);
}
+
DBUG_RETURN(0);
}
[30 Sep 2009 16:04]
MySQL Verification Team
Verified in 7.0.7 as described by Hartmut
[7 Feb 2010 16:46]
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/99560 3074 Pekka Nousiainen 2010-02-07 bug#31782 02_bug31782.diff In information_schema.files return extra row for tablespace itself. It has file_type TABLESPACE (not DATAFILE) to avoid breaking mysqldump and old user scripts. Uses old patch attached to the bug#.
[9 Feb 2010 10:35]
Bugs System
Pushed into 5.1.41-ndb-7.0.13 (revid:pekka@mysql.com-20100209052415-xie31hvmbn1yhzr1) (version source revid:pekka@mysql.com-20100209052415-xie31hvmbn1yhzr1) (merge vers: 5.1.41-ndb-7.0.13) (pib:16)
[9 Feb 2010 10:38]
Bugs System
Pushed into 5.1.41-ndb-6.3.32 (revid:pekka@mysql.com-20100208163827-jjvujexbm0gyquno) (version source revid:pekka@mysql.com-20100207175934-oshzyerqxczf1lxq) (merge vers: 5.1.41-ndb-6.3.32) (pib:16)
[9 Feb 2010 10:41]
Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:pekka@mysql.com-20100208153743-1rr33j9x70a70qaa) (version source revid:pekka@mysql.com-20100207164515-zujrnbyc0s65ji1b) (merge vers: 5.1.41-ndb-6.2.19) (pib:16)
[10 Feb 2010 10:46]
Pekka Nousiainen
Pushed to: mysql-5.1.41 ndb-6.2.19 mysql-5.1.41 ndb-6.3.32 mysql-5.1.41 ndb-7.0.13 mysql-5.1.41 ndb-7.1.2 Set to documenting: INFORMATION_SCHEMA.FILES has now an additional row for each TABLESPACE itself. Previously only DATAFILEs in each tablespace were shown.
[11 Feb 2010 15:35]
Jon Stephens
Documented bugfix in the NDB-6.2.19, 6.3.32, 7.0.13, and 7.1.2 changelogs, as follows:
Once all data files associated with a given tablespace had been
dropped, there was no way for MySQL client applications
(including the mysql client) to tell that the tablespace still
existed. Top rememdy this problem, INFORMATION_SCHEMA.FILES now
has an additional row for each tablespace. (Previously, only the
data files in each tablespace were shown.) This row shows
TABLESPACE in the FILE_TYPE column and NULL in the FILE_NAME
column.
Also noted in "The INFORMATION_SCHEMA FILES Table".
Closed.
Description: The only place where tablespace and logfile group names are exposed to SQL clients seems to be the INFORMATION_SCHEMA.FILES table. As it is possible to drop all data files from a table space (and even necessary to be able to drop the tablespace) the table space becomes "invisible" on the SQL level as soon as the last data file is dropped even though the now empty table space still exists. This can lead to the situation where one administrator dropped all data files not the tablespace itself. Now the next administrator may try to drop the logfile group associated with the table space (or maybe even the same administrator if he has bad memory ;) which fails as the logfile group is still in use by the table space. So how would the administrator now find out which table space is to blame? As the table space doesn't have any files left that would show up in I_S.FILES the only source providing the TS->LG relationship is gone. As a workaround the ndb_show_tables tool can be used to retreive the names of existing log file groups and table spaces. There are two problems with this though: - ndb_show_tables only shows the object names but not the TS->LG relationships (not an issue right now due to the "one LG only" limitation but once that is fixed it will become an issue again) - the user may not have the privileges to execute ndb_show_tables How to repeat: on a fresh cluster: mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' ENGINE NDB; Query OK, 0 rows affected (51.95 sec) mysql> mysql> CREATE TABLESPACE ts_1 ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 ENGINE ND B; Query OK, 0 rows affected (34.10 sec) mysql> mysql> SELECT DISTINCT(TABLESPACE_NAME) FROM INFORMATION_SCHEMA.FILES; +-----------------+ | TABLESPACE_NAME | +-----------------+ | ts_1 | | NULL | +-----------------+ 2 rows in set (0.02 sec) mysql> mysql> ALTER TABLESPACE ts_1 DROP DATAFILE 'data_1.dat' ENGINE NDB; Query OK, 0 rows affected (0.50 sec) mysql> mysql> DROP LOGFILE GROUP lg_1 ENGINE NDB; ERROR 1526 (HY000): Failed to drop LOGFILE GROUP mysql> SHOW WARNINGS; +-------+------+-----------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------+ | Error | 1296 | Got error 768 'Cant drop filegroup, filegroup is used' from NDB | | Error | 1526 | Failed to drop LOGFILE GROUP | +-------+------+-----------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> SELECT DISTINCT(TABLESPACE_NAME) FROM INFORMATION_SCHEMA.FILES; +-----------------+ | TABLESPACE_NAME | +-----------------+ | NULL | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> DROP TABLESPACE ts_1 ENGINE NDB; Query OK, 0 rows affected (0.39 sec) mysql> mysql> DROP LOGFILE GROUP lg_1 ENGINE NDB; Query OK, 0 rows affected (0.66 sec) Suggested fix: Provide a way to retreive information on tablespaces without attached data files on the SQL level, e.g. by - adding an extra entry with FILE_TYPE=DATAFILE and FILE_NAME=NULL containing the TABLESPACE_NAME and LOGFILE_GROUP_NAME for each table space, like we have for LOGFILE GROUPS already - optionally adding convenience SHOW commands: SHOW LOGFILE GROUPS ENGINE=NDB and SHOW TABLESPACES ENGINE=NDB;