When does an execution plan not show in sp_WhoIsActive?

How do I get the execution plans when they don't display here?

One option could be to capture the plan_handle and then look up the query plan afterwards for that plan_handle using the following query:

SELECT CONVERT(XML, query_plan) from sys.dm_exec_text_query_plan(
    0x0600050059E32C0/*Truncated for brevity, replace with your full plan_handle*/,
    DEFAULT,
    DEFAULT
)

You can capture the plan_handle using the query in the Reason #1 section below or you could make a small edit to sp_whoisactive to expose it in the results. (It's already captured in the intermediate #sessions table that is used within sp_whoisactive).

In each of the cases where I observed a NULL query plan, the query plan was available shortly afterwards (< 1 second in all my cases) via this method.

  Would putting in dummy values into these parameters and running the query with the actual execution plan included generate the same plan that was actually used when I was logging?

This could give you an idea of what the plan might look like, and if the RetrievedFromCache property of the query plan is true, you are very likely seeing the same plan that was being used.

  Why don't the query plans display in sp_whoisactive?

There are at least a few possible reasons why a plan might not show up, but I haven't observed this with the same frequency that you report, and it will be interesting to see any other answers that fill in the gaps.

Reason #1: sys.dm_exec_requests reports a statement_start_offset of -1

First, let's take a look at a simplified version of the section of sp_whoisactive that looks up query plans:

SELECT t.text,
    r.plan_handle,
    r.statement_start_offset,
    r.statement_end_offset,
    query_plan = (
        SELECT CONVERT(xml, query_plan)
        FROM sys.dm_exec_text_query_plan (
            r.plan_handle,
            r.statement_start_offset,
            r.statement_end_offset
        )
    )
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1

And here is a sample result from my server:

Based on the documentation, it does not seem like -1 is an expected value for sys.dm_exec_requests.statement_start_offset. But it does seem to appear somewhat frequently, and the sp_whoisactive procedure does not make an attempt to handle this situation.

In my testing, I'm not even sure if it would be possible to do so; even when calling sys.dm_exec_text_query_plan with the default values (to find the whole plan, not the specific plan based on the offset), I found that no plan was provided when sys.dm_exec_requests.statement_start_offset was -1.

I think there is the possibility of a situation where the request exists, but the plan and current statement offset are not yet available. I do not know the full explanation for this behavior though. Since a plan_handle is available (at least in my cases with a NULL plan), I don't think it's the case that plan compilation is still happening.

Reason #2: It took more than 5ms to find the query plan

The full implementation of sp_whoisactive actually uses a cursor to look up each plan one at a time. There is a LOCK_TIMEOUT of 5ms to avoid delaying the entire procedure too much in case a lock is encountered when looking up a particular plan.

--Wait up to 5 ms for the SQL text, then give up
SET LOCK_TIMEOUT 5;
WHILE @@FETCH_STATUS = 0
...

However, the output for sp_whoisactive should show <timeout_exceeded /> in the query_plan field in this case. I've seen this a few times, so I think that a NULL value is more likely reason #1 in your case.

Reason #3: The query plan XML is too complex

This doesn't seem likely in your case if the query text is as simple as your example. However, it looks like there is also the possibility of a NULL query plan being shown due to the XML being too complex.

However, from looking through sp_whoisactive, it looks like this case is handled with a message Could not render showplan due to XML data type limitations. and instructions to convert the raw XML into a .sqlplan file.


T-SQL query to merge all rows into one row

For an arbitrary number of columns into a single row, you would have to build the dynamic SQL yourself.  There may be cleaner ways than this, but the below is a start point.

Note that, unless the query optimiser can do something nice for you (which I doubt), it will scan the original data a huge number of times (at least 2 + colcount * rowcount):

DECLARE @query AS NVARCHAR(MAX);

SELECT @query = N'
    WITH r AS (SELECT _r = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), * FROM #mytable)
    SELECT
';

