The system tables are the implementation of database objects. So if you, say, call a function foo()
, the DBMS looks in pg_proc
to see if there is a function foo
and what the arguments and the source code and so on are. The layout and arrangement of the system catalogs are merely the way the implementors of various features over time made them. You already pointed out the documentation of the catalogs. In many cases, there is a simple mapping, say, between a function and pg_proc
. But in other cases, such as for an index, it's a bit more complicated. You will have to dig that information out of the documentation or perhaps the many examples of system catalog queries flying around.
The information schema is specified by the SQL standard. The principle there is, if you enter these DDL commands, then a query of the information schema should give these results. In many cases, there is again a simple mapping between objects and information schema views, but it's not straightforward in all cases. So maintaining a separate documentation of the mapping of this information would be cumbersome and probably useless. The principle is DDL in, information schema out, not what happens in the PostgreSQL system catalogs.
Ultimately, if you want to know whether a sequence would be found in a catalog table or an information schema view, you need to query these tables. The reality is too complicated for this to be simpler. (I think. Send a patch if you have a better idea.)
Check the MySQL error log. It looks like trying to read table 44 is actually crashing the server.
"Lost connection to MySQL server during query" is (or should be) heart-stopping time for a MySQL DBA because it often means that whatever your query just did has actually crashed the server.
The subsequent messages seem to bear this out:
mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when
trying to connect
Operation failed with exitcode 2
A quick test of mysqldump on Windows 7 against a MySQL server that is not running returns exactly that same error and exits with %ERRORLEVEL%
set to 2. The nature of error 10061 can be found with the perror
utility.
C:\>perror 10061
Win32 error code 10061: No connection could be made because the target machine actively refused it.
...so, check the MySQL error log. What's likely happening is that you're hitting corruption severe enough that in spite of innodb_force_recovery
, the MySQL Server is crashing. I will then usually restart on its own, but this takes time so may not complete before mysqldump gets an IP connection refused on the subsequent connection attempts. This should all show up in the error log.
I assume that when the dump does not yield any error, the table's data is non-corrupted.
Incorrect assumption. If the dump does not yield any error, it could also mean that that innodb_force_recovery
allowed the server to retrieve some of the data. A "successful" dump of a table only means it's not corrupted so severely that innodb_force_recovery
can't survive it.
1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name
jump over corrupt index records and pages, which helps in dumping tables.
http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
Again, you should see chatter from InnoDB in the MySQL error log.
Final suggestion, don't use workbench. Use the mysql
command line client to explore the server schemata and use mysqldump
directly from the command line to try to restore individual schemas or individual databases.
update/additional:
how do you recover stored procedures? I get an error because they access corrupt tables
It shouldn't be because the procedures access corrupt tables, because stored procedures and functions aren't validated against their referenced tables in the way views are (drop a table that a view references, and then SHOW CREATE VIEW
doesn't work -- the same isn't the case with procs or functions)... MySQL doesn't care whether tables referenced by stored procedures and functions even exist (except at runtime)... however, unlike view definitions, which are stored in individual files, the definitions of procedures and functions are stored in the proc
table in the mysql
schema, which is a MyISAM table... so I would expect that you'd see error messages referencing that table and CHECK TABLE mysql.proc;
and REPAIR TABLE mysql.proc;
might get you going again. Note that the version of MySQL Server that you're using for your recovery needs to be the same release series (e.g. 5.5) or the table definition of mysql.proc
will be wrong... that shouldn't be the issue here, since if I read the question correctly, the crashed server and the recovery server are both 5.5.31.
If you can SELECT * FROM mysql.proc
then you can retrieve the definitions that way if for some reason the SHOW CREATE PROCEDURE
statement doesn't work.
And what options for a (partial) rescue are there for the tables that yielded an error in the dumps?
It depends on the nature and scope of the errors encountered. One option, discussed here, involves repeated INSERT ... SELECT ... LIMIT ... OFFSET
, extracting blocks of rows from readable pages into a MyISAM table (because you can't write to an InnoDB table while innodb_force_recovery
is on). There's also the Percona Data Recovery Tool for InnoDB, though I have never had occasion to use it.
Best Answer
The INFORMATION_SCHEMA database is made up of temporary tables using the MEMORY storage engine.
Example: Here is the table INFORMATION_SCHEMA.TABLES in MySQL 5.5.12 (Windows Version)
There is no physical folder for those tables, not even .frm files. You cannot mysqldump it. You cannot drop it. You cannot add tables to it. You cannot drop tables from it. So, where are the tables ???
All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.
The INFORMATION_SCHEMA database was first introduced in MySQL 5.0 to give you access to metadata about tables of other storage engines. For example, you could do SHOW DATABASES to get a list of databases. You could also query for them like this:
You could retrieve table names in a database in two ways:
or
Since its inception, MySQL has expanded the INFORMATION_SCHEMA database to have the processlist (as of MySQL 5.1). You can actually query the processlist looking for long running queries that are still running at least 10 minutes:
You can use the INFORMATION_SCHEMA to do every elaborate things: such as :
Get counts of all tables using specific storage engines:
Get the recommended MyISAM Key Buffer Size in MB
Get the recommended InnoDB Buffer Pool Size in GB
Get Disk Usage of all Databases By Storage Engine in MB
Believe me, there are still more wonderful uses for INFORMATION_SCHEMA that time does not permit me to discuss further.
Please keep in mind that the INFORMATION_SCHEMA is so sensitive that if mysql is running and you do the following:
and then go into mysql run
You will see junkfolder as one of the databases.
Knowing it is very vital for DBAs and Developers. Chapter 20 (developers) and Chapter 31 (DBAs) of the book MySQL 5.0 Certification Study Guide
are there for preparing for the Developer and DBA Certification Exams. Get the book, study those chapters well, and you could do great things with MySQL's INFORMATION_SCHEMA.
The INFORMATION_SCHEMA database as of MySQL 5.5, now features plugins, global variables (status and static), session variables (status and static), storage engine status, performance metrics instrumentation, trigger map, events (programmable) and much more.
Sorry this may seem like WTMI but I am a big proponent of using the INFORMATION_SCHEMA database.