;WITH
 r AS (
    SELECT r = CONVERT(SYSNAME, ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
    FROM #mytable
 )
,c AS (
    SELECT c = REPLACE(name, ']', ']]')
    FROM sys.dm_exec_describe_first_result_set('SELECT * FROM #mytable', '', 0)
 )
SELECT @query = @query + STUFF((SELECT N',[' + c + N'_' + r + N'] = (SELECT [' + c + N'] FROM r WHERE _r = ' + r + N')' + NCHAR(10)
                                FROM r, c
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
                                1, 1, N'')

PRINT(@query);
EXECUTE sp_executesql @query;

This kind of thing is much-better handled in a presentation layer, rather than at the database layer.  C#/.NET could munch through an IDataReader and represent to a one-row HTML table far more reliably than SQL:

using (var conn = new SqlConnection("Data Source=.; Integrated Security=SSPI"))
using (var cmd = new SqlCommand("SELECT * FROM myRealTable;", conn))
using (var reader = cmd.ExecuteReader())
{
    var htmlTableHeader = new StringBuilder();
    var htmlTableRow = new StringBuilder();
    var rownum = 1L;

    while (reader.Read())
    {
        for (var f = 0; f < reader.FieldCount; f++)
        {
            htmlTableHeader.AppendFormat("{0}_{1}	",
                                         WebUtility.HtmlEncode(reader.GetName(f)),
                                         rownum)
                           .AppendLine();

            htmlTableRow.AppendFormat("{0}	",
                                      reader.IsDBNull(f)
                                        ? "-"
                                        : WebUtility.HtmlEncode(reader.GetValue(f).ToString()))
                        .AppendLine();
        }
        ++rownum;
    }

    Console.WriteLine("

");
    Console.WriteLine(htmlTableHeader.ToString());
    Console.WriteLine("

");
    Console.WriteLine("

");
    Console.WriteLine(htmlTableRow.ToString());
    Console.WriteLine("

");
}


Single table or multiple table design when records have a clear distinction but are queried together?

I won't put all of them together. Even if you query them together, it may occur that your application will be able to post multiple images per post or multiple events per post or multiple posts per event.

In this case you would be able to save space. The JSON may be a good idea, but it would be better (in view of indices) to use multiple tables. This way you can provide indices which may improve the query process.

Another idea could be to use permutation. Which means you have a global table which holds many different types with the same structure. For example:

CREATE TABLE types(
    id int,
    type nvarchar(50),
    property1 int,
    property2 nvarchar(50)
)

This way you can store a value with Type = 'Image' which holds a property1 (order number) and a property2 (a path). And additionally you can hold an event like Type = 'Event' which holds the date in property1 and the name in property2. Just as an example.


Understanding `dbcc showcontig` Record Size

When you're storing data that is bigger than what fits into the SQL Server's 8k page, like varbinary(max) or varchar(max), it's stored in an row overflow data structure. It seems showcontig doesn't show this data at all according to books online:

  DBCC SHOWCONTIG does not display row-overflow storage information and
  other new off-row data types, such as nvarchar(max), varchar(max),
  varbinary(max), and xml.

It seems showcontig is deprecated too.  Its replacement sys.dm_db_index_physical_stats can be setup to show storage of "off row" data.  SAMPLE or DETAIL seems to show this data best.


Master and slave tables with different character sets - will replication break?

https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html describes the supported variations in table definitions.

For one, it appears that this is supported with statement-based replication:

  When using statement-based replication, a simple rule of thumb to follow is, “If the statement run on the master would also execute successfully on the slave, it should also replicate successfully”.

With row-based replication, only certain type conversions are supported, which are affected by the slave_type_conversions variable.  The key quotes according to my reading are:

  [Conversions are supported between] any of the string types CHAR, VARCHAR, and TEXT, including conversions between different widths.

However:

  [Row-based] Replication between columns using different character sets is not supported.


Does alter column change datatype use tempdb version store?

Assuming we're talking about a plain ALTER TABLE, not an operation done through some wizard that does temp table and rename tricks or anything similar.

A straight column data type change may result in update of every row in the table. It depends on the types involved. You have to consider this: the row image in the table is just a bag of bytes, until is somehow interpreted by the engine. The engine follows the table metadata description to make sense of what those bytes are. The metadata will say 'from offset 12 to offset 16 is an int, the column number 4. From offset 17 to offset 19 is a short, the column number 5' and so on and so forth. Now, when you do an ALTER that changes a column type, the metadata will change. And now the $1M question: will the new metadata describe the same bag of bytes correctly? If yes, then the row ALTER is instantaneous and all the rows in the table remain unchanged. If not, then all rows have to be updated. A huge table will lead to a huge update, all in one transaction. This update can exhaust your resources (eg. run out of log space). Shanky is right that this 'update' may take the form of a temp table->insert, but that's an implementation detail.

I wrote some time ago about SQL Server table columns under the hood which shows how some of these operations occur, and how they leave evidence in the actual physical rowset metadata.

Now, you say that you are running out of version store space when this operation is done, even if snapshot was not enabled, right? The version store is used by more than just snapshot isolation. To give a well known example, the DELETED pseudo-table in triggers is powered from the version store. Which means that the version store may be used even if you did not enable snapshot isolation. See Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?), Sunil quotes 3 non-snapshot related uses of the version-store: triggers, MARS and online index build.

SQL 2014 does not support online alter column, and as a DDL operation it should not require row-versioning for trigger nor for MARS. So IMHO it should not grow the version store.

My main suspect would be, in this case, a readable secondary (it's a shot in the dark here...). The fact that readable secondaries map all reads to snapshot isolation is well documented. I don't remember exactly the details and I may be wrong, but I think that in order for the secondary database to support snapshot isolation, the primary has to use the version store. Think about the fact that the secondary is a  physical copy of the primary and the row size has to match.

Perhaps not an answer, but too long for a comment.


Explanation requested for slow DELETE with SQL Server


Need help on best database design for quick retrieval of data

A modern relational database should be able to handle 10M records in a table with no particular problem. You may have to spec the hardware memory up if your queries aren't performant. I think the most expensive part of this would be the initial ingest, where you have to parse, organize, and import the CSVs. If you get new data regularly in the form of CSVs, that may be a concern, but that can usually be addressed by writing the ETL code in something efficient like Go.

My next suggestion would be using something non-relational. I see where you do joins in these datasets, but a non-relational dataset that just had records of a specific part in a specific store might not be unreasonable. I know MongoDB or other NoSQL options would likely work for this use case. NoSQL databases work very well with large amounts of data, but don't let you do much in terms of joins, but if this is the extent of your dataset, that very well might work for you.

I tend to lean toward Elasticsearch for datasets like these. Calling it a database might start a fight in certain circles, and this isn't the primary intended use for Elasticsearch, but I've found it work well as a sort of NoSQL database. It's search API is easy to integrate with applications and it's very easy to scale up to the performance you need.

tl;dr:
Don't knock a relational database with three tables like you describe--I think it'd work fine. But if that's the only join you'll ever need, flatten it and put it in a NoSQL database like Mongo, which should make it easier to achieve high performance. Depending on what you do with the data, Elasticsearch might be worth looking into.

I realize this is a rather general answer, but it's a rather high-level question, so I apologize.


Joining of two tables, one containing a large amount of data. Need help optimising the query

So I re-wrote your query (under a couple of assumptions) to hopefully set it up for "great success". The three main things the below queries do are:
1: Pre-filter the data from Ztest_Participation into a temp table. By doing this filtering upfront you simplify your final INSERT query for the query optimizer by pre-materializing just the data you need to JOIN to first. Also materializing it into a temp table can leverage some of the performance benefits in TempDB later on (such as the potential of the data being materialized in memory).
2: The final INSERT query leverages the TABLOCK hint which results in less log generation and removes the potential of many row lock escalations, leading to optimized performance on INSERT. (Additional information on the benefits of TABLOCK here.)
3: I simplified your JOIN query between #Ztest_Participation_Filtered and Ztest_nominal by using a better relational solution. This should also help the query optimizer when generating a performant execution plan.
-- Pre-filter Ztest_Participation down into a temp table
SELECT P.ParticipationID,
       P.BMCode,
       P.LatestVersionNo,
       P.LastVersionSubmitted
  INTO #Ztest_Participation_Filtered
  FROM Ztest_Participation AS P
 WHERE P.LatestVersionNo = P.LastVersionSubmitted

-- Create the appropriate index needed for our JOIN later on
CREATE CLUSTERED INDEX IX_TEMP_Ztest_Participation_Filtered_BMCode
    ON #Ztest_Participation_Filtered (BMCode)

-- Final insert into table "FinalResults" (replace this with your actual new table's name)
INSERT INTO FinalResults WITH (TABLOCK) -- TABLOCK hint to reduce log generation and eliminate potential for many row lock escalations
     ( ParticipationID,
       BMCode,
       LatestVersionNo,
       LastVersionSubmitted,
       nomcode,
       [Description],
       LanguageID
     )
SELECT PF.ParticipationID,
       PF.BMCode,
       PF.LatestVersionNo,
       PF.LastVersionSubmitted,
       N.nomcode,
       N.[Description],
       N.LanguageID
  FROM #Ztest_Participation_Filtered AS PF
 INNER JOIN Ztest_nominal AS N-- Simplified JOIN logic to make the query optimizer's job easier
    ON PF.BMCode = N.BMCode

*Note one assumption my above query makes is that you want to use the ParticipationID from Ztest_Participation in your new 3rd table. You won't be able to INSERT this field with your current table schema where it defines ParticipationID as an IDENTITY in the new table. You'll either need to remove that table attribute before the INSERT and then add in after the INSERT or you can temporarily turn on IDENTITY_INSERT for your new table. (But you should turn it back off after the INSERT.)
On my humble laptop, with the schemas you provided above, and 63,000,000 and 62,000 semi-randomly generated records to fill the tables respectively, the above batch of queries runs in a total runtime of 0 seconds. You can find the execution plan it generated for the FinalResults INSERT query on my end here.


How to better manage hard coded cross db references in stored procs?

Referencing the last last paragraph in your question

  I have to believe this is probably a common problem that's been
  solved, but I'm wondering whether the solution is something I can
  still do on top of the implementation we have or if it's something
  that needed to be designed differently to begin with... and if so what
  is the "proper" design to handle this issue?

Yes, I have seen many posts and blogs about this issue and the recommended approach that I have seen is to use SYNONYMS.

A synonym is like an alias.  It provides a way to reference an object like a table, view or stored procedure, but the challenging part is that a SYNONYM can only be defined as a two-part name (schema.object).  This limitation would require you to make code changes to your existing objects that reference 3-part names (Database.Schema.Object).

Here is an example of a synonym from the documentation.  The following example first creates a synonym for the base object, Product in the AdventureWorks2012 database, and then queries the synonym.

-- Create a synonym for the Product table in AdventureWorks2012.
CREATE SYNONYM MyProduct
FOR AdventureWorks2012.Production.Product;
GO

-- Query the Product table by using the synonym.
SELECT ProductID, Name
FROM MyProduct
WHERE ProductID < 5;
GO

--You can also create a `SYNONYM` for a remote object.
CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks2012.HumanResources.Employee;

In your particular situation, let's assume you have references to db1.dbo.Customers.  You could create a synonym called Db1Dbo.Customers that points to db1.dbo.Customers.  Any objects that currently reference db1.dbo.Customers would need to be changed to Db1Dbo.Customers.

Now, you want to make a copy of database db1 and call it db1copy.  After creating the db1copy database, you simply drop the existing synonym in database db1copy for DB1Dbo.Customers and recreate it to point to db1copy.dbo.Customers.

There are some 'gotchas' when using SYNONYMS though.  Take a look at Aaron Bertrand's post - #BackToBasics : The Beauty of the Synonym.  As he says, 'But it's not all roses'.  Among the 'gotchas' that Aaron discusses are:

They introduce a layer of abstraction.
IntelliSense won't always work in certain versions.
You can't ignore performance.
You can't alter a synonym.
You can only point to certain entity types.
Permissions can get messy.


Do operations on indexed views happen in parallel?

It appears to do it in sequence in your plan. Not in parallel. The insert happens into the heap. The inserted rows are inserted into the eager spool which is a blocking operator. When all rows are inserted into the base table the rows are inserted from the spool to the view.

As for the percent issue it appears that the spool confuses SSMS. Despite showing up twice in the plan (with the same NodeId) its cost should only be counted once but it seems as though its cost is completely disregarded when calculating the overall estimated subtree cost for the plan and then it displays the operator percentages as a proportion of this incorrect total.

+---------------------+-----------+------------+--------------------------+
|                     |           | % of total | % of total without spool |
+---------------------+-----------+------------+--------------------------+
| Table Insert        | 0.0100022 | 39.84%     | 50.00%                   |
| Spool               | 0.0051012 | 20.32%     | 25.50%                   |
| CX Insert           | 0.010001  | 39.83%     | 49.99%                   |
| Sequence            | 0.000002  | 0.01%      | 0.01%                    |
| Total               | 0.0251064 |            |                          |
| Total without spool | 0.0200052 |            |                          |
+---------------------+-----------+------------+--------------------------+

SQL Sentry Plan Explorer correctly handles the issue.


score ranking no tie, if tie order by score submit date


Should I worry for this number of Aborted clients & connects?

Those are counters; they are useless without dividing by Uptime or Connections.  I would say something is wrong if

Aborted_clients / Uptime > 0.3 / second
Aborted_connects / Uptime > 1 / second
Aborted_clients / Connections > 30%
Aborted_connects / Connections > 8%

If you exceed any of these guidelines, look into

making sure that your clients do disconnect, and/or
increasing max_connections (but not too much), and/or
increasing wait_timeout

"gone away" comes from network glitches and/or trying to hang onto a connection "too long".  See the various %wait_timeout settings.


Is there a way to replicate Postgres databases in Amazon RDS between regions?

AWS offers cross region data replication for MySQL RDS with a few clicks on aws console cross-region replication for MySQL, but not for postgreSQL yet, maybe in the future they will provide such functionality.

However, you might be able to implement such replication across regions with ec2 instances and avoiding RDS instances.


How can I correct SQL Server Error 8928?

Msg 8928, Level 16, State 1, Line 1 Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 72057594055557120 (type LOB data): Page (1:69965)

There is corruption in your SQL Server database column is LOB and  may have VRCHAR datatype. Index ID 1 indicated problem with Clustered index so I guess minimum repiar options repair allow data loss might delete data to recover database. Dropping and recreating index will not help.Please dont run repair first. Do you have latest valid backup of SQL server database ? Best possible solution here is to restore from valid backup.

How big is your database. Please run restore verifyonly  before restoring the backup to check that backup you are restoring is consistent. Although restore only will guarantee that backup is complete and valid in all format.

Please read SQL Server errorlog and windows event viewer to find out reason why corruption happened and then take steps to fix it.

if you dont have valid backup you might as well try to repair the database using below command

ALTER DATABASE DB_NAME SET SINGLE_USER;
GO
DBCC CHECKDB (N'DB_NAME', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO


As the error message states, you can't just modify the number column to reduce its precision and scale.

Here is one of the workarounds I would use for this situation.

Here is my table.

SQL> desc t1_num;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(30)

Add another column with the number data type with specified precision.

SQL> alter table t1_num add id2 number(10);

Table altered.

Update the new column with the data from the old column.

SQL> update t1_num set id2=id;

1 row updated.

SQL> commit;

Commit complete.

Set the old column to null to change its specification.

SQL> update t1_num set id=null;

1 row updated.

Modify the column

SQL> alter table t1_num modify id number(10);

Table altered.

Update the column with the original data.

SQL> update t1_num set id=id2;

1 row updated.

SQL> commit;

Commit complete.

Drop the temp column

SQL> alter table  t1_num drop column id2;

Table altered.

SQL> desc t1_num;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)


According to BOL, the REFERENCES permission allows the following:

  The REFERENCES permission on a table is needed to create a FOREIGN KEY
  constraint that references that table.

  The REFERENCES permission is needed on an object to create a FUNCTION
  or VIEW with the WITH SCHEMABINDING clause that references that
  object.

There is no reason for a public user to have this permission when they are unable to CREATE or ALTER any other objects.


how to change primary node on mongo replica set without losing cursor

As at MongoDB 3.4, when a primary transitions to a non-primary state (eg. as the result of an election) all active connections are dropped so they can be re-established on the new primary.

Cursor state is specific to a given mongod, so you cannot resume a cursor on a different member of the replica set.

A recommended area to investigate would be why your primary was heavily loaded and why a change in primary would have reduced the load significantly. Generally electable secondaries in the same replica set should be identically provisioned in terms of hardware resources, so exchanging server roles should have pushed similar load onto the new primary. If the load was coming from suboptimal queries that were terminated on the former primary (or due to other resource contention), you could perhaps have avoided reconfiguring your replica set by finding and addressing the root cause.

The MongoDB manual has some information on how to Evaluate Performance of Current Operations. You should also implement a monitoring solution (if you haven't already) in order to capture a baseline of normal activity and help identify metrics that change significantly when your deployment is under load.

If you have long running queries which are likely to be interrupted by a restart you could consider:

Adding some logic to your application to restart the query using criteria from the last document seen while iterating.
Using secondary reads for these queries if your use case can tolerate eventual consistency. Before doing so, it's worth reading Can I use more replica nodes to scale?.


Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution

Reading your question and thinking a bit more about a possible solution leads me to recommend transactional replication.

Make sure you do implement a change freeze to your schema.

From BOL :

  For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

Based on above truth,

Take backup of your current sql server 2005 database and make sure you have instant file initialization enabled on all servers.
Freeze all the data and schema changes <-- This is your downtime.
Migrate your database from sql server 2005 to 2014 and do all post restore steps.
Repoint your application to the new server instance.
Set up T-Rep from sql server 2014 to sql server 2008R2 (this will be an intermediate server or instance and can be on the same new server if you want).You care about this step since, you want to be on supported version. Make sure you initialize replication manually using tsql.
Now have another layer of replication from sql server 2008 R2 (intermediate) to your sql server 2005 server. Make sure you initialize replication manually using tsql.

Note: If the servers are in same datacenter, then you can even do a snapshot which will be very fast.

Above exercise will be worth, since you want a supported rollback plan. Since your business wants to keep things around for 2 weeks, you should be good once you test above.

Note: I have not tried (as I have not been in a situation wherein I have to replicate data from sql server 2014 to 2005), but it should work (though not supported) theoretically. Give it a try for small database and you will easily be able to gauge the effort required.


Find least recently used mysql table index

Starting with MySQL 5.6, the performance_schema instruments Table I/O, and computes aggregated statistics by table, and by index.

See table performance_schema.table_io_waits_summary_by_index_usage:

http://dev.mysql.com/doc/refman/5.6/en/table-waits-summary-tables.html#table-io-waits-summary-by-index-usage-table

Finding the least recently used index involves time and timestamps.

The performance schema measure counting I/O against an index.  It can be used to find the least often used index, which in practice should be pretty close.

Full example here:

http://sqlfiddle.com/#!9/c129c/4


Updating any of the NC index keys is an update that has potential to deadlock. This is the typical read-write deadlock due to different access paths. Even if you update different keys, that doesn't eliminate conflicts because the locks are on key hashes, see %%lockres%% collision probability magic marker: 16,777,215. However, you describe the problem as appearing 'immediately' which strongly suggest that some other factors are at play, and we can only speculate about in lack of actual information.

Of course it would be much more useful to capture the deadlock graph and post it here. Discussing on a mere description of the deadlock has only limited value, as your description may or may not be factual, your understanding may or may not be correct and our interpretation may or may not be accurate.


Is it possible to select internal InnoDB columns?

Unfortunately, there are no metadata available for that info. The two fields you are asking for

DB_TRX_ID
DB_ROW_ID

are only visible from a textual viewpoint in SHOW ENGINE INNODB STATUS\G

You can learn more about it from the MySQL 5.1 Documentation

MySQL 5.5+ offers some metadata for live transactions

mysql> use information_schema
Database changed
mysql> select version(),database();
+-----------+--------------------+
| version() | database()         |
+-----------+--------------------+
| 5.5.9-log | information_schema |
+-----------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+
7 rows in set (0.00 sec)

mysql>

MySQL 5.6 offers even more:

mysql> use information_schema
Database changed
mysql> select version(),database();
+------------+--------------------+
| version()  | database()         |
+------------+--------------------+
| 5.6.10-log | information_schema |
+------------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_LOCKS                           |
| INNODB_TRX                             |
| INNODB_SYS_DATAFILES                   |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  |
| INNODB_CMP                             |
| INNODB_FT_BEING_DELETED                |
| INNODB_CMP_RESET                       |
| INNODB_CMP_PER_INDEX                   |
| INNODB_CMPMEM_RESET                    |
| INNODB_FT_DELETED                      |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_FT_INSERTED                     |
| INNODB_CMPMEM                          |
| INNODB_SYS_INDEXES                     |
| INNODB_SYS_TABLES                      |
| INNODB_SYS_FIELDS                      |
| INNODB_CMP_PER_INDEX_RESET             |
| INNODB_BUFFER_PAGE                     |
| INNODB_FT_DEFAULT_STOPWORD             |
| INNODB_FT_INDEX_TABLE                  |
| INNODB_FT_INDEX_CACHE                  |
| INNODB_SYS_TABLESPACES                 |
| INNODB_METRICS                         |
| INNODB_SYS_FOREIGN_COLS                |
| INNODB_FT_CONFIG                       |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_SYS_COLUMNS                     |
| INNODB_SYS_FOREIGN                     |
+----------------------------------------+
29 rows in set (0.00 sec)

mysql>

Keep in mind that these are living, breathing entities. Do no bother run a COUNT(*). By the time you do, queries have disappeared and other queries have materialized.

Here is a sample:

mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 24E6E0FC5
                 trx_state: RUNNING
               trx_started: 2013-04-22 16:34:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 20275587
                 trx_query: (SELECT
                                        GROUP_CONCAT( CONCAT_WS('','{\"type\":\"',report_stats.type,'\",\"date\":',report_stats.date,',\"leadID\":',report_stats.leadID,',\"vehID\":',report_stats.vehID,',\"contactID\":',report_stats.contactID,'}') SEPARATOR '::') customers,
                                        COUNT(*) total, CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) sales
                                FROM
                                        D6195.report_stats
                                        LEFT JOIN D6195.report_stats self ON report_stats.leadID=self.leadID AND report_stats.vehID=self.vehID
JOIN D6195.leads ON report_stats.leadID=leads.id
LEFT JOIN icar.sales  ON sales.id=leads.salesID
                                WHERE
                                        report_stats.date BETWEEN 1364788800  AND 1367294400 AND
                                        report_stats.type IN ('Internet Lead') AND FROM_UNIXTIME(report_stats.date,'%k') BETWEEN 9 AND 18 AND self.type='First Response'  GROUP BY CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) ASC WITH ROLLUP)
       trx_operation_state: NULL
         trx_tables_in_use: 4
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 6850
*************************** 2. row ***************************
                    trx_id: 24E6E0ECD
                 trx_state: RUNNING
               trx_started: 2013-04-22 16:34:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 20275545
                 trx_query: SELECT * FROM
                        (
                        SELECT
                                actionQue.id  scheduled_action_id,
                                actionQue.date contact_due_date,
                                actions.description,
                                IF(actions.callscript = 0,null,actions.callscript) phone_script_id,
                                actions.priority,
                                leads.id customer_id,
                                CONCAT_WS('',leads.first,' ',leads.last)  customer_full_name,
                                IF((leads.dPhone IS NULL AND leads.ePhone IS NULL AND leads.fax IS NULL) OR leads.noCall='true',0,1) isContactableByPhone,
                                IF((leads.email IS NULL AND leads.email1 IS NULL) OR (leads.noBroadcast!='false' AND leads.noBroadcast IS NOT NULL),0,1) isContactableByEmail,
                                IF(leads.custSince=0,null,leads.custSince)  customer_creation_date,
                                vehicles.soldDate sold_date,
                                IF(leads.lastContact=0,null,leads.lastContact) customer_last_contact_date,
                                IF(leads.modDate!=0,leads.modDate,null) customer_modified_date,
                                CONCAT_WS(' ',vehicles.year,makes.name,models.name) vehicle_description,
                                sources.name ad_source_name,
                                CONCAT_WS(' ',sales.first,sales.last) employee_name,
                                actionQue.v
       trx_operation_state: starting index read
         trx_tables_in_use: 12
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 1
 trx_adaptive_hash_timeout: 0
2 rows in set (0.00 sec)

mysql>

You could dump the queries to a text file, and follow up with SHOW ENGINE INNODB STATUS\G

That is all that MySQL currently exposes for InnoDB's internal metadata.

Here are all the table structures

*************************** 1. row ***************************
INNODB_LOCKS
CREATE TEMPORARY TABLE `INNODB_LOCKS` (
  `lock_id` varchar(81) NOT NULL DEFAULT '',
  `lock_trx_id` varchar(18) NOT NULL DEFAULT '',
  `lock_mode` varchar(32) NOT NULL DEFAULT '',
  `lock_type` varchar(32) NOT NULL DEFAULT '',
  `lock_table` varchar(1024) NOT NULL DEFAULT '',
  `lock_index` varchar(1024) DEFAULT NULL,
  `lock_space` bigint(21) unsigned DEFAULT NULL,
  `lock_page` bigint(21) unsigned DEFAULT NULL,
  `lock_rec` bigint(21) unsigned DEFAULT NULL,
  `lock_data` varchar(8192) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_TRX
CREATE TEMPORARY TABLE `INNODB_TRX` (
  `trx_id` varchar(18) NOT NULL DEFAULT '',
  `trx_state` varchar(13) NOT NULL DEFAULT '',
  `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `trx_requested_lock_id` varchar(81) DEFAULT NULL,
  `trx_wait_started` datetime DEFAULT NULL,
  `trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_query` varchar(1024) DEFAULT NULL,
  `trx_operation_state` varchar(64) DEFAULT NULL,
  `trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_isolation_level` varchar(16) NOT NULL DEFAULT '',
  `trx_unique_checks` int(1) NOT NULL DEFAULT '0',
  `trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',
  `trx_last_foreign_key_error` varchar(256) DEFAULT NULL,
  `trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',
  `trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_is_read_only` int(1) NOT NULL DEFAULT '0',
  `trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_DATAFILES
CREATE TEMPORARY TABLE `INNODB_SYS_DATAFILES` (
  `SPACE` int(11) unsigned NOT NULL DEFAULT '0',
  `PATH` varchar(4000) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_LOCK_WAITS
CREATE TEMPORARY TABLE `INNODB_LOCK_WAITS` (
  `requesting_trx_id` varchar(18) NOT NULL DEFAULT '',
  `requested_lock_id` varchar(81) NOT NULL DEFAULT '',
  `blocking_trx_id` varchar(18) NOT NULL DEFAULT '',
  `blocking_lock_id` varchar(81) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_TABLESTATS
CREATE TEMPORARY TABLE `INNODB_SYS_TABLESTATS` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `STATS_INITIALIZED` varchar(193) NOT NULL DEFAULT '',
  `NUM_ROWS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `CLUST_INDEX_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OTHER_INDEX_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MODIFIED_COUNTER` bigint(21) unsigned NOT NULL DEFAULT '0',
  `AUTOINC` bigint(21) unsigned NOT NULL DEFAULT '0',
  `REF_COUNT` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP
CREATE TEMPORARY TABLE `INNODB_CMP` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_BEING_DELETED
CREATE TEMPORARY TABLE `INNODB_FT_BEING_DELETED` (
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP_RESET
CREATE TEMPORARY TABLE `INNODB_CMP_RESET` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP_PER_INDEX
CREATE TEMPORARY TABLE `INNODB_CMP_PER_INDEX` (
  `database_name` varchar(192) NOT NULL DEFAULT '',
  `table_name` varchar(192) NOT NULL DEFAULT '',
  `index_name` varchar(192) NOT NULL DEFAULT '',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMPMEM_RESET
CREATE TEMPORARY TABLE `INNODB_CMPMEM_RESET` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `buffer_pool_instance` int(11) NOT NULL DEFAULT '0',
  `pages_used` int(11) NOT NULL DEFAULT '0',
  `pages_free` int(11) NOT NULL DEFAULT '0',
  `relocation_ops` bigint(21) NOT NULL DEFAULT '0',
  `relocation_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_DELETED
CREATE TEMPORARY TABLE `INNODB_FT_DELETED` (
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_BUFFER_PAGE_LRU
CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE_LRU` (
  `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LRU_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SPACE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_TYPE` varchar(64) DEFAULT NULL,
  `FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `IS_HASHED` varchar(3) DEFAULT NULL,
  `NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `TABLE_NAME` varchar(1024) DEFAULT NULL,
  `INDEX_NAME` varchar(1024) DEFAULT NULL,
  `NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COMPRESSED` varchar(3) DEFAULT NULL,
  `IO_FIX` varchar(64) DEFAULT NULL,
  `IS_OLD` varchar(3) DEFAULT NULL,
  `FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_INSERTED
CREATE TEMPORARY TABLE `INNODB_FT_INSERTED` (
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMPMEM
CREATE TEMPORARY TABLE `INNODB_CMPMEM` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `buffer_pool_instance` int(11) NOT NULL DEFAULT '0',
  `pages_used` int(11) NOT NULL DEFAULT '0',
  `pages_free` int(11) NOT NULL DEFAULT '0',
  `relocation_ops` bigint(21) NOT NULL DEFAULT '0',
  `relocation_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_INDEXES
CREATE TEMPORARY TABLE `INNODB_SYS_INDEXES` (
  `INDEX_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `TYPE` int(11) NOT NULL DEFAULT '0',
  `N_FIELDS` int(11) NOT NULL DEFAULT '0',
  `PAGE_NO` int(11) NOT NULL DEFAULT '0',
  `SPACE` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_TABLES
CREATE TEMPORARY TABLE `INNODB_SYS_TABLES` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(655) NOT NULL DEFAULT '',
  `FLAG` int(11) NOT NULL DEFAULT '0',
  `N_COLS` int(11) NOT NULL DEFAULT '0',
  `SPACE` int(11) NOT NULL DEFAULT '0',
  `FILE_FORMAT` varchar(10) DEFAULT NULL,
  `ROW_FORMAT` varchar(12) DEFAULT NULL,
  `ZIP_PAGE_SIZE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_FIELDS
CREATE TEMPORARY TABLE `INNODB_SYS_FIELDS` (
  `INDEX_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `POS` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP_PER_INDEX_RESET
CREATE TEMPORARY TABLE `INNODB_CMP_PER_INDEX_RESET` (
  `database_name` varchar(192) NOT NULL DEFAULT '',
  `table_name` varchar(192) NOT NULL DEFAULT '',
  `index_name` varchar(192) NOT NULL DEFAULT '',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_BUFFER_PAGE
CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE` (
  `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `BLOCK_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SPACE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_TYPE` varchar(64) DEFAULT NULL,
  `FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `IS_HASHED` varchar(3) DEFAULT NULL,
  `NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `TABLE_NAME` varchar(1024) DEFAULT NULL,
  `INDEX_NAME` varchar(1024) DEFAULT NULL,
  `NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_STATE` varchar(64) DEFAULT NULL,
  `IO_FIX` varchar(64) DEFAULT NULL,
  `IS_OLD` varchar(3) DEFAULT NULL,
  `FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_DEFAULT_STOPWORD
CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT_STOPWORD` (
  `value` varchar(18) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_INDEX_TABLE
CREATE TEMPORARY TABLE `INNODB_FT_INDEX_TABLE` (
  `WORD` varchar(253) NOT NULL DEFAULT '',
  `FIRST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LAST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `POSITION` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_INDEX_CACHE
CREATE TEMPORARY TABLE `INNODB_FT_INDEX_CACHE` (
  `WORD` varchar(253) NOT NULL DEFAULT '',
  `FIRST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LAST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `POSITION` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_TABLESPACES
CREATE TEMPORARY TABLE `INNODB_SYS_TABLESPACES` (
  `SPACE` int(11) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(655) NOT NULL DEFAULT '',
  `FLAG` int(11) unsigned NOT NULL DEFAULT '0',
  `FILE_FORMAT` varchar(10) DEFAULT NULL,
  `ROW_FORMAT` varchar(22) DEFAULT NULL,
  `PAGE_SIZE` int(11) unsigned NOT NULL DEFAULT '0',
  `ZIP_PAGE_SIZE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_METRICS
CREATE TEMPORARY TABLE `INNODB_METRICS` (
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `SUBSYSTEM` varchar(193) NOT NULL DEFAULT '',
  `COUNT` bigint(21) NOT NULL DEFAULT '0',
  `MAX_COUNT` bigint(21) DEFAULT NULL,
  `MIN_COUNT` bigint(21) DEFAULT NULL,
  `AVG_COUNT` double DEFAULT NULL,
  `COUNT_RESET` bigint(21) NOT NULL DEFAULT '0',
  `MAX_COUNT_RESET` bigint(21) DEFAULT NULL,
  `MIN_COUNT_RESET` bigint(21) DEFAULT NULL,
  `AVG_COUNT_RESET` double DEFAULT NULL,
  `TIME_ENABLED` datetime DEFAULT NULL,
  `TIME_DISABLED` datetime DEFAULT NULL,
  `TIME_ELAPSED` bigint(21) DEFAULT NULL,
  `TIME_RESET` datetime DEFAULT NULL,
  `STATUS` varchar(193) NOT NULL DEFAULT '',
  `TYPE` varchar(193) NOT NULL DEFAULT '',
  `COMMENT` varchar(193) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_FOREIGN_COLS
CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN_COLS` (
  `ID` varchar(193) NOT NULL DEFAULT '',
  `FOR_COL_NAME` varchar(193) NOT NULL DEFAULT '',
  `REF_COL_NAME` varchar(193) NOT NULL DEFAULT '',
  `POS` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_CONFIG
CREATE TEMPORARY TABLE `INNODB_FT_CONFIG` (
  `KEY` varchar(193) NOT NULL DEFAULT '',
  `VALUE` varchar(193) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_BUFFER_POOL_STATS
CREATE TEMPORARY TABLE `INNODB_BUFFER_POOL_STATS` (
  `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `POOL_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `FREE_BUFFERS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OLD_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MODIFIED_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_DECOMPRESS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_READS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_FLUSH_LRU` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_FLUSH_LIST` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_NOT_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_MADE_YOUNG_RATE` double NOT NULL DEFAULT '0',
  `PAGES_MADE_NOT_YOUNG_RATE` double NOT NULL DEFAULT '0',
  `NUMBER_PAGES_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_PAGES_CREATED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_PAGES_WRITTEN` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_READ_RATE` double NOT NULL DEFAULT '0',
  `PAGES_CREATE_RATE` double NOT NULL DEFAULT '0',
  `PAGES_WRITTEN_RATE` double NOT NULL DEFAULT '0',
  `NUMBER_PAGES_GET` bigint(21) unsigned NOT NULL DEFAULT '0',
  `HIT_RATE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NOT_YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_PAGES_READ_AHEAD` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_READ_AHEAD_EVICTED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `READ_AHEAD_RATE` double NOT NULL DEFAULT '0',
  `READ_AHEAD_EVICTED_RATE` double NOT NULL DEFAULT '0',
  `LRU_IO_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LRU_IO_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `UNCOMPRESS_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0',
  `UNCOMPRESS_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_COLUMNS
CREATE TEMPORARY TABLE `INNODB_SYS_COLUMNS` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `POS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MTYPE` int(11) NOT NULL DEFAULT '0',
  `PRTYPE` int(11) NOT NULL DEFAULT '0',
  `LEN` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_FOREIGN
CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN` (
  `ID` varchar(193) NOT NULL DEFAULT '',
  `FOR_NAME` varchar(193) NOT NULL DEFAULT '',
  `REF_NAME` varchar(193) NOT NULL DEFAULT '',
  `N_COLS` int(11) unsigned NOT NULL DEFAULT '0',
  `TYPE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

If you want this metadata, you have two options

OPTION #1: upgrade to MySQL 5.5 (for the 7 tables)
OPTION #2: upgrade to MySQL 5.6 (for the 29 tables)


FULLTEXT Search (InnoDB) - Am I doing it right?

Will you want San Jose to come up when the user types jo?  If not, then FULLTEXT is not useful.  Instead, a simple LIKE '$searchQuery%' is all you need.

Because of how many rows you have (130K), consider

SELECT DISTINCT -- to avoid returning dups.  (There are perhaps 328 San Jose's in the world.)
Don't auto complete if (1) the user has typed only 1 letter, and (2) if the select returns "too many" results.
Another thought is to ORDER BY POPULATION DESC.
Add a LIMIT to the SELECT.
Think about what to do with accents.


Database design for an online babyShop ,Toy shop

Your design commonly looks good except few mistakes.

1) You had used a wrong field in order to establish a relation between ProductCategory and Product. You have to use ProductCategoryID in Product table instead of using ProductCategoryName. Howeever, some products may belong to more than one product category.
If this is a valid case for your project, you should add additional table something like "ProductVsProductCategory" which will allow you to establish a "many to many" relation between Product and ProductCategory tables if you intend to add some products into multiple categories.

Basic structure of ProductVsProductCategory might look like following:

ProductVsProductCategoryID (primary key)

ProductID (refers to Product table)

ProductCategoryID (refers to ProductCategory table)

2) OrderProductName field in the OrderDetail table is unnecessary. You have a relation to Produt table and you can access ProductName from there anyway. You can cancel it.

3) OrderNumber in the Order table is unnecessary if it is not a special value rather than unique identity number. You can use OrderID field as an identity of the order.

4) Similary, OrderQuantity and OrderTotalPrice fields might be unnecessary if you have not special reason to place them there. You can calculate order quantity and total price values by means of using basic sql statements that sums related OrderDetail rows.

There are many additional tables can be added depending on your project requirements. I also recommend you to follow MSDN articles and specially CodeProject in order to improve your abstraction skills.


NOTE: I work with Sybase ASE, including working with proxy tables and interserver communications/queries; I do not work with MSSQL or SSRS; so fwiw ...

If the MSSQL data is driving the main query then I'm guessing 1 of 2 scenarios are possible with normal interserver communications:

from MSSQL: for each MSSQL row of interest, perform an interserver join with the remote Sybase tables; the overhead for performing a large number of interserver calls could add up quickly
from Sybase: pull all MSSQL rows of interest in one interserver request, then with the MSSQL data set residing in Sybase conduct the joins with the local ASE tables; the overhead for a single interserver join is going to be much less than the overhead for scenario #1, definitely much faster than the current method of using Access as an intermediary stopping point, and likely faster than any bcp-based operation)

NOTE: While newer versions of ASE have the ability to push/pull parts of interserver queries to/from remote servers, I'm not sure if/how this feature works when the remote server is a non-ASE RDBMS (eg, MSSQL).

For scenario #2 you have a couple options, all based on having a proxy table defined in ASE that references the remote MSSQL table:

perform a standalone query to pull the MSSQL data into a local ASE table (could be a permanent or #temp table), eg, select/into or insert/select, then perform your local query using this MSSQL-populated table to drive the query
reference the proxy table in your multi-table query, and if the optimizer has enough info to properly cost the join it will hopefully perform an initial/single pull from the proxy table (most likely into a local worktable) and then use that data to drive the rest of the local joins


This is a matter of looking at physical writes per buffer pool or tablespace over time.  db2top will tell you this if you are in Delta mode and look at the "Delta p_reads/s" column.


Count rows with max per group and additional condition

Since you need a subquery in either case, I would use a plain aggregate in the subquery (may be cheaper):

SELECT count(*)
FROM  (SELECT race_id, max(rating100) AS rating100
       FROM   horse_main
       GROUP  BY 1) x
JOIN   horse_main h USING (race_id, rating100)
WHERE  h.race_result = 1;

If there are many rows per race_id, it will be faster to get group-wise maxima with one of these techniques:

Optimize GROUP BY query to retrieve latest record per user

Or use the window function rank() - that's what you count effectively: all greatest rating100 per race_id with race_result = 1:

SELECT count(*)
FROM  (SELECT race_result, rank() OVER (PARTITION BY race_id ORDER BY rating100 DESC) AS rnk
       FROM   horse_main) x
WHERE  rnk = 1
AND    race_result = 1;

An index on (race_id, rating100 DESC, race_result DESC) should help performance with big tables.

Assuming rating100 is defined NOT NULL, else you need to add NULLS LAST in query and index.


For certain types of parameterization, it's not going to matter if you use EXEC() or sp_executesql, because some things can't be parameterized anyway. For example, you expressed in the comments (please update your question to be more specific about your requirements!) that you are parameterizing table names, but these can't be parameterized because they need to expressed literally to SQL Server (it can't tokenize that and swap at runtime).

To protect yourself from table name vulnerabilities, you can easily protect yourself like this:

DECLARE @tablename sysname; -- pretend this is a parameter
SET @tablename = N'this_is_not_a_table';

-- in procedure body:
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @tablename)
BEGIN
  RAISERROR(N'Table does not exist.', 1, 11);
  RETURN;
END
SET @sql = N'SELECT ... FROM dbo.' + QUOTENAME(@tablename) + ...;
-- EXEC(@sql) or EXEC sys.sp_executesql @sql

Now, I prefer to use sp_executesql always, partly because it promotes using strongly-typed parameters (avoiding SQL injection issues as well as double-single-quote issues), but also because in some cases you will be passing parameters for both parameterizable and unparameterizable values (yes I made those words up). More info:

Bad Habits to Kick : Using EXEC() instead of sp_executesql
Protecting Yourself from SQL Injection - Part 1
Protecting Yourself from SQL Injection - Part 2

Now the only thing you have to worry about is if someone is able to create tables and can create a table named sys.objects; DROP TABLE foo; -- - but if you have someone you don't trust but has the ability to create tables in your database...


best way to retrieve data using multiple parameters in Cassandra?

This really depends on what other queries you're planning to execute - in Cassandra you need to model data around your queries.

For your specific question, you need either secondary index on userid, and do search (there are also some limitations there), or build second table with composite primary key consisting of userid, date, id - this would be especially useful if you'll have query "Show me all blog posts by this user".

Doing ALLOW FILTERING is generally is not recommended, because it may cause scanning of your whole cluster.


Set TRUSTWORTHY On smacks down service broker from working

Within each database there exists a dbo User. This User (Database-level) always exists, but the SID (Security IDentifier) that it maps to is not always the same; it will map to whatever Login (Instance-level) is specified either when creating the Database of when altered to have a new "Database Owner". The dbo User is one of the entries in sys.database_principals.

When initially setting the Database Owner during CREATE DATABASE, or when changing it later, the SID of the "owner" is not only placed into sys.database_principals, but is also recorded in master.sys.databases. If the Database never leaves the Instance in which it was created, there should never be a mismatch between the SID values in sys.database_principals and master.sys.databases. But, if the Database is ever restored or attached to (or from) another Instance, then it is possible that the SID values won't match. You can check the values in both places using the following queries:

USE [tempdb]; -- Change to whatever DB you want to check

SELECT      msd.owner_sid,
            msp.[name]
FROM        [master].[sys].[databases] msd
INNER JOIN  [master].[sys].[server_principals] msp
        ON  msp.[sid] = msd.[owner_sid]
WHERE       msd.[database_id] = DB_ID();

SELECT sdp.[sid]
FROM   [sys].[database_principals] sdp
WHERE  sdp.[name] = N'dbo';

Now, by default, TRUSTWORTHY is set to OFF and permissions on operations involving Impersonation (i.e. EXECUTE AS) are confined to the database where the operation was executed from. While impersonating a DB User, attempting to access another DB (or even Server/Instance-level resources, I believe), SQL Server will assume that the current DB User's SID (i.e. the one being impersonated) has a matching Login so it can take on those permissions. This is blocked when TRUSTWORTHY is OFF, but setting it to ON lifts the Database-level quarantine and allows Impersonation to extend beyond the initial Database. The SID of the dbo User might certainly exist as a Login, but if it is not the same SID that is mapped as the database owner in sys.databases, then that is a clear indication that something is not right (and most likely that that DB came from another Instance), and that there might possibly be malicious intent in the requested operation.


Spinning multiple environments from single backup file

If you are open to look into 3rd party solutions, you can try SQL Clone by Redgate.
It will created virtual data files and virtual delta files. These files combined will be presented to SQL Server and it will be treated as a separate copies of the database.
You can find documentation here: https://www.red-gate.com/products/dba/sql-clone


If you're using SQL Server Management Studio (SSMS), you don't need to "create" a connection via ODBC, you can just connect directly to the server via the SSMS user interface:

In SQL Server, the equivalent of an Oracle "database server" is called a "SQL Server Instance".  It can contain many databases, which are similar to a "schema" in Oracle.

So, when you connect via SSMS, you connect to the instance, which takes the form of:

COMPUTERNAME

For a "default, unnamed" instance, or for a "named" instance:

COMPUTERNAME\INSTANCENAME

INSTANCENAME gets resolved to a port automatically by the SQL Server Browser Service; if that service is not running, or is blocked by a firewall, you may need to connect using the port number, as in:

tcp://COMPUTERNAME,0000

where 0000 is the port number for the instance.

If you don't have access to the instance, you'll see an "authentication failed" error message.  If SSMS cannot contact the instance because of a firewall, or the server is not listening on the port you specified, you'll see an error indicating that, similar to:

  "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.   Verify that the instance name is correct and that SQL Server is configured to allow remote connections. "

To create a connection via ODBC, you open the ODBC Data Source Administrator from the Windows Control Panel.  You can either create a "User DSN" or a "System DSN", either way choose the SQL Server Native Client 12.0 driver:

Fill in the details (use the servername as detailed above):

Accept the defaults for the rest of the configuration screens if you plan to use integrated Active Directory domain authentication.  If the login will be made using a SQL Server Login, enter those details on this screen:


Oracle SQL Developer: Set the  value when exporting to CSV

Just set the below in your session, as you would do in SQL*Plus:

set null "null"

Note: this will not affect how NULLs are displayed in the data grid (you can set that from the menu: Tools - Preferences - Database - Advanced - Display Null Value As), but it affects the exported CSV.


Is it Possible to Recover Data from a BLACKHOLE table?

You said

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam

Whoever told you that should have told you to enable binary logs as @jynus commented.
The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups.
EXAMPLE #1 : Star Topology
Some have Star Topologies to write data to a MySQL Replication Master. The Master has nothing but BLACKHOLE tables. All Slaves have InnoDB or MyISAM. That way, if the Master gets hacked, there is no local data. Such a Master is known as a Distribution Master.

Apr 28, 2011 : MySQL in star topology (ServerFault)
Mar 11, 2011 : MySQL in star topology (StackOverflow)

EXAMPLE #2 : Single Slave, Multiple Masters
This involves setting up Circular Replication with three servers. Two Masters with a mix of BLACKHOLE tables and a Slave with all real tables. This technique predates multisource replication.

Apr 18, 2013 : Single slave - multiple master MySQL replication
May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB? (ServerFault)

EXAMPLE #3 : Increase Write Performance
I knew of a Web Hosting Client who went to Percona for performance issues on one table. Since the client had MySQL Replication with Multiple Slaves, Percona converted the problem table to BLACKHOLE. This increased write performance on the Master. The data had to be read from the Slaves.
I once suggested this in a post for recording audit information (Performance of a Trigger vs Stored Procedure in MySQL)
EPILOGUE
While some of the comments remind me of StarTrek DS9, the BLACKHOLE Storage Engine is what its name says: NO DATA IS EVER STORED !!! If you had binary logs enabled, that would have been your only chance of having data.


Importing huge dumps into a mysql server 5.5

If your database tables have them, it's always best to drop indices and recreate them after the import. Also, disable foreign keys temporarily by executing SET FOREIGN_KEY_CHECKS=0; (and SET FOREIGN_KEY_CHECKS=1; afterwards). This will speed up your import (though by how much is hard to tell).


Could we delete old database and grid version?

Oracle provides a deinstallation tool starting with Oracle 11.

It's located in $ORACLE_HOME/deinstall/  (in your case the old Homes). It takes care of the inventory and remote hosts in case of a RAC.


SQL Server second transaction log seems to be used as well - not only after first one is full

The Log files are used like if it was only one file. Both files have VLF so let's say the first log file have VLF1 to VLF10 and the second one have VLF11 to VLF15. It will write down stuff in the VLF1 and once that one is full, will continue in VLF2, etc... once it reach VLF15, it will then go back to VLF1 (not before) so it's 100% normal that both files still get used.
If you want to get rid of your second file, you'll have to take log backup until the "active" VLF is on the file #1 and then, you'll be able to delete the file.


Linking Fields From 2 Different Tables (Microsoft Access)

In your scenario, you have two 'entities':

Employees. These are stored in table Employee Details.
Schedules. Stored in table Schedules. A schedule stores an Employee ID and other information.

What you want to do is make sure that every schedule contains a valid Employee ID. That, you want to make it impossible to create a schedule with an Employee ID doesn't exist, and also to delete an employee from the Employee Details table if that Employee ID is currently being used in a schedule.

These requirements are handled by what databases call a foreign key constraint. In Access, you can use a graphical tool ('Relationships') to 'link' the Employee ID column in the Schedules table to the Employee ID column in the Employee Details table so that the constraints I described above are always met.

You can read up on foreign key constraints and Access' Relationships manager tool to accomplish this.


How do I export data using PostgreSQL COPY command in required format?

You cannot.  Just ask an editor to replace \, with ,.

Otherwise, if the desired output format is really what you want, you can change the query:

SELECT id::text || ',' ||
       value_1::text || ',' || ... ||
       your_array_column
...

In any case, I'd suggest fixing your array data type.  Storing numbers as text looks really bad.


Without knowing exactly what you meant by 'The "distinct" does work the "order by" does not.', I guess you get an error:

  ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

It is more or less self-explanatory.  Try

json_agg(DISTINCT (colA, colB) ORDER BY (colA, colB))

instead - you don't aggregate individual columns in json_agg() but a record, and should order by that one.


Oracle password has expired, however I have no way of changing it

If the account is locked, as far as I know, the user can't change it. The Oracle output in your question shows the account is not locked, but expired.

Log in to your database as a user who has privilege to alter other users (for example, SYS) and issue the following command:

ALTER USER tmpdbo IDENTIFIED BY new_password;

You can change the expiration behaviour. There already was a question about that on Stack Overflow:

Make Oracle Password Never Expire


Start the instance in MOUNT mode, delete the logs, then open the database.

$ rman target /

RMAN> startup mount
RMAN> delete force noprompt archivelog all;
RMAN> alter database open;


How to get readonly access to mysql files via g+w permission 750 or 640?

Well, I found online that the environment variable UMASK and UMASK_DIR must be set, regardless of what the command umask will output.

One must add export UMASK=0640 and export UMASK=0750 to the top of their startup script, immediately before calling mysqld_safe.

The leading zero is important to switch to the octal numbering system.

The first thing I tried was 0027, but that produced files with permissions u=rw,g=w,o=rwx so that is what prompted me to invert the permissions in the environment variable to 0750 (and 0640) to produce the desired results.

I hope my self-answer will help a future visitor. :-)


Finding out the exact size of the database after inserting records?

To be honest with you, that formula is correctly expressed but does not reveal fragmentation. If you were to defragment all your tables, then, yes, it is accurate.

If you want to figure out the fragmentation, let give you my method.

I have an old post, How do you remove fragmentation from InnoDB tables?. If you look on the bottom of that post, I show you how to find the fragmentation of a single InnoDB table. Let us go through this exercise.

SCENARIO

datadir is /var/lib/mysql
Given table mydb.mytable

Physical file /var/lib/mysql/mydb/mytable.ibd (If table is InnoDB)
Physical file /var/lib/mysql/mydb/mytable.MYD (If table is MyISAM)
Physical file /var/lib/mysql/mydb/mytable.MYI (If table is MyISAM)

TOTAL SIZE OF A SINGLE TABLE (mydb.mytable)

You would run the query

SELECT data_length+index_length tblsize
FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

From the OS

DB=mydb
TB=mytable
cd /var/lib/mysql/${DB}
TBL_OSBYTES=0
for X in `ls -l ${TB}.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done

Combining the two methods, you get this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
DB=mydb
TB=mytable
cd /var/lib/mysql/${DB}
TBL_PHYBYTES=0
for X in `ls -l ${TB}.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

TOTAL SIZE OF A DATABASE (mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
DB=mydb
cd /var/lib/mysql/${DB}
TBL_PHYBYTES=0
for X in `ls -l *.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}'";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

TOTAL SIZE OF ALL DATABASES

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')";
DBLIST=""
for DB in `mysql ${MYSQL_CONN} -ANe"${SQL}"` ; do DBLIST="#{DBLIST} ${DB}" ; done
TBL_PHYBYTES=0
for DB in `echo "${DBLIST}"`
do
    cd /var/lib/mysql/${DB}
    for X in `ls -l *.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

GIVE IT A TRY !!!


mysql multiple column index Cardinality order

Not sure if this answers your question, but I would not bother so much about the individual cardinality of X and Y. For index design I would start with the predicates that the index should assist with. If you have queries like:

where X = ?
where Y = ? and X = ?

go with (X, Y). If you have queries like:

where Y = ?
where Y = ? and X = ?

go with (Y, X). If your queries are like:

where X = ?
where Y = ?
where Y = ? and X = ?

go with (Y, X) and (X, Y). If your queries are like:

where X = ?
where Y = ?

go with (X) and (Y)

I assume here that the cardinality for each column is greater than 1. For cardinality in general I consider the full key cardinality (all involved columns together) to be of more importance than the individual cardinality.


MSSQL - Uniquely identify an application connecting SQL Server

The only way I can think of is, using "Application Name" in the connection string.
That is how SQL server recognises different applications.

I think, you can append application name in the connection string at runtime using your v$session column.


Re-subscribing to a merge publication after edits made offline

None of the above.

If the 5 new subscriptions are initialized with a snapshot then by default all subscription database objects will be dropped and recreated from the snapshot bcp files.  So the data from the week offline will appear to be deleted.

  If C? Is there a way to mark those records for replication so they can
  be brought up to the publisher, and ultimately the other subscribers?

With Merge there is an option to upload pending changes before reinitialization but since your subscriptions have been removed you won't have this option.

The way to handle this is to manually sync the databases using tablediff utility or Red Gate's SQL Data Compare.  Then recreate the publication from the manually merged database - then subscriptions.


Necessary to use a checksum on a backup and not solely the 'verify backup' option?

From MSDN

  CHECKSUM Specifies that the backup operation will verify each page for
  checksum and torn page, if enabled and available, and generate a
  checksum for the entire backup. This is the default behavior for a
  compressed backup.

All the verify backup option does is confirm that the header of your backup is readable, nothing else.

Neither of these options will tell you that you have a good backup! The only way to confirm that your backup is good is to restore it on another system


How can I safeguard against SQL injection where parameterizing my query is not an option?

If you really can limit to alphanumeric characters, then yes, that's fine, IF you are limiting to ANSI alphanumeric characters. In Unicode, because every character is more than one byte, many representations of alphanumeric characters are actually unsafe and could lead to injection.

You will need to sanitize the data server-side and make sure the encoding is one that is safe. I'd suggest you take a look at the ESAPI libraries by OWASP for how they do it (see third section on this page).


oracle 10G forms connection with Oracle database 10G Enterprise Edition in windows 7

The message is an indication of incompatibility of Forms version that you're using with your OS, i.e. Windows 7.

You'll need to install Oracle Forms version 10.1.2.0.2 with patchset 3 to have this corrected.

Else you can also install Oracle Forms 11g R2 which is certified for Windows 7


If there's only one my.cnf file, and it's  /etc/my.cnf, then that's the one it loads. From the manual:

On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).
/etc/my.cnf   Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf    Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options (in your home directory)

So, if you check the other directories and there are no my.cnf files, you can be certain it's loading from /etc/my.cnf.
If you use MYSQLAdministrator, supposedly you can get it from Service Control->Configuration File->Config filename,  as per this post.


I asked same question in stackoverflow. It trun out the problem was from maxscale user permissions.

GRANT SELECT ON mysql.user TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.db TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'maxscalehost';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'maxscalehost';

https://stackoverflow.com/a/59540995/5456910


Creating a database with only MySQL command line?

Yes, most linux system administrators only use the command lines client (or other cli tools to work with the database). Maybe the only other thing you will need for design is a text editor (for writing easily before executing), like vi(m), nano or emacs, and pen and paper.

Here it is a first crash course:

Create a database:

CREATE DATABASE your_database_name;

Change the current database:

use your_database_name

Create your first table:

 CREATE TABLE your_table (
     id bigint unsigned PRIMARY KEY auto_increment,
     name varchar(50) NOT NULL,
     description text,
     modified_on timestamp
 ) ENGINE = InnoDB;

Populate your table:

 INSERT INTO your_table (name, description) VALUES ('pheidlauf', 'You are awesome');

Select your table:

 SELECT * FROM your_table;

Learn about MySQL SQL variant, in particular DDL commands, DCL and about using the command line client. There are many resources (I have just linked to wikibooks and the official MySQL documentation), but the command line client also has integrated help:

mysql> help create table

Another quick startup guide.


What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the columns and tables, "Table1", "Table2", "Field1" say nothing about the table or the column and are very confusing).

I see two options depending on what your requirements are:

If the relationship between rows of tables 1 and 2 is only inferred by matching the "Field1" values and you don't need to store any more info on that many-to-many relationship, then you don't need any extra table at all. The 2 columns (Field1) in the respective tables are enough to find the related data. You could use a simple join on the two tables:

SELECT
    a.id AS ID1,
    b.id AS ID2
    -- any additional column needed from any of the 2 tables
FROM table1 AS a
  INNER JOIN table2 AS b
    ON a.Field1 = b.Field1 ;

If the relationship between rows of tables 1 and 2 is not inferred by matching the "Field1" values (but there are extra conditions/requirements) or you need to store extra info about the relationship, you need an intermediate table. To make sure (enforce) that only rows with same "Field1" are actually related in this table, the common column ("Field1") has to be added, too, and participate in the two FOREIGN KEY constraints. (you will also need UNIQUE constraints on (Fieldd1, ID), on each of the 2 tables, for the foreign keys to work):

CREATE TABLE intermediate
  ( Field1 VARCHAR(5),
    ID1    INT NOT NULL,
    ID2    INT NOT NULL,
    -- possible extra columns
    PRIMARY KEY (ID1, ID2),
    FOREIGN KEY (Field1, ID1)
      REFERENCES table1 (Field1, ID),
    FOREIGN KEY (Field1, ID2)
      REFERENCES table2 (Field1, ID)
) ;


Running sp_who2 will show you who is connected to the database.

The safest way to disconnect users is to ask them to disconnect themselves. If that is not practical, you can take the database offline or set it to SINGLE_USER mode to force the other connections out before performing your restore.

See this for more information and examples:

ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Test4]
FROM DISK = 'c:\test4.BAK'
WITH MOVE 'Test4_Data' TO 'c:\data\Test4.mdf',
MOVE 'Test4_Log' TO 'c:\data\Test4_log.ldf'


Designing a database structure for huge sets of form data

Have 'real' columns for a few of the fields -- the ones that you are likely to search or filter on.  Throw the rest (or, simply, all) of the fields into a big JSON string as another column.  Perhaps MEDIUMTEXT.


Oracle AWS RDS supermaster user cant grant select on some tables

This will not work:

GRANT SELECT ON v$sqltext_with_newlines to "TESTUSER";

v$sqltext_with_newlines is a public synonym for sys.v_$sqltext_with_newlines. If you want to grant select on that view to someone, use this:

GRANT SELECT ON sys.v$sqltext_with_newlines to "TESTUSER";

And after that, TESTUSER can select from v$sqltext_with_newlines. But:

Just because your user can select from v_$sqltext_with_newlines, that does not mean you can grant this privilege to others. For that, you need to have the select privilege granted with the GRANT OPTION to your user. This restriction can be worked around by having the GRANT ANY OBJECT PRIVILEGE system privilege, for objects in user schemas, but not for objects owned by SYS.

Here is the list of privileges of the master user:

Master User Account Privileges

  ALTER DATABASE LINK, ALTER PUBLIC DATABASE LINK, DROP ANY DIRECTORY,
  EXEMPT ACCESS POLICY, EXEMPT IDENTITY POLICY, GRANT ANY OBJECT
  PRIVILEGE, RESTRICTED SESSION, EXEMPT REDACTION POLICY

  AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE, CONNECT, CTXAPP, DBA,
  EXECUTE_CATALOG_ROLE, RECOVERY_CATALOG_OWNER, RESOURCE,
  SELECT_CATALOG_ROLE

So no, the master user does not have unlimited access, there are some things that even that user can not do.


SQL Server Buffer Pool Extension in FullText Index Population

How to check the buffer pool usage of SQL Server ( I want to know if the BPE is used )?

You can query sys.dm_os_buffer_descriptors

An example would be something like:

SELECT   CONVERT(NUMERIC(38, 2), COUNT(*) * 8 / 1024.0) AS cached_data_mb,
         CONVERT(NUMERIC(38, 2), SUM(CONVERT(BIGINT, free_space_in_bytes)) / 1024. / 1024.) AS free_mb,
         CASE database_id
              WHEN 32767 THEN 'ResourceDb'
              ELSE DB_NAME(database_id)
         END AS database_name
FROM     sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY cached_data_mb DESC;

  Did I miss any Server or Buffer Pool configuration?

It doesn't appear that way from the documentation. There aren't a lot of options presented.

  Does BPE affect FullText index population?

You're at the distinct disadvantage of using two features together that not a lot of people use separately. You may have to do your own experiments here.

One thing to be aware of is that BPE activity is single threaded, and each I/O is limited to a single 8K page. This might make them ineffective with Full Text.

I'd much rather stick an appropriate amount of RAM in the server.


The best MySQL Settings for 32GB RAM on a Dedicated Server

Configure some important variables in my.cnf file and restart MySQL service :
max_connections=500
max_connect_errors=100
innodb_buffer_pool_size={ value should be 70% of total Memory }
innodb_log_buffer_size={ less than 4 MB if there are TEXT/BLOB then set to more value between 8-128 MB }
innodb_file_per_table=ON
thread_cache_size={ if threads_connected value varies from 75 to 100, set thread_cache_size to 25 }
table_cache={ more than default }


Can I configure the PostgreSQL locale so that it ignores umlauts in ILIKE queries?

A common solution to this problem is to have a "shadow" or "search" column for proper names. This solution has the advantage of being portable.

For example, here in Ireland, there are many people who are called MacGuire and there are those called McGuire. Depending on other names, some Irish people also use accents in a particular name and some don't. Furthermore, some people put a space between "Mac" and "Guire". And don't even get me started on the problems that names such as O'Malley cause with SQL, i.e. escaping the single quote.

So, in a shadow column, one might have rules
"Mac"/"Mc"/"Mac " -> MC (shadow columns are normally kept all caps - another source of varialbility (could have Mcguire). "O" and "O'" -> O.

What's more, say the first name was Rónán (Ronán and Ronan are also common), so another rule would be

á -> A and

ó -> O

You can apply these rules via ON INSERT/UPDATE triggers.

So, this would lead to a table thus:

Display_Name    Search_name    Address
------------    -----------    -------
Ronan MacGuire  RONAN MCGUIRE  1 Clontarf D1
Ronan McGuire   RONAN MCGUIRE  2 Malahide D2
Rónán Mcguire   RONAN MCGUIRE  3 Mary's terrace D3

And then the service agent will distinguish on the basis of address.

So, I am suggesting that something similar might be a solution to your problem.


Provided Minimal Supplemental Logging was turned on before the incident occurred that you want to research (it is off by default since there is a minor performance hit by having it on) which you can check via: select supplemental_log_data_min from v$database;, then Logminer is the tool for this.

Start logminer:

execute dbms_logmnr_d.build(options=> dbms_logmnr_d.store_in_redo_logs);

Load the archive logs that you want to view into Logminer:

begin
   dbms_logmnr.start_logmnr(
     starttime => TO_TIMESTAMP('2017-03-02 07:00:00', 'YYYY-MM-DD HH24:MI:SS'),
     endtime => TO_TIMESTAMP('2017-03-02 15:00:00', 'YYYY-MM-DD HH24:MI:SS'),
     options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine);
end;

The above assumes that the ArchiveLogs are still on disk.  If you've backed them up, you'll have to unpack them from the backup and register them with the database first.

Query the Redo

select timestamp, operation, username, session#, scn, sql_redo
   from v$logmnr_contents where sql_redo like '<SOME SEARCH CRITERIA>';

End your logminer session

exec dbms_logmnr.end_logmnr;

You can read about Logminer from the docs.

If supplemental Logging is turned off, you may still be able to get the information you need by determining when the incident occurred using flashback queries to narrow down the exact time of the incident (provided you have a large enough Undo and retention policies are adequate):

select * from <SOMETABLE>
  AS OF TIMESTAMP TO_TIMESTAMP('2017-03-02 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Then query the audit trail to find out when the shared schema user logged on around the above timeframe:

select * from dba_audit_trail where username = '<SOME USER>' order by timestamp;

The above assumes that you are logging audit information to the DB (show parameter audit_trail;).  You can read about auditing from the docs.


Is there any way to determine how many rows a transaction has modified (before the transaction ends)?

Following @Nick Barnes suggestion to use pg_table_size I was able to obtain a rough estimate of progress. Note however that this can only work when only one transaction is modifying the table. If you have multiple transactions modifying the table you will not be able to tell how much a specific transaction has completed.

Moreover in order for you to be able to get a progress estimate you should know how many rows are affected by the query.

So using the command:

SELECT pg_table_size('my_table');

You can obtain the size of the table (this contains both the commited and uncommitted data).

If you know the initial size of the table initial_size, the initial number of rows N, and the number K of rows affected by the query you can get an estimated of the data that your query will write:

delta_size = (initial_size/N)*K

Now if the current table size is current_size your estimate for progress will be:

progress_perc = 100*(current_size - initial_size)/delta_size

If you do not know the initial size of the table you can estimate it by checking the growth of the table.

Using pg_table_size check how much the table grows in a certain interval of time delta_t, say 1 hour. Then you can estimate the initial size from the equation:

initial_size = current_size - growth*num_delta_t_passed

You can obtain the time since when the transaction started using the query:

SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

(taken from this gist page)

For Postgresql 9.6 it does not seem possible to obtain an accurate response. I don't know whether a better option exists for Postgresql 10+.


table_open_cache increase issue - MariaDB 10.2

I can see 3 different reasons why MariaDB might reset this variable. In order of most to least likely explanation:

You have another .cnf file where the table_open_cache variable is being set to 4895.
The MariaDB Systemd unit file (or SysV init script on older Linux versions) is overwriting the variable value you've set.
You're running a non-standard MariaDB server that's been compiled with a hard-coded value for table_open_cache. (Unlikely!)

Additionally, it's possible that another software (such as cPanel) might change the variable dynamically after MariaDB has started.

To see which options the MariaDB server will get from the option files, run:

mysqld --print-defaults

To find all the places where the MariaDB server is looking for .cnf files, run:

mysqld --verbose --help | grep -A 1 'Default options are read'

You can then grep each of these files to see if you find any instances of table_open_cache or table-open-cache.

For reference, you should be able to see the current value of the variable with:

show global variables like 'table_open_cache';

And you may also want to consult this page in the MariaDB Knowledge Base: Optimizing table_open_cache.


How do I CASCADE DROP a table being referenced in MariaDB?

I don't think that's how CASCADE works with MySQL derivatives!

Tables:

CREATE TABLE foo
(
  x int PRIMARY KEY
);

CREATE TABLE bar
(
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ON DELETE CASCADE
);

Some values:

INSERT INTO foo VALUES (3), (45), (7);
INSERT INTO bar VALUES(3), (3), (45), (7), (7);

Then:

DELETE FROM foo WHERE x = 3;

Now:

SELECT * FROM bar;

 x
 7
 7
45

So, (for the MySQL family) the DRI (Declarative Referential Integrity) works on the level of records and not on a table level. The referential constraint exists on the table bar and not on foo - therefore deleting foo has no effect on that constraint - and the system won't let you delete the parent table, but will let you delete the parent record.

Take a  look at the fiddle here and it's fairly clear (and actually logical enough in one sense) if you reflect on it - at least to me it is!

However, a big caveat to the logic argument - PostgreSQL also doesn't allow

DROP TABLE foo;

but with a far more informative error message:

ERROR:  cannot drop table foo because other objects depend on it
DETAIL:  constraint asdf on table bar depends on table foo
HINT:  Use DROP ... CASCADE to drop the dependent objects too

however

DROP TABLE foo CASCADE;

does succeed.

So, MySQL fails on both statements, whereas PostgreSQL's solution is (IMHO and as usual) superior - it "forces" the CASCADE if it's explicitly invoked. MySQL won't let it delete under either circumstance.